# What Tables Are Included in the ai-memory SQLite Schema? Complete Reference Guide

> Explore the ai-memory SQLite schema, detailing over 25 tables across core entities, pipelines, auto-improvement, and vector search. Find your complete reference guide here.

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

---

**The ai-memory SQLite schema comprises over 25 permanent tables organized into core entities, workstream pipelines, auto-improvement systems, and vector search capabilities, all defined through incremental migration scripts in `crates/ai-memory-store/migrations/`.**

The akitaonrails/ai-memory project implements a durable knowledge graph for AI agents using a single SQLite database as its persistent store. Understanding the tables included in the ai-memory SQLite schema is essential for developers building integrations, debugging data flows, or extending the system. This guide maps every table to its source migration file and functional domain.

## Core Entity Tables (V01__init.sql)

The foundation of the ai-memory SQLite schema is established in [`crates/ai-memory-store/migrations/V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V01__init.sql). This initial migration creates seven core tables that manage workspaces, content, and observability.

**`workspaces`** and **`projects`** form the organizational hierarchy. The `workspaces` table holds unique identifiers and names, while `projects` maps each project to a workspace with associated metadata.

**`pages`** represents wiki-style content with fields for path, title, author, and timestamps. This table stores the primary knowledge artifacts that agents create and modify.

**`sessions`** and **`observations`** capture runtime behavior. The `sessions` table tracks user interaction boundaries including owner, start/end times, and agent kind, while `observations` stores the individual data points generated during each session.

**`links`** enables graph traversal by connecting entities—pages, observations, and other objects—into a traversable network. The **`audit_log`** table records privileged actions such as administrative operations and deletions for compliance and security review.

## Extended Schema for Advanced Features

Beyond the core seven tables, the schema expands through subsequent migrations to support vector search, user management, background processing, and automated improvement pipelines.

### User Management and Security

Authentication and access control rely on the **`users`** table added in [`V14__users.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V14__users.sql). External data ingestion is governed by **`ingest_keys`** ([`V33__ingest_keys.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V33__ingest_keys.sql)), which stores API keys for importing data from external sources.

### Workstream Orchestration

The workstream pipeline system, defined in [`V31__managed_workstreams.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V31__managed_workstreams.sql), introduces four interconnected tables:

- **`workstreams`**: Defines top-level managed pipelines
- **`workstream_native_sessions`**: Links native sessions to specific workstreams
- **`managed_runs`**: Tracks individual executions of a workstream
- **`workstream_events`**: Logs lifecycle events including starts, finishes, and errors

### Auto-Improvement Pipeline

The self-improving AI capabilities rely on a complex state machine managed across three migration files:

**[`V21__auto_improve_pending_proposals.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V21__auto_improve_pending_proposals.sql)** creates:
- `auto_improve_runs`: High-level pipeline executions
- `auto_improve_proposals`: Individual improvement suggestions
- `auto_improve_proposal_events`: State transitions (accept, reject, apply)

**[`V22__auto_improve_scheduler.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V22__auto_improve_scheduler.sql)** adds:
- `auto_improve_scheduler_state`: Daemon state persistence
- `auto_improve_scheduler_claims`: Ownership tracking for distributed scheduler instances

**[`V24__auto_improve_rejections.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V24__auto_improve_rejections.sql)** introduces `auto_improve_rejections` to store rejected proposals for future analysis.

### Knowledge Graph Enhancements

Generic entity support arrives in [`V38__entities.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V38__entities.sql) with the **`entities`** table and a many-to-many **`entity_page_links`** join table. Inter-agent communication is facilitated by **`handoffs`** ([`V02__handoffs.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V02__handoffs.sql)), which represent work transfer objects between different AI agents.

### Vector Search and Content Tracking

Semantic search capabilities require **`page_embeddings`** ([`V04__embeddings.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V04__embeddings.sql)) to store vector representations of pages when an embedding provider is configured. Content versioning is tracked in **`wiki_migrations`** ([`V06__wiki_migrations.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V06__wiki_migrations.sql)), while user feedback resides in **`page_feedback`** ([`V37__page_feedback.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V37__page_feedback.sql)).

### Operational Monitoring

System health and access patterns are tracked through:
- **`client_activity`** ([`V46__client_activity.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V46__client_activity.sql)): Aggregates per-client metrics for rate-limiting
- **`page_access`** ([`V43__page_access_by_actor.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V43__page_access_by_actor.sql)): Records which actors accessed specific pages and when
- **`session_consolidation_jobs`** ([`V34__session_consolidation_jobs.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V34__session_consolidation_jobs.sql)): Queues background jobs for aggregating session observations
- **`maintenance_scheduler_state`** ([`V29__maintenance_scheduler.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V29__maintenance_scheduler.sql)): Persists periodic maintenance task state

## Querying the Schema Programmatically

To inspect the **ai-memory SQLite schema** at runtime, use the following Rust code leveraging `rusqlite`:

```rust
use rusqlite::{Connection, Result};

fn list_tables(conn: &Connection) -> Result<Vec<String>> {
    let mut stmt = conn.prepare(
        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"
    )?;
    let rows = stmt.query_map([], |row| row.get(0))?;
    rows.collect()
}

fn main() -> Result<()> {
    let conn = Connection::open("/path/to/ai-memory.db")?;
    let tables = list_tables(&conn)?;
    
    println!("Tables in ai-memory schema:");
    for t in tables {
        println!(" - {}", t);
    }
    Ok(())
}

```

For command-line inspection:

```bash

# List all tables

sqlite3 ai-memory.db ".tables"

# View complete CREATE statements

sqlite3 ai-memory.db ".schema"

# Query specific table structure

sqlite3 ai-memory.db "PRAGMA table_info(pages);"

```

Note that some migrations create temporary scaffolding tables (such as `sessions_new`) during schema transformations for agent-kind upgrades, but these do not persist as permanent parts of the **ai-memory SQLite schema**.

## Summary

- The **ai-memory** database contains **25+ permanent tables** spanning core entities, workstreams, and AI improvement systems.
- **Seven core tables** (`workspaces`, `projects`, `pages`, `sessions`, `observations`, `links`, `audit_log`) are defined in [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql).
- **Workstream orchestration** requires four tables introduced in [`V31__managed_workstreams.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V31__managed_workstreams.sql).
- **Auto-improvement pipelines** utilize eight tables across migrations V21, V22, and V24.
- **Vector search** depends on `page_embeddings` added in [`V04__embeddings.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V04__embeddings.sql).
- All schema changes are managed through numbered SQL files in `crates/ai-memory-store/migrations/`.

## Frequently Asked Questions

### How many tables are included in the ai-memory SQLite schema?

The production schema includes 25 permanent tables, with additional temporary tables appearing during migrations. The core implementation uses seven foundational tables for workspaces, projects, and observations, while extended features like workstreams, auto-improvement, and vector search account for the remaining 18+ tables.

### Where are the schema migrations stored in the ai-memory repository?

All schema definitions reside in `crates/ai-memory-store/migrations/` as numbered SQL files following the `V{number}__description.sql` convention. For example, the initial schema is in [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql) and user authentication tables are added in [`V14__users.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V14__users.sql).

### Which table stores vector embeddings for semantic search?

The **`page_embeddings`** table, created in [`V04__embeddings.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V04__embeddings.sql), stores vector representations of pages. This table is populated only when the system is configured with an embedding provider, enabling semantic similarity search across the knowledge base.

### How does ai-memory handle schema versioning?

The project uses a sequential migration system where each SQL file in `crates/ai-memory-store/migrations/` increments the schema version. The system tracks applied migrations internally, applying new files in numeric order during startup. This incremental approach allows the **ai-memory SQLite schema** to evolve without breaking existing installations.