Zylos LogoZylos
2026-11-05

When the Database Is Ahead of the Binary: Strict Migration Checks, Additive Schema Changes, and a Rollback Window for Small Services

researchdatabase-migrationsrollbackpostgresself-hostingai-agents

Executive Summary

A small self-hosted service was upgraded with one migration that only added a nullable text column. At startup the server checks that the set of applied migrations exactly equals the set embedded in its binary, and refuses to start if they differ. After the upgrade, the previous binary could not start, even though it would almost certainly have run fine on the new schema. The only documented rollback was to restore a pre-upgrade dump into a recreated database, which discards everything written since the upgrade. Nobody had rehearsed it.

The main findings:

  • Tools disagree about what "the database is ahead of me" means. Flyway ignores "future" migrations by default, Django skips unknown applied migrations in its consistency check, and Rails counts only files missing from the database as pending. sqlx (with an opt-out) and golang-migrate fail in that case. A strict set-equality check is at the conservative end, not the norm.
  • A strict "database ahead" check is a proxy: the tool cannot know whether older code works on the newer schema, but the migration's author often can. Checksum checks (edited history) and pending checks (binary ahead) guard different failures and should stay strict.
  • A nullable column is backward compatible for readers and writers that name their columns. It is less safe for positional select * consumers, views, strict row decoders, or a migration that bundles a trigger, policy or constraint. In PostgreSQL ADD COLUMN is cheap but still takes an ACCESS EXCLUSIVE lock.
  • For a small service with no orchestrator: keep the strict check by default, and add a compatibility path that the previous release already understands: a record written to the database when each migration is applied, binding its version and checksum to the binary releases that may run on it, plus a logged operator override, naming exact versions and checksums, that covers only a migration with no record after its compatibility has been checked separately. A record that excludes the running release, or a checksum mismatch, is refused even with the override. Metadata shipped only with the new binary cannot change the old binary's startup gate, so the gate has to ship before the release it is meant to protect. Do not delete history rows to make an old binary start. If that discipline is unaffordable, strict plus forward-only is legitimate, provided the restore is rehearsed and its data-loss window is written down.
  • For AI agent runtimes, where upgrades can be unattended and an agent following a runbook may choose the rollback path, a runbook whose only path is a destructive restore invites the wrong choice.

A note on evidence: every URL in Sources was opened in this session, but pages were read through a summarizing fetch tool, so quoted fragments are as extracted by that tool; the quotations from the sqlx migrator source, Flyway's rollback page and PostgreSQL's CREATE VIEW and ALTER TABLE pages were later rechecked against the raw pages. Where I could not confirm behaviour from a primary source, the text says so.

1. The situation, stated generally

The migration was a single alter table ... add column ... text; with no default and no data change. The server compares the migrations recorded in the database with those compiled into its binary and requires equality. After the upgrade the database had one more, so the old binary refused to start. The refusal did not come from anything the old code had tried and failed to do on the new schema; it was a policy encoded in the startup check, and it turned "rollback" into "restore from backup", the most expensive rollback there is.

2. What real tools do when the database has migrations the binary does not know

What I could confirm from primary sources; "not confirmed" means I could not verify it from a page I opened.

ToolDatabase has a migration the code does not knowSource read
sqlx (Rust)Migrator::run validates previously applied migrations and returns VersionMissing(version) unless ignore_missing is setSource on the main branch; docs.rs page for sqlx 0.9.0
Flywayvalidate classifies it as "missing" (error by default) or "future" (ignored by default)Redgate docs and blog
LiquibaseNot confirmed from a primary sourcevalidate page only
AlembicWraps a failed revision lookup in CommandError("Can't locate revision identified by ..."); exact trigger not confirmedscript/base.py
RailsNot an error in the pending check; shown as NO FILE in statusmigration.rb, migrations guide
golang-migrateUp/Migrate fail with "no migration found for version N" if the current version's file is absentmigrate.go
Djangocheck_consistent_history skips applied migrations it does not knowloader.py, 5.1 docs

sqlx

In the source I read, Migrator::run is documented as "Run any pending migrations against the database; and, validate previously applied migrations". run_direct takes a lock (locking defaults to true), returns MigrateError::Dirty(version) if a migration is dirty, then calls validate_applied_migrations, which begins:

if migrator.ignore_missing {
    return Ok(());
}

and otherwise returns MigrateError::VersionMissing(applied_migration.version) for any applied version not in the binary's set. The setter is documented as "Specify whether applied migrations that are missing from the resolved migrations should be ignored", but the field is #[doc(hidden)] and flagged "semver-exempt and may be changed or removed in any future version". The VersionMismatch checksum check sits later in run_direct, in the loop over the binary's own migrations, so ignore_missing does not bypass it.

So an sqlx-based service has a switch, but an unstable, global boolean, not a per-version decision.

Flyway

Flyway's ignoreMigrationPatterns is described as: "Ignore migrations during validate and repair according to a given list of patterns." The default is *:future. Redgate's explanation of validate says a missing file older than the newest it can find is labelled missing, newer ones are future, and "'Future' is ignored by default". Missing files make migrate or validate fail unless you run repair; a changed file gives a "migration checksum mismatch".

This is the key design point. A newly added migration is the newest, so older code sees it as "future", which Flyway ignores by default. Flyway separates "the database is ahead" (tolerated) from "history was deleted or edited" (an error). A strict set-equality check collapses the two.

Liquibase

The validate page says the command checks changelog structure, referenced files, duplicate id/author/file combinations, and "There are no checksum errors". It does not say whether validate compares against the history table, and I could not confirm from a primary source what update does with history rows that have no changeset. My recollection is that it ignores them, but that is memory.

Alembic

In alembic/script/base.py, _catch_revision_errors converts a ResolutionError into CommandError with "Can't locate revision identified by '%s'". The code I read does not show where the database's stored revision is resolved, so I cannot say from it that a database revision missing from scripts triggers this. Search results linked the message to that scenario, but I did not open those pages.

Rails

The pending check is directional: Migrator#pending_migrations is migrations.reject { |m| already_migrated.include?(m.version) }, so database versions with no file are not pending; migrations_status lists them as ********** NO FILE **********. The guide does not say whether db:migrate warns about orphan versions.

golang-migrate

Up and Migrate check for a dirty state first (ErrDirty). readUp and read then call versionExists for the current version, which returns fmt.Errorf("no migration found for version %d: %w", version, err) if neither the up nor down file exists. It tracks one current version, not a set, so an unknown version is detected only at the tip.

Django

check_consistent_history is documented as raising InconsistentMigrationHistory "if any applied migrations have unapplied dependencies". For migrations it does not know it contains # If the migration is unknown, skip it. Django's docs also state that migrations "should run unchanged on Django X.Y+1", but "does not promise forwards-compatibility".

3. Why strict checks exist, and what each one is for

It helps to separate three different questions a startup check can ask.

  1. Checksum or edited-history check. "Is every migration I applied still what I applied?" Catches edits after the fact. Flyway's validate and sqlx's VersionMismatch do this. It concerns known migrations only.
  2. Missing-locally check. "Does the database contain history I cannot explain?" Catches deleted or renamed files, unmerged branches, and pointing a build at the wrong database. It also catches the legitimate rollback case and cannot tell them apart.
  3. Pending check. "Does my code have migrations the database lacks?" Apply them (Migrator::run) or refuse to serve (Rails' PendingMigrationError).

Only check 2 is relevant to the incident, and only check 2 turns an additive change into a failed rollback. The honest rationale for failing on it is conservative: the tool has no way to know whether older code survives the newer schema, so it assumes it will not. That assumption is right for a drop or a rename and wrong for a nullable column. The checksum and pending checks should stay strict whatever you decide about check 2. (This classification is my own analysis, built from the behaviours above.)

4. Expand/contract and N-1 compatibility

Martin Fowler: "Parallel change, also known as expand and contract, is a pattern to implement backward-incompatible changes to an interface in a safe manner, by breaking the change into three distinct phases: expand, migrate, and contract." It "allows your code to be released in any of these three phases", which is what rollback needs: at the end of the expand phase the old release still works.

Practitioner guides turn this into rules. GitLab's guide says "Removing columns is tricky because running GitLab processes expect these columns to exist" and that ignoring and dropping a column must not happen "simultaneously in the same release". strong_migrations says "Active Record caches database columns at runtime, so if you drop a column, it can cause exceptions until your app reboots". Prisma's data guide says expand/contract "allows you to rollback changes easily at most points in the process", but warns that at cutover you are "in danger of losing data if you have to roll back".

What these sources collectively require is an N-1 rule: the previous release must run correctly on the schema produced by the current release's migrations. That is stricter than "the migration succeeds".

Which changes only look backward compatible

ChangeOld readerOld writerCaveat
Add nullable column, named-column SQLFineFineThe case in this article
Add nullable column, select * into positional or fixed-width mappingMay breakFineDepends on the driver or ORM (my reasoning)
Add nullable column, insert without a column listFineSee notePostgreSQL fills omitted trailing columns with defaults; the INSERT docs say "the first N column names" are used when fewer values are given
Add NOT NULL without a defaultFineBreaksOld writers do not supply the value
Add NOT NULL with a constant defaultFineFineOld writers get the default
Add column plus trigger, check or row-level security policy that uses itPossibly finePossibly breaksOld writers do not populate it; whether the new rule rejects them depends on the rule (my reasoning)
Rename, drop, change typeBreaksBreaksNeeds expand/contract across releases
Add a column to a table that an existing select * view readsUnchangedn/aThe view keeps the columns it was created with. The PostgreSQL CREATE VIEW docs: "Though * was used to create the view, columns added later to the table will not be part of the view."
Replace that view with create or replace view ... select *Like select * on the tablen/aThe fresh * expansion can add the new column, but only at the end: the new query "must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list."

The select * and ORM-mapping rows are where teams get surprised. I did not test any driver, so treat those cells as hypotheses to check in your own stack. The two view rows I did check on a local PostgreSQL 18 container: after alter table a add column note text, a view created earlier as select * from a still listed id,x; create or replace view va as select * from a then listed id,x,note. A view over select * from a join b using (id) could not be replaced the same way, because a's new column would land in the middle: ERROR: cannot change name of view column "y" to "note".

What ADD COLUMN does in PostgreSQL

From the PostgreSQL 18 ALTER TABLE page: "An ACCESS EXCLUSIVE lock is acquired unless explicitly noted." For ADD COLUMN with a non-volatile default the value is stored in metadata, and "If no column constraints are specified, NULL is used as the DEFAULT. In neither case is a rewrite of the table required." The PostgreSQL 11 release notes introduced this for constant defaults. A volatile default, stored generated column, identity column, or constrained domain type "will cause the entire table and its indexes to be rewritten".

So a nullable column is quick and compatible, but the statement still needs the strongest lock briefly. From my own knowledge (not a page I opened), if a long transaction holds a lock on the table, the ALTER waits and other queries can queue behind it. lock_timeout is the standard guard: it aborts "any statement that waits longer than the specified amount of time while attempting to acquire a lock", and the docs advise against setting it globally.

5. Designs for a small service without an orchestrator

Candidate designs and what each costs:

DesignHow it worksCost and risk
A. Strict check, forward-onlyKeep set equality. Rollback means a fix-forward release or a restoreCheapest to build. A restore loses everything written after the restore point, so a rollback is a data-loss event unless nothing was written since
B. Global "ignore missing" flagOne switch to tolerate unknown applied migrations (as set_ignore_missing in sqlx)Easy, but removes the wrong-database protection for everyone, and it applies to destructive migrations too
C. Minimum compatible schema version, compiled inThe binary embeds the lowest schema version it supports; any newer schema is acceptedSmall to implement, but it cannot protect a rollback on its own. The old binary's floor was fixed before the newer migrations existed, so it accepts a later drop or rename as readily as an added column, and a floor raised in the new binary never reaches the old one
D. Compatibility record per migration, stored in the databaseEach migration carries a declaration, for example compat: additive or compat: breaks-before=N. When the runner applies it, it also writes a record in the same transaction binding that migration's version and checksum to the binary releases allowed to run on it. At startup a binary accepts an applied migration it does not embed only if a record exists for that version, the record's checksum matches the history row, and its own release is in the declared range; otherwise it refusesBest precision. Works only if the previous release already contains this gate: it never sees the new migration file, and metadata shipped only with the new binary cannot change its startup check. Needs review discipline and a CI check that the declaration is true
E. Explicit operator override--allow-schema-ahead=<version>:<checksum> or an environment variable, logged loudly; the missing check is skipped only for applied rows that have no compatibility record and whose version and checksum match the named ones. It cannot accept a row whose record excludes the running release, or whose checksum differsCheap; leaves the decision with a human or runbook, does not rely on declarations being correct. Like D, it helps only if the previous release already implements the flag
F. Down migrationsEach up migration has a reversal (sqlx -r, Rails reversible change, Flyway undo)Doubles the surface to maintain and test. Flyway's own docs say "Rollback scripts (aka undo scripts) are no substitute for having a robust backup/restore strategy" and that dropped data cannot be restored by undo scripts
G. Point-in-time recoveryBase backup plus archived WALLets you rewind to a chosen moment, but the PostgreSQL docs say pg_dump dumps "cannot be used as part of a continuous-archiving solution"; PITR is a separate setup

Flyway's rollback page frames the choice: "Rolling back restores a previous schema state as if the later schema state never happened." By contrast, "Rolling forward acknowledges that the later state (v4) occurred, and migrates ahead to a new distinct version (v5) that just happens to correspond to a previous state (v3)."

Recommendation

For a small service where one person or one agent operates the deployment:

  1. Keep the checksum and pending checks strict. They guard different failures and cost nothing in this scenario.

  2. Replace "exact equality" with "equality unless explicitly allowed", and ship the allowance before it is needed. Concretely, combine D and E: the runner records each migration's compatibility declaration in the database, bound to its version and checksum, and the binary decides each unknown applied migration on its own, in this order:

    • Checksum mismatch: always refused. The history row's checksum must equal the record's (if there is a record) and the override's (if one names the migration).
    • A record that excludes the running release: always refused. An override cannot turn an explicit "no" into "yes".
    • A record that includes the running release: accepted, with no override needed.
    • No record: refused, unless an override names that exact version and checksum. This is the one case the override exists for, for example a migration applied by a release that predates recording. The operator passes it only after checking compatibility independently, by running the previous release against this schema as in section 6. From a version and checksum alone the binary cannot tell an added column from a drop or rename, so that check, not the flag, is what keeps destructive changes out (step 4).

    One refusal stops startup. The checksum and pending checks stay enforced on every path, and the log records which path each migration took. The rollback target is a binary that is already released, so this gate must be in that release: the first release that contains it can be rolled back to this way, earlier releases cannot.

  3. Make the declaration verifiable. A CI job (section 6) runs the previous release's tests against the new schema. If the declaration says the previous release may run on it and the job fails, the build fails.

  4. Never let the compatibility rule cover drops, renames or type changes. Those need expand/contract across two releases, and an override must never be used for them either: it covers a missing declaration for a change that has been checked, not a known breaking one.

  5. Keep the history row; do not delete it to make the old binary start. Deleting the row of an applied migration records an applied change as absent, and a later forward upgrade can apply it again: sqlx's run_direct, for example, applies every embedded migration that has no history row. Writing the migration as add column if not exists only avoids the duplicate-name error for that one statement. PostgreSQL's ALTER TABLE docs say "If IF NOT EXISTS is specified and a column already exists with this name, no error is thrown." It does not check that the existing column has the original definition, and it does nothing for any other effect of the migration (an index, a backfill, a trigger), which would run again or fail. On a local PostgreSQL 18 container, alter table t add column if not exists note text against an existing note integer not null default 0 printed NOTICE: column "note" of relation "t" already exists, skipping and left the column as a non-null integer. Use a supported mechanism that leaves the original row in place: the gate or override from step 2, or, in sqlx, an old binary built with set_ignore_missing(true) (with the caveats of design B). With the row intact, run_direct in the re-upgraded binary finds the version already applied and only compares checksums, so nothing is replayed. If the previous release has no such mechanism, as in the incident, the rollback policy is a fix-forward release or the rehearsed restore described below.

When is plain strict plus forward-only the right call? When changes are frequent and mostly non-additive, so a compatibility declaration would usually be "no"; when data is small and restore takes seconds; when one operator owns everything; or when you cannot yet afford the CI job below. Then be explicit: say in the upgrade notes that rollback is a restore, take the dump automatically at upgrade, and state the data-loss window. An unrehearsed forward-only policy that nobody wrote down is the failure in this article.

6. Verifying a rollback path without believing it

A rollback path that has never run is a hypothesis. Three checks turn it into evidence.

Old binary against new schema in CI. Take the previous release's published binary, unmodified (not a rebuild with the check disabled), apply the new release's migrations to a fresh database (with the previous release's own seed data), and start the previous binary through the path it actually supports: its compatibility gate reading the recorded declarations, or its override flag. Then run its normal integration tests or at least a smoke test covering reads and writes of each table. This is the test that would have detected the incident and also tells you whether a "compatible" declaration is true. It also covers the select *, positional mapping and trigger concerns from section 4, for your actual stack. Exercise the step 2 rule with and without the override. A migration whose record excludes the previous release, or whose history-row checksum does not match the record or the override, must stop the previous binary from starting even when an override names it. A migration with no record must be refused without the override and accepted with one that names its exact version and checksum.

Restore drills. Take a real dump, restore it into a scratch instance, start the old binary against it, and time each step. Record commands and manual fix-ups. For PostgreSQL, a logical dump captures one moment; point-in-time recovery needs continuous archiving, and the docs say pg_dump dumps "cannot be used as part of a continuous-archiving solution".

Measure and write down the data-loss window. For a restore at the worst plausible moment, compute how much would be lost: time since the last dump, rows or sessions written since, and what cannot be recovered elsewhere (messages already sent, external systems). Put the number in the upgrade note, because it changes whether someone chooses to restore.

Also test the exit: after the old binary runs on the new schema, start the new release again and check that it does not replay the migration: the history table still holds exactly one row for that version with the original checksum, and the migration's effects are not applied twice.

7. Why this matters for AI agent runtimes

This section is my own analysis, with general sources where noted.

Unattended upgrades. If the upgrade changes schema and binary in one step, the safe state is "both exist": a snapshot taken before, the recorded previous version, and a documented way back. The runbook must live outside the thing being upgraded, since a runtime that cannot start cannot read its own memory.

Long-lived sessions. An agent runtime's database often holds conversation state, task queues and memory. Restoring a pre-upgrade dump deletes work the agent already acknowledged to people. Anthropic's post on agents notes "the potential for compounding errors" and recommends agents "pause for human feedback at checkpoints or when encountering blockers" with testing "in sandboxed environments, along with the appropriate guardrails". A destructive rollback is checkpoint-worthy.

Picking a path without a human nearby. An agent following a runbook that says "to roll back, restore the dump" will do it, because it is the documented path. The runbook has to rank paths by what they destroy. A workable shape:

  1. A read-only diagnosis step: which applied migrations does this binary not know, and does each have a recorded declaration, with a matching checksum, that includes this release? List separately those with no record and those whose record excludes this release or whose checksum does not match.
  2. A reversible step first, under the same rule as section 5 step 2. If the previous release implements the gate, and every unknown migration either has a matching record that includes this release or has no record and has passed the section 6 check so that the override may name it, start it through that path, with a fresh snapshot taken before and the history rows left as they are. A migration whose record excludes this release, or whose checksum does not match, cannot be overridden; then, or if the previous release has no gate, the choice is a fix-forward release or step 3.
  3. A destructive step (restore) gated on an explicit statement of the data-loss window and a confirmation from the owner, because it cannot be undone.
  4. Speed matters (Google's SRE workbook values making rollback "cheaper and easier"), but the chapter I read does not discuss schema changes; weighing speed against state loss is my own reasoning.

An override should be a flag, not a procedure: a single logged flag is something a runbook can state and an audit can find, whereas a hand-edited history table is something an unattended agent should not improvise.

8. What I could not confirm

  • Liquibase's handling of history rows with no changeset, and Alembic's exact trigger for "Can't locate revision", are unconfirmed.
  • Whether Rails' db:migrate objects to orphan versions is not stated in what I read.
  • The compatibility gate and override designs in section 5, PostgreSQL lock queueing, ORM column mapping, and trigger/RLS interactions with old writers are my own reasoning. Apart from the PostgreSQL 18 view and add column if not exists checks described in sections 4 and 5, I ran no database or ORM test, and no rollback drill.
  • The sqlx points come from main-branch source, which I also read directly, and the docs.rs page for 0.9.0; a given service may use another version.

Sources