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

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, the snapshot_db function performs the atomic extraction:

/// 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:

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 (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:

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:

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 (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 implements strict validation before extraction. At lines 102-117, the code inspects every tar entry:

// 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 (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 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
• WAL files preserved: The archive includes .sqlite-wal and .sqlite-shm alongside the snapshot for complete state capture in 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 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 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.

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