All demos/ Reference data

DEMO-07-REFSEP 2026

Keep reference data with the schema, and retire the side file.

Once a lookup table is declared as reference data, snapshot and migrate emit its rows as INSERT statements in the same script as its DDL, and the .sql side file that held them is retired. In the log, retrofit records the declaration, and data capture writes the rows to a data file pinned to it. A rename leaves the rows intact, and each data file is witnessed and sealed, so a hand edit to one fails validate.

Stack: PostgreSQL + git
Change: RenameTable and RenameColumn under captured rows, a row added by hand, a row corrected by data capture
Reverse: none shown; every migration renders forward

Chapters

01 Retire the side file38 secDeclare statuses as reference data, capture its rows with data capture, and delete the db/statuses.sql side file. 02 Change the structure and the rows in one migration37 secRename the table and its column, add a row by hand, and migrate renders both in one migration, the captured rows under the new names. 03 Catch a hand edit to the witnessed rows37 secA hand edit to the witnessed data file fails validate. The same change, captured from the database, migrates as an upsert keyed on status_id. 04 Run it end to end2 min 09 secDeclare statuses as reference data, capture it, witness and seal it, retire the .sql side file, rename the table and column, and add a row. Then watch a hand edit to the data file fail validate, and capture the same change again from the database.

Summary

Your application reads lookup tables it never writes, and their rows live in a .sql side file, apart from the schema.

Status codes, currencies and country lists are reference data: the application reads them, never writes them, and cannot run without them. Here they sit in db/statuses.sql, a side file of INSERT statements that no migration updates. We declare statuses as reference data, and data capture writes its 3 rows to a data file pinned to the log. snapshot and migrate include those rows as they include the DDL, and the side file is deleted. Later rows can come from the database the same way, or be written by hand at the operation that needs them; the demo does both.

A rename that would break a .sql side file leaves the captured rows intact, and a hand edit to a witnessed data file fails validation before it ships. After both renames the data file still says statuses and name, and snapshot emits its rows as order_statuses and label.

pg_dump reads the database and psql applies what snapshot and migrate emit; retrofit connects to no database. The log and the data files are committed to git together. Witnessing appends each data file’s digest to the log, and the third chapter shows what that catches.

Chapter 01Retire the side file

How do we retire the side file that holds our reference data?

schema ref-tables add declares statuses as reference data. The declaration is an operation in the log, bound to the table. pg_dump reads the database, and data capture writes the 3 rows of statuses to a data file pinned to that point in the log. It lists orders as not declared. From here snapshot includes those rows, and migrate carries them in the migration whose range they are pinned in.

data witness appends the data file’s digest to the log, and we seal it with op seal. A database built from retrofit snapshot has the 3 statuses and none of the 1,200 orders, and db/statuses.sql, the side file that held the same 3 rows, is deleted.

38 sec space to pause f for fullscreen

Read transcript

reference-data-demo: capture

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

1/4 declare and capture

statuses holds rows the application reads and never writes. we declare it as reference data. the declaration is an operation in the log, on the table:

retrofit schema ref-tables add statuses

pg_dump reads the database. retrofit writes the declared table’s rows to a data file pinned to this point in the log, and lists the rest:

pg_dump --data-only --column-inserts --schema=shop shop | retrofit data capture -

2/4 witness and seal

we witness the data file, which appends its digest to the log, and seal the witness. from here validate checks those bytes:

retrofit data witness

retrofit op seal --reason 'order statuses captured as reference data'

3/4 a new database

every snapshot and migration now includes those rows. a new database, built from retrofit snapshot:

createdb scratch && retrofit snapshot -q | psql scratch -X -q -v ON_ERROR_STOP=1

psql scratch -X -c 'TABLE shop.statuses' -c 'SELECT count(*) AS orders FROM shop.orders'

three statuses and no orders. the reference rows are part of the schema, and the application’s rows are not.

4/4 retire the side file

db/statuses.sql is the side file that held those rows. retrofit snapshot emits them now, so it goes:

git rm -q db/statuses.sql && git status --short

schema ref-tables add appends a SetReferenceData operation bound to the table. pg_dump reads the database, and data capture writes the declared table’s rows to a data file under data/, pinned to that point in the log. orders is listed as not declared, and its rows stay out.
data witness appends one operation carrying the data file’s digest, and we seal it with op seal. From then on validate, snapshot and migrate compare the file with that digest.
retrofit snapshot emits the schema with the reference rows in it, and psql builds a new database from it. It has the 3 rows of statuses and none of orders.
db/statuses.sql held the same 3 rows as INSERT statements, apart from the migrations. git rm deletes it, and git status shows the swap: the log modified, the data files added, the side file gone.

Chapter 02Change the structure and the rows in one migration

How do we change reference data, both its structure and its rows?

First the structure changes: statuses becomes order_statuses, and name becomes label.

Then the rows change. The declaration is bound to the table, not its name, so data scaffold mints a reference data file for a new row under the new names. We fill in the new row and witness the file.

One migration renders both renames and only the new row: the 3 captured rows are left out, because the database already has them. The retired side file would still say statuses and name, and fail on the next new database. The data file says them too, but snapshot emits its rows under the new names.

37 sec space to pause f for fullscreen

Read transcript

reference-data-demo: change with the schema

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

1/3 rename the table and the column

we captured, witnessed and sealed the statuses. first the structure changes: statuses becomes order_statuses, and name becomes label:

retrofit op add RenameTable statuses order_statuses

retrofit op add RenameColumn order_statuses.name label

2/3 add a row

then the rows change. the declaration is on the table, not its name, so the data file we mint for a new row is reference data under the new names:

retrofit data scaffold --table shop.order_statuses

we fill in the new row and witness the file:

sed -i '' 's/<status_id>/4/; s/<label>/refunded/' data/*.append.data.sql && retrofit data witness

3/3 one migration

one migration renders both renames and only the new row. the 3 rows the database already has are left out:

retrofit migrate

retrofit migrate -q | psql shop -X -q -v ON_ERROR_STOP=1 && psql shop -X -c 'TABLE shop.order_statuses'

the side file we retired would still say statuses and name, and fail on the next new database. the data file says them too, but the snapshot emits its rows under the new names:

retrofit snapshot -q | sed -n '/^-- retrofit:snapshot-data/,/^$/p'

retrofit op seal --reason 'order_statuses: rename, add refunded'

RenameTable and RenameColumn are appended to the log, each with apply-risk-rename-in-use: code that still reads the old name breaks when the migration runs.
The declaration is bound to the table, so data scaffold mints a reference data file for one row under order_statuses and label. We fill in the new row and witness the file.
migrate emits the two renames and one INSERT, for the new row only. The 3 captured rows are left out, because the database already has them, and the dml-behind-window warning names what was omitted. psql applies it, and the table has 4 rows under label. snapshot emits the captured rows as order_statuses and label, while their data file still says statuses and name. The retired side file would have needed both renames by hand.

Chapter 03Catch a hand edit to the witnessed rows

How do we know the reference rows that ship are the ones we reviewed?

We witnessed and sealed the first data file in the first chapter. Each data file is pinned to its position in the log, and a migration carries only the data files pinned inside its range.

Someone changes paid to settled in that first file. Every existing database is already past its position, so no migration would carry the edit; only a new database built from snapshot would get it. But validate compares the file with our witness and exits 1 with data-witness-mismatch.

So we restore the file, make the change in the database, and capture the table again. The new data file is pinned at the head, inside the next migration’s range, and migrate emits it as an upsert keyed on status_id.

37 sec space to pause f for fullscreen

Read transcript

reference-data-demo: witnessed

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

1/3 a hand edit

we witnessed and sealed the captured statuses. someone changes paid to settled in the data file:

sed -i '' "s/'paid'/'settled'/" data/shop.statuses.*.data.sql && git diff --stat

each data file is pinned to its place in the log, and a migration carries only the files inside its range. every existing database is past this one, so the edit would reach new databases and no existing one. validate compares the file to our witness:

retrofit validate

exit status 1

2/3 the change, made in the database

we restore the file, make the change where the rows came from, capture the table again and witness it:

git checkout -- data

psql shop -X -q -c "UPDATE shop.order_statuses SET label = 'settled' WHERE status_id = 2"

pg_dump --data-only --column-inserts --table=shop.order_statuses shop | retrofit data capture -

retrofit data witness

3/3 the migration

the new data file is pinned at the head, so the next migration carries it, as an update by key:

retrofit migrate

retrofit op seal --reason 'order_statuses: paid is now settled'

The data file is pinned behind every existing database, so no migration would carry the edit, and it no longer matches the digest we witnessed. validate exits 1 with data-witness-mismatch, and snapshot and migrate exit the same way. The hint names both ways out: restore the file, or capture the corrected rows again.
git checkout restores the data file. psql changes the label in the database, pg_dump reads the one table, and data capture writes its 4 rows to a new data file at the head of the log. We witness it with data witness.
The new data file is pinned at the head, inside this migration’s range. migrate emits its rows as an upsert keyed on status_id: the row whose label differs is updated, and a key the capture no longer holds is deleted. Its two warnings say so, and name orders as the table whose rows would block that delete.

Chapter 04Run it end to end

Declare statuses as reference data and capture its rows from a pg_dump into a data file, witness and seal it, build a new database from retrofit snapshot, and retire the .sql side file. Rename the table and its column, add a row by hand, and render one migration. Edit the data file by hand and watch validate fail, then make the change in the database, capture it again, and read the upsert migrate emits.

2 min 09 sec space to pause f for fullscreen

Read transcript

reference-data-demo: complete walkthrough

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

declare and capture

statuses holds rows the application reads and never writes. we declare it as reference data. the declaration is an operation in the log, on the table:

retrofit schema ref-tables add statuses

pg_dump reads the database. retrofit writes the declared table’s rows to a data file pinned to this point in the log, and lists the rest:

pg_dump --data-only --column-inserts --schema=shop shop | retrofit data capture -

witness and seal

we witness the data file, which appends its digest to the log, and seal the witness. from here validate checks those bytes:

retrofit data witness

retrofit op seal --reason 'order statuses captured as reference data'

a new database

every snapshot and migration now includes those rows. a new database, built from retrofit snapshot:

createdb scratch && retrofit snapshot -q | psql scratch -X -q -v ON_ERROR_STOP=1

psql scratch -X -c 'TABLE shop.statuses' -c 'SELECT count(*) AS orders FROM shop.orders'

three statuses and no orders. the reference rows are part of the schema, and the application’s rows are not.

retire the side file

db/statuses.sql is the side file that held those rows. retrofit snapshot emits them now, so it goes:

git rm -q db/statuses.sql && git status --short

rename the table and the column

first the structure changes: statuses becomes order_statuses, and name becomes label:

retrofit op add RenameTable statuses order_statuses

retrofit op add RenameColumn order_statuses.name label

add a row

then the rows change. the declaration is on the table, not its name, so the data file we mint for a new row is reference data under the new names:

retrofit data scaffold --table shop.order_statuses

we fill in the new row and witness the file:

sed -i '' 's/<status_id>/4/; s/<label>/refunded/' data/*.append.data.sql && retrofit data witness

one migration

one migration renders both renames and only the new row. the 3 rows the database already has are left out:

retrofit migrate

retrofit migrate -q | psql shop -X -q -v ON_ERROR_STOP=1 && psql shop -X -c 'TABLE shop.order_statuses'

the side file we retired would still say statuses and name, and fail on the next new database. the data file says them too, but the snapshot emits its rows under the new names:

retrofit snapshot -q | sed -n '/^-- retrofit:snapshot-data/,/^$/p'

retrofit op seal --reason 'order_statuses: rename, add refunded'

a hand edit

someone changes paid to settled in the data file:

sed -i '' "s/'paid'/'settled'/" data/shop.statuses.*.data.sql && git diff --stat

retrofit validate

exit status 1

the change, made in the database

we restore the file, make the change where the rows came from, capture the table again and witness it:

git checkout -- data

psql shop -X -q -c "UPDATE shop.order_statuses SET label = 'settled' WHERE status_id = 2"

pg_dump --data-only --column-inserts --table=shop.order_statuses shop | retrofit data capture -

retrofit data witness

the migration

the new data file is pinned at the head, so the next migration carries it, as an update by key:

retrofit migrate

retrofit op seal --reason 'order_statuses: paid is now settled'

the rows the application reads and never writes are in the log with the schema. a rename did not strand them, and a hand edit to a witnessed data file failed validation before it shipped.