chore(core): design bounded event retention and SQLite reclamation

[LeXwDeX]

This was generated by AI during triage.

Why

The durable event store has no bounded-retention policy. Event.remove(aggregateID) deletes event_sequence and event rows only when a session is explicitly removed through the supported session path; retained sessions keep their full ordered event history indefinitely.

SQLite is initialized without auto_vacuum, so deleting rows does not by itself return allocated pages to the filesystem. The source fixes in #522, #523, and #525 reduce future write amplification but do not define retention or reclamation semantics.

Confirmed current behavior

• packages/core/src/event.ts:621-630 is the production aggregate-event deletion path.
• packages/opencode/src/session/session.ts:735-736 invokes event removal during supported session deletion.
• packages/core/src/event.ts:661-668 exposes ordered incremental replay (seq > after, ascending), so event folding or sequence gaps cannot be assumed safe.
• packages/core/src/database/database.ts:27-33 configures WAL and related pragmas but no auto_vacuum mode.
• Existing databases require an explicit migration strategy; switching to incremental auto-vacuum cannot silently run a blocking full VACUUM at normal startup.

Required decisions

Before implementation, approve an ADR that defines:

1. Retention eligibility: what constitutes an inactive or terminal aggregate without breaking session history or sync replay.
2. Retention bounds: age, size, event count, configuration surface, and defaults.
3. Scheduling and failure semantics: when cleanup runs, concurrency behavior, retry/warning behavior, and observability.
4. SQLite reclamation: behavior for new databases, opt-in migration for existing databases, exclusive-access requirements, and rollback.
5. Compatibility: effects on readAfter, workspace replay, sync cursors, backup/restore, and downgrade behavior.

Scope

• Technical design and ADR for bounded event retention and SQLite space reclamation.
• A disposable-database prototype or benchmark proving the selected SQLite path.
• After the decision checkpoint, the narrow runtime implementation and focused tests.

Out of scope: deleting historical user sessions or running VACUUM against the live local database. That destructive operator procedure is tracked separately in the linked human-only issue. Event snapshot folding is also out of scope unless separately designed against replay contracts.

Acceptance

• The ADR resolves all five decisions above and documents alternatives, rollout, rollback, and non-goals.
• Tests prove active/retained session replay is unchanged and only eligible aggregates are removed.
• Tests cover cleanup failure without blocking the main application path.
• Disposable-file tests demonstrate page reclamation and the new/existing database migration behavior without startup-time full VACUUM.
• bun run test:dag-core, focused event/session tests, package typecheck, and migration freshness checks pass.

Relations

• fix: message snapshot persists untruncated session diff summary into the event log #522, fix: event append has no idempotency guard so identical payloads are re-persisted #523, fix: summary diff cap discards trailing in-budget diffs when an earlier file exceeds 256KB; summary_diffs column is legacy dead code #525: completed source fixes; do not reimplement
• chore(ops): reclaim pre-guard session data from the local database #531: human-only local database cleanup split from this issue

[LeXwDeX]

更正：VACUUM 空闲空间风险已实测排除

正文 Approach 里把「VACUUM 需等量空闲空间」列为迁移坑，实测该风险不成立：

/dev/disk3s5   926Gi 总量   409Gi 已用   480Gi 可用   47%

库文件 10.75 GB，可用余量 480 GiB ≈ 44 倍，全量 VACUUM 与 auto_vacuum 模式切换的一次性 VACUUM 都有充足空间。独占访问仍是硬约束（需停掉 opencode），但空间不是。

另：已安装的 /usr/local/bin/opencode 1.0.39 经数据侧证实已包含 481e31e2b7 的 256 KiB 闸（修复后创建的 239 个会话，最大单条 summary 事件 0.25 MiB），故本条的一次性回收无需先升级 binary。

[LeXwDeX]

This was generated by AI during triage.

Triage result: keep needs-triage. Retention eligibility, bounds, scheduling, and the existing-database auto-vacuum migration are product/persistence decisions, so this is not safe for direct implementation yet. The destructive historical cleanup has been split into a separate ready-for-human operator issue; no code delivery or approval of #524 authorizes touching the live database.

[LeXwDeX]

SpecGit delivery branch: feat/524-event-retention-reclamation (draft pull request #537).

[LeXwDeX]

This was generated by AI during triage.

Decision checkpoint approved on 2026-09-04.

Locked delivery scope:

• Phase 1: explicit session deletion also removes every related DAG aggregate; add a soft-degrading orphan/zombie sweep, default on, using the zero-live-read-model eligibility rule.
• Phase 2: new databases use auto_vacuum=FULL before WAL initialization; existing databases are never converted at startup and require an explicit user command.
• Phase 3 archived-session retention remains off and deferred; active event truncation/snapshot folding is rejected.
• incremental_vacuum is forbidden after a disposable bun:sqlite prototype reproduced an exit-139 crash.
• Offline deletion tombstones, unarchive semantics, active-session bounding, and all live-database work in chore(ops): reclaim pre-guard session data from the local database #531 remain out of scope.

Implementation must resolve the current driver-level WAL initialization order rather than only moving the application pragma, preserve replay/sync contracts, and use disposable databases for every storage test.

Delivery: PR #537 targeting dev.

[github-actions]

Auto-closed: delivery PR #537 (view) merged into dev with a closing keyword for #524 in its body. GitHub's native auto-close only fires on the default branch (main); this mirrors it for the dev integration layer (#519).