Description

opencode.db grows to ~9 GB due to enormous message.updated.1 event snapshots

Summary

OpenCode's SQLite database can grow to several gigabytes because message.updated.1 events appear to store very large snapshots of message state, including summary.diffs.

In my case, the database reached 8.9 GB, with approximately 7.4 GB coming from message.updated.1 events alone.

The largest individual message.updated.1 event is approximately 458 MB.

This appears particularly problematic when a message's summary.diffs contains a large patch. The event payload seems to contain the entire message state, including the accumulated diff, and repeated message.updated.1 events consequently duplicate increasingly large payloads.

Environment

• OS: Linux
• Database: SQLite
• Database location: ~/.local/share/opencode/opencode.db
• Database size: 8.9 GB
• WAL size at time of investigation: 924 MB
• Sessions: 565
• Messages: 43,121
• Parts: 178,923
• Events: 362,893

The problem was discovered because the OpenCode data directory had grown to approximately 9.8 GB.

Directory contents at the time:

auth.json             322 B
bin/                   64 B
log/                  486 B
opencode.db          8.9G
opencode.db-shm       32K
opencode.db-wal      924M
repos/
snapshot/
storage/
tool-output/

Database schema

The relevant tables are:

CREATE TABLE `message` (
    `id` text PRIMARY KEY,
    `session_id` text NOT NULL,
    `time_created` integer NOT NULL,
    `time_updated` integer NOT NULL,
    `data` text NOT NULL,
    CONSTRAINT `fk_message_session_id_session_id_fk`
      FOREIGN KEY (`session_id`)
      REFERENCES `session`(`id`)
      ON DELETE CASCADE
);

CREATE TABLE `part` (
    `id` text PRIMARY KEY,
    `message_id` text NOT NULL,
    `session_id` text NOT NULL,
    `time_created` integer NOT NULL,
    `time_updated` integer NOT NULL,
    `data` text NOT NULL,
    CONSTRAINT `fk_part_message_id_message_id_fk`
      FOREIGN KEY (`message_id`)
      REFERENCES `message`(`id`)
      ON DELETE CASCADE
);

CREATE TABLE `event` (
    `id` text PRIMARY KEY,
    `aggregate_id` text NOT NULL,
    `seq` integer NOT NULL,
    `type` text NOT NULL,
    `data` text NOT NULL,
    CONSTRAINT `fk_event_aggregate_id_event_sequence_aggregate_id_fk`
      FOREIGN KEY (`aggregate_id`)
      REFERENCES `event_sequence`(`aggregate_id`)
      ON DELETE CASCADE
);

Database size breakdown

I measured the actual data payload stored in the relevant tables:

SELECT
  'message' AS table_name,
  COUNT(*) AS rows,
  ROUND(SUM(LENGTH(data)) / 1024.0 / 1024.0, 2) AS data_mb
FROM message

UNION ALL

SELECT
  'part',
  COUNT(*),
  ROUND(SUM(LENGTH(data)) / 1024.0 / 1024.0, 2)
FROM part

UNION ALL

SELECT
  'event',
  COUNT(*),
  ROUND(SUM(LENGTH(data)) / 1024.0 / 1024.0, 2)
FROM event;

Result:

table_name       rows       data_mb
-------------------------------------
message          43121       541.22
part            178923       398.95
event           362893      7784.94

This means approximately 7.8 GB of the database payload is in the event table.

The actual message + part data is only approximately 940 MB.

Event type breakdown

I then grouped the events by type:

SELECT
  type,
  COUNT(*) AS events,
  ROUND(SUM(LENGTH(data))/1024.0/1024.0, 2) AS MB,
  ROUND(MAX(LENGTH(data))/1024.0/1024.0, 2) AS max_MB
FROM event
GROUP BY type
ORDER BY SUM(LENGTH(data)) DESC;

Result:

type                              events       MB       max_MB
----------------------------------------------------------------
message.updated.1                104301     7395.24     458.18
message.part.updated.1           229217      371.85       4.97
session.updated.1                  28744       17.65       0.0
session.created.1                    319        0.17       0.0
message.removed.1                   277        0.02       0.0
session.next.model.switched.1        31        0.01       0.0
session.next.agent.switched.1         4        0.0        0.0

The overwhelming majority of the event storage is therefore:

message.updated.1
~7.4 GB

There are only 104,301 such events, yet they consume over 7 GB.

Extremely large individual events

I queried the largest event.data records:

SELECT
  id,
  type,
  LENGTH(data) AS bytes,
  substr(data, 1, 1000) AS beginning
FROM event
WHERE id = 'evt_060ebe9ca001cHOjna70i26Ao9';

Result:

id:
evt_060ebe9ca001cHOjna70i26Ao9

type:
message.updated.1

bytes:
480439717

That is approximately:

458.18 MiB

for a single event record.

The beginning of the JSON looks like:

{
  "sessionID": "ses_f9f1f666cffe3bH37Y9IIwPUVY",
  "info": {
    "id": "msg_060e601d50013bYt7bQsQhnJrB",
    "sessionID": "ses_f9f1f666cffe3bH37Y9IIwPUVY",
    "role": "user",
    "time": {
      "created": 1788332081621
    },
    "summary": {
      "diffs": [
        {
          "file": ".venv/bin/Activate.ps1",
          "patch": "Index: ..."
        }
      ]
    }
  }
}

The important part is:

info.summary.diffs[].patch

The message snapshot contains the diff/patch data.

Largest part records

For comparison, I queried the largest part records:

SELECT
  id,
  ROUND(LENGTH(data) / 1024.0 / 1024.0, 2) AS MB
FROM part
ORDER BY LENGTH(data) DESC
LIMIT 20;

The largest records were:

prt_060f17fc1001RMyCPejTFLvVQC    4.97 MB
prt_060eb9510001S64mIdgt8vQCrV    4.73 MB
prt_03754c2040016XAJpwzbTKzhGU    0.80 MB
prt_0384c55ca001Gc7uuV7P3WzQpE    0.48 MB
...

So the part table does contain relatively large records, but its total size is only approximately 399 MB.

This reinforces that the primary storage problem is the event table, specifically message.updated.1.

Observed behavior

The apparent sequence is:

1. A message contains a summary.diffs structure.
2. The diff contains potentially large patches.
3. The message is updated.
4. A message.updated.1 event stores the updated message state.
5. Subsequent updates store another complete message snapshot.
6. If the message's diff grows or remains embedded in the message state, increasingly large copies of that data are written to the event log.

This can result in repeated storage of the same large diff.

For example, individual events in my database include:

458.18 MB
458.18 MB
458.18 MB
458.18 MB

325.83 MB
325.82 MB
325.82 MB
325.82 MB
325.82 MB
325.82 MB
...

This strongly suggests that large message snapshots are being repeatedly persisted rather than storing only the changed fields/delta.

Why this is problematic

This has several consequences:

• OpenCode's local data directory can unexpectedly consume many gigabytes.
• SQLite database operations become increasingly expensive.
• Backups of the OpenCode database become unnecessarily large.
• Database maintenance such as VACUUM requires substantial temporary disk space.
• Users may not realize that historical event data is responsible for most of the storage.
• A single pathological diff can potentially result in hundreds of MB being duplicated across many events.

In this case:

~940 MB

is the combined message + part payload, while:

~7.8 GB

is the event payload.

The database therefore contains roughly 8× more event payload than actual message/part payload.

WAL observation

At the time of investigation:

opencode.db      8.9 GB
opencode.db-wal  924 MB

I ran:

PRAGMA journal_mode;
PRAGMA wal_checkpoint(PASSIVE);

The result was:

journal_mode = wal

busy = 0
log = 7
checkpointed = 7

Therefore the large database size is not simply caused by an uncheckpointed WAL.

The primary issue is persistent data in the event table itself.

Suspected root cause

The likely root cause is that message.updated.1 events contain a complete serialized message object, including potentially enormous summary.diffs data.

If a large diff is present in the message summary, repeatedly emitting full message.updated.1 snapshots causes storage to grow approximately with:

number of updates × size of message snapshot

and can become extremely expensive for large diffs.

In the worst case, this can approach quadratic growth when the serialized message state itself grows over time.

Possible solutions

Some possible approaches:

1. Avoid storing complete message snapshots in every update event

Instead of:

message.updated
    -> complete message JSON

consider storing only the changed fields or a compact event representation.

2. Avoid embedding large diffs in repeatedly emitted message snapshots

Large patch data should perhaps be stored separately or referenced by ID rather than duplicated in every message-update event.

3. Add event retention/compaction

Historical message.updated events may not need to be retained indefinitely if the current message/part projections already contain the required state.

A retention policy or event compaction mechanism could prevent unbounded growth.

4. Put a size limit on event payloads

At minimum, an individual event containing hundreds of megabytes should probably trigger a warning or be handled differently.

A single:

480,439,717 byte

event is a strong indication that something has gone wrong.

5. Avoid repeatedly serializing unchanged large fields

If summary.diffs has not changed, it should not need to be serialized into every subsequent message.updated.1 event.

Reproduction / investigation commands

These commands can be used to diagnose the issue on another installation.

Database size

ls -lh ~/.local/share/opencode/opencode.db*

Tables

sqlite3 ~/.local/share/opencode/opencode.db '.tables'

Row counts

sqlite3 ~/.local/share/opencode/opencode.db <<'SQL'
SELECT 'message', COUNT(*) FROM message
UNION ALL
SELECT 'part', COUNT(*) FROM part
UNION ALL
SELECT 'event', COUNT(*) FROM event
UNION ALL
SELECT 'session_message', COUNT(*) FROM session_message
UNION ALL
SELECT 'session', COUNT(*) FROM session
UNION ALL
SELECT 'todo', COUNT(*) FROM todo;
SQL

Data size by table

sqlite3 ~/.local/share/opencode/opencode.db <<'SQL'
SELECT
  'message' AS table_name,
  COUNT(*) AS rows,
  ROUND(SUM(LENGTH(data)) / 1024.0 / 1024.0, 2) AS data_mb
FROM message

UNION ALL

SELECT
  'part',
  COUNT(*),
  ROUND(SUM(LENGTH(data)) / 1024.0 / 1024.0, 2)
FROM part

UNION ALL

SELECT
  'event',
  COUNT(*),
  ROUND(SUM(LENGTH(data)) / 1024.0 / 1024.0, 2)
FROM event;
SQL

Event type size

sqlite3 ~/.local/share/opencode/opencode.db <<'SQL'
SELECT
  type,
  COUNT(*) AS events,
  ROUND(SUM(LENGTH(data))/1024.0/1024.0, 2) AS MB,
  ROUND(MAX(LENGTH(data))/1024.0/1024.0, 2) AS max_MB
FROM event
GROUP BY type
ORDER BY SUM(LENGTH(data)) DESC;
SQL

Largest message-update events

sqlite3 ~/.local/share/opencode/opencode.db <<'SQL'
SELECT
  id,
  type,
  ROUND(LENGTH(data) / 1024.0 / 1024.0, 2) AS MB
FROM event
WHERE type = 'message.updated.1'
ORDER BY LENGTH(data) DESC
LIMIT 20;
SQL

Expected behavior

OpenCode's local database should not grow to multiple gigabytes from ordinary session activity, particularly due to repeated serialization of the same large message/diff data.

Large diffs should not cause hundreds-of-megabytes message-update events to be generated repeatedly.

Actual behavior

A single OpenCode installation accumulated:

Database:                  8.9 GB
Event payload:             7.78 GB
message.updated events:    7.40 GB
Largest event:             458.18 MB

with the largest event containing summary.diffs[].patch data.

Impact

Severity: High for local storage consumption

This can silently consume many gigabytes of disk space and is particularly problematic for users working with repositories containing large generated files, virtual environments, build artifacts, or large diffs.

The issue can also make the database increasingly difficult to back up, inspect, migrate, or compact.

Additional note

The specific large diff observed included:

.venv/bin/Activate.ps1

This may indicate that generated/environment files are being included in the diff summary. Even if the underlying large diff is expected, repeatedly persisting hundreds-of-megabytes of that diff inside message.updated.1 events appears to be the larger architectural/storage issue.

The main concern is therefore not simply that a large diff exists, but that the same large serialized message state appears to be retained repeatedly in the event log.

Plugins

nill

OpenCode version

1.18.26

Steps to reproduce

1. Start OpenCode in a Git repository containing a large generated/environment directory or otherwise large diff, for example a .venv/ directory.
2. Make OpenCode inspect or modify the repository so that the session's message summary.diffs contains a large patch.
3. Continue the same session with multiple message/tool updates.
4. Inspect the SQLite database:

ls -lh ~/.local/share/opencode/opencode.db*

5. Check the event payload sizes:

sqlite3 ~/.local/share/opencode/opencode.db <<'SQL'
SELECT
  type,
  COUNT(*) AS events,
  ROUND(SUM(LENGTH(data))/1024.0/1024.0, 2) AS MB,
  ROUND(MAX(LENGTH(data))/1024.0/1024.0, 2) AS max_MB
FROM event
GROUP BY type
ORDER BY SUM(LENGTH(data)) DESC;
SQL

6. Observe that message.updated.1 can contain repeated large snapshots of the same message state, including summary.diffs[].patch.

In my case, this resulted in:

message.updated.1    104,301 events    7,395.24 MB    max 458.18 MB

with individual message.updated.1 records reaching approximately 458 MB.

The database grew to 8.9 GB, with the event table accounting for approximately 7.8 GB of payload.

Screenshot and/or share link

No response

Operating System

CachyOS x86_64 (Kernel: Linux 7.2.2-1-cachyos)

Terminal

zsh