Route all SQLite writers through append-only spools (single-writer architecture)

[khaliqgant]

Problem

Multiple independent OS processes write ai-history.db directly: ai-hist sync, the MCP server, and agent-relay brokers. SQLite WAL permits exactly one writer, and the write lock belongs to a process, not a lease. Any writer that dies, hangs, or is suspended mid-transaction wedges every other writer permanently.

This is not hypothetical. A multi-day sync outage was traced to two agent-relay processes in state T (SIGSTOP'd), one frozen mid-write-transaction. A stopped process never runs again on its own, so the lock was held indefinitely. sqlite3 with a 10s busy timeout waited the full 10s and still failed.

No timeout, retry, or backoff survives this. #44, #45, and #46 reduce the blast radius and make it diagnosable; none make it impossible.

Proposal

Only ai-hist sync opens the database read-write. Every other producer appends to its own append-only JSONL spool; sync ingests them with a byte-offset cursor.

This is already the proven model in this codebase — ~/.claude/history.jsonl and ~/.codex/history.jsonl are exactly this, and sync_jsonl_incremental is the consumer. Extending it to agent-relay and the MCP server means reusing a working pattern rather than inventing one.

Why it's decisive:

• Appends need no lock, so a suspended producer blocks nobody
• One writer means zero write contention
• WAL can checkpoint normally (an unchecked WAL reached 156MB)
• Readers keep working unchanged (see feat(ai-hist): read-only reads, lock diagnostics, and write guardrails #46: reads now open read-only)

Notes

Deserves an ADR — it changes the write path for every producer. crates/ai-hist-core/src/outbox.rs already establishes cursor/watermark conventions to build on.

Related: #44, #45, #46

[kjgbot]

Investigated while diagnosing #50. I am deliberately leaving #47 open and out of draft PR #59.

The producer model is broader than the current proposal assumes: Agent Relay invokes the complete sync_and_push aggregator rather than appending individual history events, while MCP tag writes use a sql.js in-memory snapshot followed by whole-file export. A safe single-writer spool therefore needs a cross-repository ADR defining the event/tag-operation schema, atomic append and acknowledgements, per-producer cursors, malformed-record quarantine, service availability, cloud-push ownership, and migration of Relay's current “works without a separate service” behavior.

Implementing only a spool consumer or disabling embedded sync would regress automatic capture and would not address the historical external process stops. PR #59 handles #48-#52's supported resilience/diagnostic work while preserving this architecture issue for an explicit design decision.