[Bug] SQLite UPSERT into part table fails during step-finish event projection

[irfndi]

Description

OpenCode crashed while persisting a step-finish event to the part table.

Failing SQL

insert into "part" ("id", "message_id", "session_id", "time_created", "time_updated", "data")
values (?, ?, ?, ?, ?, ?)
on conflict ("part"."id")
do update set "time_updated" = ?, "data" = ?

Params from the failure

placeholder	value
id	prt_eb9e9419c00129b4Qayl09Jh29
message_id	msg_eb9e8af5a001VQwRWtmki2gBkj
session_id	ses_14f0f5449ffehYzJNptQTs6Jnl
time_created	1781235532189
time_updated	1781235532193
data	{"reason":"tool-calls","snapshot":"8a393ddff176c8c0f2d8149f9074495b7d5dbd1b","type":"step-finish","tokens":{"total":404062,"input":399574,"output":170,"reasoning":492,"cache":{"write":0,"read":3826}},"cost":0.24179232}

Where it comes from

The query is generated in the SessionV1.Event.PartUpdated projector:

https://github.com/anomalyco/opencode/blob/dev/packages/core/src/session/projector.ts#L281-L292

yield* db
  .insert(PartTable)
  .values({ id, message_id: messageID, session_id: sessionID, time_created: event.data.time, data })
  .onConflictDoUpdate({ target: PartTable.id, set: { data } })
  .run()
  .pipe(Effect.orDie)

The step-finish payload is produced in packages/opencode/src/session/processor.ts and published via session.updatePart(...).

Likely problem

Drizzle ORM table-qualifies the conflict target as "part"."id" instead of the bare "id". SQLite's UPSERT conflict target must match the PRIMARY KEY / UNIQUE constraint exactly. The part table defines id as the primary key, so the valid target is ON CONFLICT ("id"). Depending on the SQLite / better-sqlite3 build, ON CONFLICT ("part"."id") can fail with a constraint-mismatch error.

Other possible causes: oversized data payload, a NOT NULL violation on time_updated/data during the update, or SQLite locking / corruption.

What would help diagnose

-- SQLite version used by the running binary
SELECT sqlite_version();

-- confirm the part table primary key
PRAGMA table_info(part);

-- check for oversized / recent part rows
SELECT id, length(data) AS data_len FROM part ORDER BY time_created DESC LIMIT 5;

Also, the full error message / stack trace from the crash would confirm whether this is the Drizzle table-qualified conflict-target issue or something else.

Related issues

Possibly related to the same persistence path: #31204, #23673, #28989, #18921.

Plugins

Oh my openagent 4.8.1

OpenCode version: 1.17.3

Steps to reproduce

1. Start or continue a session.
2. Trigger a step that finishes with tool calls and snapshot data.
3. The projector attempts to upsert the step-finish part row.
4. The query fails.

Screenshot and/or share link

Operating System

MacOS 26.1

Terminal

Ghosty/Zed

[github-actions]

This issue does not fully meet our contributing guidelines.

What needs to be fixed:

• OpenCode version is left as placeholder text (<!-- unknown — please fill in -->). Please provide the actual version (run opencode --version or check your install).
• Operating System is left as placeholder text. Please provide your OS and version.
• Terminal is left as placeholder text. Please provide the terminal emulator you are using.
• The bug report does not include an actual error message or stack trace — only a theoretical root cause analysis. Please share the real crash output or logs if available.

Please edit this issue to address the above within 2 hours, or it will be automatically closed.

---

This issue might be a duplicate of existing issues. Please check:

• [Critical Bug] Assistant responses invisible in TUI - parts table not populated #23673: Parts table not populated / assistant responses missing — same persistence path
• BUG: session_message.seq NOT NULL constraint failed on agent-switched sessions #31204: DB constraint failure (NOT NULL) in session projector — similar SQLite constraint error during session event projection
• Unknown tool name written to DB before repair callback fires; stuck pending part permanently kills session on Bedrock (400 toolUse.name constraint) #28989: Oversized snapshot parts — one of the suggested causes in this issue

If you believe this was flagged incorrectly, please let a maintainer know.

[github-actions]

Thank you for updating the issue! All required fields are now filled in. Your bug report looks good — the description, reproduction steps, version, OS, and terminal are all present. Removing the compliance flag.

[morpheus9393]

Adding +1 — this is a severe, recurring production problem that needs to ship a fix in the next release.

Local diagnosis of the affected DB:

• opencode.db: 4.71 GB, 294,444 part rows, 71,439 messages, 2,485 sessions
• SQLite 3.50.4, WAL mode, PRAGMA integrity_check = ok (not corrupted)
• Disk free: 41.8 GB (no SQLITE_FULL)
• Largest part JSON blobs: ~5 MB each (step-finish snapshots with tool-call data)
• part.id is PRIMARY KEY (TEXT) — matches the ON CONFLICT target

Observed behavior:

• The error appears as a modal Sitzungsfehler (session error) in CodeNomad, killing the active session
• It frequently co-occurs with the terminated undici socket error ([BUG] "terminated" error #30221): when the provider stream tears mid-response, the PartUpdated projector runs the upsert, and it fails — likely a FK race (PR fix(sync):[issue#10716] add retry mechanism for foreign key constraint failures on Windows #19526 addressed this for a related path but the PartUpdated projector in projectors.ts still only catches foreign(err), rethrowing everything else)
• The error dialog does not include the SQLite error code, making root-cause isolation harder

Root cause hypothesis (with evidence):

1. FK constraint race — part.message_id references message(id) ON DELETE CASCADE; if the part write lands before the parent message commits, FOREIGN KEY constraint failed fires and propagates uncaught
2. Abort/lock contention — an interrupted session holds a write lock; the projector's upsert conflicts
3. Large payload + WAL checkpoint — 5 MB JSON blobs in 294K rows with WAL checkpoint pressure

What would help:

1. Catch ALL DB errors in the PartUpdated projector (not just FK) — a failed part persistence should NEVER kill the session
2. Retry the upsert with backoff on SQLITE_BUSY/SQLITE_LOCKED
3. Consider pruning/compressing old part data, or adding a TTL/retention policy — 4.7 GB for session history is unsustainable
4. Include the SQLite error code in the failure message so users can distinguish FK race vs. constraint mismatch vs. locking

This is not a rare crash — it hits repeatedly during normal heavy agent workflows (orchestrator + subagents, long streaming responses). Please prioritize for the next release.