Readers never upgrade to writers. open_state_db_readonly() opens
file:...?mode=ro only; a read-only open failure propagates instead of
silently retrying with a write-capable handle on the multi-GiB WAL
database the agent is streaming into. The session listing no longer
self-heals idx_messages_session with CREATE INDEX (a writer lock held for
minutes on a large messages table) — a missing index degrades to the
pre-aggregated path. Cron sidebar info, insights and deep health checks in
api/routes.py use the same read-only opener.
Watcher (api/gateway_watcher.py): _poll_once returns without touching the
database when nobody is subscribed, and forces a fresh full projection on
the next subscription.
Maintenance (scripts/ensure_state_db_read_indexes.py): the covering read
indexes (idx_messages_session, idx_messages_session_role with NOCASE,
idx_sessions_webui_fingerprint) are created only under --confirm-drained,
optionally holding an exclusive flock on --lock-file for deployments that
serialise agent turns on a lock file; existing indexes are verified for
shape/collation and coverage (EXPLAIN QUERY PLAN), never silently
replaced.
Tests: tests/test_state_db_readonly_readers.py (read-only failure never
retries writable; no poll without subscribers; listing creates no index;
index tool requires drain, is idempotent, works without a lock file and
refuses a held lock; cron sidebar read is mode=ro). Neighbouring tests
that asserted the retired writable fallback / self-heal writer
(test_issue3887_index_prime, test_issue5455_listing_readonly_connection,
test_issue5455_lineage_readonly_reads, test_issue2628_cli_sessions_perf,
test_issue1494_state_db_fd_leak) now assert the read-only contract;
test_issue3506 subscribes before polling.
The session-listing path already opens the live agent state.db read-only
(file:...?mode=ro) so a write-capable handle doesn't add checkpoint/lock
surface while the agent streams into the same WAL DB (#5455). Three pure-read
projections were missed and still opened a read-WRITE connection:
- read_session_lineage_report
- read_session_lineage_metadata
- the gateway-watcher fingerprint projection (a 5s poll)
Route all three through a shared open_state_db_readonly() helper that mirrors
the listing path: read-only file: URI with a writable fallback that warns. The
self-heal write path (missing idx_messages_session) is intentionally left
writable. No behavior change beyond open mode; reads perform zero writes.
Refs #5455
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Anthony Ruiz