Declarative database schema (Supabase + pg-toolbelt)

​

Goals

• Single source of truth for DDL: supabase/schemas/**/*.sql describes the desired database shape (tables, types, routines, policies where appropriate).
• Reproducible promotion: supabase/migrations/*.sql remains what supabase db reset, CI, and remotes apply. For normal DDL, those files are produced by supabase db diff, not by hand.
• Tooling: @supabase/pg-delta and @supabase/pg-topo from pg-toolbelt assist with export, optional apply-to-empty-DB experiments, and ordering diagnostics. They are not a substitute for supabase db diff in this repo.

Backfill status (2026-05-29): the existing schema has not yet been bulk-exported into supabase/schemas/** — pg-delta’s declarative export does not scale to the full ~1256-table schema. Until a scalable backfill lands, migrations (05-28 baseline + forward) are the operative source of truth; author new DDL declaratively and let supabase db diff generate migrations.

​

Directory layout

supabase/schemas/
  README.md                 # human guidance (this repo)
  schemas/
    public/
      tables/               # one file per table (+ related indexes/triggers/policies co-located)
      functions/
      views/
      types/
      ...
  cluster/
    roles.sql
    extensions/
      ...

​

Environment variables

​

npm scripts (root package.json)

Alpha warning: @supabase/pg-delta is pre-1.0. Pin versions in package.json; use export/lint locally/CI, avoid declarative-apply against production.

​

First-time bootstrap (populate supabase/schemas/)

npx supabase start
npx supabase db reset
npm run db:schemas:export
git add supabase/schemas

​

Day-to-day DDL workflow

1. Change DDL in supabase/schemas/ (not in supabase/migrations/).
2. Optional: npm run db:schemas:lint — catches parse errors / unresolved dependencies / cycles via pg-topo.
3. Stop local Supabase: npx supabase stop (required before db diff in this workflow).
4. Generate migration: npx supabase db diff -f <snake_case_description>
   Shortcut: npm run db:schemas:diff -- -f <snake_case_description>
5. Review the generated SQL under supabase/migrations/ — watch for unintended DROPs.
6. Start + reset: npx supabase start && npx supabase db reset
7. Validate: npm run validate-migration -- --latest
8. Test RLS: npm run test:rls (and other suites per change risk).

​

Bounded declarative pilot (2026-06-09)

• Tooling decision: the pilot uses native supabase db diff --use-pg-delta — confirmed supported on the project’s Supabase CLI (the --use-pg-delta flag is present in supabase db diff). The standalone @supabase/pg-delta wrapper (npm run db:schemas:*) remains a proven, CLI-version-independent fallback.
• Scope: NEW tables in the FA core only (non-regulated). Author the table’s DDL as supabase/schemas/fa/<table>.sql, generate the migration via supabase db diff --use-pg-delta -f <name>, review for unintended DROPs, apply locally with supabase migration up, then run npm run validate-migration -- --latest + npm run test:rls. Migrations remain the promotion source of truth.
• Excluded: regulated cores (CL/PM/HR/RH/GR) and any bulk export of the existing ~1256-table schema (pg-delta export does not scale to it).
• Round-trip validation: COMPLETE (2026-06-09). The pg-delta engine was validated end-to-end against an isolated throwaway Postgres 17 (no shared stack touched): pgdelta catalog-export snapshots the current schema → a new fa_pilot_demo table is authored → pgdelta plan --source <snapshot.json> --target <db> --format sql --integration supabase emits the migration. Result (4/4 checks): a clean additive migration (CREATE TABLE public.fa_pilot_demo + PK, annotated -- Risk: safe) that does not drop the pre-existing fa_existing table, applies cleanly to a fresh copy of the prior state, and converges (re-diff after apply is empty). No coverage gaps for basic table DDL (uuid PK, columns, defaults).
  • Recommended pattern for this repo’s partial-declarative reality: pgdelta catalog-export the current state → apply the change to a DB → pgdelta plan --source <snapshot.json> --target <db> for the incremental migration. Both sides are complete states, so no spurious drops.
  • Caveat (the documented scaling limit): supabase db diff --use-pg-delta compares the WHOLE supabase/schemas/ desired state against the migration shadow — with an incomplete declarative tree it will propose dropping undeclared objects. Until the full schema is exported (it does not scale to ~1256 tables), prefer the snapshot→plan pattern above, or keep a complete declarative tree.
  • ⚠️ Scaling spike result (2026-06-09) — the pilot path is gated on a performance fix. A measured spike against the live 1282-table local schema (not the 2-table throwaway the round-trip above used) found that every pg-delta entry point hangs on the full catalog read: pgdelta catalog-export did not finish in ~9.5 min, and --filter-scoped reads (export and catalog-export) did not finish in 5–6 min — the filter is applied after the read, so it cannot prune the cost. Because supabase db diff --use-pg-delta drives the same engine over the same catalog, the “first real pilot step” is expected to hang the same way at this schema size. Do not treat db diff --use-pg-delta as the day-to-day authoring path yet; keep authoring new DDL as hand-reviewed migrations. See docs/superpowers/specs/2026-06-09-declarative-pgdelta-export-scaling-spike.md. Re-spike gate: catalog-export on a ~1300-table DB completing in < 60s.
• Read-only drift report: the catalog-drift-report job in supabase-drift-check.yml is non-blocking and complements the existing migration-ledger drift check; it also refreshes reports/db/catalog-snapshot.prod.json.
• Never declarative-apply against a hosted remote.

​

Lovable-originated schema changes

1. Run npm run db:pull-from-dev -- --name <descriptive_slug> to generate a migration from hosted DEV changes.
2. Review the generated migration under supabase/migrations/ for safety, lane correctness, and constitution requirements.
3. If local Supabase is available, replay locally (npx supabase start && npx supabase db reset) and run npm run db:schemas:export so supabase/schemas/ stays aligned.
4. Run npm run validate-migration -- --latest and npm run test:rls before merging.

​

When not to rely on declarative + db diff alone

​

Rollbacks

1. Revert or edit the declarative .sql to the prior desired state.
2. npx supabase stop
3. npx supabase db diff -f <describe_rollback>
4. Review carefully for data loss (DROP COLUMN, narrowing types, etc.).
5. Apply locally (db reset) and run tests before opening a PR.

​

Relationship to constitution / platform rules

• §5 Migrations only — still true: production schema changes ship as supabase/migrations/*.sql.
• RLS + tenant isolation — declarative SQL must follow the same patterns as migrations (SECURITY DEFINER helpers, no recursive policies, organization_id on business tables). See DATABASE_DEVELOPMENT_GUIDE.md and .cursor/rules/database-patterns.mdc.
• PF-96 / multi-tenant literals — unchanged; declarative workflow does not relax jurisdiction or tenant-configurability rules.

​

See also

• SUPABASE_CLI_LOCAL_WORKFLOW.md — CLI-first operations + declarative section
• MIGRATION_LANES.md — CI migration lane policy
• supabase/schemas/README.md — short operator checklist