# How the Single-Writer SQLite Actor Pattern Works in ai-memory-store

> Discover how the single-writer SQLite actor pattern in ai-memory-store serializes mutations via an OS thread and uses a reader pool for concurrent queries. Learn efficient SQLite management.

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

---

**The single-writer SQLite actor pattern in ai-memory-store serializes all database mutations through a dedicated OS thread that owns the sole `rusqlite::Connection`, while a separate reader pool enables concurrent read-only queries.**

This architecture eliminates SQLite's notorious "database is locked" errors by enforcing strict single-threaded access for writes. The pattern is implemented across three core components in the `akitaonrails/ai-memory` repository: a dedicated writer thread, a cloneable `WriterHandle`, and an independent `ReaderPool`.

## Core Architecture: Three Components

The single-writer SQLite actor pattern divides database access into specialized parts:

| Component | Responsibility | Source File |
|-----------|--------------|-------------|
| **Writer thread** | Executes `worker_loop`, processes `WriteCmd` messages, owns the exclusive `rusqlite::Connection` | [[`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) |
| **`WriterHandle`** | Cheap, cloneable handle with `Arc<WriterInner>` and `mpsc::Sender<WriteCmd>` for async command submission | [[`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) |
| **Reader pool** | Pool of read-only connections for parallel queries without blocking the writer | [[`reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/reader.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/reader.rs) |

## How the Writer Thread Initializes

When `ai-memory-store` starts, `WriterHandle::spawn(conn)` is invoked from [[`lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/lib.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs). This creates an `mpsc::channel(1024)`, spawns a thread named **"ai-memory-writer"**, and moves the `rusqlite::Connection` into that thread.

The spawned thread runs `worker_loop(conn, rx)`, which loops indefinitely:

```rust
// Simplified from writer.rs
pub fn spawn(conn: Connection) -> WriterHandle {
    let (cmd_tx, cmd_rx) = mpsc::channel::<WriteCmd>(1024);
    
    std::thread::Builder::new()
        .name("ai-memory-writer".into())
        .spawn(move || worker_loop(conn, cmd_rx))
        .expect("failed to spawn writer thread");
    
    WriterHandle {
        inner: Arc::new(WriterInner { cmd_tx }),
    }
}

```

The channel buffer of 1024 commands provides backpressure without blocking async callers.

## The WriteCmd Enum: All Mutations Defined

Every database mutation is explicitly enumerated in the `WriteCmd` enum. Each variant carries operation-specific payload plus a `oneshot::Sender<StoreResult<T>>` for the response.

Key variants include:

- `GetOrCreateWorkspace { name, reply }`
- `UpsertPage { page, reply }`
- `PurgeProject { workspace_id, project_id, reply }`
- `EnqueueSessionConsolidation { session_id, reply }`
- `Shutdown { reply }`

This design makes every write operation traceable and serializable by construction.

## Sending Commands from Async Context

Every public mutating API on `WriterHandle` follows an identical pattern. From [[`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs):

```rust
impl WriterHandle {
    pub async fn upsert_page(&self, page: NewPage) -> StoreResult<PageId> {
        let (tx, rx) = oneshot::channel();
        
        self.send(WriteCmd::UpsertPage {
            page,
            reply: tx,
        })?;
        
        rx.await.map_err(|_| StoreError::WriterClosed)
    }
    
    fn send(&self, cmd: WriteCmd) -> StoreResult<()> {
        self.inner
            .cmd_tx
            .try_send(cmd)
            .map_err(|_| StoreError::WriterClosed)
    }
}

```

Because `WriterHandle` implements `Clone` via `Arc<WriterInner>`, any number of concurrent tasks can hold handles and submit commands. The `mpsc` channel guarantees **FIFO ordering**, ensuring operations execute in the sequence submitted.

## Worker Loop Execution and Safety Guarantees

Inside `worker_loop`, each `WriteCmd` is matched and dispatched to concrete SQL operations, typically in [[`ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/ops.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs):

```rust
fn worker_loop(mut conn: Connection, mut rx: Receiver<WriteCmd>) {
    while let Some(cmd) = rx.blocking_recv() {
        match cmd {
            WriteCmd::UpsertPage { page, reply } => {
                let result = ops::upsert_page(&mut conn, &page);
                let _ = reply.send(result);
            }
            WriteCmd::Shutdown { reply } => {
                let _ = reply.send(Ok(()));
                break; // close connection on drop
            }
            // ... other variants
        }
    }
}

```

The single-threaded execution provides three critical guarantees:

1. **No lock contention** — SQLite's connection is never shared, eliminating `database is locked` errors
2. **Deterministic ordering** — Commands execute strictly in submission order
3. **Simplified error handling** — SQL errors propagate directly through `StoreResult` without cross-thread synchronization complexity

## ReaderPool: Concurrent Reads Without Blocking

Read operations bypass the writer entirely. The `ReaderPool` in [[`reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/reader.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/reader.rs) maintains multiple read-only connections:

```rust
use ai_memory_store::Store;

#[tokio::main]
async fn read_example() -> StoreResult<()> {
    let store = Store::open("path/to/data_dir").await?;
    
    // Read operates independently of the writer thread
    let page = store
        .reader
        .get_page(workspace_id, project_id, PagePath::new("notes/todo.md")?)
        .await?;
    
    println!("Found: {:?}", page);
    Ok(())
}

```

This separation enables **high-throughput parallel reads** while writes proceed serially without contention.

## Complete Usage Example

Creating a store and executing writes through the single-writer SQLite actor pattern:

```rust
use ai_memory_store::{WriterHandle, Store, StoreResult};
use ai_memory_core::{NewPage, PagePath};

#[tokio::main]
async fn example() -> StoreResult<()> {
    // 1. Create store — spawns the dedicated writer thread
    let store = Store::open("path/to/data_dir").await?;
    let writer = store.writer.clone();

    // 2. Atomic write via the writer actor
    let page = NewPage {
        workspace_id: 1,
        project_id: 42,
        path: PagePath::new("notes/todo.md")?,
        body: "Buy milk".into(),
        ..Default::default()
    };
    let page_id = writer.upsert_page(page).await?;

    // 3. Conditional delete — serialized after the upsert
    let deleted = writer
        .delete_page_if_latest(
            1,  // workspace_id
            42, // project_id
            PagePath::new("notes/todo.md")?,
            page_id,
        )
        .await?;
    
    println!("Was the page removed? {}", deleted);
    Ok(())
}

```

The `store.writer.clone()` demonstrates the handle's cheap cloneability—`Arc` overhead only, no additional threads or connections.

## Graceful Shutdown

The `Shutdown` variant cleanly terminates the writer thread:

```rust
pub async fn shutdown(self) -> StoreResult<()> {
    let (tx, rx) = oneshot::channel();
    self.send(WriteCmd::Shutdown { reply: tx })?;
    rx.await.map_err(|_| StoreError::WriterClosed)?;
    // Thread exits, connection closes on drop
    Ok(())
}

```

If the writer crashes or the channel closes, pending commands receive `StoreError::WriterClosed`, enabling caller-side error handling rather than silent hangs.

## Key Source Files Reference

| File | Purpose |
|------|---------|
| [[`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) | `WriteCmd` enum, `WriterHandle` API, thread spawning, `worker_loop` |
| [[`lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/lib.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs) | `Store` struct, initialization, `WriterHandle`/`ReaderPool` assembly |
| [[`reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/reader.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/reader.rs) | `ReaderPool` for concurrent read-only queries |
| [[`ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/ops.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs) | Concrete SQL implementations for each `WriteCmd` variant |
| [[`session_consolidation.rs`](https://github.com/akitaonrails/ai-memory/blob/main/session_consolidation.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/session_consolidation.rs) | Higher-level job queued via writer: `EnqueueSessionConsolidation` |

## Summary

- **Single-writer SQLite actor pattern** eliminates `database is locked` by serializing all mutations through one OS thread with exclusive `rusqlite::Connection` ownership
- **`WriterHandle`** provides cheap, cloneable async access with `mpsc` channel backpressure and `oneshot` response channels
- **FIFO command ordering** ensures deterministic execution critical for state-dependent operations
- **`ReaderPool`** enables parallel reads without writer interference
- **Explicit `WriteCmd` enum** makes all database mutations visible and traceable in [[`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs)

## Frequently Asked Questions

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

WAL mode reduces locking but still requires application-level coordination for concurrent writes. The single-writer SQLite actor pattern in `ai-memory-store` provides **guaranteed serialization without any lock retries**, simpler error handling, and explicit command ordering that WAL alone cannot enforce. This is especially valuable for operations like `CompleteObservationIngest` that depend on prior `InsertObservation` state.

### How does the channel buffer size of 1024 affect performance?

The 1024-slot `mpsc` channel in `WriterHandle::spawn` provides backpressure: async producers block when full, preventing unbounded memory growth under heavy write load. In practice, SQLite's single-threaded execution makes this buffer ample—writes complete faster than typical async task generation rates. The buffer primarily smooths burst traffic rather than sustained throughput limitations.

### Can reads ever see partially written state?

No. The `ReaderPool` uses **separate database connections**, but each connection sees only **committed transaction state**. The writer thread executes each `WriteCmd` as a complete transaction before responding via `oneshot`. Readers observe either the full pre-state or full post-state of any write, never intermediate states, maintaining ACID guarantees.

### What happens if the writer thread panics?

If `worker_loop` panics, the `mpsc` channel closes. All pending `oneshot` receivers resolve to `Err(RecvError)`, which `WriterHandle` converts to `StoreError::WriterClosed`. The `Store` owner must detect this (via operation failures) and recreate the store. The design prioritizes fail-fast over complex recovery, as panics indicate unrecoverable SQLite or logic errors per `ai-memory` architecture.