P0.7.2 — Step 3 Execution Packet

Approved execution plan for P0.7.2 ζ Thought Record CRUD. This packet gates Step 4 implementation; deviations require an amended packet.

---

§1. File inventory

1a. New files

• src/domains/trail/repository.ts — ~310 LOC (including TSDoc). CRUD + MCP tool registration.
• src/__tests__/domains/trail/repository.test.ts — ~650 LOC. 30 tests across 5 describe blocks.
• src/db/migrations/004_thought_records.sql — 20 LOC (header + 1 CREATE TABLE + 2 CREATE INDEX).
• Directories created: src/__tests__/domains/, src/__tests__/domains/trail/. Git tracks files not directories — no .gitkeep needed.

1b. Edited files

• src/db/schema.sql — append ζ ownership block at EOF. +8 lines, comment-only (no executable SQL).
• src/server.ts — add 1 import + 1 call in bootstrap(). +2 lines.

1c. Docs (new — 5-step chain)

• docs/audits/p0-7-2-thought-crud-audit.md — DONE (commit 12b229b9).
• docs/contracts/p0-7-2-thought-crud-contract.md — DONE (commit e7c158e6).
• docs/packets/p0-7-2-thought-crud-packet.md — this file.
• docs/verification/p0-7-2-thought-crud-verification.md — authored in Step 5.

1d. Explicitly NOT edited

• package.json — no new deps.
• jest.config.ts — no config change. Nested src/__tests__/domains/ discovered by existing testMatch.
• tsconfig.json — no config change.
• src/config.ts — repository does not read env.
• src/modes.ts — not relevant.
• src/startup.ts — no change; ζ uses Phase-2 DB singleton via getDb() at call-time.
• src/domains/tasks/*, src/domains/skills/* — disjoint siblings.
• docs/guides/implementation/task-breakdown.md — not amended by this task.

---

§2. Migration file — src/db/migrations/004_thought_records.sql

-- 004_thought_records — ζ Decision Trail table (P0.7.2).
--
-- Introduces the thought_records table that persists hash-chained decision-
-- trail entries. Each record captures a thought (plan/analysis/decision/
-- reflection) linked to the previous record for the same task via prev_hash.
--
-- Columns:
--   id          — UUID v4 (minted by src/domains/trail/repository.ts)
--   type        — one of P0.7.1 THOUGHT_TYPES
--   task_id     — non-empty string; scopes the chain
--   agent_id    — non-empty string; excluded from hash input
--   content     — thought text (empty string allowed)
--   timestamp   — ISO-8601 caller-supplied or minted; INCLUDED in hash
--   prev_hash   — 64 hex chars; ZERO_HASH for first record per task
--   hash        — 64 hex chars; SHA-256 over canonical-JSON of 6-field subset
--   created_at  — ISO-8601 insertion time; distinct from timestamp, NEVER hashed
--
-- Indexes:
--   idx_trail_task  — supports listThoughtRecords({task_id}) + latest-for-task
--                     lookup in createThoughtRecord.
--   idx_trail_prev  — supports P0.7.3 chain verifier hash-link traversal.
--
-- Canonical references:
--   - docs/guides/implementation/task-breakdown.md § P0.7.2
--   - docs/audits/p0-7-2-thought-crud-audit.md
--   - docs/contracts/p0-7-2-thought-crud-contract.md

CREATE TABLE thought_records (
  id          TEXT PRIMARY KEY,
  type        TEXT NOT NULL,
  task_id     TEXT NOT NULL,
  agent_id    TEXT NOT NULL,
  content     TEXT NOT NULL,
  timestamp   TEXT NOT NULL,
  prev_hash   TEXT NOT NULL,
  hash        TEXT NOT NULL UNIQUE,
  created_at  TEXT NOT NULL
);

CREATE INDEX idx_trail_task ON thought_records(task_id, created_at);
CREATE INDEX idx_trail_prev ON thought_records(prev_hash);

Runner behaviour: src/db/index.ts strips line comments, sees non-empty body, wraps in a transaction, executes the 3 statements, bumps user_version to 4.

---

§3. src/db/schema.sql append

-- ζ Decision Trail — introduced by 004_thought_records.sql (P0.7.2).
--   thought_records — hash-chained record rows. 9 columns; 8 exposed to
--                     callers. UNIQUE(hash) enforces idempotency. Per-task
--                     chain via prev_hash; ZERO_HASH for first record per
--                     task. Consumed by P0.7.3 audit_verify_chain.

Appended at EOF. Pure comment block — the file is a shipped asset, never executed.

---

§4. src/server.ts bootstrap edit

Exact diff (add lines only):

// near top with other imports:
+import { registerThoughtTools } from './domains/trail/repository.js';

// inside bootstrap(), after the existing registerColibriTool(ctx, 'server_ping', ...):
+      registerThoughtTools(ctx);

Diff size: +2 lines. The closing await start(ctx); is unchanged. The try/catch wrapping is unchanged.

Wave D collision pattern: P0.2.4 adds registerSystemTools(ctx), P0.3.2 adds registerTaskTools(ctx), P0.6.2 adds registerSkillTools(ctx). All four land in the same bootstrap() region; merge is trivial keep-all (disjoint symbols, ordering non-load-bearing — each tool independently registers its own names).

---

§5. src/domains/trail/repository.ts — skeleton

/**
 * Colibri — Phase 0 ζ Decision Trail repository + MCP tools (P0.7.2).
 *
 * SQLite-backed CRUD for hash-chained thought records, plus the
 * `thought_record` / `thought_record_list` MCP tools. Builds directly on
 * P0.7.1 primitives (computeHash, ZERO_HASH, ThoughtRecord,
 * THOUGHT_TYPES) — no re-implementation.
 *
 * The chain is SCOPED PER task_id: a record's prev_hash is the hash of the
 * most-recently-inserted record for the SAME task_id (or ZERO_HASH when
 * the task has no prior record). Cross-task records are on independent
 * chains.
 *
 * Canonical references:
 *   - docs/guides/implementation/task-breakdown.md § P0.7.2
 *   - docs/audits/p0-7-2-thought-crud-audit.md
 *   - docs/contracts/p0-7-2-thought-crud-contract.md
 *   - docs/packets/p0-7-2-thought-crud-packet.md
 *   - src/domains/trail/schema.ts (P0.7.1 primitives)
 *   - src/db/migrations/004_thought_records.sql (table schema)
 *
 * Consumed by (future):
 *   - P0.7.3 — src/domains/trail/verifier.ts reads the full chain via
 *     listThoughtRecords(...) and re-computes hashes to detect tampering.
 *
 * Purity posture:
 *   - No module-level state. getDb() is called only inside MCP handler
 *     closures (at call-time, after Phase 2 opens the DB).
 *   - No module-level env reads.
 *   - No console output. Errors propagate via throw.
 */

import Database from 'better-sqlite3';
import { randomUUID } from 'node:crypto';

import { z } from 'zod';

import { getDb } from '../../db/index.js';
import type { ColibriServerContext } from '../../server.js';
import { registerColibriTool } from '../../server.js';

import {
  THOUGHT_TYPES,
  ZERO_HASH,
  computeHash,
  type ThoughtRecord,
  type ThoughtType,
} from './schema.js';

/* -------------------------------------------------------------------------- */
/* Public types                                                               */
/* -------------------------------------------------------------------------- */

/** Minimum fields a caller must supply to create a thought record. */
export interface CreateThoughtRecordInput {
  readonly type: ThoughtType;
  readonly task_id: string;
  readonly agent_id: string;
  readonly content: string;
}

/** Filter shape for listThoughtRecords. */
export interface ListThoughtRecordsFilters {
  readonly task_id?: string;
  readonly limit?: number;
}

/** Test-seam for deterministic id + timestamp injection. */
export interface CreateOptions {
  readonly idFn?: () => string;
  readonly nowFn?: () => string;
}

/* -------------------------------------------------------------------------- */
/* Zod schemas (shared by repository validation and MCP tools)                */
/* -------------------------------------------------------------------------- */

const CreateThoughtRecordInputSchema = z.object({
  type: z.enum(THOUGHT_TYPES),
  task_id: z.string().min(1),
  agent_id: z.string().min(1),
  content: z.string(),
});

export const ThoughtRecordToolInputSchema = CreateThoughtRecordInputSchema;

export const ThoughtRecordListToolInputSchema = z.object({
  task_id: z.string().min(1).optional(),
  limit: z.number().int().positive().optional(),
});

/* -------------------------------------------------------------------------- */
/* Internal DB row mapper                                                     */
/* -------------------------------------------------------------------------- */

interface ThoughtRecordRow {
  readonly id: string;
  readonly type: string;
  readonly task_id: string;
  readonly agent_id: string;
  readonly content: string;
  readonly timestamp: string;
  readonly prev_hash: string;
  readonly hash: string;
}

function rowToRecord(row: ThoughtRecordRow): ThoughtRecord {
  return {
    id: row.id,
    type: row.type as ThoughtType,
    task_id: row.task_id,
    agent_id: row.agent_id,
    content: row.content,
    timestamp: row.timestamp,
    prev_hash: row.prev_hash,
    hash: row.hash,
  };
}

/* -------------------------------------------------------------------------- */
/* createThoughtRecord                                                        */
/* -------------------------------------------------------------------------- */

export function createThoughtRecord(
  db: Database.Database,
  input: CreateThoughtRecordInput,
  options: CreateOptions = {},
): ThoughtRecord {
  const parsed = CreateThoughtRecordInputSchema.parse(input);

  const idFn = options.idFn ?? randomUUID;
  const nowFn = options.nowFn ?? ((): string => new Date().toISOString());

  const id = idFn();
  const timestamp = nowFn();
  const createdAt = timestamp;

  const latestPrev = db.prepare<[string], { hash: string }>(
    `SELECT hash FROM thought_records
       WHERE task_id = ?
       ORDER BY created_at DESC, id DESC
       LIMIT 1`,
  );

  const insert = db.prepare(
    `INSERT INTO thought_records
       (id, type, task_id, agent_id, content, timestamp, prev_hash, hash, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
  );

  const tx = db.transaction((): ThoughtRecord => {
    const prior = latestPrev.get(parsed.task_id);
    const prevHash = prior?.hash ?? ZERO_HASH;
    const hash = computeHash({
      id,
      type: parsed.type,
      task_id: parsed.task_id,
      content: parsed.content,
      timestamp,
      prev_hash: prevHash,
    });
    insert.run(
      id,
      parsed.type,
      parsed.task_id,
      parsed.agent_id,
      parsed.content,
      timestamp,
      prevHash,
      hash,
      createdAt,
    );
    return {
      id,
      type: parsed.type,
      task_id: parsed.task_id,
      agent_id: parsed.agent_id,
      content: parsed.content,
      timestamp,
      prev_hash: prevHash,
      hash,
    };
  });

  return tx();
}

/* -------------------------------------------------------------------------- */
/* getThoughtRecord                                                           */
/* -------------------------------------------------------------------------- */

export function getThoughtRecord(
  db: Database.Database,
  id: string,
): ThoughtRecord | null {
  const stmt = db.prepare<[string], ThoughtRecordRow>(
    `SELECT id, type, task_id, agent_id, content, timestamp, prev_hash, hash
       FROM thought_records WHERE id = ?`,
  );
  const row = stmt.get(id);
  return row === undefined ? null : rowToRecord(row);
}

/* -------------------------------------------------------------------------- */
/* listThoughtRecords                                                         */
/* -------------------------------------------------------------------------- */

export function listThoughtRecords(
  db: Database.Database,
  filters: ListThoughtRecordsFilters = {},
): ThoughtRecord[] {
  const hasTask = filters.task_id !== undefined;
  const hasLimit = filters.limit !== undefined;

  // 4 SQL shapes, each statically typed.
  let rows: ThoughtRecordRow[];
  if (hasTask && hasLimit) {
    const stmt = db.prepare<[string, number], ThoughtRecordRow>(
      `SELECT id, type, task_id, agent_id, content, timestamp, prev_hash, hash
         FROM thought_records WHERE task_id = ?
         ORDER BY created_at ASC, id ASC LIMIT ?`,
    );
    rows = stmt.all(filters.task_id as string, filters.limit as number);
  } else if (hasTask) {
    const stmt = db.prepare<[string], ThoughtRecordRow>(
      `SELECT id, type, task_id, agent_id, content, timestamp, prev_hash, hash
         FROM thought_records WHERE task_id = ?
         ORDER BY created_at ASC, id ASC`,
    );
    rows = stmt.all(filters.task_id as string);
  } else if (hasLimit) {
    const stmt = db.prepare<[number], ThoughtRecordRow>(
      `SELECT id, type, task_id, agent_id, content, timestamp, prev_hash, hash
         FROM thought_records
         ORDER BY created_at ASC, id ASC LIMIT ?`,
    );
    rows = stmt.all(filters.limit as number);
  } else {
    const stmt = db.prepare<[], ThoughtRecordRow>(
      `SELECT id, type, task_id, agent_id, content, timestamp, prev_hash, hash
         FROM thought_records
         ORDER BY created_at ASC, id ASC`,
    );
    rows = stmt.all();
  }
  return rows.map(rowToRecord);
}

/* -------------------------------------------------------------------------- */
/* registerThoughtTools                                                       */
/* -------------------------------------------------------------------------- */

export function registerThoughtTools(ctx: ColibriServerContext): void {
  registerColibriTool(
    ctx,
    'thought_record',
    {
      title: 'thought_record',
      description:
        'Append a hash-chained decision-trail record for the given task.',
      inputSchema: ThoughtRecordToolInputSchema,
    },
    (input): ThoughtRecord => createThoughtRecord(getDb(), input),
  );

  registerColibriTool(
    ctx,
    'thought_record_list',
    {
      title: 'thought_record_list',
      description:
        'List decision-trail records in insertion order; optionally filter by task_id.',
      inputSchema: ThoughtRecordListToolInputSchema,
    },
    (input): { records: ThoughtRecord[] } => ({
      records: listThoughtRecords(getDb(), input),
    }),
  );
}

Notes on ordering tiebreaker (ORDER BY created_at ASC, id ASC): If two records share the same created_at (possible when tests inject nowFn returning a fixed string), the secondary id sort keeps ordering deterministic. Production inserts using default nowFn produce monotonically-increasing ISO timestamps (process wall clock is monotonic at millisecond resolution for the non-concurrent Phase-0 stdio process; tool-lock serializes writes).

Notes on exact-optional-property-types: listThoughtRecords cannot pass filters.task_id directly if it may be undefined under strict mode; we narrow via hasTask + explicit cast. Same for limit. The local variables + 4-branch static SQL avoid dynamic SQL string-building (no injection surface and no template-escape).

---

§6. Test file — src/__tests__/domains/trail/repository.test.ts

6a. Structure

1-30     Imports + helpers (makeDb, makeLinkedPair, applyMigration, frozenNow, fixedId)
32-80    beforeEach / afterEach (reset DB, close clients)
82-340   describe('createThoughtRecord'): tests 1-14
342-420  describe('getThoughtRecord'): tests 15-17
422-520  describe('listThoughtRecords'): tests 18-24
522-600  describe('determinism'): tests 9, 10 (duplicated for grouped anchor)
602-700  describe('thought_record MCP tool'): tests 25-26
702-780  describe('thought_record_list MCP tool'): tests 27-28
782-830  describe('registerThoughtTools'): tests 29-30

Total tests: 30.

6b. Helper — makeDb()

import Database from 'better-sqlite3';
import { readFileSync } from 'node:fs';
import { dirname, resolve } from 'node:path';
import { fileURLToPath } from 'node:url';

const __dirname = dirname(fileURLToPath(import.meta.url));
const MIGRATION_004 = resolve(
  __dirname, '..', '..', '..', 'db', 'migrations', '004_thought_records.sql',
);

function makeDb(): Database.Database {
  const db = new Database(':memory:');
  db.pragma('journal_mode = MEMORY');  // WAL unavailable on :memory:
  db.pragma('foreign_keys = ON');
  const sql = readFileSync(MIGRATION_004, 'utf8');
  // Strip line comments to match the runner's behavior.
  const body = sql.replace(/--[^\n]*/g, '').trim();
  db.exec(body);
  return db;
}

Uses :memory: — every test gets a fresh, isolated DB. No disk I/O.

6c. Helper — makeLinkedPair() (MCP tool e2e)

Reproduces the server.test.ts harness but pins a DB singleton via initDb(tempFilePath) so getDb() resolves inside tool handlers. Uses OS tempdir for file-backed DB (WAL requires disk), applies migration 004 post-init by extracting and re-running the file. Returns {ctx, client, db, cleanup}.

Cleanup: closeDb(), client.close(), stop(ctx), rmSync(tempDir).

6d. Test names (binding)

describe('createThoughtRecord')
  1. creates a genesis record with prev_hash === ZERO_HASH
  2. second record in same task links prev_hash to first.hash
  3. two tasks have independent chains — both start at ZERO_HASH
  4. mints a UUID v4 shaped id by default
  5. mints an ISO-8601 timestamp by default
  6. preserves all 4 thought types round-trip
  7. preserves agent_id in the stored record
  8. accepts empty-string content
  9. produces the P0.7.1 pinned hash for the canonical fixed input
  10. the returned hash matches schema.computeHash over the 6-field subset
  11. Zod rejects missing type
  12. Zod rejects empty task_id
  13. Zod rejects empty agent_id
  14. Zod rejects invalid type value

describe('getThoughtRecord')
  15. returns null for missing id
  16. returns full 8-field record after insert
  17. returns the correct record across multiple tasks

describe('listThoughtRecords')
  18. empty DB returns empty array
  19. no filter returns all records
  20. task_id filter returns only matching records
  21. orders results by insertion order (ASC)
  22. limit clamps result length
  23. task_id + limit combine
  24. listed chain has intact prev_hash linkage (each prev_hash === previous.hash)

describe('thought_record MCP tool')
  25. returns envelope {ok:true, data: ThoughtRecord}
  26. rejects invalid type with INVALID_PARAMS envelope

describe('thought_record_list MCP tool')
  27. returns envelope {ok:true, data: {records: []}} on empty
  28. filters by task_id through the tool

describe('registerThoughtTools')
  29. registers both tool names on the ctx
  30. throws when called twice (double-registration)

6e. Pinned-hash assertion (test 9)

it('produces the P0.7.1 pinned hash for the canonical fixed input', () => {
  const db = makeDb();
  const out = createThoughtRecord(
    db,
    { type: 'plan', task_id: 't1', agent_id: 'a1', content: 'hello' },
    { idFn: () => 'r1', nowFn: () => '2026-04-17T00:00:00Z' },
  );
  expect(out.hash).toBe(
    '6a2f9597f563d5515cfa69891a51806d0f93bfbe222997d3ba37c365ceee3f1a',
  );
  expect(out.prev_hash).toBe(ZERO_HASH);
});

This anchors P0.7.2’s chain algorithm against P0.7.1’s pinned hash. If either canonicalize or computeHash ever drift, this test fails at the integration layer BEFORE any drift reaches production.

---

§7. Coverage plan

Target: 100% stmt / 100% branch / 100% func / 100% line on src/domains/trail/repository.ts. Branch accounting:

Branch site	Test(s)
options.idFn ?? randomUUID	#4 (default path) + #9 (injected path)
options.nowFn ?? (...)	#5 (default) + #9 (injected)
prior?.hash ?? ZERO_HASH	#1 (empty → ZERO_HASH) + #2 (prior exists)
getThoughtRecord row === undefined	#15 (missing → null) + #16 (found → record)
listThoughtRecords 4 SQL shapes	#19 (no filter), #20 (task), #22 (limit), #23 (both)
rowToRecord	covered by any list / get test
registerThoughtTools both tools	#29 + #30
Tool handler throw path (sink error handled by middleware)	#26 + Zod path in repository (#11-#14) exercise both the tool’s Stage-2 validator and the repository’s direct Zod guard

No defensive unreachable code. Every branch has ≥1 test.

---

§8. Commit plan

Step	Commit message	Files
1. Audit	audit(p0-7-2-thought-crud): inventory ζ hash primitives + DB + server surface (DONE 12b229b9)	audit .md
2. Contract	contract(p0-7-2-thought-crud): behavioral contract (DONE e7c158e6)	contract .md
3. Packet	packet(p0-7-2-thought-crud): execution plan	this file
4. Implement	feat(p0-7-2-thought-crud): ζ trail repository + thought_record + thought_record_list tools	migration + schema.sql + repository.ts + test + server.ts
5. Verify	verify(p0-7-2-thought-crud): test evidence	verification .md

5 commits total. Step 4 is a single commit for atomicity — repo + tests + server.ts land together (otherwise the intermediate state would fail CI, since the server.ts edit depends on the export).

---

§9. Risks + mitigations

Risk	Likelihood	Impact	Mitigation
Bootstrap() 3-way merge (P0.2.4, P0.3.2, P0.6.2)	HIGH	Trivial	Keep diff to +import + +registerThoughtTools(ctx);. Sigma keep-all.
src/db/schema.sql 3-way append	HIGH	Trivial	Pure EOF append of comment block. Order-insensitive.
exactOptionalPropertyTypes trip in list() filter	MEDIUM	Build break	Use hasTask + hasLimit locals with cast; avoid dynamic SQL string-building. Confirmed at packet layer.
:memory: DB WAL pragma mismatch	LOW	Test flake	Use journal_mode = MEMORY for in-memory tests (WAL requires disk). Production uses WAL via initDb.
Nested test dir undiscovered	LOW	Missed tests	jest.config.ts testMatch: ['**/__tests__/**/*.test.ts'] discovers nested. Confirmed by reading jest.config.ts line 15.
randomUUID mocked at wrong module depth	LOW	Test noise	Inject via options.idFn; never mock node:crypto directly.
Cross-worktree leak (Wave C feedback)	LOW	Shipped-dirty PR	Pre-commit git status re-checked before every commit; explicit whitelist of file patterns.
Pinned-hash drift if ordering keys change	LOW	Determinism regression	Canonicalization is fixed by P0.7.1; this task re-asserts the hash. Cross-test anchor prevents silent drift.
Transaction + prepared statement lifetime	LOW	SQLite error	All prepared stmts are created inside createThoughtRecord (per-call; better-sqlite3 caches). Transaction wraps the entire operation.
P0.7.3 verifier depends on idx_trail_prev	LOW (future)	Perf regression	Index ships now. Future-proof.

---

§10. Non-deviation check

Lock from dispatch prompt	Where satisfied
Test path src/__tests__/domains/trail/repository.test.ts	§1a
Migration file name 004_thought_records.sql	§1a / §2
Domain is trail, not thought	§5 (import path ./domains/trail/schema.js)
Tool names thought_record + thought_record_list (snake_case)	§5 registerThoughtTools block
Reuse P0.7.1 computeHash / canonicalize / ZERO_HASH	§5 imports; no re-implementation
Pinned hash 6a2f9597... asserted	§6e test 9
thought_records table schema matches prompt baseline	§2 (9 cols)
Chain scope = per-task (verified via contract §3)	§5 createThoughtRecord SQL WHERE task_id = ?
Tools registered via registerColibriTool	§5 registerThoughtTools
schema.sql appended with ownership block	§3
Bootstrap edit is minimal (+2 lines)	§4
≥25 tests with 100% branch	§6d (30 tests); §7 (branch accounting)

No deviations beyond those approved by audit §3 (prompt §7 nullable task_id → Zod-required-min(1) per P0.7.1 lock) and audit §4 (prompt §8 global-chain default → per-task chain per P0.7.1 contract §3).

---

§11. Packet acceptance

• File inventory (§1).
• Migration SQL body (§2).
• schema.sql append (§3).
• server.ts bootstrap edit (§4).
• Repository skeleton with exact imports + exports (§5).
• 30-test matrix with exact names + helpers (§6).
• Coverage branch accounting (§7).
• Commit plan (§8).
• Risk register + Wave D collision plan (§9).
• Dispatch-prompt non-deviation check (§10).

Proceeding to Step 4 (implementation).