How ai-memory Data Storage Works: SQLite and File System Architecture

ai-memory stores all runtime state in a single SQLite database file named memory.sqlite alongside atomic markdown files in a wiki directory, using a single-writer actor pattern for ACID compliance.

The ai-memory project (akitaonrails/ai-memory) implements an embedded knowledge base for AI applications. Understanding how ai-memory data storage works reveals a hybrid architecture that balances ACID-compliant metadata management with human-readable content persistence.

Core SQLite Database

Database File Location and Constants

At the heart of ai-memory data storage is a single SQLite file. The constant defining this filename is declared in crates/ai-memory-store/src/lib.rs:

pub const DB_FILENAME: &str = "memory.sqlite";

The database resides in the project's data directory under db/memory.sqlite (e.g., <repo>/db/memory.sqlite). All structured metadata—including pages, observations, handoffs, users, and sessions—lives within this file.

Single-Writer Actor Architecture

To guarantee atomicity and consistency, ai-memory implements a single-writer actor pattern. Located in crates/ai-memory-store/src/writer.rs, this actor holds a rusqlite::Connection and processes all write operations.

The writer receives commands via an internal channel and executes them inside transactions. This design prevents race conditions and ensures that complex operations maintain ACID properties across the entire system.

Database Schema and Migrations

Migration System

The database schema is created by embedded migration scripts that run on first start. These migrations are located in crates/ai-memory-store/src/migrations/ and are applied by the Store::initialize routine.

Schema Components

The migrations create several key structures:

• Tables for pages, observations, handoffs, users, and sessions
• FTS5 full-text indexes for efficient search capabilities

This schema design allows ai-memory to maintain fast, searchable metadata while keeping the actual content stored separately.

Hybrid Storage: The Wiki Layer

Atomic File Operations

In addition to the SQLite store, ai-memory persists the actual markdown content of pages as regular files under data_dir/wiki/. The wiki writer, implemented in crates/ai-memory-wiki/src/wiki.rs, ensures atomic writes using a temporary file pattern:

1. Write to a temporary file
2. Rename to final destination
3. Call fsync to ensure durability

Index Synchronization

The Wiki::write_page method updates the SQLite index after successfully writing the Markdown file. This ensures that the database remains synchronized with the file system, allowing searches to locate pages efficiently while keeping the content human-readable and version-controlled.

Implementation Examples

Opening the Database

To connect to the ai-memory data storage from application code:

let db_path = data_dir.join(ai_memory_store::DB_FILENAME);
let conn = rusqlite::Connection::open(db_path)?;

Writing Pages Atomically

To persist content using the wiki layer:

let page_path = Path::new("notes/example.md");
wiki.write_page(page_path, &page_body)?;

Submitting Write Commands

To interact with the single-writer actor for database operations:

store.writer_handle.send(WriteCmd::InsertPage {
    path: page_path.clone(),
    body: page_body.into(),
})?;

Reader utilities for querying the database are available in crates/ai-memory-store/src/reader.rs, providing functions to retrieve metadata without blocking the writer actor.

Summary

• ai-memory uses a single SQLite file (memory.sqlite) defined in crates/ai-memory-store/src/lib.rs for structured metadata storage.
• A single-writer actor in crates/ai-memory-store/src/writer.rs processes all database mutations through a command channel to ensure ACID compliance.
• Migration scripts in crates/ai-memory-store/src/migrations/ initialize tables and FTS5 indexes via the Store::initialize routine.
• The wiki layer in crates/ai-memory-wiki/src/wiki.rs stores actual Markdown content atomically in data_dir/wiki/ while updating the SQLite index for searchability.

Frequently Asked Questions

Where is the ai-memory database file located?

The database file is located at db/memory.sqlite relative to the configured data directory. The filename is defined by the constant DB_FILENAME in crates/ai-memory-store/src/lib.rs, making it consistently discoverable across the application.

How does ai-memory handle concurrent write operations?

All writes flow through a single-writer actor that holds the exclusive rusqlite::Connection. This actor receives commands via an internal channel and executes them inside transactions, preventing race conditions and ensuring consistency without complex locking mechanisms.

What storage format does ai-memory use for page content?

ai-memory uses a hybrid approach: metadata and search indexes reside in the SQLite database, while the actual Markdown content lives as plain text files in data_dir/wiki/. The Wiki::write_page function ensures atomic file operations (temporary file, rename, fsync) before updating the database index.

How are database schema updates managed?

Schema updates are handled through migration scripts stored in crates/ai-memory-store/src/migrations/. The Store::initialize routine automatically applies these migrations on first start, creating tables for pages, observations, handoffs, users, sessions, and FTS5 full-text indexes as needed.

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