Database migrations

What a migration is here

One SQL file in supabase/migrations/, describing one change to the database, named with a date so the files sort in the order they must run.

A migration is not only for adding columns. Any change to structure or behaviour belongs in one: a new table, a policy, a function, a trigger, a grant, a data repair.

They are applied by hand

There is no automated migration runner in the deployment pipeline. A migration is applied by an owner pasting it into the SQL editor in Supabase Studio, or running it through psql inside the database container.

This has a consequence worth being explicit about: merging a migration file does not apply it. The file is the record of the change; applying it is a separate, manual step. A pull request that adds a migration is not finished until someone has run it and said so.

Automating this is worthwhile, but it has not been done, and pretending otherwise would be worse than documenting the reality.

Each file runs as one transaction

Everything in a file either applies or none of it does. That is usually what you want, particularly for a file that drops and recreates policies: a partial failure there would leave row-level security enabled with no policy, which denies access to everyone.

One exception matters. PostgreSQL will not let a newly added enum value be used in the same transaction that adds it. Adding a role label and then writing a policy that references it must be split across two files, applied in order, with the first allowed to commit first. That is exactly why the role hierarchy arrived as two migrations rather than one.

Naming

YYYYMMDD_short_description.sql

The date orders the file. The description says what it does. Older files in the directory carry generated identifiers instead of descriptions, which is a pattern worth not continuing: a filename that says add_affiliation is findable, and one that says b1efb496-b080-4874-865d-216575e279a8 is not.

Write for the person reading it later

Migrations are the permanent record of why the database looks the way it does. Comments in them are read far more often than comments in application code, because someone investigating a constraint goes looking for the migration that created it.

Say why, not what. The SQL already says what.

Two bugs worth learning from

Both were caught before reaching production, by testing against a scratch copy of the real schema and data. Both would have been invisible in review.

The trigger that undid its own repair. A migration looped over rows with incorrect identifiers and updated each one. Every update fired the table's update trigger, which saw that the batch year had not changed, took its "keep the existing identifier" branch, and reset the value straight back. The migration reported success and changed nothing. The fix was to disable the trigger around the repair and re-enable it afterwards.

The counter that could not see its own rows. The identifier allocator searched for the highest existing number by matching on batch_year. Once a row's year drifted from the year embedded in its identifier, the allocator could no longer see that row and reissued its number to someone else, producing a genuine duplicate. Matching on the identifier's prefix instead fixed it, which then made it safe to restore the unique constraint that had been dropped earlier to work around the symptom.

The general lesson: a repair that fires triggers, and a query whose filter disagrees with where the data actually lives, are both easy to get wrong and easy to catch by running them once against real data.

Before you write one

Read Reviewing database changes. It describes the testing process that caught both of the above.