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

> Discover how ai-memory versions Markdown pages using a hybrid SQLite and Git architecture. Learn about its unique approach to immutable version history and source of truth management.

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

---

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

```bash

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

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

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