How ai-memory Uses SQLite and a Git‑Backed Markdown Wiki for Storage

The ai‑memory project implements a dual‑storage architecture where a Git‑backed Markdown wiki serves as the canonical source of truth for human‑readable content, while SQLite provides indexed, query‑optimized access to metadata, embeddings, and full‑text search capabilities.

The akitaonrails/ai‑memory repository solves the persistence problem by combining the durability and version control of plain‑text Markdown files with the performance characteristics of a relational database. This design stores all content as human‑editable files in a Git repository while maintaining a synchronized SQLite index for fast programmatic retrieval, enabling both human‑readable history and machine‑optimized queries.

Dual‑Storage Architecture Overview

Git‑Backed Markdown as the Source of Truth

All canonical content lives in a plain‑text Markdown wiki located at <data_dir>/wiki/. The Wiki struct in crates/ai-memory-wiki/src/wiki.rs manages this filesystem layer, creating a Git repository on initialization to track every change.

When the system starts, Wiki::new (lines 21‑33) establishes the directory structure and initializes the Git repository. Pages follow a hierarchical path encoding: <wiki_root>/<workspace_id>/<project_id>/<page‑path>. This layout allows humans to navigate content directly via the filesystem while preserving workspace and project isolation.

Every mutation triggers GitAdapter::commit_all, creating a permanent, versioned history of the Markdown source. This guarantees that content history survives database corruption or schema changes, as the SQLite layer can be entirely rebuilt from the Git‑tracked Markdown tree.

SQLite for Indexed Queries

The SQLite database (<data_dir>/db/memory.sqlite) stores structured metadata including pages, observations, sessions, handoffs, and users. Unlike the wiki files, the database is not the source of truth but rather a query‑optimized index.

The system enforces a single‑writer actor pattern through WriterHandle in crates/ai-memory-store/src/writer.rs. This dedicated thread owns the sole rusqlite::Connection used for mutations, receiving commands via an mpsc channel. All mutating operations—such as WriteCmd::UpsertPage and WriteCmd::InsertObservation—flow through this single thread, eliminating "database is locked" errors common in multi‑process SQLite access.

For read operations, ReaderPool opens read‑only connections to the same SQLite file, enabling concurrent full‑text search via FTS5 and vector‑based retrieval without blocking writes or touching the filesystem.

Atomic Write Operations

The Write Pipeline

When persisting new content, ai‑memory ensures that the filesystem and database remain consistent through a coordinated two‑phase commit:

1. Wiki::write_page (lines 74‑80 in wiki.rs) builds the Markdown content, runs optional admission webhooks, and atomically writes the file to disk using atomic::write_file.

2. Simultaneously, the method sends an UpsertPage command to the writer thread via self.writer.upsert_page. The WriterHandle::process_cmd method (lines 80‑84 in writer.rs) handles this WriteCmd::UpsertPage variant, inserting or updating the corresponding row in SQLite with the page ID, path, content hash, and optional embedding vector.

3. Finally, GitAdapter::commit_all commits the file change to the Git repository, providing a versioned snapshot that corresponds exactly to the SQLite state.

Concurrency Safety

To prevent race conditions between file renames and database updates, the Wiki struct uses a per‑process mutation_lock (lines 10‑13 in wiki.rs). This RwLock guarantees that file operations and their corresponding SQLite row updates occur as one logical atomic operation.

The single‑writer architecture ensures that only one thread ever writes to SQLite at a time, while the ReaderPool allows unlimited concurrent reads. This design eliminates write contention while maintaining ACID guarantees across both storage backends.

Implementation Details

WriterHandle and the Single‑Writer Actor

The WriterHandle struct defined in crates/ai-memory-store/src/writer.rs owns the write‑capable database connection. It spawns a dedicated thread that loops over an mpsc channel, processing WriteCmd variants including UpsertPage, InsertObservation, and CreateHandoff.

Because all mutations serialize through this one thread, the system avoids SQLite’s write‑locking limitations while ensuring that every filesystem change has a corresponding database record before the operation returns.

ReaderPool for Query Operations

Query‑time operations never touch the Markdown files directly. Instead, ReaderPool in crates/ai-memory-store/src/reader.rs manages a pool of read‑only SQLite connections. These connections support:

• FTS5 full‑text search across page content
• Vector similarity search using stored embedding vectors
• Metadata filtering by workspace, project, or creation date

If the SQLite index becomes stale or corrupted, the system can reindex by traversing the Git‑backed Markdown tree and repopulating the database, treating the wiki as the authoritative source.

Wiki Struct Operations

The Wiki implementation in crates/ai-memory-wiki/src/wiki.rs provides the high‑level interface for content creators. It handles path resolution, Git operations through crates/ai-memory-wiki/src/git.rs, and Markdown serialization. High‑level store operations invoked by callers reside in crates/ai-memory-store/src/ops.rs, which orchestrate between the wiki layer and the storage layer.

Practical Implementation Example

The following Rust code demonstrates initializing the dual‑storage system and executing a write operation:

// Initialize the store writer and wiki (data under `/var/lib/ai-memory`)
let writer = WriterHandle::spawn();
let wiki = Wiki::new(Path::new("/var/lib/ai-memory"), writer.clone())?;

// Write a new page
let page_req = NewPage {
    workspace_id: ws_id,
    project_id: proj_id,
    path: PagePath::from("notes/intro.md"),
    body: "Welcome to the project!".into(),
    // other fields omitted for brevity
};
let page_id = wiki.write_page(page_req).await?;   // commits to Git and SQLite

// Query pages with full‑text search
let reader = ReaderPool::new(writer.clone())?;
let results = reader.search("welcome", None, None)?;  // uses SQLite FTS5

Summary

• Dual‑storage design: Git‑backed Markdown files provide human‑readable, versioned source of truth while SQLite enables fast, indexed queries.
• Single‑writer architecture: A dedicated WriterHandle thread serializes all database mutations through an mpsc channel, preventing lock contention.
• Atomic operations: The mutation_lock in Wiki::write_page ensures filesystem changes and SQLite updates occur as one logical unit.
• Query optimization: ReaderPool provides read‑only access for FTS5 full‑text search and vector retrieval without filesystem I/O.
• Reconstruction capability: The SQLite index can be rebuilt entirely from the Markdown wiki, ensuring the Git repository remains the ultimate authority.

Frequently Asked Questions

Why does ai‑memory use both SQLite and Git‑backed Markdown instead of choosing one?

The Git‑backed Markdown provides human‑readable, version‑controlled content that survives application changes and allows direct editing with standard tools. SQLite delivers the indexing, full‑text search, and embedding storage required for AI‑powered retrieval, which would be inefficient to compute from raw Markdown files on every query. This separation optimizes for both human workflow and machine performance.

How does ai‑memory prevent inconsistencies between the wiki files and the SQLite database?

All writes flow through Wiki::write_page, which uses a mutation_lock to ensure that the atomic file write and the UpsertPage database command complete together. The single‑writer actor guarantees that SQLite reflects exactly one state per Git commit, eliminating race conditions between filesystem and database mutations.

What happens if the SQLite database is corrupted or deleted?

Since the Markdown wiki in <data_dir>/wiki/ is the canonical source of truth tracked by Git, the SQLite database at <data_dir>/db/memory.sqlite can be fully reconstructed by traversing the filesystem tree and reindexing all Markdown files. The system treats the database as a disposable cache of the immutable Git history.

Can multiple processes write to the ai‑memory store simultaneously?

No, the architecture enforces a single‑writer constraint through the WriterHandle actor. Only one process may hold the write lock and submit commands to the SQLite database. However, multiple processes can open read‑only connections via ReaderPool for concurrent queries without blocking.

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