Session permanently bricked by SQLite UNIQUE constraint (event.aggregate_id, event.seq) collision #51316

Summary

A running session hit a UNIQUE constraint failed: event.aggregate_id, event.seq error in the local event store. After the first occurrence, every subsequent prompt sent to that same session fails identically and forever — the session is permanently bricked, with the client showing "Failed to send prompt / Unexpected server error".

Environment

• opencode version: 1.18.32
• Platform: macOS (darwin)
• Storage: local SQLite event store (~/.local/share/opencode/log/opencode.log via Drizzle/Effect SQL)

What happened

While a subagent (nw-software-crafter, spawned via the task tool from a parent "build" session) was mid-execution, two events targeting the same session aggregate were assigned the same sequence number and collided on insert:

level=ERROR message=failed ref=err_3dfde4eb error="EffectDrizzleQueryError: Failed query: insert into \"event\" (\"id\", \"aggregate_id\", \"seq\", \"type\", \"data\") values (?, ?, ?, ?, ?)
params: evt_..., ses_f28098316ffeCPFeKXFFOzC4LX, 19, message.part.updated.1, {...}
(cause: Cause([Fail(effect/sql/SqlError: Failed to execute statement (cause: effect/sql/SqlError/UniqueViolation: Failed to execute statement (cause: SQLiteError: UNIQUE constraint failed: event.aggregate_id, event.seq)))]))"

Right after, the server logged:

level=INFO cause="InterruptError: All fibers interrupted without error
    at ServerProcess.start ...
  [cause]: InterruptCause: The fiber was interrupted by:
      at fiber (#1)
"

The colliding event was a filesystem "patch" event (a tracked file — execution-log.json — had just been modified on disk by a CLI tool invoked from the subagent) landing at the same moment as a task tool-result event finishing in the parent session. Both wrote to session ses_f28098316ffeCPFeKXFFOzC4LX with seq=19.

The real bug: the session never recovers

After this first collision, I re-sent prompts to the same session several times (minutes apart, across a fresh page/UI load). Every single attempt failed with the exact same seq=19 collision on the exact same session:

09:54:08 ... ses_f28098316ffeCPFeKXFFOzC4LX, 19, message.part.updated.1 -> UNIQUE constraint failed
10:24:31 ... ses_f28098316ffeCPFeKXFFOzC4LX, 19, message.updated.1      -> UNIQUE constraint failed
10:24:46 ... ses_f28098316ffeCPFeKXFFOzC4LX, 19, message.updated.1      -> UNIQUE constraint failed
10:24:54 ... ses_f28098316ffeCPFeKXFFOzC4LX, 19, message.updated.1      -> UNIQUE constraint failed

This means whatever in-memory (or on-disk) "next sequence number" tracking exists for a session does not advance/recover after a failed insert — it keeps retrying the same stale seq value forever. The session is permanently unusable; the only workaround is to abandon it and start a new session.

Impact

• The client shows a generic, unactionable error: "Failed to send prompt — Unexpected server error. Check server logs for details."
• There is no indication to the user that the session itself (not the prompt, not the network, not the model) is broken.
• This is especially disruptive for orchestration workflows that spawn nested subagent sessions (e.g. via the task tool) with concurrent file-watcher activity, since the orchestrator session is the one most likely to receive overlapping events.

Expected behavior

• The event-sequence assignment for a session should not race/collide in the first place (this is presumably a read-max-seq-then-insert race rather than an atomic increment).
• If a collision does occur, the session should self-heal (recompute the correct next seq and retry) rather than being permanently stuck reusing the same doomed sequence number.
• The client-facing error should be more actionable than "Unexpected server error" — e.g. distinguishing "this session is corrupted, please start a new one" from a transient failure worth retrying.

Repro conditions (best guess)

1. Start a top-level session (agent: build).
2. From it, dispatch a subagent via the task tool (e.g. nw-software-crafter) that performs file writes opencode is watching (git-tracked files under the project directory).
3. Have the subagent invoke a CLI tool that patches a tracked file (in our case, a small append-only JSON log) at roughly the same time the subagent's own tool-result event is being flushed back to the parent session.
4. Under the right timing, two events for the parent session's aggregate get the same seq, and the insert fails with UNIQUE constraint failed: event.aggregate_id, event.seq. All future events for that session fail the same way from then on.

Logs

Full log lines available on request (redacted the base64 reasoning-signature payloads in this report for brevity, since they are unrelated to the bug and quite large).