# How ai-memory Stores Persistent Data: SQLite Architecture and Single-Writer Concurrency

> Discover how ai-memory stores persistent data using SQLite architecture and single-writer concurrency. Learn about its WAL-mode database and actor thread for efficient data management.

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

---

**ai-memory stores persistent data in a WAL-mode SQLite database managed by the `ai-memory-store` crate, using a dedicated single-writer actor thread for serialized mutations and a pooled reader for concurrent queries.**

The `ai-memory` project implements a durable storage layer through an embedded SQLite database that balances security, atomicity, and concurrent access. Understanding how ai-memory stores persistent data reveals a carefully architected system combining strict file permissions, WAL journaling, and actor-model concurrency to guarantee data integrity across multiple workspaces and projects.

## Database Location and Secure File Creation

When initializing storage, `Store::open` in [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs) (lines 101-107) resolves the database path to `<data_dir>/db/memory.sqlite`. The crate enforces strict filesystem isolation before the connection opens.

The helper `create_private_dir_all` (lines 69-77) creates the parent `db/` directory with mode `0o700` (owner read/write/execute only), while `create_private_file_if_missing` (lines 79-87) creates the SQLite file itself with mode `0o600` (owner read/write only). These permission bits prevent other system users from accessing the memory database even if they possess local filesystem access.

## SQLite Configuration and Schema Migrations

After file creation, `Store::open` applies a series of SQLite pragmas to optimize for durability and performance. The connection enables **Write-Ahead Logging (WAL) mode**, sets a **5-second busy timeout** to handle lock contention gracefully, and enforces foreign key constraints after migrations complete.

Schema versioning is handled by the `refinery` migration runner in [`crates/ai-memory-store/src/migrations.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/migrations.rs). During startup, migrations execute with foreign keys **temporarily disabled** to allow schema alterations, then re-enabled for runtime operations. This guarantees that relational integrity applies to all application data while allowing the migration engine to modify table structures without cascading constraints.

## Single-Writer Actor for Atomic Mutations

All write operations funnel through a **single-writer actor** 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::spawn` method (lines 1-20) launches a dedicated OS thread that owns the sole write-capable SQLite connection.

Application code sends mutation commands—such as `upsert_page` or entity updates—through an asynchronous `mpsc` channel to this writer thread. This architecture **serializes every mutation** into a strict linear sequence, eliminating write-write races and simplifying transaction management. Because only one thread ever writes to the database file, ai-memory avoids complex locking strategies while maintaining ACID guarantees.

## Concurrent Read Access via Connection Pooling

While writes are strictly serialized, reads scale through a **read-only connection pool** defined in [`crates/ai-memory-store/src/reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/reader.rs). The `ReaderPool::new` constructor spawns after the writer thread starts and maintains a soft cap of **4 concurrent connections** by default.

Each query—such as `page_body_by_ids`—borrows a connection from this pool, allowing multiple concurrent tasks to search and retrieve data without blocking the writer. The separation of read and write paths prevents long-running queries from stalling mutations, ensuring the UI remains responsive even during heavy indexing operations.

## Multi-Tenant Data Model and FTS5 Indexing

The physical schema, defined in `crates/ai-memory-store/src/migrations/*.sql`, stores domain objects across several core tables: `pages`, `observations`, `users`, `web_sessions`, and `auto_improve_*` tables for the proposal pipeline. Every row is keyed by a composite three-tuple of `(workspace_id, project_id, path)`, enforcing **multi-tenant isolation** at the database level.

Full-text search capabilities are provided by **FTS5 virtual tables** that index page content and frontmatter. This design allows the application to perform high-performance text queries without external search engines, keeping the deployment footprint limited to the single SQLite file.

## Entity Indexing and Retention Policies

On first open, the system triggers `ops::backfill_entity_index` (lines 36-44 in [`crates/ai-memory-store/src/ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs)) to populate entity tables from existing frontmatter, ensuring older pages remain searchable without manual re-indexing.

The [`decay.rs`](https://github.com/akitaonrails/ai-memory/blob/main/decay.rs) module calculates **salience scores** for observations, storing these values alongside each record to drive automated pruning jobs. Similarly, [`auto_improve.rs`](https://github.com/akitaonrails/ai-memory/blob/main/auto_improve.rs) persists proposals, validation runs, and telemetry, with the writer atomically upserting pages and recording conflict states (rejections or approvals) within the same transaction boundary.

## Practical Usage Example

The following Rust code demonstrates opening the store, writing a page through the single-writer actor, and querying it via the reader pool:

```rust
use ai_memory_store::{Store, StoreResult};
use std::path::Path;

// Open (or create) the store at a custom data directory
let data_dir = Path::new("/tmp/ai-memory-data");
let store: Store = Store::open(data_dir)?;

// Write a page (serialized through the single-writer actor)
let ws = store.writer.get_or_create_workspace("default").await?;
let proj = store.writer.get_or_create_project(ws, "my-app", None).await?;
let page = ai_memory_core::NewPage {
    workspace_id: ws,
    project_id: proj,
    path: ai_memory_core::PagePath::new("notes/hello.md")?,
    title: "Hello".into(),
    body: "Welcome to ai-memory!".into(),
    tier: ai_memory_core::Tier::Semantic,
    frontmatter_json: serde_json::json!({}),
    pinned: false,
    links: vec![],
    author_id: None,
    expires_at: None,
    entities: vec![],
};
store.writer.upsert_page(page).await?;

// Query the page (concurrent read-only pool)
let page_body = store
    .reader
    .page_body_by_ids(ws, proj, "notes/hello.md")
    .await?
    .expect("page must exist")
    .body;
println!("Page body: {page_body}");

```

All public symbols are exported from [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs), providing a unified interface for database initialization and access.

## Summary

- **Single-file storage**: All persistent state resides in `<data_dir>/db/memory.sqlite` with owner-only file permissions (`0o600`).
- **WAL-mode SQLite**: Configured with Write-Ahead Logging, foreign key enforcement, and a 5-second busy timeout for reliability.
- **Serialized writes**: The `WriterHandle` actor ensures atomic, ordered mutations through a dedicated thread and `mpsc` command channel.
- **Concurrent reads**: A `ReaderPool` of 4 connections allows parallel querying without blocking the writer.
- **Multi-tenant schema**: Tables use `(workspace_id, project_id, path)` composite keys, with FTS5 virtual tables enabling full-text search across isolated projects.

## Frequently Asked Questions

### Why does ai-memory use a single-writer actor instead of connection pooling for writes?

The single-writer actor eliminates write-write race conditions by ensuring only one OS thread ever holds the write connection. According to the source in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs), this design serializes all mutations through an `mpsc` channel, guaranteeing strict atomicity and preventing SQLite "database is locked" errors that commonly plague multi-threaded write access.

### What SQLite pragmas does ai-memory configure for durability and performance?

As implemented in `Store::open` in [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs), the system enables WAL mode for concurrent readers during writes, sets `foreign_keys = ON` after migrations for referential integrity, and configures a 5-second busy timeout to prevent immediate failures during transient lock contention.

### How does ai-memory ensure data isolation between workspaces and projects?

The schema enforces isolation through a composite primary key of `(workspace_id, project_id, path)` present on all core tables including `pages` and `observations`. This three-tuple guarantees that queries from one workspace cannot access rows belonging to another, effectively providing multi-tenancy within the same SQLite file.

### Can the SQLite database file be relocated or used on network storage?

The database file is relocatable by changing the `data_dir` argument passed to `Store::open`, but network-mounted filesystems are discouraged. SQLite's WAL mode and the single-writer actor rely on POSIX file locking semantics that may behave unpredictably on NFS or SMB shares, potentially corrupting the database or causing writer starvation.