# How ai-memory Implements SQLite Backup and Restore via Tar Archives Without Corrupting WAL-Mode Databases

> Learn how ai-memory creates atomic, corruption-free tar.gz backups of live WAL-mode SQLite databases using the online backup API for reliable restore.

- Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory)
- Tags: how-to-guide
- Published: 2026-08-30

---

**The `ai-memory` project creates atomic backups of live WAL-mode SQLite databases using SQLite's online backup API to snapshot the database, then bundles the snapshot with WAL files and wiki content into a validated tar.gz archive for corruption-free restore.**

The `ai-memory` repository implements a robust backup and restore system for its knowledge base, which combines a SQLite database (operating in WAL mode for concurrent performance) with a file-based wiki. This article explains how the codebase safely archives this live state without data corruption, drawing directly from the Rust source implementation in the `akitaonrails/ai-memory` project.

## How WAL-Mode SQLite Complicates Hot Backups

SQLite's **Write-Ahead Logging (WAL) mode** improves concurrency by appending changes to a separate `-wal` file rather than modifying the main database file directly. This creates a three-file dependency: the main `.sqlite` file, the `.sqlite-wal` file containing uncheckpointed transactions, and the `.sqlite-shm` shared-memory index.

Naive file copies of a live WAL database risk **split-brain corruption**—if the main file and WAL file are captured at different moments, the database becomes inconsistent. The `ai-memory` codebase solves this through a deliberate two-phase approach: first creating a consistent logical snapshot via SQLite's native API, then atomically packaging all required files.

## Phase 1: Creating a Consistent Database Snapshot with SQLite Online Backup

The foundation of `ai-memory`'s backup safety is SQLite's **online backup API**, which copies database pages transactionally while the database remains writable by other connections. This is implemented in the storage layer.

In [`crates/ai-memory-store/src/reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/reader.rs), the `snapshot_db` function performs the atomic extraction:

```rust
/// Snapshot the database to `dest_path` using SQLite's online backup
// Located at reader.rs lines 4360-4367
conn.backup(rusqlite::DatabaseName::Main, &dest_path, None)?;

```

The `backup` method with `DatabaseName::Main` targets the primary database (excluding attached databases). Passing `None` for the progress callback allows the operation to run to completion. Critically, this API acquires the necessary locks to ensure the snapshot represents a single point-in-time state, regardless of concurrent writes.

The caller in `ai-memory-mcp` logs this operation for observability:

```rust
let snapshot_path = state.paths.db_snapshot();
info!(snapshot = %snapshot_path.display(), "snapshotting SQLite for backup");
conn.backup(rusqlite::DatabaseName::Main, &snapshot_path, None)?;

```

This snapshot file is written to a temporary location (`state.paths.db_snapshot()`) rather than the live database path, ensuring the backup process never interferes with normal operations.

## Phase 2: Assembling the Tar Archive with WAL Files and Wiki Content

Once the consistent snapshot exists, the admin HTTP handler in [`crates/ai-memory-mcp/src/admin.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-mcp/src/admin.rs) (lines 714-735) constructs the final archive. This routine is exposed via the `POST /admin/backup` endpoint and handles three packaging tasks:

### 2.1 Including the Snapshot and WAL Files

The tarball contains the snapshot database plus, critically, the original WAL and SHM files from the live system:

```rust
let mut tar = tar::Builder::new(GzEncoder::new(
    File::create(&tar_path)?,
    Compression::default()
));

// Add the consistent snapshot
tar.append_path_with_name(&snapshot_path, "db.sqlite")?;

// Include WAL file if present
if snapshot_path.with_extension("wal").exists() {
    tar.append_path_with_name(
        &snapshot_path.with_extension("wal"),
        "db.sqlite-wal"
    )?;
}

// Include shared-memory file if present
if snapshot_path.with_extension("shm").exists() {
    tar.append_path_with_name(
        &snapshot_path.with_extension("shm"),
        "db.sqlite-shm"
    )?;
}

```

This design provides **dual protection**: the online backup API ensures the snapshot itself is internally consistent, while including the WAL files preserves any uncheckpointed state that may exist in the live system. During restore, SQLite can reconcile these components.

### 2.2 Archiving the Wiki Directory

The wiki content—markdown files organized under `wiki/<workspace>/<project>/`—is added via `append_dir_all`:

```rust
tar.append_dir_all("wiki", &state.paths.wiki_dir)?;
tar.finish()?;

```

The tar builder preserves the directory hierarchy, ensuring restored paths match the original structure exactly.

### 2.3 Security: Preventing Symlink Traversal

A security-critical implementation detail appears earlier in [`admin.rs`](https://github.com/akitaonrails/ai-memory/blob/main/admin.rs) (lines 215-259): the codebase deliberately **does not dereference symlinks** when walking the wiki directory. This prevents a malicious or accidental symlink from causing the backup to include files outside the intended wiki root, which could lead to information leakage or archive tampering.

## Phase 3: Validated Restore with Path Enforcement

The restore operation in [`crates/ai-memory-cli/src/commands/restore.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-cli/src/commands/restore.rs) implements strict validation before extraction. At lines 102-117, the code inspects every tar entry:

```rust
// Validate each entry matches expected paths
for entry in archive.entries()? {
    let entry = entry?;
    let path = entry.path()?;
    
    // Reject paths outside allowed set: db.sqlite, db.sqlite-wal, 
    // db.sqlite-shm, or under wiki/
    if !is_allowed_backup_path(&path) {
        bail!("Invalid path in backup archive: {:?}", path);
    }
}

```

This validation ensures that even if a backup archive is tampered with, the restore process cannot write to arbitrary filesystem locations. After validation, the archive unpacks directly into the configured root directory, with the SQLite files placed where the application expects them.

Because the backup contains both the consistent snapshot and the original WAL files, the restored database opens without requiring manual checkpoint recovery. SQLite's own startup logic reconciles any differences, presenting a clean database state to the application.

## Testing and Design Documentation

The implementation includes integration verification in [`crates/ai-memory-mcp/tests/admin_backup.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-mcp/tests/admin_backup.rs) (lines 84-134). These tests assert that:

- The produced tarball is a valid gzip stream
- Seeded wiki pages are recoverable after round-trip backup and restore

Additionally, design notes in the consolidate crate reference the approach: *"SQLite online backup API snapshots the database while writes continue … used by ai-memory backup to produce a consistent tarball"* (referenced in test context at [`recall_eval.rs`](https://github.com/akitaonrails/ai-memory/blob/main/recall_eval.rs) lines 56-58).

## Summary

- **Online backup API first**: `ai-memory` uses `rusqlite::Connection::backup()` to create a transactionally consistent snapshot of the live WAL-mode database in [`reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/reader.rs)
- **WAL files preserved**: The archive includes `.sqlite-wal` and `.sqlite-shm` alongside the snapshot for complete state capture in [`admin.rs`](https://github.com/akitaonrails/ai-memory/blob/main/admin.rs)
- **Atomic tar construction**: gzipped tar archives bundle database and wiki content without interrupting normal operations
- **Path-validated restore**: The CLI restore command enforces strict entry whitelisting to prevent directory traversal attacks
- **Symlink security**: Wiki walking deliberately avoids dereferencing symlinks to prevent information leakage

## Frequently Asked Questions

### Does ai-memory require stopping the database to create backups?

No. The `ai-memory` backup system operates on live databases using SQLite's online backup API. The `backup()` call in [`reader.rs`](https://github.com/akitaonrails/ai-memory/blob/main/reader.rs) acquires brief locks to ensure consistency while allowing concurrent reads and writes to proceed. This is essential for the MCP server architecture where the database remains active.

### Why include WAL files if the online backup already creates a consistent snapshot?

The WAL files provide additional safety margin. While the online backup produces a self-consistent database, including the original WAL files ensures that even edge cases—such as a crash during backup or unusual checkpoint timing—can be recovered. SQLite's startup logic uses these files if needed, making restores more robust.

### What prevents a malicious backup archive from overwriting system files during restore?

The restore implementation in [`restore.rs`](https://github.com/akitaonrails/ai-memory/blob/main/restore.rs) validates every tar entry path against an explicit whitelist (`is_allowed_backup_path`). Only paths matching `db.sqlite`, `db.sqlite-wal`, `db.sqlite-shm`, or entries under `wiki/` are permitted. Any unexpected path triggers an immediate error before extraction occurs.

### How does ai-memory handle large wiki directories during backup?

The tar builder streams content through a `GzEncoder`, avoiding memory buffering of the entire archive. The `append_dir_all` method walks the wiki directory iteratively, and the snapshot is created as a temporary file on disk rather than in memory. This design supports arbitrarily large knowledge bases without memory pressure.