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

> Discover how ai-memory maintains SQLite index consistency with its Markdown wiki using atomic writes, automatic rollbacks, and startup re-indexing. Learn about its robust data integrity approach.

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

---

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

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

```rust
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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-mcp/src/server.rs) exposes the same functionality through the `memory_write_page` tool:

```bash
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.