[FEATURE]: V2 offline SQLite inspection, verified backups and vacuum #54618

Activity

1. opencode-agent commented on Oct 11, 2026

   

   opencode-agentBot

   on Oct 11, 2026 – with OpenCode Agent

   Contributor

   More actions

   Thanks for the detailed proposal and for keeping the attribution from #41711! As I understand it, you'd like v2 to have offline opencode db inspect, opencode db backup and opencode db vacuum commands. They would let users look at a SQLite database, make a checked recovery copy and reclaim free pages, without running migrations or deleting sessions. Maintainers will look at whether this belongs in core and give feedback on the command interface.

   Related issues and PRs:

   • feat(storage): add safe database maintenance controls #41711: the closed maintenance PR this request takes a subset of (it carried forward fix(core): bound event log compaction #36710)
   • [FEATURE]: Session Lifecycle Management — unified storage reclamation, auto-archival, and child session cleanup #16101: session lifecycle and storage reclamation
   • Unbounded growth of the event table: opencode.db reaches 13GB+, mostly message.updated.1 snapshots (no retention/compaction) #33356: unbounded growth of the event table (v1 message.updated.1)
   • EventV2 cold history duplicates large snapshot payloads and amplifies SQLite storage/reads #47854: EventV2 cold history duplicating large snapshot payloads
   • Excessive storage: event table stores full message snapshots per streaming update — community tool available #41175 / event table dominates the database: 9.03 GB of event log for 1.09 GB of message content #53760: earlier reports of event table storage overhead
   • SQLite auto_vacuum is disabled, database files grow indefinitely #31526: SQLite auto_vacuum being disabled (closed)
   • feat(core): add replay-safe event log retention #50287: replay-safe event log retention (closed PR)
   • [FEATURE]: Project-scoped stores — split the global session DB into a system DB + per-project DBs #47429: proposal to split the global database into per-project stores
2. GladSI commented on Oct 25, 2026

   

   GladSI

   on Oct 25, 2026

   More actions

   Data point for db vacuum --apply: the expensive half can run online

   From a 17 GiB store on 2.1.4: VACUUM INTO '/tmp/copy.db' against the live database, over a read-only connection with the daemon still running, produced a consistent compact copy in 5.9 s — 17 203 MiB → 780 MiB, quick_check ok, row counts identical (133 sessions, 8 570 messages, 124 309 parts, 9 credentials), no exclusive lock needed.

   So --apply could keep the exclusive window down to the cheap part: build and verify the copy online, then take ownership only to rename it into place and truncate the WAL. That also means the slow-looking step (rewriting ~780 MiB) does not have to be what forces a daemon stop on a store that is several GB of mostly free pages.
3. romanilyin commented on Oct 26, 2026

   

   romanilyin

   on Oct 26, 2026

   Author

   More actions

   Thanks @GladSI — this is a useful measurement, especially the time needed to produce the compact copy. I agree that VACUUM INTO is worth considering for online compact backups.

   For --apply, there is an extra correctness requirement: SQLite documents the output as a consistent snapshot. Writes committed after that snapshot would not be included. Taking ownership only for the final rename would therefore need a reliable way to detect and handle those writes, plus coordinated connection shutdown, WAL handling and crash recovery. Integrity checks and matching row counts alone would not establish that no newer data is lost.

   I would keep the first implementation scoped to offline maintenance, and treat an online preparation/short cutover mode as a follow-up with concurrent-write and recovery tests. The prototype is in romanilyin#1. Maintainer feedback on whether the offline commands belong in core, and on their interface, would help me decide the next upstream step.