How ai-memory's SQLite Actor Enforces Write Ordering and Prevents Race Conditions

ai-memory guarantees serialized database writes by running all mutations through a single dedicated OS thread that owns the sole rusqlite::Connection, eliminating concurrent access entirely.

The ai-memory project implements a strict single-writer actor pattern to handle SQLite mutations safely. Rather than sharing database connections across async tasks or threads, the codebase centralizes every write operation through a dedicated actor. This design prevents the "database is locked" errors common in concurrent SQLite usage and ensures total ordering of all mutations. Understanding this architecture is essential for anyone extending the store or debugging write-related issues.

Single-Writer Thread Architecture

The foundation of ai-memory's concurrency safety lies in exclusive thread ownership of the SQLite connection.

In crates/ai-memory-store/src/writer.rs, the WriterHandle::spawn function creates a thread explicitly named "ai-memory-writer" and moves the rusqlite::Connection into that thread's scope (lines 56–63). This thread then enters worker_loop, which runs indefinitely until a shutdown signal arrives.

// Simplified conceptual flow from writer.rs lines 56-63
let handle = std::thread::Builder::new()
    .name("ai-memory-writer".to_string())
    .spawn(move || {
        // Connection owned exclusively by this thread
        worker_loop(conn, receiver)
    })?;

No other thread ever accesses this connection. The single-writer invariant is enforced at the OS level—there is simply no code path that allows another thread to directly hold or use this Connection reference.

Serialized Command Channel

All mutations flow through an async message-passing channel that preserves FIFO ordering.

Public mutating methods on WriterHandle—such as insert_observation, upsert_page, and begin_session—do not perform I/O directly. Instead, each method:

1. Creates a oneshot::Receiver for the reply
2. Constructs the appropriate WriteCmd variant
3. Sends it via self.send(...) to the writer thread

// Example from the public API pattern
pub async fn insert_observation(&self, obs: Observation) -> Result<i64, StoreError> {
    let (tx, rx) = oneshot::channel();
    self.send(WriteCmd::InsertObservation { 
        obs, 
        reply: tx 
    }).await?;
    
    // Awaits the writer thread's completion signal
    rx.await.map_err(|_| StoreError::WriterClosed)?
}

The mpsc::Sender in worker_loop (lines 1796–1810) guarantees that commands are dequeued and executed in the exact order they were issued. This provides total ordering without explicit locks.

Atomic Execution Per Command

Each WriteCmd processed by worker_loop receives exclusive access to the connection for its entire duration.

The loop matches incoming commands and delegates to helper functions in the ops module:

// Conceptual excerpt from worker_loop (lines 1796-1810)
loop {
    match receiver.recv().await? {
        WriteCmd::InsertObservation { obs, reply } => {
            let result = ops::insert_observation(&mut conn, &obs);
            let _ = reply.send(result);
        }
        WriteCmd::UpsertPage { page, reply } => {
            let result = ops::upsert_page(&mut conn, &page);
            let _ = reply.send(result);
        }
        // ... other variants
        WriteCmd::Shutdown => break,
    }
}

Helper functions such as ops::insert_observation manage their own transactions internally. Because the same Connection is used throughout and never shared between concurrent executions, every command effectively runs in isolation. This eliminates the classic race condition where multiple connections attempt to write simultaneously, triggering SQLite's busy handler or "database is locked" errors.

Back-Pressure and Failure Transparency

The oneshot reply pattern provides observable back-pressure and crash detection.

Callers await the oneshot::Receiver returned by each method. If the writer thread panics or shuts down unexpectedly, the channel closes and the caller receives StoreError::WriterClosed:

// Pattern repeated throughout WriterHandle implementation
rx.await.map_err(|_| StoreError::WriterClosed)?

This design makes failures explicit and immediate rather than allowing silent data loss or indefinite blocking. Applications can detect writer unavailability and respond appropriately—typically by restarting the store or failing fast.

Graceful Shutdown

Clean termination is handled through a dedicated command and proper handle management.

The WriteCmd::Shutdown variant breaks the worker_loop, allowing the thread to exit normally. The JoinHandle is stored inside a Mutex<Option<JoinHandle<()>>> within WriterHandle, enabling the actor to be awaited and cleaned up without leaving the database in an inconsistent state.

// Initiating shutdown
writer.send(WriteCmd::Shutdown).await?;

// The writer thread will:
// 1. Process any pending commands in the channel
// 2. Break the loop on Shutdown command
// 3. Drop the Connection (automatically committing any open transaction)
// 4. Exit cleanly

Practical Example: Using the Writer API

use ai_memory_store::{Store, Observation, Page};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Initialize store and obtain writer handle
    let store = Store::open("./data").await?;
    let writer = store.writer.clone(); // WriterHandle is Clone + Send
    
    // All mutations serialize through the dedicated thread
    let obs_id = writer
        .insert_observation(Observation::new("user query", "assistant response"))
        .await?;
    
    let page_id = writer
        .upsert_page(Page::new("https://example.com", "page content"))
        .await?;
    
    // Explicit shutdown for clean exit
    writer.send(WriteCmd::Shutdown).await?;
    
    Ok(())
}

Key Implementation Files

File	Responsibility
crates/ai-memory-store/src/writer.rs	WriterHandle, spawn, worker_loop, WriteCmd enum
crates/ai-memory-store/src/lib.rs	Public Store API exposing WriterHandle
docs/CONTRIBUTING.md	Documents the "single writer actor" design principle
docs/wiki-migrations.md	Migration examples using WriterHandle

Summary

• Single dedicated thread owns the rusqlite::Connection, enforced at construction time in WriterHandle::spawn
• mpsc channel provides FIFO ordering of all write commands, guaranteeing total serialization
• Oneshot replies give callers explicit back-pressure and failure detection via StoreError::WriterClosed
• Per-command exclusive access to the connection prevents "database is locked" errors without busy-waiting
• Graceful shutdown via WriteCmd::Shutdown ensures clean termination and database consistency

Frequently Asked Questions

Does ai-memory support concurrent writer threads for higher throughput?

No. The codebase enforces a strict single-writer invariant by design. All mutations serialize through one OS thread. For read-heavy workloads, the architecture typically pairs this single writer with multiple read-only connections that do not contend for locks.

What happens if the writer thread panics?

The oneshot reply channels close, causing all pending async operations to return StoreError::WriterClosed. The Store can detect this state and propagate the failure, allowing application-level recovery logic to restart the database connection if needed.

Why use a dedicated OS thread instead of a Tokio blocking task?

A dedicated thread provides clearer ownership semantics and avoids unexpected executor behavior. The thread name ("ai-memory-writer") aids debugging, and the pattern matches classic actor-model designs where the mailbox (the mpsc channel) and the actor (the thread) are tightly coupled.

How does this compare to SQLite's WAL mode for concurrency?

WAL mode allows concurrent reads and a single writer at the SQLite level, but ai-memory adds an additional serialization layer above this. Even with WAL enabled, the actor pattern guarantees that only one write operation is ever "in flight" from the application's perspective, eliminating application-level race conditions entirely.

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