opencode.db grows unbounded - event table (4.1GB after ~6 months) has no retention policy or cleanup command #47729

Description

opencode.db grows without bound because the event table has no retention policy or cleanup command. On a ~6-month-old install: 6.2GB database, of which the event table alone is 4.1GB (655k rows hanging off ~1.1k live event_sequence aggregates — ~600 events per aggregate on average), next to 931MB of part and 338MB of message.

There is no documented way to trim it: session rows can be deleted manually (children cascade), but the event stream for remaining sessions keeps growing indefinitely, and the serve process appears to hold a large heap (~8GB in our case) that seems correlated with database contents.

Proposed solution

Any of:

• A built-in retention setting (e.g. keep N days / N events per aggregate)
• A opencode db cleanup / vacuum command that prunes old events (and optionally old sessions) safely with the server stopped
• Automatic compaction of event history for sessions older than a threshold

OpenCode version

1.18.29 (linuxbrew install)

Operating System

Linux x86_64 (Ubuntu), headless opencode serve behind the OpenChamber web/desktop client.

Additional context

We had to stop the server, hand-write SQLite deletes + VACUUM to reclaim space. Also worth noting: cascading deletes only fire if PRAGMA foreign_keys=ON is set in the maintenance session — the app itself appears to run with FKs off, so manual cleanup needs care.