How ai‑memory Handles Atomic Writes to the Markdown Wiki and SQLite Database

ai‑memory guarantees atomic writes to both the markdown wiki and SQLite database through a write‑once‑commit pattern: the markdown side uses a tmp‑rename‑fsync dance, while the SQLite side employs a single‑writer actor with serialized transactions.

The ai‑memory project treats the markdown wiki and SQLite store as a unified source of truth. Every update must leave both systems in a consistent state—or leave no trace at all. This article breaks down the exact implementation: the atomic file‑write primitive in ai_memory_wiki::atomic and the single‑writer SQLite actor in ai_memory_store::writer.

---

Atomic Writes to the Markdown Wiki

All wiki pages are persisted through ai_memory_wiki::atomic::write_atomic, implemented in crates/ai-memory-wiki/src/atomic.rs (lines 13–44). This function provides POSIX‑guaranteed atomicity on modern filesystems.

The tmp‑rename‑fsync Sequence

The implementation follows a four‑step protocol:

1. Create a temporary file in the same directory as the target, named .ai‑memory‑tmp.{random}. This ensures the rename stays on the same filesystem.
2. Write content and sync data — the full payload is written, then sync_data() flushes buffers to stable storage.
3. Atomic rename — tmp.persist(path) moves the temp file over the destination. On POSIX systems, this is atomic and race‑free.
4. Fsync the parent directory — File::open(parent).sync_all() ensures the directory entry itself is durable.

If any step fails, the temporary file remains and the original file is untouched. The function returns the inode (or Windows file index) of the written file, which the wiki watcher uses to suppress spurious change notifications.

// crates/ai-memory-wiki/src/atomic.rs (simplified)
use std::fs::File;
use std::io::Write;
use tempfile::NamedTempFile;

pub fn write_atomic(path: &std::path::Path, content: &[u8]) -> std::io::Result<u64> {
    let parent = path.parent().unwrap();
    let mut tmp = NamedTempFile::new_in(parent)?; // Step 1
    
    tmp.write_all(content)?;                      // Step 2
    tmp.as_file().sync_data()?;                   // Step 2 (fsync data)
    
    let (_, file) = tmp.persist(path)?;           // Step 3 (atomic rename)
    let inode = file.metadata()?.ino();           // Return inode for deduplication
    
    File::open(parent)?.sync_all()?;              // Step 4 (fsync directory)
    Ok(inode)
}

---

Atomic Transactions in the SQLite Database

All database mutations flow through a single‑writer actor in crates/ai-memory-store/src/writer.rs. This design eliminates lock contention and guarantees serialized, atomic transactions.

The Writer Actor Pattern

The writer owns one Tokio task that receives commands over an mpsc channel. Each command executes inside a single SQLite transaction—no interleaving possible.

• Writer::upsert_page_batch (lines 1365–1385): Opens a transaction, runs INSERT/UPDATE for every page in the batch, then commits only on full success.
• Writer::claim_observation (lines 680–710): Creates a row, appends the observation payload, and commits atomically—preventing duplicate observation keys.

Because the channel serializes callers, SQLite's SERIALIZED threading mode is satisfied without explicit locks.

// crates/ai-memory-store/src/writer.rs (conceptual flow)
impl Writer {
    async fn upsert_page_batch(&self, pages: Vec<UpsertPage>) -> Result<()> {
        let tx = self.conn.transaction()?;  // BEGIN
        
        for page in pages {
            tx.execute(
                "INSERT INTO pages ... ON CONFLICT ... DO UPDATE ...",
                params![...]
            )?;
        }
        
        tx.commit()?;  // COMMIT only if all succeeded
        Ok(())
    }
}

---

Coordinating Dual Writes for Full Consistency

To keep the markdown wiki and SQLite database synchronized, ai‑memory sequences operations rather than using a true two‑phase commit:

1. Write the markdown file first via write_atomic—this is now durable on disk.
2. Send the DB command to the writer actor—the SQLite transaction commits atomically.
3. Rollback handling: If the DB transaction fails, the caller can retry or take compensating action. The file write is already persisted, so no partial state corrupts the system.

This satisfies the project's invariant #2: "Single‑writer SQLite actor" ensures no concurrent modifications, while the atomic file primitive guarantees crash‑safe storage.

use std::path::PathBuf;
use ai_memory_wiki::atomic::write_atomic;
use ai_memory_store::writer::Writer;

// 1️⃣ Write the markdown file atomically
let path = PathBuf::from("./wiki/pages/example.md");
let content = b"# Example\nThis is a test page.";

let _inode = write_atomic(&path, content)?; // returns inode for watcher dedup

// 2️⃣ Upsert the page metadata in SQLite (executed by the writer actor)
let writer = Writer::new(/* … */)?;
writer.upsert_page_batch(vec![
    ai_memory_store::ops::UpsertPage {
        path: path.clone(),
        frontmatter: None,
        body: content.to_vec(),
        // …
    },
])?;

---

Hook Integration: Atomic Claims with Wiki Writes

The ai‑memory‑hooks crate ties observations to wiki updates. The claim_observation method in Writer atomically reserves an observation key, then the hook handler writes the derived page.

use ai_memory_hooks::router::handle_hook;
use ai_memory_wiki::atomic::write_atomic;

// Inside a hook handler
let observation = handle_hook(payload)?; // Atomically claims in SQLite
let page_path = observation.page_path();
write_atomic(&page_path, observation.body())?; // Atomically writes to wiki

If the file write fails, the observation remains claimed—preventing reprocessing. If the DB claim had failed, no file write would occur.

---

Key Source Files

File	Purpose
crates/ai-memory-wiki/src/atomic.rs	write_atomic implementation with tmp‑rename‑fsync
crates/ai-memory-wiki/src/wiki.rs	High‑level wiki API integrating atomic writes
crates/ai-memory-store/src/writer.rs	Single‑writer SQLite actor for transactional mutations
crates/ai-memory-store/src/ops.rs	Operation structs like UpsertPage
crates/ai-memory-hooks/src/router.rs	Hook routing that coordinates DB claims with file writes

---

Summary

• Atomic file writes use write_atomic in crates/ai-memory-wiki/src/atomic.rs: temp file creation, data fsync, atomic rename, directory fsync.
• SQLite atomicity is enforced by a single‑writer actor in crates/ai-memory-store/src/writer.rs: all mutations serialize through one channel, each in a single transaction.
• Dual consistency is achieved by sequencing: durable file write first, then atomic DB transaction, with caller‑managed rollback.

---

Frequently Asked Questions

What happens if the SQLite transaction fails after the markdown file is written?

The file write is already durable, so the original markdown remains valid. The caller detects the DB failure and can retry the SQLite upsert or take compensating action. No partial state is exposed because the database never committed—and the wiki watcher ignores writes from its own inode.

Why does ai‑memory use a single‑writer actor instead of connection pooling?

SQLite handles concurrency poorly under high write contention. By funneling all writes through one Tokio task via an mpsc channel, ai‑memory eliminates lock conflicts and guarantees statement serialization. This satisfies SQLite's SERIALIZED mode requirements without explicit locking primitives.

Does the atomic write work on Windows and macOS?

Yes. The write_atomic function uses the tempfile crate, which abstracts platform differences. On Windows, persist uses MoveFileExW with MOVEFILE_REPLACE_EXISTING; on macOS and Linux, it uses rename which is atomic. The parent directory fsync is skipped on Windows where it has limited utility.

How does the wiki watcher avoid detecting its own writes?

write_atomic returns the inode (or file index on Windows) of the newly written file. The wiki watcher maintains a set of recently written inodes and ignores change events matching those IDs, preventing feedback loops where a write triggers re‑indexing that triggers another write.

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