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

> Discover how ai-memory ensures atomic writes to Markdown and SQLite using a write-once-commit pattern. Learn about its tmp-rename-fsync and single-writer actor techniques for data integrity.

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

---

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

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

```rust
// 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.

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

```rust
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`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/atomic.rs) | `write_atomic` implementation with tmp‑rename‑fsync |
| [`crates/ai-memory-wiki/src/wiki.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/wiki.rs) | High‑level wiki API integrating atomic writes |
| [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs) | Single‑writer SQLite actor for transactional mutations |
| [`crates/ai-memory-store/src/ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs) | Operation structs like `UpsertPage` |
| [`crates/ai-memory-hooks/src/router.rs`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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.