SQLite engines run with journal_mode=DELETE, synchronous=FULL and the default busy timeout #1605

Summary

Every SQLite engine the server creates runs with SQLite's defaults: journal_mode=DELETE, synchronous=FULL, and the driver's 5 s busy timeout. The only pragma we set anywhere is foreign_keys=ON. For a server that opens several connections to the same file (one pool per store, plus multiple uvicorn workers when MEMMACHINE_WORKERS > 1), DELETE journaling means every writer blocks every reader for the duration of the write, and a reader that arrives during a long write fails with database is locked after the default timeout.

This is not a priority. Filing it so the decision is recorded and not re-derived.

Where the engines are created (speedkick @ abf92a3)

• common/resource_manager/database_manager.py:379 — relational engines from SqlAlchemyConf; enable_sqlite_foreign_keys (line 50) registers a connect listener on the sync engine that runs PRAGMA foreign_keys=ON.
• common/resource_manager/database_manager.py:799 and :843 — the sqlite_vector_store and sqlite_vec_vector_store engines, built from sqlite+aiosqlite:///{path} with no engine kwargs.
• common/vector_store/sqlite_vector_store.py:644 and common/vector_store/sqlite_vec_vector_store.py:341 — per-store connect listeners (foreign keys, sqlite-vec extension load).

Measured on main on 2026-08-28 with the shipped config: journal_mode=delete, busy_timeout=5000, synchronous=2 (FULL). synchronous stays FULL after switching to WAL; it is a separate per-connection setting.

SqlAlchemyConf has no connect_args field and its URI is assembled from parts, so there is no configuration lever today; the fix has to be code.

Proposal

Register, at the same factory-registered connect listener that already sets foreign_keys, for every SQLite engine:

PRAGMA journal_mode=WAL;      -- persistent in the file, harmless to repeat
PRAGMA busy_timeout=<ms>;     -- per connection; default 5000 is too short under contention

and decide synchronous:

• FULL (current): fsync on every commit.
• NORMAL: with WAL, durable across application crashes; only a power loss can lose the last transactions. This is the usual pairing with WAL.

The stores that are rebuildable derived data (vector stores) can take NORMAL without debate. The relational stores (episode store, segment store, semantic storage, session data) need a decision.

Open questions:

1. One shared helper applied at all three creation sites, or per-store listeners as today?
2. Expose busy_timeout_ms (and synchronous) on SqlAlchemyConf and the vector-store configs, or hard-code?
3. WAL creates -wal/-shm sidecar files next to the database; anything that copies or bind-mounts a single file (compose volumes, backups) needs to know.

History

This has been analyzed and decided three separate times (2026-05, 2026-06, 2026-08) and never landed, because Postgres is the primary target and the SQLite path was never the bottleneck being measured. Recording it here so the next time SQLite concurrency comes up we wire the listener instead of re-deriving the answer.