How ai-memory Prevents Write Contention in Its SQLite Database: Single-Writer Architecture Explained

ai-memory eliminates SQLite write contention by funneling every mutating operation through a single, dedicated writer thread that owns the sole database connection.

Write contention is the Achilles' heel of SQLite in high-concurrency applications. The akitaonrails/ai-memory project solves this through a strict single-writer design that serializes all mutations while allowing concurrent reads. This article breaks down the implementation in crates/ai-memory-store, showing exactly how the Rust code guarantees exclusive write access without locks or retries.

The Core Problem: SQLite's Database-Locked Error

SQLite allows multiple readers but only one writer at a time. When multiple threads attempt concurrent writes, applications hit the infamous "database is locked" error. Traditional workarounds—retries, timeouts, or external locking—add complexity and still risk failures under load.

ai-memory takes a different approach: eliminate concurrent writers entirely by construction.

Single-Writer Actor Pattern

The heart of ai-memory's solution is the WriterHandle struct in crates/ai-memory-store/src/writer.rs. This type owns the sole rusqlite::Connection and runs on a dedicated OS thread named ai-memory-writer.

All write commands travel through an async mpsc channel as WriteCmd enum variants. Results return via oneshot channels. This guarantees that at any moment, exactly one thread holds SQLite's write lock—removing the possibility of contention at the source.

// From crates/ai-memory-store/src/writer.rs
// The WriterHandle owns the single Connection and receives commands via channel

pub struct WriterHandle {
    tx: mpsc::UnboundedSender<WriteMsg>,
    // ...
}

enum WriteCmd {
    UpsertPage { ... },
    InsertObservation { ... },
    PurgeProject { ... },
    Compact,
    MoveSession { ... },
    // ... other mutations
}

The WriterHandle::spawn function creates this dedicated thread and establishes the communication channel:

// Simplified from writer.rs lines 82-108
pub fn spawn(conn: Connection) -> Self {
    let (tx, rx) = mpsc::unbounded_channel();
    
    thread::Builder::new()
        .name("ai-memory-writer".to_string())
        .spawn(move || {
            // Thread-local Connection owned exclusively here
            for msg in rx {
                let result = Self::handle_cmd(&mut conn, msg.cmd);
                let _ = msg.reply.send(result);
            }
        })
        .expect("spawn writer thread");
    
    WriterHandle { tx }
}

Serializing Mutations Through Message Passing

Every public mutation method follows the same pattern: package the request into a WriteCmd, send it through the channel, and await the oneshot response. This design serializes all writes automatically—no explicit synchronization needed.

Here's how upsert_page works:

pub async fn upsert_page(&self, page: NewPage) -> Result<i64, StoreError> {
    let (reply_tx, reply_rx) = oneshot::channel();
    
    self.tx
        .send(WriteMsg {
            cmd: WriteCmd::UpsertPage { page },
            reply: reply_tx,
        })
        .map_err(|_| StoreError::WriterGone)?;
    
    reply_rx.await.map_err(|_| StoreError::WriterGone)?
}

Other mutations follow identical patterns:

• insert_observation → WriteCmd::InsertObservation
• purge_project → WriteCmd::PurgeProject
• compact → WriteCmd::Compact
• move_session → WriteCmd::MoveSession

All paths converge on the single thread executing handle_cmd, ensuring strict FIFO ordering of writes.

WAL Mode: Isolating Reads from Writes

While writes serialize through the dedicated thread, ai-memory enables concurrent reads via SQLite's Write-Ahead Logging (WAL) mode. This is configured in crates/ai-memory-store/src/lib.rs:

// From lib.rs lines 22-26
conn.pragma_update(None, "journal_mode", "WAL")?;
conn.pragma_update(None, "wal_autocheckpoint", 1000)?;

WAL mode allows read-only connections to operate without blocking on the writer. The ReaderPool (separate from WriterHandle) hands out connections for queries, achieving true read concurrency while the writer thread holds exclusive mutation access.

Atomic Operations and Transaction Safety

Because each WriteCmd executes entirely within the writer thread's event loop, complex operations run as single SQLite transactions. The purge_project and compact commands, for example, perform multiple table modifications atomically:

// Simplified from writer.rs lines 1300-1320
fn handle_purge_project(&mut tx: Transaction, project_id: i64) -> Result<...> {
    // Multiple deletions within one transaction
    tx.execute("DELETE FROM observations WHERE page_id IN \
                (SELECT id FROM pages WHERE project_id = ?1)", [project_id])?;
    tx.execute("DELETE FROM pages WHERE project_id = ?1", [project_id])?;
    tx.execute("DELETE FROM projects WHERE id = ?1", [project_id])?;
    tx.commit()?;
}

No interleaving occurs—other WriteCmd variants wait until the current transaction completes. This provides serializable isolation without SQLite's SERIALIZABLE overhead.

Complete Usage Example

Here's how applications interact with the single-writer architecture:

use ai_memory_store::{Store, WriterHandle};

#[tokio::main]
async fn main() -> Result<(), ai_memory_store::StoreError> {
    // Open the store (creates DB, runs migrations, spawns writer thread)
    let store = Store::open("/path/to/data")?;

    // All writes route through store.writer — the single WriterHandle
    let page_id = store.writer.upsert_page(
        ai_memory_core::NewPage {
            workspace_id: workspace_id,
            project_id: project_id,
            path: "notes/todo.md".into(),
            body: "Buy milk".into(),
            // …other fields…
        },
    ).await?;

    println!("Created page with ID {}", page_id);
    Ok(())
}

Behind upsert_page, the WriteCmd::UpsertPage travels through the mpsc channel to the ai-memory-writer thread. The response arrives via oneshot, giving the async caller a clean await interface while maintaining strict write serialization.

Key Source Files

File	Purpose
crates/ai-memory-store/src/writer.rs	WriterHandle implementation, WriteCmd enum, all mutation methods routing through single thread
crates/ai-memory-store/src/lib.rs	SQLite initialization, WAL mode configuration, WriterHandle::spawn invocation
crates/ai-memory-wiki/src/wiki.rs	Higher-level consumer showing WriterHandle usage without direct SQLite access

Summary

• Single-writer by construction: The WriterHandle owns the only rusqlite::Connection and runs on a dedicated thread named ai-memory-writer
• Channel-based serialization: All mutations become WriteCmd messages through an mpsc channel; responses return via oneshot
• WAL-mode concurrency: Read-only connections operate concurrently via ReaderPool while writes remain strictly serialized
• Atomic transactions: Complex operations execute as single SQLite transactions without interleaving from other writes

This architecture makes write contention impossible by design—there is simply never more than one writer.

Frequently Asked Questions

Does the single-writer pattern bottleneck write throughput?

Not for ai-memory's use case. The ai-memory-writer thread processes commands as fast as SQLite allows, and the channel buffer absorbs burst traffic. For workloads with extremely high write volumes, batching multiple operations into single WriteCmd variants would reduce channel overhead.

What happens if the writer thread panics?

The oneshot senders fail with StoreError::WriterGone, surfacing the failure immediately to callers. Applications must recreate the Store to respawn the writer thread and reestablish the database connection.

Why not use rusqlite's bundled Connection::execute with a Mutex?

A Mutex<Connection> would serialize access but couples all callers to SQLite's synchronous API. The channel-based WriterHandle decouples async callers from the blocking database operations, integrating cleanly with Tokio without spawn_blocking overhead.

Can readers see uncommitted writes from the writer?

No. Readers use separate connections via ReaderPool. WAL mode provides snapshot isolation—each reader sees a consistent database state as of the start of their query, unaffected by in-flight transactions in the writer thread.

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