arch: split SQLite read/write pools in ingestor (remove SetMaxOpenConns(1) single-pool architecture)

[Kpa-clawbot]

Context

All four reviewers in PR #1735 (round-1 review) noted that SetMaxOpenConns(1) on the ingestor is the underlying reason every long-running write pins all readers + writers behind it. The chunked-backfill fix in #1735 is a tactical workaround; the strategic fix is to split read/write connection pools.

Proposal

Split SQLite handles in cmd/ingestor:

• One write handle with _journal_mode=WAL&_busy_timeout=N, SetMaxOpenConns(1).
• One read handle with mode=ro, SetMaxOpenConns(N>1).
• Long-running observability reads (status snapshots, perf endpoints inside the ingestor) go to the read handle and stop competing for the writer.

Why

• Removes the entire class of "warm-up banner stuck because /api/healthz queues behind a write" failures that v3.9.2 cold-load: reader p95 catastrophically degraded for 10-15 min after backgroundLoadComplete=true #1724 / fix(#1724): chunk tx_last_seen_backfill_v1 to yield SQLite writer to readers #1725 / fix(#1724): chunk tx_last_seen_backfill — bounded reader-yield + progress surface #1735 have been chasing.
• Brings ingestor parity with the read/write separation invariant already enforced server-side (bug(db): vacuumOnStartup fails with SQLITE_BUSY when ingestor + server share DB (auto_vacuum migration #919 broken in single-container topology) #1283).

Risk

• Larger PR. WAL ordering semantics must be re-verified against bridge_handle_nodes + write-path tests. Worth a design doc first.

[Kpa-clawbot]

Severity: P2 — architectural; root cause of multiple reader-starvation incidents (#1724, #1725, #1735).

Claim: confirmed. cmd/ingestor/db.go:120 db.SetMaxOpenConns(1); comments at :1359, :1715, :1937 acknowledge writes serialize behind this. Same handle serves observability reads inside the ingestor process.

Findings:

• (carmack): Splitting read/write handles is the correct DB-side fix. Write handle keeps SetMaxOpenConns(1) + WAL; read handle opens ?mode=ro&_journal_mode=WAL with SetMaxOpenConns(N). SQLite WAL semantics already permit concurrent readers against a single writer; we're not using that property today.
• (munger): fix(#1724): chunk tx_last_seen_backfill — bounded reader-yield + progress surface #1735 / v3.9.2 cold-load: reader p95 catastrophically degraded for 10-15 min after backgroundLoadComplete=true #1724 / fix(#1724): chunk tx_last_seen_backfill_v1 to yield SQLite writer to readers #1725 are the tactical band-aids. Each one cost a PR + review cycle. The cost of NOT doing this is paid every time we add a long-running write path.
• (dijkstra): "Re-verify WAL ordering vs bridge_handle_nodes + write-path tests" — yes, but cheaper than the design-doc framing suggests: any operation that previously assumed read-your-own-write within the same *Store needs to be audited. Most are point lookups after commit; safe.

Fix path: in cmd/ingestor/db.go, refactor Open to return (writeDB, readDB) pair; thread readDB through observability paths (status snapshots, /api/healthz callers inside ingestor). Effort: L.

Dup check: searched "split read write pool ingestor" — none open. Server-side parity tracker: #1283.

Triage verdict: needs-operator-input (design-doc-first per author note; L effort + invariant change).