# What Is the Single-Writer SQLite Actor Pattern in ai-memory and Why Is It Used?

> Discover the single-writer SQLite actor pattern in akitaonrails/ai-memory. Learn how it isolates DB mutations on a thread to eliminate write contention and enable concurrent reads.

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

---

**The ai-memory project implements a single-writer SQLite actor pattern that isolates all database mutations on a dedicated thread behind an mpsc channel, eliminating write contention while allowing concurrent reads through a connection pool.**

The ai-memory repository persists all application state to a single SQLite database file, but directly sharing a database connection across asynchronous tasks would violate SQLite's thread-safety guarantees. To solve this, the codebase adopts a **single-writer SQLite actor pattern** that funnels every insert, update, and schema migration through a solitary writer thread, ensuring atomicity and preventing corruption without sacrificing read throughput.

## How the Single-Writer Actor Pattern Works

### The WriterHandle Thread

At the heart of the pattern is `WriterHandle`, a dedicated thread that owns the sole writable SQLite connection. When the system initializes, `WriterHandle::spawn` creates this thread and listens on an asynchronous **mpsc channel** for incoming database commands. All mutations—whether inserting a new project or updating page metadata—are serialized into messages and dispatched to this channel. The writer thread executes commands sequentially, preserving SQLite's "one writer at a time" invariant and preventing race conditions that could occur if multiple threads attempted simultaneous writes.

The implementation lives in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs), where the actor loop processes each command synchronously before returning a result to the caller via oneshot channels. This design centralizes transaction management and retry logic in one place, reducing duplication across the codebase.

### The ReaderPool for Concurrent Reads

While writes are strictly serialized, reads operate through a separate `ReaderPool` that maintains multiple read-only SQLite connections. Because SQLite allows concurrent readers when Write-Ahead Logging (WAL) mode is enabled, the `ReaderPool` can handle high-throughput query workloads without blocking the `WriterHandle` thread. This separation of concerns means long-running analytics queries never stall urgent write operations, and vice versa.

The public API exposes these components through the `Store` struct in [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs), which pairs the `ReaderPool` with the `WriterHandle` to provide a unified interface for both operations.

## Why ai-memory Uses This Pattern

### Enforcing SQLite Concurrency Constraints

SQLite's locking model permits multiple readers but strictly limits writers to one per database file. Attempting concurrent writes from separate threads triggers immediate `SQLITE_BUSY` errors or, in worst cases, database corruption under high load. By routing every mutation through the single-writer actor, ai-memory respects these constraints while still providing an asynchronous API to the rest of the application.

This invariant is explicitly documented as **Invariant #2** in the wiki migration module, which states that all schema changes and data mutations must pass through the single writer to maintain consistency during version upgrades.

### Deterministic Write Ordering

The ai-memory architecture relies on strict event ordering for core features like **page supersession** and **handoff ownership**. When a user updates a document, the system must guarantee that earlier writes are fully persisted before subsequent operations read the state. The mpsc channel naturally provides this guarantee: messages are processed in FIFO order, ensuring that the sequence of operations matches the sequence of user actions. Without the actor pattern, arbitrary thread scheduling could cause "last-write-wins" anomalies or torn reads during complex transactions.

### Simplified Error Handling

Centralizing writes in `WriterHandle` allows the system to implement sophisticated error recovery—such as automatic retries on transient busy errors or transaction rollback on failure—in a single location. According to the source code in [`crates/ai-memory-wiki/src/wiki.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/wiki.rs), this centralization is critical for the wiki layer, which routes every markdown mutation through the writer to ensure atomic updates of both content and metadata indices.

## Implementation in the Codebase

### Core Files and Architecture

The pattern spans several key files:

- **[`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs)** — Implements `WriterHandle::spawn` and the command processing loop that serializes mutations.
- **[`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs)** — Defines the `Store` struct and documents the architectural requirement that all writes must originate from the single writer.
- **[`crates/ai-memory-wiki/src/wiki.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/wiki.rs)** — Demonstrates integration, showing how the wiki layer receives a `WriterHandle` and dispatches markdown persistence commands through it.
- **[`crates/ai-memory-wiki/src/migrations/mod.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/migrations/mod.rs)** — References invariant #2 (lines 40-43), ensuring that schema migrations also respect the single-writer constraint to prevent corruption during version upgrades.

### Sending Commands to the Writer

Commands are typically structs implementing a trait that the `WriterHandle` recognizes. For example, creating a new project requires packaging the parameters into a command object and sending it via the handle's asynchronous interface.

## Practical Code Examples

### Initializing the Store

To begin using the pattern, spawn the writer and create the store:

```rust
use ai_memory_store::{WriterHandle, ReaderPool, Store};

// Open the database connection
let conn = rusqlite::Connection::open("ai_memory.db")?;

// Spawn the single-writer actor thread
let writer = WriterHandle::spawn(conn);

// Create a pool of read-only connections
let reader_pool = ReaderPool::new(&writer)?;

// Combine into the unified store interface
let store = Store::new(reader_pool, writer);

```

### Executing Write Operations

All mutations flow through the writer handle. For example, creating a new project:

```rust
use ai_memory_store::ops::CreateProject;

let cmd = CreateProject {
    workspace_id: 1,
    project_name: "documentation".into(),
    // ... additional fields
};

// Serialized through the mpsc channel to the writer thread
store.writer().send(cmd).await?;

```

The `send` method returns a future that completes only after the writer thread has executed the SQL and committed the transaction, guaranteeing durability before the async function resolves.

### Performing Read Queries

Reads bypass the writer and use the connection pool directly:

```rust
// Executes on a separate read-only connection
let pages = store.reader().list_pages(project_id).await?;
println!("Retrieved {} pages from the project", pages.len());

```

Because `ReaderPool` maintains multiple connections, this query runs concurrently with any ongoing write operations without blocking the `WriterHandle` thread.

## Summary

- **Single-threaded writer**: The `WriterHandle` owns the sole write-capable SQLite connection and processes all mutations through an mpsc channel, enforcing SQLite's concurrency constraints.
- **Concurrent readers**: The `ReaderPool` provides multiple read-only connections for parallel queries, enabling high-throughput data access without interfering with writes.
- **Invariant enforcement**: The codebase documents this architecture as **invariant #2** in the migration module, ensuring that schema changes respect the single-writer rule.
- **Deterministic ordering**: Message passing through the channel guarantees that write operations execute in the exact order they were received, preventing race conditions in critical features like page handoffs.
- **Centralized reliability**: All transaction logic, retry semantics, and error handling reside in [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs), simplifying maintenance and reducing bug surface area.

## Frequently Asked Questions

### What prevents multiple writers in ai-memory?

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) takes ownership of the `rusqlite::Connection` and moves it onto a dedicated background thread. Because the connection is not `Clone` or `Send` to other threads, the type system prevents any other component from acquiring a write handle. All mutations must be encoded as commands and sent through the mpsc channel, ensuring that only the actor thread ever invokes SQL `INSERT`, `UPDATE`, or `DELETE` statements.

### How does the ReaderPool avoid blocking the writer?

`ReaderPool` creates separate SQLite connections with read-only flags or utilizes Write-Ahead Logging (WAL) mode to allow concurrent snapshot reads. These connections are distinct from the writer's connection, meaning a long-running `SELECT` query in the pool never acquires locks that would stall the `WriterHandle` thread's commit operations. The store architecture explicitly separates `store.reader()` and `store.writer()` interfaces to maintain this isolation.

### Can this pattern work with WAL mode enabled?

Yes, the pattern is specifically designed to complement SQLite's WAL mode. While WAL allows readers to see a consistent snapshot without blocking the writer, it still enforces that only one process writes to the database at a time. The single-writer actor satisfies this requirement by serializing writes in the `WriterHandle` thread, while the `ReaderPool` leverages WAL's snapshot isolation to serve stale-but-consistent data without locking.

### Where is the single-writer invariant documented?

The constraint is explicitly referenced as **invariant #2** 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) at lines 40-43. This documentation ensures that future maintainers understand that migration scripts must dispatch schema changes through the `WriterHandle` rather than attempting direct connection access, preserving data integrity during version upgrades.