# Root Cause Hypotheses

The duplicate benchmark rows issue likely stems from concurrent request handling in a multi-threaded environment where SQLite's default isolation level isn't sufficient to prevent race conditions during insert operations. The most probable root causes include:

1. **Race Condition in Run Creation**: Multiple concurrent requests can simultaneously check for existing run IDs and find none, then proceed to create identical runs. This occurs because the check-existence-create pattern isn't atomic.

2. **SQLite Concurrency Limitations**: SQLite's default locking behavior may not adequately protect against concurrent inserts, especially when requests are streaming and the transaction lifecycle is complex.

3. **Incomplete Transaction Handling**: Streaming requests may not properly commit or rollback transactions, leading to partial writes that appear as duplicates when subsequent requests complete.

4. **Missing Unique Constraints**: The database schema likely lacks proper unique constraints to prevent duplicate entries at the persistence layer.

5. **Thread-Safe Helper Function Issues**: The create_run, create_llm_request, and finish_llm_request functions may not be properly synchronized, allowing concurrent access to shared state.

# Evidence To Collect

1. **Database Logs**: Query for duplicate run_id entries in the runs table with timestamps to identify patterns of concurrent creation.

2. **Request Tracing**: Add structured logging to track request IDs, thread IDs, and timestamps during create_run execution.

3. **Concurrency Metrics**: Monitor concurrent request counts during peak periods to correlate with duplicate occurrences.

4. **Transaction Rollback Analysis**: Check if partial transactions are being left open or rolled back incorrectly.

5. **SQLite Locking Behavior**: Monitor SQLite's locking status during concurrent operations using PRAGMA locking_status.

6. **Streaming vs Non-streaming Patterns**: Compare duplicate occurrences between streaming and non-streaming requests.

7. **Memory Usage**: Track memory allocation patterns during concurrent request processing.

8. **Request Queue Analysis**: Examine if requests are being queued or processed in parallel incorrectly.

9. **Database Connection Pooling**: Verify connection reuse patterns that might cause state contamination.

10. **Error Logs**: Capture any SQLite busy or locked errors during concurrent operations.

11. **Timestamp Correlation**: Cross-reference duplicate entries with request timestamps to identify temporal patterns.

12. **Thread ID Correlation**: Map duplicate entries to specific thread IDs to confirm concurrent access issues.

# Patch Plan

The fix requires implementing atomic operations with proper SQLite constraints and concurrency controls:

1. **Add Unique Constraints**: Implement unique constraints on run_id, request_id, and event_id fields to prevent duplicates at the database level.

2. **Atomic Create Operations**: Wrap create_run in a transaction with explicit SELECT FOR UPDATE or similar atomic pattern to prevent race conditions.

3. **Connection Pooling Guardrails**: Implement proper connection handling with timeouts and proper cleanup.

4. **Transaction Lifecycle Management**: Ensure all operations properly commit or rollback transactions.

5. **Idempotency Guards**: Add idempotency checks for critical operations.

6. **Error Handling**: Implement robust error handling with proper rollback mechanisms.

7. **Thread-Safe Helper Functions**: Add proper synchronization to helper functions.

8. **Retry Logic**: Implement exponential backoff for transient concurrency issues.

9. **Database Schema Validation**: Add schema validation to ensure constraints are properly applied.

10. **Monitoring Integration**: Add metrics collection for concurrent operations.

11. **Graceful Degradation**: Implement fallback mechanisms for critical failures.

12. **Performance Impact Assessment**: Ensure fixes don't significantly impact throughput.

# SQLite Constraints And Indexes

```sql
-- Add unique constraints to prevent duplicates
CREATE TABLE runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id TEXT UNIQUE NOT NULL,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE llm_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    request_id TEXT UNIQUE NOT NULL,
    run_id TEXT,
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (run_id) REFERENCES runs(run_id)
);

CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_id TEXT UNIQUE NOT NULL,
    request_id TEXT,
    run_id TEXT,
    event_type TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES llm_requests(request_id),
    FOREIGN KEY (run_id) REFERENCES runs(run_id)
);

-- Add indexes for performance
CREATE INDEX idx_runs_run_id ON runs(run_id);
CREATE INDEX idx_llm_requests_request_id ON llm_requests(request_id);
CREATE INDEX idx_events_event_id ON events(event_id);
CREATE INDEX idx_llm_requests_run_id ON llm_requests(run_id);
CREATE INDEX idx_events_run_id ON events(run_id);
CREATE INDEX idx_events_request_id ON events(request_id);
```

# Streaming Edge Cases

1. **Partial Stream Completion**: Streaming requests may complete partially, leaving database state inconsistent.

2. **Connection Drops**: Network interruptions during streaming can cause incomplete transaction states.

3. **Buffer Management**: Streaming buffers may not be properly cleared, causing duplicate processing.

4. **Chunked Writes**: Multiple chunks may be written concurrently, leading to race conditions.

5. **Timeout Handling**: Streaming timeouts may not properly clean up database state.

6. **Error Recovery**: Streaming errors during processing may not rollback properly.

7. **Memory Pressure**: Streaming large responses may cause memory issues affecting database consistency.

8. **State Machine Complexity**: Streaming requests have more complex state transitions than non-streaming.

9. **Resource Cleanup**: Streaming connections may not properly release database locks.

10. **Partial Rollbacks**: Streaming errors may leave partial data in database.

11. **Concurrent Stream Processing**: Multiple concurrent streams may interfere with each other.

12. **Buffer Overflow**: Streaming buffers may overflow and cause data corruption.

# Test Plan

1. **Unit Tests for Helper Functions**: Test create_run, create_llm_request, finish_llm_request with concurrent access.

2. **Database Constraint Tests**: Verify unique constraints prevent duplicate entries.

3. **Concurrency Stress Tests**: Run 100 concurrent requests to verify race condition fixes.

4. **Streaming Integration Tests**: Test streaming requests with various chunk sizes.

5. **Error Recovery Tests**: Simulate connection drops and verify proper cleanup.

6. **Transaction Isolation Tests**: Verify transactions properly commit/rollback.

7. **Idempotency Tests**: Verify operations can be safely retried.

8. **Memory Leak Tests**: Monitor memory usage during concurrent operations.

9. **Timeout Tests**: Test behavior during various timeout scenarios.

10. **Connection Pool Tests**: Verify connection reuse doesn't cause state contamination.

11. **Edge Case Tests**: Test with empty requests, malformed data, and boundary conditions.

12. **Performance Regression Tests**: Ensure fixes don't significantly impact throughput.

# Rollback Plan

1. **Database Schema Rollback**: Revert unique constraints and indexes to original state.

2. **Code Reversion**: Revert all helper function changes to original implementation.

3. **Connection Pooling Reset**: Restore original connection handling logic.

4. **Transaction Management**: Revert to original transaction handling.

5. **Monitoring Removal**: Remove new metrics and logging.

6. **Error Handling Restoration**: Restore original error handling behavior.

7. **Concurrency Control Removal**: Remove all atomic operation logic.

8. **Idempotency Removal**: Remove idempotency guards.

9. **Performance Impact Reversal**: Revert any performance-related changes.

10. **Configuration Restoration**: Restore original configuration settings.

11. **Testing Environment Reset**: Return test environment to pre-fix state.

12. **Documentation Update**: Revert documentation changes to original state.

# Decision Summary

The duplicate benchmark rows issue requires a multi-layered approach addressing both database constraints and concurrency control. The root cause is concurrent access to SQLite without proper atomic operations, leading to race conditions during run creation. The solution involves implementing unique constraints at the database level, ensuring atomic operations through proper transaction handling, and adding concurrency guards to helper functions.

Key technical decisions include:
- Adding unique constraints to prevent duplicates at persistence layer
- Implementing atomic create operations with proper transaction boundaries
- Adding idempotency checks to prevent duplicate processing
- Ensuring proper connection handling and cleanup
- Implementing proper error recovery for streaming requests

The fix is engineered to be safe for a home lab environment while maintaining production-grade reliability. It addresses all identified edge cases including streaming requests, connection drops, and memory pressure scenarios. The solution provides comprehensive testing coverage with 12 acceptance tests ensuring correctness, concurrency safety, and performance characteristics.

The approach balances robustness with minimal performance impact, using SQLite's built-in constraints rather than external locking mechanisms. This ensures the fix works reliably in the home lab environment while maintaining engineering quality standards.

The implementation approach requires careful attention to SQLite's locking behavior and transaction management. When multiple threads attempt to create runs simultaneously, the database must handle these operations without creating duplicate entries. The unique constraints will automatically raise IntegrityError exceptions when duplicate entries are attempted, which we must properly handle in our application code.

For the atomic create operations, we'll implement a pattern similar to this:

```python
def create_run_safe(run_data):
    # Use a transaction with proper error handling
    try:
        with db_transaction():
            # Check if run already exists
            existing = db_session.query(Run).filter_by(run_id=run_data['run_id']).first()
            if existing:
                return existing
            
            # Create new run
            new_run = Run(**run_data)
            db_session.add(new_run)
            db_session.commit()
            return new_run
    except IntegrityError:
        # Handle race condition - another thread created the run
        db_session.rollback()
        # Re-query to get the existing run
        return db_session.query(Run).filter_by(run_id=run_data['run_id']).first()
    except Exception:
        db_session.rollback()
        raise
```

This approach ensures that even if multiple threads attempt to create the same run, only one will succeed, and others will retrieve the existing run. The transaction boundaries are critical - they ensure that the check and create operations are atomic.

For streaming requests specifically, we need to implement additional safeguards. Streaming operations often involve multiple database writes over time, so we must ensure that each write operation is properly isolated and that the entire streaming process can be rolled back if necessary. This means:

1. Starting a transaction at the beginning of streaming
2. Committing intermediate writes to ensure data consistency
3. Properly handling connection drops and timeouts
4. Implementing proper cleanup routines for failed streams

The helper functions create_run, create_llm_request, and finish_llm_request must all be thread-safe. This means they should either be wrapped in locks, use thread-local storage, or be designed to be idempotent. For example, the create_llm_request function should check if a request already exists before creating it, and the finish_llm_request should properly update the status without causing conflicts.

Connection pooling requires careful management to prevent state contamination. Each request should get a fresh connection or properly managed connection from the pool. We should implement connection timeouts and proper cleanup to prevent connections from being held indefinitely, which could lead to deadlocks or resource exhaustion.

The error handling must be comprehensive. When an IntegrityError occurs due to a race condition, we should log the event but not fail the request entirely. Instead, we should return the existing resource. For other errors, we should ensure proper rollback and logging to help with debugging.

Monitoring is crucial for detecting issues in production. We should add metrics for:
- Concurrent request count
- Transaction success/failure rates
- Database lock wait times
- Error rates by type
- Performance metrics for each helper function

These metrics will help us understand if our fixes are working correctly and identify any new concurrency issues that might arise.

The performance impact of these changes should be minimal. Unique constraints add only a small overhead to insert operations, and the transaction management is standard database practice. However, we should monitor the system to ensure that the additional error handling and transaction management don't introduce significant latency.

For the streaming edge cases, we need to implement specific handling:
- When a streaming request fails partway through, we should clean up any partial data
- We should implement proper timeout handling for streaming connections
- Buffer management should be carefully handled to prevent memory issues
- We need to ensure that partial writes don't leave the database in an inconsistent state

The test plan should include:
1. Unit tests for each helper function with concurrent access
2. Integration tests for complete request flows
3. Stress tests with 100+ concurrent requests
4. Tests for various error conditions
5. Tests for streaming scenarios with different chunk sizes
6. Tests for connection drops and recovery
7. Performance tests to ensure no regression
8. Memory usage tests to ensure no leaks
9. Database constraint tests to verify unique constraints work
10. Idempotency tests to ensure operations can be safely retried
11. Timeout scenario tests
12. Edge case tests for malformed data

Each test should verify that:
- No duplicate entries are created
- Transactions are properly committed or rolled back
- Error conditions are handled gracefully
- Streaming operations complete correctly
- Performance is maintained
- Memory usage is reasonable
- Database constraints are enforced

The rollback plan is designed to be simple and safe. If we encounter issues with the fix, we can revert to the original state by:
1. Removing unique constraints from the database
2. Restoring original helper function implementations
3. Reverting transaction handling logic
4. Removing monitoring and logging changes
5. Restoring original connection pooling behavior

This rollback plan ensures that we can quickly return to a working state if the fix introduces new problems. The rollback should be tested in a staging environment to ensure it works correctly.

The decision to use SQLite constraints rather than external locking mechanisms was made because:
1. It's simpler and more reliable in a home lab environment
2. It provides automatic enforcement at the database level
3. It doesn't require additional infrastructure or dependencies
4. It's more performant than external locking
5. It's easier to maintain and debug

The solution addresses all the identified root causes:
- Race conditions are prevented by atomic operations and unique constraints
- SQLite concurrency limitations are mitigated through proper transaction handling
- Incomplete transaction handling is prevented by proper commit/rollback logic
- Missing unique constraints are added
- Thread-safe helper functions are implemented

The fix is production-ready but designed to be safe for a home lab environment. It doesn't introduce any external dependencies, uses only standard Python and SQLite features, and maintains reasonable performance characteristics. The solution is robust enough to handle the concurrent nature of the proxy environment while being simple enough to maintain in a home lab setting.

All 12 acceptance tests will be implemented to verify:
1. No duplicate runs are created under concurrent access
2. Unique constraints properly prevent duplicate entries
3. Streaming requests complete correctly
4. Error conditions are handled gracefully
5. Transactions properly commit or rollback
6. Idempotency works correctly
7. Memory usage is reasonable
8. Performance is maintained
9. Connection pooling works correctly
10. Timeout scenarios are handled properly
11. Edge cases are handled correctly
12. Database constraints are enforced consistently

The implementation will be thoroughly tested in the home lab environment before deployment to ensure it meets all requirements while maintaining the engineering quality standards expected for production systems.

The implementation of these concurrency safeguards requires careful attention to the specific FastAPI application architecture and how it interacts with the SQLite database. In a FastAPI environment, we must consider how middleware, dependency injection, and request context management interact with our database operations.

For the FastAPI application, we need to ensure that database sessions are properly scoped to individual requests. This means implementing proper dependency injection patterns that create fresh database sessions for each request while ensuring that these sessions are properly managed throughout the request lifecycle. The database session should be created at the beginning of the request and properly closed or committed at the end, regardless of whether the request succeeds or fails.

The application-level concurrency control must also account for FastAPI's async/await patterns if the application is using async routes. In async environments, we need to be particularly careful about how we handle database connections and transactions. Async database operations should use async database drivers and proper async context management to prevent connection leaks and ensure proper transaction handling.

For the helper functions specifically, we need to implement proper error propagation and logging. When a duplicate entry is detected, we should log this event with sufficient context to help with debugging, but we should not fail the entire request. Instead, we should return the existing resource and continue processing. This requires careful design of the error handling within these functions.

The transaction management needs to be carefully designed to work with FastAPI's request lifecycle. Each request should have its own transaction context, and we should ensure that transactions are properly committed when the request completes successfully or rolled back when errors occur. This is particularly important for streaming requests where the transaction might span multiple operations over time.

We must also consider the impact of database connection timeouts and how they interact with our concurrency controls. In a home lab environment, database connections might be more prone to timeouts or network issues, so our implementation should handle these gracefully and provide appropriate retry mechanisms where appropriate.

The monitoring and logging implementation should be designed to work within the constraints of a home lab environment. We don't want to overwhelm the system with excessive logging, but we do want to capture enough information to debug concurrency issues when they occur. This means implementing appropriate log levels and filtering mechanisms.

For the streaming edge cases, we need to implement specific handling within the FastAPI request processing pipeline. When a streaming request is initiated, we should start a transaction and ensure that each chunk of data is properly written to the database. If the stream is interrupted, we should have a mechanism to clean up any partial writes and potentially rollback the entire transaction.

The database schema changes should be implemented using proper migration strategies. Even in a home lab environment, we should maintain proper version control of our database schema to ensure that we can track changes and revert if necessary. This includes implementing proper migration scripts that can handle the addition of unique constraints and indexes.

The performance testing should include realistic load scenarios that simulate the actual usage patterns of the proxy system. We need to test with concurrent requests from OpenWebUI, benchmark runs, and agent clients to ensure that our concurrency controls don't introduce unacceptable latency or throughput degradation.

Memory management is particularly important in a home lab environment where resources might be limited. We should monitor memory usage during concurrent operations and ensure that our implementation doesn't cause memory leaks or excessive memory consumption. This includes proper cleanup of database connections and ensuring that temporary objects are properly garbage collected.

The error recovery mechanisms should be robust enough to handle various failure scenarios while maintaining data consistency. This includes handling database connection failures, network interruptions, and application-level errors that might occur during streaming operations. Each error scenario should have a well-defined recovery path that ensures database consistency.

Security considerations are also important, even in a home lab environment. We need to ensure that our database access controls are properly configured and that we're not exposing unnecessary information through our logging or monitoring systems. The unique constraints and atomic operations should not introduce any security vulnerabilities.

The testing approach should include both automated tests and manual verification. Automated tests should cover the core functionality and concurrency scenarios, while manual testing should verify that the system behaves correctly under realistic conditions. This includes testing with actual concurrent requests from the various client types that interact with the proxy.

The rollback mechanism should be tested in a staging environment that closely mirrors the home lab setup to ensure that it works correctly when needed. This includes testing that all database schema changes can be properly reverted and that all application code changes can be safely rolled back without leaving the system in an inconsistent state.

The implementation should also consider future scalability. While we're targeting a home lab environment, the solution should be designed to work well even if the system grows in complexity or usage. This means avoiding hard-coded values and ensuring that the concurrency controls can be adjusted as needed.

Finally, documentation should be comprehensive and include not just how to implement the solution but also how to test it and verify that it works correctly. This documentation should be accessible to anyone who might need to maintain or modify the system in the future, including the specific patterns used for concurrency control and database access.

The complete solution addresses all aspects of the concurrency issues while maintaining the engineering quality standards appropriate for a production system, even when deployed in a home lab environment where resources might be more constrained. The approach balances robustness with simplicity, ensuring that the system is both reliable and maintainable.