# Understanding the ai-memory SQLite Database Structure: Schema, Tables, and Relationships

> Explore the ai-memory SQLite database structure. Learn about its migration-driven schema, hierarchical workspace-project-entity model, versioned wiki pages, and full-text search capabilities.

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

---

**The ai-memory SQLite database uses a migration-driven schema centered on a hierarchical workspace-project-entity model with versioned wiki pages, full-text search, and audit logging.**

The `akitaonrails/ai-memory` project persists all data in a single SQLite file managed through incremental SQL migrations. The ai-memory SQLite database structure follows a strict normalization pattern where every core table references both `workspaces` and `projects`, establishing a three-tuple identity that scopes all content. This design supports versioned documentation, AI session tracking, and cross-session handoffs while maintaining referential integrity through cascading foreign keys.

## Core Schema Architecture

The database schema is defined incrementally via migration scripts stored in `crates/ai-memory-store/migrations/`. Each file adds tables, indexes, or triggers to evolve the structure without breaking existing data.

**Key migration files include:**
- [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql) — Core tables: `workspaces`, `projects`, `pages`, `sessions`, `observations`, `links`, and `audit_log`
- [`V02__handoffs.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V02__handoffs.sql) — Session handoff snapshots and workflow state
- [`V03__decay.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V03__decay.sql) — Relevance scoring via the `decay` table
- [`V36__page_expiry.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V36__page_expiry.sql) — Optional page expiration timestamps
- [`V37__page_feedback.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V37__page_feedback.sql) — User rating and comment storage
- [`V38__entities.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V38__entities.sql) — Generic entity bucket for auto-improvement pipelines

According to the source code in [`crates/ai-memory-store/src/schema.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/schema.rs), Rust structs mirror these SQL definitions, providing type-safe access to the SQLite backend.

## Main Tables and Relationships

The schema revolves around four functional domains: project organization, documentation, session management, and operational metadata.

### Workspaces and Projects

The hierarchy begins with **workspaces**, scoped by `id` (BLOB), `name`, and `created_at`. Every subsequent table references `workspace_id` with cascade deletion.

**Projects** nest inside workspaces with a unique constraint on `(workspace_id, name)` to enforce single naming per workspace. The `repo_path` column links the database record to a local filesystem repository. Foreign keys from `projects` to `workspaces` cascade on delete, ensuring workspace removal cleans all associated data.

### Pages and Full-Text Search

The **pages** table implements a versioning system for wiki content. Each row stores `path`, `title`, `body`, `body_sha256`, and `frontmatter_json`, alongside versioning flags.

**Critical columns:**
- `is_latest` — Boolean flag marking the current version
- `supersedes` — Self-referencing foreign key (set-null) linking to the previous version
- `pinned` — Boolean indicating sticky status
- Embedding fields (`embedding_provider`, `embedding_model`, `embedding_dim`) — Optional vector metadata

A partial unique index enforces only one latest version per `(workspace_id, project_id, path)` combination.

**Full-text search** is implemented via the **pages_fts** virtual table using FTS5. Triggers (`pages_fts_ai`, `pages_fts_ad`, `pages_fts_au`) automatically synchronize inserts, deletes, and updates between `pages` and `pages_fts`, indexing `title` and `body` columns.

### Sessions and Observations

**Sessions** represent AI-agent execution contexts with columns for `agent_kind`, `cwd` (current working directory), and `summary_page_id` (linking to generated documentation). Both `workspace_id` and `project_id` foreign keys cascade on deletion.

**Observations** capture discrete events during a session. The table links to `sessions(id)` with cascade deletion and stores `kind`, `title`, `body`, and an `importance` score. This creates an immutable log of agent activity tied to specific workspace and project scopes.

### Links and Navigation

The **links** table uses a composite primary key `(from_page_id, to_path, link_type)` to store explicit markdown relationships. Foreign keys reference `pages(id)` with cascade deletion on the source and set-null on the target, allowing link preservation even if the destination page is renamed or deleted.

### Handoffs and Workflow State

Added in [`V02__handoffs.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V02__handoffs.sql), the **handoffs** table enables session resumption across different agents. It stores:
- `from_session_id` and `from_agent` (source context)
- `to_agent` and `cwd` (target context)
- JSON fields for `questions`, `steps`, and `files`
- `state` — Enum values: `open`, `accepted`, `expired`
- `accepted_by_session` — Links to the resuming session (set-null)

Handoffs maintain foreign keys to workspaces, projects, and sessions with appropriate cascade and set-null behaviors to preserve history while allowing cleanup.

### Auxiliary Tables

**Entities** ([`V38__entities.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V38__entities.sql)) provides a generic bucket for auto-improvement systems, storing `kind` and `metadata` JSON with references to pages, projects, and workspaces.

**Page_feedback** ([`V37__page_feedback.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V37__page_feedback.sql)) captures user ratings and comments with foreign keys to `pages(id)` and `users(id)`.

**Page_expiry** ([`V36__page_expiry.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V36__page_expiry.sql)) manages content lifecycle via an `expires_at` timestamp tied to `pages(id)` with cascade deletion.

**Decay** ([`V03__decay.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V03__decay.sql), modified in [`V49__decay_tombstone_index.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V49__decay_tombstone_index.sql)) stores relevance scores for ranking, linking `entity_id` to `entities(id)` with cascade deletion and tracking `decay_factor` and `updated_at`.

**Audit_log** is the exception to the workspace-project rule. It stores immutable operation records with `id` (INTEGER AUTOINCREMENT), `at` timestamp, `op` type, and optional references to workspace, project, or page IDs without foreign key constraints, guaranteeing log survival even after data deletion.

## Querying the Database Structure

Direct SQL access allows inspection of the ai-memory SQLite database structure and content. Below are practical queries demonstrating the schema relationships.

Retrieve the latest version of every page in a specific workspace and project:

```sql
SELECT p.id, p.path, p.title, p.body
FROM pages p
WHERE p.workspace_id = ? AND p.project_id = ? AND p.is_latest = 1;

```

Execute full-text search over page content using the FTS5 virtual table:

```sql
SELECT p.id, p.title, snippet(pages_fts) AS snippet
FROM pages_fts
JOIN pages p ON pages_fts.rowid = p.rowid
WHERE pages_fts MATCH 'machine learning';

```

List all open handoffs awaiting agent acceptance:

```sql
SELECT h.id, h.summary, h.created_at, h.from_agent, h.to_agent
FROM handoffs h
WHERE h.workspace_id = ? AND h.project_id = ? AND h.state = 'open';

```

Extract the decay scores for relevance-ranked entities:

```sql
SELECT d.entity_id, d.decay_factor, d.updated_at
FROM decay d
JOIN entities e ON d.entity_id = e.id
WHERE e.workspace_id = ? AND e.project_id = ?
ORDER BY d.decay_factor DESC;

```

## Summary

- **Hierarchical scoping**: Every table except `audit_log` references `workspaces` and `projects`, enforcing strict data isolation through foreign keys with cascade deletion.
- **Versioned documentation**: The `pages` table uses `is_latest` flags and `supersedes` relationships to maintain historical versions while exposing only current content through partial unique indexes.
- **Integrated search**: The `pages_fts` FTS5 virtual table, synchronized via triggers, provides full-text search capabilities without application-level indexing.
- **Session continuity**: Handoffs with JSON metadata and state tracking enable complex AI agent workflows across multiple executions.
- **Migration-driven evolution**: Schema changes are applied through versioned SQL files in `crates/ai-memory-store/migrations/`, ensuring deterministic database state across deployments.

## Frequently Asked Questions

### What is the primary key type used in ai-memory tables?

Most tables use `id` with a **BLOB** type as their primary key. The exception is the `audit_log` table, which uses `INTEGER AUTOINCREMENT` for its `id` column to ensure strictly sequential, immutable log entries.

### How does ai-memory handle page versioning without losing history?

The database implements soft versioning through the `is_latest` boolean column and the `supersedes` foreign key. When a page updates, the old row remains with `is_latest = 0`, while the new row points to it via `supersedes` and sets `is_latest = 1`. A partial unique index on `(workspace_id, project_id, path)` where `is_latest = 1` guarantees data integrity while preserving historical versions.

### Can I query the ai-memory database directly without using the Rust API?

Yes. Since ai-memory uses standard SQLite, you can open the database file directly with the `sqlite3` CLI or any SQLite-compatible tool. The schema uses standard SQL types and foreign key constraints, though you should respect the triggers on `pages_fts` to avoid desynchronizing the full-text search index. Direct queries against `crates/ai-memory-store/migrations/` files reveal the exact table definitions.

### What is the purpose of the decay table in the ai-memory schema?

The **decay** table stores relevance scores for entities used by ranking algorithms. It links to the `entities` table via `entity_id` with cascade deletion, tracking a `decay_factor` float and `updated_at` timestamp. This supports relevance-based retrieval where older or less-accessed content receives lower priority scores, implemented originally in [`V03__decay.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V03__decay.sql) and optimized with tombstone indexes in [`V49__decay_tombstone_index.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V49__decay_tombstone_index.sql).