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

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, 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, 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, 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 — Implements WriterHandle::spawn and the command processing loop that serializes mutations.
• 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 — 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 — 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:

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:

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:

// 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, 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 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 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}