# ai‑memory Database Schema: Complete Guide to Tables, Indexes, and Migration Files

> Explore the ai-memory database schema. Understand its nine tables, indexes, and migration files for efficient data management and advanced AI capabilities.

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

---

**The ai‑memory database uses SQLite with nine core tables—workspaces, projects, pages, sessions, observations, links, handoffs, audit_log, and page_embeddings—organized around a three‑tuple identity (`workspace_id`, `project_id`, `path`) with full‑text search, vector embeddings, and automated decay tracking.**

The ai‑memory project provides a structured persistence layer for AI agent contexts, wiki‑style documentation, and cross‑session memory. Understanding the **ai‑memory database schema** is essential for querying data, writing migrations, or extending the system. This guide breaks down every table, constraint, index, and migration file as implemented in the [akitaonrails/ai‑memory](https://github.com/akitaonrails/ai-memory) repository.

---

## Core Architectural Concepts

### Three‑Tuple Identity

Every domain object in ai‑memory belongs to a hierarchical namespace defined by:

- **`workspace_id`** – top‑level organizational container
- **`project_id`** – code repository or logical project within a workspace
- **`path`** – unique identifier for pages within a project

This design enables multi‑tenancy and clean data isolation. Foreign key relationships enforce `ON DELETE CASCADE` or `SET NULL` to maintain referential integrity.

### Migration‑Based Evolution

The schema evolves through numbered SQL files in `crates/ai-memory-store/migrations/`. Current production schema spans **V01 through V58**, with foundational tables established in V01–V05.

---

## Core Tables in the ai‑memory Database Schema

### workspaces

The root container for all data.

| Column | Type | Constraints |
|--------|------|-------------|
| `id` | BLOB | PRIMARY KEY |
| `name` | TEXT | UNIQUE |
| `created_at` | INTEGER | epoch microseconds |

**Source file:** [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql)

---

### projects

Groups related pages and sessions under a workspace.

| Column | Type | Constraints |
|--------|------|-------------|
| `id` | BLOB | PRIMARY KEY |
| `workspace_id` | BLOB | FK → workspaces.id |
| `name` | TEXT | part of UNIQUE(workspace_id, name) |
| `repo_path` | TEXT | filesystem path |
| `created_at` | INTEGER | |

The composite unique constraint prevents duplicate project names within a workspace.

---

### pages

Versioned wiki documents with full‑text search and embedding support.

| Column | Type | Constraints |
|--------|------|-------------|
| `id` | BLOB | PRIMARY KEY |
| `workspace_id` | BLOB | FK |
| `project_id` | BLOB | FK |
| `path` | TEXT | part of identity tuple |
| `title` | TEXT | |
| `tier` | TEXT | CHECK(tier IN ('stable', 'working', 'scratch', 'archived')) |
| `body` | TEXT | markdown content |
| `body_sha256` | BLOB | content hash for deduplication |
| `frontmatter_json` | TEXT | DEFAULT '{}' |
| `is_latest` | INTEGER | CHECK(0 or 1) |
| `supersedes` | BLOB | FK → pages.id (nullable) |
| `pinned` | INTEGER | CHECK(0 or 1) |
| `created_at` | INTEGER | |
| `updated_at` | INTEGER | |
| `last_accessed_at` | INTEGER | added V03 |
| `access_count` | INTEGER | DEFAULT 0, added V03 |
| `superseded_at` | INTEGER | added V03 |
| `embedding_provider` | TEXT | |
| `embedding_model` | TEXT | |
| `embedding_dim` | INTEGER | |

The `tier` column implements a lifecycle policy: **stable** (reference docs), **working** (active development), **scratch** (temporary), **archived** (deprecated).

**Critical constraint:** Only one row per `(workspace_id, project_id, path)` may have `is_latest = 1`. Enforced by `idx_pages_latest_path` unique index.

---

### sessions

Represents a single agent execution context.

| Column | Type | Constraints |
|--------|------|-------------|
| `id` | BLOB | PRIMARY KEY |
| `workspace_id` | BLOB | FK |
| `project_id` | BLOB | FK |
| `agent_kind` | TEXT | CHECK(agent_kind IN ('planner', 'executor', 'reviewer', 'user')) |
| `cwd` | TEXT | current working directory |
| `started_at` | INTEGER | |
| `ended_at` | INTEGER | nullable |
| `summary_page_id` | BLOB | FK → pages.id |

Sessions capture temporal boundaries for observation collection and handoff generation.

---

### observations

Arbitrary data captured during a session.

| Column | Type | Constraints |
|--------|------|-------------|
| `id` | BLOB | PRIMARY KEY |
| `session_id` | BLOB | FK → sessions.id |
| `workspace_id` | BLOB | FK |
| `project_id` | BLOB | FK |
| `kind` | TEXT | e.g., 'note', 'tool_call', 'error' |
| `title` | TEXT | |
| `body` | TEXT | |
| `importance` | INTEGER | CHECK(1–10) |
| `created_at` | INTEGER | |

The `importance` score drives retention priority in decay sweeps.

---

### links

Bidirectional page relationships with typed semantics.

| Column | Type | Constraints |
|--------|------|-------------|
| `from_page_id` | BLOB | FK → pages.id, part of PK |
| `to_path` | TEXT | part of PK |
| `link_type` | TEXT | DEFAULT 'references', part of PK |
| `to_page_id` | BLOB | FK → pages.id (nullable) |

Composite primary key: `(from_page_id, to_path, link_type)`. The nullable `to_page_id` supports links to not‑yet‑created pages.

---

### handoffs

Structured snapshots for agent‑to‑agent context transfer.

| Column | Type | Constraints |
|--------|------|-------------|
| `id` | BLOB | PRIMARY KEY |
| `workspace_id` | BLOB | FK |
| `project_id` | BLOB | FK |
| `from_session_id` | BLOB | FK → sessions.id (nullable) |
| `from_agent` | TEXT | |
| `to_agent` | TEXT | |
| `cwd` | TEXT | |
| `summary` | TEXT | |
| `open_questions` | TEXT | DEFAULT '[]', JSON array |
| `next_steps` | TEXT | DEFAULT '[]', JSON array |
| `files_touched` | TEXT | DEFAULT '[]', JSON array |
| `state` | TEXT | CHECK(state IN ('open', 'accepted', 'expired')) |
| `created_at` | INTEGER | |
| `updated_at` | INTEGER | |
| `accepted_by` | TEXT | |
| `accepted_by_session` | BLOB | FK → sessions.id |

Added in [`V02__handoffs.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V02__handoffs.sql). The state machine enforces valid handoff lifecycles.

---

### audit_log

Append‑only event stream for debugging and compliance.

| Column | Type | Constraints |
|--------|------|-------------|
| `id` | INTEGER | AUTOINCREMENT PRIMARY KEY |
| `at` | INTEGER | |
| `op` | TEXT | operation name |
| `workspace_id` | BLOB | nullable FK |
| `project_id` | BLOB | nullable FK |
| `page_id` | BLOB | nullable FK |
| `detail` | TEXT | DEFAULT '{}' |

No foreign key constraints—log survives referenced row deletion.

---

### page_embeddings

Vector storage for semantic search.

| Column | Type | Constraints |
|--------|------|-------------|
| `page_id` | BLOB | FK → pages.id, PRIMARY KEY |
| `vector` | BLOB | serialized embedding |
| `provider` | TEXT | e.g., 'openai' |
| `model` | TEXT | e.g., 'text-embedding-3-small' |
| `dim` | INTEGER | CHECK(dim > 0) |
| `created_at` | INTEGER | |

One embedding per page (latest version). Added in [`V04__embeddings.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V04__embeddings.sql).

---

## Critical Indexes in the ai‑memory Database Schema

| Index Name | Table(s) | Purpose |
|------------|----------|---------|
| `idx_pages_latest_path` | pages | UNIQUE enforce one latest version per identity tuple |
| `idx_pages_updated` | pages | recent page queries |
| `idx_pages_supersedes` | pages | version chain navigation |
| `pages_fts` | pages (virtual) | FTS5 full‑text search on title + body |
| `idx_sessions_recent` | sessions | recent session lookup per workspace/project |
| `idx_observations_session` | observations | session‑scoped observation retrieval |
| `idx_links_to` | links | inbound link discovery |
| `idx_audit_recent` | audit_log | recent event retrieval |
| `idx_pages_project` | pages | cascade delete performance |
| `idx_sessions_project` | sessions | cascade delete performance |
| `idx_observations_project` | observations | cascade delete performance |
| `idx_handoffs_project` | handoffs | cascade delete performance |
| `idx_embeddings_provider_model` | page_embeddings | provider/model filtering |

The `pages_fts` virtual table enables ranked full‑text queries:

```sql
SELECT p.id, p.title, p.path, rank
FROM pages p
JOIN pages_fts f ON p.rowid = f.rowid
WHERE pages_fts MATCH 'vector database'
  AND p.is_latest = 1;

```

---

## Working with the Schema: SQL Examples

### Insert Workspace, Project, and Page

```sql
-- Create workspace
INSERT INTO workspaces (id, name, created_at)
VALUES (randomblob(16), 'platform-team', strftime('%s','now')*1000000);

-- Create project
INSERT INTO projects (id, workspace_id, name, repo_path, created_at)
VALUES (
    randomblob(16),
    (SELECT id FROM workspaces WHERE name='platform-team'),
    'api-gateway',
    '/repos/api-gateway',
    strftime('%s','now')*1000000
);

-- Create latest page version
INSERT INTO pages (
    id, workspace_id, project_id, path, title, tier, body,
    body_sha256, frontmatter_json, is_latest, created_at, updated_at
) VALUES (
    randomblob(16),
    (SELECT workspace_id FROM projects WHERE name='api-gateway'),
    (SELECT id FROM projects WHERE name='api-gateway'),
    'design/rate-limiting.md',
    'Rate Limiting Design',
    'working',
    '# Rate Limiting\n\nTBD',

    X'3a2f...',  -- actual sha256
    '{"author": "alice"}',
    1,
    strftime('%s','now')*1000000,
    strftime('%s','now')*1000000
);

```

### Record Session Observation

```sql
INSERT INTO observations (
    id, session_id, workspace_id, project_id,
    kind, title, body, importance, created_at
) VALUES (
    randomblob(16),
    (SELECT id FROM sessions WHERE cwd='/repos/api-gateway' ORDER BY started_at DESC LIMIT 1),
    (SELECT workspace_id FROM sessions WHERE id = ?),
    (SELECT project_id FROM sessions WHERE id = ?),
    'tool_call',
    'Redis connection established',
    'Successfully connected to redis-cluster:6379',
    6,
    strftime('%s','now')*1000000
);

```

### Query Open Handoffs

```sql
SELECT h.id, h.from_agent, h.to_agent, h.summary, h.created_at
FROM handoffs h
WHERE h.workspace_id = :ws_id
  AND h.project_id = :proj_id
  AND h.state = 'open'
ORDER BY h.created_at DESC;

```

---

## Migration Files Defining the ai‑memory Database Schema

| File | Description |
|------|-------------|
| [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql) | Core tables: workspaces, projects, pages, sessions, observations, links, audit_log, and FTS5 setup |
| [`V02__handoffs.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V02__handoffs.sql) | Handoff table and state management |
| [`V03__decay.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V03__decay.sql) | Decay tracking: `last_accessed_at`, `access_count`, `superseded_at` on pages |
| [`V04__embeddings.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V04__embeddings.sql) | `page_embeddings` table for vector search |
| [`V05__cascade_indexes.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V05__cascade_indexes.sql) | Performance indexes for cascade deletes |
| [`V06__wiki_migrations.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V06__wiki_migrations.sql) through [`V58__close_retired_entity_windows.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V58__close_retired_entity_windows.sql) | Extensions: auto‑improve, API credentials, query cache, decay tombstones, content windows |

All migrations live in `crates/ai-memory-store/migrations/` and execute sequentially via `rusqlite`'s embedded migration runner.

---

## Summary

- **ai‑memory** persists data in SQLite with nine core tables organized by `workspace_id`, `project_id`, `path` identity.
- **pages** implements versioning via `is_latest` and `supersedes` with tier‑based lifecycle management.
- **Full‑text search** uses FTS5 (`pages_fts`); **semantic search** uses `page_embeddings` with provider/model tracking.
- **Decay tracking** (V03+) enables automated memory retention policies via `last_accessed_at`, `access_count`, and `superseded_at`.
- **Handoffs** provide structured agent context transfer with JSON arrays for open questions, next steps, and touched files.
- Schema evolves through **58 numbered migrations** in `crates/ai-memory-store/migrations/`, ensuring reproducible upgrades.

---

## Frequently Asked Questions

### What database does ai‑memory use?

ai‑memory uses **SQLite** as its sole database engine, embedding `rusqlite` for Rust‑native access. The database file is created per workspace or managed as a single consolidated store depending on deployment configuration.

### How does page versioning work in ai‑memory?

Each page insertion creates a new row with a unique `id`. The `is_latest` flag marks the current version, while `supersedes` references the previous version's `id`. A unique index on `(workspace_id, project_id, path, is_latest)` where `is_latest = 1` ensures only one latest version exists. Old versions are retained for history until decay sweeps remove them based on `tier` and access patterns.

### What is the purpose of the tier column in pages?

The **tier** column implements a content lifecycle policy: `stable` (long‑term reference), `working` (active development), `scratch` (disposable notes), and `archived` (deprecated but retained). Decay algorithms use tier to compute retention priority—`stable` pages decay slowest, `scratch` fastest.

### How do I query the ai‑memory database directly?

Connect to the SQLite file (location depends on your ai‑memory configuration) and query using standard SQLite. Use the `pages_fts` virtual table for full‑text search and join `page_embeddings` for semantic similarity. Enable foreign key enforcement with `PRAGMA foreign_keys = ON;` to match application behavior.