Migration safety

A new version of your app often comes with database changes, written as migration files in api/db/. On the Build Server those run against test data, but on Rehearsal and Live they run against real data. A migration that worked fine on an empty table can fail, or destroy data, on a table with a million rows in it.

Migration safety is the checkup check that looks at those changes before they ship. It runs in every checkup that targets a server, and it is one of the checks Live won’t ship without: a version can’t be published to Live unless this check ran against Live’s own database and nothing it found is still open. Every finding it raises blocks Live until it is fixed or waived: a failure, a statement that changes rows, a statement that could not be verified, and data that would be deleted.

What it looks at

The check compares the migrations in your project (api/db/meta/_journal.json and the .sql files it lists) with the ones the target server’s database has already applied. Only the difference matters, meaning the migrations the next publish would run. Each one is split on --> statement-breakpoint into separate statements.

A migration whose every object already exists on the server is set aside. A squashed baseline over a schema that older migrations already built is an example. The publish records it without running it, so there is nothing to check.

Safe, risky, lossy

Every remaining statement is classified by fixed pattern rules. No AI is involved, so the same SQL always gets the same answer:

Two refinements keep the list honest:

• A lossy statement on a table (or column) that is empty on the server is downgraded to safe, since there is nothing to lose. The check counts rows to find out, and it only reads.
• A risky change to a table that the same publish creates is downgraded to safe, because that table has no rows yet for the change to fail on.

Statements wrapped in the usual idempotency guard (DO $$ BEGIN … EXCEPTION WHEN duplicate_object THEN NULL; END $$) are classified by the statement inside. Any other DO block is opaque, so it stays risky.

Why a risky statement is risky

Each one can fail, or misbehave, only because of data that is already in the table:

• SET NOT NULL fails if any row holds NULL in that column, and scans the whole table under an exclusive lock. Backfill the NULLs in an earlier migration first.
• ADD CONSTRAINT … UNIQUE fails if two rows already share a value, and builds its index while the table is locked against writes. Resolve the duplicates first.
• ALTER COLUMN … TYPE converts every value. It fails on any value that doesn’t convert, and rewrites the table under an exclusive lock.
• ADD COLUMN … NOT NULL without a DEFAULT fails on any table that has rows, because those rows would have no value for the new column.
• RENAME succeeds, but the version that is still running reads the old name. Between the migration and the new version taking over, that version gets errors. The safe way is to add the new name, ship code that uses it, and drop the old name in a later release.

Each risky finding repeats the statement and gives this explanation as its suggested fix.

The dry-run

Risky statements aren’t judged from their text alone. The check runs them for real, on a copy:

1. It takes the target server’s latest completed backup. If the server has no backup yet, the checkup takes one itself. A backup is a dump, so it only reads the database. It appears on the server’s Backups list as a safety backup.
2. It restores that backup into a scratch database on the same database server, beside the real one. The real database is never written to.
3. It replays every pending statement in order, safe ones included, because a risky statement often depends on an earlier one (for example a constraint on a column the same migration just added).
4. It reports each risky statement that failed, with Postgres’s own error, or that changed rows, with how many.
5. It drops the scratch database, whatever happened.

If the backup can’t be taken, the dry-run can’t happen. The finding then says the statements were not verified: it lists them with the reason each is risky, followed by the backup’s error. Fix the backup (see Backups) and run the checkup again, or waive the finding if you have checked the statements yourself.

Lossy statements

A lossy statement on a table that holds data becomes a finding that blocks Live: data in orders.legacy_ref will be deleted. Its detail shows the exact statement. You close it by fixing the migration, or with Acknowledge data loss, which waives it with your reason on the record.

The backup right before the migrations run

When the checked version contains any lossy or risky statement, whether it was waived, acknowledged or dry-run cleanly, the publish takes a fresh safety backup of the target database immediately before its migration step. If that backup fails, the publish stops before running any migration. A data-changing migration therefore never runs without a recovery point from minutes earlier. To roll back, restore that backup from the Database page.

What it can’t promise

The check narrows the risk, but it doesn’t remove it. Know its limits:

• The dry-run uses the backup’s data, not today’s. Rows written after that backup weren’t part of it. A NULL written an hour ago can still fail SET NOT NULL for real. The pre-migration backup is what covers this gap.
• Time and locks aren’t measured. The scratch copy has no traffic, so a statement that locked a busy table for a minute looks instant there. Large ALTER COLUMN … TYPE, SET NOT NULL and UNIQUE changes on big tables deserve a quiet moment to publish.
• Code compatibility isn’t checked. The check knows SQL, not your app. A rename, or a dropped column the running version still reads, passes the dry-run and breaks the running version until the new one is serving.
• Classification is by pattern. Anything unrecognised is treated as risky rather than safe, so the rules err on the side of a dry-run, but a data change inside an opaque block is only as visible as its row count.
• Platform migrations aren’t included. Those that Sapilon ships in api/src/_internal/db/ are tested before release and aren’t part of this check.

Next: Checkup · Servers: backups · Live