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

Date: 2026-05-14 Branch: feature/p4-5-1-advisory-persistence Base SHA: 41226615 Task: μ Phase 4 Wave 3 — mcp_advisories migration + append-only repository

This audit inventories the migration runner, the append-only repository pattern, the better-sqlite3 bigint mode considerations, and the P4.1.1 envelope shape that P4.5.1 has to land on. The audit’s findings drive the contract in docs/contracts/p4-5-1-advisory-persistence-contract.md and the execution plan in docs/packets/p4-5-1-advisory-persistence-packet.md.

---

§1. The P4.1.1 envelope — the 8 fields we persist

src/domains/integrity/schema.ts (Wave 1) defines AdvisorySchema over the 8-field envelope:

#	Field	Zod type	TS type
1	role	z.enum(['Translator','Sentinel','Guide'])	AdvisoryRole
2	check	z.enum(['circular_logic','coercion_trap','axiom_drift','axiom_regression'])	AdvisoryCheck
3	result	z.enum(['PASS','WARN','BLOCK'])	AdvisoryResult
4	severity	z.enum(['LOW','MED','HIGH'])	AdvisorySeverity
5	evidence	z.array(z.unknown())	unknown[]
6	recommendation	z.string()	string
7	decision_hash	z.string().regex(/^[a-f0-9]{64}$/)	string
8	timestamp_logical	z.bigint().nonnegative()	bigint

The envelope is the structural contract for every downstream μ surface. P4.5.1 persists the envelope byte-for-byte — no extra columns, no id, no created_at. The decision_hash IS the de-facto primary key (UNIQUE constraint).

---

§2. The migration runner — how to land migration 010

src/db/index.ts:138-176 (discoverMigrations) scans src/db/migrations/*.sql for NNN_*.sql, parses the leading integer, sorts ascending, and applies any with version > current user_version inside a db.transaction that also bumps PRAGMA user_version = NNN.

Two layers of idempotency exist:

1. Runner level — user_version skips already-applied migrations on second boot.
2. Statement level — CREATE TABLE IF NOT EXISTS + CREATE INDEX IF NOT EXISTS make a hand-replayed migration body a no-op.

The most-recent existing migration is 009_model_candidates.sql (Phase 1.5 δ Router, R92). The next number is 010. The PM has pre-assigned 010_mcp_advisories.sql to this task; sibling Wave-3 agents (P4.3.1 + P4.4.1) operate on different files and do NOT touch migrations.

PRAGMA foreign_keys = ON is set by initDb but is documentary in Phase 0 — no FK references mcp_advisories.

---

§3. better-sqlite3 bigint mode — the timestamp_logical precision gate

better-sqlite3 returns SQLite INTEGER columns as JavaScript number by default, which loses precision above 2^53 (9_007_199_254_740_992). The advisory timestamp_logical is a uint64 Lamport clock value; it MUST round-trip as bigint.

The migration runner at src/db/index.ts:237-307 does NOT call db.defaultSafeIntegers(true). The safe-integers mode is therefore per-statement (via stmt.safeIntegers(true)) or per-handle (via db.defaultSafeIntegers(true)).

Decision: call db.defaultSafeIntegers(true) once per repository function call, at the boundary, before preparing the statement that reads timestamp_logical. Per-handle setting persists across statements on that handle but is idempotent. Tests will exercise the 2^53+ boundary explicitly.

Alternative considered: stmt.safeIntegers(true) per-prepared-statement. Rejected because it forces calling .safeIntegers(true) on every read statement and is easy to forget; the per-handle setting is one line in insertAdvisory and reads cleanly.

Caveat: when safeIntegers(true) is set, ALL INTEGER columns are returned as bigint, not just timestamp_logical. The repository deserializer must cope. Since the only other INTEGER column candidate would be a rowid (which we don’t read), no additional handling is needed.

---

§4. The ζ Decision Trail repository — closest precedent

src/domains/trail/repository.ts (P0.7.2) is the closest precedent for an append-only, dedup-on-content repository:

• Insertion uses db.prepare(...).run(...) inside a db.transaction (line 267-303).
• Selection uses db.prepare<[BindParams], RowType>(...).get(...) or .all(...) (lines 254, 321, 369).
• Listing with optional filters uses four statically-typed SQL strings, one per filter power-set (line 354-382) — no dynamic WHERE concatenation.
• Type narrowing uses a row-shape interface plus a rowToRecord mapper (lines 171-203).
• Append-only enforcement is a code-level convention: the module exports only createThoughtRecord, getThoughtRecord, listThoughtRecords — no updateThoughtRecord/deleteThoughtRecord exist (line 237, 317, 344).

P4.5.1 diverges from the ζ pattern on three points:

1. Dynamic WHERE for listAdvisories. The advisory filter is a 5-field bag (role, check, severity, result, since); the static-SQL power-set would balloon to 32 statements. We instead build a parameterized WHERE at call time (bind parameters, NOT string interpolation — no injection surface).
2. Dedup on collision. ζ throws on hash collision (UNIQUE violation surfaces). μ catches it: INSERT OR IGNORE + SELECT WHERE decision_hash = ? returns the existing row.
3. bigint boundary. ζ has no bigint column; μ has timestamp_logical.

---

§5. SQL keyword: check must be quoted

check is a reserved SQL keyword. SQLite’s tokenizer accepts unquoted check in some positions but silently mis-parses it in others. The mitigation is consistent quoting: "check" in every DDL and DML statement that names the column.

• CREATE TABLE: "check" TEXT NOT NULL CHECK("check" IN (...)) (note: the column constraint keyword is CHECK and the column name is "check").
• INSERT: INSERT INTO mcp_advisories (..., "check", ...) VALUES (...).
• SELECT: SELECT ..., "check", ... FROM mcp_advisories.
• WHERE: WHERE "check" = ?.
• Index: CREATE INDEX ... ON mcp_advisories("check", severity).

The TypeScript surface diverges: check is also problematic in JS in some lexical contexts (it’s a reserved word in strict-mode-with-with, but legal as an object property name + variable name in ES2015+). The Advisory interface uses check directly without escaping. In the row mapper we use row['check'] bracket notation or aliasing to be unambiguous.

---

§6. Filter design — what listAdvisories accepts

The acceptance criteria require filtering by { role, check, severity, result, since }. The PM dispatch packet pins the exact shape; I cross-check against the integrity.md spec and Wave-4 P4.6.1 needs:

• role — string equality on the role column
• check — string equality on the "check" column
• severity — string equality on the severity column
• result — string equality on the result column
• since — timestamp_logical >= ? (bigint)

ORDER BY is timestamp_logical ASC — Lamport-monotonic, immune to clock skew (per the ζ pattern that ORDERs by rowid ASC for insertion-order; μ uses the Lamport clock instead).

No LIMIT in the public surface (the PM packet does not list it). Wave-4 P4.6.1 may wrap with a LIMIT at the MCP-tool layer; the repository does not impose one.

---

§7. Append-only enforcement — invariant AX-01 (integrity.md)

The contract bars UPDATE, DELETE, ALTER, DROP operations on advisories. Three layers of enforcement:

1. Public API surface. Only insertAdvisory, getAdvisory, listAdvisories are exported. No update*, delete*, clear* symbols.
2. SQL surface. Repository source contains no UPDATE , DELETE , ALTER , DROP SQL tokens.
3. Test gate. A test reads repository.ts source and asserts the four forbidden tokens are absent (mirrors P4.1.1’s static-scanner pattern for Date.now, Math.random).

The static scanner uses readFileSync(repositorySourcePath, 'utf8') and string-search for the four tokens (with trailing space to avoid false positives on words containing them, e.g. drop is not a SQL keyword in our source but might appear in a comment — the trailing space tightens it to SQL usage).

---

§8. JSON serialization of evidence — bigint hazard

evidence: unknown[] in the envelope may contain bigint values (e.g. a timestamp_logical nested in an evidence record). JSON.stringify THROWS on bigint by default with TypeError: Do not know how to serialize a BigInt.

Decision: the repository serializes evidence via JSON.stringify(evidence, replacer) where the replacer converts bigint to a string representation. On read, the parsed array contains the bigint-as-string — round-trip equality holds for non-bigint content. This is a known asymmetry; downstream consumers that need bigint inside evidence must re-parse.

Alternative considered: use κ’s canonicalize(...) from src/domains/rules/canonical.ts to serialize. Canonicalize handles bigint by emitting the bigint’s decimal-string form. Rejected for the repository: canonicalize is for hashing (preimage stability); persistence does not require sorted-key serialization. Plain JSON.stringify with a bigint replacer is sufficient and matches the mcp_witnesses.witnesses pattern in migration 008 (TEXT NOT NULL DEFAULT ‘[]’).

The repository documents the bigint serialization behavior and adds a test that bigint-containing evidence does not throw on insert.

---

§9. Tests — in-memory SQLite, no tmpdir, no Windows WAL

The ζ repository test at src/__tests__/domains/trail/repository.test.ts:70-78 shows the canonical hermetic pattern:

function makeTestDb(): Database.Database {
  const db = new Database(':memory:');
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  db.exec(THOUGHT_RECORDS_MIGRATION_SQL);
  return db;
}

P4.5.1 follows the same pattern:

function makeTestDb(): Database.Database {
  const db = new Database(':memory:');
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  db.defaultSafeIntegers(true);
  db.exec(MCP_ADVISORIES_MIGRATION_SQL);
  return db;
}

The reputation tools.test.ts uses initDb(tmpdir) instead — chosen because P2.5.1 tests the migration runner (007 ordering). P4.5.1 tests the repository, not the runner; the in-memory exec pattern is faster and avoids the Windows WAL-lock concurrency issue.

Test groups (15+ assertions):

• G1 Schema introspection (PRAGMA table_info shows 8 columns, indexes present)
• G2 Insert happy path
• G3 Idempotent insert (same hash → inserted: false)
• G4 CHECK constraints enforced (bad role / check / result / severity → throw)
• G5 getAdvisory roundtrip
• G6 getAdvisory unknown hash → null
• G7 listAdvisories — empty filter, all rows ASC
• G8 listAdvisories — role filter
• G9 listAdvisories — check + severity AND-combined
• G10 listAdvisories — since (bigint) filter
• G11 bigint roundtrip across 2^53 boundary
• G12 Append-only — module exports do not contain update*/delete*/clear*
• G13 Static scanner — repository source has no UPDATE/DELETE/ALTER/DROP
• G14 evidence containing bigint roundtrips
• G15 Determinism check — insert twice across processes (simulated) yields same row count

---

§10. Migration number reservation

Pre-assigned by the PM dispatch packet: 010_mcp_advisories.sql. The latest on main@41226615 is 009_model_candidates.sql. The sibling Wave-3 agents (P4.3.1 + P4.4.1) operate on src/domains/integrity/roles.ts + escalation.ts — neither touches migrations. No collision risk.

Per memory feedback feedback_migration_collision.md: parallel agents MUST use pre-assigned numbers. Compliance: this audit confirms 010 is the next free slot and reserves it.

---

§11. Decision summary

Concern	Decision
Migration number	010 (PM-assigned; verified next free slot)
Migration filename	010_mcp_advisories.sql
Repository file	src/domains/integrity/repository.ts
Test file	src/__tests__/domains/integrity/repository.test.ts
Test posture	In-memory SQLite + db.exec(migrationSQL) per test
bigint mode	db.defaultSafeIntegers(true) per handle
check quoting	"check" in every SQL statement
Dedup	INSERT OR IGNORE + SELECT WHERE decision_hash = ?
Append-only	API surface + SQL token static scanner
Filter dynamic WHERE	Bind-parameterized, no string interpolation
evidence bigint	JSON.stringify with bigint-to-string replacer
ORDER BY	timestamp_logical ASC
Indexes	("check", severity) and (role)

---

§12. Open questions resolved

None. All design choices are captured above. The contract document (Step 2) crystallizes these into invariants; the packet (Step 3) ports them into the file-by-file execution plan.

---

End of P4.5.1 audit.