fix(core): bound event log compaction

[chubes4]

Issue for this PR

Closes #33356

Type of change

• Bug fix
• New feature
• Refactor / code improvement
• Documentation

What does this PR do?

Adds read-only event-log status plus explicit, dry-run-by-default bounded compaction (--session or --all, --apply, maximum 10,000 rows). Apply verifies latest message/part projection contents, batches in one immediate transaction, and only writes checkpoints for aggregates with no workspace or sync owner. Sync has no marker version negotiation, so synced/owned aggregates are reported and rejected.

How did you verify your code works?

• bun test test/session-event-log-compaction.test.ts test/event.test.ts
• bun typecheck (30 packages)
• Prettier check

Screenshots / recordings

Not applicable; database maintenance CLI.

Checklist

• I have tested my changes locally
• I have not included unrelated changes in this PR

AI assistance

• AI assistance: Yes
• Tool(s): GPT-5.6-terra via OpenCode
• Used for: Implementation, tests, and review-driven revision; Chris owns the change.

[github-actions]

Hey! Your PR title Compact superseded session event snapshots doesn't follow conventional commit format.

Please update it to start with one of:

• feat: or feat(scope): new feature
• fix: or fix(scope): bug fix
• docs: or docs(scope): documentation changes
• chore: or chore(scope): maintenance tasks
• refactor: or refactor(scope): code refactoring
• test: or test(scope): adding or updating tests

Where scope is the package name (e.g., app, desktop, opencode).

See CONTRIBUTING.md for details.

[github-actions]

Thanks for updating your PR! It now meets our contributing guidelines. 👍

[chubes4]

Rebased onto current dev (323d687173) and reverified after the latest upstream changes.

Fresh production evidence from today: ~/.local/share/opencode/opencode.db is now 27.2 GiB (page_size=4096, page_count=7,124,817, freelist_count=0), so this remains live append-only event data rather than reclaimable SQLite slack.

Verification on the rebased branch:

• bun test test/session-event-log-compaction.test.ts test/event.test.ts — 45 passed
• bun typecheck — 30 tasks passed across the workspace
• bun prettier --check $(git diff --name-only origin/dev...HEAD) — passed

The branch is current, clean, mergeable, and CI is green. Maintainer review would be appreciated.

[itse4elhaam]

Any timeline on this getting merged?

My local opencode.db grew by 36 gbs in the past 3 weeks.

[chubes4]

I'll try Discord and see if I can get a maintainer to look at this. Mine got up to 84gb.

[chubes4]

Production-scale dry-run validation at exact head fe5e1de1e00d97aa391b1cd78bc4011c17ca5f22 found one operational blocker and one safety-boundary issue.

Safety boundary

I did not point this PR CLI directly at the live database. Database.Service opens the normal writable layer, executes journal_mode, wal_checkpoint(PASSIVE), and DatabaseMigration.apply() even for event-log-status and dry-run. I used an APFS copy-on-write clone of the production main DB and confirmed the CLI target first with OPENCODE_DB=... opencode db path. The clone had the production shape while avoiding production writes (the source WAL was about 2 MiB and was intentionally not copied).

Snapshot:

• DB size: 96,975,740,928 bytes
• pages: 23,675,718 x 4096
• freelist: 0
• events: 7,894,217
• parts: 1,640,035
• messages: 311,823
• sessions: 6,376

Verification

• bun test test/session-event-log-compaction.test.ts --timeout 30000 --only-failures: 1 pass, 21 assertions
• packages/core typecheck: pass
• packages/opencode typecheck: pass
• opencode db event-log-status: completed successfully

{
  "events": 7894217,
  "payloadBytes": 79722946429,
  "compactableEvents": 7571556,
  "recommended": true
}

Session-scoped dry-run:

opencode db compact-events --session ses_05c8c577dffe3gCA4gW5hlPzUH --limit 1000

Completed in 243.24s, with dryRun: true, candidates: 1000, rewritten: 0, projectionMismatches: 0, compatibilityRejected: 0, malformed: 0, payloadBytesReclaimed: 463240, and hasMore: true.

All-scope dry-run:

opencode db compact-events --all --limit 1000

Produced no report before being terminated after 3600s.

EXPLAIN QUERY PLAN confirms why the output limit does not bound the work: the latest CTE scans event through event_aggregate_type_seq_idx, evaluates JSON, and uses a temporary B-tree for GROUP BY; the outer query also uses a temporary B-tree for ORDER BY. LIMIT 1001 is downstream of that global work.

Post-run invariants on the clone remained unchanged: 7,894,217 events, 79,722,946,429 payload bytes, 38 migrations, freelist 0, and 0 event.compacted.1 markers.

Conclusion

The dry-run mutation guard itself held, and session-scoped execution works against this dataset. However, --all --limit 1000 is not operationally bounded at production scale because candidate discovery performs global JSON grouping before the limit. Also, status/dry-run are logically read-only at the compaction function level but not at the CLI database-layer level. I recommend addressing both before treating this as safe production maintenance tooling.

AI assistance disclosure: OpenAI GPT-5.6-sol via OpenCode was used to inspect the implementation, run the isolated validation, analyze the SQLite query plan, and draft this report. Chris Huber reviewed and is responsible for the submitted findings.

[chubes4]

Updated the PR at 063a6d9ec42232d045e84bfcbd2c1752b6e2e055 after the production-scale blocker found in the previous report.

Repair

• Replaced global 7.9M-row JSON grouping with keyset-resumable session and event-sequence cursors.
• Bounded each all-scope invocation to 25 session aggregates and at most the requested snapshot limit.
• Merged message and part candidates by aggregate sequence so neither policy starves or gets skipped.
• Advanced past projection/compatibility rejections instead of trapping subsequent batches.
• Strengthened projection verification to include entity, session, and part-to-message linkage.
• Added a dedicated read-only database layer for status/dry-run; apply retains normal writable initialization.
• Fixed Bun SQLite read-only flags so read-only means readwrite: false, create: false, and no WAL-mode write.
• Merged current origin/dev into the PR branch.

Verification

• Focused core suite: 17 pass, 63 assertions.
• packages/core typecheck: pass.
• packages/opencode typecheck: pass.
• Push hook monorepo typecheck: 30 packages passed.
• Focused lint: zero errors (warnings were pre-existing assertions in touched legacy files).

Production-clone results on a 96,975,740,928-byte database with 7,894,217 events:

• Previous --all --limit 1000: no output after 3,600 seconds.
• Updated --all --limit 1000: completed in 1.40 seconds.
• Initial dry-run: 225 candidates across both message and part policies, 517,536 reclaimable payload bytes, zero mismatches.
• Applied clone validation: 200,215 events rewritten across resumable 10,000-row batches.
• Logical payload reclaimed in those batches: approximately 2.8 GB.
• Projection mismatches encountered and safely skipped: 10.
• Sync compatibility: zero workspace sessions and zero owned aggregates in this database snapshot.
• Post-apply SQLite PRAGMA quick_check: ok.
• Clone event count remained 7,894,217; IDs/sequences are retained by the update predicate.

A byte-for-byte fixture test confirms the read-only layer leaves the main DB unchanged and creates no sidecars for a rollback-journal database. Against the WAL production clone, dry-run left the main DB and WAL unchanged; SQLite updated the existing SHM lock-coordination file mtime, which is expected for a concurrent read-only WAL connection.

Full clone compaction was intentionally stopped after 200,215 rewrites because APFS copy-on-write consumed about 17 GB during mutation and completing all 7.5M snapshots would exhaust local disk. This is a clone-storage constraint, not a query or integrity failure.

AI assistance disclosure: OpenAI GPT-5.6-sol via OpenCode analyzed the production query plan, implemented and reviewed the resumable/read-only repair, ran tests and typechecks, and validated it against the production clone. Chris Huber reviewed and is responsible for the submitted changes and evidence.

[chubes4]

Added the complete maintenance workflow at 006c45735cb3cb0dbe17881f5f11201f6946d179.

The PR now owns the entire operation:

opencode db compact-events \
  --all --apply --limit 10000 --until-done \
  --backup /absolute/path/to/recovery.sqlite \
  --vacuum

Safety properties:

• Acquires SQLite exclusive locking mode before the first batch and retains it through backup verification and source vacuum, preventing concurrent writers from invalidating cursor completion or diverging the backup.
• Runs every structured aggregate/sequence continuation internally; no shell parsing or external loop.
• Counts malformed rows only within each cursor interval.
• Runs source PRAGMA quick_check before physical reclamation.
• Creates a compact recovery database with VACUUM INTO.
• Attaches and verifies the recovery database with quick_check before vacuuming the source.
• Runs source VACUUM, verifies the source again, and reports exact before/after bytes.
• Requires --all --apply --until-done --backup <absolute-path> --vacuum together for physical reclamation.

Validation on a production-derived 100-session fixture:

• 91,673 total events; 88,243 snapshot events; 959,777,454 payload bytes.
• Completed 7 internal batches in 75.99 seconds.
• Inspected 66,143 superseded snapshots.
• Rewrote 66,133; safely skipped 10 projection mismatches.
• Reclaimed 809,040,739 payload bytes logically.
• Created and verified the recovery database.
• Vacuumed source from 1,170,042,880 to 321,605,632 bytes: 848,437,248 bytes physically reclaimed.
• Source quick_check: ok.
• Backup quick_check: ok.
• Exact bidirectional EXCEPT comparison of all event columns: zero differing rows.
• A second idempotent full workflow rewrote zero rows and retained source/backup integrity.
• Exclusive-lock test proves a second SQLite writer receives SQLITE_BUSY while maintenance owns the database.

Verification:

• Focused suite: 19 pass, 67 assertions.
• Core and CLI typechecks: pass.
• Push hook monorepo typecheck: 30 packages passed.
• PR standards/compliance checks remain green on the preceding head and will rerun for this head.

AI assistance disclosure: OpenAI GPT-5.6-sol via OpenCode designed, implemented, reviewed, and production-fixture-tested the exclusive maintenance, backup verification, and vacuum workflow. Chris Huber reviewed and is responsible for the submitted changes and evidence.