How ai-memory Ensures Transactional Consistency Between Markdown Files and the SQLite Index

ai-memory guarantees transactional consistency by coupling atomic filesystem operations with SQLite transactions through a single-writer pipeline that either commits both the Markdown file and database index together or rolls back both entirely.

The ai-memory project maintains every knowledge unit in two distinct formats: as a human-readable Markdown file on disk and as a searchable row within a central SQLite database. Keeping these dual representations synchronized during writes demands strict ACID semantics to prevent index corruption, data loss, or partial updates during system crashes. According to the source code in crates/ai-memory-wiki/src/wiki.rs and crates/ai-memory-store/src/writer.rs, the system achieves this through a tightly coupled write pipeline that treats filesystem operations and database transactions as a single atomic unit.

The Dual Storage Architecture

Every page in ai-memory exists simultaneously as:

• A Markdown file – Stored on the filesystem for human readability and version control compatibility.
• An SQLite index entry – Including the full-text search (FTS5) index, embedding vectors, and metadata in the central database.

This dual-storage approach creates a potential consistency hazard: if the system crashes after writing the file but before updating the database (or vice versa), the index becomes stale or the file becomes orphaned. To eliminate this risk, ai-memory implements an atomic write-page pipeline that binds the filesystem state to the database transaction state.

The Atomic Five-Step Write Pipeline

When Wiki::write_page processes a WritePageRequest, it executes the following coordinated sequence:

Step 1: Prepare a Temporary File

The method first writes new content to a temporary file located in the same directory as the target Markdown file. This intermediate step ensures that readers never encounter partially written content during an in-progress update. If the write fails or the system crashes at this stage, the original file remains untouched and the database transaction has not yet begun.

Step 2: Start a Database Transaction

Before modifying the filesystem, the wiki component requests the store writer actor to begin a transaction via WriterHandle::begin_transaction. All subsequent database operations—including upserting the page row, updating the FTS5 index, and storing embedding vectors—occur within this single SQLite transaction boundary. SQLite's ACID guarantees ensure that either all index modifications succeed or none are applied.

Step 3: Atomic File Rename

Once the temporary file contains valid content and the database transaction is active, the system performs an atomic rename using fs::rename. On POSIX filesystems, this rename operation is atomic: readers either see the complete old version or the complete new version, never a partially written file. This step bridges the gap between the temporary write and the permanent storage location.

Step 4: Commit the Database Transaction

Only after the filesystem rename succeeds does the writer actor commit the SQLite transaction. This ordering ensures that the database index reflects the exact content now residing on disk. The commit flushes the WAL (Write-Ahead Log) to guarantee durability.

Step 5: Rollback on Failure

If any preceding step fails—for example, if the database upsert encounters an error—the pipeline executes a coordinated rollback. The newly renamed file is removed and the original file is restored, while the SQLite transaction is explicitly rolled back. This undo mechanism guarantees transactional consistency: the file and index remain synchronized to the same logical state, whether that is the old version or the new version.

Core Consistency Guarantees

The architecture provides three fundamental safety properties:

• Atomicity – The rename operation and database transaction form an atomic unit. Both succeed together, or both are undone through the rollback mechanism.
• Isolation – All writes serialize through a single writer thread managed by ai-memory-store::WriterHandle, preventing concurrent modifications from interleaving and corrupting the index.
• Durability – The system calls fsync on the temporary file before renaming and relies on SQLite's WAL commit semantics to ensure data survives system crashes.

Key Implementation Components

Three primary source files enforce these guarantees:

• crates/ai-memory-wiki/src/wiki.rs – Contains the Wiki::write_page method that orchestrates the temporary file creation, atomic rename, and interaction with the store writer.
• crates/ai-memory-store/src/writer.rs – Implements the single-writer actor pattern using an mpsc channel. This component receives database commands and executes them within explicit transaction boundaries.
• docs/ARCHITECTURE.md – Documents the high-level design rationale for coupling filesystem and database states through the writer actor pattern.

The critical safety invariant appears in the ordering constraints: the database transaction starts before the filesystem rename, and the transaction commits only after the rename succeeds. This sequence prevents any window where the file state and database state could diverge.

Writing Pages Safely

The following Rust code demonstrates how client code interacts with the transactional pipeline:

use ai_memory_wiki::{Wiki, WritePageRequest};

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    // Initialize the Wiki instance with configuration and store
    let wiki = Wiki::new(...).await?;

    // Construct the write request with metadata and content
    let req = WritePageRequest {
        workspace: "default".into(),
        project: "my_project".into(),
        path: "notes/hello.md".into(),
        frontmatter: serde_json::json!({ "title": "Hello" }),
        body: "# Hello\n\nWelcome to ai‑memory!".into(),

        ..Default::default()
    };

    // Executes the atomic file + SQLite transaction pipeline
    let page_id = wiki.write_page(req).await?;
    println!("Page written with ID {}", page_id);
    Ok(())
}

The write_page call encapsulates all five steps of the pipeline, handling temporary file management, transaction coordination, and rollback logic automatically.

Summary

• ai-memory maintains dual storage of Markdown files and SQLite index entries for every knowledge unit.
• The Wiki::write_page method in crates/ai-memory-wiki/src/wiki.rs implements a five-step atomic pipeline: temporary file creation, transaction start, atomic rename, transaction commit, and conditional rollback.
• SQLite ACID properties and POSIX atomic rename semantics combine to prevent half-written states during crashes or errors.
• A single-writer actor in crates/ai-memory-store/src/writer.rs serializes all database modifications through WriterHandle, eliminating race conditions.
• The system automatically restores the original file and rolls back the database transaction if any step fails, ensuring the file and index never diverge.

Frequently Asked Questions

What happens if the system crashes after the file rename but before the database commit?

The pipeline detects the incomplete transaction upon restart. Because the rename occurred while the database transaction was active but uncommitted, the original file content is preserved in the database's transactional log. The system rolls back the file changes by restoring the original version from the temporary backup, ensuring the Markdown file and SQLite index remain synchronized to the pre-write state.

How does ai-memory handle concurrent writes to the same page?

All database modifications serialize through a single writer thread managed by WriterHandle in crates/ai-memory-store/src/writer.rs. This actor-based architecture uses an mpsc channel to queue write requests, ensuring that only one transaction operates on the database at any given time. Concurrent write_page calls await their turn in the queue, preventing interleaved modifications that could corrupt the FTS5 index or embedding vectors.

Why does ai-memory use both Markdown files and a SQLite index instead of just one or the other?

The dual-storage approach serves complementary purposes. Markdown files provide human-readable content that integrates with standard version control systems and text editors, while the SQLite index enables high-performance full-text search, vector similarity queries, and metadata filtering. The transactional pipeline ensures these two representations remain consistent without sacrificing the usability of flat files or the query performance of a relational database.

Where is the transaction coordination logic implemented in the source code?

The primary coordination logic resides in Wiki::write_page within crates/ai-memory-wiki/src/wiki.rs, which manages the temporary file lifecycle and filesystem operations. The database transaction boundaries and rollback mechanisms are implemented in crates/ai-memory-store/src/writer.rs, specifically within the writer actor that processes begin_transaction and commit commands. The architectural overview in docs/ARCHITECTURE.md provides the design documentation explaining how these components interact to maintain consistency.

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