IF Commons GitHub

Safe migrations

0015Active

0015-sql-safety · owner if · domain data

The core's part

FR-001checkpassing

A step MAY carry a verification, queries to run before it (captures of a value) and after it (checks that a query returns a row, returns none, or returns the value captured), and execute MUST run the captures before the step's statements and the checks after them in the step's transaction, and a check that fails MUST roll the step back, record nothing and be returned as an error value naming the check; the verification MUST be part of the step's identifier.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: a step's verification is run in its transaction, a check that fails rolls the step back and records nothing, and the verification is part of the step's identifier

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

Tests: passed: suite/safety.test.ts a step's verification is run in its transaction, a check that fails rolls the step back an

Cited by: sql/lifecycle/plan

FR-002checkpassing

execute MUST be able to try a step again when its error is of a kind the project names (a lock timeout), a number of times with a pause between, and MUST return the last error where it still fails.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: a step is tried again when its error is of the kind the project names, a number of times with a pause, and the last error is returned where it still fails

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

Tests: passed: suite/safety.test.ts a step is tried again when its error is of the kind the project names, a number of times w

Cited by: sql/lifecycle/run

FR-003checkpassing

plan MUST apply the transforms before it decides that a step is refused, so that a destructive step that a transform has made safe is not refused.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: a destructive step that a transform has made safe is not refused

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

Tests: passed: suite/safety.test.ts a destructive step that a transform has made safe is not refused

Quarantine

FR-004checkpassing

Quarantine MUST make a step that drops a column copy the column's values with the table's key into a table of its own before the drop (<table>_<column>_quarantine, written by the case style and in the schema of the tables), MUST NOT run again once that table is there, and MUST make the step data-dependent and quarantine-reversible, with the statements that add the column again and restore its values as the step's down.

Enforced (check): .github: cd commons/data && bun run verify — suite/quarantine-reuse.test.ts: a column dropped a second time does not lose its new values to the quarantine of the first drop: the step fails its verification, is rolled back, and says what to do; suite/safety.test.ts: dropping a column keeps its values in a quarantine table, does not copy again, is data-dependent and quarantine-reversible, and undoing the step restores the values

Proved by: data/suite/quarantine-reuse.test.ts, data/suite/safety.test.ts

Written in: data/sql/extensions/safety/quarantine.ts

Tests: passed: suite/quarantine-reuse.test.ts a column dropped a second time does not lose its new values to the quarantine of the first; passed: suite/safety.test.ts dropping a column keeps its values in a quarantine table, does not copy again, is data-dep

Cited by: Data, sql/extensions/safety/quarantine

FR-005checkpassing

Quarantine MUST make a step that drops a table rename it to <table>_quarantined instead, MUST make the step data-dependent and quarantine-reversible, with the rename back as its down, and the step MUST say, from governance's quality.retention where it has one, how long it is to be kept.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: dropping a table renames it to its quarantined name with its rows, undoing brings it back, and a purge drops a quarantine table and nothing else

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

Written in: data/sql/extensions/safety/quarantine.ts

Tests: passed: suite/safety.test.ts dropping a table renames it to its quarantined name with its rows, undoing brings it back,

FR-006checkpassing

A purge MUST be a step that a person asks for by name (purge), that drops a quarantine table, and MUST be refused unless the table is a quarantine table.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: dropping a table renames it to its quarantined name with its rows, undoing brings it back, and a purge drops a quarantine table and nothing else

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

Written in: data/sql/extensions/safety/quarantine.ts

Tests: passed: suite/safety.test.ts dropping a table renames it to its quarantined name with its rows, undoing brings it back,

FR-007checkpassing

A step that drops a column or a table MUST, with quarantine off, remain destructive and be refused unless allowed.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: with quarantine off a drop is destructive again and refused; every feature is on by default and each setting can be changed

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

Written in: data/sql/extensions/safety/quarantine.ts

Tests: passed: suite/safety.test.ts with quarantine off a drop is destructive again and refused; every feature is on by defaul

Cited by: Data, sql/extensions/safety/quarantine

Lock discipline

FR-008checkpassing

Lock discipline MUST write, at the start of a step that changes a table that exists, the settings that make the step ask for its locks with a timeout and run with a limit (lock_timeout, statement_timeout), for a dialect that has them, and MUST give execute the retry for a lock timeout.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: a step that changes a table asks for its locks with a timeout where the dialect has them, and gives the run a retry for a lock timeout

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

Written in: data/sql/extensions/safety/locks.ts

Tests: passed: suite/safety.test.ts a step that changes a table asks for its locks with a timeout where the dialect has them,

Cited by: Data, sql/extensions/safety/locks, sql/physical/dialect

FR-009checkpassing

Where the dialect can add a constraint without checking the rows that are there, a foreign key or a check added to a table that exists MUST be added in two statements, the constraint not validated and then validated, and the step's verification MUST say the constraint is valid.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: a foreign key or a check is added without checking the rows and then checked, and the step's verification says the constraint is valid

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

Written in: data/sql/extensions/safety/locks.ts, data/sql/extensions/safety/verify.ts

Tests: passed: suite/safety.test.ts a foreign key or a check is added without checking the rows and then checked, and the step

Cited by: Data, sql/extensions/safety/locks, sql/extensions/safety/verify, sql/physical/dialect

Verification

FR-010checkpassing

Verification MUST give each step the checks that follow from its change: a table made exists with its columns; a column added exists and is as nullable as said; a column or a table dropped (or quarantined) is gone and its quarantine table is there; a rename leaves the new name and not the old; a constraint or an index made exists where the dialect can say so; and a step that changes the structure and not the data MUST find the same number of rows in the tables it touches as before.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: each step says what must be true after it, and a step that changes the structure and not the data finds the same number of rows, on three engines

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

Written in: data/sql/extensions/safety/verify.ts

Tests: passed: suite/safety.test.ts each step says what must be true after it, and a step that changes the structure and not t

Cited by: Data, sql/extensions/safety/verify

Settings

FR-011checkpassing

Each feature MUST be on by default and be switched off by a setting, and the settings (the timeouts, the number of tries, the pause, the suffixes) MUST each have a default and be changeable.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: with quarantine off a drop is destructive again and refused; every feature is on by default and each setting can be changed

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

Written in: data/sql/extensions/safety/mod.ts, data/sql/extensions/safety/settings.ts

Tests: passed: suite/safety.test.ts with quarantine off a drop is destructive again and refused; every feature is on by defaul

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

What the database holds

FR-012checkpassing

On SQLite, DuckDB and PostgreSQL, dropping a column that holds values MUST leave its values in the quarantine table and, run backwards by the step's down, MUST restore them; dropping a table MUST leave it under its quarantine name with its rows and, run backwards, MUST bring it back; a step whose check fails MUST leave the database and the ledger as they were; and a lock timeout MUST be tried again and then given up as an error value.

Enforced (check): .github: cd commons/data && bun run verify — suite/safety.test.ts: dropping a column keeps its values in a quarantine table, does not copy again, is data-dependent and quarantine-reversible, and undoing the step restores the values; suite/safety.test.ts: dropping a table renames it to its quarantined name with its rows, undoing brings it back, and a purge drops a quarantine table and nothing else; suite/safety.test.ts: with the protection layer and idempotency, dropping a column on three engines leaves the values in quarantine, the view without the column, and the step safe to run twice

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

Tests: passed: suite/safety.test.ts dropping a column keeps its values in a quarantine table, does not copy again, is data-dep; passed: suite/safety.test.ts dropping a table renames it to its quarantined name with its rows, undoing brings it back,; passed: suite/safety.test.ts with the protection layer and idempotency, dropping a column on three engines leaves the v

Out of scope

  • Changing a column's type without loss (a second column, a check that every value survives the cast, a swap): a later feature, on the roadmap.
  • Creating an index without blocking writes: it cannot run in a transaction, and the run has no steps outside one yet.
  • When a quarantine table is purged: a person says, with a step; a schedule is the project's.
  • A quarantine table's own rights: it is in the schema of the tables, where only the lifecycle's role has any.

Edge cases

  • A column dropped from a table that has no primary key: its values are copied with every column that the table has, per FR-004.
  • A column dropped twice (the step run again): the quarantine table is there, so it is not made again, per FR-004.
  • A table dropped whose quarantine name is taken: an error value, per FR-005.
  • A purge of a table that is not a quarantine table: an error value, per FR-006.
  • Quarantine off: a drop is destructive again and refused, per FR-007.
  • A lock timeout three times in a row with three tries: the last error is returned, per FR-002.

Assumptions

  • A column's values are small enough to copy within the step's transaction; a big copy is a step a person splits.
  • A lock timeout is reported by the engine with a message that the project can recognise.

Open questions

OQ-1open question

Whether the quarantine tables are also listed in the governance tables, with the step and the retention, so that the database says what it is keeping and why.

Key entities

  • Quarantine table - where the values of a dropped column, or the dropped table itself, are kept until purged.
  • Verification - the checks that a step did what it said, run in its transaction.
  • Capture - a value read before a step, to be compared after it.

Success criteria

SC-001success criterion

No step destroys data unless a person purges it, holds a lock without a limit, or is recorded without having done what it said.

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/safety/locks, sql/extensions/safety/mod, sql/extensions/safety/quarantine, sql/extensions/safety/settings, sql/extensions/safety/verify, sql/lifecycle/plan, sql/lifecycle/run, sql/physical/dialect

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