Migrations only add

A migration adds. It never drops, renames or retypes anything, never tightens a column or a constraint the last release writes through, and never deletes data. That is the whole rule, and a test fails the build when a migration breaks it.

Why

Every box follows :stable and Podman’s auto-update moves it. When a release migrates the database and then fails its health check, the update is rolled back, and the previous release starts on the schema the new one left. A migration can’t be rolled back with it. So the previous release has to work on the new schema, and the only way to promise that is for the new schema to hold everything the old one did, in the same shape, accepting the same writes.

Add a column and the old release never looks at it. Drop one and the old release’s first query is an error, on a plant, at three in the morning, with nothing left to roll back to.

The Helm chart has the same problem: helm rollback puts the old image back and leaves the database where the new one moved it.

What the check refuses

TestMigrationsAreAdditive (internal/store/migrations_additive_test.go) reads every embedded migration, PostgreSQL’s and SQLite’s, and fails on:

In a migration	Why the previous release breaks
DROP anything: table, column, index, constraint, default, NOT NULL	It reads or writes what is gone, or relied on the rule that was dropped
RENAME a table or a column	It looks for the old name
ALTER COLUMN: a type, SET NOT NULL, SET DEFAULT	It was written against the old type and the old rule
ADD COLUMN ... NOT NULL with no DEFAULT	Its INSERT doesn’t name the column and fails
ADD COLUMN with UNIQUE, PRIMARY KEY or CHECK	Its rows can break the rule
ADD CONSTRAINT, ADD UNIQUE, ADD CHECK, ADD FOREIGN KEY on an existing table	Its writes can be refused
CREATE UNIQUE INDEX on a table the migration didn’t create	Its writes can be refused
DELETE, TRUNCATE	Rows it expects are gone
UPDATE of a column the migration didn’t add	It reads data that changed meaning
CREATE TRIGGER, CREATE OR REPLACE, ALTER ... SET SCHEMA/LOGGED, row level security	Its writes are changed or rejected
Any statement the check doesn’t know to be additive	Teach the check first, in the same change

What passes: CREATE TABLE, CREATE INDEX, ALTER TABLE ... ADD COLUMN that is nullable or has a default, INSERT, and UPDATE that fills in a column the same migration added. A new table can carry any constraint it likes, because no earlier release writes to it.

The same test runs on SQL written to be wrong, so a check that stopped catching a drop fails there and not in the field.

The changes that look like they need more

You can’t drop, so a change that seems to need one is done by adding and then leaving the old thing alone.

• Rename a column. Add the new column with a default. The new release reads the new one and writes both. The old column stays, unused, for good.
• Change a type. Same: a new column of the new type, the new release writes both, the old column stays.
• A column that must now be NOT NULL. Enforce it in the application. If a constraint is truly needed, it goes on a table that is new.
• Retire a table. Stop using it. It stays.
• Clean up old data. The application does it at runtime (retention does this already), not a migration.

Dead columns cost a few bytes. A migration that can’t be undone while the release it belongs to can be costs a plant.

The guard: an app refuses a schema it can’t be trusted on

Adding is safe for one release, not for ever. A release has only been proven on the schema right after it, so the app counts how far ahead the database is.

schema_migrations records, beside each migration, which release applied it (app_version). On start, before it touches anything, the app compares the migrations in the database with the ones built into it:

The database has	What happens
Nothing this release doesn’t know	Starts
Migrations from one newer release	Starts, with a warning naming both versions. This is a rolled-back update. It migrates nothing, and the newer release finds its schema already there when it comes back
Migrations from two or more newer releases	Refuses to start, naming this release, the newest migration it knows, the newest in the database and the releases that wrote them

The refusal reads:

refusing to start: this database's schema is more than one release ahead of
HotLoop 4.18.10. 0022_x.sql is the newest migration this release knows, and the
database has 2 it does not (up to 0024_z.sql), written by 4.19.0 and 4.20.0.
Only the release right after this one is known to work on that schema. Start
HotLoop 4.20.0 or newer against this database (an update to :stable gets
there), or restore a backup taken by HotLoop 4.18.10 or earlier

On a box that follows :stable the next update fixes it, because :stable moves to the newest release, which knows every migration. The unit then sits “activating” until it does. That is on purpose: a box that is down and says why beats a box that is up and wrong.

The guard runs whether or not the start migrates (HOTLOOP_DB_MIGRATE), and on a restore, which opens the database the same way. A database from before the guard has no app_version column and can’t hold a migration newer than the release that is reading it, so it starts; the column is added on the first start of a release that has the guard.

Proving it for a real release

The check proves the SQL. test/prevrelease.sh proves the release: it builds this checkout, lets it migrate a fresh database and make data on a real OPC UA server, then starts the previous release’s published image on that database and runs its login, devices, tags, a write through the gate and history. It is in the release checklist (RELEASE_CHECKLIST.md) and runs for PostgreSQL/TimescaleDB and for SQLite. A release that never had SQLite can’t be started on a SQLite file, so that half skips until one exists.

The migrations before this rule

The rule starts at 0022. The ones before it were audited when it was written (0001 to 0021, PostgreSQL’s and SQLite’s): none drops, renames or retypes anything, and the check passes them all with no exceptions. 0016 to 0021 were also run against the real 4.17.0 image, which knows only 0001 to 0015 (test/prevrelease.sh).

Shipped migrations are never edited. A mistake in one is fixed by the next.

A test holds me to that. internal/store/migrations/SHIPPED.sum lists every migration that exists at a released tag with the SHA-256 of its text (carriage returns removed, so a Windows checkout and a Linux one agree), and TestShippedMigrationsAreUnedited fails the build when a listed file changes or goes missing. Why it needs a test: a database that already ran the old text never runs the new one, so an edit quietly leaves two databases on the same version with two different schemas, and the one that breaks is the customer’s. A migration that isn’t listed is unreleased and can still change. The release adds the new ones to the list in the release commit (the step is in RELEASE_CHECKLIST.md, “The shipped migrations”).