How ai-memory Keeps Its SQLite Index Consistent with the Markdown Wiki

ai-memory enforces consistency by writing Markdown files to disk atomically before upserting to SQLite, with automatic rollback on failure and startup re-indexing to ensure the database never diverges from the filesystem source of truth.

The akitaonrails/ai-memory project treats the Markdown wiki as the single source of truth while maintaining a derived SQLite index for fast search. To keep the SQLite index consistent with the Markdown wiki, the codebase implements a strict "write-page-first-to-disk-then-upsert-to-SQLite" pattern in crates/ai-memory-wiki/src/wiki.rs.

The Write-Page-First-to-Disk Pattern

The core consistency logic resides in the Wiki::write_page method. This public API orchestrates a four-phase commit that guarantees atomicity between the filesystem and the database.

Step 1: Atomic Filesystem Installation

When write_page receives a request, it first prepares the content by sanitizing the markdown, scrubbing secrets, stamping the author, and applying admission webhooks. The function then performs an atomic file write:

• Writes the rendered markdown to a temporary file.
• Flushes the buffer to disk with fsync.
• Renames the temporary file into its final location at <wiki_root>/<workspace_id>/<project_id>/<path>.

This ensures that the filesystem always contains the latest version before any database operations begin.

Step 2: SQLite Upsert via Single-Writer Actor

After the file is safely on disk, the function builds a batch of NewPage structs containing the title, body, frontmatter, and extracted links. It then dispatches these to the SQLite store through the single-writer actor:

self.writer.upsert_pages_batch(pages).await

This call persists the page metadata and content to the SQLite index, making it searchable while maintaining referential integrity.

Step 3: Rollback on Failure

If the SQLite upsert fails, the system invokes rollback_or_inconsistent to remove the newly installed markdown files. This guarantees that the database never contains records without a corresponding markdown file on disk.

The source code explicitly documents this safety invariant in crates/ai-memory-wiki/src/wiki.rs (lines 84-87):

"Install files first so the DB is never ahead of markdown. If the SQL batch fails below, rollback restores the prior disk state; if the process crashes in this window, startup/reindex repairs the derived DB from the markdown source of truth."

Crash Recovery Through Startup Re-indexing

On process startup, ai-memory validates the integrity of the SQLite index. If the database is missing, corrupted, or detected as out of sync, the system triggers a full re-index.

The startup sequence, implemented in crates/ai-memory-store/src/writer.rs and the reindex helper used by ai_memory_store::init, performs the following:

• Scans the entire wiki directory hierarchy.
• Parses each markdown file.
• Rebuilds the SQLite index from scratch.

Because the markdown files represent the immutable source of truth, this recovery path can always restore consistency regardless of when a crash occurred.

Practical Implementation

You can interact with this consistency model through the Rust API or the MCP server.

Using the Wiki API Directly

use ai_memory_wiki::{Wiki, WritePageRequest};

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let wiki = Wiki::new(/* config */).await?;
    let req = WritePageRequest {
        workspace_id: "default".into(),
        project_id: "demo".into(),
        path: "notes/todo.md".into(),
        frontmatter: serde_json::json!({ "tags": ["rust"] }),
        body: "Finish the ai‑memory article.".into(),
        tier: ai_memory_core::Tier::Normal,
        pinned: false,
        title: Some("Todo".into()),
        admission_ctx: None,
        author_id: None,
        actor: None,
    };
    let page_id = wiki.write_page(req).await?;
    println!("Page stored with id: {}", page_id);
    Ok(())
}

Via the MCP Tool

The MCP handler in crates/ai-memory-mcp/src/server.rs exposes the same functionality through the memory_write_page tool:

curl -X POST http://127.0.0.1:49374/mcp \
  -H "Content-Type: application/json" \
  -d '{
        "tool": "memory_write_page",
        "args": {
          "workspace_id": "default",
          "project_id": "demo",
          "path": "notes/todo.md",
          "frontmatter": { "tags": ["rust"] },
          "body": "Finish the ai‑memory article.",
          "tier": "Normal",
          "pinned": false
        }
      }'

Both interfaces invoke the same Wiki::write_page implementation, preserving the atomic guarantees.

Summary

• Atomic file writes using temp-file-to-rename patterns ensure filesystem durability before database operations.
• Strict ordering guarantees that SQLite never contains references to files that don't exist on disk.
• Automatic rollback via rollback_or_inconsistent removes partially written files if the database transaction fails.
• Startup re-indexing in ai-memory-store rebuilds the SQLite index from the markdown source of truth, repairing any inconsistency caused by crashes.

Frequently Asked Questions

What happens if the process crashes during a write?

If the crash occurs after the markdown file is written but before the SQLite commit completes, the startup re-indexer detects the mismatch. It scans the markdown directory and rebuilds the SQLite index to match the filesystem state, ensuring the database never diverges permanently.

Why does ai-memory treat Markdown as the source of truth rather than SQLite?

The filesystem provides superior durability and human accessibility. Markdown files are plain text, portable, and can be version-controlled independently. By treating them as canonical, the system ensures that even total database corruption results in only a performance penalty (re-indexing) rather than data loss.

How does the atomic file write prevent data loss?

The implementation writes to a temporary file, flushes to disk with fsync, then performs an atomic rename. This guarantees that readers either see the complete new file or the previous version, never a partial write. If the process crashes during the write, the temporary file is discarded, leaving the previous version intact.

Can I manually trigger a re-index of the SQLite database?

Yes. The re-indexing logic is encapsulated in the store initialization code and can be invoked programmatically. While typically run automatically on startup, you can force a rebuild by deleting the SQLite database file and restarting the application, or by calling the internal reindex helper directly if building a custom interface.

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