The World as Tables

$ go run ./cmd/tables -mode weigh | tail -72 | head -18
tables: what a creature weighs, and which of its numbers anything ever asks about

  a 16x12 valley, 21 browsers off stream 12 and 8 hunters off stream 22, 12 years
  every birth and every death appended to tables.jsonl, and nothing in the valley reads it

  the archive at the end of 12 years
    born records                                         1303
    of those, on the browsers' row                       1287
    and on the hunters'                                    16
    died records                                         1096
    browsers still walking                                207
    hunters still walking                                   0
    bytes                                            11913330
    steps from the deepest creature to a founder           33

  the last born record of each row, with its genome taken out
{"r":"born","id":1303,"tick":43644,"kind":1,"of":1228,"with":700}
{"r":"born","id":54,"tick":905,"kind":1,"of":28,"row":1}

Those two lines are six of the seven columns, written out, and the seventh is argued into existence further down this page. The first is a browser born in the twelfth year to two parents; the second is one of sixteen hunters this valley ever had, born in the first year to a single parent, on a row that is written down because it is not the default. Sixty-odd bytes each. Everything else on the line the archive really wrote is the genome, and the run above threw it away to print these.

The hunters are gone, which the block above says in its own count of them still walking, and that is the valley telling the truth about itself and not a fault in the run. Their sixteen born records are in the archive all the same, which is the point of an archive: an animal that lasted two years is as permanently written down as one that lasted a century.

$ go run ./cmd/tables -mode weigh | tail -53 | head -17
  one born record, split the way this schema splits it
                                                        bytes    share
    the genome, as one column would hold it            9041.6    99.3%
    everything else on the line                          60.3     0.7%
    the whole line                                     9101.8   100.0%

  the seven columns, and what asks about each
    column      type     bytes   asked by
    id          bigint       8   every join, every climb, every death
    row         int          4   which of the two rows this animal stood on
    species     int          4   how many stood in a species, and when it opened
    tick        bigint       8   everything about when: births a year, a lifetime, a lag
    parent      bigint       8   one step up a pedigree, and the whole climb is this column
    mate        bigint       8   the other step up, and who bred with whom
    generation  int          4   how far a creature stands from a founder
                            44   the whole of what is queryable
    genome      jsonb     9042   read back whole, joined on never

Ninety-nine point three percent against nought point seven. The right-hand column of the lower table is the test the rule asks for, filled in for each of the seven, and every one of them passes it with a question somebody has already asked in this book. id is what a death is recorded against and what a parent is a reference to. tick is every question about when. species is the register's own key. parent and mate are the two ways up a pedigree. row says whether an animal browsed or hunted, and it is a column because the entire trophic argument is a question about counting the two rows separately.

generation is the one that has to be argued differently, because it is not a fact about a creature at all: it is the length of the walk from that creature to a founder, and it can always be worked out again from parent. Storing a thing you can recompute is usually a mistake. It is four bytes on a row of nine thousand, and the recomputation is a climb of thirty-three steps done once for every creature in every table of drift anybody ever prints. Four bytes to turn a walk into a read is not a close call.

$ go run ./cmd/tables -mode weigh | tail -28 | head -11
  how many numbers a genome holds, over the 1303 born records
     numbers   creatures   browsers   hunters
         168        1099       1099         0
         169          25         25         0
         170          80         80         0
         171          80         80         0
         172           2          2         0
         173           1          1         0
         192          16          0        16
    7 widths in 12 years, and a column apiece would need 192 columns
    Postgres stops a table at 1600, and a genome is not 192 numbers twice running

Seven different widths in twelve years, in one small valley. A founding browser carries a hundred and sixty-eight numbers and a founding hunter a hundred and ninety-two, because the two rows are wired to different sets of outputs; and structural mutation adds a link or a node to a lineage whenever it fires, so a browser thirty generations down carries a hundred and seventy-three. There is no width that is the width of a genome. There is a width per creature, and it changes as the population evolves, which is the entire point of the population.

A table of columns has one width for every row in it. To hold this valley you would need a hundred and ninety-two columns, of which most rows would use a hundred and sixty-eight and leave the rest null; and the first creature to grow past that would need a migration, on a live database, in the middle of year forty. Postgres would let you go to sixteen hundred columns and then stop, and a network with five hundred links in it is well past that. The variable width is what settles it, and the ninety-nine percent is how much it costs to be wrong.

$ go run ./cmd/tables -mode weigh | tail -35 | head -6
  the same creature as one row of the creature table
    the seven integer columns                              44
    the row's own header, whatever is in it                23
    the genome column                                  9041.6
    a row                                              9108.6
    what a query can filter on, of a row                0.48%

$ go run ./cmd/tables -mode weigh | tail -16 | head -14
  finding one creature by its number, in a history of 1303 births
    records read walking the archive                     2399
    bytes parsed doing it                            11913330
    comparisons in an ordered index, log2 n                11

  climbing the deepest pedigree in it, 33 steps to a founder
    records read, a pass over the file a step           79167
    comparisons, one descent a step                       363
    the first over the second                             218

  and what keeping one column in order costs
    keys in an index on parent, at 8 bytes each         10424
    the table those keys point into                  11867724
    the keys, as a share of the table                   0.09%

The first block is the row Postgres stores: twenty-three bytes of its own bookkeeping, forty-four bytes of integers, and the genome. The second is the reason anybody bothers. The archive answers "who was creature 1303's parent" by reading eleven point nine megabytes from the beginning; a column kept in order answers it in eleven comparisons, because that is how many halvings it takes to get from thirteen hundred to one. Climb the deepest line in the valley and the file is read thirty-three times over, seventy-nine thousand records, against three hundred and sixty-three comparisons. Two hundred and eighteen to one, and the ratio grows as the history does, because one side of it is a count and the other is a logarithm.

The last block is the part that surprises people who have been taught to be careful with indexes. An index on parent holds one eight-byte key for every row, and the rows are nine kilobytes each, so the keys come to nine hundredths of one percent of the table. The tree around them is bigger than the keys alone, and it is still nothing. That is why the creature table can afford four indexes without anybody arguing about it: an index costs what it costs relative to the row it points into, and a table with a blob in it is the cheapest table in the world to index.

-- The world itself: one row, written once at tick zero, never updated.
-- Everything else in this schema hangs off it.
--
-- one is always true and can be nothing else, so a unique constraint
-- on it is the database refusing a second world.
CREATE TABLE world (
    world   text    NOT NULL,
    seed    bigint  NOT NULL,
    opened  bigint  NOT NULL,
    genesis jsonb   NOT NULL,
    one     boolean NOT NULL DEFAULT true,

    CONSTRAINT world_key         PRIMARY KEY (world),
    CONSTRAINT world_is_one_row  UNIQUE (one),
    CONSTRAINT world_one_is_true CHECK (one),
    CONSTRAINT world_opened_on_a_real_tick CHECK (opened >= 0)
);

Four columns an operator chose and a fifth that is a trick. one is true and a check constraint forbids it being anything else, so a unique constraint on it means the table can hold exactly one row. Two worlds in one database is not something this book has an answer for: the snapshot tables name a world and the creature table does not, so a second world would put two creatures numbered 1 in the same table with nothing to tell them apart. The database can be made to refuse that, in three lines, so it is.

genesis is a blob for the same reason the genome is. It is prose an operator wrote, read once and printed thereafter, and nothing will ever be selected by a line of it.

-- What happened, in the world's own words, appended and never
-- rewritten. entry is here because tick is not a key: a tick in which
-- four things happen is four rows, and the database hands out the
-- order rather than letting anything write its own.
CREATE TABLE chronicle (
    entry bigint GENERATED ALWAYS AS IDENTITY,
    tick  bigint NOT NULL,
    kind  text   NOT NULL,
    text  text   NOT NULL,

    CONSTRAINT chronicle_key PRIMARY KEY (entry),
    CONSTRAINT chronicle_happened_on_a_real_tick CHECK (tick >= 0)
);

-- Every question asked of a chronicle is about a stretch of time, and
-- entry on the end returns one tick's rows in the order they were written.
CREATE INDEX chronicle_by_tick ON chronicle (tick, entry);

entry is the column no query asked for and the one that has to be argued hardest. The chronicle records what happened, and the obvious key is the tick it happened on, until you notice that four things can happen on one tick and that a founding document is a stretch of lines all landing at once. A table whose rows have no order cannot answer "and then what", so the database hands out a number in the order the rows arrive.

GENERATED ALWAYS AS IDENTITY rather than a plain default is the part to copy. It means an insert that tries to supply its own entry is refused outright, so there is no road by which a program can put a row in the middle of a history. The index puts entry on the end of tick so that the rows of one tick come back in the order they were written without anybody sorting them.

-- One row a birth: seven numbers and a genome. The seven are every
-- number anything will ever filter, join, group or climb on. The
-- genome is read back whole or not at all, so it is one column.
--
-- parent and mate are null on a founder, because nought is a creature
-- id and null is not. Nothing here is ever updated.
CREATE TABLE creature (
    id         bigint NOT NULL,
    row        int    NOT NULL,
    species    int    NOT NULL,
    tick       bigint NOT NULL,
    parent     bigint,
    mate       bigint,
    generation int    NOT NULL,
    genome     jsonb  NOT NULL,

    CONSTRAINT creature_key    PRIMARY KEY (id),
    CONSTRAINT creature_parent FOREIGN KEY (parent) REFERENCES creature (id),
    CONSTRAINT creature_mate   FOREIGN KEY (mate)   REFERENCES creature (id),

    -- A creature that is its own parent is a climb that never ends.
    CONSTRAINT nothing_is_its_own_parent CHECK (id <> parent AND id <> mate),
    -- Every birth is paid for by one parent, which may or may not have
    -- had a mate. A second parent with no first one is a row somebody
    -- wrote wrong.
    CONSTRAINT no_second_parent_without_a_first CHECK (parent IS NOT NULL OR mate IS NULL),

    CONSTRAINT creature_born_on_a_real_tick  CHECK (tick >= 0),
    CONSTRAINT creature_generation_counts_up CHECK (generation >= 0)
);

-- The two columns a pedigree is climbed by, and the two a history is
-- grouped and windowed by. row is not indexed and never will be: it
-- holds two distinct values in the whole table.
CREATE INDEX creature_by_parent  ON creature (parent);
CREATE INDEX creature_by_mate    ON creature (mate);
CREATE INDEX creature_by_species ON creature (species);
CREATE INDEX creature_by_tick    ON creature (tick);

Everything above the constraints was argued in bytes two sections ago. The constraints are the part that is free. parent and mate are foreign keys back into the same table, so a child naming a parent no row ever wrote down is impossible; the archive is written in birth order, and a parent always exists before its child does, so the constraint costs one lookup on a key that is already indexed. nothing_is_its_own_parent makes a climb that never ends impossible, in a check the server evaluates once per insert and never again.

no_second_parent_without_a_first is a rule about this world and not about databases: every birth here is paid for by one parent, which may or may not have found a mate. A row with a mate and no parent describes neither a founder nor a child; it is a mistake, and a schema that can name a mistake should.

Nulls are load-bearing in two of those columns. A founder has no parents, and the temptation is to write nought, because nought is what the archive writes and what a Go zero value is. Nought is a creature number. Creature nought does not exist today and a schema that depends on that is a schema with a bomb in it, so a founder's parents are null, which is SQL's own word for a value that is not there.

-- One row a death. A table and not two more columns on creature,
-- because a creature row is written once and never touched again:
-- columns for a death would mean coming back years later to update a
-- row nine kilobytes wide.
--
-- id is the key and not a column beside one, which is the database
-- holding a fact about this world: a creature dies once.
CREATE TABLE death (
    id    bigint NOT NULL,
    tick  bigint NOT NULL,
    cause text   NOT NULL,

    CONSTRAINT death_key           PRIMARY KEY (id),
    CONSTRAINT death_of_a_creature FOREIGN KEY (id) REFERENCES creature (id),
    CONSTRAINT death_on_a_real_tick CHECK (tick >= 0)
);

CREATE INDEX death_by_tick ON death (tick);

The interesting decision is that this is a table. Two more columns on creature, a nullable died_on and a nullable cause, would hold exactly the same facts and would look tidier in a listing. What they would cost is that every birth writes two nulls it has to come back and fill in later, on a row nine kilobytes wide, which means the commonest write in the whole system becomes an update of a row that is ninety-nine percent genome no statement changed.

Split into two tables, every statement this world runs against these two is an insert. A creature row is true the moment it is written and stays true. A death row is written once. Nothing is ever rewritten, which is the same property the archive file had and the reason it was trustworthy, kept while gaining the ability to be asked a question. And id is the primary key and not an ordinary column beside one, which is the database holding one more fact about this world: a creature dies once.

-- The species register, and the only table here whose rows change
-- after they are written: closed is filled in on the tick the last
-- member dies, and holder changes hands. number and opened never move.
--
-- closed is null and not nought. In memory the register uses nought
-- for a species that is still standing and gets away with it because
-- nothing ever closed on tick nought; in a database nought is a tick
-- and null is the absence of one.
CREATE TABLE species (
    number int    NOT NULL,
    opened bigint NOT NULL,
    closed bigint,
    holder bigint,

    CONSTRAINT species_key    PRIMARY KEY (number),
    CONSTRAINT species_holder FOREIGN KEY (holder) REFERENCES creature (id),
    CONSTRAINT species_opened_on_a_real_tick CHECK (opened >= 0),
    CONSTRAINT species_closes_after_it_opens CHECK (closed IS NULL OR closed >= opened)
);

This is the one table in the schema whose rows change after they are written, and saying so plainly is better than pretending otherwise. closed is filled in on the tick a species loses its last member, and holder changes hands whenever the register decides it should. number and opened never move.

closed being null and not nought is the small thing on this page most likely to be got wrong by somebody translating the program into a schema. In memory the register uses nought to mean a species that is still standing, and it gets away with it because nothing has ever closed on tick nought. Write that into a column and the world acquires a species that closed at the beginning of time as soon as anything closes on the first tick, which the founding document is about to make a busy one. Null is not a tidier nought. It is the answer to a different question, and "has this species closed" is that question.

-- A saved world, in two tables: one row saying a save happened, and
-- one row for each part of what was saved.
--
-- taken_at is the only wall-clock column in this schema, and it is for
-- whoever is looking at the database. A world's own clock is tick.
CREATE TABLE snapshot (
    world    text        NOT NULL,
    tick     bigint      NOT NULL,
    taken_at timestamptz NOT NULL DEFAULT now(),
    parts    int         NOT NULL,

    CONSTRAINT snapshot_key   PRIMARY KEY (world, tick),
    CONSTRAINT snapshot_world FOREIGN KEY (world) REFERENCES world (world),
    CONSTRAINT snapshot_on_a_real_tick CHECK (tick >= 0),
    CONSTRAINT snapshot_has_parts CHECK (parts > 0)
);

-- One row a component. bytea and not jsonb: a part is whatever the
-- thing that owns it marshalled itself into, and this table hands back
-- exactly the bytes it was given.
CREATE TABLE snapshot_part (
    world text   NOT NULL,
    tick  bigint NOT NULL,
    part  text   NOT NULL,
    bytes bytea  NOT NULL,

    CONSTRAINT snapshot_part_key PRIMARY KEY (world, tick, part),
    CONSTRAINT snapshot_part_of  FOREIGN KEY (world, tick)
        REFERENCES snapshot (world, tick) ON DELETE CASCADE
);

Two tables and one save. The parent row says a save happened at a tick and how many parts it had; the child rows are the parts. ON DELETE CASCADE means that removing an old save takes its parts with it, so there is no way to end up with parts belonging to a save that is not there.

bytes is bytea and not jsonb, which is the column-or-blob rule pushed one step further. A part is whatever the thing that owns it turned itself into, and this table's whole job is to hand back the same bytes it was given. Parsing them as JSON on the way in and rebuilding them on the way out would be two chances to change something in a value whose only requirement is being unchanged.

taken_at is the only wall clock in this schema and the only column in it no run in this book prints. A world's clock is tick, and everything the world reasons about is in ticks; the timestamp is for a person looking at the database wondering when they last saved. It carries now() as a default, which makes it the server's opinion of the time rather than the program's, and that is the correct owner for a fact about the database instead of about the world.

$ go run ./cmd/tables -mode plan
tables: the schema this build holds, and not one statement of it run

  n    file                     sha256, first 16    bytes   stmts   what it makes
  0001 0001_world.sql           55b759d43aa929d4      641       1   world
  0002 0002_chronicle.sql       58878101d9384245      735       2   chronicle
  0003 0003_creature.sql        5f95f200d36d9d2e     1740       5   creature
  0004 0004_death.sql           43845ea86c947233      696       2   death
  0005 0005_species.sql         54029ed637c5b65d      858       1   species
  0006 0006_snapshot.sql        e80d9a5289d0f66c     1183       2   snapshot, snapshot_part

  6 files, 5853 bytes, applied in the order of their numbers and no other
  the ledger, "migration", is not one of them: it has to exist before any of them runs

The whole schema is not quite six kilobytes of text, which is less than one creature. That mode opens no socket: the files are compiled into the binary with go:embed, so a program can say what schema it holds without a database being anywhere near it, and a build whose migrations are numbered wrong fails before it dials anything. The checksums are the other half of that and they come back further down.

// internal/store/migrate.go
// Ledger is the one table in this database that is not part of the
// world. It is the record of which migrations have been applied, and
// it is the whole of what makes running them twice safe: the second
// run does nothing because the ledger says the first one already did
// it, and not because the SQL was written to survive being run again.
const Ledger = "migration"

// ledger is the only statement in this system that has to be safe to
// run against a database that may or may not already have had it run.
// Everything else is protected by the table it creates; this one is
// the bootstrap, and IF NOT EXISTS is exactly the right tool for a
// statement with nothing to look itself up in.
const ledger = `
CREATE TABLE IF NOT EXISTS ` + Ledger + ` (
    n           int         NOT NULL,
    name        text        NOT NULL,
    sha256      text        NOT NULL,
    applied_at  timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT migration_key PRIMARY KEY (n)
)`

That is an eighth table and it is not part of the world. It belongs to the migration system, and it holds a row for every file that has been applied: the number, the name, the sha256 of the bytes that were applied, and when. It is also the one statement in the system carrying IF NOT EXISTS, and that is consistent instead of an exception. Every other statement is protected by this table. This table has nothing to look itself up in, so it has to be written to survive its own second run.

// internal/store/migrate.go
// Pending is the whole of the decision Migrate makes, with no database
// anywhere in it: given the migrations this build holds and the rows
// the ledger already has, which ones still have to run.
//
// It is a function and not four lines inside Migrate because it is the
// part that can be wrong in a way nobody notices, and a check that
// needs a container to run is a check that gets run less often than
// one that does not.
func Pending(steps []Step, done []Applied) (Report, error) {
	var rep Report
	by := map[int]Applied{}
	for _, a := range done {
		by[a.N] = a
	}
	held := map[int]bool{}
	for _, s := range steps {
		held[s.N] = true
	}
	for _, a := range done {
		if !held[a.N] {
			return rep, fmt.Errorf(
				"store: the database has had %s applied and this build does not hold it: "+
					"a schema built by something newer than the program opening it", a.Name)
		}
	}
	for _, s := range steps {
		if a, ok := by[s.N]; ok {
			if a.Sum != s.Sum {
				return rep, fmt.Errorf(
					"store: %s has changed since it was applied: the ledger holds %s and the file is %s. "+
						"A migration that has run anywhere is finished; a change goes in a new one",
					s.Name, short(a.Sum), s.Short())
			}
			rep.Skipped = append(rep.Skipped, s)
			continue
		}
		rep.Ran = append(rep.Ran, s)
	}
	return rep, nil
}

Four cases, and the checksum is the one that earns its keep. A migration already in the ledger with matching bytes is skipped. One not in the ledger is run. One in the ledger whose file has changed since stops everything, because the number is still 0003, the ledger still says 0003 ran, and the database now holds something no file in the tree describes. Nothing else can see that mistake. The fourth is a ledger row this build has never heard of, which means an older program has been pointed at a newer database, and starting up and writing into it would be the worst thing available.

Pending takes two slices and returns a plan, with no database in its signature anywhere. That is deliberate: the part of a migration runner that goes subtly wrong is the deciding and not the executing, and a check that needs a container to run is a check somebody skips.

$ go test ./internal/store/ -run 'TheMigrationsAreNumbered|TheSchemaIsSevenTables|ASecondRunHasNothing|AHalfMigrated|AMigrationEdited|ASchemaBuiltBySomethingNewer' -v
=== RUN   TestTheMigrationsAreNumberedFromOneWithNoGaps
    migrate_test.go:37: 6 migrations, numbered 1 to 6, every one of them read
--- PASS: TestTheMigrationsAreNumberedFromOneWithNoGaps (0.00s)
=== RUN   TestTheSchemaIsSevenTablesAndNoOthers
    migrate_test.go:73: seven tables, in six files, and not one of them is the ledger
--- PASS: TestTheSchemaIsSevenTablesAndNoOthers (0.00s)
=== RUN   TestASecondRunHasNothingLeftToDo
--- PASS: TestASecondRunHasNothingLeftToDo (0.00s)
=== RUN   TestAHalfMigratedDatabaseRunsOnlyWhatIsLeft
--- PASS: TestAHalfMigratedDatabaseRunsOnlyWhatIsLeft (0.00s)
=== RUN   TestAMigrationEditedAfterItRanIsRefused
--- PASS: TestAMigrationEditedAfterItRanIsRefused (0.00s)
=== RUN   TestASchemaBuiltBySomethingNewerIsRefused
--- PASS: TestASchemaBuiltBySomethingNewerIsRefused (0.00s)
PASS
ok  	theworld/internal/store	0.00s

Six tests of a migration runner and not one of them opens a socket. The second is the odd one: it reads the SQL out of the embedded files, pulls every CREATE TABLE out of it, and fails unless the set is exactly the seven this world has. A schema that quietly grows an eighth table is the drift that stays hidden until somebody asks what a table is for and gets no answer.

// internal/store/migrate.go
// Migrate brings a database up to the schema this build holds.
//
// Each migration runs inside its own transaction together with the
// ledger row that records it, so the two cannot come apart: Postgres
// rolls back DDL like anything else, and a migration that fails in its
// fourth statement leaves the database exactly as it was before its
// first, with nothing written in the ledger to say otherwise. A run
// that stops in the middle stops on a migration boundary, which is the
// only place a schema is ever in a state somebody can reason about.
//
// One transaction for the whole run would be tidier and is wrong: a
// long migration on a live database holds locks for as long as the
// slowest thing in the set, and a failure at the end throws away work
// that had nothing wrong with it.
func (d *DB) Migrate(ctx context.Context, steps []Step) (Report, error) {
	var rep Report
	if _, err := d.pool.Exec(ctx, ledger); err != nil {
		return rep, fmt.Errorf("store: the ledger: %w", err)
	}
	done, err := d.Applied(ctx)
	if err != nil {
		return rep, err
	}
	plan, err := Pending(steps, done)
	if err != nil {
		return rep, err
	}
	rep.Skipped = plan.Skipped
	for _, s := range plan.Ran {
		if err := d.apply(ctx, s); err != nil {
			return rep, err
		}
		rep.Ran = append(rep.Ran, s)
	}
	return rep, nil
}

// apply runs one migration and writes its ledger row, both or neither.
func (d *DB) apply(ctx context.Context, s Step) error {
	tx, err := d.pool.Begin(ctx)
	if err != nil {
		return fmt.Errorf("store: %s: %w", s.Name, err)
	}
	defer tx.Rollback(ctx)

	if _, err := tx.Exec(ctx, s.SQL); err != nil {
		return fmt.Errorf("store: %s: %w", s.Name, err)
	}
	const row = `INSERT INTO ` + Ledger + ` (n, name, sha256) VALUES ($1, $2, $3)`
	if _, err := tx.Exec(ctx, row, s.N, s.Name, s.Sum); err != nil {
		return fmt.Errorf("store: %s: recording it: %w", s.Name, err)
	}
	if err := tx.Commit(ctx); err != nil {
		return fmt.Errorf("store: %s: %w", s.Name, err)
	}
	return nil
}

The line to look at is tx.Exec(ctx, s.SQL) sitting in the same transaction as the insert that records it. Postgres rolls back CREATE TABLE the way it rolls back anything else, which is not true of every database and is why this can be done at all. A migration that fails in its fourth statement leaves the database exactly as it was before its first, with no ledger row claiming otherwise, and a run that stops in the middle stops on a migration boundary.

One transaction around the whole run would look tidier and would be worse. A long migration against a live database holds its locks for as long as the slowest statement in the set, and a failure in the last file throws away five files with nothing wrong with them. One transaction a migration is the unit that matches what a migration is.

$ podman exec -w /bench world-go go run ./cmd/tables -mode up -twice
tables: bringing a database up to the schema this build holds

  the first run
    0001 0001_world.sql           applied
    0002 0002_chronicle.sql       applied
    0003 0003_creature.sql        applied
    0004 0004_death.sql           applied
    0005 0005_species.sql         applied
    0006 0006_snapshot.sql        applied
    6 applied, 0 already there, and this run changed the database
    the ledger holds 6 rows, numbered 0001 0002 0003 0004 0005 0006

  the second run
    0001 0001_world.sql           already applied, and its bytes still agree
    0002 0002_chronicle.sql       already applied, and its bytes still agree
    0003 0003_creature.sql        already applied, and its bytes still agree
    0004 0004_death.sql           already applied, and its bytes still agree
    0005 0005_species.sql         already applied, and its bytes still agree
    0006 0006_snapshot.sql        already applied, and its bytes still agree
    0 applied, 6 already there, and this run changed nothing at all
    the ledger holds 6 rows, numbered 0001 0002 0003 0004 0005 0006

That is the idempotence claim in one run instead of two, so there is no question of the second block coming from a different afternoon. Six applied and a ledger holding six rows; then six skipped, none applied, and the same six rows. The second block says the bytes still agree, which is the checksum being checked and not merely stored.

Notice what the second run did not do. It did not ask the server whether the tables exist. It did not send a statement that would have been harmless. It read six rows and decided, and six statements never sent are six statements that cannot go wrong.