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

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

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

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