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

> Discover how ai-memory uses SQLite and a Git-backed Markdown wiki for dual storage. Explore efficient access to human-readable content and query-optimized metadata.

- Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory)
- Tags: how-to-guide
- Published: 2026-09-06

---

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

```rust
// 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.