Prevent synchronous SQLite reads and retention sweeps from blocking the server

[amadad]

Current umbrella scope

This is the parent tracker for synchronous SQLite work blocking the server event loop across heavy reads and retention sweeps. The original 11-second thread-list cause was addressed by #1204; later reports identify residual reads, timeline stalls, and periodic sweep costs. Do not treat the original measurement as proof that its exact query is still unfixed.

• Thread-list reads still run synchronously on the event loop after #1204 #1207 — Residual thread-list/read isolation and shared machinery for other heavy reads, including the timeline path described in follow-up comments.
• bb-app: event loop stalls (~2s) from sync SQLite / events truncation without yielding #3196 — Investigate truncation-sweep stalls, bounded/yielding work, and accurate blocking-work attribution on the reported 0.42.1 path.
• Preserve query-level efficiency as well as event-loop isolation; use measured offending operations to choose the fix.
• Validate cold/warm large-history reads and retention activity while unrelated requests remain responsive, recording query, decoding, projection, and sweep costs separately.
• Review retention threshold and batch throughput against history generation rates; document any resulting configuration consistently across SDK/CLI and UI where applicable.

#3196's truncation mechanism is a suspected cause, not a newly confirmed reproduction. Child issues retain their independent evidence and acceptance criteria. Remaining umbrella retention questions, including automation-run retention, are not completed merely by closing a read-isolation child.

Historical report

The following measurements and environment describe the original 0.35.1 incident; follow-up evidence is in the comments and linked children.

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).

AGENT GENERATED

[SawyerHood]

I did a big perf fix for the sidebar that should go out in the next release

[amadad]

Thanks — confirmed. We are now running 0.36.1-nightly.31381952211.1 (cb3054d97), which includes #1204 and #1209. Migration 0088 applied successfully, and events_goal_thread_sequence_idx is present on our ~1.6 GB / 652k-event production database. We’ll monitor true cold-cache sidebar loads and report timings or event-loop stalls if the residual work tracked in #1207 still appears. Appreciate the detailed root-cause work.

[amadad]

This remains reproducible on bb-app 0.37.0, now specifically on the timeline path.

Production state:

• bb.db: about 1.66 GiB
• events: about 710k rows / roughly 1.03 GiB of event JSON
• largest active conversation observed: 44,246 events / 131.7 MiB JSON

Immediately after upgrading to 0.37.0, opening its latest timeline page logged:

• total: 3,578 ms
• event-query: 3,462 ms
• 1,212 rows / 1.74 MiB returned
• JSON decode: 46 ms
• projection: 54 ms

The event query blocked the serving event loop, so this is not only a retention-size concern. The dominant stage is the synchronous timeline read. This appears to satisfy #1207's stated revisit condition: a real install still reports event-loop stalls after the query-level thread-list fixes, and another route now needs the same isolation machinery.

[SawyerHood]

Reproduction & root-cause report: https://get-bb.github.io/reports/issues/1131.html

• Verdict: PARTIALLY REPRODUCED · root-cause confidence: high
• Root cause (short): All SQLite work is synchronous on the single server event loop; the original 11 s thread-list stall was fixed by Fix the #1131 cold stall at its source: index the latest-goal lookup, drop the thread-list GROUP BY #1204, but the latest timeline page still runs two window-independent whole-thread scans (listTodoSnapshotEventRowsForThread fetching every tool-call payload, and listStoredEventRowsByParentToolCallIds with sequenceBounds=null walking every event with json_extract), reading 77-100 MB / 918 major faults for a 1.7 MiB page on a 44k-event thread and blocking every other request for the duration; the periodic sweeps (unbatched destroyed-environment prune with SET NULL over ~297k event rows, per-tick truncation UPDATE) are additional synchronous multi-second writes on the same loop.
• Proposed fix: 1) Bound the parent/child backfill on the latest page: pass sequenceStart: window.sequenceStart instead of sequenceBounds null in selectStandardTimelineEventRows (apps/server/src/services/threads/timeline.ts ~L1450) and back listStoredEventRowsByParentToolCallIds with a partial index on rows that have a parentToolCallId, so it stops walking all 44k events with json_extract. 2) Limit listTodoSnapshotEventRowsForThread to the todo/task rows: add a partial expression index events(thread_id, sequence) WHERE item_kind='toolCall' AND type IN ('item/started','item/completed') AND json_extract(data,'$…
• Caveats: Absolute stall times were reproduced at ~10x smaller magnitude (0.28-0.46 s cold vs the reporter's 3.4 s) because this host has local NVMe and 16 cores; the transferable evidence is 77-100 MB of disk reads / 918 major page faults for a 1.7 MiB page and the query plans, not milliseconds. The revised cold-timeline script now waits for the startup sweeps (destroyed-environment prune 5.0 s here / 26.8…

The report has copy-paste repro steps, screenshots where the bug is visual, code permalinks at 16ceb3a54, and repro artifacts. It was independently re-run by a second agent (see its Verification section).

AGENT GENERATED: by Claude Opus 5