Skip to main content

​
Supabase migrations: native GitHub integration

Supabase’s GitHub integration deploys supabase/migrations/. There is no migration workflow of our own. Flow: PR into staging (preview branch builds the schema from the files) -> merge (applies to staging) -> release PR staging -> main -> merge (applies to production).

​
Layout (since 2026-10-16)

  • supabase/migrations/20261016205500_prod_schema_baseline.sql: production’s schema, dumped 2026-10-16 20:55 UTC with the supabase db dump recipe. Every branch starts here.
  • supabase/migrations/ after the baseline: migrations not yet in production.
  • supabase/migrations-archive/: every earlier file, original names. Supabase ignores this folder. Tests read both folders through lib/migrations/__tests__/migration-files.ts, so guard tests still check every new migration.

​
Why a baseline

The old files could not rebuild the database. Tables (ai_token_usage_daily, api_token_usage, import_agent_runs), functions and a role (analytics_read) were created by hand and never committed, 78 dates had two or more files sharing one version, and some files ran in the wrong order. Every Supabase branch build failed, and the integration has reported MIGRATIONS_FAILED on staging and main for months. Left out of the baseline on purpose: Supabase-managed schemas, scratch schemas (cfo_scratch, repair_ops), one-off backup/recon tables, event triggers and pg_cron schedules (data, not schema). The baseline resets Supabase’s default grants before restoring production’s, so branches get production’s privileges.

​
Rules for new migrations

  • Unique 14-digit version. npx supabase migration new <name>. CI runs scripts/check-migration-versions.mjs in the lint job.
  • No CONCURRENTLY; wrap LOCK TABLE in BEGIN/COMMIT. Supabase’s runner sends a file as one batch. For an index on a big production table, build it CONCURRENTLY by hand first, then merge a plain CREATE INDEX IF NOT EXISTS.
  • Never change a database by hand. A recorded version with no file stops every later deploy, and the change is lost the next time a branch is built.

​
Cutover

  1. PR preview branch builds green from the baseline.
  2. Staging: back up supabase_migrations.schema_migrations, then record the baseline and the files after it as applied (they are already on staging).
  3. Merge the PR to staging; the integration should report staging up to date.
  4. Production (separately approved): back up and record the baseline as applied. The files after it are not in production yet; the next release applies them.
  5. Supabase dashboard: production branch main with “Deploy to production”, staging persistent branch on git staging, automatic branching “Supabase changes only”. GitHub: make Supabase Preview a required check.

​
Known drift, to fix later

  • Staging is ahead of production. Migrations on main that production never got (the multi-portfolio backend 20260928_06..20261008, the snaptrade webhook and vault_folders unique indexes, the ai_chat_logs FTS indexes) are in the archive but missing from production and the baseline. Staging has them.
  • Staging also differs from production on policies, grants and functions (staging: 445 RLS policies, production: 640).