# App tables > Declare the tables your app owns in tables, and better-supabase upgrade writes them with the scope column, row level security, grants and a pgTAP test. Source: https://bettersupabase.com/docs/repository/tables Blocks cover the tables every app needs. The tables only your app has, such as `projects` and `tasks`, go in `tables`. `better-supabase upgrade` writes them into one schema file next to the block files, with the scope column, row level security, grants, indexes and a pgTAP test, so they follow the same rules as the blocks around them. ```ts title="better-supabase.config.ts" import { defineConfig } from "better-supabase/config"; export default defineConfig({ blocks: ["memberships", "organizations", "crm"], tables: { projects: { columns: { name: { type: "text", nullable: false }, budget_minor: { type: "bigint", nullable: false, default: "0" }, currency: { type: "text", nullable: false, default: "'EUR'" }, owner_id: { type: "uuid", references: { table: "auth.users", onDelete: "set null" }, }, contact_id: { type: "uuid", references: { block: "crm", table: "contacts" }, }, }, }, tasks: { columns: { project_id: { type: "uuid", nullable: false, references: { table: "projects", onDelete: "cascade" }, }, title: { type: "text", nullable: false }, done: { type: "boolean", nullable: false, default: "false" }, }, }, drafts: { owner: "user", columns: { body: { type: "text" } } }, }, }); ``` [Create](https://app.bettersupabase.com/create) edits the same key on its canvas, and `init --preset` or `apply --preset` with its URL writes it into the config (see [init](/docs/cli/init#a-design-from-create)). Dragging a table onto itself adds `parent_id` with `on delete set null`. A second key to the same table gets a free name you can rename: `owner_id`, `assignee_id` and so on for `auth.users` (the scope can claim `user_id`), and `_2_id` for other tables. Create also starts without blocks: "Draw your own tables" adds a per-user table to an empty canvas. ## What each table gets [#what-each-table-gets] Every table lives in `public` and gets `id`, the scope column and, unless `timestamps: false`, `created_at` and `updated_at` with the `updated-at` trigger. These names are reserved, so a column can't take them. Columns keep the order you write them in. The `id` is a random `uuid` unless the table sets `id: "bigint"`, which makes it a number `generated by default as identity`; a reference to the table then needs a `bigint` column. | `owner` | Scope column | Who reaches a row | | ------------------- | -------------------------------------------------------------------------------- | --------------------------------------------------------------------- | | `inherit` (default) | follows `database.sql.owner`: the tenant column (`organization_id`) or `user_id` | per tenant, the members of the row's tenant; per user, the row's user | | `user` | `user_id`, referencing `auth.users`, defaulting to `auth.uid()` | the row's user, in any `database.sql.owner` | | `none` | none | every signed-in user reads; only the service role writes | A tenant-scoped table needs the `memberships` block. The tenant column follows `blocks.memberships.names.organization`, and the client sends its value on insert; the insert policy rejects a tenant the user isn't a member of. ## Columns [#columns] | Key | Values | | ------------ | ---------------------------------------------------------------------------------------------------------- | | `type` | `text`, `uuid`, `integer`, `bigint`, `numeric`, `boolean`, `timestamptz`, `date`, `jsonb`, `text[]` | | `nullable` | defaults to `true` | | `default` | `now()`, `gen_random_uuid()`, `auth.uid()`, `true`, `false`, a number, a `'quoted'` text, `'{}'` or `'[]'` | | `unique` | adds a unique constraint | | `references` | another app table, `auth.users`, or a block's table; `onDelete` defaults to `no action` | | `format` | what the column holds; see the next table | | `options` | 1 to 100 allowed values for a `text` (single select) or `text[]` (multi-select) column | | `min`, `max` | the smallest and largest value of a number, or the shortest and longest `text`, in characters | | `comment` | a description, written as `comment on column` | | `identity` | `true` on an `integer` or `bigint` column numbers rows: `generated by default as identity` | | `access` | `read` or `server`; see [Column access](#column-access) | | `encrypted` | `true` stores the value as ciphertext; see [Encrypted columns](#encrypted-columns) | | `format` | Type | What the database checks | | ----------- | -------------------- | --------------------------------------------- | | `email` | `text` | an `@` with text on both sides and a dot | | `url` | `text` | starts with `http://` or `https://` | | `rating` | `integer`, `bigint` | 1 to 5, unless `min` or `max` say otherwise | | `percent` | `integer`, `numeric` | 0 to 100, unless `min` or `max` say otherwise | | `phone` | `text` | nothing; a hint for forms and generators | | `long_text` | `text` | nothing; a hint for a multi-line input | | `color` | `text` | nothing; a hint for a color picker | | `currency` | `bigint` | nothing; marks an amount in minor units | Each check is a named constraint, `
__check`, and the pgTAP file checks that it exists. `FIELD_FORMATS` from `better-supabase/config` holds the same list with each format's types, check and input, and [custom fields](/docs/blocks/custom-fields#types) use the same names. A column takes either `options` or a `format`, not both. Keep money as a `bigint` of minor units next to a `text` currency column, as the blocks do: in Create, picking Currency renames the column to `_minor` and adds `_currency` with a length of 3. A reference to a block's table names the block's logical table (`{ block: "crm", table: "contacts" }`), so it follows the block's renames, and the block has to be in `blocks`. A name a block already uses, such as `tags` with the `tags` block, is rejected with the block's name. A reference between two tenant-scoped app tables, a table's reference to itself included, also checks the tenant: the target gets a unique index on `(organization_id, id)`, and the reference becomes a foreign key on `(organization_id, )`, so a row can't point at another tenant's row even when the caller knows its id. `onDelete: "set null"` clears only the reference column (`on delete set null ()`, Postgres 15 and later). The pgTAP file checks that the key covers both columns. ## Column access [#column-access] A column's `access` decides who reads and writes it, with Postgres column grants. Leave it out and signed-in users read and write the column within the row policies. Set `read` and they read it, but only server code with the service role writes it. Set `server` and they can't read or write it at all. ```ts title="better-supabase.config.ts" tables: { projects: { columns: { name: { type: "text", nullable: false }, budget: { type: "bigint", access: "read" }, internal_notes: { type: "text", access: "server" }, }, }, }, ``` Once a column sets `access`, `upgrade` revokes the table-wide grants from `authenticated` and grants `select` on the readable columns, and `insert` and `update` on the writable ones, by name. A query that names a hidden column fails with `42501` (permission denied), even with `where false`, because Postgres checks column grants while it plans the query. The `
_view` lists the readable columns instead of `t.*`, and the typed client leaves server columns out of a default select: `gen` marks them `selectable: false` and makes them optional in the row type. Select one by name with the service role client to read it. `tables` refuses a few combinations, so the generated SQL never needs a column the caller can't see: - A required column with `access` needs a `default`, because signed-in users can't fill it on insert. - A `server` column can't be the subject title, a search column, part of the subject URL or rank group, a rollup or lookup source, or in `events.include` or `subject.versions.columns`. A table with `versions: true` and a `server` column has to list `versions.columns` without it. - A table in `expose` sets its grants there, so its columns can't set `access`. The pgTAP file checks each column with `has_column_privilege` for `authenticated`, and that reading a `server` column and updating a restricted one fail with `42501`. In Create, the column inspector's Access field sets the key, and the Data tab's Preview as toggle shows the sample rows as a signed-in member or as the server. Tightening a column's access is a breaking change in the design diff. ADR 0051 explains why access uses grants rather than a view or policies. ## Encrypted columns [#encrypted-columns] With the [`field-encryption`](/docs/blocks/field-encryption) block installed, `encrypted: true` stores a column as ciphertext under the row's data key: the tenant's for a tenant table, the user's for `owner: "user"`, the app's for `owner: "none"`. Signed-in users write the plaintext as usual, and a trigger checks the column's format, options and bounds and then encrypts it before the row is stored. ```ts title="better-supabase.config.ts" tables: { client_notes: { columns: { client_name: { type: "text", nullable: false }, diagnosis: { type: "text", encrypted: true }, birth_date: { type: "date", encrypted: true }, }, auditReads: true, }, }, ``` The column is `text` in the database, and signed-in users can't select it, the same as a `server` column. They read it through `public.
_reveal(ids)`, which returns `id` and each encrypted column in its configured type, for the rows of `ids` the caller's scope reaches (at most 500 per call). It is a security invoker wrapper around the security definer function `better_supabase.
_reveal`, which PostgREST doesn't expose: ```ts const { data } = await supabase.rpc("client_notes_reveal", { ids }); // [{ id: "…", diagnosis: "asthma", birth_date: "1990-05-01" }] ``` `gen` types the column as its configured type, not as `text`, and marks it `selectable: false`. An encrypted column with `access: "server"` reveals only to the service role. Anon callers get `42501` (`APP_REVEAL_FORBIDDEN`), and more than 500 ids `22023` (`APP_REVEAL_TOO_MANY`). `auditReads: true` writes one audit entry per tenant for each `reveal` call, with the type `.viewed`, the category `read`, the acting member, the row ids and the column names, so a list of 200 rows costs one entry rather than 200. It needs the [`audit`](/docs/blocks/audit#read-audits) block. An encrypted column can't be unique, an identity, a reference or `text[]`, and it can't appear where the database has to read the value: `unique` and `indexes` keys, the subject title, URL, search and rank group, rollups, lookups, `events.include` or `subject.versions.columns`. Turning encryption on or off is a breaking change in the design diff. To encrypt rows that already hold plaintext, run `update
set = ` once after you install it: the trigger seals any value that isn't ciphertext yet. The [limits](/docs/blocks/field-encryption#limits) of the block apply. ## Relations [#relations] A column with `references` is a many-to-one link: many rows point at one row of the target. A table that references itself through a `parent_id` holds a tree. A many-to-many link is a table of its own with two references, each `onDelete: "cascade"`, and a composite `unique` on the pair, so the same two rows are linked once: ```ts title="better-supabase.config.ts" tables: { projects: { columns: { name: { type: "text", nullable: false } } }, labels: { columns: { name: { type: "text", nullable: false } } }, projects_labels: { unique: [["project_id", "label_id"]], columns: { project_id: { type: "uuid", nullable: false, references: { table: "projects", onDelete: "cascade" }, }, label_id: { type: "uuid", nullable: false, references: { table: "labels", onDelete: "cascade" }, }, }, }, folders: { columns: { name: { type: "text", nullable: false }, parent_id: { type: "uuid", references: { table: "folders", onDelete: "cascade" }, }, }, }, }, ``` In Create, connecting two tables offers "Many to many", which adds the junction table, and the Tree primitive adds a table with `parent_id`. Each edge shows its cardinality and its `onDelete` action. ```ts title="better-supabase.config.ts" tables: { contacts: { id: "bigint", comment: "People the team works with", unique: [["organization_id", "code"]], indexes: [["stage", "created_at"]], columns: { code: { type: "text", nullable: false, min: 2, max: 12 }, email: { type: "text", format: "email" }, stage: { type: "text", options: ["lead", "customer"] }, score: { type: "integer", format: "rating" }, number: { type: "bigint", identity: true }, }, }, }, ``` ## Table keys [#table-keys] | Key | Values | | ------------ | -------------------------------------------------------------------------------------------------------- | | `id` | `uuid` (default) or `bigint` | | `unique` | sets of columns that are unique together: `[["organization_id", "code"]]`, named `
__key` | | `indexes` | sets of columns indexed together, in order, named `
__idx` | | `comment` | a description, written as `comment on table` | | `owner` | `inherit`, `user` or `none`; see above | | `timestamps` | `false` drops `created_at`, `updated_at` and the trigger | A set can name any column the table has, including `id`, the scope column and the timestamps. A column a set names that the table doesn't have fails `upgrade` with the table and the column. ## Record features [#record-features] Give a table a `subject` and it becomes a subject type, the same as an entry in `subjects` (see [Subject registry](/docs/blocks/sql#subject-registry)). Comments, attachments, tags, versions, watchers, activity and search then reach its rows, because those blocks attach to any registered subject. On [Create](https://app.bettersupabase.com/create), the Features tab of a table turns each one on, adds its block and shows it on the table. ```ts title="better-supabase.config.ts" tables: { projects: { columns: { name: { type: "text", nullable: false } }, subject: { type: "project", title: "name", url: "/projects/{id}", search: {}, rank: true, softDelete: true, }, }, }, ``` | Key | What it does | | ------------- | -------------------------------------------------------------------------------------------------- | | `type` | the subject type, which must not clash with an entry in `subjects` | | `title` | the column `subject_title` returns | | `url` | the path `subject_url` returns, with `{id}` replaced | | `search` | indexes rows in the `search` block; `title` and `body` name the columns, `{}` uses the title | | `permissions` | the permission per action, as in `subjects` | | `rank` | adds a `rank` column for manual order (`text collate "C"`, ADR 0021); `{ group }` ranks per column | | `softDelete` | adds a `deleted_at` column, which hides the row from subject readers and search | | `versions` | keeps a version on every change; see [History](#history) | The subject's tenant column is the table's tenant column when the table is scoped to the tenant, and none otherwise. A user column named `rank` or `deleted_at` fails `upgrade` when the subject adds that column. Search triggers for app tables are written into the app tables file, which runs after the `search` block. ## Events [#events] Set `events` and every insert, update and delete on the table writes an event to the outbox in the same transaction, so writes through PostgREST, the SQL editor or another service emit it too. The table needs the [outbox](/docs/blocks/outbox) block. On [Create](https://app.bettersupabase.com/create), the Emit events switch in a table's Features tab sets it and adds the block. ```ts title="better-supabase.config.ts" tables: { tasks: { columns: { title: { type: "text", nullable: false }, status: { type: "text" }, }, subject: { type: "task", title: "title" }, events: { on: ["created", "updated"], include: ["title", "status"] }, }, }, ``` `events: true` emits all three kinds with no extra columns. The event type is the subject type, or the table name, followed by `.created`, `.updated` or `.deleted`. The payload is an `AppTableEventData`: | Field | Value | | ---------------- | --------------------------------------------------------------------- | | `id` | the row's id | | `organizationId` | the tenant column, when the table is scoped to the tenant | | `subjectType` | the subject type, when the table has a `subject` | | `subjectId` | the row's id as text, when the table has a `subject` | | `changed` | on an update, the columns whose value changed | | the `include`d | each column `include` names, with its new value (old value on delete) | An update that changes only `updated_at` emits nothing. `gen blocks` writes `appTableEvents` into `lib/supabase/blocks.ts`, a typed event map you pass to `createOutbox`, and `appTableEventSchemas`, which documents them in the AsyncAPI document through `defineApi({ events: { blocks: appTableEventSchemas } })`. To react to the events, consume them with [`createRuleSink`](/docs/blocks/automations#run-rules-from-events); an automation's `workflow` action [starts a workflow](/docs/blocks/workflow-editor#start-from-outbox-events). The decision is ADR 0048. ## History [#history] Set `subject.versions` and the [versions](/docs/blocks/versions) block keeps a version of the row each time one of its columns changes, through a trigger on the table. The table needs the versions block; `upgrade` names it when it is missing. On [Create](https://app.bettersupabase.com/create), the Versions feature of a table sets it. ```ts title="better-supabase.config.ts" tables: { projects: { columns: { name: { type: "text", nullable: false }, budget_minor: { type: "bigint", nullable: false, default: "0" }, }, subject: { type: "project", title: "name", versions: { columns: ["name", "budget_minor"] }, }, }, }, ``` `versions: true` keeps every column the config declares. `columns` narrows the list, and `auto: false` leaves out the trigger, so versions are kept only when the app calls `versions.snapshot`. `versions.restoreFields(id, fields)` writes some fields of a version back and leaves the rest of the row as it is. The [record history](/recipes) recipe lists the versions with the fields each one changed and a restore per field. ## Imports and bulk edits [#imports-and-bulk-edits] `appTableImport` from `better-supabase/blocks/imports` imports a CSV into an app table and updates the rows that already exist, matched on one of the table's `unique` sets. See [imports](/docs/blocks/imports#upsert-into-an-app-table). With the [bulk](/docs/blocks/bulk) block installed, `upgrade` also writes `app_table_set`, which the built-in `appTables.set` bulk action calls to set columns on the selected rows of any app table that is a subject. ## Rollups and lookups [#rollups-and-lookups] A rollup aggregates the rows of another app table that reference this one, such as a project's open tasks. A lookup reads a column of the row a reference points at, such as a task's project name. Both are built from structured keys, never from SQL, so a design link can't carry a query (ADR 0044). ```ts title="better-supabase.config.ts" tables: { projects: { columns: { name: { type: "text", nullable: false } }, rollups: { open_tasks: { table: "tasks", via: "project_id", aggregate: "count", where: { column: "status", equals: "todo" }, }, next_due_on: { table: "tasks", via: "project_id", aggregate: "min", column: "due_on", }, }, }, tasks: { columns: { project_id: { type: "uuid", references: { table: "projects" } }, status: { type: "text", options: ["todo", "done"] }, due_on: { type: "date" }, }, lookups: { project_name: { via: "project_id", column: "name" } }, }, }, ``` | Key | What it does | | --------------------- | ------------------------------------------------------------------------------------------------------------------------- | | `rollups.*.table` | the app table to aggregate | | `rollups.*.via` | the column of that table that references this one | | `rollups.*.aggregate` | `count`, `sum`, `min`, `max` or `avg` | | `rollups.*.column` | the column to aggregate: a number for `sum` and `avg`, a number, date, time or text for `min` and `max`, none for `count` | | `rollups.*.where` | `{ column, equals }`: only the rows whose select column (a text column with `options`) equals one option | | `lookups.*.via` | a column of this table that references another app table | | `lookups.*.column` | the column of the referenced row to read, including `created_at` and `updated_at` | `upgrade` writes `public.
_view`: every column of the table, then one column per rollup and lookup. The view is `security_invoker`, so it reads the table and the linked tables with the caller's row level security, and a rollup counts only the rows the caller can read. `authenticated` can read it and `anon` can't. `sum` returns 0 when there are no rows, and the other aggregates return null. `count` and `sum` over `integer` or `bigint` return `bigint`. `better-supabase gen` reads the view as a read-only model, so `db.projectsView.list()` lists projects with their rollups and the repository has no insert or update. Write through the table, then read through the view: ```ts const projects = await db.projectsView .list({ orderBy: { nextDueOn: "asc" } }) .orThrow(); ``` A name already used by a column of the table, or a table named `
_view`, fails `upgrade`. Each rollup runs one subquery per row read, using the index on the reference column, so page long lists. Adding or removing a rollup or lookup is a safe change on the Changes tab, since the view keeps no rows. For a formula these keys can't express, write your own view. ## The files [#the-files] | File | What it holds | | -------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `supabase/schemas/900_better_supabase_app_tables.sql` | the tables, the columns (`add column if not exists`), RLS, policies, grants, checks, unique keys, indexes, comments, triggers and the `
_view` of each table with rollups or lookups | | `supabase/tests/900_better_supabase_app_tables.test.sql` | pgTAP: each table exists, has RLS, its policies, checks, unique keys and indexes, and a stranger can't read or insert outside their scope; each view runs as the invoker and shows a stranger no rows | `upgrade --check` fails when the files are stale, and removing the last app table removes them. A table listed in `expose` keeps the grants `expose` gives it. ## Screens [#screens] better-supabase writes no pages. Build a table's screens on its generated data hooks (`useTasks`, `useTask`, see [data hooks](/docs/frontend/data-hooks)) and start from the `record-*` [UI recipes](/docs/registry/ui-recipes#app-tables), which build them on ReUI: a grid with saved views, a form, a board, a calendar, a timeline and a Gantt chart. ## Example [#example] The Next.js example declares three app tables in `apps/examples/nextjs/better-supabase.config.ts`: `projects`, scoped to the organization with a label from the `tags` block, `tasks` in those projects, and `saved_views`, scoped to the user. `/projects` shows the tasks in the `record-*` screens. `GET /api/projects` (`src/app/api/projects/route.ts`) lists the organization's projects, and `tests/lib/app-tables.test-d.ts` checks the generated repositories. The example writes its pgTAP test to the fixture's `supabase/tests/database` through `database.sql.testsDir`. ## Limits [#limits] The file adds new columns with `add column if not exists`, so under the `migrations` workflow a column you change or drop needs a migration of its own; pg-delta and migra diff the file and handle it.