# Understanding the Single-Writer SQLite Actor Pattern in ai-memory

> Discover the single-writer SQLite actor pattern for guaranteed serialized writes and atomic transactions. Learn how ai-memory enables parallel reads with ReaderPool for efficient database access.

- Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory)
- Tags: deep-dive
- Published: 2026-08-19

---

**The single-writer SQLite actor pattern routes all database mutations through a dedicated thread via a bounded mpsc channel, guaranteeing serialized writes and atomic transactions while a separate `ReaderPool` enables parallel read access.**

The `ai-memory` project stores all persistent state in an embedded SQLite database. While SQLite allows multi-threaded access when connections are thread-exclusive, supporting concurrent writers would require complex connection pooling and fine-grained locking that invites race conditions. To eliminate this complexity and ensure data integrity, `ai-memory` implements a strict **single-writer SQLite actor pattern** that centralizes every insert, update, and schema migration through one exclusive write thread.

## Core Architecture of the Actor Pattern

### The Dedicated Writer Thread and Channel

At the heart of the implementation, one dedicated thread owns the sole SQLite connection used for writing. All mutation requests—inserts, updates, deletes, and schema migrations—are serialized through a bounded `mpsc` channel to this thread. According to the source in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) (around line 439), the channel receiver is wrapped in a run loop that executes commands sequentially, ensuring that **only one SQL statement runs at a time** and that each transaction remains atomic.

### The WriterHandle Abstraction

The `WriterHandle` type provides the public facade for enqueuing operations. It is `Clone` and cheap to pass around the codebase; calling methods like `insert_page` or `purge_project` simply enqueues a command and awaits the response without touching the database connection directly. The actual SQL execution happens asynchronously on the writer thread. As implemented in [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs) (line 125), the `Store` type initializes this handle at startup and exposes it to the rest of the application.

### Parallel Reads via ReaderPool

While writes are strictly serialized, read performance is not sacrificed. The architecture employs a separate **`ReaderPool`** of lightweight SQLite connections for read-only queries. Because these connections never mutate state, they can operate in parallel across multiple threads without synchronization overhead, allowing high read-throughput while the writer thread handles consistency-critical modifications.

## Why ai-memory Uses the Single-Writer Pattern

**Thread-safety without explicit mutexes.** By serializing all writes through the actor's channel, the pattern eliminates the need for complex locking around the SQLite connection. The writer thread naturally serializes access, preventing the race conditions that plague multi-writer connection pools.

**Guaranteed atomicity and consistency.** Every mutation executes inside a single transaction on the writer thread. This design ensures the database never reaches an inconsistent intermediate state, and failures are reported back to the caller as a complete result rather than partial writes.

**Deterministic write ordering.** The order of writes matches the submission order exactly, which is critical for `ai-memory`'s event-driven ingestion pipeline and auto-improvement loops that depend on sequential processing of hooks.

**Simplified error handling.** Because the writer thread executes commands in isolation, error handling is centralized. Callers receive a single `Result` representing the complete success or failure of their operation, without needing to handle interleaved transaction conflicts.

**Maintainability.** The write side is encapsulated in a small, well-tested module (`WriterHandle`), making schema changes easier to reason about. The codebase enforces a strict rule—documented in [`crates/ai-memory-wiki/src/migrations/mod.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/migrations/mod.rs) (line 39)—that **no module is allowed to execute raw SQL outside the writer actor**.

## Implementation and Code Examples

The pattern is instantiated when the application starts. The `Store` creates the `WriterHandle`, which spawns the background thread and command loop via `WriterHandle::spawn`. The following examples demonstrate typical usage patterns found in [`crates/ai-memory-wiki/src/wiki.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/wiki.rs) (line 74) and [`crates/ai-memory-mcp/src/server.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-mcp/src/server.rs) (line 355):

```rust
// Obtain a writer handle at startup (see Store::new in lib.rs)
let store = Store::new(&config)?;
let writer = store.writer.clone();          // `WriterHandle` is `Clone`

// Insert a new page (enqueues the write, non-blocking from caller perspective)
let page_id = writer
    .insert_page(workspace_id, project_id, path, markdown)
    .await?;

// Move a session between projects atomically
writer
    .move_session(session_id, new_workspace, new_project)
    .await?;

// Purge an entire project and all related data
writer
    .purge_project(old_workspace, old_project)
    .await?;

// Execute a custom multi-statement transaction on the writer thread
writer.transaction(|tx| {
    tx.execute("INSERT INTO page …", &[])?;
    tx.execute("UPDATE meta SET …", &[])?;
    Ok(())
}).await?;

```

All calls to `WriterHandle` methods are non-blocking for the caller (they await a response), but the underlying SQLite execution happens exclusively on the writer thread. The command definitions and the channel receiver logic are located in the `WriterHandle` implementation within [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs).

## Summary

- The **single-writer SQLite actor pattern** centralizes all database mutations through one thread to eliminate race conditions.
- A **bounded mpsc channel** serializes write requests, while a separate **`ReaderPool`** allows parallel reads.
- The **`WriterHandle`** type provides a `Clone`-able, high-level API for enqueuing operations like `insert_page` and `purge_project`.
- This design guarantees **atomic transactions**, **deterministic ordering**, and **thread-safety** without explicit mutexes on the connection.
- The codebase enforces a strict policy that all SQL execution must go through the writer actor, as documented in the migrations module.

## Frequently Asked Questions

### Why not use multiple writer threads with SQLite's WAL mode?

While SQLite's Write-Ahead Logging (WAL) mode improves concurrency, it does not eliminate the complexity of coordinating transactions across multiple writer connections. The single-writer actor pattern avoids the need for application-level locking and connection pooling entirely, reducing the surface area for bugs while still allowing parallel reads through the `ReaderPool`.

### How does the bounded mpsc channel prevent resource exhaustion?

The channel used to communicate with the writer thread is bounded, meaning it has a fixed capacity. If the writer thread falls behind, backpressure propagates to callers, who will await capacity rather than enqueueing unlimited commands. This prevents unbounded memory growth under heavy write load and ensures the system degrades gracefully.

### What is the "No SQL outside the writer actor" rule?

This architectural rule, noted in [`crates/ai-memory-wiki/src/migrations/mod.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/migrations/mod.rs), mandates that no module in the codebase may execute raw SQL except through the `WriterHandle`. This constraint centralizes all mutation logic, ensures consistent transaction handling, and prevents accidental bypassing of the serialization guarantees provided by the actor pattern.

### Can the writer thread become a performance bottleneck?

For write-heavy workloads, the single writer thread does serialize operations, which limits write throughput to the speed of sequential SQLite transactions. However, this is often acceptable for `ai-memory`'s use case, which prioritizes consistency and simplicity over maximum write concurrency. Furthermore, separating reads to the `ReaderPool` ensures that query-heavy operations do not contend with writes.