# What Information Is Stored in the `pages` Table of the ai‑memory SQLite Database?

> Discover what information the ai-memory SQLite pages table stores including content metadata tier classification vector embeddings and full version history.

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

---

**The `pages` table in ai‑memory stores every version of markdown wiki pages, tracking content, metadata, tier classification, and optional vector embeddings with full version history.**

The `pages` table serves as the primary persistence layer for the ai‑memory wiki system, a Rust‑based knowledge management tool that organizes markdown documents into workspaces and projects. According to the source schema defined 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) (lines 23‑42), this table holds a complete auditable history of page modifications, allowing the system to distinguish between current and superseded versions while supporting advanced features like semantic search and content decay.

## Core Schema and Column Definitions

The `pages` table schema is designed to capture both the semantic content of wiki pages and their operational metadata. The Rust domain type `Page` in [`crates/ai-memory-core/src/page.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-core/src/page.rs) mirrors this structure for type‑safe database operations.

### Identity and Location Fields

Each row is uniquely identified and hierarchically organized:

- **`id`** (BLOB, primary key): A stable binary identifier for the specific page version.
- **`workspace_id`** (BLOB): Foreign key referencing `workspaces.id`, isolating pages by organizational workspace.
- **`project_id`** (BLOB): Foreign key referencing `projects.id`, grouping pages within a specific project.
- **`path`** (TEXT): The POSIX‑style relative path of the markdown file within the wiki root, serving as the logical page identifier.

### Content and Metadata

These columns store the actual page content and derived metadata:

- **`title`** (TEXT): Human‑readable title extracted from YAML front‑matter or the first H1 heading.
- **`tier`** (TEXT): Classification tier (`working`, `episodic`, `semantic`, or `procedural`) used for lifecycle management.
- **`body`** (TEXT): The complete markdown content excluding YAML front‑matter.
- **`body_sha256`** (BLOB): SHA‑256 hash of the body field for integrity verification and change detection.
- **`frontmatter_json`** (TEXT, default `'{}'`): Parsed front‑matter stored as JSON to enable cheap metadata queries without YAML parsing.
- **`pinned`** (INTEGER, default 0): Boolean flag indicating user‑pinned status; pinned pages are excluded from automated decay sweeps.

### Versioning and Lifecycle

The table implements an append‑only versioning strategy:

- **`is_latest`** (INTEGER, default 1): Flag where `1` indicates the current visible version and `0` marks historical versions.
- **`supersedes`** (BLOB, nullable): Points to the `id` of the previous version, forming a linked list of page history. Null for the initial version.
- **`created_at`** (INTEGER): Microseconds since the Unix epoch when the version was created.
- **`updated_at`** (INTEGER): Microseconds since the Unix epoch of the last modification.

### Embedding Metadata

Optional vector embedding data supports semantic search capabilities:

- **`embedding_provider`** (TEXT, nullable): Name of the embedding service (e.g., OpenAI, Ollama).
- **`embedding_model`** (TEXT, nullable): Specific model identifier used to generate embeddings.
- **`embedding_dim`** (INTEGER, nullable): Dimensionality of the stored vector embedding.

## Constraints and Indexing Strategy

The `pages` table enforces data integrity through specific constraints defined in the migration file. A **unique index** on `(workspace_id, project_id, path)` with a `WHERE is_latest = 1` clause guarantees exactly one current version per logical page path within a project.

Additional indexes optimize critical access patterns:

- **`updated_at`**: Supports queries for recent activity and time‑based decay calculations.
- **`supersedes`**: Enables efficient traversal of version history chains.

## Querying the pages Table

You can retrieve the latest pages for a specific workspace and project using the following Rust code, which corresponds to the `Page` domain model:

```rust
// Query latest pages with metadata
let rows = store
    .reader
    .query("
        SELECT id, path, title, tier, created_at, updated_at
        FROM pages
        WHERE workspace_id = ?1
          AND project_id   = ?2
          AND is_latest = 1
        ORDER BY updated_at DESC
        LIMIT 10
    ", &[workspace_id, project_id])
    .await?;

```

For direct database inspection or debugging, use this SQL query against the SQLite file:

```sql
-- Run with: sqlite3 memory.db
SELECT
    id,
    path,
    title,
    tier,
    created_at,
    updated_at,
    pinned
FROM pages
WHERE workspace_id = X'...'
  AND project_id   = X'...'
  AND is_latest = 1
ORDER BY updated_at DESC;

```

## Summary

- The `pages` table stores every version of markdown content in ai‑memory, with `is_latest` distinguishing current from historical versions.
- Core fields include `path`, `title`, `body`, and `tier`, along with `body_sha256` for integrity and `frontmatter_json` for efficient metadata access.
- Versioning is implemented via the `supersedes` column linking to previous versions, supporting full audit trails.
- Optional embedding columns (`embedding_provider`, `embedding_model`, `embedding_dim`) support vector search integration.
- A unique partial index enforces single latest version per path, while indexes on `updated_at` and `supersedes` optimize common queries.

## Frequently Asked Questions

### How does ai‑memory handle page versioning in the `pages` table?

ai‑memory uses an append‑only versioning model where new edits insert new rows rather than updating existing ones. The `is_latest` flag transitions from `1` to `0` on the old row, while the new row sets `is_latest = 1` and populates `supersedes` with the previous version’s `id`. This creates an immutable history chain navigable via the `supersedes` foreign key.

### What is the purpose of the `tier` column in the `pages` table?

The `tier` column classifies pages into lifecycle categories—`working`, `episodic`, `semantic`, or `procedural`—that determine how the system manages content decay. According to the schema in [`V01__init.sql`](https://github.com/akitaonrails/ai-memory/blob/main/V01__init.sql), this classification drives automated maintenance policies, with the `pinned` flag providing a manual override to protect specific pages from decay sweeps.

### Can I query pages by their YAML front‑matter metadata?

Yes. The `frontmatter_json` column stores parsed front‑matter as JSON, enabling direct SQL queries against specific metadata fields without re‑parsing markdown. This design allows efficient filtering by tags, authors, or custom front‑matter properties using SQLite’s JSON operators, while the raw `body` field preserves the original markdown content.

### What are the `embedding_*` columns used for?

These nullable columns support optional vector embeddings for semantic search. When populated by later migrations or background jobs, `embedding_provider` and `embedding_model` record the source of the embedding, while `embedding_dim` stores the vector dimensionality. This metadata allows the system to route queries to appropriate vector indices and handle multiple embedding strategies within the same database.