p24-infra Docs

supabase-migrations-ci

3 min read

Playbook: Supabase Migrations CI Pipeline

What it is

Automated migration apply on push to main or rc/* when supabase/migrations/** changes. Workflow: .github/workflows/apply-supabase-migrations.yml Script: scripts/apply-supabase-migrations.sh Tracking table: public._p24_applied_migrations

Non-transactional migrations (CREATE INDEX CONCURRENTLY, etc.)

Each migration is applied inside psql --single-transaction by default. A few statements cannot run inside a transaction block — notably CREATE INDEX CONCURRENTLY (also VACUUM, and ALTER TYPE ... ADD VALUE on older Postgres). Applying such a file in a transaction fails with:

ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

To opt a migration out of the transaction wrapper, add this directive on any line of the file (a plain SQL comment — inert to psql):

-- migrate:no-transaction

apply-supabase-migrations.sh greps for it and applies that file without --single-transaction.

Requirement: a no-transaction migration is not atomic — an error mid-file leaves earlier statements committed. Make every statement idempotent (CREATE ... IF NOT EXISTS, DROP ... IF EXISTS) so a re-run is safe. Precedent: uq_credential_job_active (#5237/#5246), uq_orchestrator_merge_active (#4434).

Root cause of incident this fixes

PR #2237 deployed code reading server_status column but the migration that adds the column (monitoring/supabase/migrations/049_server_heartbeat.sql) was not in CI. Result: 3h dispatch outage.

Rules going forward

  • ALL new migrations go to supabase/migrations/ only (timestamp prefix: 20260XXX_name.sql)
  • monitoring/supabase/migrations/ is frozen — do not add new files there
  • Every PR that adds a migration to supabase/migrations/ triggers this workflow automatically

First-time setup (backfill tracking table)

The tracking table needs to know which pre-existing migrations are already applied. Run this once after the workflow is deployed (or whenever re-seeding):

-- Mark all existing supabase/migrations/*.sql as already applied.
-- Run via Management API: docs/playbooks/supabase-management-api-sql-windows.md
CREATE TABLE IF NOT EXISTS public._p24_applied_migrations (
  filename   TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
INSERT INTO public._p24_applied_migrations (filename) VALUES
  -- Add all filenames from supabase/migrations/ that are already applied.
  -- Run: ls supabase/migrations/*.sql | xargs -I{} basename {} to get the list.
  ('20260621_dev_r_projects_phase1_columns.sql'),
  ('20260622_dev_r_status_snapshots.sql'),
  ('20260623101658_dev_r_services_credential_rotation_fix.sql'),
  -- ... (add remaining files)
ON CONFLICT DO NOTHING;

Troubleshooting

Workflow not triggering:

  • Check push is to main or rc/* branch
  • Check that the push includes changes to supabase/migrations/**

CREATE INDEX CONCURRENTLY cannot run inside a transaction block:

  • The migration must opt out of the transaction wrapper — add -- migrate:no-transaction to the file (see the Non-transactional migrations section above), then re-run the workflow.

Migration failed mid-apply:

  1. Identify which file failed (look for FAIL <filename> in the run log)
  2. Fix the SQL or apply manually via Management API
  3. If applied manually, record it: 
    INSERT INTO public._p24_applied_migrations (filename) VALUES ('<filename.sql>') ON CONFLICT DO NOTHING;
  4. Re-run the workflow via workflow_dispatch

Check what’s pending without applying:

gh workflow run apply-supabase-migrations.yml -f dry_run=true

Check tracking table:

SELECT filename, applied_at FROM public._p24_applied_migrations ORDER BY applied_at DESC LIMIT 20;

Escalation

If psql connection fails:

  1. Verify SUPABASE_DB_PASSWORD is set in /opt/p24-infra/bms-4/.env
  2. Run secrets-sync.yml to re-sync the file
  3. Check Supavisor pooler health: psql -h aws-0-eu-central-1.pooler.supabase.com -p 5432 -U postgres.mwkqmgadqnkkihjdeqsi -d postgres -c "SELECT 1;"

Audit Log — Log to infra_operations

After this operation completes, log it to the infra_operations audit table.

Python (Linux server — bms-4, vps-i1, vps-h1, or similar):

import sys
sys.path.insert(0, '/opt/p24-infra')
from scripts.lib.log_op import log_op
 
log_op(
    actor="ci",  # "radieu" for manual human ops, "claude" for agent
    op_type="migration",
    resource="supabase-schema",
    result="success",  # "success" | "failed" | "skipped"
    detail="Supabase schema migration deployed via CI/CD pipeline",
    env="vps-i1",
    gh_issue=2730,
)

PowerShell (Windows dev machine):

$env:SUPABASE_URL = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_URL=").ToString().Split("=",2)[1].Trim()
$env:SUPABASE_SERVICE_KEY = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_SERVICE_KEY=").ToString().Split("=",2)[1].Trim()
python -c "
import os, sys
sys.path.insert(0, 'C:/code_2026/p24-infra')
from scripts.lib.log_op import log_op
log_op('ci', 'migration', 'supabase-schema', 'success', 'Supabase schema migration deployed via CI/CD pipeline', 'vps-i1')
"
$env:SUPABASE_URL = ''; $env:SUPABASE_SERVICE_KEY = ''

Graph View

Backlinks