Contract — P4.5.1 Advisory Persistence (μ Wave 3)

Date: 2026-05-14 Branch: feature/p4-5-1-advisory-persistence Step: 2 of 5 — behavioral contract Builds on: docs/audits/p4-5-1-advisory-persistence-audit.md

This contract is the behavioral specification for migration 010_mcp_advisories.sql + repository src/domains/integrity/repository.ts. It crystallizes the audit findings into invariants, public surface, and test-grade acceptance criteria. The packet (Step 3) translates these into the file-by-file execution plan.

§1. Public surface

The repository module exports exactly three functions and exactly two types. Anything else is an invariant violation.

export function insertAdvisory(
  db: Database.Database,
  advisory: Advisory,
): InsertResult;

export function getAdvisory(
  db: Database.Database,
  decision_hash: string,
): Advisory | null;

export function listAdvisories(
  db: Database.Database,
  filter: AdvisoryFilter,
): Advisory[];

export type InsertResult =
  | { readonly inserted: true }
  | { readonly inserted: false; readonly existing: Advisory };

export type AdvisoryFilter = {
  readonly role?: AdvisoryRole;
  readonly check?: AdvisoryCheck;
  readonly severity?: AdvisorySeverity;
  readonly result?: AdvisoryResult;
  readonly since?: bigint;
};

Re-exports from ./schema.js (no re-declaration): Advisory, AdvisoryRole, AdvisoryCheck, AdvisoryResult, AdvisorySeverity.

Forbidden exports: updateAdvisory, deleteAdvisory, clearAdvisories, mutateAdvisory, removeAdvisory, dropAdvisories. Test G12 enforces this by snapshot.

§2. The database schema — migration 010_mcp_advisories.sql 

CREATE TABLE IF NOT EXISTS mcp_advisories (
  role               TEXT NOT NULL CHECK(role IN ('Translator','Sentinel','Guide')),
  "check"            TEXT NOT NULL CHECK("check" IN ('circular_logic','coercion_trap','axiom_drift','axiom_regression')),
  result             TEXT NOT NULL CHECK(result IN ('PASS','WARN','BLOCK')),
  severity           TEXT NOT NULL CHECK(severity IN ('LOW','MED','HIGH')),
  evidence           TEXT NOT NULL,
  recommendation     TEXT NOT NULL,
  decision_hash      TEXT NOT NULL UNIQUE,
  timestamp_logical  INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_advisories_check_severity ON mcp_advisories("check", severity);
CREATE INDEX IF NOT EXISTS idx_advisories_role ON mcp_advisories(role);

Invariant S1. Exactly 8 columns. No id, no created_at, no rowid alias. decision_hash is the unique row identifier. Invariant S2. All 4 enum columns carry CHECK constraints with the closed-set values from the P4.1.1 envelope. Invariant S3. "check" is quoted everywhere in DDL and DML. Invariant S4. decision_hash carries the UNIQUE constraint — the dedup gate. Invariant S5. Two indexes ship: (check, severity) and (role). Invariant S6. Migration is idempotent — both IF NOT EXISTS clauses plus the migration-runner version gate.

§3. Behavioral contract

§3.1 insertAdvisory(db, advisory) — append-only insert with dedup

Pre-conditions:

  • db is an open better-sqlite3 handle with migration 010 applied.
  • advisory conforms to Advisory (8 fields, closed enums, 64-char hex decision_hash, non-negative bigint timestamp_logical).

Post-conditions:

  • If no row exists with decision_hash = advisory.decision_hash, exactly one row is inserted and { inserted: true } is returned.
  • If a row exists with decision_hash = advisory.decision_hash, NO row is inserted and { inserted: false, existing: <persisted row> } is returned.
  • The function never throws on dedup collision.

Invariant I1 (idempotent). Two calls with the same advisory (or with two Advisorys that share decision_hash) produce one row. Invariant I2 (append-only). No UPDATE, DELETE, ALTER, DROP statement reaches the database from this function. Invariant I3 (transaction-isolated). The INSERT-OR-IGNORE + lookup-on-collision sequence runs inside a db.transaction so a concurrent writer cannot interleave between the IGNORE and the SELECT. (Required for test G3 to be byte-deterministic on fast CI.) Invariant I4 (closed-enum write). Every CHECK constraint catches violations before the function returns. Bypass attempts (passing an unrecognized role/check/result/severity at runtime via cast) surface as SQLITE_CONSTRAINT_CHECK errors.

Failure modes:

  • ZodError-like via the SQL CHECK constraint — surfaces as better-sqlite3’s SqliteError with code: 'SQLITE_CONSTRAINT_CHECK' when an enum value is invalid.
  • SqliteError code: 'SQLITE_CONSTRAINT_NOTNULL' if required fields are missing (TypeScript prevents this; runtime cast is the only path).

§3.2 getAdvisory(db, decision_hash) — point lookup

Pre-conditions: decision_hash is a string. (Format is not validated — an unmatched string yields null.)

Post-conditions:

  • Returns the unique Advisory matching the input hash, or null if no row matches.
  • The returned Advisory.evidence is deserialized from the stored JSON-string form (round-trips for non-bigint content; bigint content survives as the JSON.stringify-replacer’s string representation).
  • The returned Advisory.timestamp_logical is a bigint (per the defaultSafeIntegers(true) post-condition).

Invariant G1 (no side effects). No INSERT/UPDATE/DELETE runs against the database.

§3.3 listAdvisories(db, filter) — filtered ASC scan

Pre-conditions:

  • db is open and migration 010 is applied.
  • filter is an AdvisoryFilter. Every field is optional; {} means “no filter”.

Post-conditions:

  • Returns an array of Advisory matching ALL of the non-undefined filter fields.
  • ORDER BY timestamp_logical ASC (Lamport-monotonic).
  • All Advisory.timestamp_logical values in the result are bigint.
  • Empty result array when no rows match (NOT null).

Invariant L1 (parameterized). Every filter value reaches SQL via bind parameters, NOT string interpolation. No SQL injection surface. Invariant L2 (since semantics). filter.since is INCLUSIVE — rows with timestamp_logical >= filter.since are returned. Invariant L3 (AND combination). Multiple filter fields are AND-combined. Invariant L4 (order stability). Within rows that share timestamp_logical, the order is rowid ASC (SQLite’s default tiebreaker for an indexed ORDER BY on a non-unique column). Tests do not assert tie-break order beyond “no crash”.

§4. Failure modes — exhaustive list

Trigger Surface
advisory.role not in 3-enum SqliteError code='SQLITE_CONSTRAINT_CHECK' from insertAdvisory
advisory.check not in 4-enum same
advisory.result not in 3-enum same
advisory.severity not in 3-enum same
advisory.timestamp_logical not a bigint TypeError from better-sqlite3 bind (the bind layer rejects non-integer non-bigint inputs to INTEGER columns)
evidence contains a function TypeError from JSON.stringify — caught NOT by this contract; documented as a known limitation in §6
decision_hash already present { inserted: false, existing } — NO throw

§5. Determinism

  • insertAdvisory does not mint clocks, IDs, or randomness. The 8-field row is identical in both shape and value to the input advisory after a successful insert.
  • getAdvisory and listAdvisories produce byte-identical output for byte-identical (DB state, filter) pairs, except that JSON.stringify of evidence with mixed bigint/non-bigint elements produces a string whose internal byte order matches the input’s iteration order (V8 preserves array order; behavior is deterministic).
  • No Date.now(), no Math.random(), no process.hrtime() reach this module.

§6. Known limitations (documented, not bugs)

  • L1 (bigint inside evidence). JSON.stringify on a bigint throws by default. The repository installs a replacer that converts bigint → decimal string. Downstream callers reading bigint-containing evidence get back a string (not a bigint). This is acceptable because the canonical hash is computed at write time over the original object (P4.1.1’s computeDecisionHash handles bigint via κ’s canonicalize), so the dedup invariant survives round-tripping through JSON.
  • L2 (no LIMIT). listAdvisories has no LIMIT parameter. Wave-4 P4.6.1 wraps with LIMIT at the MCP tool layer if needed.
  • L3 (no LIMIT × OFFSET pagination). Same reason as L2.
  • L4 (no full-text search). recommendation is plain TEXT; SQLite’s FTS5 module is not used. Wave-4 P4.6.1 may add this.

§7. Acceptance criteria

Tests in src/__tests__/domains/integrity/repository.test.ts:

AC Test group Assertion
AC1 G1.1 PRAGMA table_info(mcp_advisories) returns 8 rows
AC2 G1.2 The 8 column names match the envelope (case-sensitive)
AC3 G1.3 PRAGMA index_list(mcp_advisories) returns ≥ 2 user indexes
AC4 G1.4 PRAGMA index_info(idx_advisories_check_severity) shows (check, severity) in order
AC5 G1.5 PRAGMA index_info(idx_advisories_role) shows (role)
AC6 G1.6 Inserting an invalid role/check/result/severity throws SQLITE_CONSTRAINT_CHECK
AC7 G1.7 decision_hash UNIQUE constraint catches a direct INSERT-with-duplicate-hash
AC8 G2.1 insertAdvisory(db, validAdvisory) returns { inserted: true } and row count = 1
AC9 G3.1 insertAdvisory(db, sameAdvisory) returns { inserted: false, existing: <round-tripped advisory> }
AC10 G3.2 Idempotent insert does NOT throw
AC11 G3.3 Row count after 2 identical inserts = 1
AC12 G5.1 getAdvisory(db, advisory.decision_hash) round-trips structurally equal
AC13 G6.1 getAdvisory(db, 'not-a-real-hash') returns null
AC14 G7.1 listAdvisories(db, {}) returns all rows in timestamp_logical ASC order
AC15 G8.1 listAdvisories(db, { role: 'Sentinel' }) filters correctly
AC16 G9.1 listAdvisories(db, { check: 'circular_logic', severity: 'HIGH' }) AND-combined
AC17 G10.1 listAdvisories(db, { since: 1000n }) returns only timestamp_logical >= 1000n
AC18 G11.1 bigint roundtrip — timestamp_logical = 999_999_999_999_999n (> 2^53) survives insert/read as bigint
AC19 G12.1 Object.keys(repositoryModule) contains no update*, delete*, clear*, mutate*, remove*, drop*
AC20 G13.1 repository.ts source contains no UPDATE SQL token
AC21 G13.2 repository.ts source contains no DELETE SQL token
AC22 G13.3 repository.ts source contains no ALTER SQL token
AC23 G13.4 repository.ts source contains no DROP SQL token
AC24 G14.1 evidence containing a bigint element survives insert + read (no throw on either side)
AC25 G15.1 All other filter combinations don’t crash (smoke: role+result, severity-only, since-only)

25 ACs total. Expected pass count: 25/25.

§8. Out-of-scope (not contract’d)

  • Pagination (LIMIT/OFFSET) — Wave-4 P4.6.1 layer.
  • MCP tool registration — Wave-4 P4.6.1.
  • Reading from getDb() singleton — repository takes a db parameter; the singleton is wired by P4.6.1’s tool handler.
  • Transactional bulk insert — repository inserts one row per call. Bulk is Wave-7 P4.7.1 parity harness territory.
  • evidence schema validation — evidence: unknown[] is the contract; deep validation is the detector’s job (Wave 2).

End of P4.5.1 contract.

Back to top

Colibri — documentation-first MCP runtime. Apache 2.0 + Commons Clause.

This site uses Just the Docs, a documentation theme for Jekyll.