# How SQLite Powers Indexing and Data Storage in ai-memory

> Discover how ai-memory leverages SQLite for efficient indexing and data storage. Explore its use of FTS5 and a unique identity system for robust data management.

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

---

**ai-memory uses SQLite as its sole persistence layer, implementing a single-writer actor pattern for concurrency, FTS5 for full-text search, and a 3-tuple identity system for strict data isolation across workspaces and projects.**

The ai-memory project stores all structured data—including workspaces, projects, pages, sessions, observations, and vector embeddings—within a single SQLite database file. This architecture provides a compact, self-contained storage engine while the Rust codebase builds sophisticated indexing and concurrency abstractions on top of the core database engine.

## Single-Writer Actor: Serialized Write Access Through writer.rs

To eliminate "database is locked" errors common in multi-threaded SQLite environments, ai-memory funnels all mutating SQL statements through a dedicated writer thread. This thread owns the sole `rusqlite::Connection` used for writes, ensuring serialized access to the database file.

All write operations—whether inserting pages, updating embeddings, or recording feedback—are dispatched as commands to this actor. Located in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs), this module implements the command enums and async wrappers that queue operations for the writer thread. Functions like `store_embedding` and `store_embeddings` (lines 2845–2890) handle binary blob storage for vector data, while `bump_access` and `record_page_feedback` increment counters used by retention policies, all executing through this serialized pathway.

## Schema Design: 3-Tuple Identity and Core Tables

The database schema enforces strict per-project isolation through a consistent three-tuple primary key pattern: `(workspace_id, project_id, path)`. This identity system appears across domain tables including pages, observations, and audit logs.

The initial schema definition resides in [`crates/ai-memory-store/migrations/V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V01__init.sql). This migration establishes the core tables and their relationships:

- **Projects** scoped to workspaces via foreign keys
- **Pages** identified by the full 3-tuple with versioning support
- **Sessions** and observations linked to specific projects
- Foreign key cascades that maintain referential integrity during deletions

## Full-Text Search Indexing with SQLite FTS5

For full-text search capabilities, ai-memory leverages SQLite's FTS5 virtual table extension. The `pages_fts` virtual table indexes only the `title` and `body` columns from the main `pages` table, creating a separate inverted index for efficient text queries.

The [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql) migration defines this virtual table alongside three triggers:
- `pages_fts_ai` – automatically inserts into the FTS index on page creation
- `pages_fts_ad` – removes entries when pages are deleted
- `pages_fts_au` – synchronizes the index when page content updates

These triggers ensure the search index remains consistent with the underlying page data without requiring manual synchronization logic in the application code.

## Safe Query Preparation in fts_query.rs

User-supplied search queries require sanitization before execution against the FTS5 index. The [`crates/ai-memory-store/src/fts_query.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/fts_query.rs) module handles this through the `prepare_fts5_query` function, which normalizes stop-words and safely injects parameters into the `WHERE pages_fts MATCH ?` clause.

The module implements an additional safety mechanism: it validates all queries against a throwaway in-memory SQLite instance before execution. This guarantees that malformed user input never causes runtime syntax errors in the production database connection.

```rust
use ai_memory_store::fts_query::prepare_fts5_query;

let raw_query = "ai memory architecture";
let safe_query = prepare_fts5_query(raw_query);
let rows = conn.query_row(
    "SELECT id FROM pages_fts WHERE pages_fts MATCH ?1",
    [&safe_query],
    |r| r.get::<_, i64>(0),
)?;

```

## Relational Indexes for Fast Look-ups

Beyond full-text search, ai-memory defines conventional B-tree indexes in [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql) to accelerate common relational queries:

- **`idx_projects_workspace`** on `projects.workspace_id` – speeds up workspace-scoped project listings
- **`idx_pages_latest_path`** (unique, conditional on `is_latest = 1`) – enables fast retrieval of current page versions without scanning historical records
- **`idx_sessions_recent`** on `(workspace_id, project_id, started_at DESC)` – optimizes "recent sessions" queries
- **`idx_observations_session`** on `(session_id, created_at)` – accelerates observation scans within specific sessions

These indexes support the application's UI patterns while maintaining the atomicity guarantees of the underlying SQLite transactions.

## Storing Vector Embeddings in SQLite

Vector embeddings are co-located with their corresponding page metadata to ensure atomic updates. The `pages` table includes columns for `embedding_provider`, `embedding_model`, `embedding_dim`, and `vector_bytes` (stored as binary blobs).

When the system generates embeddings for page content, the writer thread stores these through the `store_embedding` or `store_embeddings` commands defined in [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs). This design keeps semantic search data in the same row as the source material, allowing transactional updates that prevent drift between page content and its vector representation.

```rust
let page = NewPage {
    workspace_id,
    project_id,
    path: "ideas/ai-memory.md".into(),
    title: "AI Memory Design".into(),
    tier: "working".into(),
    body: "…".into(),
};
let page_id = writer_handle.upsert_page(page).await?;

```

## Atomic Operations and Transaction Safety

Complex mutations that touch multiple tables—such as moving a project to a new workspace, purging a project and all its history, or compacting the database—execute within single SQLite transactions. The [`crates/ai-memory-store/src/ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs) module contains the low-level SQL functions that the writer actor invokes for these operations.

By executing these through the single-writer thread, ai-memory guarantees that multi-step operations like workspace migration either complete entirely or roll back without leaving partial state. This transactional safety extends to decay calculations and feedback recording, where in-place updates to access counters remain consistent with the surrounding session data.

## Summary

- **Single-writer architecture**: All writes flow through [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs) to prevent SQLite locking errors and ensure serialized access.
- **3-tuple identity**: Tables use `(workspace_id, project_id, path)` primary keys for strict isolation, defined in [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql).
- **FTS5 integration**: Full-text search indexes page titles and bodies via virtual tables with automatic synchronization triggers.
- **Query safety**: [`fts_query.rs`](https://github.com/akitaonrails/ai-memory/blob/main/fts_query.rs) validates and sanitizes user queries against an in-memory SQLite instance before production execution.
- **Strategic indexing**: Conventional indexes on foreign keys and common query patterns accelerate relational lookups without compromising write performance.
- **Co-located embeddings**: Vector data stores as binary blobs within page rows, enabling atomic content and embedding updates.

## Frequently Asked Questions

### Why does ai-memory use a single-writer pattern instead of connection pooling?

SQLite handles concurrent writes by locking the entire database file, which can result in "database is locked" errors under multi-threaded contention. By routing all mutations through a single thread that owns the `rusqlite::Connection`, ai-memory eliminates these conflicts and guarantees that write operations execute serially without retry logic or busy-waiting.

### How does ai-memory prevent FTS5 query syntax errors from crashing the application?

The [`fts_query.rs`](https://github.com/akitaonrails/ai-memory/blob/main/fts_query.rs) module validates every user-supplied query against a disposable in-memory SQLite database before executing it on the production connection. This validation step catches malformed FTS5 syntax—such as unbalanced quotes or invalid operators—and allows the application to return helpful errors rather than propagating database exceptions.

### What is the 3-tuple identity system in ai-memory's schema?

Every major entity in ai-memory is scoped to a specific workspace and project through a composite key of `(workspace_id, project_id, path)`. This three-tuple appears in primary key definitions and foreign key constraints across [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql), ensuring that data from different workspaces never intersects and that cascading deletes properly isolate project-specific content.

### Can ai-memory handle high-throughput embedding storage with SQLite?

Yes. The `store_embeddings` command in [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs) writes binary vector data directly to the `vector_bytes` column within the same transaction as page metadata updates. By co-locating embeddings with their source pages and using the single-writer pattern, the system maintains consistency between vector and relational data without requiring a separate vector database.