Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt

Use this file to discover all available pages before exploring further.

Version: 1.0.0
Last Updated: 2026-04-24
Audience: Developers and AI agents working on Postgres schema for Encore Health OS. This document is the canonical reference for maintaining DDL under supabase/schemas/ and generating versioned migrations under supabase/migrations/ with the Supabase CLI.

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

​
Directory layout

After a successful npm run db:schemas:export, pg-delta typically emits a tree similar to: 
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/
      ...
File ordering: Supabase applies declarative SQL in lexicographic path order when loading from a flat layout; pg-delta already groups by object type. If you split DDL manually, name files so dependencies are satisfied (e.g. base tables before foreign keys that reference them). @supabase/pg-topo validates statement-level ordering when you run npm run db:schemas:lint. Column ordering: When altering existing tables in exported files, append new columns at the end of the table definition where possible to reduce noisy diffs on re-export.

​
Environment variables

VariablePurposeDefault
SUPABASE_LOCAL_DB_URLPostgres URL for pgdelta targets/sourcespostgresql://postgres:postgres@127.0.0.1:54322/postgres
SUPABASE_SCHEMAS_DIRDeclarative schema root<repo>/supabase/schemas
SUPABASE_CATALOG_EXPORT_PATHOutput for npm run db:catalog:export<repo>/reports/db/catalog-snapshot.json

​
npm scripts (root package.json)

ScriptWhat it runs
npm run db:schemas:exportpgdelta declarative export --integration supabase → refresh supabase/schemas/ from a running local DB
npm run db:schemas:applypgdelta declarative apply — applies declarative tree to a target DB (use for disposable / empty DBs only)
npm run db:schemas:planpgdelta plan … — forward args (see tsx scripts/database/pgdelta-run.ts plan --help via npx pgdelta plan --help)
npm run db:catalog:exportpgdelta catalog-export — JSON catalog snapshot for offline diffs
npm run db:catalog:remote:dev / npm run db:catalog:remote:prodExport read-only hosted dev/prod catalog snapshots from Postgres system catalogs
npm run db:schemas:validate / npm run db:schemas:lint@supabase/pg-topo static analysis over supabase/schemas/ (skips if no .sql files yet)
npm run db:schemas:diffsupabase stop then forwards to supabase db diff — pass -- -f <name> [flags]
npm run db:docs:generateGenerate schema markdown docs from catalog snapshot or migration inventory fallback
npm run db:docs:generate:dev / npm run db:docs:generate:prodGenerate environment-specific schema docs from hosted catalog snapshots
npm run db:docs:checkVerify generated schema docs are up-to-date
npm run db:docs:refreshExport catalog then regenerate schema docs
npm run db:docs:refresh:allExport hosted dev and prod catalogs, then regenerate both environment-specific doc sets
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/)

When Docker and the local Supabase DB port (54322 per supabase/config.toml) are available: 
npx supabase start
npx supabase db reset
npm run db:schemas:export
git add supabase/schemas
Commit the generated .sql tree. Until then, only supabase/schemas/README.md may be present — db:schemas:lint intentionally no-ops when there are zero .sql files.

​
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).
Continue to follow MIGRATION_LANES.md: new migrations with timestamp ≥ cutoff must be DDL-only (schema lane) unless explicitly marked / named for system-defaults DML.

​
Lovable-originated schema changes

Lovable can apply DDL directly to hosted DEV, which bypasses local migration authoring. Treat this as a drift-capture workflow:
  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.
supabase/migrations/*.sql remains the promotion source of truth; declarative files are an audit/authoring surface, not a production deployment shortcut.

​
When not to rely on declarative + db diff alone

Keep explicit migrations (hand-authored or carefully reviewed) for cases that diff tools handle poorly (aligns with Supabase / migra limitations):
TopicGuidance
DMLUse idempotent migrations in the system-defaults lane (-- @lane: system-defaults or filename convention). Never tenant/test data.
View ownership / grantsCapture in a dedicated migration; verify security behavior (views bypass RLS by default in Postgres unless security_invoker).
Materialized viewsPrefer explicit migration; refresh schedules may need pg_cron + vault patterns.
RLS ALTER POLICYOften safer to DROP POLICY + CREATE POLICY in a migration; ensure init-plan pattern (select auth.uid()) per db:check:rls-initplan.
CommentsCOMMENT ON statements in migrations if you need them versioned.
PartitionsFor active tables, use ADR-020 two-phase pattern (phase-1 shadow + sync, phase-2 cutover); treat as migration-authored unless proven stable in your diff workflow.
PublicationLogical replication / CDC workflow changes; treat as migration-authored unless proven stable in your diff workflow.
DomainCustom data type definitions; treat as migration-authored unless proven stable in your diff workflow.

​
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