# How ai-memory Ensures Transactional Consistency for Index Updates

> ai-memory guarantees transactional consistency for index updates by routing all writes through a single-writer actor, ensuring atomic SQLite commits and automatic rollbacks.

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

---

**ai-memory guarantees transactional consistency for index updates by routing all write operations through a single-writer actor that executes SQLite transactions spanning both the page data and the FTS5 search index, ensuring atomic commits and automatic rollbacks on failure.**

ai-memory is a knowledge management system that stores markdown pages and their searchable embeddings in a unified SQLite database. To prevent index divergence during concurrent writes, the system implements a strict transactional model where all mutations—page writes, deletions, hand-offs, and embedding updates—are bundled into atomic database transactions that commit only when every component is consistent.

## Single-Writer Actor Architecture

All write operations in ai-memory flow through a dedicated writer thread managed by the `WriterHandle` struct defined in [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs). This actor model, orchestrated by [`crates/ai-memory-core/src/actor.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-core/src/actor.rs), ensures that only one thread ever mutates the database at any given time.

By serializing access through a single writer, ai-memory eliminates race conditions that could otherwise corrupt the FTS5 index or embedding tables. When a client calls `write_page` or `apply_batch`, the request is queued to the writer actor, which opens a SQLite transaction and processes the entire batch before releasing the lock.

## Atomic SQLite Transactions for Index Updates

The core consistency mechanism relies on SQLite's built-in transaction support. In [`crates/ai-memory-store/src/writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/writer.rs), every public write method follows this pattern (see lines 245–260):

```rust
let tx = conn.transaction()?;
// ... execute statements against pages table ...
// ... execute statements against FTS5 virtual table ...
tx.commit()?;

```

The `conn.transaction()?` call opens a deferred transaction that captures the current database state. All SQL statements affecting the raw page rows, the FTS5 full-text index rows, and the embedding vectors execute inside this transaction boundary. Only when `tx.commit()?` succeeds does SQLite persist the changes to disk. If any statement fails, the transaction automatically rolls back, leaving the index in its previous consistent state.

## Coordinating Filesystem State with Database Commits

ai-memory stores the canonical markdown source on the filesystem while mirroring the searchable content in SQLite. To prevent skew between these two storage layers, [`crates/ai-memory-wiki/src/wiki.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-wiki/src/wiki.rs) implements a specific ordering: the filesystem rename happens *before* the database transaction begins, and the index update occurs *inside* the transaction.

For example, when `reindex_page_locked` processes a file change (lines 1260–1275), it first persists the new markdown content to disk, then immediately calls `self.store.reindex_page_locked(&tx, …)` within the same SQLite transaction. This ensures that if the index update fails, the transaction rolls back and the next read operation will detect the filesystem change and retry, maintaining the invariant that **"the index is always a faithful representation of the persisted pages"** (documented at line 75 of the same file).

## Batching Embeddings and Search Index Updates

Embeddings are not treated as secondary data. In [`crates/ai-memory-store/src/ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs), the `upsert_pages` function inserts both the page metadata and the generated vector embeddings within the same transaction boundary (see lines 290–312 and 322–340 in writer.rs).

```rust
// Example: Safely write a page and update the index in one transaction
let page = Page::new(...);
let mut writer = store.writer_handle();
writer.write_page(page).await?; // internally opens a transaction, writes markdown, upserts page + index

```

This bundling prevents scenarios where a page exists in the FTS5 text index but lacks its corresponding embedding vector, or vice versa. The semantic search table and the full-text index commit together as a single atomic unit.

## Failure Handling and Automatic Rollback

Error handling in the writer actor is designed to prevent partial state exposure. If any operation inside the transaction—whether an embedding calculation, an FTS5 insert, or a page metadata update—returns an error, the error bubble propagates up to trigger `tx.rollback()?`. 

Because SQLite transactions provide ACID isolation, readers never observe intermediate states. Either the entire batch of index updates is visible, or none are. This rollback mechanism ensures that filesystem changes (like renamed markdown files) never result in orphaned index entries, and database entries never point to missing content.

## Summary

- **Single-writer architecture**: All mutations route through `WriterHandle` in [`writer.rs`](https://github.com/akitaonrails/ai-memory/blob/main/writer.rs), eliminating concurrent write conflicts.
- **Transaction boundaries**: Every index update wraps page data, FTS5 rows, and embeddings in a single SQLite transaction (`conn.transaction()`).
- **Filesystem coordination**: The wiki layer renames files before opening transactions, then updates the index inside the transaction (lines 1260–1275).
- **Atomic failure recovery**: Automatic rollback on error prevents partial writes from corrupting the search index or leaving orphaned embeddings.

## Frequently Asked Questions

### What happens if a filesystem rename succeeds but the database transaction fails?

The system retains the renamed file on disk, but the index remains unchanged because the transaction rolled back. The next read operation detects the filesystem change and triggers a re-index, maintaining eventual consistency without corrupting the search index. This ensures the invariant that the index always reflects the actual page content is eventually restored.

### Does ai-memory support concurrent writers to the SQLite database?

No. According to the actor model implementation in [`crates/ai-memory-core/src/actor.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-core/src/actor.rs), all write requests are routed through a single `WriterHandle` that processes mutations sequentially. This design eliminates write-write conflicts and simplifies transactional guarantees by ensuring only one transaction is active at any time.

### How are embedding vectors kept in sync with the FTS5 text index?

Both are persisted within the same transaction via `upsert_pages` in [`crates/ai-memory-store/src/ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs). When a page is written, the embedding vector table and FTS5 index rows are inserted together (lines 322–340 in writer.rs), ensuring the semantic search index and full-text index commit atomically and never diverge.

### Can readers access partially committed index updates?

No. SQLite's transaction isolation ensures that readers either see the complete committed state or the pre-transaction state. Since the writer holds the transaction open until all index updates—including FTS5 and embedding tables—complete successfully, partial writes are never visible to concurrent read operations.