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

> Discover how ai-memory prevents SQLite locked errors using a single-writer actor model. It separates reads and writes for efficient, concurrent database operations.

- Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory)
- Tags: internals
- Published: 2026-09-05

---

**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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs), the `WriterHandle` spawns a dedicated thread that owns the sole mutable `rusqlite::Connection`:

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

```rust
// 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`](https://github.com/akitaonrails/ai-memory/blob/main/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

```rust
// 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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) | `WriterHandle`, `WriteCmd` enum, single-writer thread implementation |
| [`crates/ai-memory-store/src/reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/reader.rs) | Read-only connection pool and read-only APIs |
| [`crates/ai-memory-store/src/ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs) | Concrete SQLite operations (INSERT, UPDATE, SELECT) used by both paths |
| [`docs/ARCHITECTURE.md`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs) using channel-based messaging with transaction-wrapped commands
- **Reads** scale freely via the pool in [`reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/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.