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

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 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 (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

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, 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 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)
• 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.

// 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, 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.

// 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) 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 (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.

// 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, 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. 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	Public API surface, Store struct, re-exports
src/writer.rs	Single-writer actor, WriteCmd enum, WriterHandle
src/reader.rs	Read-only connection pool and query implementations
src/scope.rs	Workspace/project resolution and validation
src/ops.rs	High-level operation structs for business logic
src/session_consolidation.rs	Background job for merging sessions to pages
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) 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) 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) 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.

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