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

# Declarative database schema (Supabase + pg-toolbelt)

> Version: 1.0.0 Last Updated: 2026-04-24 Audience: Developers and AI agents working on Postgres schema for Encore Health OS.

**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/`](../../../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`](https://www.npmjs.com/package/@supabase/pg-delta) and [`@supabase/pg-topo`](https://www.npmjs.com/package/@supabase/pg-topo) from [pg-toolbelt](https://github.com/supabase/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:

```text theme={null}
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

| Variable                       | Purpose                                    | Default                                                   |
| ------------------------------ | ------------------------------------------ | --------------------------------------------------------- |
| `SUPABASE_LOCAL_DB_URL`        | Postgres URL for `pgdelta` targets/sources | `postgresql://postgres:postgres@127.0.0.1:54322/postgres` |
| `SUPABASE_SCHEMAS_DIR`         | Declarative schema root                    | `<repo>/supabase/schemas`                                 |
| `SUPABASE_CATALOG_EXPORT_PATH` | Output for `npm run db:catalog:export`     | `<repo>/reports/db/catalog-snapshot.json`                 |

***

## npm scripts (root `package.json`)

| Script                                                             | What it runs                                                                                                          |
| ------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------- |
| `npm run db:schemas:export`                                        | `pgdelta declarative export --integration supabase` → refresh `supabase/schemas/` from a **running** local DB         |
| `npm run db:schemas:apply`                                         | `pgdelta declarative apply` — applies declarative tree to a target DB (use for disposable / empty DBs only)           |
| `npm run db:schemas:plan`                                          | `pgdelta plan …` — forward args (see `tsx scripts/database/pgdelta-run.ts plan --help` via `npx pgdelta plan --help`) |
| `npm run db:catalog:export`                                        | `pgdelta catalog-export` — JSON catalog snapshot for offline diffs                                                    |
| `npm run db:catalog:remote:dev` / `npm run db:catalog:remote:prod` | Export 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:diff`                                          | `supabase stop` then forwards to `supabase db diff` — pass `-- -f <name> [flags]`                                     |
| `npm run db:docs:generate`                                         | Generate schema markdown docs from catalog snapshot or migration inventory fallback                                   |
| `npm run db:docs:generate:dev` / `npm run db:docs:generate:prod`   | Generate environment-specific schema docs from hosted catalog snapshots                                               |
| `npm run db:docs:check`                                            | Verify generated schema docs are up-to-date                                                                           |
| `npm run db:docs:refresh`                                          | Export catalog then regenerate schema docs                                                                            |
| `npm run db:docs:refresh:all`                                      | Export 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:

```bash theme={null}
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 `DROP`s.
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](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):

| Topic                       | Guidance                                                                                                                                                           |
| --------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **DML**                     | Use idempotent migrations in the `system-defaults` lane (`-- @lane: system-defaults` or filename convention). Never tenant/test data.                              |
| **View ownership / grants** | Capture in a dedicated migration; verify security behavior (views bypass RLS by default in Postgres unless `security_invoker`).                                    |
| **Materialized views**      | Prefer explicit migration; refresh schedules may need `pg_cron` + vault patterns.                                                                                  |
| **RLS `ALTER POLICY`**      | Often safer to `DROP POLICY` + `CREATE POLICY` in a migration; ensure init-plan pattern `(select auth.uid())` per `db:check:rls-initplan`.                         |
| **Comments**                | `COMMENT ON` statements in migrations if you need them versioned.                                                                                                  |
| **Partitions**              | For 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. |
| **Publication**             | Logical replication / CDC workflow changes; treat as migration-authored unless proven stable in your diff workflow.                                                |
| **Domain**                  | Custom 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](../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](SUPABASE_CLI_LOCAL_WORKFLOW.md) — CLI-first operations + declarative section
* [MIGRATION\_LANES.md](MIGRATION_LANES.md) — CI migration lane policy
* supabase/schemas/README.md — short operator checklist