session: stale event_sequence counter wedges a session permanently on reload (UNIQUE session_message.session_id, seq) #56975

Summary

A session becomes permanently undrivable when the persisted per-session sequence
counter (event_sequence.seq) is stale — at or below an already-occupied
session_message.seq. On the next session load the runner rehydrates that stale
value, every projection insert collides on the (session_id, seq) unique index,
and the session is suspended for good. Restarting the server does not help:
the stale counter is on disk and is re-read on every restart.

This is the same failure class as #53146 / #56528, reported here with a single
writer, 4 affected sessions on one host, and the DB-level numbers that
pin the stale value. It is not limited to dual-process topology or agent
switches — a long-lived session whose counter row was never advanced goes
stuck the first time it is re-loaded.

Environment

• opencode version: 2.0.31 (@opencode/cli 2.0.31, channel latest); the
  wedged session was created under 2.0.27
• OS: Linux 7.0.0-38-generic x86_64 (Ubuntu 24.04)
• Terminal: xterm-256color (headless server, opencode serve --service)
• Shell: /bin/bash
• Install/channel: npm global (@opencode/[email protected]), channel latest
• Active plugins: none configured (MCP servers only)
• Storage: SQLite WAL, PRAGMA integrity_check = ok, 1.3 GB, 2167 sessions,
  78 056 session_message rows

Reproduction

1. Use a long-running session and let it accumulate history (here: 132
   session_message rows, sparse seq range 2…1321).
2. Run a manual compaction. It completes normally and the session returns to
   idle — the last durable rows are compaction @ seq 1318 and idle @ seq 1321.
3. Leave the session untouched long enough for the serve process to reload it
   (here: 4 days; a server restart or a fresh process loading the session has
   the same effect).
4. Send a prompt. The runner seeds its next seq from event_sequence.seq (= 5,
   stale) and attempts INSERT … seq = 5, which is already occupied →
   UNIQUE constraint failed: session_message.session_id, session_message.seq.
5. The session goes suspended at the moment of the prompt. Every subsequent
   turn fails the same way. Restarting the serve re-reads 5 and keeps failing.

Expected Behavior

• The sequence counter is reconciled with max(session_message.seq) when a
  session is loaded/rehydrated (seq = COALESCE(MAX(seq), -1) + 1), or
• a UNIQUE(seq) collision on the projection insert re-derives the seq and
  retries instead of failing the whole runner and suspending the session
  permanently.

Actual Behavior

ERROR "Failed to drain Session"
  cause: EffectDrizzleQueryError: Failed query: insert into "session_message"
    ("id","session_id","type","seq","time_created","time_updated","data") values (?,?,?,?,?,?,?)
  [cause]: SQLiteError: UNIQUE constraint failed:
    session_message.session_id, session_message.seq
    at SessionRunner.publishLLMEvent -> SessionStep.attempt -> SessionRunner.runStep
ERROR "Failed to publish Session execution lifecycle"  (same cause, +15 ms)

Additional Context

The numbers (read from a snapshot of the live DB, not the live file)

Session ses_ec231d026ffe…:

session_message schema (2.0.31):

CREATE TABLE "session_message" (
  id text PRIMARY KEY, session_id text NOT NULL, type text NOT NULL,
  seq integer NOT NULL, time_created integer NOT NULL,
  time_updated integer NOT NULL, data text NOT NULL,
  FOREIGN KEY (session_id) REFERENCES session_v2(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX session_message_session_seq_idx
  ON session_message (session_id, seq);

event_sequence:

CREATE TABLE event_sequence (aggregate_id text PRIMARY KEY, seq integer NOT NULL, owner_id text);

There is no sqlite_sequence / AUTOINCREMENT — the counter is application-owned,
so nothing in SQLite can rescue a stale value.

Recurrence on this host (single writer)

The serve process holding the DB was the only process with the file open, and it
had been running since 2026-10-17 (before the failing prompt). The same UNIQUE
error appears 13 times in the serve log across 4 distinct sessions:

Fleet shape in the same DB (snapshot 2026-10-19T02:07Z): of the 16 sessions
that have an event_sequence row, 14 have counter <= max(session_message.seq)
— 3 strictly behind and 11 exactly at max — i.e. they are one reload away from
the same wedge (the 11 are wedged if the counter means "next seq", safe if it
means "last allocated"). The other 2151 sessions have no counter row at all
(the allocator apparently derives the seq from the rows in that case, which is
why only sessions with a stale row are affected). A later snapshot (06:24Z)
reads 15 of 16, because one healthy session advanced into the at-max bucket
while the fleet kept working; both snapshots are internally consistent.

This suggests the counter row is written once (at/around session creation) and
is not advanced by the projection-insert path, while rehydration trusts it
unconditionally. A session that lives entirely inside one process is fine
(the in-memory counter is authoritative); the first reload after the row went
stale is fatal.

Workaround that unblocks a wedged session (verified on a DB copy)

-- in a transaction
UPDATE event_sequence
   SET seq = (SELECT COALESCE(MAX(seq), -1) + 1
                FROM session_message WHERE session_id = :sid)
 WHERE aggregate_id = :sid;
-- or INSERT the row if it is missing

then restart the serve so the runner rehydrates the repaired counter. Verified
on a snapshot copy: counter 5 → 1322, PRAGMA integrity_check ok, and a probe
INSERT … seq=1322 succeeds (rolled back). Alternatively, shifting that
session's rows out of the colliding range (seq = seq + 100000) also verifies
clean if you would rather not move the counter.

Why the compaction is implicated

The compaction is the last projection write before the wedge (rows 1318 and
1321), and the counter was still 5 afterwards. Note that the counter is 5
even though the session wrote 132 rows over four days — so the projection path
does not maintain it at all, and the compaction is not proven to be the writer
that resets it. What is proven is the sequence of events: durable rows advance
to 1321, the counter stays at 5, the session goes idle, and the first reload
seeds 5 and dies immediately. Fixing rehydration (or retrying on
UniqueViolation) covers every variant reported so far.

Related: #53146 (canonical, dual-process), #56528 (agent-switch variant, closed
as duplicate), #51411 (event table variant).