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

> Discover how ai-memory leverages SQLite FTS5 virtual tables and graph traversal for efficient database indexing and hybrid retrieval, powered by Reciprocal Rank Fusion.

- Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory)
- Tags: internals
- Published: 2026-08-31

---

**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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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:**

```bash

# Search wiki pages for "memory management"

ai-memory search "memory management"

```

**Direct FTS5 query via SQLite:**

```sql
-- 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:**

```bash
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:**

```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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/fts_query.rs)** – Handles query normalization and FTS5 match expression generation
- **[`docs/ARCHITECTURE.md`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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.