Audit ~21 unguarded shared-connection SQLite stores (follow-up to #927/#929)

[olegbrok]

Scope

#927's root cause — a long-lived check_same_thread=False connection shared across threads with no serialization — is a codebase-wide pattern, not a task_store one-off. Verified on main: 25 files use check_same_thread=False; only 4 carry any lock (agent_registry, mesh_store, message_context_store, pinky_memory/store). task_store (fixed in #929) was simply the store hot enough to lose the race first.

Unguarded stores to audit: session_store, conversation_store, agent_comms, skill_store, trigger_store, app_store, user_profile_store, research_store, voice_store, activity_store, hooks, dream_runner, plugin_manager, outreach_config, presentation_store, hub_store, bearer_tokens, signer_store, federation/state, imessage (plus re-check the 4 locked ones for lock coverage of ALL access, not just RMW).

Template

#929 established the pattern: _db as a thread-local property (per-thread sqlite3.connect, per-connection pragmas — pragmas are per-connection in SQLite so the old single application was already incomplete — default check_same_thread enforcement restored, caller-local close). Apply per store with judgment, not mechanically: a store whose access is provably single-threaded (document why) may instead get an assert; a store needing cross-call transactions needs the lock approach instead.

Method (batched, not one mega-PR)

1. Classify each store: call-site thread census first (which daemon threads touch it).
2. Priority by heat: session_store, conversation_store, agent_comms first — highest concurrent traffic.
3. Convert in small batches (3-5 stores/PR) with a per-store concurrency hammer in the Make task store SQLite connections thread-local #929 shape (shared-row point-read-heavy — the incident poisoned during a point read).
4. Each PR through the standard gauntlet.

Acceptance

Zero files with an unguarded shared check_same_thread=False connection; every conversion carries its hammer; the four legacy-locked stores re-verified or converted for consistency.

Related: #927 (incident + forensics), #929 (template fix), #797/#220 (prior art in the same family).

🤖 Opened by Barsik

[olegbrok]

Framing correction — audit proceeds, evidence downgraded

Per the correction on #927 (2026-07-29): both Pi incidents attributed to the shared-connection race are now underdetermined — an external direct-sqlite open of the daemon-held db (which unlinks wal/shm on close and poisons the daemon's connection) produces the identical signature, and it was running during both windows.

What this changes here:

• The audit's justification is code-level: a shared check_same_thread=False connection with zero serialization is unsafe by construction. That stands regardless of which mechanism fired on the Pi.
• The "priority by heat" ranking loses its proven-incident backing; treat it as a traffic-based ordering heuristic only.
• Conversion PRs (batch 1 in flight) should not cite the Pi incidents as confirmed race firings.

Adjacent-but-distinct hazard surfaced by the same forensics, tracked separately: daemon has no self-heal for a poisoned connection, and external in-place opens of daemon-held WAL dbs are unconditionally dangerous (even mode=ro recreates the shm).

🤖 Barsik

[olegbrok]

Batch 1 merged: PR #933 → 28e57c1 (session_store, conversation_store, agent_comms → thread-local per the #929 template, with per-store shared-row point-read hammers). Full gauntlet: independent reviewer reproduction of the 816-test matrix + hammers 30/30, exact-SHA CI green on all legs (3.12 slow-green at 25m39s — runner, not code), pragma-parity ruling documented on the PR (FK-off preserved in agent_comms by decision). Batch 2 (next ~4 stores by census traffic) dispatched. 17 stores remain after batch 2 selection.

🤖 Barsik

[olegbrok]

Batch 2 merged: PR #934 → db41aed (activity_store, skill_store, trigger_store, user_profile_store → thread-local, per-store 12-thread/25-round shared-row hammers). Gauntlet: reviewer PASS with no actionable findings (dedicated #899/#889 gates verified — conversion touches only connection ownership/lifecycle; per-store pragma parity incl. Trigger keeping its original FK ON), exact-SHA CI green on all legs (4478 passed × 3 Python versions).

Running tally: 8 stores converted (task #929; session, conversation, agent_comms #933; activity, skill, trigger, user_profile #934). Remaining unguarded: app_store, research_store, voice_store, hooks, dream_runner, plugin_manager, outreach_config, presentation_store, hub_store, bearer_tokens, signer_store, federation/state, imessage — plus the re-check of the 4 legacy-locked stores. Batch 3 dispatches when the builder lane frees (currently on #931).

🤖 Barsik