storage: opencode.db at ~687 MB with 77.9% of the file in a single session.revert cell, and desktop drafts.sqlite at 153 MB that is 99.5% free pages

Description

On OpenCode Desktop 2.0.28 the two local SQLite stores have grown to 840 MB combined, and the largest single contributor is not the one the existing growth reports describe. I measured both files read-only (sqlite3 with file:...?mode=ro, no writes, no VACUUM) and located the payload at column level.

File sizes as measured on 2026-10-14

file	bytes
`C:\Users\zkfy\.local\share\opencode\opencode.db`	720,060,416	686.70 MiB
`opencode.db-wal`	10,184,672	9.71 MiB
`opencode.db-shm`	32,768
`C:\Users\zkfy\AppData\Roaming\ai.opencode.desktop\drafts.sqlite`	160,628,736	153.19 MiB
`drafts.sqlite-wal`	4,161,232	3.97 MiB
`drafts.sqlite-shm`	32,768

Environment: Windows 10.0.19045.6466, win32 x64, OpenCode Desktop 2.0.28, channel latest (Electron app, @opencodedesktop). Usage is moderate — many sessions across several projects (e.g. E:\workspace\istic-detection-project, E:\workspace\open-mining-platform).

Finding 1 — 77.9% of `opencode.db` is one cell in the `session` table

PRAGMA page_size=4096, page_count=175796, freelist_count=2275 (1.3%), auto_vacuum=0, journal_mode=wal. The database is almost entirely live data, not slack.

Per-column byte totals, measured with SUM(LENGTH(CAST(<col> AS BLOB))):

table	rows	dominant column	bytes	MiB	% of file
`session`	57	`revert`	560,856,932	534.87	77.9%
`session_message`	5,207	`data`	56,022,330	53.43	7.8%
`part`	17,013	`data`	51,005,000	48.64	7.1%
`message`	4,426	`data`	20,737,630	19.78	2.9%
`kv`	4	`value`	6,337,707	6.04	0.9%
`event`	0	—	0	0	0%

The revert column is the whole story. Over the session table:

SUM(LENGTH(CAST(revert AS BLOB))) = 560,856,932
MAX(LENGTH(CAST(revert AS BLOB))) = 560,856,932 — identical, i.e. one single row holds 100% of the column.
The second-largest session row in the entire table is 1,370 bytes. There is no gradual growth curve; it is one pathological cell.
That cell is 456,718,787 characters of UTF-8 (ratio 1.228 bytes/char, consistent with CJK content).

The offending row:

id           ses_ed1de7238ffe0O3DK29NbzPyfx
directory    E:/workspace/istic-detection-project
slug         lucky-harbor
title        调整systemTypeLatest日本科技组织称谓
version      1.19.0
agent        Sisyphus - ultraworker
model        space-bunny-free / opencode
tokens_input 131508   tokens_output 14624
tokens_cache_read 2614629

and revert begins:

{"messageID":"msg_12f534640001oNuae1v9ic6Nvi","snapshot":"9408827d2503e669c200b0ea65501610e04954ea","diff":"diff --git a...

So the revert column embeds a full unified diff as part of the session's undo/rollback state. At 535 MB that is not undo metadata; it is a serialised copy of a worktree diff living in a single row.

Worth noting for 2.0: the V2 table does not carry this. session_v2.revert totals 0 bytes across 59 rows, and session_v2 as a whole is 19 KB. The 535 MB is stranded in the legacy V1 session table while V2 state sits alongside it — so the bulk is unreachable via V2's session model but still occupies 78% of the database file.

Finding 2 — `drafts.sqlite` is 153 MB holding 0.81 MB of data

This is the desktop app's own Electron-layer store, not the core DB.

page_size=4096  page_count=39216  freelist_count=39008  auto_vacuum=0 (NONE)
file       153.19 MiB
freelist   152.38 MiB   (99.47% of the file)
live data    0.81 MiB   ( 0.53% of the file)

188x more file than live content. Actual current contents:

table	rows	value bytes
`state`	249	47,128
`document`	283	58,103
`blob`	8	550,800

document keys are overwhelmingly per-entity rows that accumulate: ~150 opencode.draft.<uuid>.<suffix> keys plus opencode.workspace.<hash>.<suffix>.dat:session:<id> keys, alongside opencode.global.dat:prompt-history.

With auto_vacuum=0, deleted rows free pages that are reused but never returned to the filesystem, so the file stays pinned at its high-water mark forever.

I confirmed what occupied the freed pages with a read-only raw page scan (opening the file 'rb'; no SQLite write of any kind). Sampling 287 pages spread across the file recovered deleted content that is unmistakably composer drafts:

page@20000 : "slop":0,"zero_terms_query":"NONE","boost":1.0}}},{"match_phrase":{"videoTitle":{"query":
page@35000 : "zero_terms_query":"NONE","boost":1.0}}},{"match_phrase":{"title":{"query":
page@5000  : 2026-09-21 09:42:32 [pool-14508-thread-1] INFO c.z.n.s.i.NewsDataHandleTaskServiceImpl - consultants_news

i.e. large pasted payloads (an Elasticsearch query document and Java/Spring log output) were held in drafts, then deleted. The space was never reclaimed.

So drafts.sqlite is not accumulating a lot of data — it is holding onto one large transient burst forever. Deleting the app data or running a VACUUM would return ~152 MB, but nothing in the product appears to do that.

Plugins

~/.config/opencode/opencode.jsonc → "plugin": ["@tarquinen/opencode-dcp@latest"]
~/.config/opencode/cli.json → "plugins": ["@tarquinen/opencode-dcp@latest"]
~/.config/opencode/tui.json → "plugin": ["oh-my-openagent@latest", "@tarquinen/opencode-dcp@latest"]

OpenCode version

2.0.28 (channel latest, OpenCode Desktop for Windows)

Steps to reproduce

Not reproducible in a single deterministic step — this report does not include a synthetic reproduction, and I am not going to invent one. Both stores grow as a by-product of ordinary use rather than from a fixed trigger, and I do not yet know the exact code path that produces the 535 MB revert payload.

What I can state factually:

opencode.db accumulates the session.revert payload as sessions run; on this machine exactly one session carries it, and the other 56 sessions total under 20 KB combined. So it is not "per-session cost", it is a single outlier.
drafts.sqlite reaches a high-water mark when large text is pasted into the composer, and then never shrinks regardless of subsequent activity.

To locate the growth trigger I would need to correlate the oversized session (ses_ed1de7238ffe0O3DK29NbzPyfx) with what its tools wrote into E:\workspace\istic-detection-project — that session's turn history is intact, so a targeted reproduction should be feasible once someone instruments it.

Operating System

Windows 10.0.19045.6466 (win32 x64)

Terminal

Not applicable — desktop (Electron) application, not a terminal client. Database was inspected externally with the Python standard library sqlite3 module in read-only mode.

Additional Context

Measured vs. assumed

Measured (read-only, this machine):

Every byte count in the two tables above, via SUM/MAX(LENGTH(CAST(col AS BLOB))) and PRAGMA counters.
event contains 0 rows in this database, so it contributes nothing here.
session_v2.revert totals 0 bytes across 59 rows.
drafts.sqlite freelist_count=39008 of page_count=39216, auto_vacuum=0.
The deleted draft content recovered by raw page scan.

Not verified — explicitly assumptions:

Why that one session's revert diff reached 535 MB. I did not inspect the session's turn history or the workspace files that produced the diff, so I cannot say whether it came from one enormous file, many files, or a diff computed against a pathological tree.
Whether the revert payload is rewritten in full on every update (which would make it quadratic like the message.updated.1 path) or written once. The current state only shows the final value; no history survives to distinguish the two.
Which code path serialises diff into session.revert. I have not read the source to confirm this, and snapshot.diffFull uses context: Number.MAX_SAFE_INTEGER per reports elsewhere in this tracker — I have not verified that applies here.
Which action caused the drafts.sqlite high-water mark. The deleted content is recovered, but not the sequence of events that wrote and then removed it.

Related issues (checked, not duplicates)

The open growth reports all target the event table, which is empty (0 rows) in this database, so none of them describes this instance:

Unbounded growth of the event table: opencode.db reaches 13GB+, mostly message.updated.1 snapshots (no retention/compaction) #33356 — event table unbounded growth (open, labelled 2.0)
Excessive storage: event table stores full message snapshots per streaming update — community tool available #41175 — event table stores full message snapshots per streaming update
v2: define snapshot retention and disk GC policy #36093 — v2 snapshot retention / disk GC policy
2.0: Scope event streams and bound event payloads #36441 — 2.0: scope event streams and bound event payloads
[FEATURE]: Session Lifecycle Management — unified storage reclamation, auto-archival, and child session cleanup #16101 — session lifecycle management / storage reclamation
New and incremental core databases do not reclaim deleted pages during their lifecycle #47591 — core databases do not reclaim deleted pages; this is about the core DB layer (Database.layerFromPath), not the Electron desktop drafts.sqlite measured above
[FEATURE]: V2 offline SQLite inspection, verified backups and vacuum #54618 — request for user-facing VACUUM / offline SQLite inspection

The only prior report I found that documents a multi-hundred-MB session.revert value is #38362, which recorded LENGTH(revert) = 423,582,308 on 1.18.4. That issue is closed (auto-closed after 60 days of inactivity, reason NOT_PLANNED) and was framed as a V8 OOM-on-startup crash whose primary cause it identified as the event table, with revert as a secondary observation. This report differs in three ways: it is 2.0.28, the event table is empty so the OOM framing does not apply, and the revert column is still present and still unbounded in the V2 schema layout.

Suggested remedies (not implementation proposals, just the shape of the fix)

Cap or stop inlining the diff into session.revert. A snapshot hash already identifies the tree state, and the value is already separately serialised in the row — the diff text appears to be redundant persistence of derivable state. If it must be retained, it belongs in its own row with a size bound, not inline in the session row.
Drop or migrate the legacy V1 session table on 2.0 upgrade. 534.87 MiB of this database is unreachable from the V2 session model, so a one-time migration would reclaim ~78% of the file with no loss of V2-visible state.
Give drafts.sqlite auto_vacuum=INCREMENTAL and run a periodic incremental_vacuum. It is an Electron-layer store holding 0.81 MiB of live data in a 153.19 MiB file.
Reclaim on user-facing milestones — e.g. after deleting a session/draft batch, or when freelist exceeds a fraction of the file. Without an exposed maintenance path the user has no recourse.

Diagnostic SQL used (all read-only):

SELECT SUM(LENGTH(CAST(revert AS BLOB))), MAX(LENGTH(CAST(revert AS BLOB))) FROM session;
PRAGMA page_size; PRAGMA page_count; PRAGMA freelist_count; PRAGMA auto_vacuum;
SELECT name, SUM(pgsize) FROM dbstat GROUP BY name ORDER BY 2 DESC;  -- dbstat unavailable in this build

Note: the dbstat virtual table was not available in the Python sqlite3 build used here, so per-table page attribution above was derived from per-column payload sums rather than dbstat. Column sums reconcile to 662.77 MiB of a 686.70 MiB file, with the ~23.9 MiB remainder accounted for by indexes, small columns, and the 8.9 MiB freelist.

Thanks for the careful measurements. I could reproduce the drafts.sqlite part on 2.0.28, and it still happens on the latest dev (6a7cedc).

The script below runs the desktop app's own drafts storage code. It saves a ~100 MB draft, clears it, then reopens the store a few times. After the draft is cleared the file stays at 102 MiB: freelist_count is 26148 of 26161 pages, auto_vacuum is 0 and there is no live data left. It never shrinks after reopening either. The store is opened without auto_vacuum, and nothing runs a vacuum, so the file keeps its largest size.

About the 535 MB session.revert cell: that row was written by v1 (its version is 1.19.0). v2's revert state has no diff field, so v2 doesn't produce this growth. In v2 it's leftover data in the legacy v1 tables, which these issues already cover:

session: delete leaves pre-V2 legacy rows (session/message/part) orphaned in the database #50260: deleting a session leaves the legacy session/message/part rows behind
[FEATURE]: V2 offline SQLite inspection, verified backups and vacuum #54618: offline inspection, vacuum and cleanup of legacy tables
[FEATURE]: Session Lifecycle Management — unified storage reclamation, auto-archival, and child session cleanup #16101: reclaiming storage over a session's lifetime

Reproduction script (drafts-freelist.ts)

// Reproduces #55282 (finding 2): the desktop drafts store never gives freed pages back.
// Run from a checkout of anomalyco/opencode (after `bun install`):
//   REPO=/path/to/opencode bun /workspace/repro/drafts-freelist.ts
import { mkdtempSync, statSync } from "node:fs"
import { tmpdir } from "node:os"
import { join } from "node:path"
import { DatabaseSync } from "node:sqlite"

const repo = process.env.REPO ?? "/workspace/repo"
const { openDatabase } = await import(join(repo, "packages/desktop/src/main/storage/database.ts"))
const { createDraftStore } = await import(join(repo, "packages/desktop/src/main/storage/drafts.ts"))

const file = join(mkdtempSync(join(tmpdir(), "drafts-")), "drafts.sqlite")
const stats = (label: string) => {
  const db = new DatabaseSync(file)
  db.exec("PRAGMA wal_checkpoint(TRUNCATE)")
  const get = (p: string) => Object.values(db.prepare(`PRAGMA ${p}`).get() as object)[0]
  const live = db.prepare("SELECT COALESCE(SUM(LENGTH(value)),0) AS n FROM document").get() as { n: number }
  console.log(
    `${label.padEnd(34)} file=${(statSync(file).size / 2 ** 20).toFixed(2)} MiB page_count=${get("page_count")} freelist=${get("freelist_count")} auto_vacuum=${get("auto_vacuum")} live_document_bytes=${live.n}`,
  )
  db.close()
}

// 1. Desktop opens the store, the user pastes a large payload (~100 MB of log text) into the composer.
let handle = openDatabase(file)
let store = createDraftStore(handle.db, { collectDelay: 60_000 })
const big = "2026-09-21 09:42:32 [pool-14508-thread-1] INFO c.z.n.s.i.NewsDataHandleTaskServiceImpl - consultants_news\n".repeat(1_000_000)
store.set("opencode.draft.00000000-0000-0000-0000-000000000000.prompt", JSON.stringify({ text: big }))
await store.flush()
store.close()
handle.close()
stats("after large draft saved")

// 2. The draft is cleared (sent / discarded).
handle = openDatabase(file)
store = createDraftStore(handle.db, { collectDelay: 60_000 })
store.set("opencode.draft.00000000-0000-0000-0000-000000000000.prompt", null)
await store.flush()
store.close()
handle.close()
stats("after draft cleared")

// 3. App restarts (several times): nothing ever reclaims the space.
for (let i = 0; i < 3; i++) {
  handle = openDatabase(file)
  store = createDraftStore(handle.db, { collectDelay: 60_000 })
  store.close()
  handle.close()
}
stats("after 3 restarts")

Run it: bun drafts-freelist.ts

I'd like to work on this.

I read the source rather than taking the summary on trust, and I agree with the triage: the session.revert half is v1 leftover (version 1.19.0, and v2's revert state has no diff field), so the actionable half is the drafts store.

Confirmed in packages/desktop/src/main/storage/database.ts: openDatabase sets journal_mode=WAL; synchronous=NORMAL; temp_store=MEMORY and nothing else. There is no auto_vacuum anywhere in the desktop or core sources, and no VACUUM is run anywhere, so every store opened through it — drafts.sqlite and state.sqlite alike — keeps the high-water mark of its largest content. collectBlobs in drafts.ts deletes orphaned blobs, and the freed pages stay in the file.

Plan:

auto_vacuum=INCREMENTAL for the store, adopted on open. SQLite only applies the mode to a file that was vacuumed after the pragma, so this is PRAGMA auto_vacuum=INCREMENTAL followed by VACUUM when the file still reports mode 0 — a no-op in size for a fresh database, and a one-time reclaim for a file that already has free pages, which is the case in this report.
PRAGMA incremental_vacuum after collectBlobs' delete, so pages return to the filesystem when the app drops drafts instead of waiting for a rewrite.

One consequence worth a maintainer's word before I finish it: step 1 rewrites the file once, at the first open after the change. For the 153 MB file in this report that is a one-time cost the user is asking for, but it is startup work on a path every desktop store goes through. If you would rather keep first-open cheap, I will drop the VACUUM and only set the mode for databases created from now on, leaving existing files to whatever cleanup path #54618 ends up providing.

I'll share the diff and the measurements from the drafts-freelist scenario once the tests pass.

Navigation Menu

Repository navigation

storage: opencode.db at ~687 MB with 77.9% of the file in a single session.revert cell, and desktop drafts.sqlite at 153 MB that is 99.5% free pages #55282

Description

File sizes as measured on 2026-10-14

Finding 1 — 77.9% of `opencode.db` is one cell in the `session` table

Finding 2 — `drafts.sqlite` is 153 MB holding 0.81 MB of data

Plugins

OpenCode version

Steps to reproduce

Operating System

Terminal

Additional Context

Measured vs. assumed

Related issues (checked, not duplicates)

Suggested remedies (not implementation proposals, just the shape of the fix)

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

storage: opencode.db at ~687 MB with 77.9% of the file in a single session.revert cell, and desktop drafts.sqlite at 153 MB that is 99.5% free pages #55282

Description

Description

File sizes as measured on 2026-10-14

Finding 1 — 77.9% of opencode.db is one cell in the session table

Finding 2 — drafts.sqlite is 153 MB holding 0.81 MB of data

Plugins

OpenCode version

Steps to reproduce

Operating System

Terminal

Additional Context

Measured vs. assumed

Related issues (checked, not duplicates)

Suggested remedies (not implementation proposals, just the shape of the fix)

Activity

opencode-agent commented on Oct 14, 2026

argszero commented on Oct 14, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Finding 1 — 77.9% of `opencode.db` is one cell in the `session` table

Finding 2 — `drafts.sqlite` is 153 MB holding 0.81 MB of data