Supabase migration lanes (prod promotion)

​

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)

• All scanned files: hard fails on a hardcoded Supabase project host — any of the retired (anuwknikgsijbameytzr.supabase.co, aximlomrwfuhctxjhrsf.supabase.co) or active (anuwknikgsijbameytzr.supabase.co dev2, aximlomrwfuhctxjhrsf.supabase.co prod2) refs — plus JWT-like literals (eyJ…) and known dev-only bootstrap UUIDs. Migrations must read connection details from vault.decrypted_secrets/env, never embed a project host.
• 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.

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.

​

Migrations-only-forward (direct-apply policy)

• Forward changes go through a migration file. New schema or system-default changes are added as a new timestamped migration; do not edit applied migrations.
• Any direct remote apply (Supabase MCP execute_sql, the SQL Editor, or psql against a live project) MUST be accompanied by an equivalent local migration file in the same change/PR, so the repo ledger and the database never diverge.
• supabase-drift-check.yml is the backstop — it links the project via the SUPABASE_{DEV,PROD}_PROJECT_ID secrets (which must point at dev2/prod2) and fails if the live schema drifts from the committed migrations. A red drift check means a direct apply was made without a matching migration.

​

Seed-style rows with foreign keys

​

Commands

# 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

​

Legacy inventory (retrofit lane headers)

npm run db:report:migration-lane-inventory

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)

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 (see accuracy note below — not currently present) 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

Accuracy note (2026-05-26): This table now lists only workflows that actually exist in .github/workflows/. Several previously-referenced workflows (supabase-promote-pr.yml, db-migration-guard.yml, migration-lane-audit.yml, supabase-bootstrap-prod.yml) were documented but never implemented and have been removed from this table. supabase/config.toml’s comment block still references some of these; it is Lovable-auto-generated (do-not-hand-edit), so correct it at the generator if it regenerates.

Author-time allocator (§5.8, 2026-06-09): mint a collision-resistant version with npm run -s db:migration:next-version (append a slug: npm run -s db:migration:next-version -- fa_add_thresholds → <version>_fa_add_thresholds.sql). It returns the next free opaque 14-digit version above the max of local + origin/development migrations (not date +%s raw), so concurrent worktrees are less likely to mint the same prefix. For the CLI-generated paths (supabase db diff -f / supabase migration new), which stamp their own wall-clock prefix, run npm run db:migration:reversion right after generating to pull the file above origin/development; .husky/pre-push runs db:migration:reversion:check so a below-base migration is caught before the PR. This is ergonomics only — the deploy-boundary guard below is the actual collision guarantee. When a collision does occur, follow the one blessed path in MIGRATION_COLLISION_RESOLUTION.md (npm run db:migration-versions:fix) rather than re-researching a bespoke fix. Concurrency caveat (2026-06-09): the migration version-collision guard in supabase-deploy-dev.yml / supabase-deploy-prod.yml reads the remote ledger then gates db push. supabase-realign.yml writes the same ledger (db push

• migration repair) under a separate concurrency group. Do not run supabase-realign concurrently with a deploy — the guard assumes no out-of-band ledger writes during a deploy. Re-baseline/realign during a quiet window only.

​

Current baseline (2026-05-28)

• 00000000000000_baseline_2026_05_28_ddl.sql — full schema (1225 tables, 4459 policies, RLS 100%).
• 00000000000001_baseline_2026_05_28_data.sql — global system-default catalogs (loaded with session_replication_role = replica).

​

Re-baseline cadence

• Trigger: when the active (non-archived) migration count exceeds ~150.
• Procedure: run Phase 1 of the realignment plan — replay the repo into a throwaway DB, diff vs dev, dump a cleaned baseline, archive the old files under supabase/migrations/_archive/, then supabase migration repair dev + prod to the new baseline.
• Churn policy: the pre-commit filename lint (scripts/database/lint-migration-filename.ts) blocks new UUID-suffix names for human commits, but Lovable’s bot bypasses the local pre-commit hook, so UUID-named migrations still arrive. Rather than fight this, tolerate them between baselines and fold them into the next baseline. The enforced gate is apply-cleanliness (supabase-ci.yml) + drift visibility (supabase-drift-check.yml), not naming.