Responses retries can leave orphaned in_progress rows on duplicate internal_message_id

[AnthonyRonning]

Summary

POST /v1/responses currently persists the responses row and its user message through two independent database operations. When a client retries with the same metadata.internal_message_id, the second operation collides with the unique user-message UUID after a new in_progress Response has already committed.

The request returns 500 before orchestration starts, leaving a Response that has no user message, no worker, and no path to leave in_progress. Every duplicate retry can add another orphan.

This is especially likely with Maple's current retry flow: if the original request was accepted but the client observed a transport/stream error, Maple retries once with the same internal_message_id, then checks conversation items to determine whether the original request actually went through.

Deterministic reproduction

1. Create or use an existing conversation.
2. Call POST /v1/responses with a valid UUID in metadata.internal_message_id and wait for the request to be accepted or completed.
3. Submit another Responses request using the same conversation and the same internal_message_id.
4. The second request:
   • inserts a new responses row with status = in_progress;
   • fails to insert user_messages because user_messages.uuid is unique;
   • maps the database error to HTTP 500;
   • returns before any streaming/orchestration task is started.
5. The newly inserted Response remains orphaned.

A transient client disconnect after the first request is accepted produces the same sequence when the client retries.

Root cause

In src/web/responses/handlers.rs::persist_request_data():

1. state.db.create_response(new_response) commits a fresh Response.
2. state.db.create_user_message(new_msg) runs afterward and can fail on the UUID constraint.

In src/db.rs, those methods each check out their own pooled connection. They therefore do not share a transaction.

The schema declares user_messages.uuid UUID NOT NULL UNIQUE, so reusing the client-provided message UUID raises a unique violation. The error is currently passed through map_generic_db_error, which reports a 500.

There is no stale-Response cleanup task. Conversation deletion will eventually cascade-delete the row, but otherwise it remains indefinitely.

History/context

• Commit 8409286 intentionally removed the earlier full Responses idempotency implementation to align with the OpenAI API.
• Commit 08c60ad later added metadata.internal_message_id so the frontend and persisted conversation item would share an ID for deduplication/reconciliation.
• The latter did not make Response creation idempotent or atomic.

This issue does not require restoring the earlier full idempotency system.

Recommended lightweight fix

Add one DB-layer operation such as:

fn create_response_with_user_message(
    &self,
    new_response: NewResponse,
    new_message: NewUserMessage,
) -> Result<(Response, UserMessage), DBError>;

Its PostgreSQL implementation should use one connection and one Diesel transaction:

1. Insert the Response.
2. Set new_message.response_id from the inserted Response.
3. Insert the user message.
4. Commit only if both inserts succeed.

Then replace the two independent calls in persist_request_data() with that operation. A duplicate UUID or any other user-message insertion failure will roll back the Response automatically.

Also classify the exact user_messages_uuid_key unique violation as a duplicate-user-message domain error and map it to 409 Conflict instead of 500. The existing API-key duplicate-name handling provides a local example of matching DatabaseErrorKind::UniqueViolation plus the constraint name.

No schema migration should be necessary.

Avoid these alternatives

• Existence pre-check only: remains race-prone when two requests arrive concurrently.
• Delete the Response after message failure: leaves a crash/failure window around compensating cleanup.
• ON CONFLICT DO NOTHING: would preserve a newly created Response without its input message and could allow duplicate generation.
• Return the existing Response from persist_request_data(): the caller currently proceeds into orchestration, which would risk starting a second model run unless the entire handler gains a separate replay path.

Acceptance criteria

• Response and user-message creation for an existing conversation is atomic.
• Reusing an internal_message_id cannot increase the number of Response rows.
• The exact duplicate-message UUID case returns 409 rather than 500.
• Other database failures remain 500 and also roll back the Response.
• The original Response continues normally; the duplicate request never starts another model run.
• Normal Response creation still links user_messages.response_id to the created Response.
• A database-backed regression test proves that a duplicate message UUID leaves the Response count unchanged.

Out of scope

Full idempotent replay can be designed separately. That would include request hashing, distinguishing same-key/different-body requests, looking up the original Response, and deciding how to return or re-stream stored/in-progress output. The current lightweight fix only restores database integrity and gives duplicate submission an accurate status code.

Audit query

This identifies definite orphan candidates older than five minutes:

SELECT r.uuid, r.created_at, r.conversation_id
FROM responses r
WHERE r.status = 'in_progress'
  AND r.created_at < now() - interval '5 minutes'
  AND NOT EXISTS (
    SELECT 1
    FROM user_messages m
    WHERE m.response_id = r.id
  )
ORDER BY r.created_at;