How SQLite Powers Indexing and Data Storage in ai-memory

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

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

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 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 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.
• FTS5 integration: Full-text search indexes page titles and bodies via virtual tables with automatic synchronization triggers.
• Query safety: 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 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, 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 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.

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