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.1
Last Updated: 2026-04-24
Related: 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: 
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

# 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): 
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: 
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
Used by .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]). 
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 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

TriggerWorkflow
PR → production (migrations touched)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 — RLS init-plan, FK indexes, and migration lane checks run on every matching PR
Push → productionsupabase-deploy-prod.yml — db push (skippable via skip_db_push on manual dispatch), selective function deploy, post-link audit
Schedule / manualmigration-lane-audit.yml — full-repo informational audit (continue-on-error on scan; see Phase B above)
See also SUPABASE_MULTI_ENV_SETUP.md.