Key Database Tables Used by ai-memory: A Complete Schema Guide

All persistent state in ai-memory is stored in a single SQLite database defined by migration scripts in crates/ai-memory-store/migrations/, featuring hierarchical tables (workspaces, projects, pages), session tracking (sessions, observations), graph relationships (links, entities), and AI-specific tables (embeddings, feedback, auto-improve).

The ai-memory project is a Rust-based knowledge management system that organizes AI interactions and documentation. Understanding the key database tables used by ai-memory is essential for extending the platform or integrating with its stored knowledge, as every workspace, page, and observation persists through a strictly additive migration scheme.

Core Hierarchical Tables: Workspaces, Projects, and Pages

The foundation of the data model rests on three normalized tables that create a containment hierarchy.

Workspaces and Projects

The workspaces table, defined in [V01__init.sql at line 6](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V01__init.sql#L6), serves as the top-level container. Each row stores a UUID primary key and a display name.

Projects belong to a workspace via a foreign key relationship. Defined at [line 12 in V01__init.sql](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V01__init.sql#L12), the table tracks a project UUID, its human-readable name, and the parent workspace ID.

Pages

The pages table ([V01__init.sql line 23](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V01__init.sql#L23)) represents individual markdown documents in the wiki. Beyond the (workspace_id, project_id, path) composite key, it stores the author, creation timestamp, and modification metadata. The domain struct mirroring this table lives in [crates/ai-memory-core/src/page.rs](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-core/src/page.rs).

Session and Observation Tracking

Sessions

The sessions table ([V01__init.sql line 79](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V01__init.sql#L79)) models logical interaction windows. It tracks start and end timestamps, the owning actor identifier, and the active project context. This table enables replay and audit of user or harness interactions.

Observations

Individual data captures—such as hook payloads or LLM API calls—land in the observations table ([V01__init.sql line 92](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V01__init.sql#L92)). Each row links to a session_id and optionally a page_id, storing a sanitized payload alongside a Unix epoch creation time. The corresponding domain struct is implemented in [crates/ai-memory-core/src/observation.rs](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-core/src/observation.rs).

Graph and Relationship Tables

Links

The many-to-many links table ([V01__init.sql line 106](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V01__init.sql#L106)) enables hyperlinks and backlinks between pages. By storing source and target page references, it forms the edges of the knowledge graph that the retrieval engine traverses.

Entities and Entity-Page Links

Later migrations introduced abstraction for embeddable objects. The entities table ([V38__entities.sql line 16](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V38__entities.sql#L16)) stores a canonical ID and type for abstract objects that may represent pages, observations, or other concepts.

The entity_page_links table ([V38__entities.sql line 27](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V38__entities.sql#L27)) connects these entities to the pages that reference them, supporting the vector-search index construction.

Vector Search and AI-Specific Tables

Page Embeddings

Semantic search relies on page_embeddings ([V04__embeddings.sql line 11](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V04__embeddings.sql#L11)). This table stores pre-computed vector embeddings as BLOB data, keyed by workspace, project, and page path, with a timestamp for versioning.

Page Feedback and Access Control

User relevance signals feed into page_feedback ([V37__page_feedback.sql line 20](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V37__page_feedback.sql#L20)), which records ratings or corrections to guide the auto-improvement loop.

Fine-grained permissions rely on page_access ([V43__page_access_by_actor.sql line 19](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V43__page_access_by_actor.sql#L19)), tracking which actors have read specific pages.

Workflow and Collaboration Tables

Handoffs

The handoffs table ([V02__handoffs.sql line 6](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V02__handoffs.sql#L6)) models collaborative work requests. It captures the creator actor, target receiver, associated page or project, and status flags (pending, claimed, completed).

Workstreams

Long-running orchestrated tasks use workstreams and its satellite tables: workstream_native_sessions, managed_runs, and workstream_events. Defined in [V31__managed_workstreams.sql (line 4)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V31__managed_workstreams.sql#L4), these tables persist state for the ai-memory run command, tracking native sessions, managed execution runs, and event logs.

Auto-Improvement Pipeline Tables

The self-improving capabilities of ai-memory rely on a family of tables prefixed with auto_improve_, first introduced in [V21__auto_improve_pending_proposals.sql (line 3)](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V21__auto_improve_pending_proposals.sql#L3):

• auto_improve_proposals: Stores pending changes awaiting review
• auto_improve_runs: Tracks execution attempts of proposed improvements
• auto_improve_scheduler_state: Persists the cron-like scheduler configuration

These tables enable the system to queue, execute, and learn from automated content suggestions.

Audit and Security Tables

Audit Log

Security-sensitive actions write immutably to audit_log ([V01__init.sql line 116](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/migrations/V01__init.sql#L116)). This append-only table records administrative events—such as page deletions or project renames—providing a forensic trail for compliance and security review.

Querying the ai-memory Database

The ai-memory-store crate uses rusqlite for database access. Below are idiomatic Rust patterns for interacting with the key tables.

Fetching Pages by Project

// Fetch all pages belonging to a specific project
let mut stmt = db.prepare(
    "SELECT path, author_id, created_at FROM pages \
     WHERE workspace_id = ?1 AND project_id = ?2"
)?;
let rows = stmt.query_map([workspace_id, project_id], |row| {
    Ok(PageInfo {
        path: row.get::<_, String>(0)?,
        author: row.get::<_, String>(1)?,
        created: row.get::<_, i64>(2)?,
    })
})?;
for page in rows {
    println!("{:?}", page?);
}

Recording Observations

// Record a new observation (simplified)
let payload = sanitize(payload_raw); // see crates/ai-memory-core/src/sanitize.rs
db.execute(
    "INSERT INTO observations (session_id, page_id, payload, created_at) \
     VALUES (?1, ?2, ?3, unixepoch())",
    [&session_id, &page_id, &payload],
)?;

Retrieving Vector Embeddings

// Retrieve the most recent embeddings for a page (used by the search engine)
let mut stmt = db.prepare(
    "SELECT vector FROM page_embeddings \
     WHERE workspace_id = ?1 AND project_id = ?2 AND page_path = ?3 \
     ORDER BY created_at DESC LIMIT 1"
)?;
let embedding: Vec<u8> = stmt.query_row([workspace_id, project_id, page_path], |r| r.get(0))?;

Summary

• Hierarchical organization: Workspaces contain projects, which contain pages, modeled in V01__init.sql
• Event sourcing: Sessions and observations capture every interaction for audit and replay
• Graph structure: Links and entity-page relationships enable knowledge graph traversal
• AI infrastructure: Embeddings, feedback, and auto-improve tables support semantic search and continual learning
• Immutability: The schema follows an additive-only migration policy, ensuring backward compatibility across releases

Frequently Asked Questions

What database engine does ai-memory use?

Ai-memory uses SQLite as its sole persistence layer. All tables reside in a single database file, accessed through the rusqlite crate within the ai-memory-store actor.

How are page embeddings stored and retrieved?

Embeddings are stored in the page_embeddings table as binary BLOB data, versioned by timestamp. The retrieval query selects the most recent vector for a given page path, workspace, and project, as shown in the code example above.

What is the purpose of the observations table?

The observations table acts as the system's event log, capturing discrete data points like hook payloads or LLM responses. Each observation links to a session and optionally a page, providing the raw material for the auto-improvement pipeline and audit trails.

Where is the database schema defined in the source code?

Schema definitions live in crates/ai-memory-store/migrations/ as numbered SQL files (e.g., V01__init.sql, V38__entities.sql). The ai-memory-store crate applies these migrations sequentially on startup, and the schema is deliberately immutable to prevent data loss across upgrades.

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