SQLite Architecture in ai-memory: Derived Index Pattern for AI Knowledge Bases

ai-memory uses SQLite as a derived index that mirrors markdown content, employing a single-writer actor pattern to maintain transactional consistency between human-readable files and searchable database tables including FTS5 and vector embeddings.

The ai-memory project treats markdown wiki files as the immutable source of truth while leveraging a high-performance SQLite database to power vector search, full-text retrieval, and graph traversal. This SQLite architecture enables fast querying without sacrificing data portability or human readability. All database tables—including wiki_pages, wiki_links, and specialized FTS5 virtual tables—serve as derived indices that can be rebuilt from the canonical markdown source.

Derived Index Pattern and Storage Layout

Mirror Tables for Markdown Content

The database schema in ai-memory directly reflects the repository's markdown structure. According to docs/design-decisions.md, core tables include wiki_pages for content storage, wiki_links for relationship tracking, an FTS5 virtual table named search_index for full-text search, and search_vector_embeddings for vector storage. This design enables brute-force cosine similarity on packed vectors alongside lexical-entity lookups.

Source of Truth Separation

Unlike traditional applications where the database is primary, ai-memory inverts this relationship. The markdown files remain the canonical data source, while the SQLite file acts as a disposable, rebuildable cache. This separation allows the system to use SQLite's online-backup API for complete state snapshots without complex migration scripts.

Single-Writer Concurrency Model

WriterHandle and Connection Ownership

To eliminate "database is locked" errors common in concurrent SQLite scenarios, ai-memory implements a dedicated writer thread. In crates/ai-memory-store/src/writer.rs, the WriterHandle struct owns a rusqlite::Connection exclusively. All write operations funnel through this single actor, ensuring that index updates occur atomically with underlying markdown changes.

// Example: opening the SQLite store (used by many crates)
let conn = rusqlite::Connection::open(store.db_path()).unwrap();
// The writer owns this connection; all writes go through it.

Source: crates/ai-memory-store/src/tests/handoff_ownership.rs

Transactional Consistency Guarantees

When the system sanitizes an observation, the store writes both the observation and the corresponding SQLite row within the same transaction. As documented in docs/design-decisions.md, there is no background "index-after-return" task—the index maintains strict synchronization with persisted pages at all times.

// Example: inserting a new wiki page updates both the markdown file
// and the SQLite index in one transaction (writer side)
store.write_page(page_path, markdown_content).await?;

Conceptual example – the writer implementation guarantees the atomic write

Multi-Modal Search Implementation

FTS5 and Vector Hybrid Queries

The memory_query tool demonstrates the architecture's retrieval capabilities by combining three distinct streams: FTS5 full-text search, lexical-entity matching, and optional vector-based reranking. The implementation in crates/ai-memory-mcp/src/server.rs queries the same SQLite file for all three modalities, accessing packed embedding vectors stored in the search_vector_embeddings table.

// Example: performing a memory_query – FTS5 + entity + optional vector
let results = store
    .memory_query(
        query,
        embedder,          // Option<&dyn Embedder>
        reranker,          // Option<&dyn Reranker>
        limit,
        workspace,
        project,
    )
    .await?;

Source: crates/ai-memory-mcp/src/server.rs

Graph Traversal via Recursive CTEs

Graph relationships between wiki pages are stored in standard SQLite tables (wiki_links) rather than a separate graph database. This enables sophisticated graph-RRF (Reciprocal Rank Fusion) retrieval using recursive common table expressions (CTEs), leveraging SQLite's native SQL capabilities for network traversal as implemented in the query logic.

Backup and Operational Portability

The single-file nature of SQLite simplifies disaster recovery operations. The ai-memory backup command utilizes SQLite's online-backup API to clone the entire database state. As noted in docs/deploy.md, restoring this backup immediately restores the complete searchable state without requiring additional migrations or re-indexing operations, since the database functions purely as a derived index of the markdown source.

Summary

• ai-memory uses SQLite as a derived index that mirrors markdown content while keeping human-readable files as the source of truth.
• A single-writer actor pattern (WriterHandle) prevents locking issues by funneling all writes through one rusqlite::Connection in crates/ai-memory-store/src/writer.rs.
• The architecture supports hybrid retrieval combining FTS5 full-text search, vector similarity, and graph traversal within a single SQLite file.
• Transactional consistency ensures that SQLite index updates occur atomically with markdown file modifications.
• Backup operations leverage SQLite's online-backup API for complete state portability without complex migration procedures.

Frequently Asked Questions

Why does ai-memory use SQLite instead of a dedicated vector database?

The project prioritizes operational simplicity and file portability. SQLite provides sufficient performance for the targeted use case while eliminating external service dependencies. According to the design decisions documented in docs/design-decisions.md, storing packed vectors in SQLite tables enables brute-force cosine similarity without requiring separate vector database infrastructure.

How does ai-memory prevent "database is locked" errors during concurrent operations?

All database writes are serialized through a dedicated writer thread implemented in crates/ai-memory-store/src/writer.rs. The WriterHandle maintains exclusive ownership of the rusqlite::Connection, eliminating concurrent write conflicts that typically cause locking errors in multi-threaded SQLite applications.

What happens if the SQLite index becomes corrupted?

Since the SQLite database functions as a derived index rather than the primary data store, corruption is recoverable by rebuilding the index from the markdown source files. The system can regenerate all tables—including wiki_pages, wiki_links, and search_vector_embeddings—by re-scanning the repository's wiki files, making the SQLite file effectively disposable.

Can queries target the SQLite database directly without using the ai-memory APIs?

While technically possible, direct database access is discouraged because the schema is optimized for internal use. The recommended approach uses the memory_query interface exposed in crates/ai-memory-mcp/src/server.rs, which properly orchestrates FTS5, entity, and vector retrieval streams while maintaining transactional consistency with the underlying markdown state.

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