App 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.

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 edits the same key on its canvas, and init --preset or apply --preset with its URL writes it into the config (see init). 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 <table>_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 getsCopy Anchor Link

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.

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.

ColumnsCopy Anchor Link

Each check is a named constraint, <table>_<column>_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 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 <name>_minor and adds <name>_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, <column>), 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 (<column>), Postgres 15 and later). The pgTAP file checks that the key covers both columns.

Column accessCopy Anchor Link

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.

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 <table>_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 columnsCopy Anchor Link

With the 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.

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.<table>_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.<table>_reveal, which PostgREST doesn't expose:

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 <subject type or table>.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 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 <table> set <column> = <column> once after you install it: the trigger seals any value that isn't ciphertext yet. The limits of the block apply.

RelationsCopy Anchor Link

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:

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.

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 keysCopy Anchor Link

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 featuresCopy Anchor Link

Give a table a subject and it becomes a subject type, the same as an entry in subjects (see Subject registry). Comments, attachments, tags, versions, watchers, activity and search then reach its rows, because those blocks attach to any registered subject. On Create, the Features tab of a table turns each one on, adds its block and shows it on the table.

tables: {
  projects: {
    columns: { name: { type: "text", nullable: false } },
    subject: {
      type: "project",
      title: "name",
      url: "/projects/{id}",
      search: {},
      rank: true,
      softDelete: true,
    },
  },
},

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.

EventsCopy Anchor Link

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 block. On Create, the Emit events switch in a table's Features tab sets it and adds the block.

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:

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; an automation's workflow action starts a workflow. The decision is ADR 0048.

HistoryCopy Anchor Link

Set subject.versions and the 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, the Versions feature of a table sets it.

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 recipe lists the versions with the fields each one changed and a restore per field.

Imports and bulk editsCopy Anchor Link

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. With the 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 lookupsCopy Anchor Link

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).

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" } },
  },
},

upgrade writes public.<table>_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:

const projects = await db.projectsView
  .list({ orderBy: { nextDueOn: "asc" } })
  .orThrow();

A name already used by a column of the table, or a table named <table>_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 filesCopy Anchor Link

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.

ScreensCopy Anchor Link

better-supabase writes no pages. Build a table's screens on its generated data hooks (useTasks, useTask, see data hooks) and start from the record-* UI recipes, which build them on ReUI: a grid with saved views, a form, a board, a calendar, a timeline and a Gantt chart.

ExampleCopy Anchor Link

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.

LimitsCopy Anchor Link

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.