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

> Discover how ai-memory data storage works using SQLite and file system architecture. Learn about runtime state, atomic markdown files, and ACID compliance with a single-writer actor pattern.

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

---

**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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs):

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

```rust
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:

```rust
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:

```rust
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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs) for structured metadata storage.
- A **single-writer actor** in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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.