# What Is the Role of the SQLite Store in ai-memory?

> Discover the SQLite store's role in ai-memory. It acts as the immutable source of truth, persisting all observations, embeddings, and metadata for your AI system.

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

---

**The SQLite store in ai-memory functions as the centralized, single-writer derived index and state store that persists all observations, embeddings, and metadata as the system's immutable source of truth.**

The `akitaonrails/ai-memory` repository implements a Rust-based memory architecture where the SQLite store serves as the backbone for durable persistence. Unlike ephemeral caches, this embedded database acts as the authoritative ledger that powers full-text search, vector retrieval, and cross-session synchronization across workspace and project boundaries.

## Single-Writer Architecture and Transactional Guarantees

### The Writer Actor Pattern

All write operations flow through a dedicated writer actor defined in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs). This actor owns an exclusive `rusqlite::Connection`, ensuring that only one thread can mutate the database at any time. By funneling updates through `WriteCmd` messages, the system eliminates SQLite's traditional locking contention while maintaining strict serialization of transactions.

### Atomic Schema Management

The store handles schema migrations atomically through the same writer channel. When opening a store via `Store::open(dir.path())`, the system initializes `<data_dir>/db/memory.sqlite` and applies migrations transactionally. This guarantees that schema versions remain synchronized with data contents across restarts and backup operations.

## Multi-Reader Query Engine with FTS5 and Vector Support

### Read-Only Connection Pool

Parallel read operations utilize independent `rusqlite::Connection` instances managed through [`crates/ai-memory-store/src/reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/reader.rs). These connections execute prepared statements with bound parameters (e.g., `:ws`, `:proj`) to enforce scoped lookups without creating new rows, enabling high-throughput concurrent queries across multiple threads.

### Full-Text and Vector Search Integration

The SQLite file houses both **FTS5 virtual tables** for keyword search and packed embedding vectors for semantic retrieval. As documented in [`docs/vector-backend-policy.md`](https://github.com/akitaonrails/ai-memory/blob/main/docs/vector-backend-policy.md), the store persists vector data alongside traditional rows, allowing retrieval pipelines to execute fused queries combining FTS5 results with vector similarity scores via reciprocal rank fusion (RRF).

## Scoped Isolation for Multi-Session Workflows

### Workspace and Project Scoping

Every row in the store carries a three-tuple identity `(workspace_id, project_id, path)` that enables strict tenant isolation. The reader implementation in [`crates/ai-memory-core/src/active_project.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-core/src/active_project.rs) filters all queries through these identifiers, ensuring that concurrent sessions from different users or machines access only their authorized data subsets while sharing the same underlying SQLite file.

## Code Examples: Opening and Querying the Store

The public API exposes the store through `Store::open`, returning handles to both the single writer and the reader pool.

```rust
// Open the store (creates <data_dir>/db/memory.sqlite if missing)
let store = ai_memory_store::Store::open("./data")?;

// Write a new observation via the single-writer actor
store.writer.send(ai_memory_store::WriteCmd::InsertObservation {
    workspace_id: ws_id,
    project_id: proj_id,
    path: page_path,
    content: "User asked about SQLite store".into(),
})?;

// Query recent observations using the read-only pool
let recent = store.reader.query_observations(
    ws_id,
    proj_id,
    Some(chrono::Utc::now() - chrono::Duration::hours(1)),
)?;

// Execute full-text search across pages
let hits = store.reader.search_pages("sqlite store")?;
for hit in hits {
    println!("{} – {}", hit.path, hit.snippet);
}

```

## Summary

- **Single-writer SQLite**: The [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) actor owns the sole `rusqlite::Connection`, preventing lock contention while ensuring ACID compliance.
- **Derived index store**: As noted in [`docs/prior-art-implementation-findings.md`](https://github.com/akitaonrails/ai-memory/blob/main/docs/prior-art-implementation-findings.md), SQLite serves as the "derived index/state store" and source of truth for all system knowledge.
- **Multi-reader scaling**: The [`crates/ai-memory-store/src/reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/reader.rs) module provides connection pools with prepared statements for concurrent FTS5 and vector queries.
- **Tenant isolation**: Scoped identifiers `(workspace_id, project_id, path)` enable safe multi-user access without separate database instances.
- **Unified persistence**: Both structured metadata and vector embeddings coexist in `<data_dir>/db/memory.sqlite`, enabling hybrid retrieval pipelines.

## Frequently Asked Questions

### Why does ai-memory use SQLite instead of a client-server database?

The design prioritizes embedded simplicity and zero-config deployment. SQLite's single-file architecture aligns with ai-memory's goal of local-first, multi-tenant storage where `<data_dir>/db/memory.sqlite` can be easily backed up, versioned, and transported without external dependencies.

### How does the single-writer pattern prevent performance bottlenecks?

By serializing writes through a dedicated actor in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs), the system avoids SQLite's writer lock starvation. This pattern batches transactions efficiently while allowing unlimited concurrent reads through the separate reader pool.

### Can multiple processes access the same ai-memory store simultaneously?

While SQLite supports multiple readers, the single-writer architecture requires all writes to flow through one process's writer actor. Multiple processes can open the store read-only, but write operations must coordinate through the owning process to maintain the source-of-truth guarantees.

### Where are vector embeddings stored relative to the main data?

According to [`docs/vector-backend-policy.md`](https://github.com/akitaonrails/ai-memory/blob/main/docs/vector-backend-policy.md), embeddings are packed and stored within the same SQLite file as the primary data, enabling JOIN operations between vector similarity scores and FTS5 results without external vector databases.