# How the `ai-memory-store` Crate Functions: A Deep Dive into the SQLite Persistence Layer

> Discover how the ai-memory-store crate works. Learn about its SQLite persistence, single-writer thread, and connection pool for efficient, concurrent data management.

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

---

**The `ai-memory-store` crate manages SQLite-backed persistence for ai-memory through a single-writer thread pattern that eliminates database locking issues while allowing concurrent read-only queries via a connection pool.**

The `ai-memory-store` crate serves as the foundational storage layer for the [akitaonrails/ai-memory](https://github.com/akitaonrails/ai-memory) repository. It wraps SQLite with a custom concurrency model designed to handle the specific workload of an AI-assisted memory system: frequent small writes from LLM interactions and parallel read-heavy queries for context retrieval.

## Core Architecture: Single Writer, Multiple Readers

The crate's architecture centers on a strict separation between write and read paths. This design prevents the classic SQLite "database is locked" errors that plague multi-threaded applications.

### The `Store` Entry Point

The `Store` struct in [`src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/lib.rs) (lines 101–108) acts as the public façade. Its `open` method (starting at line 118) performs three critical setup steps:

1. Creates the SQLite database file at `<data_dir>/db/memory.sqlite`
2. Runs pending migrations via `migrations::run`
3. Spawns the writer thread and initializes the reader pool

```rust
use std::path::Path;
use ai_memory_store::Store;

fn main() -> Result<(), ai_memory_store::StoreError> {
    let data_dir = Path::new("/tmp/ai_memory_demo");
    let store = Store::open(data_dir)?;
    // `store.writer` and `store.reader` are now available
    Ok(())
}

```

### Writer Thread and `WriterHandle`

The **single-writer guarantee** is enforced by `WriterHandle` (re-exported from [`src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/writer.rs), line 53). Rather than sharing a connection across threads, the crate spawns a dedicated OS thread that owns a `rusqlite::Connection` exclusively, as documented in [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs) lines 1–8.

All mutations flow through this pattern:

- Callers invoke methods like `upsert_page` or `insert_observation` on `WriterHandle`
- Each method constructs a `WriteCmd` variant (defined at line 51 in [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs))
- Commands are sent via an `mpsc` channel to the writer thread
- The writer executes SQL inside a transaction and replies through a `oneshot` channel

This actor-style design ensures **exactly one writer at any moment**, serializing all mutations without explicit locks.

```rust
// Upserting a page through the writer handle
let page = NewPage {
    workspace_id: workspace_id,
    project_id: project_id,
    path: PagePath::from_str("notes/todo.md")?,
    body: b"# TODO\n- [ ] Write docs".to_vec(),

    author_id: None,
    created_at: chrono::Utc::now(),
};
let page_id = store.writer.upsert_page(page)?;

```

### Read-Only Connection Pool

For concurrent queries, `ReaderPool` (defined in [`src/reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/reader.rs), line 137) maintains a soft-capped pool of read-only connections—defaulting to 4 connections. These connections run with `PRAGMA journal_mode=WAL` (Write-Ahead Logging), enabling true concurrent reads that don't block on the writer thread.

The pool is instantiated during `Store::open` at line 154 and supports operations like `read_page_body`, search APIs, and audit log queries.

```rust
// Reading with a pooled connection
let mut pool = store.reader.clone();
let page_body = pool.read_page_body(page_id)?;
println!("{}", String::from_utf8_lossy(&page_body));

```

## The `WriteCmd` Protocol

Every mutation in `ai-memory-store` is expressed as a variant of the `WriteCmd` enum. This centralized command definition (starting at line 51 in [`src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/writer.rs)) creates a typed, exhaustive interface for all storage operations:

- Workspace and project creation (`CreateWorkspace`, `CreateProject`)
- Page upserts and content updates
- Session lifecycle management (`StartSession`, `EndSession`)
- Observation insertion during active sessions
- Handoff handling between sessions
- Background job scheduling (`EnqueueSessionConsolidation`)
- Auto-improve workflows (`StageAutoImproveRun`, `ApproveAutoImprove`)

The exhaustiveness of `WriteCmd` ensures that no mutation bypasses the single-writer serialization.

## Scope Resolution with `ScopeResolver`

All public APIs accept user-visible workspace and project names, but internally the crate operates with typed IDs (`WorkspaceId`, `ProjectId`, `PageId`). The `ScopeResolver` in [`src/scope.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/scope.rs) (line 180) bridges this gap.

It enforces a critical invariant: **every operation requires a valid workspace + project pair**. When names are provided, `ScopeResolver` either looks up existing IDs or creates missing entries transparently. The resulting `ResolvedScope` accompanies every write command.

```rust
// Scope resolution happens automatically in writer methods
// Callers can use names, IDs, or a mix—the resolver normalizes
let scope = store.writer.get_or_create_scope("my-ws", "my-proj")?;

```

## Background Jobs and Async Patterns

Two specialized subsystems extend the writer's capabilities without breaking the single-writer contract:

### Session Consolidation

The `SessionConsolidationJob` (in [`src/session_consolidation.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/session_consolidation.rs), line 15) handles the merge of ephemeral session observations into durable pages. When a session ends, the writer enqueues consolidation via `EnqueueSessionConsolidation`. A separate worker later claims jobs with `ClaimSessionConsolidation` and performs the merge—still through the same writer thread.

### Auto-Improve Pipeline

LLM-generated improvements flow through dedicated `WriteCmd` variants staged in [`src/auto_improve.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/auto_improve.rs). Proposals are created via `StageAutoImproveRun`, then approved or rejected through subsequent commands. All state transitions respect the single-writer serialization.

## Key Source Files

| File | Purpose |
|------|---------|
| [`src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/lib.rs) | Public API surface, `Store` struct, re-exports |
| [`src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/writer.rs) | Single-writer actor, `WriteCmd` enum, `WriterHandle` |
| [`src/reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/reader.rs) | Read-only connection pool and query implementations |
| [`src/scope.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/scope.rs) | Workspace/project resolution and validation |
| [`src/ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/ops.rs) | High-level operation structs for business logic |
| [`src/session_consolidation.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/session_consolidation.rs) | Background job for merging sessions to pages |
| [`src/auto_improve.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/auto_improve.rs) | Types for LLM-driven improvement proposals |

## Summary

- **`ai-memory-store` uses a single-writer thread** to eliminate SQLite locking, with all mutations serialized through `WriteCmd` messages
- **Concurrent reads are served by `ReaderPool`**, a WAL-enabled connection pool with soft-capped size
- **Scope resolution enforces workspace/project invariants** automatically via `ScopeResolver`
- **Background jobs** (session consolidation, auto-improve) extend functionality without breaking the writer contract
- **All storage logic is typed** through ID newtypes and exhaustive command enums

## Frequently Asked Questions

### How does `ai-memory-store` prevent "database is locked" errors?

The crate never shares a `rusqlite::Connection` across threads. Instead, `WriterHandle::spawn` (in [`src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/writer.rs)) creates a dedicated OS thread that exclusively owns the write connection. All mutations are queued as `WriteCmd` messages and executed serially by this thread, guaranteeing exactly one writer at all times.

### Can I use `ai-memory-store` for read-heavy workloads?

Yes. The `ReaderPool` (defined in [`src/reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/reader.rs)) maintains multiple read-only connections with WAL mode enabled. Reads execute concurrently on pool connections without contending with the writer thread. The default soft cap is 4 connections, configurable during `Store` initialization.

### What happens if I provide workspace/project names instead of IDs?

The `ScopeResolver` (in [`src/scope.rs`](https://github.com/akitaonrails/ai-memory/blob/main/src/scope.rs)) automatically translates names to internal IDs. It validates that both workspace and project are provided, creates missing entries when appropriate, and returns a `ResolvedScope` that all writer methods consume. This happens transparently in every public API call.

### How are background jobs like session consolidation scheduled?

Jobs are scheduled through the same `WriteCmd` channel as all other mutations. `EnqueueSessionConsolidation` adds work to a queue table; `ClaimSessionConsolidation` allows workers to grab pending jobs. Both commands execute on the writer thread, ensuring consistency with other state changes.