Bug: stale event sequence permanently rejects new session events #51411

Description

A durable aggregate can become permanently unwritable when event_sequence.seq falls behind rows already present in event. The next local publish derives seq = event_sequence.seq + 1, updates the sequence row, then collides with the existing (aggregate_id, seq) unique index. The transaction rolls back, so every later prompt repeats the same collision.

The event log itself remains intact; the failure is the stale sequence cursor. New publishes should recover inside the existing immediate transaction by deriving the local high-water mark from both tables, then write the next contiguous sequence without changing owner-claim behavior.

Plugin developers commonly run and compare v1 and v2 while supporting both host generations. That is a normal development workflow, not an unsupported user mistake. If only OPENCODE_CONFIG_DIR is isolated while the default data DB remains shared, cross-generation startup can leave an active v1 session damaged (see #42260). This defensive recovery is necessary for a legacy database already left in that state; it does not replace proper v1/v2 data isolation.

Plugins

None

OpenCode version

1.18.32 (observed); latest dev inspected for the fix

Steps to reproduce

1. Create an aggregate and durable events through any normal OpenCode path.
2. In a stopped, disposable SQLite fixture, lower only that aggregate's event_sequence.seq below the highest event.seq; do not alter the event rows.
3. Start OpenCode and publish another durable event for the aggregate.
4. Observe UNIQUE constraint failed: event.aggregate_id, event.seq; retries use the same stale sequence and the session remains unusable.

This can be left behind by historical cross-version/shared-database migration scenarios. Related context: #42260, #42444, #42389, and the template-closed report #51316.

Operating System

macOS 26 / Apple Silicon

Terminal

iTerm2