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

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/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/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/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/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:

// 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/crates/ai-memory-store/src/writer.rs):

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/crates/ai-memory-store/src/ops.rs):

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/crates/ai-memory-store/src/reader.rs) maintains multiple read-only connections:

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:

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:

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/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/crates/ai-memory-store/src/lib.rs)	Store struct, initialization, WriterHandle/ReaderPool assembly
[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/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/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/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.

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