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

> Explore the ai-memory SQLite schema architecture. Understand foundational tables like workspaces, projects, and sessions, plus auxiliary tables for advanced features.

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

---

**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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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.

```sql
-- 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**.

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

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

```sql
-- 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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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`](https://github.com/akitaonrails/ai-memory/blob/main/V14__users.sql), this table enables **authenticated multi-tenancy**:

```sql
-- 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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/V31__managed_workstreams.sql) | `managed_workstreams` | Structured workstream objects for improvement loops |
| [`V36__page_expiry.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V36__page_expiry.sql) | `page_expiry` | TTL-based content lifecycle management |
| [`V37__page_feedback.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V37__page_feedback.sql) | `page_feedback` | Explicit feedback signals (thumbs up/down) for ranking |
| [`V38__entities.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V38__entities.sql) | `entities` | Generic metadata storage for proposals and patches |

## Practical Query Patterns

### Listing Recent Sessions with Context

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

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

```sql
-- 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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/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.