core: concurrent writers on shared SQLite DB cause UNIQUE constraint failures and permanent event_sequence desync #54464

Summary

When two opencode processes (a legacy opencode-ai V1 process and the new @opencode/cli V2 service) run concurrently against the same SQLite database, both write the per-aggregate event stream with an unguarded sequence counter. They allocate the same seq, producing SQLiteError: UNIQUE constraint failed: event.aggregate_id, event.seq. The failure is not self-healed: event_sequence.seq is left behind MAX(event.seq), so any later event write for that session collides again. The store never reconciles the watermark, and the colliding writer surfaces an unhandled EffectDrizzleQueryError instead of retrying or taking ownership.

Environment

• opencode version: opencode v2.0.26
• OS: Windows 10.0.19045.7725 (win32 x64)
• Terminal: Windows Terminal (WT_SESSION set), TERM=xterm-256color
• Shell: C:\WINDOWS\system32\cmd.exe
• Install/channel: npm global @opencode/cli@2.0.26 (release). Legacy opencode-ai was also installed and running during the incident.
• Active plugins: none configured (opencode.jsonc defines no plugins, and no .opencode/plugin(s)/ directory exists). Note: @opencode-ai/plugin@1.18.31 (V1 SDK) is present in package.json but V1 plugins do not load on V2.

Reproduction

1. Have a legacy opencode-ai (V1) process running a session, using the shared data dir ~/.local/share/opencode.
2. Start opencode V2 (@opencode/cli) against the same data dir; the V2 CLI starts a service (opencode serve --service) that runs migrations and begins writing events for the same session.
3. Let both processes write events for the same session aggregate (e.g. continue the session in V1 while V2 is active).
4. Observe repeated UNIQUE constraint failed: event.aggregate_id, event.seq in ~/.local/share/opencode/log/opencode.log while event_sequence.seq < MAX(event.seq).

Expected Behavior

• A second writer should either acquire the per-aggregate owner/lease (event_sequence.owner_id) or fail fast with a clear, handled error instead of an unhandled Drizzle error.
• The event store should keep event_sequence.seq consistent with MAX(event.seq) — i.e. self-heal or reconcile on load so a session is never permanently unable to write events.
• Running two opencode instances (or versions) against one data dir should be detected/prevented, or documented as unsupported.

Actual Behavior

Event inserts reuse an already-taken seq and fail. Three real occurrences on 2026-10-11 (all in the same server run run=0e1b403f):

timestamp=2026-10-11T05:09:44.400Z level=ERROR ... error="EffectDrizzleQueryError: Failed query: insert into \"event\" (\"id\", \"aggregate_id\", \"seq\", \"type\", \"data\") values (?, ?, ?, ?, ?)
params: evt_1295def4a0013KOeqOSu6zncqN,ses_ed7375da6ffeXS4BoGpzP5CiTY,1729,message.part.updated.1,...
(cause: ... SQLiteError: UNIQUE constraint failed: event.aggregate_id, event.seq)"

timestamp=2026-10-11T05:11:14.244Z level=ERROR ... seq=1729 message.updated.1  (same UNIQUE failure)
timestamp=2026-10-11T05:11:25.174Z level=ERROR ... seq=1729 message.updated.1  (same UNIQUE failure)

Resulting inconsistent state for aggregate ses_ed7375da6ffeXS4BoGpzP5CiTY:

event_sequence.seq = 1728
MAX(event.seq)     = 1772      -- 1623 rows, seq 150..1772, no gaps, no duplicate rows

So the next write computes 1729, which already exists, and collides. The watermark never caught up on its own; the only affected aggregate was this session (event_sequence had 153 rows; exactly one had seq < MAX(event.seq)). event_sequence.owner_id was NULL for every row, i.e. the single-writer guard was never engaged.

Additional Context

• Timeline from the log (run ids = distinct server/process instances):
  • run=0e1b403f: first log 2026-10-11T02:26:22Z, last log 2026-10-11T05:11:25Z — this is the legacy opencode-ai process; its final log line is the third UNIQUE error, after which it exited.
  • run=a2c3cf22 and run=89563b95: started 2026-10-11T04:25:27Z (the V2 CLI + serve --service).
  • Both overlapped, so two writers hit the same event table.
• Because the session's durable event log froze while the watermark stayed at 1728, the session's time_updated stopped at the last successful write and no events were persisted afterwards.
• Workaround applied locally (server left running): UPDATE event_sequence SET seq = (SELECT MAX(seq) FROM event WHERE aggregate_id = event_sequence.aggregate_id) WHERE aggregate_id = 'ses_ed7375da6ffeXS4BoGpzP5CiTY'; → watermark 1728 → 1772, zero aggregates with watermark < event_max, no new UNIQUE errors.
• Frequency: occurred exactly 3 times, only while two processes shared the DB; it is intermittent and depends on concurrent writes, which makes it easy to miss but leave the store permanently broken for the affected aggregate.
• The owner_id column (added by migration 20260504145000_add_sync_owner) looks like the intended single-writer mechanism, but it was NULL, so ownership was never claimed. Consider claiming/validating ownership before allocating seq, and reconciling event_sequence.seq with MAX(event.seq) on startup.
• Redactions: no secrets included; session/event ids are opaque.