# How ai-memory's SQLite Actor Enforces Write Ordering and Prevents Race Conditions > Discover how ai-memory's SQLite actor serializes database writes using a single OS thread and rusqlite connection. Learn how this prevents race conditions and ensures data integrity. - Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory) - Tags: internals - Published: 2026-08-30 --- **ai-memory guarantees serialized database writes by running all mutations through a single dedicated OS thread that owns the sole `rusqlite::Connection`, eliminating concurrent access entirely.** The `ai-memory` project implements a strict **single-writer actor pattern** to handle SQLite mutations safely. Rather than sharing database connections across async tasks or threads, the codebase centralizes every write operation through a dedicated actor. This design prevents the "database is locked" errors common in concurrent SQLite usage and ensures total ordering of all mutations. Understanding this architecture is essential for anyone extending the store or debugging write-related issues. ## Single-Writer Thread Architecture The foundation of ai-memory's concurrency safety lies in **exclusive thread ownership** of the SQLite connection. In [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs), the `WriterHandle::spawn` function creates a thread explicitly named **"ai-memory-writer"** and moves the `rusqlite::Connection` into that thread's scope (lines 56–63). This thread then enters `worker_loop`, which runs indefinitely until a shutdown signal arrives. ```rust // Simplified conceptual flow from writer.rs lines 56-63 let handle = std::thread::Builder::new() .name("ai-memory-writer".to_string()) .spawn(move || { // Connection owned exclusively by this thread worker_loop(conn, receiver) })?; ``` No other thread ever accesses this connection. The single-writer invariant is enforced at the OS level—there is simply no code path that allows another thread to directly hold or use this `Connection` reference. ## Serialized Command Channel All mutations flow through an **async message-passing channel** that preserves FIFO ordering. Public mutating methods on `WriterHandle`—such as `insert_observation`, `upsert_page`, and `begin_session`—do not perform I/O directly. Instead, each method: 1. Creates a `oneshot::Receiver` for the reply 2. Constructs the appropriate `WriteCmd` variant 3. Sends it via `self.send(...)` to the writer thread ```rust // Example from the public API pattern pub async fn insert_observation(&self, obs: Observation) -> Result { let (tx, rx) = oneshot::channel(); self.send(WriteCmd::InsertObservation { obs, reply: tx }).await?; // Awaits the writer thread's completion signal rx.await.map_err(|_| StoreError::WriterClosed)? } ``` The `mpsc::Sender` in `worker_loop` (lines 1796–1810) guarantees that commands are **dequeued and executed in the exact order they were issued**. This provides total ordering without explicit locks. ## Atomic Execution Per Command Each `WriteCmd` processed by `worker_loop` receives **exclusive access to the connection** for its entire duration. The loop matches incoming commands and delegates to helper functions in the `ops` module: ```rust // Conceptual excerpt from worker_loop (lines 1796-1810) loop { match receiver.recv().await? { WriteCmd::InsertObservation { obs, reply } => { let result = ops::insert_observation(&mut conn, &obs); let _ = reply.send(result); } WriteCmd::UpsertPage { page, reply } => { let result = ops::upsert_page(&mut conn, &page); let _ = reply.send(result); } // ... other variants WriteCmd::Shutdown => break, } } ``` Helper functions such as `ops::insert_observation` manage their own transactions internally. Because the same `Connection` is used throughout and never shared between concurrent executions, **every command effectively runs in isolation**. This eliminates the classic race condition where multiple connections attempt to write simultaneously, triggering SQLite's busy handler or "database is locked" errors. ## Back-Pressure and Failure Transparency The **oneshot reply pattern** provides observable back-pressure and crash detection. Callers await the `oneshot::Receiver` returned by each method. If the writer thread panics or shuts down unexpectedly, the channel closes and the caller receives `StoreError::WriterClosed`: ```rust // Pattern repeated throughout WriterHandle implementation rx.await.map_err(|_| StoreError::WriterClosed)? ``` This design makes failures **explicit and immediate** rather than allowing silent data loss or indefinite blocking. Applications can detect writer unavailability and respond appropriately—typically by restarting the store or failing fast. ## Graceful Shutdown Clean termination is handled through a dedicated command and proper handle management. The `WriteCmd::Shutdown` variant breaks the `worker_loop`, allowing the thread to exit normally. The `JoinHandle` is stored inside a `Mutex>>` within `WriterHandle`, enabling the actor to be awaited and cleaned up without leaving the database in an inconsistent state. ```rust // Initiating shutdown writer.send(WriteCmd::Shutdown).await?; // The writer thread will: // 1. Process any pending commands in the channel // 2. Break the loop on Shutdown command // 3. Drop the Connection (automatically committing any open transaction) // 4. Exit cleanly ``` ## Practical Example: Using the Writer API ```rust use ai_memory_store::{Store, Observation, Page}; #[tokio::main] async fn main() -> Result<(), Box> { // Initialize store and obtain writer handle let store = Store::open("./data").await?; let writer = store.writer.clone(); // WriterHandle is Clone + Send // All mutations serialize through the dedicated thread let obs_id = writer .insert_observation(Observation::new("user query", "assistant response")) .await?; let page_id = writer .upsert_page(Page::new("https://example.com", "page content")) .await?; // Explicit shutdown for clean exit writer.send(WriteCmd::Shutdown).await?; Ok(()) } ``` ## Key Implementation Files | File | Responsibility | |------|--------------| | [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) | `WriterHandle`, `spawn`, `worker_loop`, `WriteCmd` enum | | [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs) | Public `Store` API exposing `WriterHandle` | | [`docs/CONTRIBUTING.md`](https://github.com/akitaonrails/ai-memory/blob/main/docs/CONTRIBUTING.md) | Documents the "single writer actor" design principle | | [`docs/wiki-migrations.md`](https://github.com/akitaonrails/ai-memory/blob/main/docs/wiki-migrations.md) | Migration examples using `WriterHandle` | ## Summary - **Single dedicated thread** owns the `rusqlite::Connection`, enforced at construction time in `WriterHandle::spawn` - **`mpsc` channel** provides FIFO ordering of all write commands, guaranteeing total serialization - **Oneshot replies** give callers explicit back-pressure and failure detection via `StoreError::WriterClosed` - **Per-command exclusive access** to the connection prevents "database is locked" errors without busy-waiting - **Graceful shutdown** via `WriteCmd::Shutdown` ensures clean termination and database consistency ## Frequently Asked Questions ### Does ai-memory support concurrent writer threads for higher throughput? No. The codebase enforces a strict single-writer invariant by design. All mutations serialize through one OS thread. For read-heavy workloads, the architecture typically pairs this single writer with multiple read-only connections that do not contend for locks. ### What happens if the writer thread panics? The oneshot reply channels close, causing all pending async operations to return `StoreError::WriterClosed`. The `Store` can detect this state and propagate the failure, allowing application-level recovery logic to restart the database connection if needed. ### Why use a dedicated OS thread instead of a Tokio blocking task? A dedicated thread provides clearer ownership semantics and avoids unexpected executor behavior. The thread name ("ai-memory-writer") aids debugging, and the pattern matches classic actor-model designs where the mailbox (the `mpsc` channel) and the actor (the thread) are tightly coupled. ### How does this compare to SQLite's WAL mode for concurrency? WAL mode allows concurrent reads and a single writer at the SQLite level, but ai-memory adds an **additional serialization layer** above this. Even with WAL enabled, the actor pattern guarantees that only one write operation is ever "in flight" from the application's perspective, eliminating application-level race conditions entirely.