IF Commons GitHub

The lifecycle of a physical schema

0011Active

0011-sql-lifecycle · owner if · domain data

The unit and the state

FR-001checkpassing

The lifecycle MUST be a unit of its own, sql/lifecycle/, whose public surface is the entry ./sql/lifecycle of the package's exports, that imports the physical and logical schemas, governance and the engine and nothing else, and that names no engine.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: the lifecycle is a unit with a public surface, and its files name no engine

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

Written in: data/sql/lifecycle/api.ts

Tests: passed: suite/lifecycle.test.ts the lifecycle is a unit with a public surface, and its files name no engine

FR-002checkpassing

A state MUST be a plain, serializable value that holds, for a logical schema bound to a dialect and a naming, every table with its words and written name, its columns (words, written name, written type, nullability, written default), its primary key, foreign keys, uniques, checks and indexes by identity and written form, its table clause and the rows of its code table; and stateHash MUST be the SHA-256 of its canonical JSON, the same for the same structure whatever the order of its tables and columns, and different when the dialect or the naming differs.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: a state is a hashed value that is the same for the same structure in any order, and another for another dialect or naming; the hash is the SHA-256 the kernel makes

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

Written in: data/sql/lifecycle/hash.ts, data/sql/lifecycle/state.ts

Tests: passed: suite/lifecycle.test.ts a state is a hashed value that is the same for the same structure in any order, and anothe

Cited by: Data, sql/lifecycle/hash, sql/lifecycle/state

FR-003checkpassing

bind MUST mark each statement it makes as the core's or an extension's (origin), so that what the lifecycle diffs (the core's tables, constraints, indexes and rows) is told from what it applies again (what extensions add).

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: bind marks what it makes as the core's and what an extension adds as the extension's

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

Tests: passed: suite/lifecycle.test.ts bind marks what it makes as the core's and what an extension adds as the extension's

Cited by: sql/physical/statement

Changes

FR-004checkpassing

diff MUST give the changes that take one state to another, matching tables and columns by their words and not by their written names, so that a change of case style is a rename and not a drop and an add, and matching a table or column that carries lifecycle.renamedFrom with the element of that name; each change MUST have a key, stable for the same change, that a hand-written step names.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: tables and columns are matched by their words, so a change of case style is a rename, and renamedFrom makes a rename of a change of name

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

Written in: data/sql/lifecycle/change.ts, data/sql/lifecycle/state.ts

Tests: passed: suite/lifecycle.test.ts tables and columns are matched by their words, so a change of case style is a rename, and

Cited by: Data, sql/lifecycle/change

FR-005checkpassing

applyChange MUST give the state that a change leads to, so that applying the changes of diff(a, b) to a gives a state of the hash of b; a plan whose steps do not end at the hash of the state wanted MUST be an error value.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: for any two states the changes of their difference lead from the first to the second, and the difference of a state with itself is empty

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

Written in: data/sql/lifecycle/change.ts

Tests: passed: suite/lifecycle.test.ts for any two states the changes of their difference lead from the first to the second, and

Cited by: Data, sql/lifecycle/change

FR-006checkpassing

Each change MUST be classified safe, data-dependent (it may fail or change on the data that is there), destructive (it loses data) or manual (the engine cannot do it in place, or it may lose data in a way only a person can judge), and as lossless (undone without loss by its down statements), quarantine (undone from what an extension kept) or irreversible.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: changes are classified, destructive ones are refused unless allowed, what the dialect cannot do needs a person's step, and all of it is returned together

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

Written in: data/sql/lifecycle/plan.ts

Tests: passed: suite/lifecycle.test.ts changes are classified, destructive ones are refused unless allowed, what the dialect cann

Cited by: Data, sql/lifecycle/plan, sql/physical/dialect

Plans

FR-007checkpassing

A plan MUST be the ordered steps from the state a database is in (or from none, an empty database) to the state wanted, each with the hash of the state it needs and of the state it leaves, an identifier made of its content, a name a person reads, its class, its reversibility, the statements that do it and, where it has one, the statements that undo it, and the logical elements it is made from; a plan between equal states MUST have no step; the order MUST be renames, then drops of constraints and indexes, then changes of columns, then new tables, then new constraints, indexes and rows, then drops of tables.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: a plan has the ordered steps with the hash of the state each needs and leaves, an identifier made of its content, and none between equal states

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

Written in: data/sql/lifecycle/plan.ts

Tests: passed: suite/lifecycle.test.ts a plan has the ordered steps with the hash of the state each needs and leaves, an identifi

FR-008checkpassing

A plan MUST refuse, as error values returned together, a destructive step that was not allowed, and a change that the dialect cannot do in place and no hand-written step resolves; a hand-written step MUST replace the statements of the change whose key it names and be marked as written by a person, and one that resolves no change MUST be an error value.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: changes are classified, destructive ones are refused unless allowed, what the dialect cannot do needs a person's step, and all of it is returned together

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

Written in: data/sql/lifecycle/plan.ts

Tests: passed: suite/lifecycle.test.ts changes are classified, destructive ones are refused unless allowed, what the dialect cann

Cited by: Idempotent migrations

FR-009checkpassing

What extensions add to the schema, and the setup the core makes, MUST be repeatable objects of the plan, each with a key and the hash of its text, and dueRepeatables MUST give those whose hash is not the one last applied.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: what extensions add is repeatable, with a key and the hash of its text, and only what changed is due; suite/lifecycle.test.ts: what extensions add is applied once and again only when its text changes

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

Written in: data/sql/lifecycle/plan.ts

Tests: passed: suite/lifecycle.test.ts what extensions add is repeatable, with a key and the hash of its text, and only what chan; passed: suite/lifecycle.test.ts what extensions add is applied once and again only when its text changes

Cited by: Data, sql/lifecycle/plan

The ledger and the run

FR-010checkpassing

A ledger MUST be a list of entries, each with the identity and the hashes of what was applied and a chain hash made of the entry before it, so that verifyChain finds an entry that was changed, taken out, added or moved.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: a ledger whose entry was changed, taken out, put in or moved fails its chain

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

Written in: data/sql/lifecycle/hash.ts, data/sql/lifecycle/ledger.ts

Tests: passed: suite/lifecycle.test.ts a ledger whose entry was changed, taken out, put in or moved fails its chain

Cited by: Data, sql/lifecycle/hash, sql/lifecycle/ledger

FR-011checkpassing

execute MUST apply a plan to a driver (a way to run statements and read rows) by making the ledger table where there is none, reading the ledger and checking its chain, finding where the database is among the plan's states (nothing to do, the start, or after a step) and refusing as an error value a database that is in none of them, then running the setup the plan holds (a schema, an extension its types need), then each step's statements with its ledger entry in one transaction, then what extensions add, so that a step that fails leaves the database and the ledger as they were; run again it MUST do nothing.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: a plan runs on a real engine step by step with the ledger, does nothing run again, refuses another base, rolls a failed step back, and finds a ledger that was touched; suite/lifecycle.test.ts: what extensions add is applied once and again only when its text changes

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

Written in: data/sql/lifecycle/run.ts

Tests: passed: suite/lifecycle.test.ts a plan runs on a real engine step by step with the ledger, does nothing run again, refuses; passed: suite/lifecycle.test.ts what extensions add is applied once and again only when its text changes

Cited by: Data, sql/lifecycle/run

FR-012checkpassing

On SQLite, DuckDB and PostgreSQL, the steps of a plan from an empty database, and then of each plan from one version of a domain to the next, MUST leave the database with the tables, columns, nullability, keys and indexes of the physical schema bound for the domain's latest version, and the ledger MUST hold one entry for each step.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: the steps of a plan from empty and then from each version to the next leave the database that bind makes for the last version, with one ledger entry for each step; suite/lifecycle.test.ts: what extensions add is applied once and again only when its text changes

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

Tests: passed: suite/lifecycle.test.ts what extensions add is applied once and again only when its text changes; passed: suite/lifecycle.test.ts the steps of a plan from empty and then from each version to the next leave the database t

FR-013checkpassing

For any two states, applyChange over diff MUST reach the second, and the diff of a state with itself MUST be empty.

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: for any two states the changes of their difference lead from the first to the second, and the difference of a state with itself is empty

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

Written in: data/sql/lifecycle/change.ts

Tests: passed: suite/lifecycle.test.ts for any two states the changes of their difference lead from the first to the second, and

Cited by: Data, sql/lifecycle/change

Governance

FR-014checkpassing

Governance MUST have a vocabulary lifecycle of annotations that say what the physical schema cannot know: renamedFrom (the name an element had) and since (the release that made it).

Enforced (check): .github: cd commons/data && bun run verify — suite/lifecycle.test.ts: the lifecycle vocabulary says what an element was called and when it was made, and lint finds a renamedFrom that is not a name or is the element's own

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

Written in: data/governance/vocabularies/lifecycle.ts

Tests: passed: suite/lifecycle.test.ts the lifecycle vocabulary says what an element was called and when it was made, and lint fi

Cited by: governance/vocabularies/lifecycle, Data

Out of scope

  • Ways to change a database with no strategy: the strategies and features say how a plan is delivered, reviewed and made safe (0012 and the specifications after it).
  • Reading a state from a live database: the convergence strategy's.
  • Changing a primary key, a table clause (a partition) or a column's type in place: a hand-written step until the features of safety and rewrite.
  • A constraint or an index renamed together with its table: it is dropped and made again.

Edge cases

  • A domain whose tables are written in camel case after being in snake case: renames of every table and column, per FR-004.
  • A column that carries renamedFrom: one rename and no drop, per FR-004.
  • A new column that is required and has no default, on a table that has rows: data-dependent, per FR-006.
  • A column dropped: destructive and refused unless allowed, per FR-008.
  • A type changed: manual, so a plan without a step for it is an error value, per FR-008.
  • A plan run against a database in another state: an error value naming both hashes, per FR-011.
  • A ledger entry deleted by hand: the chain breaks, per FR-010.
  • A step that fails in the middle: rolled back, no ledger entry, per FR-011.

Assumptions

  • DDL is transactional on the engines the library supports, so a step is atomic; a step that cannot be (an index made concurrently) is the safety feature's.
  • A hash is made by this layer itself, so that a plan is a pure function; it is the same SHA-256 the kernel makes.

Open questions

OQ-1open question

Whether a state is also kept as a file next to the migrations (a snapshot), or only the hash is, for the history strategy (0018).

Key entities

  • State - the written structure of a database's tables, addressed by its hash.
  • Change - one difference between two states, with a key.
  • Step - a change made into statements, classified, from one state hash to another.
  • Plan - the ordered steps from one state to another and the repeatable objects.
  • Ledger - the chained record of what was applied.

Success criteria

SC-001success criterion

Whatever workflow delivers a plan, the database it leaves is the one bind makes for the latest version.

SC-002success criterion

No step runs on a database in a state it was not made for, and nothing destroys data unless it was allowed.

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

governance/vocabularies/lifecycle, Data, sql/lifecycle/change, sql/lifecycle/hash, sql/lifecycle/ledger, sql/lifecycle/plan, sql/lifecycle/run, sql/lifecycle/state, sql/physical/dialect, sql/physical/statement, Idempotent migrations, Making a table again, History

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