# Root Cause Hypotheses

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

1.  **Lack of Idempotency in `create_run` (Primary Suspect):**
    The `create_run` function likely performs a standard `INSERT` without checking if a request with the same unique identifier (e.g., a `request_id` or `session_id`) has already been processed. In a concurrent environment, if an agent client retries a request due to a network hiccup, or if a user double-clicks a button, the backend receives two distinct HTTP requests. Without a unique constraint on a business-logic key, the database treats these as two separate benchmark runs.

2.  **SQLite Concurrency & Transaction Isolation:**
    SQLite's default "Journal" mode handles concurrent writes by locking the entire database. If the application is using `DELETE` journal mode (the default), concurrent writes can lead to "Database is locked" errors. If the application code catches these errors and retries the logic without checking if the previous attempt actually succeeded before the lock was acquired, it can result in duplicate rows.

3.  **Streaming State Desynchronization:**
    For streaming requests, the `finish_llm_request` or `add_event` functions might be called multiple times if the stream is interrupted and resumed. If the logic does not check if a "Finished" state already exists for a specific `run_id`, a resumed stream might trigger a second "finish" logic block, potentially duplicating metadata or final status rows.

# Evidence To Collect

To confirm these hypotheses, the following data points must be gathered:

*   **Database Schema Audit:** Run `SELECT sql FROM sqlite_master WHERE name='runs';` to check for existing `UNIQUE` constraints.
*   **Log Correlation:** Extract logs for the specific `run_id`s that were duplicated. Look for two different `request_id`s (from headers) mapping to the same logical benchmark, or two identical `request_id`s hitting the server.
*   **SQLite Mode Check:** Run `PRAGMA journal_mode;` and `PRAGMA synchronous;`. If it returns `delete`, this is a high-risk configuration for concurrent FastAPI workers.
*   **Trace Analysis:** Identify if the duplicates occur during:
    *   The initial `create_run` call.
    *   The `finish_llm_request` call (indicating a streaming retry issue).
*   **Client Behavior:** Check the OpenWebUI and Agent logs to see if they are issuing "Retry" commands on 500 errors or timeouts.

# Patch Plan

The fix follows a multi-layered approach: **Database Integrity**, **Application Idempotency**, and **Concurrency Optimization**.

### Phase 1: Database Schema Hardening
Add a unique constraint to the `runs` table to prevent the database from ever accepting a duplicate "logical" run.
```sql
-- Add a unique constraint on a client-provided or generated idempotency key
ALTER TABLE runs ADD COLUMN idempotency_key TEXT UNIQUE;
-- Or, if using a specific session/request ID:
CREATE UNIQUE INDEX idx_unique_run_request ON runs (request_id);
```

### Phase 2: Application Logic Update
Modify `create_run` to use an "Upsert" or "Insert Ignore" pattern. This ensures that if a request is retried, the database handles it gracefully.

**Python (SQLAlchemy/Raw SQL) Example:**
```python
def create_run(db_session, request_id: str, metadata: dict):
    # Use ON CONFLICT to handle retries gracefully
    query = """
    INSERT INTO runs (request_id, metadata, status)
    VALUES (:request_id, :metadata, 'started')
    ON CONFLICT(request_id) DO NOTHING
    RETURNING id;
    """
    result = db_session.execute(query, {"request_id": request_id, "metadata": metadata})
    run = result.fetchone()
    
    if not run:
        # Logic to fetch the existing run if the insert was skipped
        run = db_session.execute(
            "SELECT * FROM runs WHERE request_id = :request_id", 
            {"request_id": request_id}
        ).fetchone()
    return run
```

### Phase 3: Concurrency Configuration
Switch SQLite to **Write-Ahead Logging (WAL)** mode. This allows multiple readers and one writer to operate simultaneously without blocking each other as aggressively as the default mode.

```python
# Execute during database initialization
db.execute("PRAGMA journal_mode=WAL;")
db.execute("PRAGMA synchronous=NORMAL;")
```

# SQLite Constraints And Indexes

To ensure engineering-grade stability, the following schema constraints are required:

| Constraint Type | Column(s) | Purpose |
| :--- | :--- | :--- |
| `UNIQUE` | `idempotency_key` | Prevents duplicate rows from identical client retries. |
| `UNIQUE` | `run_id` | Ensures the primary identifier is never reused. |
| `INDEX` | `(status, created_at)` | Optimizes dashboard queries for "Active" runs. |
| `INDEX` | `(request_id)` | Speeds up lookups during `finish_llm_request` updates. |
| `FOREIGN KEY` | `run_id` (in `events` table) | Ensures `add_event` cannot orphan an event to a non-existent run. |

**SQL Example for `events` table:**
```sql
CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    content TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (run_id) REFERENCES runs(id) ON DELETE CASCADE
);
CREATE INDEX idx_run_events ON events(run_id);
```

# Streaming Edge Cases

Streaming introduces specific failure modes that must be handled:

1.  **Partial Completion:** A client receives 50% of a stream and then disconnects. The `finish_llm_request` might not be called.
    *   *Fix:* Implement a "heartbeat" or a timeout in the backend that marks a run as `failed` or `interrupted` if no data is received for $X$ seconds.
2.  **Client-Side Reconnection:** Some agents (like OpenWebUI) might reconnect to a stream. If the backend sees a new connection for an existing `run_id`, it must check if the run is already `finished`.
    *   *Fix:* In `finish_llm_request`, check `if run.status == 'finished': return`.
3.  **Rapid Event Injection:** In high-throughput benchmarks, `add_event` might be called faster than SQLite can commit.
    *   *Fix:* Use a background task queue (e.g., `BackgroundTasks` in FastAPI) to batch `add_event` writes if the volume is high, or ensure `WAL` mode is active.

# Test Plan

The following 12 tests must pass in the CI/CD pipeline to validate the fix:

**Unit & Integration Tests:**
1.  `test_create_run_success`: Verify a standard run creates exactly one row.
2.  `test_create_run_duplicate_idempotency_key`: Send two identical `request_id`s; verify only one row exists.
3.  `test_create_run_different_metadata_same_key`: Verify that if a retry has different metadata, the original metadata is preserved (or handled via `ON CONFLICT DO UPDATE`).
4.  `test_add_event_foreign_key_integrity`: Attempt to add an event to a non-existent `run_id`; verify it fails.
5.  `test_finish_llm_request_idempotency`: Call `finish_llm_request` twice for the same `run_id`; verify status remains `finished`.
6.  `test_streaming_disconnect_handling`: Simulate a client disconnect during a stream; verify the run status eventually updates to `interrupted`.

**Concurrency & Load Tests:**
7.  `test_concurrent_create_run_race_condition`: Use `threading` or `asyncio.gather` to fire 10 simultaneous `create_run` requests with the same `request_id`.
8.  `test_sqlite_wal_mode_active`: Verify `PRAGMA journal_mode` returns `wal`.
9.  `test_high_frequency_add_event`: Rapidly fire 100 `add_event` calls for a single run to check for "Database Locked" errors.
10. `test_agent_retry_logic`: Simulate an agent receiving a 500 error and retrying the exact same payload.
11. `test_large_payload_metadata`: Ensure metadata blobs (e.g., 1MB+ JSON) do not crash the `create_run` logic.
12. `test_schema_migration_rollback`: Verify that the `UNIQUE` constraint can be added/removed without data loss.

# Rollback Plan

In the event of a regression (e.g., excessive `SQLITE_BUSY` errors or performance degradation):

1.  **Immediate Rollback:** Revert the application code to the previous version and point the load balancer/proxy back to the stable container.
2.  **Database Reversion:**
    *   If the `UNIQUE` constraint caused legitimate requests to fail, drop the constraint:
        `ALTER TABLE runs DROP CONSTRAINT idx_unique_run_request;` (Note: SQLite may require recreating the table if the constraint is complex).
3.  **Emergency Hotfix:** If `WAL` mode caused issues with specific filesystem drivers (rare in home labs but possible), revert to `journal_mode=DELETE` and increase the `timeout` parameter in the SQLite connection string.

# Decision Summary

*   **Primary Fix:** Implement **Idempotency Keys** at the application level and **Unique Constraints** at the database level. This is the industry standard for preventing duplicates in distributed/concurrent systems.
*   **Concurrency Strategy:** Enable **WAL Mode**. This is the most effective way to handle FastAPI's asynchronous nature with SQLite, as it allows concurrent reads and writes.
*   **Streaming Strategy:** Ensure `finish_llm_request` is idempotent by checking the current state of the run before applying final updates.
*   **Engineering Grade:** This solution avoids "quick fixes" (like just adding a sleep timer) and instead addresses the root architectural weakness (lack of uniqueness guarantees).

### Detailed Implementation Guide

To move from a conceptual fix to a production-ready deployment, the following implementation details provide the necessary boilerplate and architectural patterns.

#### 1. Database Connection Manager
In a FastAPI environment, managing the SQLite connection pool and ensuring `WAL` mode is active is critical. Using `SQLAlchemy` with `aiosqlite` is the standard for asynchronous SQLite interaction.

```python
import logging
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
from sqlalchemy import text

# Configuration
DATABASE_URL = "sqlite+aiosqlite:///benchmark_data.db"

# Engine configuration for SQLite
# 'connect_args' is used to pass specific SQLite parameters
engine = create_async_engine(
    DATABASE_URL,
    connect_args={"check_same_thread": False},
    echo=False,
    # This ensures that the connection handles busy states gracefully
    # by waiting up to 30 seconds before throwing a "Database is locked" error.
    connect_timeout=30 
)

async def init_db():
    """Initialize the database with WAL mode and necessary indexes."""
    async with engine.begin() as conn:
        # Enable Write-Ahead Logging
        await conn.execute(text("PRAGMA journal_mode=WAL;"))
        # Set synchronous to NORMAL for a balance of safety and speed in WAL mode
        await conn.execute(text("PRAGMA synchronous=NORMAL;"))
        # Set a busy timeout
        await conn.execute(text("PRAGMA busy_timeout=30000;"))
        
        # Ensure the runs table has the unique constraint
        # This is a safety net for the application logic
        await conn.execute(text("""
            CREATE TABLE IF NOT EXISTS runs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                request_id TEXT UNIQUE,
                idempotency_key TEXT UNIQUE,
                metadata JSON,
                status TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                finished_at TIMESTAMP
            );
        """))
        await conn.commit()

AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_db():
    async with AsyncSessionLocal() as session:
        yield session
```

#### 2. Idempotent `create_run` Logic
The application logic must handle the case where a client retries a request. By using the `idempotency_key` (which should be generated by the client or a proxy like Nginx/Traefik), we can ensure that the database rejects the second attempt while the application returns the existing record.

```python
from fastapi import HTTPException, Depends
from sqlalchemy.future import select
from sqlalchemy.dialects.sqlite import insert

async def create_run(
    db: AsyncSession = Depends(get_db),
    request_id: str,
    idempotency_key: str,
    metadata: dict
):
    """
    Creates a new benchmark run or returns the existing one if the 
    idempotency_key has already been processed.
    """
    # Use SQLite's ON CONFLICT clause
    stmt = insert(Run).values(
        request_id=request_id,
        idempotency_key=idempotency_key,
        metadata=metadata,
        status="started"
    ).on_conflict_do_nothing(index_elements=['idempotency_key'])
    
    result = await db.execute(stmt)
    await db.commit()

    # If no row was inserted (due to conflict), fetch the existing one
    if result.rowcount == 0:
        logging.info(f"Duplicate request detected for key: {idempotency_key}")
        query = select(Run).where(Run.idempotency_key == idempotency_key)
        result = await db.execute(query)
        run = result.scalar_one_or_none()
        if not run:
            # This handles the edge case where a row was deleted 
            # or the conflict was on a different index
            raise HTTPException(status_code=409, detail="Conflict detected")
        return run
    
    return result.scalar_one()
```

#### 3. Background Event Logging
To prevent `add_event` from blocking the main request thread (especially during high-frequency streaming), use FastAPI's `BackgroundTasks`.

```python
from fastapi import BackgroundTasks

async def add_event_task(run_id: int, event_type: str, content: str):
    """Background worker to persist events without blocking the stream."""
    async with AsyncSessionLocal() as db:
        new_event = Event(run_id=run_id, event_type=event_type, content=content)
        db.add(new_event)
        await db.commit()

@app.post("/run/{run_id}/event")
async def log_event(
    run_id: int, 
    event_type: str, 
    content: str, 
    background_tasks: BackgroundTasks
):
    # Offload the DB write to a background task
    background_tasks.add_task(add_event_task, run_id, event_type, content)
    return {"status": "queued"}
```

# Observability and Alerting

To maintain an engineering-grade system, "silent" failures or duplicates must be visible via monitoring.

### 1. Structured Logging
Replace standard print statements with structured JSON logging. This allows for easy parsing by ELK or Loki.

**Log Schema:**
```json
{
  "timestamp": "2023-10-27T10:00:00Z",
  "level": "WARNING",
  "event": "duplicate_request_ignored",
  "request_id": "req_123",
  "idempotency_key": "key_456",
  "action": "on_conflict_do_nothing",
  "message": "Client retried a request that was already processed."
}
```

### 2. Prometheus Metrics
Expose the following metrics to monitor the health of the database and the idempotency logic:

*   `benchmark_duplicate_requests_total`: Counter incremented whenever `on_conflict_do_nothing` is triggered.
*   `benchmark_db_write_latency_seconds`: Histogram tracking the time taken for `create_run` and `add_event`.
*   `benchmark_sqlite_busy_errors_total`: Counter for `SQLITE_BUSY` errors (indicates WAL mode or `busy_timeout` issues).
*   `benchmark_active_runs`: Gauge showing the number of runs with status `started`.

### 3. Alerting Rules
*   **High Duplicate Rate:** Alert if `benchmark_duplicate_requests_total` exceeds 10% of total requests in a 5-minute window (indicates a buggy client or agent).
*   **Database Lock Alert:** Alert if `benchmark_sqlite_busy_errors_total` > 0.
*   **Stale Runs:** Alert if a run remains in `started` status for > 30 minutes without an `add_event` call.

# Migration and Data Cleanup

Before deploying the patch, the existing database must be cleaned to ensure the new `UNIQUE` constraints do not fail during the migration.

### 1. Data Audit Script
Run this script to identify existing duplicates:
```sql
SELECT idempotency_key, COUNT(*) 
FROM runs 
GROUP BY idempotency_key 
HAVING COUNT(*) > 1;
```

### 2. Cleanup Strategy
For every duplicate found:
1.  Identify the "Primary" row (the one with the earliest `created_at`).
2.  Identify the "Duplicate" row.
3.  Update the `idempotency_key` of the duplicate to a unique value (e.g., `key_456_dup_1`) or delete it if it contains no unique data.

### 3. Migration Steps
1.  **Backup:** `cp benchmark_data.db benchmark_data.db.bak`
2.  **Cleanup:** Run the cleanup script to remove/rename duplicates.
3.  **Schema Update:**
    ```sql
    -- If the column doesn't exist
    ALTER TABLE runs ADD COLUMN idempotency_key TEXT;
    -- Create the unique index
    CREATE UNIQUE INDEX idx_unique_idempotency ON runs(idempotency_key);
    ```
4.  **Verify:** Run `SELECT count(*) FROM runs WHERE idempotency_key IS NULL;` to ensure all rows are covered.

# Advanced Concurrency & SQLite Internals

Understanding why SQLite behaves this way is key to maintaining the system.

### 1. The "Single Writer" Limitation
Even with WAL mode, SQLite only allows one writer at a time. In a FastAPI app with multiple workers (e.g., Gunicorn with `uvicorn.workers.UvicornWorker`), multiple processes might attempt to write simultaneously. 

**The Solution:**
*   **WAL Mode:** Allows readers to proceed while a writer is active.
*   **Busy Timeout:** The `PRAGMA busy_timeout=30000` tells SQLite to wait up to 30 seconds for the lock to clear before returning an error. This is essential for handling the "bursty" nature of agent requests.

### 2. Connection Pooling
In an async environment, using a single global connection can lead to "thread-safety" issues or "database is locked" errors because the same connection object is shared across concurrent tasks.
*   **Best Practice:** Use a connection pool (like the one provided by `SQLAlchemy`'s `AsyncSession`) where each request gets its own connection from the pool, and the pool manages the lifecycle of those connections.

### 3. Filesystem Considerations
Since this is a home lab, the underlying filesystem matters:
*   **Ext4/XFS:** Excellent support for WAL mode.
*   **NFS/SMB:** **DO NOT** use SQLite on a network share with WAL mode. Network filesystems often have buggy locking mechanisms that will lead to database corruption. If the lab uses a NAS, the database should reside on a local SSD.

# Agent-Specific Protocol Handling

Clients like OpenWebUI and custom agents have specific behaviors that can trigger the duplicate issue.

### 1. OpenWebUI Behavior
OpenWebUI often uses Server-Sent Events (SSE). If the connection drops, the frontend might automatically attempt to reconnect.
*   **Fix:** The backend must check the `request_id` or `idempotency_key` provided by the frontend. If the run is already `finished`, the backend should return the final result immediately rather than starting a new run.

### 2. Agent Retry Logic
Agents (like LangChain or AutoGPT) often have built-in retry logic. If an agent receives a 504 Gateway Timeout (common in long-running benchmarks), it will retry the exact same payload.
*   **Fix:** The agent must be configured to generate a unique `idempotency_key` for each *logical* task, not each *network* attempt. This key must be passed in the headers.

# Extended Test Plan

To ensure 100% reliability, we expand the test suite to 24 scenarios.

**Unit & Integration Tests (1-12):**
1.  `test_create_run_success`: Verify a standard run creates exactly one row.
2.  `test_create_run_duplicate_idempotency_key`: Send two identical `request_id`s; verify only one row exists.
3.  `test_create_run_different_metadata_same_key`: Verify that if a retry has different metadata, the original metadata is preserved.
4.  `test_add_event_foreign_key_integrity`: Attempt to add an event to a non-existent `run_id`; verify it fails.
5.  `test_finish_llm_request_idempotency`: Call `finish_llm_request` twice for the same `run_id`; verify status remains `finished`.
6.  `test_streaming_disconnect_handling`: Simulate a client disconnect during a stream; verify the run status eventually updates to `interrupted`.
7.  `test_concurrent_create_run_race_condition`: Use `asyncio.gather` to fire 10 simultaneous `create_run` requests with the same `request_id`.
8.  `test_sqlite_wal_mode_active`: Verify `PRAGMA journal_mode` returns `wal`.
9.  `test_high_frequency_add_event`: Rapidly fire 100 `add_event` calls for a single run to check for "Database Locked" errors.
10. `test_agent_retry_logic`: Simulate an agent receiving a 500 error and retrying the exact same payload.
11. `test_large_payload_metadata`: Ensure metadata blobs (e.g., 1MB+ JSON) do not crash the `create_run` logic.
12. `test_schema_migration_rollback`: Verify that the `UNIQUE` constraint can be added/removed without data loss.

**Stress & Edge Case Tests (13-24):**
13. `test_max_concurrent_writers`: Simulate 50 concurrent workers attempting to write to the DB simultaneously to test `busy_timeout`.
14. `test_db_file_lock_recovery`: Manually lock the `.db` file and verify the app waits and eventually succeeds or fails gracefully.
15. `test_idempotency_key_collision`: Verify that two different users with different `request_id`s but the same `idempotency_key` (if allowed) are handled correctly.
16. `test_metadata_json_serialization`: Ensure complex nested JSON objects in metadata are correctly serialized/deserialized.
17. `test_finish_llm_request_with_no_events`: Verify a run can finish even if zero events were logged.
18. `test_rapid_status_transitions`: Rapidly toggle a run from `started` -> `processing` -> `finished` to check for race conditions.
19. `test_long_running_stream_timeout`: Verify that a stream taking > 10 minutes is flagged for manual review or auto-timeout.
20. `test_multiple_agents_same_run`: Verify that two different agents cannot "claim" the same `run_id` simultaneously.
21. `test_sqlite_vacuum_performance`: Measure the time taken to `VACUUM` the database with 100,000 rows.
22. `test_connection_pool_exhaustion`: Verify the app handles cases where all DB connections are in use.
23. `test_partial_metadata_update`: Verify that updating metadata for an existing run doesn't overwrite the `idempotency_key`.
24. `test_zero_byte_event_content`: Ensure `add_event` handles empty strings or null content gracefully.

# Infrastructure & Filesystem Constraints

For a home lab environment, the following infrastructure rules apply:

1.  **Disk Type:** Use an SSD (NVMe preferred). SQLite performance in WAL mode is significantly better on flash storage due to lower seek times during journal commits.
2.  **Permissions:** Ensure the user running the FastAPI process has exclusive write access to the `.db` file and the `-wal` and `-shm` files. If another process (like a backup script) locks the file, the app will hang.
3.  **Backup Strategy:** Since WAL mode creates `-wal` and `-shm` files, a simple `cp` of the `.db` file is insufficient. Use the SQLite `backup` API or `VACUUM INTO` to create a consistent backup.
4.  **Containerization:** If running in Docker, ensure the volume is mounted as a local path, not a network-mounted volume (NFS/SMB), to avoid locking issues.

# Long-term Maintenance & Optimization

To keep the system "engineering-grade" over time:

### 1. Periodic Maintenance
*   **Vacuuming:** Schedule a weekly `VACUUM;` command to reclaim space from deleted rows and defragment the database.
*   **Analyze:** Run `ANALYZE;` periodically to update the query planner's statistics on the `runs` and `events` tables.

### 2. Index Management
As the `events` table grows, the `idx_run_events` index will become large. Monitor the size of the database. If it exceeds 1GB, consider partitioning the `events` table by month or moving older events to a "cold" archive table.

### 3. Query Optimization
Ensure all queries in `finish_llm_request` and `add_event` use the primary keys or indexed columns. Avoid `SELECT *` in production code; explicitly select the columns needed to reduce I/O overhead.

### 4. Summary of Changes
*   **Database:** Switched to WAL mode, added `busy_timeout`, and implemented `UNIQUE` constraints on `idempotency_key`.
*   **Application:** Implemented `ON CONFLICT` logic in `create_run` and moved `add_event` to background tasks.
*   **Observability:** Added structured logging and Prometheus metrics for duplicate detection and DB health.
*   **Reliability:** Added a 24-point test suite covering concurrency, streaming, and agent-specific retries.