App tables

Blocks cover the tables every app needs. The tables only your app has, such as projects and tasks, go in appTables. better-supabase sql sync 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: ["tenant", "organizations", "crm"],
  appTables: {
    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: { scope: "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).

What each table getsCopy Anchor Link

Every table lives in public and gets id (a uuid primary key), 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.

A tenant-scoped table needs the tenant block. The tenant column follows sql.naming.tenant, and the client sends its value on insert; the insert policy rejects a tenant the user isn't a member of.

ColumnsCopy Anchor Link

Keep money as a bigint of minor units next to a text currency column, as the blocks do. 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.

The filesCopy Anchor Link

sql sync --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.

ExampleCopy Anchor Link

The Next.js example declares two app tables in apps/examples/nextjs/better-supabase.config.ts: projects, scoped to the organization with a label from the tags block, and saved_views, scoped to the user. 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 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. A foreign key between two tenant-scoped app tables doesn't check that both rows share a tenant, so validate that in the app when it matters.