← Back to Docs

Database Schema

Polylogue does not store the archive in one SQLite file. The archive is a set of SQLite files split by durability class, all in WAL mode. Each file (a "tier") owns a distinct kind of data and carries its own independent schema version. This page describes the conceptual shape and points at the canonical DDL; for the deeper invariant detail see Internals and Architecture.

Authoritative source

The DDL under polylogue/storage/sqlite/archive_tiers/ is the single source of truth for tables, columns, CHECK constraints, and indexes. This document deliberately does not re-list every column, because an exhaustive column table drifts from the DDL and goes stale. When you need an exact column, read the tier file named below — do not trust a prose summary over the CREATE TABLE statement.

There is no single global "schema version" number. Each tier is versioned and bootstrapped independently.

The runtime table inventory per tier is also enumerated in polylogue/cli/commands/status.py (_ARCHIVE_TIER_TABLES), which polylogue status uses to report row counts; that dict is a useful cross-check against the DDL.

The Five Tiers

source.db — raw acquisition (rebuild-from-source)

Stores acquired bytes and source evidence. Everything downstream is rebuilt from this tier, so it is the durable record of what was ingested.

Core tables: raw_sessions (one row per acquired payload, keyed by raw_id, carrying origin, native_id, blob_hash, and parse/validation state), raw_artifacts (artifact-taxonomy classification per acquired file), blob_refs / blob_publication_reservations / gc_generations (content-addressed blob references, in-flight publication protection, and the GC bookkeeping described in Internals § Blob Store Model), raw_hook_events, and history_sidecars.

index.db — parsed tree, search, and insights (rebuildable)

The parsed session/message/block tree, full-text search indexes, cross-session topology, and the materialized read models. Rebuildable from source.db.

Core tables: sessions, messages, blocks, plus the actions view (not a table — see below), session_links (typed cross-session edges), threads / thread_sessions, attachment tables (attachments, attachment_refs, attachment_native_ids), paste_spans, cost tables (price_catalogs, model_prices, session_reported_costs, session_model_usage), the auto-tag side of session_tags, and the insight read models (session_profiles, session_work_events, session_phases, session_latency_profiles, session_tag_rollups, threads) plus insight_materialization for cache invalidation.

embeddings.db — vectors (rebuildable, expensive)

message_embeddings (a vec0 virtual table, 1024-dimensional float embeddings), message_embeddings_meta, and embedding_status. Populated only when embedding is enabled with a valid Voyage key (see Architecture § Embedding Pipeline). Rebuildable, but re-embedding costs Voyage API calls.

user.db — irreplaceable human input (back up this one)

The only tier that is not rebuildable from source. Its canonical table is assertions: user marks, annotations, corrections, suppressions, tags, metadata, saved views, recall packs, workspaces, blackboard notes, candidate claims, and operator judgments are all assertion rows with lifecycle state. Candidate review uses the closed status vocabulary candidate, accepted, rejected, deferred, and superseded; active assertions are separate durable claims and are not returned by candidate-review reads. index.db.session_tags remains a rebuildable auto-tag/read-model projection; human-owned tag and metadata writes do not have separate user-tier tables. user_settings stores durable local settings, while context_deliveries records exact context-image delivery receipts without overloading epistemic assertions. Immutable, versioned annotation construct definitions live in annotation_schemas; independent labeling-run provenance and counts live in annotation_batches. The resulting annotation rows stay in assertions and link to their batch via an annotation-batch:<id> scope_ref.

ops.db — disposable daemon telemetry

Ingest cursors (ingest_cursor), ingest attempts (ingest_attempts), convergence debt (convergence_debt), cursor-lag samples, daemon stage/event logs, embedding catch-up runs, OTLP spans/telemetry, and the MCP call log (mcp_call_log, #7s57 — tool name, session id when known, timing, and success/failure per MCP tool invocation). mcp_call_session_refs normalizes plural invocations so one call remains queryable through every member session. Completed calls first enter an atomic, archive-namespaced outbox under the Polylogue XDG state directory; MCP startup retries pending files through the daemon's authenticated, idempotent writer route. The outbox is deleted only after a matching acknowledgement; conflicting IDs are quarantined rather than blocking later calls. MCP readiness_check reports pending/quarantined counts and bytes, oldest debt, queue pressure, and delivery failures. ops.db remains safe to discard and rebuild from later telemetry.

Schema/data-model ownership matrix (#2246)

This matrix records the current owner for the evidence-cockpit concepts that were ambiguous during #2246. It is intentionally source-grounded: canonical DDL stays in polylogue/storage/sqlite/archive_tiers/, and this prose names the storage owner rather than duplicating full table definitions.

JSON payload contracts

SQLite STRICT mode does not make a TEXT column JSON-shaped. New executable JSON checks should use the shared helpers in archive_tiers/common.py so the contract is visible in canonical DDL instead of hand-written per table.

Recent index-tier versions:

• version 34 (polylogue-rgbj) adds idx_web_constructs_message on web_content_constructs(message_id) — the only messages(message_id) FK child table that lacked a leading index on its foreign-key column. Without it, SQLite's ON DELETE CASCADE enforcement fell back to a full table scan of web_content_constructs per deleted message, making _replace_full_session_messages_and_blocks's session-scoped DELETE FROM messages cost O(deleted_messages x web_content_constructs_rows) — the dominant cost in a production 10+ minute full-session replacement;
• version 9 adds typed sessions.session_kind for standard vs temporary sessions, so temporary chats are archive schema rather than only parser tags;
• version 8 adds web_content_constructs for provider-native web/export constructs such as citations, source references, canvas/artifact revisions, asset pointers, and task/search records;
• version 7 enforces object JSON for blocks.tool_input and session_provider_usage_events.payload_json, two hot read-path columns with current typed writers.

Broader payload columns such as insight evidence, inference, enrichment, work-event arrays, source hook payloads, and user assertion JSON should be constrained only after their historical write shapes are audited and the required tier-version/rebuild consequence is documented.

Identifiers

Primary keys in index.db are generated from natural components rather than opaque UUIDs (see the GENERATED ALWAYS AS (...) STORED columns in index.py):

• sessions.session_id = origin || ':' || native_id
• messages.message_id = session_id || ':' || COALESCE(native_id, position || '.' || variant_index)
• blocks.block_id = message_id || ':' || position

origin is a closed vocabulary (Origin enum in polylogue/core/enums.py, e.g. claude-code-session, claude-ai-export, chatgpt-export, codex-session, aistudio-drive) enforced by a SQL CHECK. It is the public source-origin token; the provider-wire Provider enum is used only at the parsing/schema boundary.

Timestamps, hashes, and types

• Timestamps are stored as integer epoch milliseconds in *_at_ms columns (e.g. created_at_ms, updated_at_ms, occurred_at_ms).
• Content hashes are 32-byte BLOB columns (CHECK(length(...) = 32)), SHA-256 over the NFC-normalized session payload — title, timestamps, messages, attachments, and blocks. User metadata (tags, summaries, notes) is excluded from the hash, so editing it does not trigger re-import. See Internals § Content Hash Model.
• Tables use SQLite STRICT mode and closed-enum CHECK constraints generated from the StrEnum vocabularies in polylogue/core/enums.py.

Content blocks are first-class rows

Structured message content lives in the blocks table — one row per block, keyed (message_id, position), with a block_type constrained to the BlockType enum (text, thinking, reasoning, tool_use, tool_result, image, code, document). Tool calls carry tool_name, tool_id, and tool_input; tool_command and tool_path are virtual generated columns extracted from tool_input JSON.

actions is a view, not a table: it left-joins tool_use blocks to their matching tool_result block by both tool_id and session_id. Provider tool_id values are treated as session-local evidence, so the view must not pair a use block with a same-id result block from another session.

Full-text search

index.db carries three FTS5 virtual tables, all using the unicode61 tokenizer (no porter stemmer in this SQLite build):

messages_fts is kept in sync with blocks by the messages_fts_ai/ad/au triggers. During bulk ingest these triggers are suspended for performance and restored before commit; see Internals § FTS5 Model for the drift-detection and repair behavior.

Schema Versioning — durability-keyed regimes

On startup each tier's on-disk PRAGMA user_version is compared against its tier constant:

• Empty file (user_version == 0): bootstrap fresh.
• Version match: open as-is.
• Older durable tier (source.db or user.db): ordinary open is rejected; an explicit additive numbered migration may proceed only after a backup was scratch-restored, integrity-checked, and authenticated by the archive's local verification key.
• Derived mismatch or newer durable tier: reject and require rebuild or a newer runtime as appropriate.

Derived tiers (index.db, embeddings.db) have no migration chain. For an index-tier schema bump, the operator rebuilds from durable evidence:

polylogue ops reset --index && polylogued run

Durable source/user changes instead use additive SQL under storage/sqlite/migrations/{source,user}/NNN_*.sql, advancing exactly one version per step. The bootstrap branching that decides fresh-init, open, migrate, rebuild, or reject is shared across sync and async backends in storage/sqlite/schema_bootstrap.py. See Internals § Schema Versioning Model and CONTRIBUTING § Schema-Touching Changes.

Inspecting an archive

polylogue ops status                    # daemon/archive snapshot, including per-tier row counts
polylogue ops maintenance archive-plan  # planned archive file set
polylogue ops doctor                     # schema health + referential integrity
polylogue ops doctor --schemas           # provider-schema conformance over raw records

The devtools lab schema roundtrip command verifies committed provider schema packages reload and roundtrip cleanly through typed models.

See also: Architecture · Internals · Data Model · Configuration