Skip to content
AnglinAI Docs

Migration tracking & DB-test CI (#1327)

Two related problems, one now addressed in CI, one still requiring an ops step.

1. DB-test harness — built (manual trigger for now)

.github/workflows/db-tests.yml boots a fresh Postgres with the Supabase CLI (supabase db start, which reads supabase/migrations/ — the repo source of truth), then runs scripts/run-db-tests.sh over supabase/tests/*.test.sql.

It’s the first environment that can actually validate clean apply (needs Docker + the Supabase CLI, which CI has but local dev here did not). The #1325 rename (unique 14-digit version prefixes) fixed the version-collision that previously stopped a from-scratch apply after the first same-day migration — the CLI now applies migrations in correct order.

It now GATES PRs (issue #1998, 2026-08-24). A from-scratch apply (supabase db start) completes cleanly end-to-end and all supabase/tests/*.test.sql pass. The remaining latent issues surfaced on clean apply were fixed IN PLACE (prod-safe: prod won’t re-run already-recorded migrations, and every fix is idempotent so re-running is a no-op):

  1. Invalid SQL — 20250222…_004_multi_tenancy.sql ALTER COLUMN … SET DEFAULT (SELECT …) (a column DEFAULT can’t be a subquery). Fixed earlier by pinning the literal default tenant id.
  2. Cross-migration-set / ordering — 20260326…_002_music_scores_lyrics.sql sorts before …_043_music_scores.sql creates the table. Fixed: the ALTER is guarded to a no-op when the table is absent, and the lyrics column is created idempotently in …_043.
  3. Function-not-yet-created — 20260407…_064_security_advisor_fixes.sql pinned search_path on deduct_credits(uuid,integer), an overload not created until migration 070. Fixed: the ALTER FUNCTION block now skips functions that don’t yet exist.
  4. ON CONFLICT vs partial index — 20260517…_113_org_chart_annual_plan.sql used ON CONFLICT (slug) against a partial unique index. Fixed: spelled out the matching WHERE slug IS NOT NULL predicate.
  5. Storage-trigger delete — 20260605…_146_drop_vpat_source_file_url.sql deleted from storage.objects, blocked by the local storage trigger. Fixed: the deletes are wrapped and the trigger error swallowed (bucket is empty on a fresh DB anyway).
  6. Broken dollar-quote — 20260816…_249_workshop_category_and_trainings.sql nested $$ string literals inside a DO $$ block. Fixed: the block now uses a distinct $do$ tag.
  7. Duplicate version prefixes — 20260816000000 and 20260816020000 were each used by two files (PK collision in schema_migrations on fresh apply). Fixed: the later-committed file of each pair was renamed to the next free version (…000001 / …020001); both renamed files are idempotent (CREATE OR REPLACE / ADD COLUMN IF NOT EXISTS / guarded), so if prod’s ledger lacks the new version and re-applies it, it is a safe no-op.

The PR gate runs on changes under supabase/migrations/**, supabase/tests/**, supabase/seed.sql, supabase/config.toml, and the runner script. The runner uses psql -v ON_ERROR_STOP=1 and asserts on each test’s exit code — critical, because the tests print “ALL TESTS PASSED” from inside their own transaction even when a statement errored, so a naive psql -f would exit 0 on a broken test.

2. Prod migration tracking — still broken (ops step needed)

Finding (2026-06-27): Supabase’s hosted migration history is decoupled from the repo. Creating a fresh dev branch replays Supabase’s own recorded migrations, not the repo files — and it still records only the first migration under the old name (20250213_001_credits_system.sql), failing immediately afterward. So:

  • The renamed repo files (#1325) are the source of truth for supabase db */CI, but Supabase’s stored migration history predates the rename and is incomplete/colliding.
  • Prod schema itself is fine (applied out-of-band over time); it’s the ledger that’s wrong. There is no reliable automated record of which migrations are applied to prod — reconciliation today is the manual scripts/audit-migrations.py + scripts/migration-reconciliation-overlay.json (see migration-ledger-reconciliation.md).

Reconciliation plan (do deliberately, against prod — not blind)

  1. Snapshot the live prod schema (scripts/snapshot-prod-schema.sql via the Supabase MCP/psql) and run scripts/audit-migrations.py to get the current applied/not-applied verdict per (renamed) file.
  2. Re-baseline Supabase’s hosted migration history to match the repo: mark every already-applied migration as applied at its new version (supabase migration repair --status applied <version> for each), so the ledger lines up with supabase/migrations/.
  3. Confirm a fresh dev branch then comes up …HEALTHY with all migrations recorded (not MIGRATIONS_FAILED).
  4. From then on, treat supabase_migrations.schema_migrations as the source of truth and retire the manual overlay.

Given the historical migrations don’t faithfully reproduce prod (invalid SQL that never ran, cross-set dependencies — see §1), repairing all ~200 individually is costly and low-value. The cleaner path:

  1. pg_dump --schema-only the live prod DB → supabase/migrations/<ts>_baseline.sql.
  2. Move the existing 202 files to an archive/ (kept for history, not applied).
  3. The baseline becomes migration #1; new migrations stack on top. From-scratch apply == prod by construction, so the §1 harness can flip to a PR gate.
  4. Re-baseline the hosted ledger to the single baseline version.

This makes the ledger and from-scratch apply correct in one move instead of chasing latent issues through 200 files. Requires a prod schema dump (DB credentials / direct psql — not available via the MCP), so it’s an explicit ops task. The apps/music and other app-specific migration sets should be scoped to their own projects as part of this.

Until then, the §1 harness runs on demand (workflow_dispatch) and locally; it is not yet a PR gate.