p24-infra Docs

plan-5653-shared-supabase-migration-tracking

8 min read

Plan #5653 — Shared-Supabase migration tracking fix (all 3 phases)

Issue: radieu/p24-infra#5653 · Return-To: radieu/et-operational-platform#1500 · Design/decisions: #5645 (7 decisions confirmed by radieu on #1500).

Canonical standard this plan implements: docs/playbooks/supabase-per-repo-migration-ledger.md.

Problem (recap)

Supabase project mwkqmgadqnkkihjdeqsi is intentionally shared across repos. Supabase’s built-in schema_migrations ledger is one-per-project, so every repo’s supabase db push perpetually fails (“Remote migration versions not found in local migrations directory”), and apply_migration stamps the shared ledger with apply-time versions that don’t match file timestamps. Fix: per-repo ledger + custom idempotent runner, replacing supabase db push; ban apply_migration project-wide.

Confirmed decisions (from #5645 → #1500)

#Decision
D1Reuse p24-infra’s proven _p24_applied_migrations + custom-runner pattern, generalised per repo.
D2et-op runner transport = Management API /database/query (et-op CI runs on GitHub-hosted ubuntu-latest, cannot reach bms-4/psql).
D3Duplicate a small runner per repo. No shared package/submodule.
D4Verify all migrations against the live schema catalog before seeding the ledger (thorough option; precedent = incident #4450).
D5Concurrency = GH Actions concurrency: group plus a Postgres advisory lock in the runner (both layers).
D6Ban mcp__claude_ai_Supabase__apply_migration project-wide; fix p24-infra worker guidance. Leave the ~510-row schema_migrations pollution untouched.
D7All three repos in this push (Phase 1 et-op, Phase 2 p24-infra, Phase 3 whatsup).

Phase 1 — et-operational-platform (routed to an et-op session)

289 migrations in supabase/migrations/ (confirmed via GitHub contents API), default branch main, existing .github/workflows/supabase-migrations.yml uses supabase db push --linked.

1a. Ledger table

CREATE TABLE IF NOT EXISTS public._et_op_applied_migrations (
  filename   TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE public._et_op_applied_migrations IS
  'Per-repo migration ledger for radieu/et-operational-platform. Managed by scripts/apply-migrations.mjs. See p24-infra docs/playbooks/supabase-per-repo-migration-ledger.md';
 
-- Lock it down (MANDATORY). This table is in the PostgREST-exposed `public` schema; without RLS +
-- these REVOKEs it comes up with Supabase's default `authenticated=arwdDxtm` grants, so any
-- authenticated session (incl. a driver/technician PIN login) could DELETE ledger rows (→ the
-- runner replays those migrations, including destructive DML) or INSERT a future filename (→
-- permanently suppress that migration). RLS enabled + zero policies denies every non-bypassing
-- role; the runner uses `postgres`/`service_role`, which bypass RLS.
ALTER TABLE public._et_op_applied_migrations ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE public._et_op_applied_migrations FROM PUBLIC;
REVOKE ALL ON TABLE public._et_op_applied_migrations FROM anon, authenticated;

Shipped as 20260806100000_secure_et_op_migration_ledger.sql (PR #1541, review finding C1).

1b. Runner — scripts/apply-migrations.mjs (Node 20, zero deps, Management API)

Shape (the et-op session finalises; this is the reference design):

#!/usr/bin/env node
// Idempotent migration runner for et-operational-platform against the SHARED Supabase project.
// Transport: Supabase Management API /database/query (no psql; runs on ubuntu-latest).
// Tracks applied files in public._et_op_applied_migrations. Fail-fast. See p24-infra playbook
// docs/playbooks/supabase-per-repo-migration-ledger.md.
import { readFileSync, readdirSync } from 'node:fs';
import { join } from 'node:path';
 
const REF   = process.env.SUPABASE_PROJECT_ID;              // mwkqmgadqnkkihjdeqsi
const TOKEN = process.env.SUPABASE_ACCESS_TOKEN;            // sbp_… Management API PAT
const DIR   = process.env.MIGRATIONS_DIR || 'supabase/migrations';
const DRY   = process.env.DRY_RUN === 'true';
const LOCK_KEY = 5653001;                                   // constant repo-scoped advisory-lock key
if (!REF || !TOKEN) { console.error('SUPABASE_PROJECT_ID / SUPABASE_ACCESS_TOKEN required'); process.exit(1); }
 
const URL = `https://api.supabase.com/v1/projects/${REF}/database/query`;
async function q(sql) {
  const r = await fetch(URL, {
    method: 'POST',
    headers: { Authorization: `Bearer ${TOKEN}`, 'Content-Type': 'application/json' },
    body: JSON.stringify({ query: sql }),
  });
  const text = await r.text();
  if (!r.ok) throw new Error(`query failed (${r.status}): ${text}`);
  return text ? JSON.parse(text) : [];
}
const sqlLit = s => `'${s.replace(/'/g, "''")}'`;
 
// Bootstrap the ledger AND lock it down in one shot, so a fresh repo copying this runner can never
// come up with an unprotected ledger table. All three statements are idempotent / safe to re-run.
await q(`CREATE TABLE IF NOT EXISTS public._et_op_applied_migrations (
  filename TEXT PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
ALTER TABLE public._et_op_applied_migrations ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE public._et_op_applied_migrations FROM PUBLIC;
REVOKE ALL ON TABLE public._et_op_applied_migrations FROM anon, authenticated;`);
 
const applied = new Set(
  (await q(`SELECT filename FROM public._et_op_applied_migrations;`)).map(r => r.filename));
const files = readdirSync(DIR).filter(f => f.endsWith('.sql')).sort();
const pending = files.filter(f => !applied.has(f));
console.log(`total=${files.length} applied=${applied.size} pending=${pending.length}`);
if (DRY) { pending.forEach(f => console.log('PENDING', f)); process.exit(0); }
 
let done = 0;
for (const f of pending) {
  const body = readFileSync(join(DIR, f), 'utf8');
  const noTxn = /^\s*--\s*migrate:no-transaction(\s|$)/im.test(body);
  console.log(`APPLY ${f}${noTxn ? ' (no-transaction)' : ''} ...`);
  try {
    if (noTxn) {
      // Cannot take an xact lock; rely on workflow concurrency group + self-idempotent DDL.
      await q(body);
      await q(`INSERT INTO public._et_op_applied_migrations(filename) VALUES (${sqlLit(f)}) ON CONFLICT DO NOTHING;`);
    } else {
      // D5 layer 2: transaction-level advisory lock + re-check guard, all in one query/transaction.
      await q(
        `BEGIN;\n` +
        `SELECT pg_advisory_xact_lock(${LOCK_KEY});\n` +
        `DO $guard$ BEGIN\n` +
        `  IF EXISTS (SELECT 1 FROM public._et_op_applied_migrations WHERE filename = ${sqlLit(f)}) THEN\n` +
        `    RAISE EXCEPTION 'MIGRATION_ALREADY_APPLIED';\n` +
        `  END IF;\nEND $guard$;\n` +
        `${body}\n;\n` +
        `INSERT INTO public._et_op_applied_migrations(filename) VALUES (${sqlLit(f)});\n` +
        `COMMIT;`
      );
    }
    console.log(`OK   ${f}`); done++;
  } catch (e) {
    if (String(e).includes('MIGRATION_ALREADY_APPLIED')) { console.log(`SKIP ${f} (raced)`); continue; }
    console.error(`FAIL ${f} — stopping.\n${e}`); process.exit(1);
  }
}
console.log(`Done: applied=${done} skipped=${pending.length - done}`);

1c. CI workflow — .github/workflows/supabase-migrations.yml (replaces db push)

name: Supabase Migrations
on:
  push:
    branches: [main]
    paths: ['supabase/migrations/**']
  workflow_dispatch:
    inputs:
      dry_run: { description: 'List pending only', type: boolean, default: false }
concurrency:
  group: supabase-migrations-et-op       # D5 layer 1 — one migration run at a time
  cancel-in-progress: false
jobs:
  migrate:
    runs-on: ubuntu-latest
    timeout-minutes: 20
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with: { node-version: '20' }
      - name: Apply migrations
        run: node scripts/apply-migrations.mjs
        env:
          SUPABASE_PROJECT_ID:  ${{ secrets.SUPABASE_PROJECT_ID }}
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
          DRY_RUN: ${{ github.event.inputs.dry_run || 'false' }}

Delete the old supabase db push body; keep the same filename so branch protection / required checks don’t need reconfiguring.

1d. Verified backfill of all 289 (D4) — the deliberate, un-shortcut step

Follow docs/playbooks/supabase-per-repo-migration-ledger.md#first-time-adoption-verified-backfill exactly for every one of the 289 files:

  1. Parse each file for created/altered objects.
  2. Confirm each object in the live catalog via Management API read-only queries (information_schema.tables/.columns, pg_proc, pg_policies, pg_indexes, pg_type, views).
  3. Classify verified-present (seed as applied) / effect-absent (leave pending — runner applies it) / data-only-or-ambiguous (name-match against shared schema_migrations, else flag for manual review).
  4. Emit supabase/migrations/<ts>_backfill_et_op_ledger.sql (INSERT … ON CONFLICT DO NOTHING for the verified set), plus a report listing the pending + flagged sets in the PR body.
  5. Include the 4 known-mismatched et-op migrations from #5645 in the verification (they ARE applied — confirm their objects exist, seed them by filename so future runs skip them).

Do not blanket-seed all 289 as applied (that is the #4450 failure mode).

1e. Order of operations (et-op session)

Ledger table + backfill SQL first (via one Management-API run or a bootstrap migration), then land the runner + workflow. Verify a DRY_RUN=true dispatch reports pending=0 (or exactly the known-good pending set) before enabling push-triggered runs.

Phase 2 — p24-infra (this repo, done in this PR)

  1. infra/agent-prompts/worker-issue.md — replaced the apply_migration recommendation (was line 821) with: write the migration file only + let the repo’s ledger-backed CI apply it, and an explicit project-wide ban on apply_migration (and on hand-applying via execute_sql).
  2. docs/playbooks/supabase-per-repo-migration-ledger.md — new playbook formalising the standard (per-repo ledger + runner, the ban, the verified-backfill procedure, transport reference).
  3. CLAUDE.md — added a “Do NOT” entry for the apply_migration ban.
  4. This plan doc.

The existing ~510-row schema_migrations pollution is left untouched (D6).

Phase 3 — whatsup-android-chat-puller (routed to a whatsup session)

28 migrations in supabase/migrations/ (confirmed), default branch dev, currently applied ad-hoc manually via the Management API (ungoverned, not broken). Same pattern as Phase 1, scaled to 28:

  • Ledger public._whatsup_applied_migrations.
  • Runner scripts/apply-migrations.mjs (identical shape to Phase 1; LOCK_KEY=5653003, ledger/concurrency-group names swapped).
  • CI workflow triggering on push to dev (its default branch) when supabase/migrations/** changes, concurrency: supabase-migrations-whatsup.
  • Verified backfill of all 28 per D4 (same procedure; smaller set).
  • Requires the repo to have SUPABASE_PROJECT_ID + SUPABASE_ACCESS_TOKEN GitHub secrets (add if absent).

Rollout / verification checklist

  • Phase 2 PR merged to p24-infra main (ban + playbook + plan live before executors start).
  • Phase 1 issue created in et-op with this plan linked; runner + ledger + workflow + verified 289 backfill; DRY_RUN clean; old db push removed.
  • Phase 3 issue created in whatsup; runner + ledger + workflow + verified 28 backfill; secrets present.
  • Each repo: first real push-triggered run applies cleanly and records to its own ledger.
  • Confirm no repo references supabase db push or apply_migration afterward.

Risks

  • Backfill mis-classification (D4) — mitigated by catalog verification + manual-review flag for ambiguous/data-only migrations. Precedent #4450.
  • Advisory lock on no-transaction migrations — those fall back to the workflow concurrency: group + IF NOT EXISTS idempotency (documented limitation).
  • Management API rate/size limits on a 289-migration first run — the backfill seeds “already applied” via SQL, so the first runner execution should have ~0 pending; only genuinely-pending files get applied one query each.

Graph View