Session message-writer self-collides (UNIQUE constraint messages.session_id, msg_uid) under concurrency even with fully isolated per-process sessions.db

[Broccolito]

Summary

Even after fully isolating each biorouter run to its own sessions.db
(a unique XDG_DATA_HOME per invocation — verified: separate DB files, the main
~/.local/share/biorouter/sessions/sessions.db untouched, no shared writer),
running several biorouter run processes concurrently still aborts a fraction of
them with:

Error: error returned from database: (code: 2067) UNIQUE constraint failed: messages.session_id, messages.msg_uid

Since each process writes only its own private DB, this is a self-collision
inside a single run — biorouter inserts two message rows with the same
(session_id, msg_uid) into its own store. It looks like an internal race in
message persistence (msg_uid assignment) that surfaces under concurrency/CPU
scheduling, and scales with session length (number of tool-call messages).

This is distinct from #31

#31 is about multiple processes sharing one sessions.db. This is different:
the DBs are provably separate (per-process XDG_DATA_HOME, confirmed via
distinct file paths + untouched main DB), yet the collision still happens. So
per-process isolation is NOT sufficient — the writer races with itself.

Environment

• biorouter 1.88.3, macOS (Apple Silicon), 16-core.
• Each run: unique --name, unique XDG_DATA_HOME=<tmpdir>, --quiet,
  --output-format json, --max-turns 50, multi-tool agent sessions (many
  messages), a medcp stdio extension providing the tools.

Observed behavior (rate scales with concurrency × session length)

concurrency	session length	collision rate
1 (sequential)	long (max-turns 50)	~0%
6	short (max-turns 4, few messages)	0%
2	long	nonzero (~50% in a small sample)
4	long	~44%

The aborted run emits (no text response), 0 tool calls, and an empty/invalid
--output-format json. A single sequential run never collides; the problem only
appears once ≥2 runs execute concurrently, and worsens with more messages.

Expected

msg_uid assignment / message insertion within a single session should be
race-free regardless of process concurrency or CPU scheduling. Suggestions:

• Generate msg_uid atomically (a per-session monotonic counter under a lock, or
  a UUID) so a single run cannot violate its own (session_id, msg_uid)
  uniqueness.
• Open the session DB in WAL mode with a busy_timeout, and retry the
  insert on SQLITE_BUSY/constraint instead of aborting the whole run.
• On an unrecoverable DB error, surface a clear message and still emit valid
  --output-format json, rather than empty output + a raw SQLite dump.

Impact

Parallel/batch use of biorouter run is unreliable: any concurrency silently
fails a fraction of runs with empty output, forcing callers to add external
retry logic. This blocks straightforward "run N agents in parallel" workflows and
makes headless throughput far lower than the hardware allows (a 16-core box
cannot safely run more than ~1–2 concurrent long agent sessions today).

[Broccolito]

Update: precise reproducer — the collision happens on the TOOL-ERROR RECOVERY path, not from external concurrency.

Further investigation (biorouter 1.88.3, macOS) shows this is reproducible with a single biorouter run, no other biorouter process, its own private XDG_DATA_HOME, and an idle machine. My earlier "concurrency" framing was a spurious correlation.

What actually happens

1. The model issues a tool call whose backend returns an error. In our case an MCP tool (SQL over SQL Server) is sent ... LIMIT 50 (Postgres/MySQL syntax) and correctly returns:

[tool_error kind=tool_failure retryable=false] ... Electronic health records error:
(102, b"Incorrect syntax near 'LIMIT'.DB-Lib error message 20018, severity 15:
General SQL Server error: Check messages from the SQL Server")

2. After such a tool error (often the second in a row), biorouter aborts the loop and tries to recover, printing:

The tool calling loop was interrupted. How would you like to proceed?
  error: The error above was an exception we were not able to handle.
These errors are often related to connection or authentication
We've removed the conversation up to the most recent user message

3. That recovery — "we've removed the conversation up to the most recent user message", i.e. rewriting/truncating the persisted message history — is where the insert fails:

Error: error returned from database: (code: 2067) UNIQUE constraint failed: messages.session_id, messages.msg_uid

So biorouter re-inserts messages with msg_uids that already exist in its own session, violating the uniqueness constraint. The run then dies: (no text response), 0 tool calls, and --output-format json emits a partial/invalid document (prose prepended before the JSON).

Reproduction

Single run, nothing else running:

XDG_DATA_HOME=$(mktemp -d) biorouter run --quiet --output-format json \
  --provider <bedrock> --model <claude-opus> --max-turns 50 \
  -t "<prompt that makes the model send SQL with LIMIT to a SQL Server MCP tool>"
# dies ~15s with the UNIQUE constraint error

Also reproduces with --no-session (so it is not specific to named sessions).

Notes on frequency

• Tool errors themselves are normally survivable: 84–96% of our successful runs contained tool/SQL errors and finished fine, so only some error-recovery paths trip this.
• It is strongly model-dependent in our data: Claude Opus runs died far more often than GPT-5.5 or a local Qwen model given the same tool and the same class of SQL errors, presumably because of how often/quickly it repeats a failing call before the recovery path is taken.
• Impact: an otherwise-recoverable tool error becomes a hard, unrecoverable run failure — the model never gets the chance to correct its query.

Suggested fixes

• Make msg_uid assignment collision-free on the recovery/truncation path (regenerate ids, or INSERT OR REPLACE/upsert when rewriting history).
• Treat a tool error as a normal turn result and let the model retry, instead of raising an unhandled exception that forces conversation rewriting.
• If the run must abort, still emit valid --output-format json (currently prose is printed before the JSON, so the document cannot be parsed).