Add a database migration

SupaCloud uses sequential, numbered SQL migrations in server/migrations/.

Steps

1. Find the next number — safely. Migration numbers are a shared namespace across parallel developers and worktrees. Before picking a number:

   git fetch
   ls server/migrations/        # scan the highest number on main

   Also scan any sibling worktrees. Then take the next free number.

2. Add the file as NNN_short_description.sql in server/migrations/.

3. Keep closed CHECK vocabularies executable. If a migration renames a stored value to one the old CHECK (... IN (...)) does not permit, use this exact sequence: DROP CHECK → UPDATE old rows → ADD CHECK. A deletion or a rewrite to a value already permitted by the old CHECK may happen before the narrowing CHECK. The committed guard catches the unsafe rename order:

   python3 scripts/check-migration-check-order.py --self-test
   python3 scripts/check-migration-check-order.py

   Add a seeded upgrade test whenever rows can carry the old value; an empty database cannot expose this class of failure.

4. Verify it applies and the server still type-checks:

   cd server && cargo check

Caution

Never edit an applied migration. Migrations are applied once and tracked by filename; changing one that has already run will not re-run and will diverge environments. Add a new migration instead.

Note

Gaps in the sequence are fine — sqlx tracks migrations by filename, not by a contiguous range. A few historical numbers are unused.

See also

• Local development setup