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

> Explore the key database tables in ai-memory with this complete schema guide. Understand how workspaces, sessions, links, and AI tables store your persistent state.

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

---

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

```rust
// 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

```rust
// 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

```rust
// 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`](https://github.com/akitaonrails/ai-memory/blob/main/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`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql), [`V38__entities.sql`](https://github.com/akitaonrails/ai-memory/blob/main/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.