How ai-memory Versions Markdown Pages: SQLite and Git Hybrid Architecture

ai-memory treats Markdown files as the source of truth on disk while maintaining immutable version history in SQLite through an is_latest flagging system and Git checkpoints.

The ai-memory repository implements a robust versioning strategy for markdown knowledge bases, combining filesystem simplicity with database-level history tracking. This hybrid approach ensures that every edit creates an immutable record while keeping the latest content instantly accessible for search and AI retrieval. Understanding how ai-memory versioning markdown pages work reveals a carefully designed balance between durability, performance, and auditability.

On-Disk Layout and File Structure

All markdown pages reside in a predictable directory hierarchy under <data_dir>/wiki/<workspace_id>/<project_id>/<page_path>. The file on disk always contains the latest version of the page, making the filesystem itself a readable cache of current state.

In crates/ai-memory-wiki/src/wiki.rs, the Wiki::abs_path method (lines 80-89) resolves the absolute filesystem location for any logical page path. This ensures that external editors can read and modify files directly without needing database access, while the system maintains consistency through re-indexing workflows.

SQLite Version Schema and Metadata

The pages table in the SQLite store acts as an append-only ledger where every write creates a distinct row. Each version row contains:

• id – The unique version identifier
• workspace_id, project_id, path – The composite logical key locating the page
• is_latest – A boolean flag set to 1 only for the current version
• supersedes – Foreign key referencing the id of the previous version (if any)

This schema enables constant-time lookups of current content while preserving the complete linear history. The is_latest column is indexed to support high-throughput read operations from the ReaderPool.

Writing New Versions Atomically

When creating a new version, Wiki::write_page (or the CLI ai-memory write-page command) executes a coordinated two-phase commit across filesystem and database.

First, the markdown body is written atomically to disk using atomic::write_atomic, preventing corruption from partial writes or process crashes. Then, WriterHandle::upsert_page handles the database transaction:

1. Inserts a new row with is_latest = 1
2. Updates the previous latest row for the same (workspace, project, path) to is_latest = 0
3. Records the superseded version's id in the supersedes column of the new row

According to the source code in crates/ai-memory-wiki/src/wiki.rs (lines 1629-1630), this transaction ensures that "the new row becomes the sole is_latest = 1 version," maintaining strict consistency.

# Create a new version via CLI

ai-memory write-page \
  --workspace <workspace-uuid> \
  --project <project-uuid> \
  --path notes/todo.md \
  --title "Todo List" \
  --body "- [ ] Finish report"

Reading the Latest Version

The ReaderPool in crates/ai-memory-store/src/reader.rs provides optimized queries for retrieving current content. The latest_page_id_by_ids method (lines 1638-1650) executes a SELECT filtering on is_latest = 1, returning the current version ID without scanning historical rows.

All consumer queries—from the web UI to search indexing to auto-improve workflows—filter on is_latest = 1 to ensure they operate only on authoritative content.

let reader = store.reader_pool().await?;
let latest_id = reader
    .latest_page_id_by_ids(workspace_id, project_id, "notes/todo.md".into())
    .await?;
println!("latest version id = {}", latest_id.unwrap());

Re-indexing and Content-Addressed Deduplication

When files are edited outside the write pipeline (via external editors or Git operations), Wiki::reindex_page_locked (lines 1624-1630) reconciles the filesystem state with the database. The method parses the modified file and calls WriterHandle::upsert_page, but includes a critical optimization: SHA-256 content hashing.

Before inserting a new version, the system checks if the file's hash matches the current database record. If the content is unchanged, the existing row (already marked is_latest = 1) is preserved, preventing unnecessary version bloat from no-op modifications.

Soft Deletes and Decay Workflows

The versioning system supports lifecycle management through Writer::soft_delete_for_decay_if_latest in crates/ai-memory-store/src/ops.rs (lines 645-652). When a page expires under retention policies, the operation marks the latest row as "decayed" while preserving the version chain.

Importantly, the is_latest flag remains set until the decay tombstone is removed, ensuring that retention sweeps cannot delete content that was refreshed after selection. This prevents race conditions between cleanup jobs and active edits.

Git Checkpoint Integration

Beyond the SQLite versioning, ai-memory maintains a Git repository of the entire wiki tree. The GitAdapter enables restoration of historic checkpoints through Wiki::restore_page_from_checkpoint.

When restoring from a Git commit hash, the system:

1. Checks out the file content from the specified commit
2. Writes it to the live filesystem path
3. Upserts the content as a new SQLite version with a fresh id

This preserves the linear SQLite history while allowing point-in-time recovery from Git. The restored content becomes the new is_latest version, with the previous version referenced in supersedes.

wiki.restore_page_from_checkpoint(
    workspace_id,
    project_id,
    "notes/todo.md".into(),
    "a1b2c3d4",  // Git commit hash
).await?;

Summary

• Filesystem as cache: Markdown files on disk always reflect the latest version, enabling direct external editing
• Immutable SQLite ledger: The pages table stores every write as a new row with is_latest flagging and supersedes linking for history traversal
• Atomic coordination: Wiki::write_page uses atomic::write_atomic for filesystem safety and WriterHandle::upsert_page for transactional database updates
• Content-aware deduplication: Re-indexing compares SHA-256 hashes to skip creating redundant versions when content is unchanged
• Dual recovery: Git checkpoints provide snapshot recovery capabilities that feed into the SQLite versioning stream as new revisions

Frequently Asked Questions

How does ai-memory prevent version conflicts during concurrent writes?

All database operations occur within SQLite transactions managed by WriterHandle::upsert_page in crates/ai-memory-store/src/writer.rs. The transaction updates the previous is_latest = 1 row to 0 and inserts the new row atomically, ensuring that only one version can hold the latest flag at any moment regardless of concurrency.

Can I access historical versions directly from the filesystem?

No. The filesystem only stores the current version. Historical versions exist solely in the SQLite store (crates/ai-memory-store/src/reader.rs). To access previous content, use the ReaderPool APIs querying by version_id or restore from Git checkpoints.

What happens if I edit a markdown file in Vim or VS Code?

External edits are detected during the next re-index operation. Wiki::reindex_page_locked hashes the file content; if changed, it creates a new database version. If unchanged (or reverted to a previous hash), the existing is_latest row remains unchanged to prevent version spam.

How does the Git integration handle merge conflicts?

The Wiki::restore_page_from_checkpoint method treats Git as a read-only source of historical bytes. It does not perform merges; instead, it restores the exact file state from a specific commit hash and commits that content as a new version in SQLite, leaving conflict resolution to standard Git tools outside the versioning flow.

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