Shared-service event_sequence cursor drifts behind event rows; affected session becomes permanently unappendable (UNIQUE constraint failed: event.aggregate_id, event.seq) #56460

Environment

• opencode --version:

  opencode v2.0.32
  

  (v2.0.32 binary is both the terminal CLI and the shared background service binary)
• opencode service status (at time of report, healthy):

  http://127.0.0.1:49374
  

• opencode api get /api/info:

  {"version":"2.0.32","pid":46567,"urls":["http://127.0.0.1:49374"],"paths":{"tmp":"/private/var/folders/z_/zv74l5x504z3d609vr82tdy40000gn/T/opencode"},"capabilities":{"persistentPty":true}}

• Clients: OpenCode desktop 1.19.3 (recently auto-updated from 1.18.35) + TUI
• OS: macOS 26 (Apple Silicon)
• DB: default ~/.local/share/opencode/opencode.db (~7 GB, WAL mode)
• Scope: shared service — one specific session became permanently unappendable; unaffected sessions kept working

Summary

During a desktop app update/restart while an active session was being driven, the shared service's event_sequence cursor for that session fell behind the rows actually present in the event table (cursor said 571, rows existed up to seq 579). Every subsequent append allocated seq 572, collided with the existing row, and the insert aborted:

UNIQUE constraint failed: event.aggregate_id, event.seq

The session then refused every further prompt (three consecutive failures on separate user attempts), and survived a desktop app restart, because the detached service process (opencode serve --service, pid unchanged) kept the stale allocator state.

Evidence

Session: ses_eb51dbc16ffexNsR6fl4Dj9XKF, client run: 321720bc

Failing log line (fixed timestamps: 17:35:28.025Z, 17:36:48.240Z, 17:36:50.969Z, 17:36:58.474Z, 17:37:05.465Z):

timestamp=2026-10-17T17:35:28.025Z level=ERROR run=321720bc message="prompt_async failed" sessionID=ses_eb51dbc16ffexNsR6fl4Dj9XKF cause="Cause([Die(EffectDrizzleQueryError: Failed query: insert into \"event\" (\"id\", \"aggregate_id\", \"seq\", \"type\", \"data\") values (?, ?, ?, ?, ?)
params: evt_14aeed316001NVSbqp1zDdUvjF,ses_eb51dbc16ffexNsR6fl4Dj9XKF,572,message.updated.1,{...role:user...} (cause: Error: UNIQUE constraint failed: event.aggregate_id, event.seq))])"

Note the retried inserts used the same seq (572) with fresh event ids, and the row already occupying (aggregate, 572) was a different event type (message.part.updated.1) — i.e., rows had been appended beyond the cursor by some other write path.

DB state immediately before the fix (read-only inspection with the service still running):

select seq from event_sequence where aggregate_id = 'ses_eb51dbc16ffexNsR6fl4Dj9XKF';
-- 571

select max(seq) from event where aggregate_id = 'ses_eb51dbc16ffexNsR6fl4Dj9XKF';
-- 579

-- rows 572..579 present (mix of session.updated, message.part.updated, message.updated)

Drift audit across the whole DB — exactly one aggregate was affected:

select es.aggregate_id, es.seq, e.actual
from event_sequence es
join (select aggregate_id, max(seq) as actual from event group by aggregate_id) e
  on e.aggregate_id = es.aggregate_id
where e.actual > es.seq;
-- 1 row: ses_eb51dbc16ffexNsR6fl4Dj9XKF | 571 | 579

Workaround

Resyncing the cursor to the table's true max unblocked the session immediately; continuing client run (role=server) activity afterward shows no further errors:

update event_sequence
set seq = (select max(seq) from event e where e.aggregate_id = event_sequence.aggregate_id)
where aggregate_id = 'ses_eb51dbc16ffexNsR6fl4Dj9XKF';

Repro conditions (not deterministic)

Timeline that preceded the drift:

1. Desktop app 1.18.35 running with a shared service already serving an active session (opencode serve --service, detached, parent 1).
2. Desktop auto-update to 1.19.3 completes and a new app instance launches while the session is mid-run; the old app generation exits.
3. A second app/CLI generation connects to the still-running service during the same minutes.
4. Shortly after, the session stops accepting prompts with the repeated UNIQUE constraint failed: event.aggregate_id, event.seq insert error above.

Suspected mechanism: some write path appends event rows without advancing (or advancing a different counter than) event_sequence, and/or the service process's allocator doesn't recompute its next seq from the event table after a client/service concurrency event, leaving the cursor permanently stale for that aggregate.

Suggested fix

Make the allocator self-heal: on a UNIQUE (aggregate_id, seq) violation, recompute max(seq) for the aggregate and retry the insert (once), so a drifted cursor can never brick a session. (A repair against WAL-mode SQLite survived fine in this case.)

Reported per https://opencode.ai/v2/docs/troubleshooting/#report-an-issue. Session ids/message ids retained; no prompts, file contents, or credentials included.