All demos/ Liquibase adoption

DEMO-04-LQBSEP 2026

Retrofit writes the rollback. Liquibase runs it.

Given a dump of the schema Liquibase built, retrofit imports it and renders the two migrations that add and validate a foreign key, and their rollbacks, from one operation log; Liquibase deploys them. One of those rollbacks runs nothing, because PostgreSQL cannot unvalidate a constraint.

Stack: Liquibase + PostgreSQL
Change: AddForeignKey, NOT VALID
Reverse: generated; one a classified no-op

Chapters

01 Add retrofit to a Liquibase project41 secImport a schema built by 1,222 changesets, and seal the operation log as the baseline. 02 Forward and rollback, deployed by Liquibase1 min 48 secRender the forward and the rollback of each change from the same log, so the two cannot disagree, and let Liquibase deploy them. 03 The rollback that does nothing1 min 04 secPostgreSQL has no statement that unvalidates a constraint, and the one path back drops the key first, so the rollback that runs nothing is the one to deploy. 04 The rollback we might have written56 secThe obvious rollback to write drops the key and adds it back unvalidated, leaving a window with nothing enforcing it and a second full scan to pay. 05 Prove the entire lifecycle8 minThe whole sequence in one run, with identify checking, after every move Liquibase makes, that the log and the database agree.

Summary

Your migrations run through Liquibase. It writes the rollback for the changes it can reverse and runs whatever you wrote for the rest, and nothing checks that what you wrote undoes the forward, or that the forward can be undone at all.

Metabase builds its schema with 1,222 Liquibase changesets, written in YAML rather than SQL. So we import the schema they produced: a schema-only pg_dump, which retrofit import reads as 4,462 operations without touching a database. Then we add the foreign key the schema is missing: ai_usage_log.user_id is indexed and points at core_user.id, and nothing enforces it. Added in one step, the key would scan every row under a SHARE ROW EXCLUSIVE lock on both tables. Liquibase reverses addForeignKeyConstraint on its own, but a validation is raw SQL, and a raw SQL changeset gets no generated rollback. A rollback runs when something has already gone wrong, and a wrong one adds to the damage.

We create the foreign key with PostgreSQL’s NOT VALID option, so the constraint checks new writes immediately and defers the full-table scan of existing rows to a later validation step. From one operation log, retrofit renders forward and rollback for both changes, and Liquibase deploys them as two changesets whose <rollback> blocks we did not write.

Rolling back the validation changeset is ordinary Liquibase work. Liquibase removes its ledger row, and the constraint stays validated, because PostgreSQL cannot unvalidate a constraint and the generated rollback is an explicit no-op that says so. liquibase status now says the validation is not applied, while identify reads a fresh dump and confirms the database still carries it. The next liquibase update runs VALIDATE CONSTRAINT again and PostgreSQL scans nothing.

The fourth chapter runs the rollback we might have written by hand instead: drop the key and add it back NOT VALID. Liquibase runs it just as cleanly, and the next liquibase update takes the lock and scans all 5,000,000 rows again. Below are the changelog we write and that hand-written rollback.

The changelog we write

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
  <changeSet id="001-add-fk" author="retrofit">
    <sqlFile path="001-add-fk.sql" relativeToChangelogFile="true" splitStatements="true"/>
    <rollback><sqlFile path="001-add-fk.rollback.sql" relativeToChangelogFile="true" splitStatements="true"/></rollback>
  </changeSet>
  <changeSet id="002-validate" author="retrofit">
    <sqlFile path="002-validate.sql" relativeToChangelogFile="true" splitStatements="true"/>
    <rollback><sqlFile path="002-validate.rollback.sql" relativeToChangelogFile="true" splitStatements="true"/></rollback>
  </changeSet>
</databaseChangeLog>

The rollback we might have written

-- the hand-written rollback for the validate: drop the key, put it back NOT VALID
ALTER TABLE public.ai_usage_log DROP CONSTRAINT fk_ai_usage_log_user_id;
ALTER TABLE public.ai_usage_log ADD CONSTRAINT fk_ai_usage_log_user_id FOREIGN KEY (user_id) REFERENCES public.core_user (id) ON DELETE SET NULL NOT VALID;

Chapter 01Add retrofit to a Liquibase project

Can a Liquibase project take on retrofit without touching a changeset?

Metabase’s 1,222 changesets are Liquibase YAML rather than SQL, so the input is the schema they produced: a schema-only pg_dump of the database, DDL and no DML. The database and its rows stay in place; this demo captures the schema and manages no data with retrofit.

retrofit import reads every statement in the dump and stops with exit status 2, because it would have to drop content from 128 of them: the option lists behind identity columns, START WITH 1 INCREMENT BY 1 and so on, which PostgreSQL sets to the same values without them. Nothing else in 18,000 lines of Metabase DDL is outside the log, so we import again with --allow-capability-gaps, which accepts those skips, and seal the operation log as the baseline. Liquibase’s ledger has not changed.

identify then reads the same dump back against the log and confirms the two agree at the baseline seal: the import captured the schema as it stands. The later chapters use the same gate after Liquibase has moved the database.

41 sec space to pause f for fullscreen

Read transcript

liquibase-adopt-retrofit-demo: adopt liquibase project

Generated from the demo script. Every command below is run verbatim by the recording.

1/4 a schema Liquibase built

Metabase’s image applied 1,222 Liquibase changesets to this database. all of them stay:

psql "$DB" -X -Atq -c "SELECT count(*) || ' changesets in databasechangelog' FROM databasechangelog"

psql "$DB" -X -Atq -c "SELECT count(*) || ' tables in public' FROM pg_tables WHERE schemaname = 'public'"

Metabase’s changesets are Liquibase YAML, not SQL, so we capture the schema they produced with pg_dump. import reads the DDL in that dump as it is and runs nothing against a database. pg_dump also writes a comment on the citext extension, which import skips and identify would count, so the dump drops that line:

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/baseline.sql

2/4 import, and read the exit

import reads the dump into an operation log. it exits 2 when a skip drops content:

retrofit import project/baseline.sql -q > project/import.log

exit status 2

128 statements carry content the log does not model: the sequence options behind identity columns, which PostgreSQL defaults to the same values. the 128 columns themselves are in the log.

3/4 accept the skips and seal

we import again with the skips accepted, and seal the point Retrofit’s log starts from:

retrofit import project/baseline.sql --force --allow-capability-gaps --seal=baseline --summary-file project/import-summary.json --skip-manifest project/import-skips.jsonl -q > project/import.log

cat project/import-summary.json

4,462 operations, ending in a seal. Liquibase’s ledger has not changed:

retrofit op show --include-sealed -q | tail -1 | grep -o '^Seal([0-9a-f]*\|reason="[^"]*"'

psql "$DB" -X -Atq -c "SELECT count(*) || ' changesets in databasechangelog' FROM databasechangelog"

4/4 confirm the import with identify

identify reads a dump back against the log and confirms the two agree. on the dump we just imported, that is the baseline seal:

retrofit identify project/baseline.sql -q

Retrofit read the dump and wrote its own log. no changeset was edited, and the ledger and the database are as we found them.

Dump the schema Liquibase built, 1,222 changesets in. import reads the DDL as it is and runs nothing against a database.
import writes the dump’s DDL to the log as operations, and exits 2 when a skip drops content. 128 statements carry what the log does not model: the option lists behind identity columns, which PostgreSQL defaults the same way.
--allow-capability-gaps accepts the 128 skips; --seal seals the 4,462 operations as the baseline, the point retrofit’s log starts from. The identity columns are in the log; only their option lists are not. Liquibase’s ledger still has 1,222 rows.
identify reads a dump and reports the seal or operation it matches in the log. On the dump we just imported, that is the baseline seal: the import captured the schema as it stands.

Chapter 02Forward and rollback, deployed by Liquibase

Where does the rollback come from, and what makes it trustworthy?

We author the foreign key as a single step, and retrofit op add reports that validating it would scan and lock the table. We remove it and author it NOT VALID instead, count the rows against the join over 5,000,000 rows, and author the validation as a second migration. retrofit migrate renders the forward and the rollback of each change from the same log; the validation changeset’s rollback is one comment line and no SQL. An eight-line changelog points Liquibase at the four files, liquibase update deploys them, and identify --expect last on a fresh dump confirms the database is at the last seal.

1 min 48 sec space to pause f for fullscreen

Read transcript

liquibase-adopt-retrofit-demo: both directions

Generated from the demo script. Every command below is run verbatim by the recording.

1/4 author the key, and read the advisory

in Metabase’s schema, ai_usage_log.user_id is indexed and points at core_user.id, but no foreign key enforces it. the rule we want: a usage row’s user is null or a real user, and deleting a user clears the pointer. this is illustrative, not a proposal for Metabase. we author the key as a single step:

retrofit op add AddForeignKey ai_usage_log referenced_table=core_user on_delete=set_null name=fk_ai_usage_log_user_id --column=public.ai_usage_log.user_id --referenced_column=public.core_user.id -q

added in one step, the key checks every existing row while blocking writes to both tables. the advisory says how to avoid that: author it NOT VALID, and validate in a later step.

2/4 stage the key, check the rows, validate

we remove the plain key and author it NOT VALID. new writes are checked at once; the existing rows are checked in the next migration:

retrofit op remove last -q

retrofit op add AddForeignKey ai_usage_log referenced_table=core_user on_delete=set_null name=fk_ai_usage_log_user_id not_valid=true --column=public.ai_usage_log.user_id --referenced_column=public.core_user.id -q

retrofit op seal --reason 'protect ai_usage_log.user_id' -q

before validating, we count the rows with a user and count the join, over 5,000,000 rows:

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/orphan-check.sql

zero orphans. the validation is a second migration, sealed on its own:

retrofit op add ValidateConstraint fk_ai_usage_log_user_id -q

retrofit op seal --reason 'validate ai_usage_log.user_id' -q

3/4 both directions from one log

Liquibase runs each changeset in its own transaction, so we render with envelope=caller and attest that. forward and rollback for each migration:

retrofit migrate --from seal~2 --to seal~1 --envelope caller --allow-external-atomicity -q > project/src/main/resources/db/changelog/001-add-fk.sql

retrofit migrate --from seal~2 --at seal~1 --down --envelope caller --allow-external-atomicity -q > project/src/main/resources/db/changelog/001-add-fk.rollback.sql

retrofit migrate --from seal~1 --to seal --envelope caller --allow-external-atomicity -q > project/src/main/resources/db/changelog/002-validate.sql

retrofit migrate --from seal~1 --at seal --down --envelope caller --allow-external-atomicity -q > project/src/main/resources/db/changelog/002-validate.rollback.sql

each rollback notes that the log does not record it; Liquibase’s ledger records what runs. the key’s rollback drops it. the validation’s rollback is one comment line and no SQL:

grep -h '^ALTER TABLE\|^-- reverse-noop' project/src/main/resources/db/changelog/00*.sql

PostgreSQL cannot unvalidate a constraint, so that rollback runs nothing and says what state it leaves. the changelog is the one file we write. each changeset points at a generated file and its rollback:

cp inputs/changelog.xml project/src/main/resources/db/changelog/changelog.xml

grep -n 'changeSet\|sqlFile' project/src/main/resources/db/changelog/changelog.xml

4/4 Liquibase deploys

liquibase update-count 1 runs the first changeset. the key is installed and not yet validated:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase update-count --count=1

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-unvalidated.sql

liquibase update runs the second. the same constraint now reads validated:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase update

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-validated.sql

both changesets are rows in Liquibase’s ledger, after Metabase’s 1,222:

psql "$DB" -X -Atq -c "SELECT orderexecuted || '  ' || id || '  ' || exectype FROM databasechangelog ORDER BY orderexecuted DESC LIMIT 2"

identify –expect last on a fresh dump confirms the database is at the last seal:

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql --expect last -q

Retrofit rendered both directions of both migrations from one log, and Liquibase deployed them. we wrote the changelog.

op add appends one operation to the log; advisories arrive here, before any SQL exists. Added in one step, this key would scan every row under a SHARE ROW EXCLUSIVE lock on both tables, so the advisory says NOT VALID now and validate later.
A seal makes the operations since the last one tamper-evident and gives them an id migrate renders between. We remove the plain key, author it NOT VALID, and seal. Zero orphans in 5,000,000 rows; then the validation, sealed on its own.
migrate renders the SQL from one seal to the next; --down renders the reverse from the same operations, so the two cannot disagree. The validation’s rollback is one comment line and no SQL, because PostgreSQL cannot unvalidate a constraint.
Each generated file is a sqlFile in a changeset, its rollback in the rollback block. The catalog reads unvalidated, then validated, and identify --expect last on a fresh dump confirms the database and the log agree.

Chapter 03The rollback that does nothing

What happens when the reverse is not expressible?

The second Liquibase changeset, 002-validate, is one statement, VALIDATE CONSTRAINT, and after this release it is the most recent changeset, so rolling it back is ordinary work: liquibase rollback-count 1 runs its rollback block, as a release rollback by count or by tag would before the key’s. Liquibase removes the ledger row, the generated rollback runs nothing, and the constraint stays validated. liquibase status reads the ledger and identify reads a fresh dump, and they disagree about where the database is. liquibase update runs VALIDATE CONSTRAINT again and PostgreSQL scans nothing; both durations, as PostgreSQL logged them, are on screen.

1 min 04 sec space to pause f for fullscreen

Read transcript

liquibase-adopt-retrofit-demo: rollback that does nothing

Generated from the demo script. Every command below is run verbatim by the recording.

1/3 roll the validation back

both changesets are deployed and the key is validated. rolling back the most recent changeset is ordinary work, and after this release that changeset is the validation; a release rollback by count or by tag would run its block first, during an incident. we roll it back:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase rollback-count --count=1

Liquibase reports the changeset rolled back and removes its row. the constraint is still validated:

psql "$DB" -X -Atq -c "SELECT count(*) || ' rows in databasechangelog' FROM databasechangelog"

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-validated.sql

2/3 the ledger and the database disagree

liquibase status reads the ledger, which now says the validation is not applied:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase status

identify reads a fresh dump against the log, and says the database is still at the validation seal:

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql -q

identify works from the schema’s shape, so the ledger does not affect it. the rollback that ran is the one Retrofit rendered:

grep '^-- reverse-noop' project/src/main/resources/db/changelog/002-validate.rollback.sql

the validation changed no shape. it checked rows that were already there, and PostgreSQL keeps that result; there is no statement that unvalidates a constraint. dropping and re-adding the key would discard the check and repeat the scan, so the rollback runs nothing and says so.

3/3 liquibase update brings the ledger back

liquibase update runs the validation changeset again. PostgreSQL finds the constraint validated and scans nothing:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase update

both validates, with the durations PostgreSQL logged. the first scanned 5,000,000 rows:

./assertions/validate-durations.sh

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql --expect last -q

the ledger and the database agree again, and the scan was paid once.

The block a release rollback would run first, run on its own. Liquibase reports the changeset rolled back and removes its ledger row. The generated rollback ran nothing, and the catalog still says validated.
The ledger says the validation is not applied; the dump says the constraint is validated. identify reads the schema’s shape, not a ledger, and still reports the validation seal. The reverse-noop line says why nothing ran.
Liquibase runs the validation changeset again. PostgreSQL finds the constraint validated and scans nothing: the first validate and the re-run, with the durations PostgreSQL logged.

Chapter 04The rollback we might have written

What does the hand-written rollback cost, and why does nothing catch it?

Without retrofit, the rollback for 002-validate is written by hand, and the one most of us would reach for reads like an undo: drop the key, add it back NOT VALID. We run it through a second changelog that differs from the first only in that block, so the same database takes both rollbacks. Liquibase runs it without comment; its checksum covers the change, not the rollback.

PostgreSQL drops the constraint and adds it back unvalidated, so between the two statements nothing enforces the key. A row written in that window is never checked, because the key that comes back does not scan the rows already there; rows written after it comes back are checked as usual.

Afterwards the catalog reads unvalidated, liquibase status says the validation is not applied, and identify confirms the database matches the first seal: the ledger and the database agree with each other, which is why nothing reports a problem. The next liquibase update pays for it, taking the lock and scanning all 5,000,000 rows again, in the middle of a rollback. This chapter does not use retrofit.

56 sec space to pause f for fullscreen

Read transcript

liquibase-adopt-retrofit-demo: rollback we would have written

Generated from the demo script. Every command below is run verbatim by the recording.

1/3 the rollback we might have written

Retrofit is not in this chapter. this is the rollback most of us might write for the validation by hand: drop the key, add it back NOT VALID.

cp inputs/002-validate.handwritten.rollback.sql inputs/changelog-handwritten.xml project/src/main/resources/db/changelog/

cat project/src/main/resources/db/changelog/002-validate.handwritten.rollback.sql

a second changelog with the same changeset ids and only that rollback swapped. Liquibase’s checksum covers the change and not the rollback block, so the two files are the same to it:

grep -n 'logicalFilePath=\|path="002-validate.handwritten' project/src/main/resources/db/changelog/changelog-handwritten.xml

in a release rollback this block runs first, and the key’s own rollback then drops the key it just re-added, so both statements take their locks on both tables for nothing. we roll only the validation back with it:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase --changelog-file=changelog-handwritten.xml rollback-count --count=1

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-unvalidated.sql

2/3 the ledger and the database agree

the ledger says the validation is not applied, and now the database says the same:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase status

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql -q

identify confirms the database matches the first seal, so the gate passes. nothing reports a problem, and the check the validation did is gone.

3/3 liquibase update scans every row again

liquibase update runs the validation changeset again, and this time PostgreSQL has every row to check:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase update

every VALIDATE CONSTRAINT PostgreSQL has run on this table, in order: the first scan, the re-run after the generated rollback, and this one:

./assertions/validate-durations.sh

the generated rollback described this in one line: it leaves the constraint validated and runs nothing, so re-validating stays free while the constraint survives:

grep '^-- reverse-noop' project/src/main/resources/db/changelog/002-validate.rollback.sql

Liquibase ran what it was given, and PostgreSQL did what it was told. the difference between the two rollbacks is a lock and a scan, in the middle of a rollback.

Same changeset ids, only the validation changeset’s rollback swapped. Liquibase’s checksum covers the change and not the rollback block, so the two files are the same to it. The catalog reads unvalidated.
The ledger says the validation changeset is not applied, and now the database says the same. identify confirms the database matches the first seal, so the gate passes. The check the validation did is gone.
PostgreSQL has every row to check again. Every validate this table has had, in order, then the generated line read against what happened.

Chapter 05Prove the entire lifecycle

The uninterrupted run. We restore the vendored bed, watch retrofit import stop with exit status 2 and accept the skips, seal the baseline, read the advisory and re-author the key NOT VALID, render both migrations in both directions and compare the bytes, and let Liquibase deploy them. Then the generated rollback that runs nothing, the hand-written one and the scan it pays again, and finally liquibase rollback-count 2 to take the key out and liquibase update to put the key and its validation back, with identify confirming at every stop that the log and the database agree.

8 min space to pause f for fullscreen

Read transcript

liquibase-adopt-retrofit-demo: complete walkthrough

Generated from the demo script. Every command below is run verbatim by the recording.

KEEP=1: leaving the database and project/ in place

a real Liquibase project

Metabase’s image applies 1,222 Liquibase changesets to a fresh PostgreSQL. we vendor what it built, the schema and the changelog table, pinned by image digest and checksum:

sed -n '1,12p' inputs/MANIFEST.md

Retrofit is pinned by source revision:

retrofit --version

we start a disposable PostgreSQL 17 and restore both dumps:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml up -d --wait db

psql "$DB" -X -v ON_ERROR_STOP=1 -q -f inputs/metabase-v0.63.16.schema.sql

psql "$DB" -X -v ON_ERROR_STOP=1 -q -f inputs/metabase-v0.63.16.ledger.sql

then one user row and five million usage rows, so a validation has rows to scan:

psql "$DB" -X -v ON_ERROR_STOP=1 -q -f assertions/setup-fixture.sql

in Metabase’s schema, ai_usage_log.user_id is indexed, and no foreign key enforces it:

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/baseline-proof.sql

Liquibase is pinned by image digest as well:

docker compose -p "$COMPOSE_PROJECT" -f compose.yaml run --rm liquibase --version | grep -i 'liquibase version'

we will add a foreign key from ai_usage_log.user_id to core_user.id, so that deleting a user clears the pointer. the rule is illustrative, not a proposal for Metabase.

add Retrofit to a Liquibase project

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/baseline.sql

cp inputs/retrofit.toml project/retrofit.toml

retrofit schema create public project/ --is-default -q

import reads the dump into an operation log. it exits 2 when a skip drops content:

retrofit import project/baseline.sql -q > project/import.log

exit status 2

128 statements carry content the log does not model: the sequence options behind identity columns, which PostgreSQL defaults to the same values. we import again with the skips accepted, and seal the point Retrofit’s log starts from:

retrofit import project/baseline.sql --force --allow-capability-gaps --seal=baseline --summary-file project/import-summary.json --skip-manifest project/import-skips.jsonl -q > project/import.log

cat project/import-summary.json

4,462 operations, ending in a seal:

retrofit op show --include-sealed -q | tail -1 | grep -o '^Seal([0-9a-f]*\|reason="[^"]*"'

Liquibase’s ledger has not changed:

psql "$DB" -X -Atq -c "SELECT count(*) || ' changesets in databasechangelog' FROM databasechangelog"

identify reads a dump back against the log and confirms the two agree. on the dump we just imported, that is the baseline seal:

retrofit identify project/baseline.sql -q

the change, and why it is two migrations

we author the foreign key as a single step:

retrofit op add AddForeignKey ai_usage_log referenced_table=core_user on_delete=set_null name=fk_ai_usage_log_user_id --column=public.ai_usage_log.user_id --referenced_column=public.core_user.id -q

added in one step, the key checks every existing row while blocking writes to both tables. the advisory says how to avoid that: author it NOT VALID, and validate in a later step. we remove the plain key and author it NOT VALID:

retrofit op remove last -q

retrofit op add AddForeignKey ai_usage_log referenced_table=core_user on_delete=set_null name=fk_ai_usage_log_user_id not_valid=true --column=public.ai_usage_log.user_id --referenced_column=public.core_user.id -q

retrofit op seal --reason 'protect ai_usage_log.user_id' -q

before validating, we count the rows with a user and count the join, over 5,000,000 rows:

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/orphan-check.sql

zero orphans. the validation is a second migration, sealed on its own:

retrofit op add ValidateConstraint fk_ai_usage_log_user_id -q

retrofit op seal --reason 'validate ai_usage_log.user_id' -q

both directions, deployed by Liquibase

Liquibase runs each changeset in its own transaction, so we render with envelope=caller and attest that. forward and rollback for each migration:

retrofit migrate --from seal~2 --to seal~1 --envelope caller --allow-external-atomicity -q > project/src/main/resources/db/changelog/001-add-fk.sql

retrofit migrate --from seal~2 --at seal~1 --down --envelope caller --allow-external-atomicity -q > project/src/main/resources/db/changelog/001-add-fk.rollback.sql

retrofit migrate --from seal~1 --to seal --envelope caller --allow-external-atomicity -q > project/src/main/resources/db/changelog/002-validate.sql

retrofit migrate --from seal~1 --at seal --down --envelope caller --allow-external-atomicity -q > project/src/main/resources/db/changelog/002-validate.rollback.sql

each rollback notes that the log does not record it; Liquibase’s ledger records what runs. the key’s rollback drops it. the validation’s rollback is one comment line and no SQL:

grep -h '^ALTER TABLE\|^-- reverse-noop' project/src/main/resources/db/changelog/00*.sql

cp inputs/changelog.xml project/src/main/resources/db/changelog/changelog.xml

grep -n 'changeSet\|sqlFile' project/src/main/resources/db/changelog/changelog.xml

liquibase update-count 1 runs the first changeset. the key is installed and not yet validated:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase update-count --count=1

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-unvalidated.sql

liquibase update runs the second. the same constraint now reads validated:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase update

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-validated.sql

both changesets are rows in Liquibase’s ledger, after Metabase’s 1,222:

psql "$DB" -X -Atq -c "SELECT orderexecuted || '  ' || id || '  ' || exectype FROM databasechangelog ORDER BY orderexecuted DESC LIMIT 2"

identify –expect last on a fresh dump confirms the last seal:

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql --expect last -q

the rollback that does nothing

rolling back the most recent changeset is ordinary work, and after this release that changeset is the validation; a release rollback by count or by tag would run its block first, during an incident. we roll it back:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase rollback-count --count=1

Liquibase reports the changeset rolled back and removes its row. the constraint is still validated:

psql "$DB" -X -Atq -c "SELECT count(*) || ' rows in databasechangelog' FROM databasechangelog"

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-validated.sql

liquibase status reads the ledger, which now says the validation is not applied:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase status

identify reads a fresh dump against the log, and says the database is still at the validation seal:

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql -q

identify works from the schema’s shape, so the ledger does not affect it. the rollback that ran is the one Retrofit rendered:

grep '^-- reverse-noop' project/src/main/resources/db/changelog/002-validate.rollback.sql

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase update

both validates, with the durations PostgreSQL logged. the first scanned 5,000,000 rows:

./assertions/validate-durations.sh

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql --expect last -q

the rollback we might have written

Retrofit is not in this section. this is the rollback most of us might write for the validation by hand: drop the key, add it back NOT VALID.

cp inputs/002-validate.handwritten.rollback.sql inputs/changelog-handwritten.xml project/src/main/resources/db/changelog/

cat project/src/main/resources/db/changelog/002-validate.handwritten.rollback.sql

a second changelog with the same changeset ids and only that rollback swapped. Liquibase’s checksum covers the change and not the rollback block, so the two files are the same to it:

grep -n 'logicalFilePath=\|path="002-validate.handwritten' project/src/main/resources/db/changelog/changelog-handwritten.xml

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase --changelog-file=changelog-handwritten.xml rollback-count --count=1

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-unvalidated.sql

the ledger says the validation is not applied, and now the database says the same:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase status

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql -q

identify confirms the database matches the first seal, so the gate passes. nothing reports a problem, and the check the validation did is gone. liquibase update runs the validation again, and this time PostgreSQL has every row to check:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase update

every VALIDATE CONSTRAINT PostgreSQL has run on this table, in order: the first scan, the re-run after the generated rollback, and this one:

./assertions/validate-durations.sh

the generated rollback described this in one line: it leaves the constraint validated and runs nothing, so re-validating stays free while the constraint survives:

grep '^-- reverse-noop' project/src/main/resources/db/changelog/002-validate.rollback.sql

reverse the key, and reapply

now the release rollback itself. liquibase rollback-count 2 runs the validation’s block and then the key’s, which is real SQL, and stops at the demo’s changesets; Metabase’s rows are not touched:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase rollback-count --count=2

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-absent.sql

psql "$DB" -X -Atq -c "SELECT count(*) || ' changesets in databasechangelog' FROM databasechangelog"

identify confirms the database is back at the baseline:

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql -q

liquibase update puts both back from the same files, and the database is at the last seal again:

docker compose -p '$COMPOSE_PROJECT' -f compose.yaml run --rm liquibase update

psql "$DB" -X -v ON_ERROR_STOP=1 -P pager=off -f assertions/catalog-validated.sql

pg_dump "$DB" --schema-only --no-owner --no-privileges | grep -v 'COMMENT ON EXTENSION' > project/now.sql

retrofit identify project/now.sql --expect last -q

Retrofit rendered both directions from one log, and Liquibase ran them. where the rollback that leaves the database stable is to run nothing, the file said so, and what the alternative costs.