# Root Cause Hypotheses

The intermittent duplication of benchmark rows in a FastAPI/SQLite stack under concurrent proxy traffic typically stems from a combination of missing idempotency guarantees, SQLite's default concurrency behavior, and streaming lifecycle mismanagement. Below are the primary technical hypotheses, ranked by likelihood and impact:

1. **Missing Unique Constraints & Idempotency Keys**: The `create_run` and `create_llm_request` functions likely perform plain `INSERT` statements without `ON CONFLICT` or `INSERT OR IGNORE` directives. When OpenWebUI or the agent client retries a request due to network jitter or proxy timeouts, the application blindly creates a second row. Without a unique constraint on `run_id` or `request_id`, SQLite allows the duplicate, and the application layer has no mechanism to detect or suppress it.

2. **SQLite Default Locking Mode & Retry Loops**: SQLite defaults to `DEADLOCK` journaling and short busy timeouts. Under concurrent writes from multiple FastAPI workers or async tasks, `SQLITE_BUSY` errors occur. If the application or an ORM automatically retries the transaction without checking for existing rows, or if the retry logic is implemented at the HTTP layer (proxy/client) rather than the DB layer, duplicate inserts are generated. The proxy may also buffer and forward the same request twice during a 503/504 recovery.

3. **Streaming Response Lifecycle Misalignment**: Streaming endpoints in FastAPI use async generators. If `add_event` or `finish_llm_request` is called inside the generator without proper state tracking, a client disconnect, proxy restart, or SSE reconnection can trigger multiple `finish` calls. Additionally, if the streaming generator is not properly awaited or is wrapped in a background task that runs concurrently with the main request handler, race conditions on the same `run_id` can cause duplicate metadata rows.

4. **Async/Await Concurrency Bugs**: FastAPI's async nature means that if `create_run` or `add_event` is called without `await` in certain code paths, or if multiple coroutines share the same database connection without proper isolation, writes can interleave. SQLite is not thread-safe by default for concurrent writes on the same connection. If the app uses a shared connection pool without `check_same_thread=False` or proper async wrappers (e.g., `aiosqlite`), internal SQLite locks can cause silent failures or duplicate commits.

5. **Lack of Transactional Boundaries**: Helper functions may execute individual `INSERT` statements without wrapping them in explicit transactions (`BEGIN`/`COMMIT`). In SQLite, autocommit mode means each statement is its own transaction. Under high concurrency, this increases lock contention and makes it impossible to enforce atomicity across `create_run` → `create_llm_request` → `add_event` sequences. A partial failure followed by a retry can leave orphaned or duplicated rows.

6. **Proxy-Level Request Duplication**: The proxy routing traffic from OpenWebUI, benchmark runners, and agent clients may implement transparent retries on 5xx responses. If the application does not return idempotent status codes (e.g., `201 Created` with a stable `Location` header, or `409 Conflict` on duplicate keys), the proxy treats the duplicate as a new request and forwards it, compounding the DB-level issue.

# Evidence To Collect

To validate the hypotheses and guide the patch, collect the following telemetry, logs, and database artifacts:

1. **Database State Analysis**:
   ```sql
   -- Identify exact duplicates
   SELECT run_id, COUNT(*) as cnt 
   FROM runs 
   GROUP BY run_id 
   HAVING cnt > 1 
   ORDER BY cnt DESC;

   -- Check for duplicate request metadata
   SELECT request_id, run_id, COUNT(*) 
   FROM llm_requests 
   GROUP BY request_id, run_id 
   HAVING COUNT(*) > 1;

   -- Verify constraint/index state
   PRAGMA index_list('runs');
   PRAGMA index_list('llm_requests');
   ```

2. **SQLite Configuration & Concurrency Metrics**:
   ```sql
   PRAGMA journal_mode;      -- Should be WAL for concurrent reads/writes
   PRAGMA busy_timeout;      -- Default is 0; should be 3000-5000ms
   PRAGMA locking_mode;      -- Should be NORMAL or EXCLUSIVE
   PRAGMA integrity_check;   -- Rule out corruption
   ```

3. **Application Logs & Traces**:
   - Enable structured logging with correlation IDs (`trace_id`, `run_id`, `request_id`).
   - Log entry/exit of `create_run`, `create_llm_request`, `finish_llm_request`, `add_event`.
   - Capture HTTP status codes, retry counts, and SSE event boundaries.
   - Look for `SQLITE_BUSY`, `SQLITE_CONSTRAINT`, or `OperationalError` traces.

4. **Network & Proxy Telemetry**:
   - Inspect proxy access logs for duplicate `POST /runs` or `POST /requests` with identical payloads within a 2-second window.
   - Check for `502/503/504` responses followed by immediate retries.
   - Verify if OpenWebUI or agent clients implement automatic retry logic on streaming endpoints.

5. **Concurrency Stress Baseline**:
   - Run a controlled load test (e.g., `locust` or `k6`) simulating 50 concurrent streaming and non-streaming requests.
   - Monitor SQLite lock wait times and transaction commit rates.
   - Record exact duplication rate under controlled conditions to establish a baseline before patching.

# Patch Plan

The fix must enforce idempotency, transactional safety, and streaming lifecycle integrity while remaining lightweight for a home-lab environment.

1. **Enforce Idempotency at the Application Layer**:
   - Require an `idempotency_key` (or reuse `run_id`/`request_id`) in all creation endpoints.
   - Implement `INSERT OR IGNORE` or `ON CONFLICT DO NOTHING` for all metadata inserts.
   - Return `200 OK` with existing data if a duplicate key is detected, rather than `201 Created`.

2. **Explicit Transaction Management**:
   - Wrap multi-step operations (`create_run` → `create_llm_request`) in explicit transactions using `BEGIN IMMEDIATE` to reserve write locks early.
   - Use `aiosqlite` or `sqlite3` with `check_same_thread=False` and proper async context managers.
   - Example pattern:
     ```python
     async with aiosqlite.connect("benchmarks.db") as db:
         db.execute("PRAGMA journal_mode=WAL")
         db.execute("PRAGMA busy_timeout=5000")
         async with db.execute("BEGIN IMMEDIATE") as txn:
             await create_run(txn, run_id, metadata)
             await create_llm_request(txn, run_id, request_id)
             await txn.commit()
     ```

3. **Streaming Lifecycle Guardrails**:
   - Introduce a `status` column (`pending`, `streaming`, `completed`, `failed`, `cancelled`).
   - Use atomic state transitions: `UPDATE runs SET status='completed' WHERE run_id=? AND status='streaming'`.
   - Implement an `asyncio.Lock` per `run_id` to serialize `add_event` and `finish_llm_request` calls.
   - Handle client disconnects via `StreamingResponse`'s `on_disconnect` callback to mark runs as `cancelled`.

4. **Proxy & Client Coordination**:
   - Return `409 Conflict` with a JSON body containing the existing `run_id` if idempotency key collision occurs.
   - Add `Retry-After` headers on `503` responses to prevent aggressive proxy retries.
   - Document idempotency requirements for OpenWebUI and agent client integrations.

5. **Error Handling & Retry Logic**:
   - Catch `sqlite3.OperationalError` for `SQLITE_BUSY` and implement exponential backoff (max 3 retries).
   - Log constraint violations (`SQLITE_CONSTRAINT_UNIQUE`) as warnings, not errors, since they indicate successful idempotency.
   - Ensure all DB operations are wrapped in `try/finally` to release locks and close cursors.

6. **Migration Strategy**:
   - Apply schema changes via a versioned migration script.
   - Use `CREATE UNIQUE INDEX` instead of `ALTER TABLE ADD CONSTRAINT` for SQLite compatibility.
   - Backfill missing `status` values to `completed` for historical data.

# SQLite Constraints And Indexes

SQLite's performance and correctness under concurrency depend heavily on proper indexing and constraint enforcement. The following DDL and pragmas should be applied:

```sql
-- Enable WAL mode for concurrent read/write safety
PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;

-- Runs table: enforce unique run_id, track lifecycle status
CREATE TABLE IF NOT EXISTS runs (
    run_id TEXT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'streaming', 'completed', 'failed', 'cancelled')),
    metadata JSON,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- LLM Requests table: composite unique key for idempotency
CREATE TABLE IF NOT EXISTS llm_requests (
    request_id TEXT NOT NULL,
    run_id TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    prompt_tokens INTEGER DEFAULT 0,
    completion_tokens INTEGER DEFAULT 0,
    status TEXT DEFAULT 'pending',
    PRIMARY KEY (request_id, run_id)
);

-- Events table: indexed for fast retrieval, unique per request/event_type
CREATE TABLE IF NOT EXISTS events (
    event_id TEXT PRIMARY KEY,
    run_id TEXT NOT NULL,
    request_id TEXT NOT NULL,
    event_type TEXT NOT NULL,
    payload JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(request_id, event_type, created_at)
);

-- Indexes for query performance
CREATE INDEX IF NOT EXISTS idx_runs_status ON runs(status, created_at);
CREATE INDEX IF NOT EXISTS idx_requests_run_id ON llm_requests(run_id);
CREATE INDEX IF NOT EXISTS idx_events_run_id ON events(run_id, created_at);
CREATE INDEX IF NOT EXISTS idx_events_request_id ON events(request_id);
```

**Conflict Resolution Strategies**:
- Use `INSERT OR IGNORE` for idempotent creation:
  ```sql
  INSERT OR IGNORE INTO runs (run_id, metadata) VALUES (?, ?);
  ```
- Use `ON CONFLICT` for upserts (e.g., updating status):
  ```sql
  INSERT INTO runs (run_id, status) VALUES (?, 'streaming')
  ON CONFLICT(run_id) DO UPDATE SET status = excluded.status, updated_at = CURRENT_TIMESTAMP;
  ```
- Avoid `REPLACE` as it deletes and reinserts, breaking foreign key references and increasing lock contention.

**Connection Pooling & Thread Safety**:
- SQLite is not designed for high-concurrency connection pooling like PostgreSQL. For a home lab, use a single async connection per worker or `aiosqlite` with `check_same_thread=False`.
- If using Uvicorn with multiple workers, each worker should open its own database connection. Cross-worker writes are serialized by SQLite's file-level locking.

# Streaming Edge Cases

Streaming endpoints introduce unique concurrency and state management challenges. The following edge cases must be explicitly handled:

1. **Double Finish Signals**:
   - *Scenario*: Client disconnects mid-stream, proxy retries, or SSE reconnection triggers a second `finish_llm_request`.
   - *Fix*: Atomic state transition with `WHERE status = 'streaming'`. If rows affected == 0, the run is already finished/cancelled. Return `200 OK` with existing data.
   - *Code Guardrail*:
     ```python
     cursor = await db.execute(
         "UPDATE runs SET status='completed', updated_at=CURRENT_TIMESTAMP WHERE run_id=? AND status='streaming'",
         (run_id,)
     )
     if cursor.rowcount == 0:
         return {"status": "already_completed", "run_id": run_id}
     ```

2. **Event Ordering & Backpressure**:
   - *Scenario*: LLM provider streams tokens faster than SQLite can commit, causing memory buildup or dropped events.
   - *Fix*: Batch events in memory (e.g., 10-50 events) and flush asynchronously. Use `asyncio.Queue` with bounded size to apply backpressure. Flush on `finish` or disconnect.
   - *Code Guardrail*: Implement a background task that drains the queue and executes `INSERT OR IGNORE` in bulk.

3. **Client Disconnect & Orphaned Runs**:
   - *Scenario*: Client closes connection before `finish` is called, leaving run in `streaming` state indefinitely.
   - *Fix*: Use FastAPI's `StreamingResponse` with `on_disconnect` callback to mark run as `cancelled`. Implement a periodic cleanup job (e.g., every 5 minutes) to transition `streaming` runs older than 10 minutes to `failed`.
   - *SQL*: `UPDATE runs SET status='failed' WHERE status='streaming' AND updated_at < datetime('now', '-10 minutes');`

4. **SSE Reconnection & Event Duplication**:
   - *Scenario*: Client reconnects with `Last-Event-ID`, proxy forwards duplicate events.
   - *Fix*: Enforce `UNIQUE(request_id, event_type, created_at)` or use a monotonically increasing `sequence_number`. Use `INSERT OR IGNORE` to silently drop duplicates.
   - *Logging*: Log ignored duplicates at `DEBUG` level to avoid noise.

5. **Generator Cleanup & Resource Leaks**:
   - *Scenario*: Async generator raises exception, leaving DB transaction open or lock held.
   - *Fix*: Wrap generator in `try/except/finally`. Use `contextlib.asynccontextmanager` for DB connections. Ensure `db.rollback()` on exception.
   - *FastAPI Pattern*:
     ```python
     async def event_generator():
         async with db_connection() as db:
             try:
                 yield "data: init\n\n"
                 # stream events
                 yield "data: finish\n\n"
             except Exception:
                 await db.execute("UPDATE runs SET status='failed' WHERE run_id=?", (run_id,))
                 raise
     ```

# Test Plan

The following 12 acceptance tests validate idempotency, concurrency, streaming integrity, and rollback safety. Each test includes setup, execution, and assertions.

1. **Concurrent Run Creation Idempotency**
   - *Setup*: 10 concurrent tasks call `POST /runs` with identical `run_id`.
   - *Action*: Execute concurrently.
   - *Assert*: Exactly 1 row in `runs`. All requests return `200` or `201`. No `SQLITE_CONSTRAINT` errors logged as failures.

2. **Streaming Event Deduplication**
   - *Setup*: Simulate SSE stream with 50 events, inject 5 duplicate events with identical `request_id`, `event_type`, `created_at`.
   - *Action*: Process stream.
   - *Assert*: Exactly 50 unique events in `events` table. Duplicates silently ignored. No constraint violations.

3. **Double Finish Atomicity**
   - *Setup*: Create run, start streaming, call `finish_llm_request` twice concurrently.
   - *Action*: Execute both finish calls.
   - *Assert*: `runs.status` is `completed`. Exactly 1 status update recorded. Second call returns `200` with `already_completed` flag.

4. **Proxy Retry Idempotency**
   - *Setup*: Mock proxy retrying `POST /requests` with same `request_id` after simulated 503.
   - *Action*: Send initial request, wait 1s, send retry.
   - *Assert*: 1 row in `llm_requests`. Second request returns `200` with existing data. No duplicate rows.

5. **SQLite Busy Timeout & Retry**
   - *Setup*: Simulate `SQLITE_BUSY` by holding a write lock in background thread.
   - *Action*: Execute 20 concurrent inserts.
   - *Assert*: All succeed after retries. No data loss. `busy_timeout` respected. Lock wait times logged.

6. **WAL Mode Concurrent Read/Write**
   - *Setup*: 5 writers inserting runs, 10 readers querying runs concurrently.
   - *Action*: Run for 30 seconds.
   - *Assert*: No `database is locked` errors. Readers see consistent snapshots. `PRAGMA journal_mode` remains `WAL`.

7. **Client Disconnect Cleanup**
   - *Setup*: Start streaming run, close client connection before `finish`.
   - *Action*: Trigger `on_disconnect` callback.
   - *Assert*: `runs.status` transitions to `cancelled`. No orphaned `streaming` rows. Background cleanup job handles stale runs.

8. **High Concurrency Load (100 Parallel)**
   - *Setup*: 100 concurrent requests (50 streaming, 50 non-streaming).
   - *Action*: Execute load test.
   - *Assert*: 100% success rate. Zero duplicate rows. SQLite lock contention < 5%. Response times within SLA.

9. **Streaming Backpressure Handling**
   - *Setup*: Simulate slow DB commits, generate events faster than commit rate.
   - *Action*: Stream 1000 events.
   - *Assert*: Events queued, flushed in batches. No memory leak. No dropped events. Backpressure applied via bounded queue.

10. **Invalid Idempotency Key Handling**
    - *Setup*: Send request with malformed/missing `idempotency_key`.
    - *Action*: Execute request.
    - *Assert*: Returns `400 Bad Request` or `422 Unprocessable Entity`. No DB writes. Validation error logged.

11. **Schema Migration with Existing Data**
    - *Setup*: Pre-populate DB with 1000 rows lacking `status` column.
    - *Action*: Run migration script adding `status` with default `completed`.
    - *Assert*: Migration succeeds. All rows updated. Constraints applied. No data loss. Rollback script restores original schema.

12. **Rollback & Legacy Compatibility**
    - *Setup*: Deploy patch, run 50 requests. Revert code to pre-patch version.
    - *Action*: Run 50 more requests.
    - *Assert*: App functions without new constraints. Legacy code path handles existing data gracefully. No crashes on missing columns/indexes.

# Rollback Plan

A safe rollback ensures zero downtime and data integrity if the patch introduces regressions.

1. **Pre-Deployment Safeguards**:
   - Full database backup: `sqlite3 benchmarks.db ".backup benchmarks.db.bak"`
   - Export schema: `sqlite3 benchmarks.db ".schema" > schema_before.sql`
   - Tag git commit: `git tag v1.2.0-patch`

2. **Feature Flagging**:
   - Wrap new idempotency logic and streaming guards behind a config flag: `ENABLE_IDEMPOTENCY_PATCH=true`.
   - If `false`, fall back to legacy `INSERT` behavior (with warning logs).
   - Allows instant toggle without code redeployment.

3. **Rollback Procedure**:
   - Stop Uvicorn workers gracefully (`SIGTERM`).
   - Revert code: `git checkout v1.1.9`
   - Restore DB if schema migration broke compatibility: `cp benchmarks.db.bak benchmarks.db`
   - Restart workers.
   - Verify legacy endpoints respond normally.

4. **Constraint Safety**:
   - Unique indexes are additive and safe to keep even after rollback. They prevent duplicates without breaking legacy code.
   - If rollback requires dropping indexes: `DROP INDEX IF EXISTS idx_runs_unique;`
   - Monitor for `SQLITE_CONSTRAINT` errors post-rollback; they indicate legacy code attempting duplicate inserts (expected, but should be logged).

5. **Post-Rollback Validation**:
   - Run smoke tests against legacy endpoints.
   - Verify no duplicate rows are created under load.
   - Check logs for `SQLITE_BUSY` or lock contention spikes.
   - Document findings for iterative patch refinement.

# Decision Summary

The intermittent duplicate benchmark rows stem from a combination of missing idempotency guarantees, SQLite's default concurrency behavior, and unguarded streaming lifecycle transitions. The patch enforces idempotency via unique constraints and `INSERT OR IGNORE`, wraps multi-step operations in explicit `BEGIN IMMEDIATE` transactions, and introduces atomic state transitions for streaming runs. SQLite is configured for WAL mode with appropriate busy timeouts to handle concurrent proxy traffic safely. Streaming edge cases are mitigated through per-run locks, bounded event queues, and disconnect handlers. The test plan covers 12 critical scenarios including concurrency, deduplication, backpressure, and rollback compatibility. The rollback plan ensures zero downtime via feature flags, backups, and additive schema changes. This approach delivers engineering-grade reliability while remaining lightweight and maintainable for a private home-lab environment. Deploy with monitoring, validate against the acceptance tests, and iterate based on telemetry.