Description

Agent switch corrupts the session sequence counter → session permanently wedged (UNIQUE constraint on session_message.seq)

Environment

• OpenCode CLI/server: v2.0.32 (TUI + web-login server, opencode serve)
• Storage: SQLite (opencode-web-login-v1.db), WAL mode
• Schema: session_message has a unique index (session_id, seq); the sequence counter lives in event_sequence(aggregate_id) -> seq; pending inputs queue in session_inbox
• State affected: long-running session (single directory, ~1600 seq messages)

Summary

Switching the agent on an existing long-running session reset the session's sequence counter to 0 instead of continuing past the highest already-used seq. The newly generated messages then march upward from seq 0 until they hit an already-occupied seq, where the insert fails permanently:

SQLiteError: UNIQUE constraint failed: session_message.session_id, session_message.seq

Once this starts, every operation on the session fails with HTTP 500 (agent switch, model switch, inbox promote / drain) and the session becomes unrecoverable via the UI. Restarting the server does not help — the counter is rehydrated from the corrupted low value and the session keeps failing.

Impact

• Session is permanently stuck; every user action returns 500
• Pending user messages can sit in session_inbox without being delivered (or get dropped on enqueue/retry)
• Compaction enters a retry loop: it keeps re-attempting, burning one seq per attempt, and wedges at the first occupied seq
• Data itself is not lost, but the session is unusable until the counter is repaired manually

Reproduction

1. Use a session that has accumulated many messages (original projection seq space occupied up to N, e.g. N=1605).
2. Switch agent (e.g. build → plan).
3. (Aggravating factor observed, likely race) a second opencode serve --service instance running concurrently, sharing the same SQLite DB.
4. Observe:
   • agent-switched message is inserted with seq=0 while projection max seq is 1605 (counter reset, not continued)
   • promoting the next queued message fails with UNIQUE constraint failed: session_message.session_id, session_message.seq
   • POST /api/session/{id}/model → 500 with the same error, repeatedly

Evidence

Timeline (all messages logged with exact timestamps):

• 14:42:57 — previous round completes normally (idle, succeeded)
• 17:21:21 — user switches agent build → plan; record agent-switched written at seq=0; queued user message gets enqueued_seq=1 in session_inbox; SessionRunner.drain → SessionInbox.promote → SessionInbox.publish fails with:
  Failed to drain Session
  cause: SQLiteError: UNIQUE constraint failed: session_message.session_id, session_message.seq
  Session state becomes suspended, message never delivered.
• 17:40:15-17:40:43 — three attempts to switch model:
  POST /api/session/.../model → 500
  at Session.switchModel
  UNIQUE constraint failed: session_message.session_id, session_message.seq
• Counter state after corruption:
  event_sequence.seq = 76 <- cursor points into an already-occupied low range
  session_message: min seq 0, max seq 1605 (all seqs 0..1605 occupied by pre-reset history)
• 18:43:32 — server restart does NOT fix it. The runner rehydrates from the low counter and keeps writing LOW seqs (8, 9, 10, 17, 19, 22, 23, 26, 29, 34, 35, ... up to 74), then wedges permanently at the first occupied seq (77):
  compaction seq 23, 26, 29, ... 74 -> next attempt 77 -> UNIQUE violation, no progress

Root-cause smoking gun: the agent-switched record exists at seq=0 while other records of the same session occupy seq 0–1605 with strictly increasing timestamps after seq 0 — the counter was reset to 0 at agent-switch time, instead of max(session_message.seq)+1.

Relevant code path (from the stack trace):
SessionRunner.drain -> SessionInbox.promote -> SessionInbox.publish
-> insert into session_message (unique index on (session_id, seq)) FAILS
Session.switchModel -> same insert, same failure

Workaround / Fix that unblocked me

Repairing the counter to be above the occupied range restored the session immediately (no code change needed):

-- event_sequence.seq for the session: 76 -> (MAX(seq)+1) per session_message
UPDATE event_sequence
  SET seq = (SELECT MAX(seq)+1 FROM session_message WHERE session_id = ?)
WHERE aggregate_id = ?;

after which a server restart rehydrates the counter correctly and:

• the queued message is delivered normally
• the compaction retry loop completes
• model switching works again
• zero UNIQUE errors in the log after the restart

Suggested fix

Either of:

1. On session (re)load / agent switch, recompute the counter as max(seq)+1 over session_message for that session (and assert event_sequence.seq > max(session_message.seq) when rehydrating — if lower, self-heal instead of failing inserts forever).
2. Make the projection insert resilient: on UNIQUE(seq) collision, re-derive seq = COALESCE(MAX(seq), -1) + 1 for the session instead of hard-failing the whole runner and wedging the session in an unrecoverable suspended state.

The self-healing on rehydration seems especially important: currently a corrupted counter is reloaded on every restart and the session remains dead forever.

Plugins

No response

OpenCode version

No response

Steps to reproduce

No response

Screenshot and/or share link

No response

Operating System

No response

Terminal

No response