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

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. 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. External data ingestion is governed by ingest_keys (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, 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 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 adds:

• auto_improve_scheduler_state: Daemon state persistence
• auto_improve_scheduler_claims: Ownership tracking for distributed scheduler instances

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 with the entities table and a many-to-many entity_page_links join table. Inter-agent communication is facilitated by handoffs (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) to store vector representations of pages when an embedding provider is configured. Content versioning is tracked in wiki_migrations (V06__wiki_migrations.sql), while user feedback resides in page_feedback (V37__page_feedback.sql).

Operational Monitoring

System health and access patterns are tracked through:

• client_activity (V46__client_activity.sql): Aggregates per-client metrics for rate-limiting
• page_access (V43__page_access_by_actor.sql): Records which actors accessed specific pages and when
• session_consolidation_jobs (V34__session_consolidation_jobs.sql): Queues background jobs for aggregating session observations
• maintenance_scheduler_state (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:

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:

# 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.
• Workstream orchestration requires four tables introduced in 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.
• 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 and user authentication tables are added in V14__users.sql.

Which table stores vector embeddings for semantic search?

The page_embeddings table, created in 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.

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