Understanding the ai-memory SQLite Database Structure: Schema, Tables, and Relationships

The ai-memory SQLite database uses a migration-driven schema centered on a hierarchical workspace-project-entity model with versioned wiki pages, full-text search, and audit logging.

The akitaonrails/ai-memory project persists all data in a single SQLite file managed through incremental SQL migrations. The ai-memory SQLite database structure follows a strict normalization pattern where every core table references both workspaces and projects, establishing a three-tuple identity that scopes all content. This design supports versioned documentation, AI session tracking, and cross-session handoffs while maintaining referential integrity through cascading foreign keys.

Core Schema Architecture

The database schema is defined incrementally via migration scripts stored in crates/ai-memory-store/migrations/. Each file adds tables, indexes, or triggers to evolve the structure without breaking existing data.

Key migration files include:

• V01__init.sql — Core tables: workspaces, projects, pages, sessions, observations, links, and audit_log
• V02__handoffs.sql — Session handoff snapshots and workflow state
• V03__decay.sql — Relevance scoring via the decay table
• V36__page_expiry.sql — Optional page expiration timestamps
• V37__page_feedback.sql — User rating and comment storage
• V38__entities.sql — Generic entity bucket for auto-improvement pipelines

According to the source code in crates/ai-memory-store/src/schema.rs, Rust structs mirror these SQL definitions, providing type-safe access to the SQLite backend.

Main Tables and Relationships

The schema revolves around four functional domains: project organization, documentation, session management, and operational metadata.

Workspaces and Projects

The hierarchy begins with workspaces, scoped by id (BLOB), name, and created_at. Every subsequent table references workspace_id with cascade deletion.

Projects nest inside workspaces with a unique constraint on (workspace_id, name) to enforce single naming per workspace. The repo_path column links the database record to a local filesystem repository. Foreign keys from projects to workspaces cascade on delete, ensuring workspace removal cleans all associated data.

Pages and Full-Text Search

The pages table implements a versioning system for wiki content. Each row stores path, title, body, body_sha256, and frontmatter_json, alongside versioning flags.

Critical columns:

• is_latest — Boolean flag marking the current version
• supersedes — Self-referencing foreign key (set-null) linking to the previous version
• pinned — Boolean indicating sticky status
• Embedding fields (embedding_provider, embedding_model, embedding_dim) — Optional vector metadata

A partial unique index enforces only one latest version per (workspace_id, project_id, path) combination.

Full-text search is implemented via the pages_fts virtual table using FTS5. Triggers (pages_fts_ai, pages_fts_ad, pages_fts_au) automatically synchronize inserts, deletes, and updates between pages and pages_fts, indexing title and body columns.

Sessions and Observations

Sessions represent AI-agent execution contexts with columns for agent_kind, cwd (current working directory), and summary_page_id (linking to generated documentation). Both workspace_id and project_id foreign keys cascade on deletion.

Observations capture discrete events during a session. The table links to sessions(id) with cascade deletion and stores kind, title, body, and an importance score. This creates an immutable log of agent activity tied to specific workspace and project scopes.

Links and Navigation

The links table uses a composite primary key (from_page_id, to_path, link_type) to store explicit markdown relationships. Foreign keys reference pages(id) with cascade deletion on the source and set-null on the target, allowing link preservation even if the destination page is renamed or deleted.

Handoffs and Workflow State

Added in V02__handoffs.sql, the handoffs table enables session resumption across different agents. It stores:

• from_session_id and from_agent (source context)
• to_agent and cwd (target context)
• JSON fields for questions, steps, and files
• state — Enum values: open, accepted, expired
• accepted_by_session — Links to the resuming session (set-null)

Handoffs maintain foreign keys to workspaces, projects, and sessions with appropriate cascade and set-null behaviors to preserve history while allowing cleanup.

Auxiliary Tables

Entities (V38__entities.sql) provides a generic bucket for auto-improvement systems, storing kind and metadata JSON with references to pages, projects, and workspaces.

Page_feedback (V37__page_feedback.sql) captures user ratings and comments with foreign keys to pages(id) and users(id).

Page_expiry (V36__page_expiry.sql) manages content lifecycle via an expires_at timestamp tied to pages(id) with cascade deletion.

Decay (V03__decay.sql, modified in V49__decay_tombstone_index.sql) stores relevance scores for ranking, linking entity_id to entities(id) with cascade deletion and tracking decay_factor and updated_at.

Audit_log is the exception to the workspace-project rule. It stores immutable operation records with id (INTEGER AUTOINCREMENT), at timestamp, op type, and optional references to workspace, project, or page IDs without foreign key constraints, guaranteeing log survival even after data deletion.

Querying the Database Structure

Direct SQL access allows inspection of the ai-memory SQLite database structure and content. Below are practical queries demonstrating the schema relationships.

Retrieve the latest version of every page in a specific workspace and project:

SELECT p.id, p.path, p.title, p.body
FROM pages p
WHERE p.workspace_id = ? AND p.project_id = ? AND p.is_latest = 1;

Execute full-text search over page content using the FTS5 virtual table:

SELECT p.id, p.title, snippet(pages_fts) AS snippet
FROM pages_fts
JOIN pages p ON pages_fts.rowid = p.rowid
WHERE pages_fts MATCH 'machine learning';

List all open handoffs awaiting agent acceptance:

SELECT h.id, h.summary, h.created_at, h.from_agent, h.to_agent
FROM handoffs h
WHERE h.workspace_id = ? AND h.project_id = ? AND h.state = 'open';

Extract the decay scores for relevance-ranked entities:

SELECT d.entity_id, d.decay_factor, d.updated_at
FROM decay d
JOIN entities e ON d.entity_id = e.id
WHERE e.workspace_id = ? AND e.project_id = ?
ORDER BY d.decay_factor DESC;

Summary

• Hierarchical scoping: Every table except audit_log references workspaces and projects, enforcing strict data isolation through foreign keys with cascade deletion.
• Versioned documentation: The pages table uses is_latest flags and supersedes relationships to maintain historical versions while exposing only current content through partial unique indexes.
• Integrated search: The pages_fts FTS5 virtual table, synchronized via triggers, provides full-text search capabilities without application-level indexing.
• Session continuity: Handoffs with JSON metadata and state tracking enable complex AI agent workflows across multiple executions.
• Migration-driven evolution: Schema changes are applied through versioned SQL files in crates/ai-memory-store/migrations/, ensuring deterministic database state across deployments.

Frequently Asked Questions

What is the primary key type used in ai-memory tables?

Most tables use id with a BLOB type as their primary key. The exception is the audit_log table, which uses INTEGER AUTOINCREMENT for its id column to ensure strictly sequential, immutable log entries.

How does ai-memory handle page versioning without losing history?

The database implements soft versioning through the is_latest boolean column and the supersedes foreign key. When a page updates, the old row remains with is_latest = 0, while the new row points to it via supersedes and sets is_latest = 1. A partial unique index on (workspace_id, project_id, path) where is_latest = 1 guarantees data integrity while preserving historical versions.

Can I query the ai-memory database directly without using the Rust API?

Yes. Since ai-memory uses standard SQLite, you can open the database file directly with the sqlite3 CLI or any SQLite-compatible tool. The schema uses standard SQL types and foreign key constraints, though you should respect the triggers on pages_fts to avoid desynchronizing the full-text search index. Direct queries against crates/ai-memory-store/migrations/ files reveal the exact table definitions.

What is the purpose of the decay table in the ai-memory schema?

The decay table stores relevance scores for entities used by ranking algorithms. It links to the entities table via entity_id with cascade deletion, tracking a decay_factor float and updated_at timestamp. This supports relevance-based retrieval where older or less-accessed content receives lower priority scores, implemented originally in V03__decay.sql and optimized with tombstone indexes in V49__decay_tombstone_index.sql.

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