How ai-memory Manages SQLite Read and Write Operations: A Single-Writer Actor Model

ai-memory prevents SQLite "database is locked" errors by separating reads and writes into two distinct paths: a dedicated writer thread for all mutations and a pool of read-only connections for concurrent queries.

The akitaonrails/ai-memory project implements a high-concurrency SQLite access pattern that eliminates lock contention while maintaining full ACID compliance. This architecture is critical for AI applications that require both fast parallel reads and reliable persistent writes without blocking the async runtime.

The Core Problem: SQLite Concurrent Access

SQLite's default locking model traditionally forces readers and writers to compete for the same database handle. In async Rust applications, this creates two pain points:

• Write contention: Multiple threads attempting to write trigger SQLITE_BUSY errors
• Blocking I/O: Synchronous database calls stall the async runtime

ai-memory solves this with a deliberate architectural split between read and write paths.

Write Path: Single-Writer Thread with Command Queue

All database mutations in ai-memory flow through exactly one writer thread. This design guarantees deterministic write ordering and eliminates race conditions entirely.

WriterHandle and WriteCmd Messaging

In crates/ai-memory-store/src/writer.rs, the WriterHandle spawns a dedicated thread that owns the sole mutable rusqlite::Connection:

// Create a writer handle from an existing SQLite connection
let conn = rusqlite::Connection::open("ai-memory.db")?;
let writer = WriterHandle::spawn(conn);

Mutating operations are sent as typed WriteCmd messages over an mpsc channel. Results return through a oneshot reply channel, keeping the caller's async context alive:

// Resolve (or create) a workspace – a write operation
let ws_id = writer.get_or_create_workspace("my-workspace").await?;

// Insert a new observation (writes go through the writer thread)
let obs = NewObservation::new(...);
let obs_id = writer.insert_observation(obs.sanitized()).await?;

Transaction Boundaries

Every write command executes inside a single SQLite transaction. The worker_loop receives a WriteCmd, starts a transaction, runs the requested operation, commits, and replies. Available operations include:

• ops::upsert_page
• ops::insert_observation
• purge_project

If the writer thread crashes, the channel returns StoreError::WriterClosed, providing clear failure signaling.

Read Path: Pool of Read-Only Connections

Reads bypass the writer entirely, enabling unlimited parallel query execution.

Reader Pool Implementation

In crates/ai-memory-store/src/reader.rs, a pool of read-only connections is created from the same database file opened in WAL (Write-Ahead Logging) mode. Key characteristics:

• Separate SQLite handles mean reads never contend with writes
• Lightweight async lock (tokio::sync::RwLock) protects the pool
• Multiple concurrent reads execute while the writer runs exclusively

// Perform a read-only query – uses the read pool (no writer involvement)
let page = store.get_latest_page(&ws_id, &proj_id, "notes.md").await?;
println!("Latest revision: {}", page.id);

Public Store APIs

The read pool powers store methods like:

• Store::get_page
• Store::search

These methods remain fully asynchronous without blocking on write operations.

Concurrency Model Comparison

Aspect	Write Path	Read Path
Connection ownership	Single dedicated thread	Pool of shared handles
Synchronization	mpsc + oneshot channels	tokio::sync::RwLock
Transaction scope	Per-command transactions	Read-only, no transactions needed
Concurrency limit	Exactly one write at a time	Unlimited parallel reads
Error on contention	StoreError::WriterClosed	None (lock-free for readers)

Safety Guarantees and Design Invariants

ai-memory's architecture enforces two critical invariants documented in docs/ARCHITECTURE.md:

1. "Single-writer SQLite actor" — All mutations serialize through one thread
2. "One config-read path" — Reads use a dedicated, separate connection pool

These invariants deliver concrete benefits:

• No SQLITE_BUSY errors — Single writer eliminates lock conflicts
• Fast parallel queries — Read-only pool scales with core count
• Compile-time operation safety — The WriteCmd enum ensures only valid operations reach the database

Key Source Files

File	Role
crates/ai-memory-store/src/writer.rs	WriterHandle, WriteCmd enum, single-writer thread implementation
crates/ai-memory-store/src/reader.rs	Read-only connection pool and read-only APIs
crates/ai-memory-store/src/ops.rs	Concrete SQLite operations (INSERT, UPDATE, SELECT) used by both paths
docs/ARCHITECTURE.md	High-level SQLite actor model documentation

Summary

• ai-memory manages SQLite read and write operations through strict separation of concerns: one writer thread for mutations, one connection pool for reads
• Writes serialize through WriterHandle in writer.rs using channel-based messaging with transaction-wrapped commands
• Reads scale freely via the pool in reader.rs using WAL-mode connections and async locks
• This single-writer actor model eliminates SQLITE_BUSY errors while maintaining full async responsiveness throughout the system

Frequently Asked Questions

What prevents write conflicts in ai-memory's SQLite access?

A dedicated writer thread owns the only mutable rusqlite::Connection. All write operations are sent as WriteCmd messages over an mpsc channel, ensuring exactly one write executes at any time. This design eliminates the classic "database is locked" race condition entirely.

How does ai-memory achieve concurrent reads without blocking the writer?

The project maintains a separate pool of read-only connections opened in WAL mode. These connections use distinct SQLite handles from the writer, so reads never contend for locks. A tokio::sync::RwLock protects the pool itself, allowing many parallel queries while writes proceed uninterrupted.

Why does the writer use channels instead of async mutexes?

Channels decouple the synchronous SQLite connection from the async caller. The WriterHandle methods are async and await on oneshot replies, letting the async runtime continue other work while the dedicated thread blocks on database I/O. This prevents thread pool exhaustion that would occur with synchronous mutex guards in async code.

What happens if the writer thread crashes?

The oneshot reply channel returns StoreError::WriterClosed to any pending callers. This explicit error type allows the application to detect writer failure and potentially restart the database connection, rather than hanging indefinitely on a deadlocked mutex.

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