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

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 (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. 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. 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. 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) to populate entity tables from existing frontmatter, ensuring older pages remain searchable without manual re-indexing.

The decay.rs module calculates salience scores for observations, storing these values alongside each record to drive automated pruning jobs. Similarly, 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:

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, 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, 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, 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.

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