UAT: SQLite WAL mode not configured — spec requires WAL mode for concurrent reads during plan execution #3963

New issue

Open

opened 2026-04-06 07:55:37 +00:00 by freemo · 0 comments

freemo commented

2026-04-06 07:55:37 +00:00

Owner

Bug Report

Feature Area: Infrastructure and Database Layer
Severity: Medium — performance and concurrency degradation during plan execution
Found by: UAT tester (code analysis)

What Was Tested

Code analysis of src/cleveragents/infrastructure/database/unit_of_work.py and src/cleveragents/infrastructure/database/migration_runner.py for SQLite WAL mode configuration.

Expected Behavior (from spec)

The specification explicitly states (section "Technology Stack"):

SQLite — Primary database. Zero-configuration, file-based, ACID-compliant. Sufficient for local mode; supports WAL mode for concurrent reads during plan execution.

The filesystem layout section also shows:

~/.cleveragents/
├── cleveragents.db                # SQLite database (WAL mode)
├── cleveragents.db-wal            # WAL file (auto-managed by SQLite)
├── cleveragents.db-shm            # Shared memory file (auto-managed)

WAL (Write-Ahead Logging) mode is required to allow concurrent reads while a write transaction is in progress — critical for plan execution where the strategy/execution actors read the database while the plan lifecycle service writes to it.

Actual Behavior

Neither unit_of_work.py nor migration_runner.py configures WAL mode. The SQLite engine is created without any PRAGMA journal_mode=WAL setting:

# unit_of_work.py lines 91-97 — no WAL mode
self._engine = create_engine(
    self.database_url,
    echo=False,
    future=True,
    isolation_level="SERIALIZABLE",
    connect_args={"check_same_thread": False},
    # ❌ Missing: PRAGMA journal_mode=WAL
)

SQLite defaults to DELETE journal mode, which uses exclusive locks and prevents concurrent reads during writes. This means:

During plan execution (write operations), read queries from actors will block
The .db-wal and .db-shm files mentioned in the spec will never be created
Concurrent access patterns expected by the spec will fail

Code Location

src/cleveragents/infrastructure/database/unit_of_work.py (lines 88-103)
src/cleveragents/infrastructure/database/migration_runner.py (lines 244-264)

Fix Required

Enable WAL mode when creating SQLite engines using SQLAlchemy's connect_args and an event listener:

from sqlalchemy import event

engine = create_engine(
    self.database_url,
    echo=False,
    future=True,
    isolation_level="SERIALIZABLE",
    connect_args={"check_same_thread": False},
)

# Enable WAL mode for concurrent reads
@event.listens_for(engine, "connect")
def set_sqlite_pragma(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA journal_mode=WAL")
    cursor.close()

This should be applied in both unit_of_work.py (for the main engine) and migration_runner.py (for the migration engine) for file-based SQLite databases. In-memory databases (:memory:) do not need WAL mode.

Automated by CleverAgents Bot
Supervisor: UAT Testing | Agent: ca-uat-tester

## Bug Report **Feature Area:** Infrastructure and Database Layer **Severity:** Medium — performance and concurrency degradation during plan execution **Found by:** UAT tester (code analysis) --- ## What Was Tested Code analysis of `src/cleveragents/infrastructure/database/unit_of_work.py` and `src/cleveragents/infrastructure/database/migration_runner.py` for SQLite WAL mode configuration. ## Expected Behavior (from spec) The specification explicitly states (section "Technology Stack"): > **SQLite** — Primary database. Zero-configuration, file-based, ACID-compliant. Sufficient for local mode; supports **WAL mode for concurrent reads during plan execution**. The filesystem layout section also shows: ``` ~/.cleveragents/ ├── cleveragents.db # SQLite database (WAL mode) ├── cleveragents.db-wal # WAL file (auto-managed by SQLite) ├── cleveragents.db-shm # Shared memory file (auto-managed) ``` WAL (Write-Ahead Logging) mode is required to allow concurrent reads while a write transaction is in progress — critical for plan execution where the strategy/execution actors read the database while the plan lifecycle service writes to it. ## Actual Behavior Neither `unit_of_work.py` nor `migration_runner.py` configures WAL mode. The SQLite engine is created without any `PRAGMA journal_mode=WAL` setting: ```python # unit_of_work.py lines 91-97 — no WAL mode self._engine = create_engine( self.database_url, echo=False, future=True, isolation_level="SERIALIZABLE", connect_args={"check_same_thread": False}, # ❌ Missing: PRAGMA journal_mode=WAL ) ``` SQLite defaults to DELETE journal mode, which uses exclusive locks and prevents concurrent reads during writes. This means: - During plan execution (write operations), read queries from actors will block - The `.db-wal` and `.db-shm` files mentioned in the spec will never be created - Concurrent access patterns expected by the spec will fail ## Code Location - `src/cleveragents/infrastructure/database/unit_of_work.py` (lines 88-103) - `src/cleveragents/infrastructure/database/migration_runner.py` (lines 244-264) ## Fix Required Enable WAL mode when creating SQLite engines using SQLAlchemy's `connect_args` and an event listener: ```python from sqlalchemy import event engine = create_engine( self.database_url, echo=False, future=True, isolation_level="SERIALIZABLE", connect_args={"check_same_thread": False}, ) # Enable WAL mode for concurrent reads @event.listens_for(engine, "connect") def set_sqlite_pragma(dbapi_connection, connection_record): cursor = dbapi_connection.cursor() cursor.execute("PRAGMA journal_mode=WAL") cursor.close() ``` This should be applied in both `unit_of_work.py` (for the main engine) and `migration_runner.py` (for the migration engine) for file-based SQLite databases. In-memory databases (`:memory:`) do not need WAL mode. --- **Automated by CleverAgents Bot** Supervisor: UAT Testing | Agent: ca-uat-tester

freemo referenced this issue

2026-04-06 07:59:14 +00:00

[Automated] CleverAgents Build Session - 2026-04-06 #3775

freemo referenced this issue

2026-04-06 08:09:29 +00:00

[Automated] CleverAgents Build Session - 2026-04-06 #3775

freemo referenced this issue

2026-04-06 10:35:53 +00:00

[Automated] CleverAgents Build Session - 2026-04-06 #3775