# How ai-memory Ensures Data Integrity with Its SQLite Writer Actor > Learn how ai-memory ensures data integrity with its SQLite writer actor. Discover how ACID compliance and serialized writes protect your data in this technical deep dive. - Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory) - Tags: how-to-guide - Published: 2026-09-12 --- **ai-memory guarantees ACID data integrity by routing every database mutation through a dedicated OS thread that owns the sole `rusqlite::Connection`, ensuring strictly serialized writes and explicit transaction isolation.** The `akitaonrails/ai-memory` repository implements a Rust-based memory store where concurrent SQLite access risks the classic "database is locked" race condition. To eliminate this hazard while preserving performance, the codebase adopts a single-writer actor pattern that confines all mutations to one exclusive thread. ## Single-Writer Actor Architecture The design contract is documented in the header comments of [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) (lines 1-8): every mutating SQL statement must flow through a single dedicated thread. By ensuring that only one writer exists at any moment, the system prevents concurrent access violations that would otherwise trigger SQLite’s busy errors or corrupt database state. This approach avoids the complexity of connection pooling or WAL-mode coordination while maintaining strict thread safety. ## Channel-Based Command Dispatch Callers interact with the writer through a `WriterHandle`, a cheap, cloneable struct defined in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) (lines 29-38). The handle holds an `Arc` containing: - An `mpsc::Sender` for dispatching commands - A join handle for the background thread This indirection allows multiple async tasks to share access without cloning the underlying SQLite connection. When a caller invokes an operation like `insert_observation`, the handle packages the request into a `WriteCmd` and pushes it into the channel. ## Spawning the Dedicated Writer Thread The `WriterHandle::spawn` method, located in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) (lines 41-48), constructs a **bounded channel with 1024 slots** to prevent unbounded memory growth under load. It then spawns the worker loop on a thread explicitly named **"ai-memory-writer"**: ```rust // Conceptual implementation based on writer.rs lines 41-48 let (tx, rx) = mpsc::channel(1024); let handle = std::thread::Builder::new() .name("ai-memory-writer".to_string()) .spawn(move || writer_loop(conn, rx))?; ``` This dedicated thread owns the `rusqlite::Connection` for its entire lifetime, satisfying SQLite’s threading requirements through exclusive ownership. ## Sequential Processing and Atomic Transactions The worker loop in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) (lines 69-73) blocks on `rx.blocking_recv()`, processing commands strictly one-after-another. Because the loop runs on a single thread, commands execute **sequentially**, preventing any concurrent access to the SQLite connection. Each `WriteCmd` delegates to functions in the `ops` module (e.g., `ops::insert_observation`, `ops::upsert_page`). As shown in lines 332-335 of [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs), these functions wrap their SQL logic in explicit transactions: ```rust // From the ops module implementation pattern pub fn insert_observation( conn: &Connection, obs: Observation ) -> Result { let tx = conn.transaction()?; // ... SQL execution ... tx.commit()?; Ok(id) } ``` This two-layer protection—serial execution plus transaction boundaries—ensures that every operation satisfies ACID properties, even if the calling async task is cancelled mid-flight. ## Request-Response and Graceful Shutdown After executing a command, the worker sends the result back via a `oneshot::Sender` embedded in the `WriteCmd` structure (lines 25-31 of [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs)). If the receiver has been dropped (e.g., the caller timed out), the writer logs a warning but continues operating, as the data has already been persisted. The system supports clean termination through a `Shutdown` command (lines 72-73). When received, the worker breaks its loop, allowing the thread to exit without leaving uncommitted transactions or dangling locks. ## Practical Usage Example Application code interacts with the SQLite writer actor through ergonomic async methods that hide the underlying channel communication: ```rust use ai_memory_store::WriterHandle; // Spawn the writer with an existing rusqlite::Connection let writer = WriterHandle::spawn(connection); // Atomic insert - serialized through the actor thread let obs_id = writer .insert_observation(sanitized_observation) .await?; // Returns StoreResult // Atomic upsert - wrapped in a SQLite transaction let page_id = writer .upsert_page(new_page) .await?; ``` If the writer thread panics or shuts down, subsequent calls return `StoreError::WriterClosed` (defined in [`crates/ai-memory-store/src/error.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/error.rs)), surfacing failure modes explicitly rather than hanging indefinitely. ## Summary - **Exclusive thread ownership**: The `rusqlite::Connection` is confined to one OS thread named "ai-memory-writer", eliminating lock contention. - **Bounded backpressure**: A 1024-slot `mpsc` channel queues commands without unbounded memory growth. - **Strict serialization**: The worker loop processes `WriteCmd` messages sequentially via `rx.blocking_recv()`, preventing concurrent writes. - **ACID transactions**: Each operation uses SQLite transactions through the `ops` module functions. - **Explicit error propagation**: `StoreError::WriterClosed` signals actor termination, preventing silent failures. ## Frequently Asked Questions ### How does ai-memory prevent the "database is locked" error? ai-memory routes all writes through a single dedicated thread that owns the sole SQLite connection. Because only one write executes at a time in the worker loop (lines 69-73 of [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs)), SQLite never encounters concurrent access violations that trigger its busy/locked state. ### What happens if the SQLite writer thread crashes? If the writer thread panics or shuts down, the `WriterHandle` detects that the channel is closed. Subsequent API calls immediately return `StoreError::WriterClosed` (defined in [`crates/ai-memory-store/src/error.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/error.rs)), preventing callers from waiting indefinitely on a dead actor. ### Why does ai-memory use a bounded channel instead of an unbounded one? The `WriterHandle::spawn` method creates a bounded channel with exactly 1024 slots ([`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs), lines 41-48). This backpressure mechanism prevents memory exhaustion when producers outpace the database; if the channel fills, senders await capacity rather than allocating unlimited queue memory. ### Does every write operation run inside a database transaction? Yes. The worker delegates commands to functions in the `ops` module (such as `insert_observation` and `upsert_page`), which explicitly wrap their SQL in `BEGIN TRANSACTION`/`COMMIT` blocks. As referenced in [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs) lines 332-335, this ensures each individual write satisfies atomicity and durability guarantees even when modifying multiple tables.