P0.2.2 — Step 1 Audit

Inventory of the worktree against the task spec for P0.2.2 SQLite Initialization (α System Core task group, second α task). Scope: what already exists that the new DB layer must integrate with, and what is absent.

Baseline: worktree E:/AMS/.worktrees/claude/p0-2-2-sqlite-init/ at commit c8143bfc (R75 skills mirror resync, on top of Wave A MCP server bootstrap 40cd679d).

§1. Surface being added

Targets this task creates:

  • src/db/index.ts — new module. Holds initDb(path), getDb(), closeDb(), and the migration runner (file-based, lexicographic scan of src/db/migrations/*.sql, PRAGMA user_version tracking). Does not yet exist.
  • src/db/schema.sql — shipped asset (not executed), referenced by package.json#files line 15. Holds a human-readable header describing the earning rule. The filename already appears in package.json but the file itself does not exist.
  • src/db/migrations/001_init.sql — new file. α’s first migration slot; empty body (comment-only) so α ships with zero user tables per the earning rule.
  • src/__tests__/db-init.test.ts — new test file, co-located with other Phase-0 tests (deviation from spec’s tests/db/init.test.ts; see §4e).

A worktree scan confirms absence of the authoring targets:

  • ls src/db/ → “No such file or directory”
  • ls src/db/migrations/ → directory does not exist
  • grep -rn "better-sqlite3\|initDb\|getDb\|closeDb\|user_version\|PRAGMA" src/ → zero matches
  • grep -rn "db-init\|db\.test\|sqlite.*test" src/__tests__/ → zero matches

This is a greenfield module set. P0.2.2 authors all four files in a single PR.

§2. Adjacent code that the new module must integrate with

2a. src/config.ts (95 lines — P0.1.4, commit 3bd154a7)

Authoritative Phase-0 environment wrapper. Fields the new module consumes or references:

  • config.COLIBRI_DB_PATH: string (default 'data/colibri.db') — line 48. This is the canonical source of the runtime DB path. getDb() MUST NOT inline-default elsewhere; any production caller passes config.COLIBRI_DB_PATH into initDb() explicitly at the P0.2.3 two-phase startup seam. This task does not make getDb() lazy-init from config — the packet forbids that (see §3 below).
  • assertNoDonorNamespace(env) runs at module load. Importing config transitively enforces the AMS_* absence guard. src/db/index.ts does NOT re-check AMS_* — config has already thrown if any is present.
  • The eager frozen snapshot + pure factory pattern is the idiom to mirror: src/db/index.ts has no Zod schema of its own, but the singleton + explicit-parameter split (initDb(path) pure, getDb() returns cached instance) is the DB-layer analog.

2b. src/modes.ts (186 lines — P0.4.1, commit a64d7349)

Runtime mode enum + capability matrix. The new module does not consume detectMode directly — P0.2.3 (two-phase startup) will gate initDb on capabilitiesFor(mode).canWriteDatabase. For P0.2.2, initDb is mode-agnostic: it always opens the file and runs migrations. The gate is the caller’s responsibility.

However, the style is load-bearing:

  • Frozen singletons (FULL_CAPS, READONLY_CAPS, …) — the DB module’s internal instance variable is the moral analog, except it is a mutable cell (null until initDb, set on first call, cleared on closeDb).
  • Pure detectMode(env) with no eager side-effects — the new module mirrors this: initDb(path) is a factory; getDb() throws before initDb() rather than lazy-initializing (deviation from spec file’s example on lines 437-442; see §4f).

2c. src/server.ts (559 LOC — P0.2.1, commit 40cd679d)

MCP server bootstrap. Documents the 5-stage middleware chain that P0.2.3 (two-phase startup) will use the DB layer from. Key relevant points:

  • bootstrap() is the hook where initDb will be called (P0.2.3 task, not P0.2.2). Currently it is a no-op placeholder.
  • The AuditSink seam at src/server.ts is where P0.7 (ζ Decision Trail) will plug in. ζ will need a live DB handle — which means src/db/index.ts must expose getDb() synchronously without awaiting anything, because the middleware chain is sync-friendly.
  • The server’s logger (defaults to console.error) is the pattern for boot-time output. The new DB module MUST NOT console.log (stdout pollution corrupts MCP JSON-RPC wire format — donor bug #3, documented in src/server.ts lines 27-29). Errors propagate via throw; no console calls inside initDb.

2d. src/__tests__/config.test.ts (207 lines) and src/__tests__/modes.test.ts

Establish the per-test env-isolation pattern and the filesystem discipline:

  • beforeEach / afterEach for env restoration — the DB test file does NOT mutate env (uses initDb(explicitPath)), so this scaffolding is lighter.
  • Unique temp path per test. The DB test file uses os.tmpdir() + a UUID + fs.rmSync(path, { recursive: true, force: true }) in afterEach to avoid cross-test pollution.
  • No jest.isolateModulesAsync — the zod v3 locale-cache bug documented in src/__tests__/config.test.ts lines 10-14 would trip here too. The DB module has no eager side-effects (initDb is a pure factory taking explicit path), so module-load isolation is not required.
  • spawnSync + tsx is available for eager-module-load cases but the DB module design avoids that need entirely.

2e. src/__tests__/server.test.ts (1387 LOC)

Larger test file — establishes conventions for complex Phase-0 surfaces. Relevant:

  • Pure-factory test style (no process.env mutation except dedicated subprocess tests).
  • Tests live in src/__tests__/ per jest.config.ts line 15 roots: ['<rootDir>/src'].
  • The singleton pattern (createNoOpAuditSink) returns frozen objects — the DB module exports Database.Database (vendor type, not frozen; that is acceptable because better-sqlite3 Database is designed to be the singleton handle).

2f. package.json line 15

Declares "src/db/schema.sql" in the files array. This means the file must exist for npm publish to succeed and npm pack to bundle the expected surface. P0.2.2 creates the file; prior to this task the declaration is a forward-reference.

2g. jest.config.ts — coverage discipline

Lines 42-46 collect coverage from src/**/*.ts excluding __tests__/. The new src/db/index.ts is automatically included; target is 100% stmt/func/line and ≥90% branch (matching the Wave A pattern).

§3. Spec reconciliation — three load-bearing deviations from the task-prompt file

The task-prompt file docs/guides/implementation/task-prompts/p0.2-alpha-system-core.md has three items that diverge from Wave A’s conventions and the packet’s design decisions. All three are Sigma-pre-approved per the dispatch prompt; the contract and packet bake them in.

Deviation 1 — test file location

  • Spec (line 305, 360): tests/db/init.test.ts.
  • Wave A convention: src/__tests__/<name>.test.ts per jest.config.ts line 15 roots: ['<rootDir>/src']. Jest will not discover files outside src/.
  • Decision: src/__tests__/db-init.test.ts. Confirmed by observing that all existing tests (config.test.ts, modes.test.ts, server.test.ts, smoke.test.ts) live in src/__tests__/.

Deviation 2 — env var name

  • Spec (line 401, 439): config.DATABASE_PATH.
  • Actual config schema: config.COLIBRI_DB_PATH (src/config.ts line 48).
  • Decision: use COLIBRI_DB_PATH. The spec’s DATABASE_PATH is a stale reference from an earlier draft.

Deviation 3 — migration tracking mechanism

  • Spec (line 311, 416-428): “ordered migrations” with no tracking mechanism; spec example simply re-executes every .sql file on every boot. This is non-idempotent if any migration contains a CREATE TABLE without IF NOT EXISTS and incorrect otherwise because user_version would never advance.
  • Decision: PRAGMA user_version. Rationale:
    • Fresh-DB zero-table assertion (SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' → 0) would fail if a _migrations tracking table existed. user_version is stored in the SQLite database header, invisible to sqlite_master.
    • Each migration file’s numeric prefix (001_init.sql → 1, 002_beta.sql → 2, …) is the user_version to bump to inside the migration transaction. initDb reads current user_version, skips already-applied migrations, runs pending in order, bumps the pragma inside the same transaction for atomicity.
    • The spec’s IF NOT EXISTS approach would silently mask schema drift. user_version gives a monotone integer that either advances or fails hard.

Deviation 4 — empty-file guard

  • Spec (lines 416-428): db.exec(sql) on every file.
  • Observed corner case: better-sqlite3.exec('') on an empty string throws "You have attempted to create more than one statement. Because better-sqlite3 does not yet support transactions, you must add a semicolon...". Even a comment-only file with only whitespace can hit the “no statements” guard.
  • Decision: strip comments + whitespace; skip db.exec if the remainder is empty; still bump user_version. The 001_init.sql placeholder is comment-only, so this path is exercised on the very first boot.

§4. Acceptance criteria mapping

Spec acceptance criteria (from docs/guides/implementation/task-breakdown.md §P0.2.2 and the dispatch prompt) map to audit facts as follows:

Criterion Source Audit observation
Uses better-sqlite3 sync API spec line 117 better-sqlite3@^11.5.0 in package.json:28, @types/better-sqlite3@^7.6.12 devDep line 33. Native build verified via npm ci in worktree (prebuild-install deprecation warnings only).
schema.sql ships empty header only spec line 118, 303-304 Target file does not exist; packet gives exact contents (comment-only, ≤30 lines).
initDb(path) creates DB if not exists spec line 119 better-sqlite3 new Database(path) creates the file. Parent dir creation via fs.mkdirSync(dir, { recursive: true }) per packet (spec doesn’t specify; observed gap — first-boot in fresh checkout needs this).
Idempotent spec line 120 PRAGMA user_version gates migration re-application. Verified in test suite.
WAL mode spec line 121 db.pragma('journal_mode = WAL') returns 'wal'. Verified in test.
Foreign keys ON spec line 122 db.pragma('foreign_keys = ON') → foreign_keys returns 1. Verified in test.
PRAGMA integrity_check at startup spec line 123 db.pragma('integrity_check', { simple: true }) returns 'ok' on a fresh DB; garbage-bytes test covers the fail-fast path.
Fresh DB has zero user tables spec line 124 001_init.sql is comment-only; SELECT COUNT(*) FROM sqlite_master ... returns 0.
No “78 tables” target spec line 318 Explicitly not introduced. Donor extraction (docs/reference/extractions/alpha-system-core-extraction.md line 99) cites 78 tables — none copied.
Migration runner applies new files dispatch prompt line 150 999_test.sql fixture test exercises the runtime discovery path.
Creates parent dir if missing dispatch prompt line 158 fs.mkdirSync(dir, { recursive: true }) before new Database(path).
getDb() before initDb() throws dispatch prompt line 144 Explicit check; error message starts with "Database not initialized — call initDb() first".
getDb() after initDb() returns same instance dispatch prompt line 146 instance === initDb(p); getDb() === instance in test.
closeDb() closes and resets singleton dispatch prompt line 147 instance.close(); instance = null;.

All 12 acceptance criteria have clear test paths and no spec-level ambiguity remains after the four deviations above are resolved in the contract.

§5. Donor genealogy (reference only)

docs/reference/extractions/alpha-system-core-extraction.md (extracted R45) documents the donor AMS’s 78-table schema split across 5 .sql files (schema.sql, schema-memory.sql, schema-thought.sql, schema-merkle.sql, vector-schema.sql). None of this lands in P0.2.2. Per the earning rule, α owns the file; each concept’s later sub-task earns its tables:

  • P0.3 (β Task Pipeline) → 002_beta.sql: tasks, task_dependencies / task_transitions
  • P0.6 (ε Skill Registry) → 003_epsilon.sql: skills
  • P0.7 (ζ Decision Trail) → 004_zeta.sql: thought_records, actions, audit_events
  • P0.8 (η Proof Store) → 005_eta.sql: merkle_nodes, merkle_roots
  • P0.9 (ν Integrations) → 006_nu.sql: sync_log

Each future task supplies its own NNN_<concept>.sql; P0.2.2 supplies only 001_init.sql (empty).

The donor’s PRAGMA usage (journal_mode = WAL, foreign_keys = ON, integrity_check) is the pattern Colibri re-implements. The donor’s monkey-patch of process.stdout.write is not reproduced (see §2c).

§6. Spec gap discovered

docs/architecture/data-model.md is referenced by six source files (verified via grep -l "data-model.md" docs/):

  • docs/packets/r75-plan-red-packet.md
  • docs/guides/implementation/task-prompts/p0.2-alpha-system-core.md (line 324)
  • docs/guides/implementation/task-prompts/p0.1-infrastructure.md
  • docs/guides/implementation/task-breakdown.md (line 114)
  • docs/architecture/decisions/ADR-001-sqlite-migration.md
  • docs/PLAN-RED.md

The file does not exist (ls docs/architecture/ → only decisions/ and README.md). The “earning rule” it is supposed to codify is stated informally in docs/2-plugin/database.md §”‘Earned’ Tables” (lines 194-204), but that is not a normative spec.

Consequence for P0.2.2: none — the dispatch prompt and this audit are sufficient. The rule is clear enough to implement.

Recommendation: queue a follow-up round to author docs/architecture/data-model.md with §2 as the normative earning-rule statement. Noted in the writeback blockers.

§7. Baseline verification 

$ git rev-parse HEAD
c8143bfc0ecd6d9657b5ccd464ebdc141e2c171a

$ git log -1 --format=%s
chore(r75): resync .claude/skills/colibri-* mirror (#121)

$ node --version
v20.x (from CI matrix — P0.1.3 established Node 20 floor)

$ cat package.json | grep better-sqlite3
    "better-sqlite3": "^11.5.0",
    "@types/better-sqlite3": "^7.6.12",

All prerequisites from the dependency chain (P0.1.1 scaffolding, P0.1.2 Jest ESM, P0.1.4 config) are present at HEAD. P0.4.1 modes and P0.2.1 server are available but not consumed by this task.

§8. Exit criteria for Step 1

  • All target files catalogued as absent in §1.
  • All integration surfaces documented in §2.
  • Spec vs packet deviations resolved in §3 (four, all Sigma-pre-approved).
  • Acceptance criteria mapped to test paths in §4.
  • Donor genealogy acknowledged as reference-only in §5.
  • Spec gap (data-model.md) flagged in §6.
  • Baseline commit + toolchain verified in §7.

Ready to proceed to Step 2 (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.