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

# Supabase migration lanes (prod promotion)

> Version: 1.0.1 Last Updated: 2026-04-24 Related: BRANCHING_CI_SPIKE.md (CLI/API options for Branching + CI)

**Version:** 1.0.1\
**Last Updated:** 2026-04-24\
**Related:** [`BRANCHING_CI_SPIKE.md`](./BRANCHING_CI_SPIKE.md) (CLI/API options for Branching + CI)

This repo enforces **two lanes** for new SQL under `supabase/migrations/` so production receives **DDL + idempotent system catalogs**, not ad-hoc tenant/test data.

## Lanes

### 1. `schema` (default)

* **Use for:** tables, indexes, constraints, RLS policies, functions, triggers, views, extensions, cron wiring that uses **vault** / `current_setting` — no tenant data.
* **Rules:** After migration timestamp cutoff (see below), **no top-level** `INSERT` / `UPDATE` / `DELETE`. Statements inside `$$ … $$` function bodies are ignored for this rule.
* **Marking:** No header required (default lane).
* **Partition safety:** For active high-volume tables, phase-1 partition migrations (shadow table + sync trigger + helper functions) belong in `schema` lane; phase-2 cutover must be promoted separately with runbook evidence (ADR-020).

### 2. `system-defaults`

* **Use for:** global catalogs the product expects in every environment (`pf_module_permissions`, jurisdiction profiles, notification templates, etc.) — always **idempotent**.
* **Rules:**
  * First line of file: `-- @lane: system-defaults` **or** filename contains `system_defaults` / `system-defaults`.
  * Every `INSERT` must include **`ON CONFLICT …`** and/or **`WHERE NOT EXISTS` / `NOT EXISTS`** in the same statement (rough static check in CI).
  * `UPDATE` / `DELETE` for catalog corrections are allowed in this lane (e.g. renaming seeded display labels).

## Cutoff (strict vs patterns-only)

CI runs `scripts/database/check-migration-lane.ts` on **changed** migration files only.

* **All scanned files:** hard fails on hardcoded dev project host (`zkgxozahyczcnzpwhbbf.supabase.co`), JWT-like literals (`eyJ…`), and known dev-only bootstrap UUIDs.
* **Strict DML rules** (schema vs system-defaults) apply only when the migration filename’s timestamp prefix is **≥ `MIGRATION_LANE_STRICT_AFTER`** (default `20260424160000`). Older files in the same PR are still checked for banned patterns only.

Override locally:

```bash theme={null}
MIGRATION_LANE_STRICT_AFTER=20990101000000 npx tsx scripts/database/check-migration-lane.ts --since=origin/prod
```

## What does **not** belong in migrations

* Staging/demo **organizations, users, employees, residents** — use `supabase/seeds/` and SQL Editor / seed runners for non-prod only. See supabase/seeds/README.md.
* **Secrets** in SQL (anon JWT, service keys). Use `vault.decrypted_secrets` and Edge Function secrets.

### Seed-style rows with foreign keys

Migrations that insert **test fixtures** referencing rows that exist only after full seed runs (e.g. `pf_profiles` as `pf_documents.author`) must guard inserts—typically `IF EXISTS (SELECT 1 FROM …)` inside a `DO $$ … $$` block—so `supabase db diff --linked` shadow databases and CI drift gates do not fail with FK violations. Example: `20260313162459_5b58857b-e8d5-4ef0-bd40-ec6a2d305736.sql` (PF-11 Phase 5 verification seed).

## Commands

```bash theme={null}
# Migrations changed since a git ref (typical: PR base SHA)
npx tsx scripts/database/check-migration-lane.ts --since=<sha>

# Migrations changed between two commits (deploy hooks)
npx tsx scripts/database/check-migration-lane.ts --ref-range=<from>..<to>

# Full-repo audit (slow; legacy files use patterns-only unless strict cutoff matches)
npx tsx scripts/database/check-migration-lane.ts --full-repo-audit

# Optional: write Markdown report (used by promote PR + migration-lane-audit workflows)
npx tsx scripts/database/check-migration-lane.ts --since=<sha> --emit-markdown=reports/migration-lane-report.md
```

npm: `npm run db:check:migration-lane -- --since=<sha>`

**Note:** Invoking `check-migration-lane.ts` with **no** `--since`, `--ref-range`, or `--full-repo-audit` prints a skip message and exits **0** (no files scanned). Custom jobs must pass one of those flags.

### Legacy inventory (retrofit lane headers)

One-off or periodic reports for classifying historical migrations (DDL-only vs system-defaults vs ambiguous):

```bash theme={null}
npm run db:report:migration-lane-inventory
```

Writes `reports/db/migration-lane-inventory.json` and `reports/db/MIGRATION_LANE_INVENTORY.md`. To apply **high-confidence** `-- @lane: system-defaults` first-line headers without changing SQL semantics:

```bash theme={null}
npm run db:apply:migration-lane-headers -- --dry-run
npm run db:apply:migration-lane-headers -- --apply
# Optional: also apply medium-confidence rows (see script help)
npm run db:apply:migration-lane-headers -- --apply --include-medium
```

### Release PR summary (git range + Dashboard links)

Used by [`.github/workflows/supabase-promote-pr.yml`](../../../.github/workflows/supabase-promote-pr.yml): lists changed migration paths vs the PR base SHA, links to Supabase Dashboard for `srcaoozjkrughebmbvfb`, and optionally appends a **redacted** unified diff (`eyJ…`-style tokens → `[REDACTED_JWT]`).

```bash theme={null}
npx tsx scripts/supabase/release-pr-db-summary.ts --base=<sha> --out=reports/release-db-summary.md
npx tsx scripts/supabase/release-pr-db-summary.ts --base=<sha> --out=reports/release-db-summary-artifact.md --with-diff
```

### Phased strictness (cutoff)

1. **Today:** strict lane rules apply to migrations with filename timestamp ≥ `MIGRATION_LANE_STRICT_AFTER` (default `20260424160000`); older files get banned-pattern checks when touched, or full-repo patterns-only under the audit workflow below.
2. **Phase B:** [`.github/workflows/migration-lane-audit.yml`](../../../.github/workflows/migration-lane-audit.yml) runs `--full-repo-audit` on a schedule (and `workflow_dispatch`), uploads a Markdown artifact, and **does not block** merges: the scan step uses `continue-on-error: true`, so the workflow can stay **green** while the artifact still lists violations (informational triage).
3. **Later:** lower `MIGRATION_LANE_STRICT_AFTER` in steps only after inventory shows the target slice is clean; optional end state is strict on all files once headers and catalog DML are aligned.

## CI wiring

| Trigger                                                 | Workflow                                                                                                                                                                                                             |
| ------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| PR → `production` (migrations touched)                  | [`supabase-promote-pr.yml`](../../../.github/workflows/supabase-promote-pr.yml) — lane report + **Release DB summary** (Dashboard links, changed migration list) + redacted diff **artifact**; single sticky comment |
| PR → `development` or `production` (migrations touched) | [`db-migration-guard.yml`](../../../.github/workflows/db-migration-guard.yml) — RLS init-plan, FK indexes, and migration lane checks run on every matching PR                                                        |
| Push → `production`                                     | [`supabase-deploy-prod.yml`](../../../.github/workflows/supabase-deploy-prod.yml) — `db push` (skippable via `skip_db_push` on manual dispatch), selective function deploy, post-link audit                          |
| Schedule / manual                                       | [`migration-lane-audit.yml`](../../../.github/workflows/migration-lane-audit.yml) — full-repo informational audit (`continue-on-error` on scan; see Phase B above)                                                   |

See also [SUPABASE\_MULTI\_ENV\_SETUP.md](./SUPABASE_MULTI_ENV_SETUP.md).