P0.2.2 — Step 3 Packet

Execution plan. Gates Step 4 (implement). Sigma-pre-approved via the dispatch prompt; this packet records the pre-approval so the chain is auditable.

---

§1. Work breakdown

The task adds a new src/db/ subsystem with one TS module, one SQL header asset, one migration slot, and a Jest test file. The work is structured as one coding round + one tightening round; both collapse into a single PR unless the test cycle surfaces a surprise.

1.1 Round A — implement (§3)

File-level targets in build order:

1. src/db/schema.sql — lowest risk, no imports. Pure header comment.
2. src/db/migrations/001_init.sql — pure header comment.
3. src/db/index.ts — the core module. Largest risk.
4. src/__tests__/db-init.test.ts — exercise all 12 acceptance criteria.

Discipline: each subfile committed as a single feat(p0-2-2): <change> once all four compile-and-pass together. No incremental feat commits for a partial module.

1.2 Round B — tighten (§4)

After npm test green: review coverage report; add targeted tests if branch coverage is below 90% on src/db/index.ts. Possible needs:

• Non-integer migration prefix skip path.
• Migration prefix collision throw.
• Close-before-init no-op.

---

§2. Design decisions locked in

All four deviations from the task-prompt file, catalogued in the audit §3, are locked in this packet. Summary:

#	Issue	Decision	Rationale
1	Test location	src/__tests__/db-init.test.ts (not tests/db/init.test.ts)	jest.config.ts line 15 roots: ['<rootDir>/src'].
2	Env var name	COLIBRI_DB_PATH (not DATABASE_PATH)	Matches src/config.ts line 48.
3	Migration tracking	PRAGMA user_version (not _migrations table)	Zero-user-tables assertion; header-stored integer; atomic.
4	Empty-migration guard	Strip -- comments + whitespace; skip db.exec if empty	better-sqlite3.exec('') raises “no statements”; 001_init.sql is comment-only.

Additional locked-in decisions:

#	Issue	Decision	Rationale
5	getDb() lazy init	No — throws if instance is null	Consumer-owned lifecycle; easier to test; fail-fast.
6	Parent dir creation	fs.mkdirSync(dir, { recursive: true }) before new Database	Fresh-checkout first-boot; spec omits it but empty data/ dir exists.
7	initDb re-call with existing instance	Close existing, open new	Test re-seat path; hot-reload friendly.
8	Migration prefix collision	Throw at discovery	Protects against copy-paste mistakes (two 002_*.sql).
9	Migration with non-integer prefix	Silently skip	Allows .gitkeep, README.md, etc., in the migrations directory.
10	On boot failure after handle opens	Call db.close() then throw	Don’t leak the handle across a boot-abort path.

---

§3. File plans

3.1 src/db/schema.sql

-- Colibri α System Core — database bootstrap (shipped asset, NOT executed).
--
-- α owns the file `data/colibri.db`; it does not own the schema. Tables are
-- earned by the concept whose Phase 0 sub-task introduces them, per the rule
-- in docs/2-plugin/database.md §"'Earned' Tables":
--
--     P0.3  β Task Pipeline     → migrations/002_beta.sql     (tasks, task_dependencies)
--     P0.6  ε Skill Registry    → migrations/003_epsilon.sql  (skills)
--     P0.7  ζ Decision Trail    → migrations/004_zeta.sql     (thought_records, audit_events)
--     P0.8  η Proof Store       → migrations/005_eta.sql      (merkle_nodes, merkle_roots)
--     P0.9  ν Integrations      → migrations/006_nu.sql       (sync_log)
--
-- Pragmas (journal_mode = WAL; foreign_keys = ON) are set from src/db/index.ts
-- *before* any migration runs, so they apply to the very first table earned.
--
-- This file is declared in package.json#files so npm-published tarballs
-- carry it as a reference. It contains NO executable SQL.

3.2 src/db/migrations/001_init.sql

-- 001_init — α System Core empty migration slot.
--
-- Reserves migration number 1 so later concept migrations start at 002+.
-- α ships zero user tables; per the earning rule, every table is introduced
-- by its owning concept's migration file.
--
-- The migration runner in src/db/index.ts strips comments and whitespace,
-- detects that the body is empty, skips db.exec, and bumps PRAGMA user_version
-- to 1 inside the same transaction. This keeps the runner uniform: every
-- migration file advances user_version, even when its body is intentionally
-- empty.

3.3 src/db/index.ts

Structure:

/**
 * JSDoc header — canonical refs, donor-bug mitigations, consumer list.
 */

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

import Database from 'better-sqlite3';

// Internal types
interface Migration {
  readonly version: number;
  readonly file: string;
  readonly path: string;
}

// Module state (mutable singleton)
let instance: Database.Database | null = null;

// Constants
const MIGRATIONS_DIRNAME = 'migrations';
const MIGRATION_PREFIX_RE = /^(\d+)_/;

// Private helpers
function migrationsRoot(): string;
function discoverMigrations(): Migration[];  // throws on prefix collision
function stripSqlComments(sql: string): string;  // removes -- line comments
function ensureParentDir(path: string): void;

// Public exports
export function initDb(path: string): Database.Database;
export function getDb(): Database.Database;
export function closeDb(): void;

// Type re-export (convenience)
export type { Database } from 'better-sqlite3';

Key algorithms:

initDb(path):

export function initDb(path: string): Database.Database {
  // 1. Replace existing singleton (if any) to support test re-seat.
  if (instance !== null) {
    instance.close();
    instance = null;
  }

  // 2. Parent dir.
  ensureParentDir(path);

  // 3. Open DB (creates if missing).
  const db = new Database(path);

  // 4. Pragmas.
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');

  // 5. Integrity check — abort boot on failure.
  const integrity = db.pragma('integrity_check', { simple: true });
  if (integrity !== 'ok') {
    db.close();
    throw new Error(`Database integrity check failed: ${String(integrity)}`);
  }

  // 6. Migrations.
  const migrations = discoverMigrations();
  const currentVersion = db.pragma('user_version', { simple: true }) as number;

  for (const m of migrations) {
    if (m.version <= currentVersion) continue;

    const body = stripSqlComments(readFileSync(m.path, 'utf-8')).trim();
    try {
      const tx = db.transaction((version: number, sql: string) => {
        if (sql.length > 0) {
          db.exec(sql);
        }
        db.pragma(`user_version = ${version}`);
      });
      tx(m.version, body);
    } catch (err) {
      db.close();
      throw new Error(
        `Migration ${m.file} failed: ${(err as Error).message}`,
      );
    }
  }

  // 7. Set singleton + return.
  instance = db;
  return db;
}

discoverMigrations():

function discoverMigrations(): Migration[] {
  const root = migrationsRoot();
  if (!existsSync(root)) return [];

  const seen = new Map<number, string>();
  const results: Migration[] = [];

  for (const file of readdirSync(root)) {
    if (!file.endsWith('.sql')) continue;
    const match = MIGRATION_PREFIX_RE.exec(file);
    if (match === null || match[1] === undefined) continue;
    const version = parseInt(match[1], 10);
    if (!Number.isSafeInteger(version) || version <= 0) continue;

    const prior = seen.get(version);
    if (prior !== undefined) {
      throw new Error(
        `Migration prefix collision at ${version}: ${prior} vs ${file}`,
      );
    }
    seen.set(version, file);
    results.push({ version, file, path: join(root, file) });
  }

  results.sort((a, b) => a.version - b.version);
  return results;
}

migrationsRoot(): uses fileURLToPath(import.meta.url) to locate the compiled module directory; joins 'migrations'. This works for both tsx (TS source path) and tsc (dist path) because migrations are copied alongside the module. Since tsc does NOT copy .sql files automatically, the packet flags a follow-up note — see §7 for handling.

stripSqlComments(sql): removes -- line comments (up to newline or EOF). Does NOT handle /* ... */ block comments (none used in Phase 0 migrations). Pragmatic regex: /--[^\n]*/g → replace with empty.

ensureParentDir(path):

function ensureParentDir(path: string): void {
  const dir = dirname(path);
  if (dir === '.' || dir === '') return;
  mkdirSync(dir, { recursive: true });
}

getDb() and closeDb(): straightforward per contract §3–§4.

3.4 src/__tests__/db-init.test.ts

Test organization (Jest describe nesting mirrors contract §2–§9):

describe('src/db/index.ts — P0.2.2 SQLite init')
  describe('initDb(path)')
    • creates DB file if not exists
    • creates parent directory if missing
    • returns a Database.Database instance
    • fresh DB has zero user tables at the α floor
    • PRAGMA journal_mode returns wal
    • PRAGMA foreign_keys returns 1
    • PRAGMA user_version is 1 after 001_init is applied
    • is idempotent (second call same-path does not double-apply migrations)
    • handles relative paths
    • handles paths with no directory component
    • runs newly-added migration when re-opened
    • applies a fake 999_test.sql if injected into migrations dir
    • throws on integrity_check failure (garbage bytes)
    • closes handle before throwing on integrity failure
    • throws on migration SQL error with file name in message
  describe('getDb()')
    • throws with exact message when called before initDb
    • returns the same instance as initDb
    • returns the same instance across multiple calls
  describe('closeDb()')
    • is a no-op before initDb
    • closes the handle and resets the singleton
    • makes getDb throw after it is called
  describe('module purity')
    • importing the module has no side-effects

Shared fixture:

import * as fs from 'node:fs';
import * as os from 'node:os';
import * as path from 'node:path';
import { randomUUID } from 'node:crypto';

let tempPaths: string[] = [];

function makeTempDbPath(): string {
  const dir = path.join(os.tmpdir(), `colibri-test-${randomUUID()}`);
  const p = path.join(dir, 'test.db');
  tempPaths.push(dir);
  return p;
}

afterEach(() => {
  closeDb();
  for (const p of tempPaths) {
    try {
      fs.rmSync(p, { recursive: true, force: true });
    } catch {
      // Swallow ENOENT / EBUSY — WAL files may linger.
    }
  }
  tempPaths = [];
});

Garbage-bytes integrity test:

it('throws on integrity_check failure (garbage bytes)', () => {
  const p = makeTempDbPath();
  // Create a valid DB first, then stomp on its header.
  closeDb();
  const db = initDb(p);
  db.close();
  // SQLite header is first 16 bytes. Overwrite with zeros — triggers
  // "file is not a database" or integrity failure on re-open.
  const content = fs.readFileSync(p);
  content.fill(0, 0, 100);
  fs.writeFileSync(p, content);
  expect(() => initDb(p)).toThrow(/Database integrity check failed|file is not a database/);
});

Note: stomping the SQLite header causes the new Database() call itself to throw "file is not a database" on open, which is caught by the outer expect().toThrow and is a valid failure mode for a corrupt DB file. Either path is acceptable for the “fail boot on corrupt DB” acceptance criterion.

999-migration fixture:

it('applies a fake 999_test.sql if injected into migrations dir', () => {
  const p = makeTempDbPath();
  // Find the migrations directory adjacent to the compiled module.
  const migrationsDir = path.resolve(
    path.dirname(fileURLToPath(import.meta.url)),
    '..', 'db', 'migrations',
  );
  const fixture = path.join(migrationsDir, '999_test.sql');
  fs.writeFileSync(
    fixture,
    'CREATE TABLE IF NOT EXISTS _p0_2_2_fixture (id INTEGER PRIMARY KEY);',
  );
  try {
    const db = initDb(p);
    expect(db.pragma('user_version', { simple: true })).toBe(999);
    const has = db.prepare(
      "SELECT name FROM sqlite_master WHERE type='table' AND name='_p0_2_2_fixture'",
    ).get();
    expect(has).toBeTruthy();
  } finally {
    try { fs.unlinkSync(fixture); } catch {}
  }
});

The fixture test is non-hermetic (touches the real src/db/migrations/ directory), but the finally cleanup is aggressive. The risk is minimal because Jest runs serially inside a single worker for this suite (testEnvironment: 'node', default maxWorkers), and no other test examines the migrations directory.

Module-purity test:

it('importing the module has no side-effects', () => {
  // Import performed at file top. Observable side-effects would include:
  //   - A DB file at data/colibri.db (we never call initDb with that path here).
  //   - Any mkdir under data/.
  // The strongest assertion: after import, getDb() throws (singleton is null).
  expect(() => getDb()).toThrow(/Database not initialized/);
});

---

§4. Coverage strategy

Target: 100% stmt/func/line + ≥90% branch on src/db/index.ts.

Uncoverable lines (expected exceptions):

• None anticipated. All branches are testable via the fixture patterns above.

If branch coverage falls short, candidate additional tests:

• mkdirSync already-exists path (path with existing parent dir).
• closeDb with instance === null (already tested).
• discoverMigrations with missing migrations/ dir (test via a temporary module copy — deferred only if coverage requires).

---

§5. Build and test discipline

Run order for each local iteration:

npm run lint   # ESLint on src/; zero errors required
npm test       # Jest ESM; 100% green required
npm run build  # tsc; dist/ produced; no errors required

Before PR: all three green. If better-sqlite3 rebuild fails on another developer’s machine (Windows ABI mismatch), the writeback notes it.

---

§6. Commit plan

1. feat(p0-2-2): src/db/ scaffold — schema header + 001_init placeholder — files 1-2 from §1.1.
2. feat(p0-2-2): src/db/index.ts — initDb/getDb/closeDb + migration runner — file 3.
3. feat(p0-2-2): src/__tests__/db-init.test.ts — acceptance suite — file 4.
4. (optional) test(p0-2-2): tighten coverage on <branch> — only if Round B adds tests.
5. verify(p0-2-2-sqlite-init): test evidence — Step 5 doc.

Audit + contract + packet commits are already in place.

---

§7. Known concerns / follow-ups

7.1 tsc does not copy .sql files to dist/

The migration runner uses fileURLToPath(import.meta.url) to locate migrations/ relative to the module. Under tsx (dev + test), the module is at src/db/index.ts and the migrations are at src/db/migrations/*.sql — path works.

Under tsc build output, the module is at dist/db/index.js but tsc by default does NOT copy .sql files. This is a future concern for the production bundle (P0.2.3 or a later packaging task) — a step that copies src/db/migrations/ to dist/db/migrations/ is required before npm start works.

Scope for P0.2.2: we document this in the src/db/index.ts JSDoc header and in the writeback. We do NOT fix it here — it is out of scope (the spec says “return Database instance”, nothing about production packaging), and P0.2.3 startup wiring is the natural home for it.

7.2 docs/architecture/data-model.md does not exist

Documented in audit §6. Flagged in writeback. Six files reference it; queue a round to author it.

7.3 No migration timing metric

Spec does not require boot-time metric emission; initDb does not record duration. P0.2.3 two-phase startup will log total boot time including DB init; that is the appropriate seam.

7.4 Empty-migration skip is implementation-defined

The empty-migration skip (§3.3 stripSqlComments path) is a convenience for 001_init.sql. Future concepts MUST include at least one statement or the migration is a no-op from SQLite’s perspective (though user_version still advances). This is acceptable; the migration-runner tests assert both paths.

---

§8. Packet exit criteria

• Work breakdown in §1.
• All 10 design decisions locked in §2.
• File plans with full code skeletons in §3.
• Coverage target stated in §4.
• Build / test gate order in §5.
• Commit plan in §6.
• Known concerns flagged in §7.

Ready to proceed to Step 4 (implement).