IF Commons GitHub

Invariants in the database

0043Active

0043-sql-invariants · owner if · domain data

Requirements

FR-001checkpassing

The quality vocabulary MUST have appendOnly (a concept, with an optional chain naming the members that hold the hash, the previous hash, the sequence and the start), transitions (a code attribute, with from each code the codes it may become) atMost (a concept, a member that is never above another) and temporal (a concept, the members that say from when and until when a record holds, so that it can be read as of an instant), and lint MUST find a member that the concept does not have and a code that the attribute's list does not have.

Enforced (check): .github: cd commons/data && bun run verify — suite/enforcement.test.ts: the vocabulary says a concept is append-only (perhaps as a chain), that a status moves only along its moves and that a number is never above another, and lint finds a member or a code that is not there; suite/identity-operations.test.ts: with the invariants in the database, a statement typed by hand is refused what governance forbids: the audit trail cannot be changed, removed, forked or truncated, a status moves only along its moves, a budget cannot go above its limit, and the operations still work

Proved by: data/suite/enforcement.test.ts, data/suite/identity-operations.test.ts

Written in: data/sql/extensions/invariants/mod.ts

Tests: passed: suite/enforcement.test.ts the vocabulary says a concept is append-only (perhaps as a chain), that a status moves onl; passed: suite/identity-operations.test.ts with the invariants in the database, a statement typed by hand is refused what governance

FR-002checkpassing

For a dialect that has triggers, an append-only concept MUST be made so that the database refuses any statement that changes or removes a record, and, on PostgreSQL, one that empties the table.

Enforced (check): .github: cd commons/data && bun run verify — suite/identity-operations.test.ts: with the invariants in the database, a statement typed by hand is refused what governance forbids: the audit trail cannot be changed, removed, forked or truncated, a status moves only along its moves, a budget cannot go above its limit, and the operations still work

Proved by: data/suite/identity-operations.test.ts, data/suite/issues.test.ts

Written in: data/sql/extensions/invariants/mod.ts

Tests: passed: suite/identity-operations.test.ts with the invariants in the database, a statement typed by hand is refused what governance

FR-003checkpassing

For a concept that is a chain, the database MUST refuse a record that is not number one after the start where there is none, and any other that is not one more than the last and does not name the hash of the last, so that a record cannot be left out, put between or forked.

Enforced (check): .github: cd commons/data && bun run verify — suite/identity-operations.test.ts: with the invariants in the database, a statement typed by hand is refused what governance forbids: the audit trail cannot be changed, removed, forked or truncated, a status moves only along its moves, a budget cannot go above its limit, and the operations still work

Proved by: data/suite/identity-operations.test.ts

Written in: data/sql/extensions/invariants/mod.ts

Tests: passed: suite/identity-operations.test.ts with the invariants in the database, a statement typed by hand is refused what governance

FR-004checkpassing

The database MUST refuse a change of a status to a code that its attribute's moves do not allow (a code with no moves is terminal), and a record in which a member is above the member it may not be above; and the operations of the identity model MUST run through these triggers.

Enforced (check): .github: cd commons/data && bun run verify — suite/identity-operations.test.ts: with the invariants in the database, a statement typed by hand is refused what governance forbids: the audit trail cannot be changed, removed, forked or truncated, a status moves only along its moves, a budget cannot go above its limit, and the operations still work

Proved by: data/suite/identity-operations.test.ts

Written in: data/sql/extensions/invariants/mod.ts

Tests: passed: suite/identity-operations.test.ts with the invariants in the database, a statement typed by hand is refused what governance

FR-005checkpassing

Where a dialect has no triggers, the statement source MUST give an error value naming what it cannot enforce, unless the project says to carry the invariants, in which case it MUST make no trigger; and the invariants a schema declares MUST be listable.

Enforced (check): .github: cd commons/data && bun run verify — suite/identity-operations.test.ts: a dialect without triggers is an error value naming what it cannot enforce unless the project says to carry it, and the invariants a schema declares can be listed

Proved by: data/suite/identity-operations.test.ts

Written in: data/sql/extensions/invariants/mod.ts

Tests: passed: suite/identity-operations.test.ts a dialect without triggers is an error value naming what it cannot enforce unless the proj

FR-006checkpassing

enforcementOf MUST say, for a dialect, its tier (full, standard or basic), what it can make the database do and what it cannot and why; where MUST say, for a guarantee, whether the database or the application's runtime enforces it for that dialect, and how; and placed MUST say it for each invariant of a schema, so that on a dialect that can the database enforces each of the identity model's invariants and on one that cannot each says why it is the application's.

Enforced (check): .github: cd commons/data && bun run verify — suite/enforcement.test.ts: a dialect says what it can enforce in the database (PostgreSQL everything, SQLite checks, keys and triggers, DuckDB checks and keys), and each guarantee is placed in the database or in the application's runtime, with why

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

Written in: data/sql/physical/enforcement.ts

Tests: passed: suite/enforcement.test.ts a dialect says what it can enforce in the database (PostgreSQL everything, SQLite checks,

Cited by: governance/issue, Data, sql/physical/enforcement

FR-007checkpassing

Where the dialect has event triggers (PostgreSQL), the invariants MUST come with a guard, unless the project leaves it out, that refuses to drop a trigger or function of an invariant (alone, with its table or by cascade), to disable one, to rename one and to replace one, for any session that does not hold an unlock secret (kept outside the database; the database holds only its hash); the guard's own functions MUST be named as an invariant's so that they are guarded too; and with no secret given nothing but a superuser's disabling of the guard MUST open it.

Enforced (check): .github: cd commons/data && bun run verify — suite/identity-operations.test.ts: on PostgreSQL the owner of a table cannot weaken the invariants: dropping, disabling, renaming or replacing a trigger or its function, or dropping the table, is refused unless the session holds the unlock secret, and with the guard left out it is not

Proved by: data/suite/identity-operations.test.ts

Tests: passed: suite/identity-operations.test.ts on PostgreSQL the owner of a table cannot weaken the invariants: dropping, disabling, rena

Cited by: sql/extensions/invariants/mod

Out of scope

  • Row-level security as a sealed scope, and roles and grants: separate guarantees, and the weaknesses found in them are tracked as defects of the PostgreSQL embodiment.
  • A superuser: one can disable an event trigger, as one can change anything; the guard is against the owners of tables and the roles of applications.
  • Enforcement by the application's runtime: the operations runtime (0031), whose limits are said there.
  • Concurrency: the tests cannot show two connections acting at once.

Edge cases

  • A trigger that must also hold for a table owner: a table's owner can drop a trigger; the guard of FR-007 refuses it, and a migration that must change an invariant unlocks the guard for its transaction.
  • Event triggers belong to the database, not a schema: the guard's names are the database's, and an invariant's names begin inv_, per FR-007.
  • A dialect that gains triggers later: its tier changes with its features and nothing else does, per FR-006.
  • DuckDB: the invariants are carried as data and enforced by the runtime, and the report says so, per FR-005.

Assumptions

  • A trigger's cost is a few reads of an index on each write of a record of the concept.
  • The application supplies the chain's sequence; the database checks it, per FR-003.

Open questions

OQ-1open question

Whether the unlock should also be bound to a role and not only to a secret that the session holds.

Key entities

  • Invariant - what must always be true of the records of a concept.
  • Tier - how much a dialect lets the database enforce.

Success criteria

SC-001success criterion

A statement typed by hand that breaks what governance says must always be true is refused by the database, on every engine that can, and the library says which engines cannot.

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/issue, Data, sql/extensions/invariants/mod, sql/physical/enforcement, arcactl

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