Tracking Schema Changes Without a Framework — Run-Once Scripts, Checksums and Immutability

Published Jul 7, 2026
by

The backfill ran a second time on the second deploy — and overwrote values that had been corrected by the business in the meantime. Accidents like this are not prevented by discipline, only by a memory: if you want to track database schema changes without introducing Flyway or Liquibase, all it takes is exactly one table and about 40 lines of shell — and along the way you make the same four design decisions that sit inside every migration tool.

The key points up front:

  • Why idempotent object files are not enough: data-dependent change scripts — backfill, conversion, data rescue — may run exactly once per database.
  • Design decision 1: the filename as the run-once key — a tracking table with a UNIQUE constraint remembers which script has already run.
  • Design decision 2: the checksum — an unchanged file means skip, a changed file means deploy abort.
  • Design decision 3: immutability — applied change scripts are never edited; every correction is a new script.
  • Design decision 4: the chicken-and-egg problem — the tracker itself has to exist before the first change script runs.
  • Why Flyway and Liquibase do exactly the same thing at their core — and where the limits of building it yourself lie: concurrent deploys, the transaction gap, missing tooling.

Prerequisite: Postgres and a bash with sha256sum — that is all the self-built tracker needs. Every procedure shown here is verified against Postgres 16 in a throwaway Docker container, including the checksum abort. The building blocks come from the open DI² Starter Kit on GitHub, where they run in production and under CI.

Contents

The Gap in the Idempotent Deploy

The convention-based deploy model from Deploying a SQL Schema Without a Migration Tool gets by without a migration chain: object files describe the desired state of each table, are phrased idempotently (CREATE TABLE IF NOT EXISTS, CREATE OR REPLACE), and run in full on every deploy. For structure, that works — an object file can run any number of times, and the result is always the same.

As soon as data enters the picture, a gap opens up. Not every data migration is affected — some can be phrased idempotently with care. But three classes of work resist the repeatable form, in whole or in part:

  • The backfill: existing rows receive a value for a new column — the classic middle step of the late NOT NULL. A WHERE guard (WHERE country_code IS NULL) does make the UPDATE repeatable, but the guard is hand-made safety: if it is missing, the second run overwrites what users have since corrected — the accident from the first paragraph.
  • The conversion: a script transforms existing values, say prices from euros to cents (SET price = price * 100). No guard helps here — after the first run, the row looks just as “unfinished” to the script as before. A second run multiplies every price by a hundred once more.
  • The data rescue: before a destructive structural change, values are set aside — say, the contents of a column that is dropped in the same deploy. If the rescue runs a second time, the source is already gone or empty.

All three need the same guarantee: exactly once per database — run-once. And this guarantee cannot live in the file itself; it needs a memory inside the database: a table that records which change script has already run here. Exactly such a table is what every migration tool keeps — Flyway calls it flyway_schema_history, Liquibase DATABASECHANGELOG. Build it yourself and you make four design decisions, one after the other — and afterwards you understand, as a side effect, why the frameworks behave the way they do.

Design Decision 1: the Filename as the Run-Once Key

The tracking table is deliberately small — one row per applied change script:

  1: CREATE TABLE IF NOT EXISTS app.schema_change_log
  2: (
  3:     id           bigint         NOT NULL GENERATED ALWAYS AS IDENTITY
  4:    ,filename     varchar(200)   NOT NULL
  5:    ,checksum     varchar(64)    NOT NULL
  6:    ,git_sha      varchar(64)    NOT NULL
  7:    ,applied_on   timestamptz    NOT NULL DEFAULT now()
  8:    ,applied_by   varchar(100)   NOT NULL DEFAULT current_user
  9:
 10:    ,CONSTRAINT pk_schema_change_log           PRIMARY KEY (id)
 11:    ,CONSTRAINT uq_schema_change_log_filename  UNIQUE (filename)
 12: );

The central decision sits in line 11: the filename is the run-once key. It is stored with its path (app/postdeploy/202607061000.backfill-customer-country-code.sql), and the UNIQUE constraint turns that identity into a database guarantee — the same file simply cannot be registered as applied twice. The timestamp prefix YYYYMMDDHHMM. in the name performs the second service: the alphabetical sort order of the files is at the same time their chronological execution order, without a version number to maintain anywhere. One consequence deserves to be said out loud: because the name is the identity, renaming an applied file turns it into a seemingly new script that has never run — the immutability we get to in a moment therefore covers the filename too.

The remaining columns are deploy metadata that pay for themselves at the first incident: applied_on and applied_by answer “when and by whom”, git_sha records which repository state the deploy ran from — an optional extra the frameworks do not keep in this form, but one that allows a direct jump to the matching code state when things go wrong. The checksum column gets its own section in a moment.

Registration goes through a small procedure instead of a hand-assembled INSERT — the values travel in as parameters, not as string concatenation:

  1: CREATE OR REPLACE PROCEDURE app.sp_ins_schema_change
  2: (
  3:     INOUT p_id            bigint
  4:    ,IN    p_filename      varchar
  5:    ,IN    p_checksum      varchar
  6:    ,IN    p_git_sha       varchar
  7: )
  8: LANGUAGE plpgsql
  9: AS $procedure$
 10: BEGIN
 11:
 12:    INSERT INTO app.schema_change_log
 13:        (
 14:            filename
 15:           ,checksum
 16:           ,git_sha
 17:        )
 18:    VALUES
 19:        (
 20:            trim(p_filename)
 21:           ,trim(p_checksum)
 22:           ,trim(p_git_sha)
 23:        )
 24:    RETURNING id INTO p_id;
 25:
 26: END;
 27: $procedure$;

After the first deploy, the backfill script from the opening example sits in the tracker exactly once — git_sha shows the fallback unknown here because the test run took place outside a git checkout:

                            filename                            | checksum   | git_sha | applied_by
----------------------------------------------------------------+------------+---------+------------
 app/postdeploy/202607061000.backfill-customer-country-code.sql | 589a8c00…  | unknown | postgres

Design Decision 2: the Checksum

The filename alone would have a weak spot: it only says that a file of this name has run — not what content it had at the time. So the deploy runner computes a sha256 checksum of the file when applying it and stores it alongside. On every further deploy it compares afresh — and exactly three cases fall out:

  1: PSQL=(psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -v ON_ERROR_STOP=1)
  2:
  3: name="app/postdeploy/$(basename "$file")"
  4: checksum="$(sha256sum "$file" | cut -d' ' -f1)"
  5:
  6: <em># What does the tracker know about this file?</em>
  7: <em># (the psql variable :'fname' quotes injection-safely — no string concatenation)</em>
  8: applied="$("${PSQL[@]}" -tA -v "fname=$name" -f - <<'SQL'
  9: SELECT checksum FROM app.schema_change_log WHERE filename = :'fname';
 10: SQL
 11: )"
 12:
 13: if [ -z "$applied" ]; then
 14:   <em># case 1: never ran — execute, then register as applied</em>
 15:   echo "applying $name"
 16:   "${PSQL[@]}" -f "$file"
 17:   "${PSQL[@]}" -v "fname=$name" -v "sum=$checksum" -v "sha=$GIT_SHA" -f - <<'SQL'
 18: CALL app.sp_ins_schema_change(NULL, :'fname', :'sum', :'sha');
 19: SQL
 20: elif [ "$applied" = "$checksum" ]; then
 21:   <em># case 2: already ran, file unchanged — skip</em>
 22:   echo "$name skipped (already applied)"
 23: else
 24:   <em># case 3: already ran, file edited afterwards — abort the deploy</em>
 25:   echo "Error: $name was applied with checksum $applied" >&2
 26:   echo "       but now hashes to $checksum." >&2
 27:   echo "       Applied change files are immutable — create a new file instead." >&2
 28:   exit 1
 29: fi

Case 1 and case 2 are everyday business. The second deploy acknowledges the backfill script with a single line and executes nothing again:

app/postdeploy/202607061000.backfill-customer-country-code.sql skipped (already applied)

Case 3 is the actual design decision. When the checksum of an already-applied file changes, silently skipping would be conceivable — or a warning. Instead, the runner aborts hard:

Error: app/postdeploy/202607061000.backfill-customer-country-code.sql was applied with checksum 589a8c00…
       but now hashes to 462a2bdd….
       Applied change files are immutable — create a new file instead.
exit code: 1

The abort is the right severity because an edited applied file is not a cosmetic flaw but a contradiction in the system: the existing environment was built with the old content, every new environment would be built with the new one — two databases claiming to be on the same state, and not being. A warning would log this contradiction and keep going; the abort forces it to be resolved before anything gets deployed. In the verified negative test, the tracker stayed untouched and the edited script was not executed — the deploy stops before damage is done.

Design Decision 3: Immutability

The checksum abort enforces a rule you should follow even without it: applied change scripts are immutable. If the backfill got a value wrong, you do not correct the old file — you write a new script with a new timestamp:

db/schemas/app/postdeploy/
├── 202607061000.backfill-customer-country-code.sql   ← applied, stays unchanged
└── 202607071130.fix-customer-country-code-at.sql     ← the correction is a new script

The reason lies in the two paths a database can take to its current state. An existing environment has already executed the old script — it only sees the correction script. A new environment replays both scripts chronologically on its first deploy: first the original backfill, then the correction. Both paths end in the same state, and the tracker documents seamlessly what happened when. An edited script would have destroyed this convergence — depending on when an environment was set up, it would carry different data.

“Applied” is the precise boundary of the rule. As long as a script has only run on your own development database, it may still change — the throwaway environment gets rebuilt anyway. It becomes immutable with its first application on a shared environment, at the latest with the merge. Flyway has the repair command for controlled exceptions, which resets the stored checksums — the self-built tracker deliberately has no counterpart; there, the exception is always: a new script.

For the same reason, applied scripts are never deleted or “cleaned up” either: they stay in the directory permanently, because every future greenfield environment needs the complete history. On existing environments they cost nothing anymore — the tracker skips them on every deploy with a single SELECT.

For all this to hold, every change script must bring one property with it: it must also run cleanly on an empty but current database. A WHERE guard turns the backfill into a no-op there (UPDATE 0), an IF EXISTS protects the data rescue from the already-missing source. In the verified greenfield run, exactly that went through: zero rows touched, constraint set, script registered as applied.

Design Decision 4: the Chicken-and-Egg Problem

The last decision is inconspicuous, but without it the very first deploy fails: the runner wants to ask the tracker before the first change script — yet on a pristine database, the table does not exist yet. The test against the empty container shows it unmistakably:

ERROR:  relation "app.schema_change_log" does not exist

What sharpens the problem is that change scripts do not only run after the object files: a data rescue has to kick in before the destructive structural change, that is, before the deploy’s tables section. So the tracker cannot rely on being created somewhere “in the middle” — it has to exist before everything else.

The solution uses the deploy model’s own division of labor: the tracking table and the insert procedure are themselves ordinary, idempotent object files — and the runner applies exactly these two files as the very first step of every deploy, before any change script. On an existing environment that is a no-op (IF NOT EXISTS and CREATE OR REPLACE kick in); on a new environment, the tracker comes into being at the very moment it is first needed. That the two files run a second time later, in the regular section order, is harmless for the same reason — in the verified double run, both applications went through cleanly.

Flyway solves this identically, just more invisibly: flyway_schema_history is created automatically before the first migration runs.

The Same Thing in Flyway & Co.

Once you have made the four decisions yourself, you read the frameworks’ version tables like an old acquaintance:

Design decisionSelf-builtFlyway (flyway_schema_history)Liquibase (DATABASECHANGELOG)
Run-once keyfilename with UNIQUEversion number + script nameID + AUTHOR + FILENAME
Checksumsha256CRC32 (checksum column)MD5SUM column
Immutabilitydeploy abort on mismatch“Migration checksum mismatch” on validate/migratechecksum verification on every update
Chicken-and-eggtracker object files run firsttable is created automaticallytable is created automatically

The often-feared Flyway error “Migration checksum mismatch” is thus not a framework annoyance but exactly design decision 2: someone edited an already-applied migration, and the tool refuses to ignore the contradiction. The documentation references: Flyway schema history table and Liquibase DATABASECHANGELOG.

The Limits of Building It Yourself

The self-built tracker delivers the core — run-once, checksum, abort — with one table and about 40 lines of shell, without a new dependency in the stack. But the picture only becomes honest with the limits, and two of them sit inside the shown core itself:

  • Concurrent deploys. Between the tracker query and the registration lies a window of time: if two deploys start simultaneously, both see “never ran” and execute the same script twice — the UNIQUE constraint only catches the second INSERT, not the second execution. The frameworks solve this with a lock table (Liquibase: DATABASECHANGELOGLOCK); in Postgres, a pg_advisory_lock at the start of the deploy is enough. The core shown here relies instead on the deploy pipeline running one deploy at a time — an assumption you should know about and enforce in CI.
  • The gap between execution and registration. Change script and CALL run as two separate psql invocations. If the deploy crashes exactly in between, the script has run but counts as not applied — and would run again on the next deploy. To close the gap, put script and registration into one shared transaction (psql --single-transaction with both files). The remaining limit is one the self-built tracker shares with the frameworks: statements like CREATE INDEX CONCURRENTLY tolerate no surrounding transaction.

On top of that comes the tooling a framework ships around its version table and the self-built tracker simply does not have: no repair command for controlled checksum corrections, no baselines for onboarding existing databases, no out-of-order migrations for teams working in parallel, no repeatable migrations, no rollback workflows (Liquibase), no multi-engine support, no programmatic migrations. If you have one engine, one team, and a shell-based, serialized deploy anyway, the self-built tracker takes you far; if you need several of these points, you are better served by the framework — and after this article, you understand it from the inside.

FAQ

How do you track schema changes in PostgreSQL?

For the deploy state, a tracking table is enough: one row per applied change script, with the filename as a UNIQUE key, a checksum, the git SHA, and a timestamp. It answers “which script has already run on this database”. A full DDL audit — who manually ran an ALTER TABLE, and when — is a different tool’s job: event triggers or the pgaudit extension handle that, not the deploy tracker.

Why does Flyway abort with “checksum mismatch”?

Because an already-applied migration file was changed afterwards. Flyway stores a checksum when applying and compares it afresh on every run — if it deviates, two truths exist: existing environments ran with the old content, new ones would get the new one. The abort forces the resolution. The clean way out: revert the change and ship it as a new migration.

May you edit an already-applied migration?

No — applied change scripts are immutable. Every correction becomes a new script with its own timestamp that carries the change forward. Only then do existing environments (which replay just the correction) and new environments (which replay the complete history chronologically) arrive at the same state. That holds for the self-built tracker just as it does for Flyway and Liquibase.

Do you need a framework to track database schema changes?

Not necessarily. The core — a run-once key, a checksum, an abort on modification, tracker-before-everything-else — fits into one table and about 40 lines of shell. A framework pays off when its extras are needed: rollback support, protection against concurrently running deploys, dry-run tooling, or support for multiple database engines.

What happens to the change scripts on a new, empty database?

They all run exactly once on the first deploy, in the chronological order of their timestamp prefix. That is why every script must also run cleanly on an empty but current database — a WHERE guard turns the backfill into a no-op there, an IF EXISTS protects against already-missing sources. Afterwards, the new environment’s tracker is filled just like that of every existing environment.