# Root Cause Hypotheses

The occurrence of duplicate benchmark rows in a FastAPI service utilizing SQLite suggests a failure in atomicity and idempotency, likely exacerbated by the asynchronous nature of FastAPI and the specific locking behavior of SQLite.

### Hypothesis 1: Lack of Client-Side Idempotency Keys
The most likely cause is that the proxy (OpenWebUI, agent clients) is implementing a retry mechanism. If a request times out or a network glitch occurs during a streaming response, the client may resend the exact same request. If `create_run` is called at the start of every request without checking for a unique client-provided identifier, a new row is inserted every time the client retries, leading to duplicates.

### Hypothesis 2: "Check-then-Act" Race Condition
If the code implements a manual check (e.g., `if not run_exists(id): create_run(id)`), a race condition exists. In a concurrent environment, two threads/tasks may both execute the check simultaneously, find that the run does not exist, and both proceed to call `create_run`. Because SQLite's default isolation levels and the FastAPI thread pool can overlap execution, this leads to duplicate entries.

### Hypothesis 3: SQLite Write Contention and Application-Level Retries
SQLite allows multiple concurrent readers but only one writer. If the application uses a library or a wrapper that automatically retries `database is locked` errors without idempotency logic, a write that actually succeeded (but the confirmation was delayed or interrupted) might be retried by the application layer, resulting in a second insertion.

### Hypothesis 4: Streaming Lifecycle Mismatch
For streaming requests, `create_run` is called at the start and `finish_llm_request` at the end. If the stream is interrupted (client disconnects), `finish_llm_request` may never be called. If the client then retries the request to "resume" or "restart," a new run is created. The "duplicate" is actually a failed attempt followed by a successful one, but without a way to link them, they appear as duplicates of the same logical benchmark.

---

# Evidence To Collect

To move from hypothesis to certainty, the following telemetry and logs must be gathered:

1.  **Request Correlation IDs:** Check if the incoming HTTP requests from OpenWebUI or the agent client contain a `X-Request-ID` or a similar unique header. Compare these IDs against the timestamps of the duplicate rows in SQLite.
2.  **SQLite Journal Mode Check:** Execute `PRAGMA journal_mode;`. If it is in `DELETE` mode (default), write contention is significantly higher than in `WAL` (Write-Ahead Logging) mode.
3.  **FastAPI Worker Logs:** Analyze logs for `HTTP 500` or `HTTP 504` errors immediately preceding the duplicate entries. This would indicate a timeout that triggered a client-side retry.
4.  **Database Row Analysis:**
    *   Compare the `created_at` timestamps of duplicate rows. If they are within milliseconds, it is a race condition. If they are seconds apart, it is a client retry.
    *   Check if the `payload` or `prompt` is identical.
5.  **Thread/Task Dumps:** Use a tool like `py-spy` or FastAPI's internal instrumentation to see if multiple threads are blocked on the same SQLite write lock.

---

# Patch Plan

The fix requires a multi-layered approach: moving from "check-then-act" to "atomic-upsert" and introducing a client-side idempotency key.

### Phase 1: Database Schema Hardening
Modify the schema to enforce uniqueness at the engine level. We will introduce a `client_request_id` column.

### Phase 2: Refactoring Helper Functions
We will rewrite the helpers to use `INSERT OR IGNORE` or `ON CONFLICT` clauses.

**Revised `create_run`:**
```python
def create_run(db: Session, client_request_id: str, metadata: dict):
    # Use a unique constraint on client_request_id to prevent duplicates
    stmt = insert(Run).values(
        client_request_id=client_request_id, 
        **metadata
    ).on_conflict_do_nothing(index_elements=[Run.client_request_id])
    db.execute(stmt)
    db.commit()
    # Return the existing run if the insert was ignored
    return db.query(Run).filter(Run.client_request_id == client_request_id).first()
```

**Revised `create_llm_request`:**
Ensure this is linked to the `run_id` and uses a unique combination of `run_id` and a sequence number or request hash.

**Revised `finish_llm_request`:**
Implement an idempotent update.
```python
def finish_llm_request(db: Session, request_id: int, completion_data: dict):
    db.query(LLMRequest).filter(LLMRequest.id == request_id).update(
        {"status": "completed", **completion_data}
    )
    db.commit()
```

### Phase 3: Middleware for Idempotency
Add a FastAPI middleware that ensures every request has a `X-Idempotency-Key`. If the client doesn't provide one, the server generates one for the session, but encourages the client to provide it.

### Phase 4: SQLite Optimization
Enable WAL mode to allow concurrent reads and writes, reducing the likelihood of `database is locked` errors that trigger retries.

---

# SQLite Constraints And Indexes

To prevent duplicates at the storage level, the following SQL migrations must be applied.

### 1. Enable WAL Mode
This is critical for any FastAPI/SQLite production setup to prevent writer starvation.
```sql
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
```

### 2. Schema Constraints
We must move away from relying on auto-incrementing primary keys as the sole identifier for logical runs.

```sql
-- Add a unique constraint to the runs table
-- This ensures that even if the Python code fails, the DB rejects the duplicate
CREATE UNIQUE INDEX idx_runs_client_request_id ON runs(client_request_id);

-- Ensure LLM requests are tied to a run and cannot be duplicated for the same run step
CREATE UNIQUE INDEX idx_llm_req_run_step ON llm_requests(run_id, step_number);

-- Add foreign key constraints to ensure referential integrity
PRAGMA foreign_keys = ON;
```

### 3. Idempotent Insertion Example
Instead of:
`INSERT INTO runs (metadata) VALUES ('...');`

Use:
```sql
INSERT INTO runs (client_request_id, metadata, created_at) 
VALUES ('req_12345', '{"model": "gpt-4"}', CURRENT_TIMESTAMP)
ON CONFLICT(client_request_id) DO UPDATE SET
    metadata = excluded.metadata; 
-- Or DO NOTHING if the first request is the source of truth.
```

---

# Streaming Edge Cases

Streaming responses in FastAPI (using `StreamingResponse`) introduce a specific failure mode: the request handler continues to run after the client has disconnected, or the client reconnects while the previous handler is still cleaning up.

### The "Zombie Run" Problem
If a stream is interrupted, `finish_llm_request` is never called. The row remains in a `pending` state.
*   **Risk:** A retry creates a second row.
*   **Fix:** The `create_run` logic must check for an existing `pending` run with the same `client_request_id` and "adopt" it rather than creating a new one.

### The "Partial Write" Problem
If `add_event` is called inside the stream loop, and the stream crashes, we have a partial log.
*   **Risk:** Duplicate events if the client retries and the server restarts the stream from the beginning.
*   **Fix:** Use a sequence number for events. `add_event(run_id, sequence_num, event_data)`. Use `INSERT OR IGNORE` on the `(run_id, sequence_num)` tuple.

### Implementation Guardrail for Streaming:
```python
async def streaming_endpoint(request: Request):
    client_id = request.headers.get("X-Idempotency-Key")
    run = create_run(db, client_id, ...)
    
    try:
        async for chunk in llm_stream():
            add_event(db, run.id, chunk)
            yield chunk
        finish_llm_request(db, run.id, ...)
    except asyncio.CancelledError:
        # Handle client disconnect
        # Mark run as 'interrupted' instead of leaving it 'pending'
        mark_run_interrupted(db, run.id)
        raise
```

---

# Test Plan

The following 12 acceptance tests must pass to verify the fix.

### Concurrency & Race Condition Tests
1.  **Simultaneous Identical Requests:** Fire 10 identical requests with the same `X-Idempotency-Key` using `asyncio.gather`. **Expected:** Only 1 row created in `runs` table.
2.  **Rapid-Fire Event Logging:** Call `add_event` 100 times concurrently for the same run. **Expected:** No duplicate events; all events persisted in correct sequence.
3.  **Read-While-Write Stress:** Perform heavy reads on the `runs` table while simultaneously inserting 50 runs. **Expected:** No `database is locked` errors (verifies WAL mode).

### Idempotency & Retry Tests
4.  **Client Retry After Timeout:** Simulate a request that takes 30s, client times out at 10s and retries. **Expected:** The second request attaches to the first run; no duplicate row.
5.  **Duplicate Request ID (Different Payload):** Send a request with an existing `client_request_id` but different metadata. **Expected:** System either ignores the update or updates the existing row (depending on policy), but does not create a new row.
6.  **Interrupted Stream Recovery:** Start a streaming request, kill the connection halfway, then restart the request with the same ID. **Expected:** The system recognizes the interrupted run and resumes/restarts without duplicating the `run` row.

### Integrity & Constraint Tests
7.  **Foreign Key Violation:** Attempt to `create_llm_request` for a `run_id` that does not exist. **Expected:** SQLite raises a `ForeignKeyViolation` error.
8.  **Unique Constraint Trigger:** Manually attempt to insert a duplicate `client_request_id` via SQL. **Expected:** `SQLITE_CONSTRAINT_UNIQUE` error.
9.  **Null Idempotency Key:** Send a request without the `X-Idempotency-Key` header. **Expected:** Server generates a temporary ID; request succeeds.

### Lifecycle Tests
10. **Full Lifecycle Success:** `create_run` $\rightarrow$ `create_llm_request` $\rightarrow$ `add_event` $\rightarrow$ `finish_llm_request`. **Expected:** All rows linked correctly; status is `completed`.
11. **Orphaned Run Cleanup:** Create a run, then crash the process before `finish_llm_request`. Restart and check status. **Expected:** Run is marked as `interrupted` or `pending`.
12. **Migration Verification:** Run the migration script on a database already containing duplicates. **Expected:** Migration successfully cleans duplicates (keeping the earliest) and applies the unique index.

---

# Rollback Plan

If the patch introduces regressions (e.g., blocking legitimate requests due to overly aggressive unique constraints):

1.  **Code Reversion:** Revert the FastAPI helper functions to the previous version.
2.  **Index Removal:** Drop the unique indexes to restore the previous behavior.
    ```sql
    DROP INDEX idx_runs_client_request_id;
    DROP INDEX idx_llm_req_run_step;
    ```
3.  **Journal Mode Reset:** If WAL mode causes issues with backup tools or filesystem locks:
    ```sql
    PRAGMA journal_mode=DELETE;
    ```
4.  **Data Recovery:** Since we are adding constraints and not deleting data (unless the migration script is run), the original data remains intact. If the migration script deleted duplicates, a backup of the `.db` file must be restored.

---

# Decision Summary

| Component | Decision | Justification |
| :--- | :--- | :--- |
| **Concurrency** | SQLite WAL Mode | Enables concurrent readers and a single writer without blocking, essential for FastAPI's async nature. |
| **Idempotency** | `client_request_id` | Shifts the responsibility of identity to the client, allowing the server to distinguish between a "retry" and a "new request." |
| **Atomicity** | `ON CONFLICT DO NOTHING` | Eliminates the "check-then-act" race condition by making the check and the insert a single atomic operation. |
| **Integrity** | Unique DB Indexes | Provides a "last line of defense." Even if the application code has a bug, the database prevents data corruption. |
| **Streaming** | `asyncio.CancelledError` | Ensures that disconnected clients don't leave "zombie" runs in a pending state, which confuses retry logic. |
| **Architecture** | Middleware-based ID | Ensures consistency across all endpoints (OpenWebUI, Agent, etc.) without duplicating ID logic in every route. |