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

Date: 2026-05-14 Branch: feature/p4-5-1-advisory-persistence Step: 3 of 5 — execution plan Builds on: docs/audits/p4-5-1-advisory-persistence-audit.md, docs/contracts/p4-5-1-advisory-persistence-contract.md

This packet is the file-by-file execution plan. Step 4 (feat) lands the three files in a single commit; Step 5 (verify) writes the test evidence document.

§1. Files to land in Step 4

§1.1 src/db/migrations/010_mcp_advisories.sql — new

8-column table + 2 indexes. CHECK constraints mirror the P4.1.1 envelope enums byte-for-byte. "check" quoted everywhere. Idempotent via IF NOT EXISTS.

The full DDL is the §2 block of the contract; the migration file copies it verbatim and adds a comment header following the 008/009 prose convention.

§1.2 src/domains/integrity/repository.ts — new

Three exported functions + two exported types. Full body sketched in §3 below.

§1.3 src/__tests__/domains/integrity/repository.test.ts — new

15 test groups, 25 ACs. Hermetic in-memory SQLite per test. Full structure sketched in §4 below.

No other files are touched. The sibling Wave-3 agents own roles.ts and escalation.ts.

§2. The migration body — verbatim copy 

-- 010_mcp_advisories — μ Phase 4 Integrity Monitor — advisory persistence (P4.5.1).
--
-- The shipped `mcp_advisories` table that R94 Wave 3 establishes. Holds the
-- 8-field advisory envelope from P4.1.1 (`src/domains/integrity/schema.ts`)
-- with CHECK constraints mirroring the closed-enum schemas:
--
--   role       — Translator / Sentinel / Guide        (3 values)
--   check      — circular_logic / coercion_trap /     (4 values)
--                axiom_drift / axiom_regression
--   result     — PASS / WARN / BLOCK                  (3 values)
--   severity   — LOW / MED / HIGH                     (3 values)
--
-- The `decision_hash` UNIQUE constraint is the dedup gate per integrity.md
-- §Advisory record schema L146 — repository INSERT OR IGNORE collapses
-- byte-identical advisories on write rather than storing duplicates.
--
-- Two indexes ship: `(check, severity)` for the typical "show me all HIGH-
-- severity coercion_trap warnings" query, and `(role)` for role-faceted
-- audits.
--
-- `"check"` is a SQL keyword — it is quoted in every DDL position. SQLite's
-- tokenizer accepts unquoted `check` in some contexts but silently mis-parses
-- in others; consistent quoting is the contract.
--
-- `timestamp_logical INTEGER` carries a uint64 Lamport clock value. The
-- repository sets `db.defaultSafeIntegers(true)` per handle so the column
-- round-trips as `bigint` rather than losing precision above 2^53.
--
-- Phase 0 posture (integrity.md §165-170): no Phase 0 tool reads or writes
-- this table. P4.5.1 lands the surface; P4.6.1 (Wave 4) wires `integrity_*`
-- MCP tools against it.
--
-- Append-only contract (AX-01 + design invariant 5): no UPDATE/DELETE/ALTER
-- statement on this table appears anywhere in `src/`. Enforced via the
-- repository's exported surface (insert/get/list only) plus a static
-- scanner test (repository.test.ts G13).
--
-- node-style FOREIGN KEYs are not used. `decision_hash` IS the de-facto
-- primary key — no `id`, no `created_at`, no auto-increment.
--
-- Canonical references:
--   - docs/guides/implementation/task-prompts/p4.1-mu-integrity.md §P4.5.1
--   - docs/3-world/physics/enforcement/integrity.md §Phase 4 scope (L172-178)
--   - docs/3-world/physics/enforcement/integrity.md §Advisory record schema (L129-146)
--   - src/domains/integrity/schema.ts (P4.1.1 envelope)
--   - docs/audits/p4-5-1-advisory-persistence-audit.md
--   - docs/contracts/p4-5-1-advisory-persistence-contract.md
--   - docs/packets/p4-5-1-advisory-persistence-packet.md

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);

-- DOWN (manual; the runner is forward-only — see src/db/index.ts):
--   DROP INDEX IF EXISTS idx_advisories_role;
--   DROP INDEX IF EXISTS idx_advisories_check_severity;
--   DROP TABLE IF EXISTS mcp_advisories;
--   PRAGMA user_version = 9;

§3. The repository body — code-level plan 

/**
 * Colibri — Phase 4 μ Integrity Monitor — Advisory Persistence (P4.5.1).
 * <header doc covering audit + contract references>
 */

import type Database from 'better-sqlite3';

import {
  type Advisory,
  type AdvisoryCheck,
  type AdvisoryResult,
  type AdvisoryRole,
  type AdvisorySeverity,
} from './schema.js';

// Re-export types so callers can reach for them via the repository path.
export type {
  Advisory,
  AdvisoryCheck,
  AdvisoryResult,
  AdvisoryRole,
  AdvisorySeverity,
};

/* Row-shape interface (DB layer, bigint timestamp). */
interface AdvisoryRow {
  readonly role: string;
  readonly check: string;
  readonly result: string;
  readonly severity: string;
  readonly evidence: string;
  readonly recommendation: string;
  readonly decision_hash: string;
  readonly timestamp_logical: bigint;
}

/* JSON serialization helpers — bigint → decimal-string replacer. */
function bigintReplacer(_key: string, value: unknown): unknown {
  return typeof value === 'bigint' ? value.toString() : value;
}

function serializeEvidence(evidence: readonly unknown[]): string {
  return JSON.stringify(evidence, bigintReplacer);
}

function deserializeEvidence(raw: string): unknown[] {
  const parsed: unknown = JSON.parse(raw);
  if (!Array.isArray(parsed)) {
    throw new Error(
      `Corrupt evidence column: expected JSON array, got ${typeof parsed}`,
    );
  }
  return parsed as unknown[];
}

/* Row → Advisory mapper. The closed enums are trusted because CHECK
   constraints rejected anything else at insert time. */
function rowToAdvisory(row: AdvisoryRow): Advisory {
  return {
    role: row.role as AdvisoryRole,
    check: row.check as AdvisoryCheck,
    result: row.result as AdvisoryResult,
    severity: row.severity as AdvisorySeverity,
    evidence: deserializeEvidence(row.evidence),
    recommendation: row.recommendation,
    decision_hash: row.decision_hash,
    timestamp_logical: row.timestamp_logical,
  };
}

/* InsertResult — the tagged union returned by insertAdvisory. */
export type InsertResult =
  | { readonly inserted: true }
  | { readonly inserted: false; readonly existing: Advisory };

/* AdvisoryFilter — listAdvisories shape. */
export type AdvisoryFilter = {
  readonly role?: AdvisoryRole;
  readonly check?: AdvisoryCheck;
  readonly severity?: AdvisorySeverity;
  readonly result?: AdvisoryResult;
  readonly since?: bigint;
};

/* ------------------------------------------------------------------ */
/* insertAdvisory                                                     */
/* ------------------------------------------------------------------ */

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

  const insertStmt = db.prepare(
    `INSERT OR IGNORE INTO mcp_advisories
       (role, "check", result, severity, evidence, recommendation, decision_hash, timestamp_logical)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
  );

  const selectStmt = db.prepare<[string], AdvisoryRow>(
    `SELECT role, "check" AS "check", result, severity, evidence, recommendation, decision_hash, timestamp_logical
       FROM mcp_advisories WHERE decision_hash = ?`,
  );

  const tx = db.transaction((): InsertResult => {
    const result = insertStmt.run(
      advisory.role,
      advisory.check,
      advisory.result,
      advisory.severity,
      serializeEvidence(advisory.evidence),
      advisory.recommendation,
      advisory.decision_hash,
      advisory.timestamp_logical,
    );
    if (result.changes === 1) {
      return { inserted: true };
    }
    // Dedup path — row already exists. Re-select to return the persisted form.
    const row = selectStmt.get(advisory.decision_hash);
    if (row === undefined) {
      // Defensive: changes === 0 AND no matching row would mean a
      // CHECK-constraint silent failure, which better-sqlite3 throws on,
      // so this branch is unreachable in practice.
      throw new Error(
        `insertAdvisory: row not inserted and decision_hash not found (decision_hash=${advisory.decision_hash})`,
      );
    }
    return { inserted: false, existing: rowToAdvisory(row) };
  });

  return tx();
}

/* ------------------------------------------------------------------ */
/* getAdvisory                                                        */
/* ------------------------------------------------------------------ */

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

  const stmt = db.prepare<[string], AdvisoryRow>(
    `SELECT role, "check" AS "check", result, severity, evidence, recommendation, decision_hash, timestamp_logical
       FROM mcp_advisories WHERE decision_hash = ?`,
  );
  const row = stmt.get(decision_hash);
  return row === undefined ? null : rowToAdvisory(row);
}

/* ------------------------------------------------------------------ */
/* listAdvisories                                                     */
/* ------------------------------------------------------------------ */

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

  // Build the parameterized WHERE clause from the non-undefined filter
  // fields. The fragments below MUST be string literals (no
  // user-controlled substring is ever embedded); bind parameters carry
  // every value.
  const where: string[] = [];
  const params: Array<string | bigint> = [];

  if (filter.role !== undefined) {
    where.push(`role = ?`);
    params.push(filter.role);
  }
  if (filter.check !== undefined) {
    where.push(`"check" = ?`);
    params.push(filter.check);
  }
  if (filter.severity !== undefined) {
    where.push(`severity = ?`);
    params.push(filter.severity);
  }
  if (filter.result !== undefined) {
    where.push(`result = ?`);
    params.push(filter.result);
  }
  if (filter.since !== undefined) {
    where.push(`timestamp_logical >= ?`);
    params.push(filter.since);
  }

  const whereClause = where.length === 0 ? '' : `WHERE ${where.join(' AND ')}`;
  const sql = `SELECT role, "check" AS "check", result, severity, evidence, recommendation, decision_hash, timestamp_logical
                 FROM mcp_advisories ${whereClause}
                 ORDER BY timestamp_logical ASC`;

  const stmt = db.prepare<typeof params, AdvisoryRow>(sql);
  const rows = stmt.all(...params);
  return rows.map(rowToAdvisory);
}

Important note on the column alias. SQLite’s "check" AS “check” preserves the name in the bound row. better-sqlite3 surfaces row columns by name; the row interface declares the column as check. Both halves use the quoted form so the SQL parses unambiguously and the JS side sees row.check. (TypeScript permits check as an object property name without escaping — only the with statement’s binding is reserved.)

§4. The test body — code-level plan 

import { readFileSync } from 'node:fs';
import { dirname, join } from 'node:path';
import { fileURLToPath } from 'node:url';

import Database from 'better-sqlite3';

import {
  type Advisory,
  AdvisorySchema,
  computeDecisionHash,
} from '../../../domains/integrity/schema.js';
import * as repositoryModule from '../../../domains/integrity/repository.js';
import {
  insertAdvisory,
  getAdvisory,
  listAdvisories,
  type AdvisoryFilter,
} from '../../../domains/integrity/repository.js';

/* Migration fixture. */
const MIGRATIONS_DIR = (() => {
  const here = dirname(fileURLToPath(import.meta.url));
  return join(here, '..', '..', '..', 'db', 'migrations');
})();
const MCP_ADVISORIES_MIGRATION_SQL = readFileSync(
  join(MIGRATIONS_DIR, '010_mcp_advisories.sql'),
  'utf-8',
);

const REPOSITORY_SOURCE_PATH = (() => {
  const here = dirname(fileURLToPath(import.meta.url));
  return join(here, '..', '..', '..', 'domains', 'integrity', 'repository.ts');
})();

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;
}

/* Fixture builder. */
function makeAdvisory(overrides: Partial<Advisory> = {}): Advisory {
  const role = overrides.role ?? 'Sentinel';
  const check = overrides.check ?? 'circular_logic';
  const result = overrides.result ?? 'WARN';
  const input = { record_a: 'rec-1', record_b: 'rec-2', cycle: true };
  const hash = overrides.decision_hash ?? computeDecisionHash(role, check, input, result);
  return {
    role,
    check,
    result,
    severity: 'MED',
    evidence: [{ kind: 'thought_record', id: 'rec-1' }],
    recommendation: 'Cycle detected.',
    decision_hash: hash,
    timestamp_logical: 1n,
    ...overrides,
  } as Advisory;
}

let db: Database.Database;
beforeEach(() => { db = makeTestDb(); });
afterEach(() => { db.close(); });

/* G1 — schema introspection (7 ACs) */
describe('migration 010_mcp_advisories', () => {
  it('creates the mcp_advisories table with 8 columns (AC1, AC2)', () => { ... });
  it('creates idx_advisories_check_severity (AC3, AC4)', () => { ... });
  it('creates idx_advisories_role (AC5)', () => { ... });
  it('CHECK constraint rejects invalid role (AC6.a)', () => { ... });
  it('CHECK constraint rejects invalid check (AC6.b)', () => { ... });
  it('CHECK constraint rejects invalid result (AC6.c)', () => { ... });
  it('CHECK constraint rejects invalid severity (AC6.d)', () => { ... });
  it('UNIQUE constraint catches duplicate decision_hash (AC7)', () => { ... });
});

/* G2 — insertAdvisory happy path (1 AC) */
describe('insertAdvisory — happy path', () => {
  it('returns { inserted: true } and stores 1 row (AC8)', () => { ... });
});

/* G3 — idempotent insert (3 ACs) */
describe('insertAdvisory — idempotent', () => {
  it('returns { inserted: false, existing } on duplicate hash (AC9)', () => { ... });
  it('does not throw on duplicate (AC10)', () => { ... });
  it('row count stays at 1 after 2 identical inserts (AC11)', () => { ... });
});

/* G5/G6 — getAdvisory (2 ACs) */
describe('getAdvisory', () => {
  it('round-trips structurally (AC12)', () => { ... });
  it('returns null for unknown hash (AC13)', () => { ... });
});

/* G7-G10 — listAdvisories (4 ACs) */
describe('listAdvisories', () => {
  it('empty filter returns all rows ASC by timestamp_logical (AC14)', () => { ... });
  it('role filter (AC15)', () => { ... });
  it('check + severity AND-combined (AC16)', () => { ... });
  it('since filter (inclusive bigint) (AC17)', () => { ... });
});

/* G11 — bigint roundtrip across 2^53 (1 AC) */
describe('bigint timestamp_logical', () => {
  it('survives roundtrip above 2^53 (AC18)', () => { ... });
});

/* G12 — append-only export surface (1 AC) */
describe('append-only export surface', () => {
  it('repository module exports no update*/delete*/clear*/mutate*/remove*/drop* (AC19)', () => { ... });
});

/* G13 — static SQL token scanner (4 ACs) */
describe('repository.ts source — no mutation SQL', () => {
  it('contains no UPDATE SQL token (AC20)', () => { ... });
  it('contains no DELETE SQL token (AC21)', () => { ... });
  it('contains no ALTER SQL token (AC22)', () => { ... });
  it('contains no DROP SQL token (AC23)', () => { ... });
});

/* G14 — bigint inside evidence (1 AC) */
describe('evidence containing bigint', () => {
  it('insert + read does not throw (AC24)', () => { ... });
});

/* G15 — filter smoke (1 AC) */
describe('listAdvisories — filter smoke', () => {
  it('all other filter combinations do not crash (AC25)', () => { ... });
});

The static scanner uses \\bUPDATE \\b regex with the trailing space already in the literal, so e.g. the word “update” inside a comment WITHOUT the trailing space does not false-positive. (Re-checked: the audit’s tokens are spelled UPDATE , DELETE , ALTER , DROP with trailing whitespace. Test G13 reads the file once and runs the four checks.)

§5. Build/lint expectations

  • npm run build must compile without TypeScript errors. The repository uses only better-sqlite3’s public type surface (Database.Database, prepared-statement generics).
  • npm run lint must pass. The repository:
    • Has no unused imports.
    • Uses readonly on type parameters (consistent with Advisory from schema).
    • Has no console.* calls.
    • Has no module-level Date.now() / Math.random().
    • Has explicit return types on every exported function.
  • npm test must pass — base of ~3650 tests at 41226615; +25 new tests; total ~3675.

§6. Step 4 implementation order

  1. Write src/db/migrations/010_mcp_advisories.sql.
  2. Write src/domains/integrity/repository.ts.
  3. Write src/__tests__/domains/integrity/repository.test.ts.
  4. Run npm run build → fix typing issues.
  5. Run npm run lint → fix lint issues.
  6. Run npm test (full suite) → confirm 25 new tests pass.
  7. Single commit: feat(p4-5-1-advisory-persistence): mcp_advisories table + append-only repository.

§7. Step 5 verification document

The verification doc records actual test counts, build status, lint status, and notes any retry-clean flakes (the known reputation/tools.test.ts parallel-migration race + the new kimi.test.ts 50ms timing flake — neither is in scope for this task).

End of P4.5.1 packet.

Back to top

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

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