# RAG Outbox SQLite — Single-Writer Topology, Checkpoint Discipline & Integrity Watchdog (MC #106047) # RAG Outbox SQLite — Single-Writer Topology, Checkpoint Discipline & Integrity Watchdog **Status:** ACTIVE **Created:** 2026-07-20 **Owner:** FlowForge **Related:** MC #106047 (root cause + fix), MC #106066 (follow-up: fsevents debounce) --- ## Purpose `~/system/state/ingest-queue.sqlite` is the durable outbox queue that feeds documents into LightRAG (BookStack pages, MC task outcomes, evidence files, specs, rules). It corrupted repeatedly (Apr 23, May 9, Jul 15→20-silent, Jul 20 2026) with the same `SQLITE_CORRUPT` / "database disk image is malformed" signature. This runbook documents the root cause, the fix, the writer topology, and the guard now in place so future recurrences are caught in minutes instead of days. --- ## Writer Topology (as of 2026-07-20) All writers use the shared library `~/system/lib/rag-outbox.js` (`openOutbox()`), which owns WAL-mode pragmas and the SQLite connection lifecycle. | Daemon (LaunchAgent) | Script | Trigger | Lifetime | Checkpoint mode | |---|---|---|---|---| | `com.alai.rag-drain-worker` | `tools/rag-drain-worker.js` | KeepAlive | Long-lived, continuous | **TRUNCATE** (explicit) — the single owner of periodic WAL truncation | | `com.alai.rag-bookstack-adapter` | `tools/rag-bookstack-adapter.js` | StartInterval=300s | Fresh process per run | PASSIVE (default) | | `com.alai.rag-mc-adapter` | `tools/rag-mc-adapter.js` | StartInterval=300s | Fresh process per run | PASSIVE (default) | | `com.alai.rag-fsevents-adapter` | `tools/rag-fsevents-adapter.js` | WatchPaths (`evidence/`, `specs/`, `rules/`) | Fresh process per fs-event | PASSIVE (default) | | `com.alai.lightrag-outbox-ingest` | `tools/lightrag-outbox-ingest.js` | StartInterval=21600s (6h) | Fresh process per run | PASSIVE (default) | `com.john.outbox-processor` (`daemons/outbox-processor.js`) is **not** a writer to this DB — it polls unrelated outbox tables in `durable-runner.db`, `mission-control.db`, `drafts.db`. ## Drain Worker Rate Configuration (MC #101501, 2026-07-29) `com.alai.rag-drain-worker` is deployed with: | Setting | Value | Source | |---|---:|---| | `MAX_UPLOADS_PER_MINUTE` | `20` | `~/Library/LaunchAgents/com.alai.rag-drain-worker.plist` and `~/system/config/launchagents/com.alai.rag-drain-worker.plist` | | `DRAIN_INTERVAL_MS` | `10000` | LaunchAgent environment | | `BATCH_SIZE` | `5` | `~/system/tools/rag-drain-worker.js` default | | `MAX_CONCURRENT` | `1` | LaunchAgent environment; serial uploads remain the design | `rag-drain-worker.js` now treats `DRAIN_INTERVAL_MS` as the deployed alias for `DRAIN_SLEEP_MS`. Effective behavior: batch-5 every 10s can burst near 30 docs/min, while the token bucket caps steady-state uploads at 20 docs/min. This implements the MC #101501 increase from the previous deployed cap of 10 docs/min without adding concurrency. Verification command after edits/restarts: ```bash launchctl print "gui/$(id -u)/com.alai.rag-drain-worker" | grep -E 'state =|pid =|MAX_UPLOADS_PER_MINUTE|DRAIN_INTERVAL_MS' tail -12 ~/system/logs/rag-drain-worker.log ``` Expected evidence lines: ```text MAX_UPLOADS_PER_MINUTE => 20 DRAIN_INTERVAL_MS => 10000 [drain] Rate config: max_uploads_per_min=20 batch_size=5 drain_sleep_ms=10000 ``` --- ## Root Cause (MC #106047) `rag-outbox.js`'s own header comment says *"Do NOT require() this from two processes simultaneously"* — the design assumed a single writer. The deployed topology has five. `openOutbox()` unconditionally ran `PRAGMA wal_checkpoint(TRUNCATE)` on every open, with **no `busy_timeout`** set anywhere in the file. `TRUNCATE` mode requires exclusive WAL access to zero out the WAL file. The four short-lived adapters each open a brand-new connection (and therefore force a new `TRUNCATE` checkpoint) on every invocation — for `rag-fsevents-adapter` this can mean several times per minute during write bursts (1,965+ file touches observed under `evidence/` alone since 2026-07-15). Meanwhile `rag-drain-worker` holds one long-lived WAL connection open continuously. Without `busy_timeout`, contending checkpoints don't wait each other out — and a `TRUNCATE` checkpoint that's interrupted or races another process's checkpoint on the same WAL/shm can leave the b-tree in an inconsistent state. This matches the observed corruption signature exactly (`tree2/page2` btree errors). The Jul 20 event was **silent for 5 days** (corruption occurred ~Jul 15 12:57 per file mtime, discovered manually by John on Jul 20) — proof no integrity guard existed before this fix. Confirmed *not* the cause: unclean shutdown/reboot (no reboot near either corruption window per `last reboot`), disk pressure (6% used, 198Gi free at time of investigation). --- ## Fix (applied 2026-07-20) **File: `~/system/lib/rag-outbox.js`** - Added `db.pragma('busy_timeout = 5000')` right after the `journal_mode`/`synchronous` pragmas, so a connection waits up to 5s for a lock instead of failing/racing immediately. - `openOutbox(dbPath, opts)` now accepts `opts.checkpointMode` (`'PASSIVE'` default, or `'TRUNCATE'`). `PASSIVE` never blocks or forces exclusivity — it checkpoints whatever it safely can. **File: `~/system/tools/rag-drain-worker.js`** - Its `openOutbox(DB_PATH)` call now passes `{ checkpointMode: 'TRUNCATE' }` explicitly — it remains the single designated owner of periodic WAL truncation, per the original single-writer spec. All four other callers (`rag-bookstack-adapter.js`, `rag-mc-adapter.js`, `rag-fsevents-adapter.js`, `lightrag-outbox-ingest.js`) pass no second argument and therefore get the new `PASSIVE` default automatically — no code changes needed in those files. **Verification:** all 5 writer daemons were restarted via `launchctl kickstart -k gui//