Synchronous SQLite on the event loop: one cold query froze all clients for 11s on a 1.7GB bb.db

[amadad]

Summary

On a self-hosted instance with a grown bb.db (1.7GB), a single cold thread-list query stalled the server event loop for 11.2 seconds — during which even static asset requests hung (measured 7.3s TTFB on /). On mobile Safari over Tailscale this presents as the whole app freezing for seconds at a time; the client is fine (boot payload is well-split and cached — nice work on #1071).

Measurements

• Event loop stalled {"maxDelayMs":11232.3, ...} logged at the exact moment of a first (cold-cache) bb thread list --json after a restart; subsequent identical calls ~0.5s server-side.
• While stalled, curl TTFB on / (static index.html) went from 2ms → 7.3s. One synchronous query freezes every client and every route.
• We had previously logged stalls up to 22s under memory pressure (leak to 4.2G before MemoryHigh, since capped).

Why the DB got big (contributing factor)

better-sqlite3 is synchronous on the serving loop, so stall duration scales with DB size/page-cache misses. Ours grew because the completed-event output truncation sweep effectively never fires:

• events = 1,447MB of 1.7GB; item/completed payloads = 913MB, 815MB of it commandExecution.aggregatedOutput.
• COMPLETED_EVENT_OUTPUT_TRUNCATION_THRESHOLD_CHARS is 32KB, but the real-world median payload is ~5-10KB — almost nothing qualifies, so the sweep reclaims ~0.
• DEFAULT_COMPLETED_EVENT_OUTPUT_TRUNCATION_BATCH_SIZE of 250/hour is below the generation rate of a busy multi-agent instance (~240 qualifying rows/hour for us) — even a threshold fix can never work down a backlog.

We're running both tuned on our fork (4KB threshold, 1000 batch): https://github.com/amadad/bb/commits/release/atum-pi-model-resolve — happy to PR either.

Suggestions (in rough order of value)

1. Move heavy read queries (thread list, timeline) off the event loop — worker thread pool for better-sqlite3, or async driver for the hot read paths. This is the structural fix; retention only shrinks the constant.
2. Lower the truncation threshold default (32KB → 4-8KB) and/or make it configurable; size the batch relative to generation rate.
3. Consider retention for automation_runs (currently unbounded, only cascade-deleted).

Environment

bb-app 0.35.1 self-hosted (Hetzner 4 vCPU/8GB, Node 24), bb.db 1.7GB / ~595K events, heavy automation usage (~1,000+ bb-originated provider sessions/month).