# How ai-memory Prevents Write Contention in Its SQLite Database: Single-Writer Architecture Explained > Learn how ai-memory prevents SQLite write contention using a single-writer architecture. Discover how a dedicated writer thread ensures database integrity and performance. - Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory) - Tags: internals - Published: 2026-09-01 --- **ai-memory eliminates SQLite write contention by funneling every mutating operation through a single, dedicated writer thread that owns the sole database connection.** Write contention is the Achilles' heel of SQLite in high-concurrency applications. The `akitaonrails/ai-memory` project solves this through a strict single-writer design that serializes all mutations while allowing concurrent reads. This article breaks down the implementation in `crates/ai-memory-store`, showing exactly how the Rust code guarantees exclusive write access without locks or retries. ## The Core Problem: SQLite's Database-Locked Error SQLite allows multiple readers but only one writer at a time. When multiple threads attempt concurrent writes, applications hit the infamous *"database is locked"* error. Traditional workarounds—retries, timeouts, or external locking—add complexity and still risk failures under load. ai-memory takes a different approach: **eliminate concurrent writers entirely by construction**. ## Single-Writer Actor Pattern The heart of ai-memory's solution is the `WriterHandle` struct in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs). This type owns the sole `rusqlite::Connection` and runs on a dedicated OS thread named `ai-memory-writer`. All write commands travel through an async `mpsc` channel as `WriteCmd` enum variants. Results return via `oneshot` channels. This guarantees that at any moment, exactly one thread holds SQLite's write lock—removing the possibility of contention at the source. ```rust // From crates/ai-memory-store/src/writer.rs // The WriterHandle owns the single Connection and receives commands via channel pub struct WriterHandle { tx: mpsc::UnboundedSender, // ... } enum WriteCmd { UpsertPage { ... }, InsertObservation { ... }, PurgeProject { ... }, Compact, MoveSession { ... }, // ... other mutations } ``` The `WriterHandle::spawn` function creates this dedicated thread and establishes the communication channel: ```rust // Simplified from writer.rs lines 82-108 pub fn spawn(conn: Connection) -> Self { let (tx, rx) = mpsc::unbounded_channel(); thread::Builder::new() .name("ai-memory-writer".to_string()) .spawn(move || { // Thread-local Connection owned exclusively here for msg in rx { let result = Self::handle_cmd(&mut conn, msg.cmd); let _ = msg.reply.send(result); } }) .expect("spawn writer thread"); WriterHandle { tx } } ``` ## Serializing Mutations Through Message Passing Every public mutation method follows the same pattern: package the request into a `WriteCmd`, send it through the channel, and await the `oneshot` response. This design serializes all writes automatically—no explicit synchronization needed. Here's how `upsert_page` works: ```rust pub async fn upsert_page(&self, page: NewPage) -> Result { let (reply_tx, reply_rx) = oneshot::channel(); self.tx .send(WriteMsg { cmd: WriteCmd::UpsertPage { page }, reply: reply_tx, }) .map_err(|_| StoreError::WriterGone)?; reply_rx.await.map_err(|_| StoreError::WriterGone)? } ``` Other mutations follow identical patterns: - **`insert_observation`** → `WriteCmd::InsertObservation` - **`purge_project`** → `WriteCmd::PurgeProject` - **`compact`** → `WriteCmd::Compact` - **`move_session`** → `WriteCmd::MoveSession` All paths converge on the single thread executing `handle_cmd`, ensuring strict FIFO ordering of writes. ## WAL Mode: Isolating Reads from Writes While writes serialize through the dedicated thread, ai-memory enables concurrent reads via SQLite's **Write-Ahead Logging (WAL) mode**. This is configured in [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs): ```rust // From lib.rs lines 22-26 conn.pragma_update(None, "journal_mode", "WAL")?; conn.pragma_update(None, "wal_autocheckpoint", 1000)?; ``` WAL mode allows read-only connections to operate without blocking on the writer. The `ReaderPool` (separate from `WriterHandle`) hands out connections for queries, achieving true read concurrency while the writer thread holds exclusive mutation access. ## Atomic Operations and Transaction Safety Because each `WriteCmd` executes entirely within the writer thread's event loop, complex operations run as single SQLite transactions. The `purge_project` and `compact` commands, for example, perform multiple table modifications atomically: ```rust // Simplified from writer.rs lines 1300-1320 fn handle_purge_project(&mut tx: Transaction, project_id: i64) -> Result<...> { // Multiple deletions within one transaction tx.execute("DELETE FROM observations WHERE page_id IN \ (SELECT id FROM pages WHERE project_id = ?1)", [project_id])?; tx.execute("DELETE FROM pages WHERE project_id = ?1", [project_id])?; tx.execute("DELETE FROM projects WHERE id = ?1", [project_id])?; tx.commit()?; } ``` No interleaving occurs—other `WriteCmd` variants wait until the current transaction completes. This provides **serializable isolation** without SQLite's `SERIALIZABLE` overhead. ## Complete Usage Example Here's how applications interact with the single-writer architecture: ```rust use ai_memory_store::{Store, WriterHandle}; #[tokio::main] async fn main() -> Result<(), ai_memory_store::StoreError> { // Open the store (creates DB, runs migrations, spawns writer thread) let store = Store::open("/path/to/data")?; // All writes route through store.writer — the single WriterHandle let page_id = store.writer.upsert_page( ai_memory_core::NewPage { workspace_id: workspace_id, project_id: project_id, path: "notes/todo.md".into(), body: "Buy milk".into(), // …other fields… }, ).await?; println!("Created page with ID {}", page_id); Ok(()) } ``` Behind `upsert_page`, the `WriteCmd::UpsertPage` travels through the `mpsc` channel to the `ai-memory-writer` thread. The response arrives via `oneshot`, giving the async caller a clean `await` interface while maintaining strict write serialization. ## Key Source Files | File | Purpose | |------|---------| | [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) | `WriterHandle` implementation, `WriteCmd` enum, all mutation methods routing through single thread | | [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs) | SQLite initialization, WAL mode configuration, `WriterHandle::spawn` invocation | | [`crates/ai-memory-wiki/src/wiki.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/wiki.rs) | Higher-level consumer showing `WriterHandle` usage without direct SQLite access | ## Summary - **Single-writer by construction**: The `WriterHandle` owns the only `rusqlite::Connection` and runs on a dedicated thread named `ai-memory-writer` - **Channel-based serialization**: All mutations become `WriteCmd` messages through an `mpsc` channel; responses return via `oneshot` - **WAL-mode concurrency**: Read-only connections operate concurrently via `ReaderPool` while writes remain strictly serialized - **Atomic transactions**: Complex operations execute as single SQLite transactions without interleaving from other writes This architecture makes write contention **impossible by design**—there is simply never more than one writer. ## Frequently Asked Questions ### Does the single-writer pattern bottleneck write throughput? Not for ai-memory's use case. The `ai-memory-writer` thread processes commands as fast as SQLite allows, and the channel buffer absorbs burst traffic. For workloads with extremely high write volumes, batching multiple operations into single `WriteCmd` variants would reduce channel overhead. ### What happens if the writer thread panics? The `oneshot` senders fail with `StoreError::WriterGone`, surfacing the failure immediately to callers. Applications must recreate the `Store` to respawn the writer thread and reestablish the database connection. ### Why not use rusqlite's bundled Connection::execute with a Mutex? A `Mutex` would serialize access but couples all callers to SQLite's synchronous API. The channel-based `WriterHandle` decouples async callers from the blocking database operations, integrating cleanly with Tokio without `spawn_blocking` overhead. ### Can readers see uncommitted writes from the writer? No. Readers use separate connections via `ReaderPool`. WAL mode provides snapshot isolation—each reader sees a consistent database state as of the start of their query, unaffected by in-flight transactions in the writer thread.