plan: diagnose & reduce 47 GiB opencode.db bloat; rebuild report tool in Go; session intelligence for skills

[itse4elhaam]

Problem Statement

The OpenCode SQLite database (~/.local/share/opencode/opencode.db) has grown to ~47 GiB (12.16M pages @ 4 KiB) with zero reclaimable free space (freelist_count = 0). The original opencode-db-report script — meant to diagnose this — hung for 7+ minutes with no output, because it ran every query unconditionally and buffered the entire report in memory before writing anything.

Investigation (all read-only) revealed the bulk of the database is not the conversation data the user thinks they are keeping:

Object	Disk (GiB)	% of DB	Rows
event	36.4	78%	986,972
message	5.8	12%	218,982
part	3.8	8%	991,763
all indexes	0.3	<1%	—
everything else	0.05	<1%	—

True byte measurement (length(CAST(data AS BLOB))): event = 38.6 GiB, message = 6.2 GiB, part = 3.7 GiB. The event table alone is ~82% of the file.

Root Cause

The event table is a write-audit / event-sourcing log that embeds a full copy of the JSON payload on every mutation:

• 729K message.part.updated.1 events — each embeds the entire part's data
• 199K message.updated.1 events — each embeds the entire message
• 55K session.updated.1 + 1.8K session.created.1 events

So event ≈ 38.6 GiB of JSON that duplicates essentially all of message + part content (~9.2 GiB) ~4x over. The database stores the state and a full copy of every change to that state.

Critical finding: all 1,786 event-attached sessions were created in the last ~3 weeks (2026-07-09 → 2026-08-02). The event table is recent data — it is growing at ~1.7 GiB/day of active use, not a historical backlog. Old sessions (>90 days) have message/part data but zero event rows.

Additional context:

• session_message table is empty (modern schema uses message + part); the Go report tool initially targeted the wrong table.
• freelist_count = 0 → VACUUM alone reclaims nothing; only row deletion + VACUUM can shrink the file.
• The storage/ file-based directory (2.5 GiB) duplicates part/message data too, but is a separate concern.

Solution

Part A — Replace the broken diagnostic script (DONE)

Rewrite opencode-db-report as a Go binary (installed at ~/.local/bin/opencode-db-report, Python original backed up as .py.bak):

• Quick mode (default): completes in <1s — metadata, row counts, indexed/bounded queries only.
• --deep: opts into dbstat page walk + global ORDER BY length(data) scans (with warnings).
• --time: age-bucket analysis (<30d / 30-90d / >90d) for session, message, part.
• Per-query timeout via context cancellation (interrupts stuck queries, continues to next section).
• Output streamed section-by-section to a temp file (nothing held in memory, partial results survive interrupts).
• Strictly read-only (mode=ro + PRAGMA query_only); never touches OpenCode processes.
• Progress lines to stderr; section timings table; clipboard copy via wl-copy/xclip/xsel; report path always printed.

Part B — Reduce the database (NOT yet executed — requires explicit approval)

Tier 0 (safest, manual): opencode session delete <id> per session — the official transaction-safe CLI (PRAGMA foreign_keys=ON + cascade handles message/part/todo/session_input/event_sequence).

Tier 1 (batched): same CLI in a loop over the 3,150 old sessions.

Tier 2 (copy-then-prune, what I'd actually do):

1. Backup: .backup the live DB first (needs ~47 GiB free; 52 GiB available — tight).
2. On the copy only: PRAGMA foreign_keys=ON; BEGIN; DELETE FROM session WHERE time_updated < now-90d; COMMIT; — cascade cleans children.
3. VACUUM the copy; PRAGMA integrity_check; verify OpenCode loads it.
4. Swap the live DB only with OpenCode stopped.

Reality check on reclaim: deleting the 3,150 sessions >90 days reclaims 86,789 messages (~490 MiB) + 347,146 parts (~1.27 GiB) + 0 events ≈ ~1.75 GiB. The 36 GiB event table is recent data and is NOT touched by old-session deletion. The structural fix for the event table is upstream (OpenCode storage design) — file a separate upstream issue about event payload duplication.

Part C — Session intelligence for flow & skills (read-only)

Analyze all sessions to improve workflow and write skills:

1. Cluster session titles by topic (done: fix/debug ~168, github/PR 109, test 81, config/setup ~81, refactor/migrate 50, opencode-tooling 70, learn 3).
2. Measure per-session tokens/cost from session table (metadata only) → find where time/money goes.
3. Count tool-call usage from part metadata → standardize dominant tools in skills.
4. Map to existing ~100 skills → identify gaps (debugging workflow and env-setup look under-served).

User Stories

1. As a user, I want opencode-db-report to produce a report in tens of seconds, so that I am not blocked by a hung script.
2. As a user, I want progress output during the report, so that I can see which section is running.
3. As a user, I want a per-query timeout, so that a stuck query cannot hang the whole run.
4. As a user, I want expensive analysis (dbstat, full scans) behind --deep, so that the default run is fast.
5. As a user, I want age-bucket analysis of data (<30d / 30-90d / >90d), so that I know how much data is old.
6. As a user, I want the report streamed to a file, so that partial results survive interruption.
7. As a user, I want to know exactly where the 47 GiB is, so that I can decide what to reclaim.
8. As a user, I want a safe, official path to delete old sessions, so that I do not risk corrupting a live database.
9. As a user, I want a backup + copy + verify workflow before any deletion, so that the live DB is never touched until proven safe.
10. As a user, I want an honest estimate of what deletion reclaims, so that I do not over-invest in low-yield cleanup.
11. As a user, I want to understand the event-table duplication problem, so that I can file the right upstream issue.
12. As a user, I want a session-intelligence report (tokens, cost, tool usage), so that I can target skills where I actually spend effort.
13. As a user, I want a ranked skill-gap list, so that I can write skills that improve my real workflow.
14. As a user, I want all analysis to remain read-only, so that the database is never mutated accidentally.

Implementation Decisions

• Language: Go (1.24) with mattn/go-sqlite3 driver; build with CGO_CFLAGS="-DSQLITE_ENABLE_DBSTAT_VTAB" for dbstat support.
• Read-only enforcement: DSN file:...?mode=ro&_query_only=1&_busy_timeout=5000 + PRAGMA query_only = ON; single connection (SetMaxOpenConns(1)).
• Timeout mechanism: context cancellation via QueryContext; driver calls sqlite3_interrupt() on deadline; timed-out optional sections are skipped, not fatal.
• Streaming: report sections written to os.TempDir()/opencode-db-report-*.txt as produced; file handle stays open; flush after each section.
• Quick-mode bounded queries: recent-window indexed filters (message.time_created) and session-count-bounded part scans instead of global top-N.
• Schema reality: modern OpenCode stores payload in message + part; session_message is empty and should be skipped; event aggregation must join event.aggregate_id → session.id.
• Flags: --quick (default), --deep, --time, --timeout SECONDS, --limit N, --print, --window-days N.
• Deletion: prefer official opencode session delete; SQL fallback only on a copy with FK cascade, never on the live DB without backup + verify + OpenCode stopped.

Testing Decisions

• Verify quick mode completes in <5s on the 47 GiB DB with a bounded timeout.
• Verify --time produces correct age buckets against known row counts.
• Verify a timed-out optional section (e.g., dbstat with 1s timeout) is skipped and the run continues.
• Verify read-only: no write operations observed on the DB/WAL while the tool runs.
• Verify report file exists + path printed even when clipboard copy fails.
• For deletion: verify integrity on the copy before swap; verify OpenCode loads the reduced DB.

Out of Scope

• Executing any DELETE / VACUUM / wal_checkpoint on the live database (requires explicit approval).
• Fixing the upstream OpenCode event-table storage duplication (separate upstream issue).
• Migrating or pruning the legacy storage/ file-based directory (2.5 GiB).
• Reading or printing private message content — metadata only.

Further Notes

• The Go source lives at /tmp/opencode-db-report-go/main.go and is mirrored to ~/.local/share/opencode/tools/opencode-db-report.go.
• The 3,150 sessions >90 days are enumerated at /tmp/sessions_90d.txt (id|title|created|updated).
• Monthly distribution of old sessions: 2026-01: 1,374 · 2026-04: 568 · 2026-03: 531 · 2026-02: 395 · 2026-05: 159 · 2025-09..12: 123.
• The event table growth (~1.7 GiB/day) means this problem will recur without an upstream fix.