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

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 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

---

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. 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.

---

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:

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

-- 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

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

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	Core tables: workspaces, projects, pages, sessions, observations, links, audit_log, and FTS5 setup
V02__handoffs.sql	Handoff table and state management
V03__decay.sql	Decay tracking: last_accessed_at, access_count, superseded_at on pages
V04__embeddings.sql	page_embeddings table for vector search
V05__cascade_indexes.sql	Performance indexes for cascade deletes
V06__wiki_migrations.sql through 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.

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