Migrations and pgTAP

Lesson 4 · about 15 minutes · the unit of database change, how it reaches prod, and the test that guards it.

Every schema change, policy, cron job, trigger and SQL function in this backend is a timestamped file in src/supabase/migrations/. There are 239 of them, applied in filename order, and prod receives them automatically on merge. That makes a migration the most consequential file you will write: it runs once, in production, with no rollback step. pgTAP is how you prove it does what you think before that happens.

The migration file

FactDetail
NameYYYYMMDDHHMMSS_slug.sql. Created by npx supabase migration new slug from src/. The timestamp is the ordering; two files with the same prefix are a merge hazard.
Baseline20250923031718_remote_schema.sql is a dump of the pre-existing prod schema. Everything after is incremental.
IdempotenceProd applies each file once, but local replays all of them on every db reset. So files use CREATE OR REPLACE, DROP POLICY IF EXISTS, and wrap cron.unschedule in an exception block. migrations/20251119213019:9 · 20251117191248:10-15
CommentsLong-lived reasoning goes in the file header and in COMMENT ON. Read 20260714200640's header for the standard: what happened, why this fix, what is unchanged.
GrantsAnything SECURITY DEFINER gets an explicit REVOKE … FROM public, anon, authenticated and GRANT … TO service_role. migrations/20260310205722_create_secret_function.sql

Local versus prod

1Locally, npx supabase db reset drops the database, replays every migration in order, then runs seed.sql. Seed data is fake companies with quartr_company_id >= 90000 so it can be deleted by range. This is the only way to apply a new migration locally; there is no incremental apply. seed.sql:1-16
2npx supabase start does the same replay on first boot. It also reads config.toml, which is why local auth and function settings match the file while prod does not.
3Prod receives migrations from the GitHub integration on merge to main. It applies pending migration files and deploys edge functions. It does not run config push. CLAUDE.md forbids db push and functions deploy from a developer machine for the same reason: two writers to prod is one too many. config.toml:77-79 · CLAUDE.md
4Dashboard-only state still exists. Vault secrets, function env vars, auth provider settings, auth hooks. A migration that depends on a vault secret must tolerate its absence, as the health runner does with ops_alert_webhook_url. migrations/20260714200657:212-226

docs/DEPLOYMENT_GUIDE.md still describes the manual link, db push, functions deploy, unlink sequence and lists 7 functions. Treat it as the emergency procedure, not the normal path; the function count alone shows it predates half the repo.

Why there is no down migration. Supabase migrations are forward-only. The repo's answer to a wrong migration is another migration that corrects it, and the file header records why. Migration 20260714200648 exists purely to record manual fixes already applied by hand in prod, so local and prod histories agree.

pgTAP: a test is a transaction

A pgTAP file is plain SQL run inside begin; … rollback;. Whatever rows it inserts vanish at the end. select plan(N) declares how many assertions will run; finish() fails the file if the count differs, which catches a test that silently skipped a branch. 155 files live in src/supabase/tests/database/, and npx supabase test db discovers them all.

Walk through account-deletion.test.sql, which guards soft_delete_user:

1Existence. has_function('public','soft_delete_user', array['uuid'], …). A dropped or re-signatured function fails here first, with a readable message. tests/database/account-deletion.test.sql:23-34
2Pure behaviour. is(obfuscate_value(id,'hello'), obfuscate_value(id,'hello')) pins determinism; isnt(…) with a different id pins that the hash depends on the user. :40-55
3Fixture. A direct insert into auth.users with a fixed uuid and every token column populated, plus a second, untouched user. Inserting into auth.* is fine here because it rolls back. :61-80
4Act, then assert on state. Call the function, then is/isnt on deleted_at, encrypted_password, session count, identity data, and finally that the other user is unchanged. Twenty-five assertions, one plan.

Compare cron-job-integrity.test.sql from lesson 2. Same frame, but its assertions read cron.job and pg_proc: a pgTAP test can assert on the schema's metadata, which is how it enforces an invariant across every future migration rather than one function.

AssertionUse for
has_function, has_table, has_column, col_type_isShape of the schema
is, isnt, okValues, including scalar subqueries
policies_are, policy_roles_are, is_rls_enabled... or a query on pg_policiesRLS coverage
throws_ok, lives_okGrants and constraints: assert that anon raises

Check yourself

Do this in the repo

  1. Create a throwaway migration, replay, and delete it: cd src && npx supabase migration new scratch, add select 1;, npx supabase db reset, then remove the file and reset again. Note how long a full replay takes; that is your inner loop.
  2. Run one test file and read its TAP output line by line: npx supabase test db then find account-deletion in the output. Change plan(25) to plan(26), rerun, and see how the failure is reported. Revert.
  3. Pick the newest migration in the folder and write down, before reading any test, which pgTAP assertions you would want for it. Then check whether a test file with a matching name exists.

Primary source

Supabase: pgTAP for the assertion catalogue, and Local development for what db reset and the migration folder do.