How ai-memory Manages Database Indexes: FTS5 Virtual Tables and Hybrid Retrieval

ai-memory uses SQLite FTS5 virtual tables with external content mappings for full-text search, automatically synchronized via database triggers, and fuses results with graph traversal and optional vector embeddings using Reciprocal Rank Fusion (RRF).

The ai-memory project stores project context in a single SQLite file and requires fast, flexible retrieval across wiki pages and raw observations. According to the ai-memory source code, the system implements a multi-layered indexing strategy centered on FTS5 virtual tables combined with entity tables, graph edges, and optional vector embeddings.

FTS5 Virtual Tables for Full-Text Search

The primary search mechanism relies on two FTS5 external-content virtual tables defined in crates/ai-memory-store/src/migrations.rs. These tables index the textual content of wiki pages and raw observations while storing the actual data only once in the primary tables.

External Content Mapping

The pages_fts and observations_fts virtual tables map directly to their source tables:

• pages_fts – Indexes the (title, body) columns of every wiki page
• observations_fts – Indexes the (title, body) columns of raw observation records

As documented in docs/ARCHITECTURE.md, the FTS5 tables use external content mode, meaning the full text lives only in the main pages and observations tables while the FTS5 engine maintains the inverted index separately.

Automatic Synchronization via Triggers

Database triggers defined in crates/ai-memory-store/src/migrations.rs ensure the FTS5 indexes remain synchronized with source data. These triggers automatically propagate INSERT, UPDATE, and DELETE operations from the base tables to their corresponding FTS5 virtual tables, eliminating the need for manual index maintenance in application code.

Entity and Graph Indexes

Beyond full-text search, ai-memory maintains specialized indexes for structured relationships:

• Lexical entity tables – Store exact entity IDs extracted from text content
• Graph edge table – Persists link relationships between pages for graph traversal

During query execution, these indexes enable entity-match streams and graph-neighbour expansion, allowing the system to surface related content that might not match the initial FTS5 query terms.

Optional Vector Embeddings

When the AI_MEMORY_EMBEDDING_PROVIDER environment variable is configured, ai-memory extends its indexing with vector storage:

• Embeddings are stored in a sqlite-vec virtual table
• Cosine similarity scores are computed against query embeddings
• Vector results participate in the RRF fusion alongside lexical results

Query Execution and Ranking

The search implementation in crates/ai-memory-store/src/reader.rs orchestrates multiple retrieval streams and combines them using Reciprocal Rank Fusion (RRF).

Reciprocal Rank Fusion

The RRF algorithm combines four distinct retrieval signals:

1. FTS5 lexical match – BM25-ranked results from pages_fts or observations_fts
2. Entity-match stream – Exact entity ID matches from lexical entity tables
3. Graph-neighbour expansion – Related pages discovered via graph edge traversal
4. Vector cosine similarity – Embedding-based similarity (when configured)

The final relevance score is the sum of inverted ranks across all streams, ensuring strong signals from any individual stream can surface relevant results.

Query Normalization

User input is sanitized before reaching FTS5 through crates/ai-memory-store/src/fts_query.rs. This module prepares safe MATCH expressions and handles the fallback logic: if the primary pages_fts search returns no hits, the system automatically queries observations_fts as a bounded fallback.

Practical Code Examples

Basic CLI search using FTS5:

# Search wiki pages for "memory management"

ai-memory search "memory management"

Direct FTS5 query via SQLite:

-- Query the pages_fts virtual table directly
SELECT path, rank, snippet(pages_fts, '<mark>', '</mark>', -1, 64) AS snippet
FROM pages_fts
WHERE pages_fts MATCH 'memory management'
ORDER BY rank;

Configuring hybrid retrieval with embeddings:

export AI_MEMORY_EMBEDDING_PROVIDER=openai
export OPENAI_API_KEY=your_key_here

# This search now fuses FTS5 and vector similarity

ai-memory search "memory management"

Programmatic access in Rust:

use ai_memory_store::{Reader, FtsQuery};

let reader = Reader::new(&store)?;
let fts_query = FtsQuery::new("memory management");
let results = reader.search(&fts_query, None)?;

for hit in results {
    println!("Path: {}", hit.path);
    println!("Score: {}", hit.score);
    println!("Snippet: {}", hit.snippet); // Contains <mark> highlights
}

Key Implementation Files

• crates/ai-memory-store/src/migrations.rs – Defines pages_fts and observations_fts virtual tables and synchronization triggers
• crates/ai-memory-store/src/reader.rs – Implements RRF fusion and query orchestration across all index types
• crates/ai-memory-store/src/fts_query.rs – Handles query normalization and FTS5 match expression generation
• docs/ARCHITECTURE.md – Documents the indexing strategy including FTS5 configuration and optional vector storage

Summary

• ai-memory uses FTS5 external-content virtual tables (pages_fts, observations_fts) to index wiki pages and observations without data duplication
• Database triggers automatically synchronize FTS5 indexes with source tables during write operations
• The system combines FTS5 results with entity matching and graph traversal using Reciprocal Rank Fusion
• Optional vector embeddings via sqlite-vec extend retrieval when an embedding provider is configured
• All indexes reside in a single SQLite file supporting atomic transactions and the one-writer invariant

Frequently Asked Questions

How does ai-memory keep FTS5 indexes synchronized with data changes?

The synchronization happens automatically through SQLite triggers defined in crates/ai-memory-store/src/migrations.rs. These triggers fire on INSERT, UPDATE, and DELETE operations against the main pages and observations tables, ensuring the corresponding FTS5 virtual tables remain consistent without requiring manual index updates in application code.

What happens if a search returns no results from the wiki pages?

If the query against pages_fts returns zero hits, ai-memory implements a bounded fallback mechanism. The system automatically retries the search against observations_fts, which indexes raw observation records. This guarantees that relevant context can still be retrieved even when no wiki page matches the query terms.

Can ai-memory use vector embeddings without full-text search?

Yes, ai-memory supports standalone vector retrieval when configured with AI_MEMORY_EMBEDDING_PROVIDER. However, the default and recommended mode uses hybrid retrieval where vector cosine similarity scores are fused with FTS5 lexical matches via RRF. The vector storage uses a sqlite-vec virtual table, while text content remains in standard FTS5 tables.

Why does ai-memory use external-content FTS5 tables instead of storing text directly in the FTS5 table?

External-content FTS5 tables allow ai-memory to store the actual text content (title and body) only once in the primary tables while the FTS5 engine maintains the inverted index separately. This approach reduces storage overhead and ensures that the full text data participates in SQLite's transactional guarantees alongside other project metadata.

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