# Root Cause Hypotheses

The intermittent duplicate benchmark rows in a FastAPI service backed by SQLite typically stem from a combination of application-level race conditions, missing database-level guardrails, and proxy/client retry behavior. Below are the most probable root causes, ranked by likelihood and impact:

1. **Non-Atomic Check-Then-Insert in `create_run`**
   FastAPI's async request handling allows multiple concurrent requests to hit the same endpoint simultaneously. If `create_run` performs a `SELECT` to check for an existing run, then conditionally executes an `INSERT`, a classic TOCTOU (Time-of-Check-to-Time-of-Use) race condition occurs. Two concurrent requests can both see "no existing run," both proceed to insert, and SQLite will accept both rows if no `UNIQUE` constraint exists. This is the most common cause of intermittent duplicates in Python/SQLite stacks.

2. **Missing or Misconfigured `UNIQUE` Constraints**
   SQLite does not enforce uniqueness by default. If the `runs` table lacks a `UNIQUE(run_id)` or `UNIQUE(idempotency_key)` constraint, the database will happily accept duplicate inserts. Even if a `PRIMARY KEY` exists, it may be on an auto-incrementing `id` rather than the business identifier (`run_id`), leaving the logical duplicate path open.

3. **Proxy/Client Retry Logic Without Idempotency**
   OpenWebUI, benchmark runners, and agent clients often implement automatic retries on HTTP 5xx, timeouts, or network drops. If the proxy forwards these retries to the FastAPI service without propagating or generating an idempotency key, `create_run` is invoked multiple times with the same logical request. Without database-level or application-level deduplication, each retry produces a new row.

4. **Streaming Connection Lifecycle Mismatch**
   Streaming requests keep the HTTP connection open for extended periods. If the client or proxy times out mid-stream, it may retry the initial request. Meanwhile, the original request may have already committed `create_run` and `create_llm_request` to SQLite. The retry triggers a second `create_run`, resulting in duplicates. Additionally, if `finish_llm_request` is called multiple times due to stream drops, state can become inconsistent unless idempotent.

5. **SQLite Connection Sharing & Transaction Isolation**
   By default, SQLite uses `DEFERRED` transaction isolation. If the FastAPI app shares a single `sqlite3.Connection` across async tasks without proper serialization, concurrent writes can interleave. While SQLite serializes writes at the journal level, application-level logic that assumes immediate visibility of writes (e.g., checking for existence before insert) will fail under concurrency. Using `isolation_level=None` (autocommit) or improper transaction boundaries exacerbates this.

# Evidence To Collect

Before applying fixes, gather concrete evidence to validate hypotheses and measure baseline behavior. This ensures the patch targets the actual failure mode rather than symptoms.

1. **Database Schema & Journal Configuration**
   - Run `PRAGMA journal_mode;` and `PRAGMA synchronous;` to verify WAL mode and sync settings.
   - Export schema: `sqlite3 bench.db ".schema"` to check for missing `UNIQUE` constraints, foreign keys, and indexes.
   - Check `PRAGMA foreign_keys;` state (SQLite requires explicit enabling per connection).

2. **Application & Access Logs**
   - Correlate timestamps of `create_run` calls with duplicate `run_id` occurrences.
   - Look for concurrent request IDs (e.g., `X-Request-ID` or `trace_id`) that map to the same logical benchmark.
   - Identify retry patterns: HTTP 504/502 responses followed by immediate 200s on the same endpoint.

3. **Network & Proxy Traces**
   - Capture proxy logs (nginx/caddy/traefik) to count retry attempts per upstream request.
   - Verify if OpenWebUI or the agent client sends idempotency headers (`Idempotency-Key`, `X-Request-ID`).
   - Measure request latency distribution to identify timeout windows that trigger retries.

4. **Concurrency Load Metrics**
   - Run a controlled load test (e.g., `k6` or `locust`) sending 10–50 concurrent requests to the benchmark endpoint.
   - Monitor SQLite `wal` stats: `PRAGMA wal_checkpoint(TRUNCATE);` and `PRAGMA page_count;` to detect write contention.
   - Track duplicate row count vs. total requests under load.

5. **Streaming Behavior Logs**
   - Log stream start, chunk count, and stream end timestamps.
   - Identify cases where `finish_llm_request` is called multiple times for the same `request_id`.
   - Check for partial commits: `llm_requests` table shows rows without corresponding `events` or `finish` records.

6. **Connection Pool & Thread Safety Audit**
   - Review how `sqlite3.Connection` is instantiated. Is it a module-level singleton? Is `check_same_thread=False` used?
   - Verify if transactions are explicitly managed or left to autocommit.
   - Check for async context managers wrapping DB operations.

# Patch Plan

The fix must be engineering-grade (production-ready patterns) while remaining safe for a private home lab (no external dependencies, pure Python/SQLite, minimal operational overhead). The patch follows a defense-in-depth strategy: database constraints as the primary guardrail, application idempotency as secondary, and transactional boundaries for consistency.

### 1. Enforce Database-Level Uniqueness
SQLite must reject duplicates at the storage layer. This is the only reliable guarantee against race conditions.

```sql
ALTER TABLE runs ADD CONSTRAINT uq_run_id UNIQUE(run_id);
ALTER TABLE llm_requests ADD CONSTRAINT uq_request_id UNIQUE(request_id);
ALTER TABLE llm_requests ADD CONSTRAINT fk_run_id FOREIGN KEY(run_id) REFERENCES runs(run_id);
ALTER TABLE events ADD CONSTRAINT uq_run_event UNIQUE(run_id, event_type, created_at);
PRAGMA foreign_keys = ON;
```

### 2. Implement Idempotency in `create_run`
Replace check-then-insert with `INSERT OR IGNORE` or `ON CONFLICT DO NOTHING`. This is atomic and safe under concurrency.

```python
import sqlite3
from contextlib import contextmanager
from typing import Optional

@contextmanager
def get_db_connection(db_path: str):
    conn = sqlite3.connect(db_path, check_same_thread=False)
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA foreign_keys=ON;")
    conn.isolation_level = None  # Manual transaction control
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

def create_run(conn: sqlite3.Connection, run_id: str, metadata: dict) -> dict:
    """Inserts a run atomically. Returns existing or new row."""
    cursor = conn.execute(
        "INSERT OR IGNORE INTO runs (run_id, metadata, created_at) VALUES (?, ?, datetime('now'))",
        (run_id, json.dumps(metadata))
    )
    # Fetch the row (either newly inserted or pre-existing)
    row = conn.execute("SELECT * FROM runs WHERE run_id = ?", (run_id,)).fetchone()
    if not row:
        # Fallback: should not happen with INSERT OR IGNORE, but safe guard
        conn.execute(
            "INSERT INTO runs (run_id, metadata, created_at) VALUES (?, ?, datetime('now'))",
            (run_id, json.dumps(metadata))
        )
        row = conn.execute("SELECT * FROM runs WHERE run_id = ?", (run_id,)).fetchone()
    return dict(zip([d[0] for d in conn.description], row))
```

### 3. Wrap Helper Calls in Explicit Transactions
Multi-step operations (`create_run` -> `create_llm_request` -> `add_event`) must be atomic. If any step fails, the entire logical operation rolls back.

```python
def create_benchmark_session(conn: sqlite3.Connection, run_id: str, request_id: str, payload: dict):
    try:
        run = create_run(conn, run_id, payload.get("metadata", {}))
        req = create_llm_request(conn, request_id, run["id"], payload.get("model", "default"))
        add_event(conn, run["id"], "session_started", {"request_id": request_id})
        return {"run": run, "request": req}
    except Exception as e:
        conn.rollback()
        raise RuntimeError(f"Benchmark session creation failed: {e}") from e
```

### 4. Configure SQLite WAL Mode & Connection Handling
WAL (Write-Ahead Logging) enables concurrent reads and serialized writes without blocking readers. It also improves crash recovery.

```python
# In FastAPI lifespan or dependency:
def init_db():
    conn = sqlite3.connect("bench.db")
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute("PRAGMA synchronous=NORMAL;")  # Balance between safety and speed
    conn.execute("PRAGMA cache_size=-64000;")   # 64MB cache
    conn.close()
```

### 5. Add Retry-Resistant Proxy/Client Guardrails
- Propagate `X-Idempotency-Key` from upstream requests.
- If missing, generate one in the proxy or FastAPI middleware.
- Store idempotency keys in a short-lived cache (Redis/memory) for 5 minutes to prevent replay attacks while allowing safe retries.

### 6. Streaming-Safe State Management
- Use `INSERT OR IGNORE` for `create_llm_request`.
- Make `finish_llm_request` idempotent: `UPDATE llm_requests SET status='completed', updated_at=datetime('now') WHERE request_id=? AND status IN ('pending', 'running');`
- Ensure `finally` blocks call cleanup functions even on stream drops.

# SQLite Constraints And Indexes

Proper schema design is the foundation of concurrency safety. Below is the production-grade schema with constraints, indexes, and performance tuning pragmas.

### Schema Definition

```sql
CREATE TABLE IF NOT EXISTS runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id TEXT NOT NULL UNIQUE,
    metadata TEXT NOT NULL DEFAULT '{}',
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'running', 'completed', 'failed')),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS llm_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id TEXT NOT NULL UNIQUE,
    run_id TEXT NOT NULL REFERENCES runs(run_id) ON DELETE CASCADE,
    model TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'running', 'completed', 'failed', 'cancelled')),
    input_tokens INTEGER DEFAULT 0,
    output_tokens INTEGER DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id TEXT NOT NULL REFERENCES runs(run_id) ON DELETE CASCADE,
    event_type TEXT NOT NULL,
    payload TEXT NOT NULL DEFAULT '{}',
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Composite unique constraint to prevent duplicate event logging
CREATE UNIQUE INDEX IF NOT EXISTS uq_run_event ON events(run_id, event_type, created_at);
```

### Indexes for Query Performance

```sql
CREATE INDEX IF NOT EXISTS idx_runs_status_created ON runs(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_requests_run_id ON llm_requests(run_id);
CREATE INDEX IF NOT EXISTS idx_requests_status ON llm_requests(status);
CREATE INDEX IF NOT EXISTS idx_events_run_id ON events(run_id);
CREATE INDEX IF NOT EXISTS idx_events_created_at ON events(created_at DESC);
```

### Performance & Concurrency Pragmas

```sql
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-64000;
PRAGMA temp_store=MEMORY;
PRAGMA busy_timeout=5000;
PRAGMA foreign_keys=ON;
```

**Rationale:**
- `WAL` mode allows concurrent reads without blocking writers. Critical for FastAPI's async concurrency.
- `synchronous=NORMAL` reduces fsync overhead while maintaining crash safety for home lab use.
- `busy_timeout=5000` prevents `database is locked` errors under contention by retrying for 5 seconds.
- Composite indexes on `(run_id, event_type, created_at)` enforce uniqueness and optimize event lookups.
- `ON DELETE CASCADE` ensures cleanup of `llm_requests` and `events` when a `run` is removed.

# Streaming Edge Cases

Streaming introduces unique failure modes that bypass standard request/response cycles. The following edge cases must be explicitly handled:

### 1. Mid-Stream Client Timeout & Retry
**Risk:** Client times out after 30s, retries the initial request. `create_run` and `create_llm_request` execute twice.
**Mitigation:** 
- Use `INSERT OR IGNORE` with `request_id` uniqueness.
- Proxy should forward `X-Idempotency-Key`. If absent, FastAPI generates one and returns it in a response header for client retry awareness.
- Log retry detection: `SELECT COUNT(*) FROM llm_requests WHERE request_id = ?;` before insert.

### 2. Partial Stream Completion & State Inconsistency
**Risk:** Stream drops after `create_llm_request` but before `add_event` or `finish_llm_request`. DB shows `status='pending'` indefinitely.
**Mitigation:**
- Implement a background cleanup task or endpoint that transitions `pending` requests to `failed` after a timeout (e.g., 10 minutes).
- Use `UPDATE ... WHERE status='pending' AND updated_at < datetime('now', '-10 minutes')` in a scheduled job.
- Ensure `finish_llm_request` uses conditional updates: `WHERE status IN ('pending', 'running')`.

### 3. Concurrent Stream Chunks & Event Ordering
**Risk:** Multiple chunks arrive concurrently, `add_event` inserts out of order or duplicates.
**Mitigation:**
- Batch event inserts per stream segment.
- Use `INSERT OR IGNORE` with composite unique constraint `(run_id, event_type, created_at)`.
- If precise ordering is required, use `ROWID` or sequence numbers in the payload, and sort on read.

### 4. Connection Drop During Long-Running Stream
**Risk:** SQLite connection remains open, holding locks or transactions. Memory leaks or `database is locked` errors.
**Mitigation:**
- Use FastAPI's `StreamingResponse` with explicit `async with get_db_connection() as conn:` context manager.
- Register `asyncio.get_event_loop().add_signal_handler` or middleware to close connections on client disconnect.
- Set `conn.isolation_level = None` and manually commit after each logical batch to avoid long-held transactions.

### 5. Backpressure & Write Contention
**Risk:** High-throughput streaming floods SQLite writes, causing WAL bloat and checkpoint delays.
**Mitigation:**
- Implement write batching: buffer events in memory, flush every 100ms or 50 events.
- Use `PRAGMA wal_autocheckpoint=1000;` to control checkpoint frequency.
- Monitor WAL size: `SELECT page_count, wal_size FROM pragma_page_count(), pragma_wal_size();`

### 6. Idempotent Finish Logic
**Risk:** `finish_llm_request` called multiple times due to stream retries, overwriting metrics or changing status incorrectly.
**Mitigation:**
```python
def finish_llm_request(conn: sqlite3.Connection, request_id: str, metrics: dict):
    conn.execute(
        """UPDATE llm_requests 
           SET status='completed', 
               input_tokens=?, 
               output_tokens=?, 
               updated_at=datetime('now')
           WHERE request_id=? AND status IN ('pending', 'running')""",
        (metrics.get("input", 0), metrics.get("output", 0), request_id)
    )
```
This ensures only the first successful finish applies, subsequent calls are no-ops.

# Test Plan

A comprehensive acceptance test suite validates the fix under normal, concurrent, and failure conditions. All tests use `pytest`, `httpx`, and `sqlite3` with in-memory or temporary file databases.

### Test Matrix (12+ Acceptance Tests)

**T1: Concurrent `create_run` with Same Idempotency Key**
- **Setup:** 20 concurrent requests to `/benchmark/run` with identical `run_id`.
- **Action:** Execute via `asyncio.gather`.
- **Assertion:** Exactly 1 row in `runs` table. All requests return 200 with same `id`.
- **Validation:** `SELECT COUNT(*) FROM runs WHERE run_id='test-1';` == 1.

**T2: Concurrent `create_run` Without Idempotency Key**
- **Setup:** 20 concurrent requests with auto-generated unique keys.
- **Assertion:** 20 distinct rows created. No constraint violations.
- **Validation:** `COUNT(*)` == 20. All `run_id`s distinct.

**T3: Streaming Request Retry Simulation**
- **Setup:** Mock client disconnects after 2s. Proxy retries with same `X-Idempotency-Key`.
- **Action:** Simulate 3 retries.
- **Assertion:** Only 1 `llm_request` row. `status` transitions correctly.
- **Validation:** `COUNT(*) FROM llm_requests` == 1. `status` == 'completed' after stream ends.

**T4: `finish_llm_request` Idempotency**
- **Setup:** Call `finish_llm_request` 5 times concurrently for same `request_id`.
- **Assertion:** Only first call updates metrics. Subsequent calls are no-ops.
- **Validation:** `input_tokens` matches first call's value. `updated_at` not overwritten by later calls.

**T5: Transaction Rollback on Partial Failure**
- **Setup:** `create_benchmark_session` fails during `add_event` (mock DB error).
- **Assertion:** `runs` and `llm_requests` tables remain empty. No partial commits.
- **Validation:** `COUNT(*)` == 0 for both tables. Exception propagated to caller.

**T6: WAL Mode Concurrent Reads/Writes**
- **Setup:** 10 concurrent writers, 10 concurrent readers.
- **Action:** Writers insert runs, readers query `runs` table.
- **Assertion:** No `database is locked` errors. Readers see committed data.
- **Validation:** `PRAGMA journal_mode` == 'wal'. All requests succeed. Latency P95 < 50ms.

**T7: Proxy Retry with Missing Idempotency Key**
- **Setup:** Proxy retries without `X-Idempotency-Key`. FastAPI generates one.
- **Action:** Verify key is returned in response header.
- **Assertion:** Client receives key. Subsequent retries with key prevent duplicates.
- **Validation:** Response header `X-Idempotency-Key` present. DB count == 1.

**T8: Missing Constraint Fallback Enforcement**
- **Setup:** Direct SQL insert bypassing application layer with duplicate `run_id`.
- **Assertion:** SQLite raises `UNIQUE constraint failed`.
- **Validation:** Exception caught, returns 409 Conflict to client.

**T9: Streaming Timeout Cleanup**
- **Setup:** Stream times out after 30s. No `finish_llm_request` called.
- **Action:** Run cleanup job after 10m.
- **Assertion:** `status` transitions to 'failed'. No zombie rows.
- **Validation:** `SELECT status FROM llm_requests WHERE request_id='timeout-1';` == 'failed'.

**T10: High Concurrency Load Stability**
- **Setup:** 100 concurrent requests over 60s. Mixed streaming/non-streaming.
- **Action:** Monitor DB locks, duplicate count, latency.
- **Assertion:** 0 duplicates. P99 latency < 200ms. No WAL bloat > 100MB.
- **Validation:** Metrics collected via Prometheus/SQLite pragmas. All within thresholds.

**T11: Schema Migration Backward Compatibility**
- **Setup:** Apply migration adding `UNIQUE` constraints to existing DB with 10k rows.
- **Assertion:** Migration succeeds. No data loss. Existing requests unaffected.
- **Validation:** `SELECT COUNT(*)` pre/post == 10k. New inserts enforce constraints.

**T12: Event Logging Under Load**
- **Setup:** 50 concurrent runs, each emitting 10 events.
- **Action:** Verify event ordering and uniqueness.
- **Assertion:** 500 events inserted. No duplicates. Ordered by `created_at`.
- **Validation:** `COUNT(DISTINCT run_id, event_type, created_at)` == 500. No constraint violations.

### Test Execution Strategy
- Run unit tests with `pytest` + `sqlite3` in-memory DB.
- Run integration tests with temporary file DB + `httpx.AsyncClient`.
- Use `pytest-asyncio` for concurrent simulation.
- Add `--cov` for coverage. Target >90% on DB helper modules.
- Automate in CI/CD pipeline. Block merges if duplicate prevention tests fail.

# Rollback Plan

Engineering-grade fixes must include a safe, rapid rollback path. This plan ensures zero-downtime recovery if the patch introduces unexpected regressions.

### 1. Versioned Migrations & Schema Backward Compatibility
- All SQL changes are versioned (`V1__add_unique_constraints.sql`, `V2__add_indexes.sql`).
- Migrations are idempotent (`CREATE TABLE IF NOT EXISTS`, `ALTER TABLE ... ADD COLUMN IF NOT EXISTS`).
- Schema remains backward compatible: new constraints don't break existing queries. Old code continues to work until upgraded.

### 2. Feature Flag & Configuration Toggle
- Wrap new DB logic behind a feature flag: `ENABLE_IDEMPOTENCY_GUARD=1`.
- If disabled, service falls back to legacy behavior (check-then-insert).
- Flag controlled via environment variable or config file. No code redeploy needed to toggle.

### 3. Backup & Restore Procedure
- Pre-patch backup: `cp bench.db bench.db.bak.$(date +%s)`
- Post-patch snapshot: `sqlite3 bench.db "PRAGMA wal_checkpoint(TRUNCATE);"`
- Rollback command: `mv bench.db.bak.$TIMESTAMP bench.db && systemctl restart fastapi-service`
- Verify backup integrity: `sqlite3 bench.db "PRAGMA integrity_check;"`

### 4. Monitoring & Rollback Triggers
- Alert on: `duplicate_count > 0`, `db_lock_errors > 5/min`, `latency_p99 > 500ms`.
- Automated rollback: If alerts fire for >2 minutes, trigger rollback script.
- Manual rollback: `./rollback.sh --version V0 --db bench.db --service fastapi`

### 5. Gradual Rollout Strategy
- Deploy to 10% traffic via proxy routing.
- Monitor for 15 minutes. Check duplicate count, error rates, latency.
- Scale to 50%, then 100%.
- If anomalies detected, revert routing to 0% and rollback.

### 6. Communication & Runbook
- Document rollback steps in incident runbook.
- Notify team via Slack/email before deployment.
- Post-incident review if rollback triggered. Update tests/schema as needed.

# Decision Summary

The intermittent duplicate benchmark rows are caused by a combination of application-level race conditions in `create_run`, missing SQLite uniqueness constraints, and proxy/client retry behavior without idempotency keys. Streaming requests exacerbate the issue by extending the retry window and introducing partial state commits.

**Recommended Engineering Approach:**
1. **Database-Level Enforcement:** Implement `UNIQUE(run_id)` and `UNIQUE(request_id)` constraints. This is the primary guardrail and guarantees no duplicates regardless of application concurrency.
2. **Idempotent Operations:** Replace check-then-insert with `INSERT OR IGNORE` or `ON CONFLICT DO NOTHING`. Wrap multi-step helper calls in explicit transactions with manual commit/rollback.
3. **SQLite Optimization:** Enable WAL mode, set `synchronous=NORMAL`, configure `busy_timeout=5000`, and enable foreign keys. This provides production-grade concurrency safety without external dependencies.
4. **Streaming Resilience:** Make `finish_llm_request` idempotent, implement mid-stream retry detection via `X-Idempotency-Key`, and add timeout cleanup for zombie requests.
5. **Comprehensive Testing:** Validate with 12+ acceptance tests covering concurrency, retries, streaming edge cases, transaction rollback, and load stability. Automate in CI/CD.
6. **Safe Rollout:** Use versioned migrations, feature flags, and gradual traffic rollout. Maintain backup/restore procedures and monitoring triggers for instant rollback.

This solution is engineering-grade (adheres to distributed systems principles: idempotency, atomicity, defense-in-depth, observability) while remaining home-lab safe (pure Python/SQLite, no external services, minimal operational overhead). It eliminates duplicates at the storage layer, handles concurrency gracefully, and provides a clear path for validation and rollback.