feat(db): split reader vs mutator open paths so multiple MCP servers can share one deadzone.db

[laradji]

Parent: none
Follow-up from: v0.2.0 in-use testing on 2026-04-17
Depends on: none

Decision (locked 2026-04-17)

Introduce db.OpenReader(path, meta) (*DB, error) as a dedicated read-path entry that does not write schema DDL and (ideally) does not hold a writer-intent lock. deadzone server switches to OpenReader. Mutator commands (scrape, consolidate, dbrelease) keep calling db.Open, which stays fully read-write and continues to own all schema creation + meta validation + migration. Exact read-only mechanism (DSN ?mode=ro, PRAGMA query_only=1, or a no-DDL code path) is an implementer sub-decision — the contract is that N concurrent deadzone server processes can open the same DB file without racing each other and without blocking a concurrent deadzone scrape writer on a sibling DB file.

Why

Today db.Open (internal/db/db.go:119-143) unconditionally executes CREATE TABLE IF NOT EXISTS meta at open time, even when the caller is deadzone server which otherwise only ever issues SELECT. Combined with raw.SetMaxOpenConns(1) holding a persistent connection, this means every server process holds a writer-capable SQLite connection for its entire lifetime. Two MCP clients running on the same machine (e.g. two Claude Code agents in two terminals, both wired to the brew-installed deadzone via .mcp.json) each spawn a server, each calls db.Open, and the second one's boot-time CREATE TABLE can SQLITE_BUSY on the first one's transient write-intent lock. This is a real workflow limitation as the user moves to multi-agent scenarios.

Acceptance criteria

• New function db.OpenReader(path string, meta Meta) (*DB, error) in internal/db/db.go:
  • Validates meta using the same validateMeta call
  • Opens the file such that no DDL and no INSERT/UPDATE/DELETE can be issued against the returned *DB. Any attempt must fail fast with a clear error, not silently succeed
  • Reads stored meta via readMeta and surfaces ErrSchemaMismatch / ErrEmbedderMismatch exactly like db.Open does today
  • Returns os.ErrNotExist (or a wrapping error) if the file does not exist, rather than creating one — readers must not spawn empty DBs
• cmd/deadzone/server.go:303 — replace db.Open(*dbPath, ...) with db.OpenReader(*dbPath, ...)
• No changes to cmd/deadzone/scrape.go (db.OpenArtifact path), cmd/deadzone/consolidate.go:60 (db.Open), or cmd/deadzone/dbrelease.go — mutator paths stay on db.Open
• Unit test: internal/db/db_test.go gains TestOpenReader_* coverage for:
  • TestOpenReader_existingDB — opens a pre-seeded DB, issues a SELECT, succeeds
  • TestOpenReader_rejectsWrite — issues an INSERT or CREATE TABLE on the returned *DB and asserts it fails
  • TestOpenReader_missingFile — opens a non-existent path, asserts os.ErrNotExist (wrapped) — does NOT create the file
  • TestOpenReader_schemaMismatch / TestOpenReader_embedderMismatch — parity with existing Open mismatch tests
• Integration test: TestOpenReader_concurrent spawns 3 goroutines that all call OpenReader against the same file in t.TempDir(), each issues a SELECT count(*) FROM docs, all succeed with the same count, no SQLITE_BUSY
• docs/research/ingestion-architecture.md — add a one-paragraph note under decision 9 (schema versioning) pointing at the reader/mutator split, documenting the contract that mutator paths own all DDL
• No README.md change (internal refactor, user-invisible)
• No CLAUDE.md change except if the "Architectural context" bullet on db.Open grows sub-bullets about OpenReader — keep brief

Concrete file pointers

Files to modify:

• internal/db/db.go — add OpenReader, possibly refactor Open to share validation helper with OpenReader
• cmd/deadzone/server.go:303 — swap the call
• internal/db/db_test.go — add reader-path tests
• docs/research/ingestion-architecture.md — brief note

Files to read as reference — do NOT refactor:

• internal/db/db.go:119-143 — current Open shape
• internal/db/artifact_meta.go — ReadArtifactMeta already opens bare with sql.Open; pattern reference if OpenReader reuses the same low-level open
• internal/db/consolidate.go — ATTACH DATABASE semantics; verify that OpenReader is NOT used here (consolidate is a mutator)
• .mcp.json (local, gitignored) — the deadzone + deadzone-dev entries both point to the same binary/DB patterns, so the fix must work for both "brew-installed binary, default DB path" and "worktree binary, worktree DB"

Implementer sub-decisions (open)

Pick one and document in the PR body:

1. Bare sql.Open + PRAGMA query_only = 1 after connect
   • Pros: minimal code change, tursogo DSN stays unchanged
   • Cons: query_only is a runtime pragma; a compromised caller could toggle it back off. Also does not prevent the CREATE TABLE IF NOT EXISTS meta race if the code path still runs it
   • Fix for race: skip the CREATE TABLE in OpenReader, only do readMeta (which SELECTs from meta) — surface a clear error if the meta table is absent ("DB was never initialized by a mutator — run deadzone consolidate or deadzone dbrelease first")
2. ?mode=ro DSN query string
   • Pros: OS-level enforcement; connection cannot write regardless of caller code
   • Cons: tursogo's DSN is a bare path per the comment at db.go:117 — support for ?mode=ro is unknown and needs a ≤5min spike before committing to this
3. Hybrid: open with bare DSN + skip CREATE TABLE + set PRAGMA query_only = 1
   • Pros: strongest of the two under tursogo constraints
   • Cons: two layers of guard (code path + pragma) to reason about

Recommendation: start with (1), spike (2) in parallel (≤15min), pick (3) only if spike confirms DSN ?mode=ro works.

Test commands (literal, for agent self-check)

• mise exec -- go test ./internal/db/... -run "TestOpenReader" -v — new tests pass
• just test -short — full suite green
• Manual smoke test:

  # Terminal 1
  deadzone server &
  # Terminal 2 (same machine, same DB)
  deadzone server &
  # Both should stay running; both should answer `search_libraries` queries via stdio
  # Terminal 3: kill both
  pkill -f 'deadzone server'

Out of scope (fenced)

• No read-write pool changes — raw.SetMaxOpenConns(1) on the mutator path stays; that's a tursogo-beta defensive measure, not the bug
• No WAL mode toggle — tursogo activates WAL by default already; don't add an explicit PRAGMA journal_mode = WAL
• No concurrency story for mutators — two concurrent consolidates are still expected to race (that's the operator's problem); this issue strictly solves the N-reader-1-DB case
• No connection pooling for MCP server — the server is one-query-at-a-time by design; keep SetMaxOpenConns(1)
• No migration of existing DBs — reader-path change is a pure refactor; existing DBs stay fully compatible
• No changes to db.OpenArtifact — per-lib artifact DBs are written by scrape then read by consolidate, both are mutator paths
• No changes to internal/db/artifact_meta.go — ReadArtifactMeta already avoids the meta-creation trap; reuse its pattern, don't refactor it

Related

• v0.2.0 in-use testing surfaced the gap during a session where the user validated that brew install-ed deadzone + the dev worktree deadzone needed to coexist in .mcp.json
• Tursogo beta posture documented at internal/db/db.go:129-132 — when tursogo reaches GA, the SetMaxOpenConns(1) defense can be revisited and this issue's constraints may loosen