p24-infra Docs

supabase-per-repo-migration-ledger

7 min read

Playbook: Per-repo Supabase migration ledger (shared-project standard)

Status: standard as of #5653 (design brainstorm + decisions in #5645 → et-operational-platform#1500). Applies to: every repo that writes schema migrations against the shared Supabase project mwkqmgadqnkkihjdeqsi — currently p24-infra, et-operational-platform, whatsup-android-chat-puller.

The rule (one line)

One shared Supabase project, but each repo tracks its own applied migrations in its own dedicated ledger table and applies them with its own small idempotent runner. supabase db push and the apply_migration MCP tool are BANNED for this project.

Why

Supabase project mwkqmgadqnkkihjdeqsi is intentionally shared across repos (“1 project, separated parts with their own repos” — by design, not a mistake to undo). Supabase’s built-in migration model assumes one project = one repo = one supabase/migrations/ folder, tracked in the single system ledger supabase_migrations.schema_migrations. That assumption structurally does not hold here:

  • supabase db push --linked compares the local repo’s files against the entire shared ledger (510+ rows, the large majority being p24-infra’s own internal schema). It has no concept of “this ledger row belongs to repo X”, so every repo’s db push perpetually fails with “Remote migration versions not found in local migrations directory”. This is not a one-off drift a migration repair can fix — it recurs on every run as long as the ledger is shared and un-partitioned. et-operational-platform’s supabase-migrations.yml failed on every run for weeks for exactly this reason.
  • mcp__claude_ai_Supabase__apply_migration stamps schema_migrations at apply-time with a version that does not match the migration file’s own timestamp prefix. In #5645 this produced 4 genuinely-et-op rows recorded under the wrong version (e.g. file 20260804140000_update_user_roles_rpc.sql stamped as ledger version 20260804195228). Any repo that needs precise file↔version correspondence is corrupted by this tool.

Both supabase db push and apply_migration are therefore banned for this project. The raw Management API /database/query endpoint is confirmed not to write the ledger and is the correct transport for out-of-band SQL.

The pattern (reuse p24-infra’s proven shape, generalised per repo)

Each repo gets three things, named for that repo:

1. A per-repo ledger table

CREATE TABLE IF NOT EXISTS public._<repo>_applied_migrations (
  filename   TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Lock it down (MANDATORY — this table lives in the PostgREST-exposed `public` schema).
-- Without this it comes up RLS-disabled with Supabase's default `authenticated=arwdDxtm`
-- grants, so ANY authenticated session (incl. a driver/technician PIN login) could DELETE
-- ledger rows — making applied migrations "pending" again so the next runner replays them,
-- including destructive DML — or INSERT a not-yet-created filename to permanently and
-- silently suppress that migration when it lands.
ALTER TABLE public._<repo>_applied_migrations ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE public._<repo>_applied_migrations FROM PUBLIC;
REVOKE ALL ON TABLE public._<repo>_applied_migrations FROM anon, authenticated;

No policies needed. RLS enabled + zero policies denies every non-bypassing role by default. The runner connects via the Management API (postgres) or service_role — both bypass RLS regardless — so it reads/writes the ledger normally. First shipped and independently reviewed in et-op (PR #1541, migration 20260806100000_secure_et_op_migration_ledger.sql).

RepoLedger table
p24-infrapublic._p24_applied_migrations (pre-existing — the reference implementation)
et-operational-platformpublic._et_op_applied_migrations
whatsup-android-chat-pullerpublic._whatsup_applied_migrations

The shared supabase_migrations.schema_migrations is left untouched — its ~510 rows become dead weight once no repo reads it for tracking. Do not try to clean it up (D6).

2. A small idempotent runner (duplicated per repo — no shared package, D3)

Read local supabase/migrations/*.sql in lexicographic order → diff against this repo’s ledger → apply pending files → record each in the ledger. Fail fast on the first error; never apply past a broken migration.

  • p24-infra (self-hosted bms-4 runner, can reach the DB directly): psql --single-transaction via the socat pooler proxy — scripts/apply-supabase-migrations.sh. This is the reference.
  • et-op / whatsup (GitHub-hosted ubuntu-latest, cannot reach the bms-4 socat proxy): apply via the Management API /database/query endpoint (transport decision D2). No psql dependency.

Runner requirements (all repos):

  • Idempotent — already-applied files are skipped; safe to re-run.
  • Fail-fast — stop on first error, do not apply further migrations.
  • -- migrate:no-transaction opt-out — a migration containing that directive comment on any line is applied without a transaction wrapper (for CREATE INDEX CONCURRENTLY, VACUUM, ALTER TYPE ... ADD VALUE). Such a file must be self-idempotent (IF NOT EXISTS).
  • Concurrency safety (D5) — both layers:
    1. a GitHub Actions concurrency: group on the migration workflow, and
    2. a Postgres advisory lock inside the runner. For a Management-API runner (each /database/query is its own connection/transaction, so a session-level pg_advisory_lock would release immediately), take a transaction-level lock per migration: wrap each migration in a single /database/query call of the form BEGIN; SELECT pg_advisory_xact_lock(<repo_key>); <re-check not already applied, else abort>; <migration body>; INSERT INTO public._<repo>_applied_migrations(filename) VALUES ('<f>'); COMMIT;. The xact lock is held for that transaction and released on COMMIT, so a second concurrent runner blocks, then its re-check sees the row and skips. (no-transaction migrations can’t take an xact lock — rely on the workflow concurrency: group + IF NOT EXISTS idempotency for those.)

3. A CI workflow that runs the runner on push

Replaces supabase-migrations.yml (supabase db push). Triggers on push to the repo’s default branch when supabase/migrations/** changes, plus workflow_dispatch with a dry-run input. Includes the concurrency: group above.

First-time adoption: verified backfill (D4 — MANDATORY, do not shortcut)

Before the runner goes live, the ledger must be seeded with the migrations that are already applied to the live DB. Verify each migration against the live schema catalog before marking it applied — the thorough option, deliberately chosen over the fast-path “seed all, verify only the broken-window set”.

Precedent for the thoroughness (D4): p24-infra incident #4450 — an unverified backfill silently marked a migration as applied that had never actually run, so the runner skipped it and a later deploy hit a missing object. A blanket “seed all 289 as applied” repeats exactly that risk.

Verification procedure (per migration file):

  1. Parse the file for the objects it creates/alters: CREATE TABLE, ALTER TABLE ... ADD COLUMN, CREATE [OR REPLACE] FUNCTION, CREATE POLICY, CREATE INDEX, CREATE TYPE, CREATE VIEW, etc.
  2. Query the live catalog (via Management API /database/query, read-only) to confirm those objects exist: information_schema.tables / .columns, pg_proc, pg_policies, pg_indexes, pg_type, information_schema.views.
  3. Classify:
    • Verified present → seed into the ledger as applied (applied_at = best-known date, or NOW()).
    • Effect absent → the migration is genuinely pending. Do not seed it; let the runner apply it on first run (it will apply cleanly if the objects really are missing).
    • Data-only / not verifiable by catalog (e.g. a backfill UPDATE, a one-off data fix) → cross-check the name against the existing shared schema_migrations ledger; if present there, seed as applied; otherwise flag for manual review — do not guess.
  4. Produce a seed SQL file (supabase/migrations/…_backfill_<repo>_ledger.sql — modelled on p24-infra’s 20260630_applied_migrations_backfill.sql) that INSERT ... ON CONFLICT DO NOTHINGs the verified-present set, and a short report of the pending + flagged sets.

Budget real time/tool calls for this — for et-op that means checking all 289 migrations, for whatsup all 28. It is a deliberate, one-time cost.

Transport reference (Management-API runner)

POST https://api.supabase.com/v1/projects/mwkqmgadqnkkihjdeqsi/database/query
Authorization: Bearer <sbp_… Management API PAT>
Content-Type: application/json
{ "query": "<SQL>" }
  • Token: repo’s own SUPABASE_ACCESS_TOKEN GitHub secret (a sbp_… PAT). Read-only catalog queries and DDL both go through the same endpoint.
  • This endpoint does not write supabase_migrations.schema_migrations — that is exactly why it is the sanctioned transport here.
  • Full escaping/gotchas (PowerShell): docs/playbooks/supabase-management-api-sql-windows.md.

Do / Don’t

  • ✅ New migration → drop a timestamped .sql in the repo’s supabase/migrations/, open a PR, let CI apply it.
  • ✅ Out-of-band apply for testing → Management API /database/query.
  • ❌ supabase db push / supabase-migrations.yml for this project.
  • ❌ mcp__claude_ai_Supabase__apply_migration — banned project-wide.
  • ❌ Hand-applying a migration with execute_sql and skipping the ledger.
  • ❌ Touching / “cleaning up” the shared supabase_migrations.schema_migrations table.
  • Reference implementation: scripts/apply-supabase-migrations.sh, docs/playbooks/supabase-migrations-ci.md.
  • Design + decisions: #5645 (brainstorm), #5653 (this implementation), et-operational-platform#1500 (Return-To).
  • Implementation plan: docs/plans/plan-5653-shared-supabase-migration-tracking.md.

Graph View

Backlinks