# Root Cause Hypotheses

Based on the symptoms (intermittent duplicate benchmark rows) and the architecture (FastAPI, SQLite, concurrent clients like OpenWebUI and Agents), there are four primary hypotheses:

### 1. The "Check-then-Insert" Race Condition (Primary Suspect)
The current implementation likely follows a pattern where the code checks if a run exists before creating it:
`if not exists(request_id): create_run(request_id)`.
In a concurrent environment, two requests (e.g., from an Agent client retrying a timeout and a simultaneous OpenWebUI request) can both execute the "check" at the same millisecond. Both see that the record does not exist, and both proceed to execute the "insert," resulting in two rows for the same logical request.

### 2. Lack of Database-Level Unique Constraints
If the `runs` table does not have a `UNIQUE` constraint on a business-logic key (like a `request_id`, `client_mutation_id`, or a hash of the prompt/parameters), SQLite will happily accept multiple rows with identical metadata. Without a schema-level guardrail, the application logic is the only line of defense, which is insufficient for concurrent web traffic.

### 3. Client-Side Retries without Idempotency Keys
Agent clients and OpenWebUI often implement aggressive retry logic. If a request reaches the FastAPI server, the server begins `create_run`, but the network connection flickers before the server can send the 200 OK. The client retries the request. Because the server sees this as a "new" request (lacking a unique identifier), it initiates a second `create_run` call.

### 4. SQLite Locking and Transaction Isolation
SQLite's default behavior in some configurations can lead to "Database is locked" errors. If the application code catches these errors and attempts a "retry" loop without checking if the previous attempt actually succeeded in committing the row, it can lead to duplicate entries if the first attempt succeeded but the "success" signal was lost.

---

# Evidence To Collect

To confirm these hypotheses, the following data points must be gathered from the production logs and database:

1.  **Duplicate Row Audit:**
    *   Query the database for rows with identical `prompt`, `model_id`, and `timestamp` (within a 1-second window).
    *   Identify if the `id` (Primary Key) is different but the content is identical.
2.  **Request ID Correlation:**
    *   Extract the `X-Request-ID` or `trace_id` from the FastAPI logs.
    *   Check if two different `run_id`s in the database share the same `trace_id` or `client_request_id`.
3.  **Concurrency Logs:**
    *   Analyze logs for "Database is locked" or "Timeout" errors occurring at the same time as the duplicate insertions.
4.  **Network Trace:**
    *   Check the Agent client logs to see if it is issuing multiple POST requests for the same benchmark task.
5.  **SQLite Journal Mode:**
    *   Verify the current journal mode (`PRAGMA journal_mode;`). If it is `DELETE` instead of `WAL`, concurrency issues are significantly more likely.

---

# Patch Plan

The fix requires a multi-layered approach: Database Integrity, Application Idempotency, and Connection Management.

### Step 1: Database Schema Hardening
We must enforce uniqueness at the storage layer. We will introduce a `client_request_id` column. This is a UUID generated by the client (or the gateway) that represents the *intent* of the request.

```sql
-- Add a unique constraint to the runs table
-- This prevents the "Check-then-Insert" race condition at the engine level.
ALTER TABLE runs ADD COLUMN client_request_id TEXT UNIQUE;

-- If the table already exists and has data, we must clean it first:
-- DELETE FROM runs WHERE client_request_id IN (SELECT client_request_id FROM runs GROUP BY client_request_id HAVING COUNT(*) > 1);
```

### Step 2: Application Logic Update (Idempotency)
Modify `create_run` to use an "Upsert" pattern or handle the `IntegrityError` gracefully.

```python
from sqlalchemy.exc import IntegrityError
from fastapi import HTTPException

def create_run(data: RunSchema):
    try:
        # Use the client_request_id provided by the caller
        new_run = Run(
            client_request_id=data.client_request_id,
            model_id=data.model_id,
            prompt=data.prompt,
            status="started"
        )
        db.add(new_run)
        db.commit()
        return new_run
    except IntegrityError:
        # This happens if the client retries a request that already succeeded
        db.rollback()
        existing_run = db.query(Run).filter_by(client_request_id=data.client_request_id).first()
        if existing_run:
            return existing_run
        raise HTTPException(status_code=400, detail="Invalid request ID")
```

### Step 3: Connection Pooling & WAL Mode
Ensure SQLite is configured for concurrent reads/writes.

```python
# Database initialization configuration
from sqlalchemy import event
from sqlalchemy.engine import Engine

def setup_database(engine: Engine):
    @event.listens_for(engine, "connect")
    def set_sqlite_pragma(dbapi_connection, connection_record):
        cursor = dbapi_connection.cursor()
        # WAL mode allows concurrent reads and writes
        cursor.execute("PRAGMA journal_mode=WAL")
        # Synchronous NORMAL is safe for WAL mode and much faster
        cursor.execute("PRAGMA synchronous=NORMAL")
        # Set a busy timeout to wait for locks to clear
        cursor.execute("PRAGMA busy_timeout=5000")
        cursor.close()
```

---

# SQLite Constraints And Indexes

To ensure "engineering-grade" performance and safety in a home-lab environment, the following constraints are required:

### 1. Unique Constraints
*   **`client_request_id`**: Must be `UNIQUE`. This is the primary defense against duplicate rows from retries.
*   **`composite_unique_index`**: If a client cannot provide a UUID, create a unique index on `(user_id, prompt_hash, model_id)`. This prevents the same user from running the exact same benchmark twice in the same second.

### 2. Indexing Strategy
*   **`idx_run_status`**: Index on `status` (e.g., 'running', 'completed', 'failed') to speed up the cleanup of abandoned runs.
*   **`idx_run_timestamp`**: Index on `created_at` for faster dashboard rendering in OpenWebUI.

### 3. Concurrency Configuration
*   **WAL Mode**: Essential. Without `journal_mode=WAL`, a write operation locks the entire database, causing `create_llm_request` to fail or block other users.
*   **Busy Timeout**: Set to `5000ms`. This prevents the app from crashing immediately if the database is momentarily locked by a heavy write (like `add_event` during a high-frequency stream).

---

# Streaming Edge Cases

Streaming introduces unique state-management challenges that can lead to "ghost" duplicates or orphaned rows.

### 1. The "Zombie" Stream
**Scenario:** A client starts a stream, the server creates a run, but the client disconnects before the first token is sent.
**Fix:** `create_run` should initialize the row with a `started_at` timestamp. A background task or a "heartbeat" check should mark runs as `failed` or `interrupted` if no `add_event` occurs within a specific window.

### 2. Partial Stream Completion
**Scenario:** A stream is interrupted halfway. The client reconnects and tries to resume.
**Fix:** The `finish_llm_request` function must be idempotent. It should check if the `run_id` is already marked as `completed`. If it is, it should return the existing result rather than attempting to write a new completion record.

### 3. Rapid Reconnection
**Scenario:** An agent client loses connection and reconnects 3 times in 1 second.
**Fix:** The `client_request_id` must be persisted in the Agent's local memory. Even if the connection is lost, the Agent must send the *same* `client_request_id` on every retry.

---

# Test Plan

To validate the fix, the following 12 tests must be executed in a staging environment that mirrors the home-lab hardware.

### Concurrency & Idempotency Tests
1.  **Identical Concurrent Requests:** Fire 10 identical requests with the same `client_request_id` simultaneously using `httpx.AsyncClient`.
    *   *Expected:* 1 success, 9 "Already Exists" or 200 OK (returning existing record).
2.  **Rapid Retry Loop:** Simulate a network failure where the server processes the request but the client receives a timeout. The client retries 3 times.
    *   *Expected:* Only 1 row in the database.
3.  **Agent Loop Stress:** Run an agent script that attempts to trigger 50 benchmarks in 10 seconds.
    *   *Expected:* No duplicate rows; all 50 runs unique or handled by idempotency.
4.  **Database Lock Contention:** Run a heavy `add_event` loop (100 events/sec) while simultaneously attempting to `create_run`.
    *   *Expected:* No "Database is locked" errors due to `busy_timeout` and `WAL` mode.

### Streaming & State Tests
5.  **Mid-Stream Disconnect:** Start a stream and kill the client connection at 50% completion.
    *   *Expected:* Run status remains `running` or moves to `interrupted`; no duplicate run created on reconnect.
6.  **Resume Stream Logic:** Attempt to resume a stream using a `run_id` that is already marked as `completed`.
    *   *Expected:* Server returns the cached result without creating a new run.
7.  **Fast Success:** A request that completes in <100ms (e.g., a very short prompt).
    *   *Expected:* Correct row creation and immediate status update.
8.  **Large Payload Concurrency:** Send 5 concurrent requests with 4000-token prompts.
    *   *Expected:* All 5 rows created correctly; no cross-talk between `add_event` calls.

### Integrity & Schema Tests
9.  **Constraint Violation:** Attempt to manually insert a row with a duplicate `client_request_id` via a raw SQL script.
    *   *Expected:* SQLite raises `IntegrityError`.
10. **Null ID Handling:** Send a request without a `client_request_id`.
    *   *Expected:* 400 Bad Request (enforce presence of the key).
11. **WAL Mode Verification:** Query `PRAGMA journal_mode;` after app startup.
    *   *Expected:* `wal`.
12. **Rollback Verification:** Simulate a failure during `add_event` (e.g., invalid data).
    *   *Expected:* The transaction rolls back, and the `run` status remains consistent.

---

# Rollback Plan

In the event that the patch causes regressions (e.g., excessive `IntegrityError` handling overhead or WAL mode issues):

### Phase 1: Immediate Reversal (Application)
1.  Revert the `create_run` function to the original "Check-then-Insert" logic.
2.  Remove the `client_request_id` requirement from the FastAPI request validation schema.

### Phase 2: Database Restoration
1.  **If the schema was altered:** Run the following SQL to remove the unique constraint (Note: This may require dropping and recreating the column if the DB version is restrictive).
    ```sql
    -- If using a standard SQLite migration:
    -- 1. Create a temporary table without the unique constraint
    -- 2. Copy data
    -- 3. Drop old table
    -- 4. Rename temporary table
    ```
2.  **Data Cleanup:** Manually delete duplicate rows identified during the incident:
    ```sql
    DELETE FROM runs 
    WHERE id NOT IN (
        SELECT MIN(id) 
        FROM runs 
        GROUP BY prompt, model_id, created_at
    );
    ```

### Phase 3: Investigation
1.  Analyze the logs to see if the `IntegrityError` was being caught correctly.
2.  Verify if the `busy_timeout` was too low for the home-lab's disk I/O speed.

---

# Decision Summary

**Selected Solution:** **Database-level Unique Constraints + Client-side Idempotency Keys.**

**Reasoning:**
*   **Why not just fix the Python code?** Application-level checks are subject to race conditions. Even with `threading.Lock`, it doesn't protect against multiple separate processes or workers (common in FastAPI/Gunicorn setups).
*   **Why `client_request_id`?** It is the industry standard for idempotent APIs (e.g., Stripe, AWS). It allows the client to "own" the identity of the request, making retries safe.
*   **Why WAL Mode?** SQLite's default locking is too restrictive for a web service. WAL mode is the standard "engineering-grade" configuration for SQLite in production-like environments.
*   **Home-Lab Suitability:** This solution requires no extra infrastructure (like Redis for locking). It keeps the stack simple (FastAPI + SQLite) while providing the same guarantees as a distributed system.

**Impact:**
*   **Complexity:** Low (Schema change + minor logic update).
*   **Safety:** High (Prevents duplicates at the source of truth).
*   **Performance:** Neutral (Unique indexes slightly increase insert time but significantly improve data integrity).

### Detailed Implementation Deep-Dive

To move from theory to production, the following code structures should be implemented. These snippets assume the use of SQLAlchemy (or a similar ORM) and FastAPI's dependency injection system.

#### 1. Robust `create_run` with Idempotency
This implementation ensures that if a client sends the same `client_request_id` twice, the second request returns the existing record instead of throwing an error or creating a duplicate.

```python
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from typing import Optional
from fastapi import HTTPException, status
from .models import Run, RunSchema
from .database import SessionLocal

def create_run_idempotent(db: Session, data: RunSchema) -> Run:
    """
    Creates a new benchmark run or returns the existing one if the 
    client_request_id has already been processed.
    """
    # Attempt to create the run
    new_run = Run(
        client_request_id=data.client_request_id,
        model_id=data.model_id,
        prompt=data.prompt,
        status="started",
        metadata=data.metadata
    )
    
    try:
        db.add(new_run)
        db.commit()
        db.refresh(new_run)
        return new_run
    except IntegrityError as e:
        db.rollback()
        # Check if the error is specifically a unique constraint violation
        if "UNIQUE constraint failed" in str(e.orig):
            # Fetch the existing record
            existing_run = db.query(Run).filter(
                Run.client_request_id == data.client_request_id
            ).first()
            if existing_run:
                return existing_run
        
        # If it's a different integrity error, re-raise
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Database integrity error: {str(e)}"
        )
```

#### 2. Optimized `add_event` for High-Frequency Streams
During a benchmark, `add_event` might be called dozens of times per second. To prevent SQLite from becoming a bottleneck, we use a session-based approach with a small buffer or ensure the transaction is handled efficiently.

```python
from datetime import datetime
from typing import Dict, Any

def add_event(db: Session, run_id: int, event_type: str, payload: Dict[str, Any]):
    """
    Records a single event in the benchmark run.
    Uses a dedicated transaction to ensure the event is persisted.
    """
    event = RunEvent(
        run_id=run_id,
        event_type=event_type,
        payload=payload,
        timestamp=datetime.utcnow()
    )
    try:
        db.add(event)
        db.commit()
    except Exception as e:
        db.rollback()
        # Log the error but don't crash the stream; 
        # the benchmark is still running.
        print(f"Failed to log event for run {run_id}: {e}")
```

#### 3. Atomic `finish_llm_request`
This ensures that the final status, total tokens, and completion time are updated in a single atomic transaction.

```python
def finish_llm_request(db: Session, run_id: int, final_data: Dict[str, Any]):
    """
    Finalizes the benchmark run.
    Ensures that status changes from 'running' to 'completed' atomically.
    """
    run = db.query(Run).filter(Run.id == run_id).first()
    if not run:
        raise HTTPException(status_code=404, detail="Run not found")
    
    if run.status == "completed":
        return run # Already finished, return existing

    run.status = "completed"
    run.total_tokens = final_data.get("total_tokens")
    run.completion_time = final_data.get("completion_time")
    run.result_summary = final_data.get("result_summary")
    
    try:
        db.commit()
        db.refresh(run)
        return run
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail="Failed to finalize run")
```

---

# Observability and Alerting

To maintain an "engineering-grade" posture, we must move beyond "fixing the bug" to "monitoring the health" of the database and the idempotency logic.

### 1. Key Performance Indicators (KPIs)
We should expose the following metrics via a `/metrics` endpoint (Prometheus format):
*   **`db_write_latency_seconds`**: Histogram of time taken for `create_run` and `add_event`.
*   **`idempotency_hits_total`**: Counter incremented every time `create_run_idempotent` catches an `IntegrityError` and returns an existing record. This helps identify if clients are retrying excessively.
*   **`sqlite_lock_contention_count`**: Counter for times the application hits the `busy_timeout` and has to retry a transaction.
*   **`active_benchmark_runs`**: Gauge showing the number of runs currently in the `started` state.

### 2. Logging Strategy
Standardize logs to include the `client_request_id` in every log line.
*   **Level INFO**: Log the start and completion of every run, including the `client_request_id`.
*   **Level WARNING**: Log every time an `IntegrityError` is caught by the idempotency logic.
*   **Level ERROR**: Log any database connection failures or `busy_timeout` expirations.

### 3. Alerting Rules
*   **High Contention Alert**: Trigger if `sqlite_lock_contention_count` exceeds 50 per minute. This suggests the home-lab disk I/O is saturated or `add_event` is being called too frequently.
*   **Duplicate Spike Alert**: Trigger if `idempotency_hits_total` spikes suddenly, which might indicate a bug in the Agent client's retry logic.

---

# Database Migration Strategy

Since this is a production incident, we must migrate the existing SQLite database without losing data.

### Step 1: Backup
Create a manual snapshot of the current database:
`cp production.db production.db.bak`

### Step 2: Schema Update Script
Since SQLite has limited `ALTER TABLE` capabilities (e.g., you cannot easily add a `UNIQUE` constraint to an existing column without recreating the table), use the following migration pattern:

```sql
-- 1. Create a new table with the desired schema
CREATE TABLE runs_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    client_request_id TEXT UNIQUE, -- The new constraint
    model_id TEXT,
    prompt TEXT,
    status TEXT,
    metadata TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_tokens INTEGER,
    completion_time FLOAT,
    result_summary TEXT
);

-- 2. Copy data from the old table
-- Note: If duplicates already exist, this will fail. 
-- You must clean duplicates first.
INSERT INTO runs_new (id, client_request_id, model_id, prompt, status, metadata, created_at, total_tokens, completion_time, result_summary)
SELECT id, client_request_id, model_id, prompt, status, metadata, created_at, total_tokens, completion_time, result_summary
FROM runs;

-- 3. Drop the old table and rename
DROP TABLE runs;
ALTER TABLE runs_new RENAME TO runs;

-- 4. Create indexes
CREATE INDEX idx_run_status ON runs(status);
CREATE INDEX idx_run_timestamp ON runs(created_at);
```

---

# Concurrency Theory and SQLite Internals

To understand why the fix works, we must look at how SQLite handles locks.

### 1. The Lock States
SQLite uses five locking states:
1.  **UNLOCKED**: No connection is accessing the database.
2.  **SHARED**: can read but not write. Multiple connections can hold SHARED locks.
3.  **RESERVED**: A connection intends to write. Only one RESERVED lock can exist at a time, but SHARED locks can still exist.
4.  **PENDING**: A connection wants to write and is waiting for all SHARED locks to clear. No new SHARED locks are allowed.
5.  **EXCLUSIVE**: The connection is writing. No other connections can read or write.

### 2. Why WAL Mode is the Solution
In standard `DELETE` journal mode, a write operation requires an `EXCLUSIVE` lock, which blocks all readers. In **Write-Ahead Logging (WAL)** mode:
*   Readers do not block writers.
*   Writers do not block readers.
*   Only one writer can exist at a time.

By using WAL mode, the `add_event` calls (which are frequent writes) will not block the `create_run` calls (which are also writes but less frequent) or the OpenWebUI dashboard queries (which are reads).

### 3. The Role of `busy_timeout`
Even in WAL mode, two concurrent writes will result in one being blocked. The `busy_timeout` tells SQLite to wait (e.g., 5000ms) for the other write to finish before throwing a "Database is locked" error. This is critical for a home-lab where disk latency can vary.

---

# Scalability Roadmap

While SQLite is excellent for a home-lab, the owner should be aware of the "ceiling" where this architecture will fail.

### Phase 1: Current (SQLite + WAL)
*   **Capacity**: ~10-20 concurrent users, thousands of rows.
*   **Limitation**: Single-writer bottleneck. If the `add_event` frequency becomes too high (e.g., 100+ events/sec), the write queue will back up.

### Phase 2: Intermediate (PostgreSQL + SQLAlchemy)
*   **Trigger**: When the home-lab grows to multiple concurrent agents or high-frequency streaming.
*   **Benefits**: True row-level locking, much higher write throughput, and better support for complex JSONB queries on the `metadata` column.
*   **Migration Path**: SQLAlchemy makes this transition relatively easy by simply changing the connection string and the driver.

### Phase 3: Advanced (Redis + PostgreSQL)
*   **Trigger**: When real-time "Live" status updates for the UI become sluggish.
*   **Architecture**: Use Redis to store the "live" stream of events (Pub/Sub) and PostgreSQL as the persistent "Source of Truth" for completed benchmarks.

---

# Security and Input Validation

Since the app is exposed to an Agent client and OpenWebUI, we must ensure the new `client_request_id` doesn't introduce vulnerabilities.

### 1. UUID Validation
The `client_request_id` should be validated to ensure it is a valid UUID. This prevents attackers from injecting SQL or long strings into the unique index.

```python
import uuid
from fastapi import Query

def validate_request_id(request_id: str):
    try:
        uuid.UUID(request_id)
        return True
    except ValueError:
        return False

# In the FastAPI route:
@app.post("/benchmark")
async def start_benchmark(data: RunSchema = Depends(validate_request_id)):
    ...
```

### 2. SQL Injection Prevention
By using an ORM (SQLAlchemy) and parameterized queries, we are already protected against standard SQL injection. However, we must ensure that the `metadata` field (which is often a JSON string) is properly serialized and not concatenated into raw SQL strings.

---

# User Experience (UX) and Error Handling

The final piece of an engineering-grade fix is how the user perceives the failure.

### 1. Handling the 409 Conflict
If a client sends a duplicate `client_request_id` and the server returns a 200 OK (because it found the existing run), the UI should be updated to reflect the existing state.

### 2. Progress Feedback
For streaming requests, if `add_event` fails due to a database lock, the server should **not** terminate the stream. It should log the error and continue sending tokens. The user's experience of receiving the LLM response is more important than the persistence of a single intermediate event.

### 3. Cleanup Task
To prevent the SQLite database from growing indefinitely (which slows down indexes), implement a simple "Garbage Collection" script:
*   **Task**: Delete runs that are older than 30 days and have a status of `completed`.
*   **Frequency**: Run once every 24 hours via a cron job or a background task.

```python
def cleanup_old_runs(db: Session):
    # Delete runs older than 30 days
    db.query(Run).filter(Run.created_at < datetime.utcnow() - timedelta(days=30)).delete()
    db.commit()
```

This comprehensive approach addresses the immediate bug (duplicates), the underlying infrastructure (SQLite locking), the operational requirements (monitoring/migration), and the long-term growth of the home-lab project.