How SQLite is Used as a Derived Index in ai‑memory: Architecture Explained

SQLite in ai‑memory acts as a derived index built on top of plain Markdown files, enabling fast full‑text search through external‑content FTS5 tables while preserving a human‑editable, Git‑versioned source of truth.

The ai‑memory project (akitaonrails/ai‑memory) implements a unique file‑first architecture where all knowledge persists as Markdown files in a Git repository. Rather than treating SQLite as the primary data store, the system constructs it as a derived index—a read‑optimized layer that accelerates queries without compromising the portability and editability of the source files.

What "Derived Index" Means in ai‑memory

A derived index is a secondary data structure computed from an authoritative source. In ai‑memory, this means:

• The source of truth remains the Markdown files on disk
• The SQLite database (<data_dir>/db/memory.sqlite) is regenerated from those files
• Any corruption or loss of the database is recoverable by rebuilding from the files

This design is documented in [docs/ARCHITECTURE.md](https://github.com/akitaonrails/ai-memory/blob/main/docs/ARCHITECTURE.md#L255), which describes the "File‑first wiki memory" approach and the explicit "derived index" strategy at line 257.

How the Derived Index is Built

External‑Content FTS5 Tables

The core of the derived index uses SQLite's FTS5 extension with external‑content tables. These tables index tokenized content without duplicating the original text:

-- From crates/ai-memory-store/migrations/V12__fts_remove_diacritics.sql
CREATE VIRTUAL TABLE pages_fts USING fts5(
    content='pages',
    content_rowid='rowid',
    title, body,
    tokenize="trigram remove_diacritics 1"
);

As noted in the migration file, the content='pages' directive makes this an external‑content table—the pages_fts table holds only tokenized fragments, while the actual content remains in the Markdown files.

Automatic Re‑indexing via File Watcher

The [crates/ai-memory-wiki/src/watcher.rs](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/watcher.rs) component monitors the file system for changes. When a Markdown file is created, modified, or deleted, the watcher triggers an update to the derived index.

Single‑Writer, Read‑Only Pool Architecture

To maintain consistency without locking contention, ai‑memory enforces strict access patterns:

Single‑writer SQLite actor — All database mutations flow through one asynchronous writer actor. This invariant is documented in [AGENTS.md](https://github.com/akitaonrails/ai-memory/blob/main/AGENTS.md#L70) and implemented in [crates/ai-memory-store/src/writer.rs](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs).

Pooled read‑only connections — Query operations, including full‑text search, use a connection pool of read‑only handles. This enables concurrent searches without blocking the writer.

The database operates in WAL mode (Write‑Ahead Logging), which allows readers to proceed without blocking on writes.

Full‑Text Search Implementation

The [crates/ai-memory-store/src/reader.rs](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/reader.rs#L1400) file implements the search API. Key capabilities include:

• Keyword search via MATCH queries against pages_fts and observations_fts
• Phrase matching using FTS5's built‑in query syntax
• BM25 ranking for relevance scoring
• Snippet extraction to highlight matching terms

Example search usage:

use ai_memory_store::{Store, ScopeResolver};
use ai_memory_core::ids::ProjectId;

let project = ProjectId::new("example");
let scope = ScopeResolver::resolve_project(&store, &project).unwrap();

// FTS5 search automatically queries the derived index
let hits = store.search(scope, "bug")?;
for hit in hits {
    println!("{}: score={}", hit.path, hit.score);
}

Hybrid Retrieval with Vector Search

When an embedding provider is configured, ai‑memory implements Reciprocal Rank Fusion (RRF) to combine multiple signals:

1. FTS5 text relevance from the derived SQLite index
2. Vector similarity from the embedding store
3. Entity‑graph scores from relationship metadata

The SQLite derived index remains the primary source for textual relevance even in this hybrid mode. This fusion logic is implemented in [crates/ai-memory-mcp/src/server.rs](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-mcp/src/server.rs#L1348).

Resilience and Recovery

Because the SQLite database is purely derived, the system is inherently resilient:

• Automatic healing: Corrupted indexes are rebuilt from Markdown files
• Explicit maintenance: The reclaim_freed_pages and VACUUM commands rebuild FTS5 indexes on demand
• Version portability: git clone or rsync captures all authoritative data; the derived index can be regenerated anywhere

Maintenance operations are defined in [crates/ai-memory-store/src/ops.rs](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs#L2994).

Summary

• File‑first design: Markdown files in Git are the immutable source of truth
• SQLite as derived index: External‑content FTS5 tables provide fast search without duplicating content
• Single‑writer concurrency: Serialized writes prevent race conditions; pooled readers enable concurrent queries
• Automatic re‑indexing: File watcher keeps the derived index synchronized with source changes
• Hybrid‑ready: FTS5 scores fuse with vector similarity for advanced retrieval
• Self‑healing: Any database corruption is recoverable by rebuilding from files

Frequently Asked Questions

What happens if the SQLite database is deleted?

The database can be fully reconstructed by running the watcher against the Markdown file directory. Since all content lives in the Git‑managed files, no data is lost—the derived index is rebuilt automatically on startup.

Why use external‑content FTS5 tables instead of storing content in SQLite?

External‑content tables keep the SQLite database small and maintain the separation between index and data. The original Markdown files remain human‑readable, editable with any text editor, and portable via standard file operations.

How does ai‑memory handle concurrent writes to the database?

All writes pass through a single asynchronous writer actor, serialized through a dedicated thread. This design eliminates write conflicts while allowing multiple read‑only connections to serve queries concurrently.

Can the derived index work without an embedding provider?

Yes. The FTS5‑based derived index provides standalone full‑text search with BM25 ranking. The embedding provider is optional and only required for vector similarity features in hybrid retrieval mode.

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