[Feature] Add event-log GC/compaction and/or per-project database to prevent unbounded opencode.db growth #47361

Feature hasn't been suggested before.

• I have verified this feature I'm about to request hasn't been suggested before. (Searched issues for "database size", "sqlite", "vacuum", "event table", "bloat" — the existing specs/storage/remove-opencode-db.md work is a code-layer refactor, not data-retention governance.)

Describe the enhancement you want to request

Add event-log GC/compaction (and ideally per-project database isolation) to keep opencode.db bounded and prevent multi-instance startup hangs on large databases.

Motivation (measured, real-world)

After ~2 months of heavy daily use (~3,000 sessions, ~30 opencode instances kept alive in parallel across projects), my global opencode.db reached 3.99 GB, of which the event table is ~1,000,000 rows / 2.89 GB (72.4%). It grows on every streamed token because each message.part.updated.1 event stores a full snapshot of the part — append-only event sourcing with no retention mechanism.

Measured breakdown (SQLite dbstat):

Consequence at this scale: starting a new opencode instance hangs indefinitely — blank TUI (71 empty lines), zero log output (not even bootstrapping), no MCP subprocess spawn, all 86 threads idle in futex/epoll. Same binary/config starts fine when the DB is smaller or after most instances are closed. Increasing swap 8G→32G did not help; error-log evidence (timeout ~6k, 502 ~2k, 503 ~1k lines; network-layer errors: 2) points to SQLite single-writer contention behind a multi-GB file, not network or memory.

Positive check: session delete cascades correctly (zero orphan events verified), so cleanup exists per-session — but there is no bulk/retention tooling for users with thousands of sessions.

Requested enhancements

1. Event-log compaction/retention: snapshot-and-prune per aggregate (keep current part state + bounded recent history), or a retention policy (e.g. drop message.part.updated events older than N days / beyond the last K per aggregate). This directly bounds the 72% growth driver.
2. Per-project / sharded database: honor an explicit override (e.g. OPENCODE_DB_PATH) or document per-domain XDG_DATA_HOME isolation, so heavy multi-project users can split the single contention point.
3. Startup resilience (fallback if 1/2 are deferred): bound database access during boot with a timeout and degrade gracefully, so a huge/contended DB can never wedge the TUI before first render.

Environment

• OpenCode 1.18.28 (npm opencode-ai), Ubuntu 24.04.4 LTS, kernel 7.0.0-30-generic
• AMD Ryzen Threadripper PRO 7985WX (64C/128T), 502 GiB RAM, NVMe storage
• 30+ instances via detached tmux panes; plugins: compaction-autocontinue-guard, tmux-bridge, session-memory-recall, mcp-tool-retrieval-hint (can re-test with --pure on request)
• Full per-table data, event samples, process-level stuck signature, and hourly error-log distribution available on request.