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’s workflow_dispatch (manual) rather than a PR gate, because a from-scratch apply does not yet complete. Running it surfaced two distinct classes of latent issue in the first ~46 of 202 migrations:

  1. Invalid SQL — 20250222…_004_multi_tenancy.sql had ALTER COLUMN tenant_id SET DEFAULT (SELECT …). A column DEFAULT can’t be a subquery (Postgres rejects it in every version), so it never ran in prod either. Fixed: pin the default tenant to its real prod id (2de55acf-…) and use that literal as the default — faithful to prod and valid from scratch.
  2. Cross-migration-set dependency — 20260326…_002_music_scores_lyrics.sql (main set) references music_scores, a table created in the separate apps/music/supabase/migrations set. The main set isn’t self-contained, so it can’t apply standalone. Not fixed — this is structural (which set owns which tables) and belongs to the reconciliation below.

Two unrelated breakages this early imply more are scattered through the remaining ~150 migrations. Closing the gap to a green PR gate is a deliberate reconciliation effort, not a spot-fix chain — see §2. Flip the trigger to pull_request once supabase db start applies everything clean.

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.