MCP: UNIQUE constraint failed on events.sequence during concurrent tool calls

[alexgorbatchev]

Bug

When multiple MCP tool calls (e.g. agentation_dismiss, agentation_resolve) are invoked concurrently, the server returns:

HTTP 400: {"error":"UNIQUE constraint failed: events.sequence"}

After this occurs, the affected annotations become permanently stuck — they can't be dismissed, resolved, or acknowledged, and they keep returning from agentation_watch_annotations in an infinite loop.

Root Cause

The globalSequence counter is incremented via ++globalSequence in the EventBus before inserting into SQLite, but the increment and the DB insert are not atomic:

https://github.com/benjitaylor/agentation/blob/main/packages/agentation-mcp/src/index.ts

The relevant pattern (visible in the built output at dist/index.js):

// Line ~75 (local EventBus)
sequence: ++globalSequence,

// Line ~169 (cloud/user variant)  
sequence: ++globalSequence,

The events table has sequence INTEGER NOT NULL UNIQUE, so when two async handlers resume in the same microtask batch, they can both read and increment the counter to the same value before either writes to SQLite, causing the constraint violation.

Steps to Reproduce

1. Install agentation + agentation-mcp in a React app
2. Start the MCP server: npx agentation-mcp server --port 4749
3. Add the <Agentation endpoint="http://localhost:4749" /> component
4. Create a single annotation in the browser
5. From Claude Code (or any MCP client), call multiple tool actions in parallel — e.g. try to dismiss 3+ annotations in a single message (Claude Code batches parallel tool calls)
6. Observe: first call may succeed, subsequent calls fail with UNIQUE constraint failed: events.sequence
7. The failed annotations are now stuck and keep returning from agentation_watch_annotations

Sending even 2 concurrent resolve/dismiss/acknowledge calls is enough to trigger it reliably.

Expected Behavior

Concurrent MCP tool calls should not corrupt the event sequence. Each event should get a unique sequence number regardless of concurrency.

Possible Fixes

• Wrap the increment + insert in a SQLite transaction with a retry loop on constraint violation
• Use SQLite's AUTOINCREMENT on the sequence column instead of managing it in JS
• Use a mutex/queue to serialize event creation

Environment

• agentation-mcp: 1.2.0
• Node: v24.14.0
• OS: macOS
• MCP client: Claude Code