All demos/ Liquibase adoption

DEMO-04-LQBSEP 2026

Retrofit writes the rollback. Liquibase runs it.

Retrofit imports a dump of the schema Liquibase built, renders the two migrations that add and validate a foreign key, and their rollbacks, from one operation log, and 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 project35 secImport a schema built by 1,222 changesets, and seal the operation log as the baseline. 02 Forward and rollback, deployed by Liquibase1 min 29 secRender the forward and the rollback of each cut from the same log, so the two cannot disagree, and let Liquibase deploy them. 03 The rollback that does nothing51 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 written52 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 lifecycle6 min 08 secThe 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.

Liquibase runs whatever a changeset’s <rollback> block says. Its built-in change types include addForeignKeyConstraint, which Liquibase reverses itself, but nothing that validates a constraint, and a raw SQL changeset gets no generated rollback. So the foreign key is Liquibase’s to roll back, and its validation is SQL written by hand with a rollback written by hand. A rollback runs when something has already gone wrong, and a wrong one adds to the damage, dropping data or taking the lock and the scan the staged rollout existed to avoid.

Metabase is our starting point, and 1,222 Liquibase changesets build its schema. Those changesets are YAML rather than SQL, so we import the schema they produced: a schema-only pg_dump, which retrofit import reads as 4,454 operations without running anything against a database. It carries DDL and no DML, and the rows the recording scans are a fixture. We seal the log as the baseline, and identify confirms it and the database agree.

In Metabase’s schema, ai_usage_log.user_id is indexed and points at core_user.id, and no foreign key enforces it. Added in one step, the key would scan every row while holding a SHARE ROW EXCLUSIVE lock on both tables (PostgreSQL’s ALTER TABLE). Ambler and Sadalage called that “a significant problem with tables with millions of rows”, and put a check of the row count against the join count ahead of adding the key (Refactoring Databases, 2006, pp. 207 and 208).

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. Retrofit renders forward and rollback for both cuts from the same log, and Liquibase deploys them as two changesets whose <rollback> blocks we did not write.

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. Liquibase reports the changeset rolled back and removes its 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 reads the ledger and says the validation is not applied, while identify reads a fresh dump and confirms the database is still at the validation seal. The ledger and the database no longer agree, and identify is working from the schema as the dump shows it, not from a ledger. 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.

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 136 of them. 128 are the option lists behind identity columns, START WITH 1 INCREMENT BY 1 and so on, which PostgreSQL sets to the same values without them; 8 are indexes on expressions such as lower(email), which the log does not carry today. Nothing else in 18,000 lines of Metabase DDL is outside the log, so we import again with --relaxed, 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.

35 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' > .runtime/metabase-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 -C .runtime/retrofit import ../metabase-baseline.sql --schema public -q > .runtime/import.log

exit status 2

136 statements carry content the log does not model: 128 identity-column sequence options, which PostgreSQL defaults to the same values, and 8 indexes on expressions. 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 -C .runtime/retrofit import ../metabase-baseline.sql --schema public --force --relaxed --seal=baseline --summary-file ../import-summary.json --skip-manifest ../import-skips.jsonl -q > .runtime/import.log

cat .runtime/import-summary.json

4,454 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 .runtime/metabase-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 in the dump as it is and runs nothing against a database.
The import stops with exit status 2 rather than drop content silently: 128 identity-column option lists that PostgreSQL sets to the same values anyway, and 8 indexes on expressions. Everything else in the dump is in the log.
4,454 operations, ending in a seal. The identity columns are in the log; only their default option lists are not. Liquibase's ledger still has 1,222 rows.
Read the same dump back against the log. identify confirms the two agree at 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 cut. retrofit migrate renders the forward and the rollback of each cut from the same log; the validation changeset’s rollback is three comment lines 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 29 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 references=core_user on_delete=set_null name=fk_ai_usage_log_user_id --column=public.ai_usage_log.user_id --ref_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 cut:

retrofit op remove last -q

retrofit op add AddForeignKey ai_usage_log references=core_user on_delete=set_null name=fk_ai_usage_log_user_id not_valid=true --column=public.ai_usage_log.user_id --ref_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 cut:

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 cut:

retrofit migrate --from seal~2 --to seal~1 --envelope caller --allow-external-atomicity -q > .runtime/liquibase/001-add-fk.sql

retrofit migrate --from seal~2 --to seal~1 --down --strict --envelope caller --allow-external-atomicity -q > .runtime/liquibase/001-add-fk.rollback.sql

retrofit migrate --from seal~1 --to seal --envelope caller --allow-external-atomicity -q > .runtime/liquibase/002-validate.sql

retrofit migrate --from seal~1 --to seal --down --strict --envelope caller --allow-external-atomicity -q > .runtime/liquibase/002-validate.rollback.sql

the key’s rollback drops it. the validation’s rollback is three comment lines and no SQL:

grep -h '^ALTER TABLE\|^-- reverse-noop' .runtime/liquibase/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 .runtime/liquibase/changelog.xml

grep -n 'changeSet\|sqlFile' .runtime/liquibase/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' > .runtime/now.sql

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

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

Added in one step, the key would scan every existing row and hold a SHARE ROW EXCLUSIVE lock on both tables, per PostgreSQL's ALTER TABLE reference. The advisory says to author it NOT VALID and validate later.
Author the key NOT VALID and seal it. Count the rows and count the join, over 5,000,000 rows, then author the validation as a second cut.
Forward and rollback for each cut, from the same log. The validation changeset's rollback is three comment lines 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.

51 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' > .runtime/now.sql

retrofit identify .runtime/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' .runtime/liquibase/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' > .runtime/now.sql

retrofit identify .runtime/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 changeset is not applied; the dump says the constraint is validated. identify confirms the database is still at the validation seal, because it works from the schema's shape rather than from a ledger. The three reverse-noop lines say 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. Retrofit is not in this chapter.

52 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 .runtime/liquibase/

cat .runtime/liquibase/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' .runtime/liquibase/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' > .runtime/now.sql

retrofit identify .runtime/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. nothing runs and it stays validated; do not drop and re-add; re-validating is free while the constraint survives:

grep '^-- reverse-noop' .runtime/liquibase/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 three generated lines 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 cuts 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 both cuts back, with identify confirming at every stop that the log and the database agree.

6 min 08 sec 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 .runtime artifacts 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' > .runtime/metabase-baseline.sql

cp inputs/retrofit.toml .runtime/retrofit/retrofit.toml

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

retrofit -C .runtime/retrofit import ../metabase-baseline.sql --schema public -q > .runtime/import.log

exit status 2

136 statements carry content the log does not model: 128 identity-column sequence options, which PostgreSQL defaults to the same values, and 8 indexes on expressions. we import again with the skips accepted, and seal the point Retrofit’s log starts from:

retrofit -C .runtime/retrofit import ../metabase-baseline.sql --schema public --force --relaxed --seal=baseline --summary-file ../import-summary.json --skip-manifest ../import-skips.jsonl -q > .runtime/import.log

cat .runtime/import-summary.json

4,454 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 .runtime/metabase-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 references=core_user on_delete=set_null name=fk_ai_usage_log_user_id --column=public.ai_usage_log.user_id --ref_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 references=core_user on_delete=set_null name=fk_ai_usage_log_user_id not_valid=true --column=public.ai_usage_log.user_id --ref_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 cut:

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 cut:

retrofit migrate --from seal~2 --to seal~1 --envelope caller --allow-external-atomicity -q > .runtime/liquibase/001-add-fk.sql

retrofit migrate --from seal~2 --to seal~1 --down --strict --envelope caller --allow-external-atomicity -q > .runtime/liquibase/001-add-fk.rollback.sql

retrofit migrate --from seal~1 --to seal --envelope caller --allow-external-atomicity -q > .runtime/liquibase/002-validate.sql

retrofit migrate --from seal~1 --to seal --down --strict --envelope caller --allow-external-atomicity -q > .runtime/liquibase/002-validate.rollback.sql

we render all four again and compare the bytes:

retrofit migrate --from seal~2 --to seal~1 --envelope caller --allow-external-atomicity -q > .runtime/verify/001-add-fk.sql

retrofit migrate --from seal~2 --to seal~1 --down --strict --envelope caller --allow-external-atomicity -q > .runtime/verify/001-add-fk.rollback.sql

retrofit migrate --from seal~1 --to seal --envelope caller --allow-external-atomicity -q > .runtime/verify/002-validate.sql

retrofit migrate --from seal~1 --to seal --down --strict --envelope caller --allow-external-atomicity -q > .runtime/verify/002-validate.rollback.sql

for f in .runtime/liquibase/00*.sql; do cmp "$f" .runtime/verify/$(basename "$f"); done

byte-identical. the key’s rollback drops it. the validation’s rollback is three comment lines and no SQL:

grep -h '^ALTER TABLE\|^-- reverse-noop' .runtime/liquibase/00*.sql

cp inputs/changelog.xml .runtime/liquibase/changelog.xml

grep -n 'changeSet\|sqlFile' .runtime/liquibase/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' > .runtime/now.sql

retrofit identify .runtime/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' > .runtime/now.sql

retrofit identify .runtime/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' .runtime/liquibase/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' > .runtime/now.sql

retrofit identify .runtime/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 .runtime/liquibase/

cat .runtime/liquibase/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' .runtime/liquibase/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' > .runtime/now.sql

retrofit identify .runtime/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. nothing runs and it stays validated; do not drop and re-add; re-validating is free while the constraint survives:

grep '^-- reverse-noop' .runtime/liquibase/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' > .runtime/now.sql

retrofit identify .runtime/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' > .runtime/now.sql

retrofit identify .runtime/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.