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

> Explore the SQLite architecture in ai-memory. Discover how the derived index pattern and single-writer actor model ensure transactional consistency for your AI knowledge base.

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

---

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

```rust
// 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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/docs/design-decisions.md), there is no background "index-after-return" task—the index maintains strict synchronization with persisted pages at all times.

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

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