SQLite db silently reverted to a 2-week-old state while the server was running (post-Aug-9 projects/threads lost; auto-update in progress)

[arnocode84]

Summary

While the desktop app's server was running (process up since Aug 16, never restarted — verified by pid/lstart), the SQLite database at ~/.bb/bb.db silently reverted to a ~2-week-old state. Every project, thread, and setting created after ~Aug 9 disappeared; rows from Aug 6–9 (including threads long gone from the sidebar) came back.

Environment

• bb.app desktop 0.38.0 at incident time (auto-updated to 0.39.0 later the same day)
• macOS arm64, Darwin 25.5.0
• Default data dir ~/.bb, default local server 127.0.0.1:38886, single machine, no remote/connect pairing

Timeline (local time, Aug 21)

• 15:04 — everything normal: spawned a thread via CLI, read its output, bb environment archive-threads OK, bb project list showed 4 projects.
• ~15:10–15:11 — bb.db mtime rewritten. First failing call: bb automation create --project <id> → HTTP 404: Project not found (first-ever call to the automations plugin on this install; correlation only, not proven causal).
• After that: bb project list --json → [] (only the Personal project remains), today's thread IDs → HTTP 404: Thread not found, and three threads dated Aug 6–9 reappeared.
• The server process had not restarted (same pid since Aug 16, one listener on 38886).
• Later, on quitting the app, a Squirrel/ShipIt auto-update turned out to be in progress; while it was replacing the bundle we observed a transient CLI/server flag mismatch (--root vs --path on project create). Possibly related to the incident, possibly not.

Evidence gathered

• Direct SQLite read of bb.db right after: projects contains only proj_personal (created Aug 6); newest threads rows are from Aug 6–9.
• The WAL was ~7 MB; replaying it on a copy of the db does not surface the lost rows either.
• server.7.log around the event shows only recurring Skipping malformed prompt history row (ZodError: empty array) for the three old threads, plus [plugin:connect] rpc listAccountServers failed: not_paired noise. No migration/reset/checkpoint entries.
• No APFS local snapshots were available for recovery.
• A cold copy of bb.db + -wal + -shm taken ~5 min after the incident is available on request.

Impact

All projects/threads/automations created between ~Aug 9 and Aug 21 lost (4 projects, ~10 threads including that morning's runs). Recreated by hand afterwards; the recreated data survived a proper quit/relaunch and the 0.38.0 → 0.39.0 update.

Hypothesis (take with salt)

The surviving state matches what the main db file would hold if a large never-checkpointed WAL were discarded (rollback instead of replay). Anything in the updater, a migration path, or a second connection that could open the db in a way that resets the WAL would produce exactly this: a silent revert to the last checkpoint, with the server process still up and serving.

Reproducibility

Not reproducible on demand. Happy to provide the cold backup files or run diagnostics.

[SawyerHood]

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

• Verdict: PARTIALLY REPRODUCED · root-cause confidence: medium
• Root cause (short): bb keeps the entire database in bb.db-wal (WAL mode, SQLite's default 1000-page auto-checkpoint only, handle never closed at shutdown), so bb.db can be weeks stale or empty and any pinned reader freezes it; when the WAL header/wal-index becomes unreadable SQLite recovery silently treats the WAL as empty and the still-open server handle serves the last checkpoint. The reporter's bb.db mtime at 15:10 is NOT produced by this mechanism (the post-revert commit does not write bb.db; bb would need ~1000 more WAL pages), so an external actor that wrote bb.db directly (in-place restore/copy/sync of the three files) is now the leading candidate; it was not identified.
• Proposed fix: (1) Close the SQLite handle in runShutdown() (apps/server/src/start-server.ts) so SQLite performs its close-time checkpoint — bounds the empty-bb.db exposure across restarts, does not help the reporter's no-restart/pinned case; (2) periodic PRAGMA wal_checkpoint(PASSIVE) (e.g. every 60 s in the sweeps) that reads (busy, log, checkpointed) and logs a warning when checkpointed < log repeatedly ("a reader is pinning bb.db-wal; bb.db is N frames behind") plus WAL size — bounds the window only when no reader pins the WAL (pinned-reader experiment shows checkpointed:0 of 8314 while pinned), otherwis…
• Caveats: Revision addressed all verifier findings by re-running: (a) the harness-only wal_checkpoint(PASSIVE) was removed from walkthrough step 6 (now prints 'bb.db mtime changed: false'), and new steps 9-10 measure that bb on its own needs 195 one-row commits (1001 WAL pages) before the auto-checkpoint writes bb.db, by which point stale frames are overwritten and recovery is impossible; an explicitly labe…

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

AGENT GENERATED: by Claude Opus 5