What Are the Key Tables in the ai-memory SQLite Schema? A Complete Architectural Guide

The ai-memory SQLite schema contains 16+ tables organized into core foundational tables (workspaces, projects, pages, sessions, observations, links, audit_log) and auxiliary tables added across migrations for multi-user support, vector embeddings, decay algorithms, and auto-improve pipelines.

The ai-memory project by Akira Matsuda (akitaonrails/ai-memory) implements a persistent knowledge base for AI agents using a single SQLite database. The schema evolves through incremental Flyway-style migrations, starting with V01__init.sql and extending through specialized migrations that add capabilities like hand-off tracking, user management, and semantic search. Understanding this SQLite schema is essential for anyone building on ai-memory or debugging agent session data.

Core Tables: The Foundation in V01__init.sql

The initial migration at crates/ai-memory-store/migrations/V01__init.sql establishes the seven foundational tables that power all ai-memory operations.

workspaces: Logical Containers for Multi-Tenant Isolation

The workspaces table represents the top-level isolation boundary in ai-memory. Each workspace receives a UUID primary key, enforces unique names, and tracks creation time.

-- From V01__init.sql lines 6-10
CREATE TABLE workspaces (
    id TEXT PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    created_at INTEGER NOT NULL
);

This design enables clean separation between different agent environments, organizations, or operational contexts without requiring separate database instances.

projects: Per-Workspace Project Metadata

Nested within workspaces, the projects table stores repository-associated configurations. The schema enforces uniqueness at the composite level: no duplicate project names within the same workspace.

-- From V01__init.sql lines 12-18
CREATE TABLE projects (
    id TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL REFERENCES workspaces(id),
    name TEXT NOT NULL,
    repo_path TEXT,
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    UNIQUE(workspace_id, name)
);

The optional repo_path column connects ai-memory data to specific code repositories, enabling context-aware agent behavior.

pages: The Central Knowledge Store

The pages table is arguably the most critical table in ai-memory. It implements a versioned wiki-style content system with vector search capabilities.

-- From V01__init.sql lines 23-42
CREATE TABLE pages (
    id TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL REFERENCES workspaces(id),
    project_id TEXT REFERENCES projects(id),
    path TEXT NOT NULL,
    title TEXT,
    body TEXT,
    front_matter TEXT,  -- JSON-encoded metadata
    is_latest BOOLEAN DEFAULT 0,
    supersedes TEXT REFERENCES pages(id),
    embedding_model TEXT,  -- Tracks which model generated embeddings
    embedding_vector BLOB, -- Raw vector bytes for similarity search
    created_at INTEGER NOT NULL,
    updated_at INTEGER NOT NULL,
    UNIQUE(workspace_id, project_id, path, is_latest),
    CHECK (is_latest IN (0, 1))
);

Key architectural decisions in this table:

• Composite unique constraint on (workspace_id, project_id, path, is_latest) enforces single "latest" version per path
• Self-referential versioning via supersedes enables full audit history
• Embedded vector storage for semantic retrieval without external vector databases

The migration also creates pages_fts, a full-text search virtual table, with triggers maintaining index synchronization automatically.

sessions: Agent Session Lifecycle Tracking

Every interaction with an AI agent creates a session record capturing operational context:

• Agent kind (Claude-Code, Codex, etc.)
• Working directory (cwd)
• Start and end timestamps
• Reference to generated summary page

-- From V01__init.sql lines 79-88
CREATE TABLE sessions (
    id TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL REFERENCES workspaces(id),
    project_id TEXT REFERENCES projects(id),
    agent_kind TEXT,
    cwd TEXT,
    started_at INTEGER NOT NULL,
    ended_at INTEGER,
    summary_page_id TEXT REFERENCES pages(id)
);

This table enables complete reconstruction of agent operational contexts for debugging or hand-off scenarios.

observations: Immutable Event Logging

The observations table implements an append-only event log central to ai-memory's memory architecture. Each record captures:

-- From V01__init.sql lines 92-102
CREATE TABLE observations (
    id TEXT PRIMARY KEY,
    session_id TEXT NOT NULL REFERENCES sessions(id),
    kind TEXT,           -- Classification (thought, event, tool_output, etc.)
    title TEXT,
    body TEXT,
    importance REAL,     -- Numeric priority for decay calculations
    created_at INTEGER NOT NULL
);

The importance field works with the decay system (see decay table below) to automatically depreciate older, less relevant observations.

links: Knowledge Graph Edges

ai-memory builds a navigable knowledge graph through explicit page relationships:

-- From V01__init.sql lines 106-112
CREATE TABLE links (
    id TEXT PRIMARY KEY,
    from_page_id TEXT NOT NULL REFERENCES pages(id),
    to_page_id TEXT REFERENCES pages(id),  -- Null if unresolved target
    to_path TEXT,                          -- Raw path when target unknown
    link_type TEXT DEFAULT 'references'
);

This design supports both resolved and dangling links—critical for incremental knowledge base construction where pages may be created in any order.

audit_log: Privileged Operation Tracking

Security-sensitive operations receive immutable audit records:

-- From V01__init.sql lines 116-124
CREATE TABLE audit_log (
    id TEXT PRIMARY KEY,
    at INTEGER NOT NULL,
    op TEXT NOT NULL,           -- Operation classification
    by_session_id TEXT REFERENCES sessions(id),
    on_page_id TEXT REFERENCES pages(id),
    related_id TEXT,            -- Flexible foreign key for various entities
    detail TEXT                 -- JSON-encoded operation specifics
);

The JSON detail column provides extensibility without schema migrations for new operation types.

Extended Tables: Capability Migrations

Subsequent migrations add specialized tables without modifying core structures—demonstrating careful forward-only migration discipline.

users: Multi-User Attribution (V14__users.sql)

Added in V14__users.sql, this table enables authenticated multi-tenancy:

-- From V14__users.sql lines 31-40
CREATE TABLE users (
    id TEXT PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    display_name TEXT,
    email TEXT,
    token_hash TEXT,        -- Salted SHA-256 for API authentication
    seen_at INTEGER,        -- Last activity timestamp
    created_at INTEGER NOT NULL,
    expires_at INTEGER      -- Optional token expiration
);

handoffs: Inter-Agent Session Transfer (V02__handoffs.sql)

The handoffs table in V02__handoffs.sql implements stateful agent collaboration:

• Source and destination session references
• Status tracking (pending, accepted, rejected)
• Timestamped lifecycle

embeddings: Vector Search Optimization (V04__embeddings.sql)

While pages stores embeddings inline, V04__embeddings.sql adds an externalized embedding cache for alternative vector models or fine-grained similarity indexing.

decay: Time-Based Importance Degradation (V03__decay.sql)

The V03__decay.sql migration implements ai-memory's forgetting mechanism—essential for managing context window limits. This table stores decay parameters applied to observation importance scores.

Auto-Improve Pipeline Tables

Three migrations support the self-improving agent architecture:

Migration	Table	Purpose
V31__managed_workstreams.sql	managed_workstreams	Structured workstream objects for improvement loops
V36__page_expiry.sql	page_expiry	TTL-based content lifecycle management
V37__page_feedback.sql	page_feedback	Explicit feedback signals (thumbs up/down) for ranking
V38__entities.sql	entities	Generic metadata storage for proposals and patches

Practical Query Patterns

Listing Recent Sessions with Context

-- Sessions from the last 7 days with project and workspace names
SELECT 
    s.id,
    s.agent_kind,
    w.name AS workspace,
    p.name AS project,
    datetime(s.started_at/1000000, 'unixepoch') AS started
FROM sessions s
JOIN workspaces w ON s.workspace_id = w.id
LEFT JOIN projects p ON s.project_id = p.id
WHERE s.started_at > (strftime('%s','now') - 604800) * 1000000
ORDER BY s.started_at DESC;

Finding Semantically Related Pages

-- Pages with embedding similarity to a reference (requires vector math)
SELECT 
    p.path,
    p.title,
    p.embedding_model
FROM pages p
WHERE p.workspace_id = ?
  AND p.is_latest = 1
  AND p.embedding_vector IS NOT NULL
ORDER BY vec_distance_cosine(p.embedding_vector, ?)  -- Hypothetical function
LIMIT 10;

Note: Actual vector similarity requires SQLite extensions or application-level computation. ai-memory may implement this in Rust code using the stored BLOB vectors.

Tracing Observation Decay Impact

-- High-importance observations with current effective importance
SELECT 
    o.id,
    o.title,
    o.importance AS original_importance,
    d.current_importance,
    datetime(o.created_at/1000000, 'unixepoch') AS created
FROM observations o
LEFT JOIN decay d ON o.id = d.observation_id
WHERE o.session_id = ?
  AND (d.current_importance IS NULL OR d.current_importance > 0.5)
ORDER BY o.importance DESC;

Schema Design Patterns in ai-memory

Several architectural patterns emerge from analyzing these migrations:

1. UUID primary keys throughout—enables distributed generation without coordination
2. Microsecond-precision timestamps (INTEGER storing microseconds since epoch) for deterministic ordering
3. Soft relationships via nullable foreign keys and TEXT path references—prioritizes flexibility over strict referential integrity
4. JSON columns (front_matter, detail) for extensible metadata without migration churn
5. Boolean flags as INTEGER with CHECK constraints—SQLite-compatible strict typing

Summary

• Seven core tables in V01__init.sql provide workspaces, projects, versioned pages, sessions, observations, knowledge graph links, and audit logging
• Nine+ extension tables add multi-user auth, hand-offs, decay algorithms, vector embeddings, and auto-improve pipeline support
• Pages table serves as the central knowledge repository with built-in versioning and vector search preparation
• Observations + decay implement adaptive memory with automatic importance degradation
• All migrations are forward-only in crates/ai-memory-store/migrations/, following Flyway naming conventions
• Microsecond timestamps and UUIDs enable distributed, time-ordered operations without central coordination

Frequently Asked Questions

How does ai-memory handle page versioning?

The pages table uses is_latest boolean flags and supersedes self-references to maintain version history. The unique constraint (workspace_id, project_id, path, is_latest) ensures only one version per path is marked latest. When updating, ai-memory creates a new row, marks it is_latest=1, and points supersedes at the previous version which gets is_latest=0.

Where are user credentials stored in the ai-memory SQLite schema?

Credentials reside in the users table added by V14__users.sql. Passwords are stored as salted SHA-256 hashes in the token_hash column, not plaintext. The table also tracks created_at, seen_at (last activity), and expires_at for token lifecycle management.

Can ai-memory embed vector similarity search without external databases?

Yes—the pages table stores raw embedding vectors in embedding_vector BLOB columns, and V04__embeddings.sql adds dedicated embedding management. However, actual similarity computation requires either: (a) loading vectors into application memory for comparison, (b) using SQLite vector extensions, or (c) hybrid approaches where top-K candidates are filtered by metadata before exact vector comparison.

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