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

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 (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 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:

// 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:

-- 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, 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.

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