P0.2.2 — Step 2 Contract

Behavioral contract for src/db/index.ts. Everything in this file is testable; the verification step (5) re-reads this contract and matches tests to clauses.

§1. Module identity

  • File: src/db/index.ts
  • Owner concept: α System Core (owns the file, not the schema)
  • Consumer (future): P0.2.3 (two-phase startup) calls initDb(config.COLIBRI_DB_PATH) during Phase 2
  • Exports (public surface):
    • initDb(path: string): Database.Database
    • getDb(): Database.Database
    • closeDb(): void
    • type Database = import('better-sqlite3').Database.Database (re-export for convenience)
  • Internal (not exported):
    • let instance: Database.Database | null
    • discoverMigrations(): Migration[]
    • applyMigration(db, migration) inside initDb

The module is pure at import time: importing it does nothing; no side-effects, no DB open, no file read. This matches the P0.4.1 src/modes.ts pattern and is asserted by test.

§2. initDb(path) — contract

2.1 Inputs

  • path: string — required. Absolute or relative (relative paths resolve from process.cwd()). No default; callers MUST pass explicitly. The P0.2.3 production caller will pass config.COLIBRI_DB_PATH; tests pass a unique temp path per test.

2.2 Output

  • Returns a live Database.Database handle with the pragmas applied and all pending migrations run.
  • The handle is also cached as the module’s singleton, retrievable via getDb().

2.3 Sequence (observable side-effects, in order)

  1. Parent directory creation. Compute dirname(path). If the directory does not exist, create it recursively (fs.mkdirSync(dir, { recursive: true })). If dirname(path) is '.' (path has no directory component), skip. The module does not race any other writer for the directory; if another process creates it concurrently the recursive: true flag makes mkdirSync idempotent.
  2. Open the database. new Database(path) — creates the file if absent, opens if present. Better-sqlite3’s constructor is sync.
  3. Pragmas (set before migrations).
    • PRAGMA journal_mode = WAL — MUST succeed and return 'wal'. If the disk doesn’t support WAL (rare; some network filesystems), better-sqlite3 falls back to 'memory' or 'truncate'; contract asserts WAL. Tests run on local disk.
    • PRAGMA foreign_keys = ON — MUST succeed.
  4. Integrity check. db.pragma('integrity_check', { simple: true }). If the result is not exactly the string 'ok':
    • Close the database handle (db.close()).
    • Throw Error with message "Database integrity check failed: <result>" where <result> is the actual return.
    • The singleton is NOT set (caller sees a fresh throw; getDb() continues to throw the “not initialized” error).
  5. Migration discovery. Read src/db/migrations/ relative to the compiled module location (fileURLToPath(import.meta.url) → dirname → join(dirname, 'migrations')). Filter *.sql. Parse the NNN_ prefix as parseInt(..., 10); skip files whose prefix is not a positive integer (e.g. .gitkeep, README files). Sort ascending by numeric prefix.
  6. Migration application.
    • Read PRAGMA user_version → current.
    • For each migration m in discovered order where m.version > current:
      • Read m.path as UTF-8 string.
      • Strip SQL comments (-- line comments) and trim whitespace. If the remainder contains only whitespace or is empty, skip the db.exec call (empty migration) but still bump user_version.
      • Run the migration body and the user_version bump inside a single SQLite transaction via db.transaction(() => { ... })(). The pragma bump is db.pragma(\user_version = ${m.version}`) — note: SQLite's user_version pragma does not accept parameters; string-interpolate after validating m.version` is a safe integer.
      • On migration error, the transaction rolls back automatically; re-throw wrapped: Error("Migration <file> failed: <original-message>"). Close the handle before re-throw. Singleton NOT set.
  7. Set singleton. Only after all migrations succeed: instance = db.
  8. Return the handle.

2.4 Idempotence guarantee

A second call to initDb(samePath):

  • If instance is set (singleton populated by a prior call on any path): close the existing handle and open the new one. This lets tests and hot-reload scenarios re-seat the DB.
  • If instance is null (first call, or post-closeDb): proceed as §2.3.

The on-disk DB is unchanged across re-calls with the same path provided no new migration files have appeared between calls. The test for idempotence:

  1. Call initDb(p); record user_version.
  2. Close via the returned handle (not closeDb, to test the singleton-replace path).
  3. Call initDb(p) again. Assert user_version is unchanged (because the set of migration files is unchanged).

2.5 Failure modes (tested)

Failure Cause Outcome
Parent dir create fails Permission denied on dirname(path) fs.mkdirSync throws EACCES; uncaught; handle never opens; singleton untouched.
new Database(path) fails File locked by another writer, disk full, permission denied SqliteError propagates; singleton untouched.
Pragma set fails Corrupt file, broken SQLite SqliteError propagates after the handle is opened; handle leaks (acceptable: OS closes on process exit — matches the Wave A discipline of not over-engineering cleanup on unrecoverable boot failure).
Integrity check fails Corrupt DB file db.close() called, then throw "Database integrity check failed: <result>". Singleton untouched.
Migration file fails SQL error, constraint violation Transaction rolls back; db.close(); throw "Migration <file> failed: <msg>". Singleton untouched.
Migration file prefix non-integer abc_foo.sql Silently skipped (with any other discovery filter). Acceptable: prefix-less files are fixtures or drafts.

§3. getDb() — contract

3.1 Inputs

None.

3.2 Output

  • Returns the cached Database.Database instance from the most recent successful initDb.

3.3 Sequence

  • If instance === null: throw Error("Database not initialized — call initDb() first"). Exact message (leading capital D, em-dash — not hyphen).
  • Otherwise: return instance.

3.4 Invariants

  • getDb() does NOT lazy-init from config.COLIBRI_DB_PATH. This is a deliberate deviation from the spec file’s example (lines 437-442), approved by Sigma. Rationale:
    • Phase 2 startup (P0.2.3) will explicitly call initDb during its heavy-init phase — there is exactly one lifecycle owner.
    • Lazy init would couple getDb() to config, which makes the module harder to test (subprocess tests for env isolation would become mandatory).
    • Throwing fast on misuse is consistent with how audit-exit stage 5 of src/server.ts handles sink failures (log-and-continue there; throw-loudly-and-crash here because a missing DB is unrecoverable).
  • getDb() has O(1) runtime complexity and no side-effects.
  • Calling getDb() multiple times MUST return the same reference (getDb() === getDb() for every call on the same singleton).

§4. closeDb() — contract

4.1 Inputs

None.

4.2 Output

Returns void.

4.3 Sequence

  1. If instance !== null: call instance.close(). Better-sqlite3’s close() is sync and idempotent at the handle level (double-close raises), but this function only calls it once per cached singleton.
  2. Set instance = null.
  3. Return.

4.4 Invariants

  • Calling closeDb() before initDb() is a no-op (no throw).
  • After closeDb(): getDb() throws the “not initialized” error; initDb(path) succeeds fresh.
  • closeDb() does NOT delete the on-disk DB file. The file persists across restarts; that is the whole point of SQLite.

§5. Migration runner — contract

5.1 Discovery

  • Root: join(dirname(fileURLToPath(import.meta.url)), 'migrations').
  • Glob: *.sql (case-sensitive).
  • Prefix pattern: /^(\d+)_/. Files not matching are silently skipped.
  • Ordering: ascending by parsed integer prefix. Ties (two files with the same prefix) are prohibited — the runner throws "Migration prefix collision at <version>: <fileA> vs <fileB>" at discovery time.

5.2 Version tracking

  • PRAGMA user_version is a 32-bit signed integer stored in the SQLite database header (positions 60-63). It is invisible to sqlite_master and therefore does not violate the “zero user tables at α floor” acceptance criterion.
  • The initial value on a brand-new database is 0. 001_init.sql bumps it to 1, 002_beta.sql to 2, etc. The version number is the target user_version, not a sequence index.
  • Gaps in the sequence are legal (if 001 and 003 exist but 002 does not, 003 runs after 001). The R75 convention reserves one migration per concept in order; gaps indicate a concept that opted out of its own migration.

5.3 Transactional semantics

Each migration + version bump runs in a single SQLite transaction:

const tx = db.transaction((version: number, sql: string) => {
  if (sql.trim() !== '') {
    db.exec(sql);
  }
  db.pragma(`user_version = ${version}`);
});
tx(m.version, body);

If the migration throws, the transaction aborts; the on-disk user_version is unchanged; initDb re-throws with the migration-name prefix and closes the handle.

§6. schema.sql — contract

src/db/schema.sql is a shipped asset, not executed. Its purpose:

  • Declared in package.json#files (line 15), so it is included in npm pack / npm publish output. Clients reading the published package can inspect the high-level schema description.
  • Contains a human-readable header describing the α earning rule and pointing to docs/2-plugin/database.md for the full story. No CREATE TABLE, no PRAGMA, no executable SQL.
  • Kept short (<30 lines) so it is a reference card, not a doc in its own right.

Any future PR that adds CREATE TABLE to schema.sql is a scope-drift bug. Tables go in migrations/NNN_<concept>.sql only.

§7. migrations/001_init.sql — contract

First migration slot. Comment-only body so α ships with zero user tables. Purpose:

  • Anchors the migration-numbering convention so later concepts (β, ε, …) start at 002+.
  • Exercises the empty-migration path in the runner (§2.3 step 6, “strip comments + whitespace; skip exec”).
  • Visible in the file tree as proof of the earning rule.

§8. Pragma invariants (tested)

After a successful initDb:

Pragma Expected Verification
journal_mode 'wal' db.pragma('journal_mode', { simple: true }) === 'wal'
foreign_keys 1 db.pragma('foreign_keys', { simple: true }) === 1
integrity_check 'ok' run inside initDb; result MUST be 'ok' or boot aborts
user_version >= 1 after 001_init.sql applied db.pragma('user_version', { simple: true }) === N where N is the highest migration prefix discovered

§9. Earning rule (acceptance criterion “zero user tables at α floor”)

Per docs/2-plugin/database.md §”‘Earned’ Tables” and the stale-but-aspirational docs/architecture/data-model.md §2, α owns the file but no tables. The assertion:

SELECT COUNT(*) AS c
FROM sqlite_master
WHERE type='table' AND name NOT LIKE 'sqlite_%'

MUST return 0 on a freshly-initialized DB with only 001_init.sql applied. Every future concept task (P0.3 β, P0.6 ε, P0.7 ζ, P0.8 η, P0.9 ν) introduces its own migration and rebases this assertion forward in its own test.

This assertion is the anchor that prevents schema drift. If a later task copies donor DDL into schema.sql, its test fails; if a later task creates tables outside its owning concept’s migration, its test fails; if the migration runner applies an unexpected migration, this test fails.

§10. Logging

  • No stdout writes (donor bug #3 — would corrupt MCP JSON-RPC wire format).
  • No stderr writes at the module level. Boot-time diagnostics are the caller’s responsibility (P0.2.3 logs boot progress via ctx.logger from src/server.ts).
  • Errors propagate via throw; initDb never catches-and-logs.

§11. Concurrency

  • initDb is sync; no async/await. Matches better-sqlite3’s sync API.
  • The module is not thread-safe in the multi-worker sense, but Node.js is single-threaded for user code; initDb is called exactly once per process in Phase 0 (during P0.2.3 heavy-init). Any “concurrent” call is a bug in the caller.
  • SQLite’s WAL mode allows external readers (other processes) to open the file in read-only mode concurrently. The contract does not guarantee behavior in multi-writer scenarios — Phase 0 is single-writer per docs/2-plugin/database.md §”Single Writer, Multiple Readers”.

§12. Testability requirements

  • Every assertion in §§2-10 MUST have at least one test in src/__tests__/db-init.test.ts.
  • Tests use os.tmpdir() + a crypto.randomUUID()-derived suffix for unique paths; cleanup via fs.rmSync(path, { recursive: true, force: true }) in afterEach, swallowing ENOENT.
  • No test touches data/colibri.db or any path under data/.
  • The module is exercised via the public exports only; no monkey-patching of fs, no jest.mock. The 999-migration test uses a real file injected into the migrations directory then removed in cleanup.
  • Target coverage on src/db/index.ts: 100% stmt/func/line, ≥90% branch. Matches P0.2.1 (98.2 stmt / 92.6 branch / 100 func / 98.2 line) and P0.4.1 (100/100/100/100).

§13. Open questions — none

All ambiguities from §1 of the packet have been resolved. No decisions are deferred to implementation.

Back to top

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

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