IF Commons GitHub

History

0018Active

0018-sql-history · owner if · domain data

The frozen history

FR-001checkpassing

A step MUST be written as a file whose name has its number and a slug of its name, whose header holds, as JSON, its identifier, the hashes of the state it needs and leaves, its key, kind, class, reversibility and idempotency and the elements it is made from, and whose body holds its statements to do it, each with its group, its origin, the elements it is made from and its guard, its statements to undo it, and what must be true after it; and the file MUST be the same for the same step every time, and parsing it MUST give the step back.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: a step is a file with a numbered slug name, a JSON header, its statements with their guards, its undo and what must be true after, the same every time, and parsing it gives the step back

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/deps.ts, data/sql/extensions/history/format.ts, data/sql/extensions/history/mod.ts

Tests: passed: suite/history.test.ts a step is a file with a numbered slug name, a JSON header, its statements with their guard

Cited by: Data, sql/extensions/history/format

FR-002checkpassing

A manifest MUST list the files in order with the hash of each file's text and a chain hash of the entry before it, so that a file that is changed, taken out, put in or moved is found; and the identifier in a file's header MUST be the one its content makes, so that a file edited with its manifest entry is found too.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: the manifest chains the files, and a file changed, taken out, put in or moved is found, as is one whose manifest entry was edited to match

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/manifest.ts

Tests: passed: suite/history.test.ts the manifest chains the files, and a file changed, taken out, put in or moved is found, as

Cited by: Data, sql/extensions/history/manifest

FR-003checkpassing

freeze MUST give only files that are new: the steps of a plan that the history does not hold, after the history's last state, and MUST NOT rewrite a file that is there; a plan that does not start where the history ends MUST be an error value naming both states; freezing what is frozen already MUST give nothing new.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: freeze gives only files that are new, never rewrites one, refuses a plan that does not start where the history ends, and freezes the repeatable objects and the state beside the steps

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/freeze.ts

Tests: passed: suite/history.test.ts freeze gives only files that are new, never rewrites one, refuses a plan that does not sta

Cited by: Data, sql/extensions/history/freeze

FR-004checkpassing

The repeatable objects of the plan and the state it ends in MUST be frozen beside the steps, replaced by each freeze, so that a replay applies what extensions add and a person can see the state the history adds up to.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: freeze gives only files that are new, never rewrites one, refuses a plan that does not start where the history ends, and freezes the repeatable objects and the state beside the steps

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/freeze.ts

Tests: passed: suite/history.test.ts freeze gives only files that are new, never rewrites one, refuses a plan that does not sta

Cited by: Data, sql/extensions/history/freeze

Replaying

FR-005checkpassing

replay MUST check the manifest and every file before it runs, MUST refuse with an error value a history that does not check, and MUST then run what the files say through the lifecycle's run (with its ledger, its transactions, its checks, its guards and its base), so that replaying the files is running the plan they were frozen from.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: the files are checked and then replayed through the lifecycle's run on three engines to the same state, the ledger holds the same steps, a second replay does nothing, and a history that does not check runs nothing

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/replay.ts

Tests: passed: suite/history.test.ts the files are checked and then replayed through the lifecycle's run on three engines to th

Cited by: Data, sql/extensions/history/replay

FR-006checkpassing

The same files MUST leave SQLite, DuckDB and PostgreSQL each in the state they name, with the ledger holding the same step identifiers and state hashes, and a second replay MUST do nothing.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: the files are checked and then replayed through the lifecycle's run on three engines to the same state, the ledger holds the same steps, a second replay does nothing, and a history that does not check runs nothing

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/replay.ts

Tests: passed: suite/history.test.ts the files are checked and then replayed through the lifecycle's run on three engines to th

Cited by: Data, sql/extensions/history/replay

Delivery

FR-007checkpassing

The delivery through the pipeline MUST be the files, the manifest, the repeatable objects and the state, as files of the physical schema's own file type, and a replay.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: freeze gives only files that are new, never rewrites one, refuses a plan that does not start where the history ends, and freezes the repeatable objects and the state beside the steps

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/freeze.ts

Tests: passed: suite/history.test.ts freeze gives only files that are new, never rewrites one, refuses a plan that does not sta

Cited by: Data, sql/extensions/history/freeze

FR-008checkpassing

The delivery as a release package MUST give an installer that runs the steps in one transaction with the guards written in the SQL itself (where the dialect has a procedural block), a preflight that returns a row only if the database is in the state the release needs, a verification of what must be true after it, a back-out that undoes the steps that can be undone in the opposite order, a runbook, and a manifest; and where a guard cannot be written in the dialect's SQL the package MUST be an error value that says so, unless the project allows it to be left out.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: a release package is plain SQL a DBA runs: a preflight that returns a row only on the right state, an installer in one transaction with the guards inside, a verification, a back-out and a runbook; and it is an error where a guard cannot be written

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/package.ts

Tests: passed: suite/history.test.ts a release package is plain SQL a DBA runs: a preflight that returns a row only on the righ

Cited by: Data, sql/extensions/history/package

FR-009checkpassing

The delivery as a procedure in the database (PostgreSQL) MUST give an installer that makes a schema, a table that is its ledger (each entry chained to the one before it by hash) and a procedure migrate that takes a lock, checks the ledger's chain, finds where the database is, refuses one that is not in the state the next step needs, runs each step not yet applied with its guards and its verification, writes its entry, and applies what extensions add; and migrate run again MUST do nothing, and a ledger entry that was changed MUST make it refuse.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: a procedure inside PostgreSQL applies the history, chains its ledger, refuses a ledger that was changed and a state no step starts from, is run again to do nothing, and applies only what is missing when the history grows

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/indb.ts

Tests: passed: suite/history.test.ts a procedure inside PostgreSQL applies the history, chains its ledger, refuses a ledger tha

Cited by: Data, sql/extensions/history/indb

Settings

FR-010checkpassing

The delivery, the folder the files are in (migrations by default), the release's label, and whether a guard that cannot be written is left out MUST each have a default and be changeable.

Enforced (check): .github: cd commons/data && bun run verify — suite/history.test.ts: the delivery, the folder, the release label, the guard policy and the runner's schema each have a default and can be changed

Proved by: data/suite/history.test.ts

Written in: data/sql/extensions/history/settings.ts

Tests: passed: suite/history.test.ts the delivery, the folder, the release label, the guard policy and the runner's schema each

Cited by: Data, sql/extensions/history/settings

Out of scope

  • The interchange formats of other migration tools (Flyway, Liquibase, Atlas): on the roadmap, as exports of these files.
  • Writing the files to disk and reading them back: the command line's; the strategy gives and takes files as text.
  • Merging two histories that grew apart: the states are addresses, so the divergence is found, and joining them is a plan from one to the other.

Edge cases

  • A plan that starts at a state the history does not end in: an error value naming both, per FR-003.
  • A file whose statements were edited and whose manifest entry was edited to match: its identifier no longer matches its content, per FR-002.
  • A file taken out of the middle: the chain breaks, per FR-002.
  • A step written by a person: a file like any other, its header says so, per FR-001.
  • A release package for SQLite: guarded statements cannot be written in its SQL, so an error value, per FR-008.
  • A procedure run on a database that is two steps behind: both steps are run in order, per FR-009.
  • A procedure run on a database in a state no step starts from: refused, per FR-009.

Assumptions

  • A file's text is UTF-8 with Unix line ends and is hashed as it is.
  • The in-database ledger is its own table, made by the installer, and not the external run's.

Open questions

OQ-1open question

Whether a frozen history is also kept as a single archive with the manifest's head hash signed, so that what a pipeline deploys can be attested.

Key entities

  • History - the frozen files of the steps, the manifest, the repeatable objects and the state.
  • Release package - the files a DBA runs with a SQL client.
  • In-database runner - a procedure and a ledger inside the database.

Success criteria

SC-001success criterion

A database is brought to a state by replaying files, and the files are the proof of how.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

Data, sql/extensions/history/format, sql/extensions/history/freeze, sql/extensions/history/indb, sql/extensions/history/manifest, sql/extensions/history/package, sql/extensions/history/replay, sql/extensions/history/settings, Convergence, Presets

Titles, headings, exports, tables, columns and requirements. Esc closes.