Database Schema Reference

Status: Accepted Reference epics: INK-825, INK-826, INK-827, INK-828, INK-829, INK-835, INK-838 ADRs: ADR-016, ADR-018, ADR-019, ADR-020

Schema Version: V001 (consolidated baseline) Source: crates/infrastructure/sqlite/src/migrations/mod.rs

---

Overview

Each workspace has a single SQLite database at {workspace_path}/inklings.db. The database uses WAL (Write-Ahead Logging) mode for concurrent read access and stores everything the workspace owns: workspace content (pages, blocks, tags, attachments), world-model primitives (provenance, derivation links, deviation records, re-validation flags), agent runtime state (LangGraph checkpoints, four-tier memory, channels and conversations), sync state, embeddings, and the event log.

This is a rebuild from a prior architecture that maintained a separate agents.db file. Per ADR-020, there is no data migration and no old workspaces: the first workspaces that exist under the new schema are created under it. That allows the consolidated layout to drop complexity rather than inherit it.

The schema is managed by rusqlite_migration using PRAGMA user_version for version tracking. The current schema is a single consolidated baseline (WORKSPACE_V001). Subsequent migrations are named WORKSPACE_V002, WORKSPACE_V003, and so on, each applied in order at startup.

Schema families

Tables are grouped into families that map one-to-one onto architectural layers:

Family	Purpose	Covered by
Content	Pages, blocks, frontmatter, types, properties, layouts, tags, references, attachments	Existing workspace content model
World-model primitives	Provenance axes (origin, lifecycle) on content; derivation links; deviation records; re-validation flags	ADR-018, ADR-019
Agent runtime	LangGraph checkpoints; four-tier agent memory; channels; conversations	ADR-016
Sync	CRDT state, offline queues, tombstones	Existing sync engine
Embeddings & search	Dense vectors, FTS5 virtual tables	Existing semantic/keyword search
Event log & history	Append-only structural change log, bookmarks, summaries	Existing event log

Agent-runtime tables share the same physical database file as content tables but are schema-separated: they do not share tables or namespaces with content. This preserves the domain rule that “agent working state is not workspace state” at the schema level while letting agent state ride along with the workspace through every sync, export, and migration path the workspace already supports.

---

Entity Relationship Diagram

The diagram below focuses on the new and changed surfaces; content, tag, and type tables are unchanged in shape from the prior schema and are summarized in the tables that follow.

---

What Changed From the Prior Schema

Readers coming from the earlier architecture should orient against three structural shifts before the table reference.

1. Single database file. The prior architecture maintained a separate workspace-level agents.db for agent runtime state (channels, conversations, scratchpads) alongside inklings.db. Those tables now live inside inklings.db under the Agent Runtime family. There is no workspace-level agents.db file under the new schema, and there is no schema that straddles files.

2. Provenance columns on content. The pages table gains origin, lifecycle, origin_source_id, and origin_at columns, making the two-axis provenance model from ADR-018 physically present on every page. See domain rule 2 and domain rule 3 for the invariants these columns enforce, and systems/world/provenance for the conceptual model.

3. Three new world-model tables; skill surface reshaped. derivation_link, deviation_record (with deviation_content_ref), and revalidation_flag are new first-class tables serving the world-model primitives. The skill subsystem tables (skills, skill_artifacts, and a new skill_permissions) move from the retired workspace-level agents.db into inklings.db in their own table family, outside both the world-content schema and the agent_memory tiers; the DSPy-specific compilation surface and the execution_traces table are retired. See Skill Storage.

The account-level database at {tauri_data_dir}/agents.db is preserved for cross-workspace account-scoped data (e.g., the account-tier agent memory surface). It is the only non-workspace SQLite file the runtime owns. Workspace-scoped agent state lives inside the per-workspace inklings.db.

---

Table Reference

Content Tables

Unchanged in shape from the prior schema except where noted.

Table	Purpose	Key Columns	Notable Indexes
workspace	Single-row workspace metadata	id, name, cloud_id, sync_enabled, metadata_cursor, deletion_cursor	—
pages	Page entities with parent-child hierarchy and provenance	id, slug (unique), parent_slug (FK self), title, raw_markdown, page_type, origin, lifecycle, origin_source_id, origin_at, is_deleted, layout	idx_pages_slug, idx_pages_parent_slug, idx_pages_page_type, idx_pages_is_deleted, idx_pages_origin, idx_pages_lifecycle
blocks	Block content within pages	id, page_id (FK pages), slot_id, content (text), content_loro (BLOB), content_loro_version, content_type, content_type_metadata, area	idx_blocks_page_id; unique on (page_id, slot_id)
frontmatter	Key-value metadata per page	page_id (FK pages), key, value	PK on (page_id, key)

The new provenance columns on pages:

Column	Definition	Notes
origin	TEXT NOT NULL CHECK (origin IN ('authored','imported','agent_produced','observed'))	Factual, immutable after creation
lifecycle	TEXT NOT NULL CHECK (lifecycle IN ('draft','candidate','canonical','retired'))	Trajectory; transitions enforced by domain
origin_source_id	TEXT	Nullable pointer to the tool/run/import that produced this page
origin_at	TEXT NOT NULL	ISO-8601 timestamp; equal to page creation time in V001

Pre-existing rows in a freshly-created workspace have no legacy-backfill path because there is no legacy data; any content migrated in by a user-initiated import lands as origin='imported', lifecycle='draft' per the import lifecycle decision documented in systems/world/provenance. Inside a live workspace, any content that predates a future schema migration adding a world-model field is backfilled as origin='authored', lifecycle='canonical' — the pragmatic default for author-owned pre-existing content.

Type System Tables

Unchanged from prior schema.

Table	Purpose	Key Columns
types	Type definitions (Page, Folder + user-defined)	id, slug (unique), name, is_system, default_layout_id, sort_order
properties	Property definitions for typed pages	id, slug (unique), name, value_type, config (JSON)
type_property_refs	Junction: which properties belong to which types	type_id (FK types), property_id (FK properties), sort_order
page_type_assignments	Junction: which types are assigned to which pages	id, page_id (FK pages), type_id (FK types), scope, source_id
container_rules	Auto-assign types to pages created in folders	id, folder_page_id (FK pages), type_id (FK types), depth
collection_views	Saved view preferences per type	id, type_slug (unique), view_mode, column_config (JSON)
layouts	Reusable CSS Grid layout definitions	id, name (unique), layout (JSON), is_builtin

Tag System Tables

Unchanged from prior schema.

Table	Purpose	Key Columns
tag_groups	Grouping for tags	id, name, slug (unique), color
tags	Tag entities	id, name, slug (unique), color, group_id (FK tag_groups)
page_tags	Junction: tag-to-page assignments	page_id (FK pages), tag_id (FK tags), assigned_at

Reference Tracking

Unchanged from prior schema. Wiki-link references remain the workspace’s [[...]] pointer surface and are distinct from derivation links (see below): a reference captures “this page mentions that page”; a derivation link captures “this page was derived from that page.”

Table	Purpose	Key Columns
references	Wiki-link tracking ([[Display|slug]])	id (autoincrement), source_page_id (FK pages), target_page_id (FK pages, nullable), target_slug, display_text, heading

World-Model Primitives

These tables are new and implement the world-model surfaces defined in systems/world. All inserts into these tables flow through the submit boundary (domain rule 1).

derivation_link

Internal, first-class relationship type connecting a derived page to one or more source pages. Used by the agent when producing inferred content and by any tool that declares itself derivation-emitting at registration. Per ADR-018, derivation is entirely internal to the workspace — sources are always workspace entities; cross-workspace derivation is not supported.

Column	Type	Notes
id	TEXT PK	UUID
derived_page_id	TEXT FK pages	The page produced from the source(s)
source_page_id	TEXT FK pages	The page the derived content draws from
link_type	TEXT	e.g., direct, supporting, contradicting; extensible
note	TEXT	Optional human- or agent-authored rationale
created_by	TEXT	authored, agent, or a specific tool id
created_at	TEXT	ISO-8601

Indexes: idx_derivation_link_derived (derived_page_id), idx_derivation_link_source (source_page_id), idx_derivation_link_source_type (source_page_id, link_type). A derived page with many sources has many rows; a source page with many descendants has many rows. Derivation topology is queried from this table and is the substrate for the standing signal (see systems/world/provenance#standing-derived and the standing discussion in systems/world/derivation-links).

deviation_record

First-class workspace record type — queryable and referenceable from content, but not a workspace entity. Per ADR-019, deviation records do not have their own origin/lifecycle/derivation axes; their status field (open/resolved/dismissed) is a narrower, deviation-specific trajectory.

Column	Type	Notes
id	TEXT PK	UUID
ref_code	TEXT	11-char base62 stable identifier, unique per workspace
deviation_type	TEXT	One of agent_answer_vs_canonical, agent_inference_revisited, user_correction_signaling_structural_gap, canonical_vs_canonical
detecting_source	TEXT	conversational_agent_run, scheduled_task, user_flag
status	TEXT	open, resolved, dismissed
resolution_note	TEXT	Populated on resolve/dismiss
detected_at	TEXT	ISO-8601
resolved_at	TEXT	ISO-8601; null while open

Indexes: idx_deviation_record_status, idx_deviation_record_type, idx_deviation_record_detected_at, idx_deviation_record_ref_code (unique, partial on non-empty).

Every submit-boundary conflict produces a deviation record — no agent self-assessment suppresses creation. Submit-boundary denials (the agent lacked a required capability) do not produce deviations; see domain rule 7.

deviation_content_ref

Junction table recording which workspace content a deviation record refers to. A deviation typically references at least the submitted content and the canonical content it conflicts with; additional context pages can be included.

Column	Type	Notes
deviation_id	TEXT FK deviation_record
page_id	TEXT FK pages
role	TEXT	submitted, canonical, supporting_context

Primary key: (deviation_id, page_id, role). Index: idx_deviation_content_ref_page (page_id) for inline-marker lookup (“does this page have open deviations against it?”).

revalidation_flag

Generated when content changes in a way that makes derived content potentially stale. Per domain rule 6 and the standing-scaled mechanics in the re-validation queue, every change to every source produces a flag; standing influences prominence, not whether the flag exists. The world model does not auto-rewrite derived content; flags are processed by the author or by a scheduled autonomous task (accept-as-still-valid, update, or retire).

Column	Type	Notes
id	TEXT PK	UUID
derived_page_id	TEXT FK pages	The page whose validity is in question
source_page_id	TEXT FK pages	The page whose change triggered the flag
derivation_link_id	TEXT FK derivation_link	The specific link implicated
flag_type	TEXT	source_edited, source_retired, source_superseded, canonical_conflict
prominence	INTEGER	Snapshot of source’s standing signal at flag-creation time, bucketed
status	TEXT	open, accepted, updated, retired, dismissed
created_at	TEXT	ISO-8601
resolved_at	TEXT	ISO-8601; null while open

Indexes: idx_revalidation_flag_derived (derived_page_id), idx_revalidation_flag_source (source_page_id), idx_revalidation_flag_status_prominence (status, prominence DESC) for triage-queue ordering.

Prominence is a snapshot at flag-creation time, not a continuously-re-evaluated signal. If a source’s standing changes after a flag is created, existing flag rows are not updated; subsequent source changes produce new flags with the current prominence. This keeps the queue’s ordering stable as the user works it.

Agent Runtime

These tables back the Python sidecar’s LangGraph-based World Agent per ADR-016. They are schema-separated from content tables: they live in the same inklings.db file but do not share tables or namespaces with content tables.

langgraph_checkpoints and langgraph_writes

LangGraph’s checkpointer persistence tables. These preserve graph-execution state — messages, state channels, interrupt state — keyed by thread_id. After a sidecar restart, LangGraph resumes from the last checkpoint for a given thread_id; the Rust side is responsible for preserving thread_id across restart boundaries via the agent-event IPC.

The schema follows langgraph-checkpoint-sqlite’s SqliteSaver layout (or a compatible custom BaseCheckpointSaver — both remain open at implementation time). Column names and semantics are LangGraph-standard:

langgraph_checkpoints

Column	Type	Notes
thread_id	TEXT	Part of composite PK; continuity key for a run
checkpoint_ns	TEXT	Namespace for nested graphs
checkpoint_id	TEXT	UUID for this checkpoint
parent_checkpoint_id	TEXT	Parent for replay/history
type	TEXT	Checkpoint type
checkpoint	BLOB	Serialized state
metadata	BLOB	Serialized metadata

Primary key: (thread_id, checkpoint_ns, checkpoint_id). Index: idx_langgraph_checkpoints_thread for history replay.

langgraph_writes

Column	Type	Notes
thread_id	TEXT
checkpoint_ns	TEXT
checkpoint_id	TEXT
task_id	TEXT	Task-local identifier
idx	INTEGER	Ordering within task
channel	TEXT	State channel name
type	TEXT	Value type tag
value	BLOB	Serialized value

Primary key: (thread_id, checkpoint_ns, checkpoint_id, task_id, idx).

The checkpointer holds run continuity, not durable operating context; see agent_memory below for the latter.

agent_memory

Four-tier agent memory — the only memory system. Per ADR-016, the runtime does not provide a virtual filesystem, does not read/write an AGENTS.md-style durable-context file, and does not operate a parallel long-term store.

Column	Type	Notes
id	TEXT PK	UUID
tier	TEXT	account, workspace, channel, conversation
scope_id	TEXT	Tier-scoped id (e.g., channel id for tier='channel'); null for account
key	TEXT	Memory key
value	TEXT	JSON-encoded content
importance	REAL	0.0–1.0; influences retention and retrieval ranking
access_count	INTEGER	Incremented on read
last_accessed_at	TEXT	ISO-8601
created_at	TEXT	ISO-8601

Indexes: idx_agent_memory_tier_scope (tier, scope_id), idx_agent_memory_tier_key (tier, key), idx_agent_memory_last_accessed (last_accessed_at DESC) for decay processing.

The account tier’s rows live in the account-level agents.db at {tauri_data_dir}/agents.db using the same schema; workspace, channel, and conversation tiers live in each workspace’s inklings.db. Consolidation, summarization, and promotion between tiers — if wanted — are scheduled World Agent tasks registered with the Rust task-runner, not a separate memory-manager service.

channels

Column	Type	Notes
id	TEXT PK	UUID
name	TEXT	Display name (unique within workspace)
description	TEXT	Optional
is_default	INTEGER	1 for the workspace’s default channel; exactly one per workspace
created_at	TEXT	ISO-8601
created_by	TEXT	Creator identifier (e.g., "system")

conversations

Column	Type	Notes
id	TEXT PK	UUID
ref_code	TEXT	11-char base62 stable identifier
channel_id	TEXT FK channels	ON DELETE CASCADE
thread_id	TEXT	LangGraph continuity key; unique; links to langgraph_checkpoints.thread_id
started_at	TEXT	ISO-8601
ended_at	TEXT	ISO-8601; null while active
status	TEXT	active, idle, archived

Indexes: idx_conversations_channel, idx_conversations_status, idx_conversations_ref_code (unique, partial on non-empty), idx_conversations_thread_id (unique, partial on non-empty).

The thread_id column is the bridge between the conversation as a product surface and the graph-execution state the checkpointer preserves.

ref_code Columns

External addressing for deep links, MCP API, and share URLs. The world-model rebuild extends the ref_code pattern to deviation records in addition to the four tables the prior schema already covered.

Table	Column	Definition	Index
pages	ref_code	TEXT NOT NULL DEFAULT ''	idx_pages_ref_code (partial, WHERE ref_code != '')
blocks	ref_code	TEXT NOT NULL DEFAULT ''	idx_blocks_ref_code (partial)
bookmarks	ref_code	TEXT NOT NULL DEFAULT ''	idx_bookmarks_ref_code (partial)
attachments	ref_code	TEXT NOT NULL DEFAULT ''	idx_attachments_ref_code (partial)
deviation_record	ref_code	TEXT NOT NULL DEFAULT ''	idx_deviation_record_ref_code (partial)
conversations	ref_code	TEXT NOT NULL DEFAULT ''	idx_conversations_ref_code (partial)

The partial unique index (WHERE ref_code != '') allows the initial insert to use a sentinel empty string without triggering false uniqueness violations; rows receive a proper 11-character base62 ref_code generated via RefCode::generate() before any external reference is emitted.

See Identifier Strategy for the full three-tier model.

Attachment Tables

Unchanged from prior schema.

Table	Purpose	Key Columns
attachments	Uploaded file metadata	id, original_filename, file_extension, content_type, size_bytes, content_hash (unique), sync_status
attachment_references	Junction: which pages reference which attachments	attachment_id (FK attachments), page_id (FK pages), block_id
attachment_sync_queue	Pending attachment uploads to cloud	id (autoincrement), attachment_id (FK attachments), retry_count, synced_at

Sync Infrastructure Tables

Unchanged from prior schema. Sync applies uniformly to content tables and world-model primitive tables; agent-runtime tables sync under the same file-level mechanism as the rest of inklings.db but the domain rule that “agent working state is not workspace state” means scheduled cross-device coordination of in-flight agent runs is an explicit product decision, not an automatic consequence of file sync.

Table	Purpose	Key Columns
sync_state	Per-block CRDT version vectors and sync cursor	block_id (PK), local_version_vector (BLOB), remote_version_vector (BLOB), last_synced_at
sync_queue	Offline push queue for block CRDT updates	id (autoincrement), block_id, update_bytes (BLOB), retry_count, version_vector (BLOB)
sync_dead_letters	Failed sync entries exceeding retry cap	id (autoincrement), block_id, update_bytes (BLOB), retry_count, last_error
page_metadata_sync	Per-field LWW state for metadata sync	page_id, field, value, changed_at, device_id
metadata_sync_queue	Pending metadata field changes for push	id (autoincrement), page_id, field, value, changed_at, device_id, synced_at
page_tombstones	Tombstones for remotely deleted pages	page_id (PK), deleted_by_device, page_title, deleted_at, had_local_edits
deletion_sync_queue	Pending page deletion tombstones for push	id (autoincrement), page_id, page_title, device_id, synced_at

Embedding Table

Unchanged from prior schema.

Table	Purpose	Key Columns
page_embeddings	Dense vector embeddings for semantic search	page_id (PK, FK pages), model_id, model_version, embedding (BLOB, 768 f32 = 3072 bytes)

Event Log and History Tables

Unchanged in shape from prior schema. The event log records structural changes to every family of table above, including world-model primitives (derivation-link creation, deviation-record status transitions, re-validation-flag resolutions) and agent-runtime state (channel/conversation lifecycle). Scheduled-autonomous-task-originated entries are distinguishable via device_id and event-source metadata for event-log UI filtering.

Table	Purpose	Key Columns
event_log	Append-only structural change log	id, entity_type, entity_id, event_type, before_value, after_value, device_id, timestamp
bookmarks	Named anchors in the event timeline	id, name, description, timestamp, created_by
event_log_summaries	Collapsed history between bookmark intervals	id (autoincrement), entity_type, entity_id, from_bookmark_id, to_bookmark_id, summary, event_count

---

Removed Surfaces

The following tables from the prior architecture are not present in the consolidated schema:

• DSPy skill-compilation surface — the Execute/Optimize/Manifest pipeline, execution_traces, artifact_traces, and the DspyModule / CodeTemplate artifact kinds are retired. Skills no longer have a compilation pipeline or a compiled-artifact store; the Skill Composer subagent composes skills at runtime on demand. The skill package tables themselves — skills, skill_artifacts (as a multi-artifact package store, not a compiled-module store), and the new skill_permissions — survive and move into inklings.db in their own family. They are not part of agent_memory and not part of the world-content schema; see Skill Storage.
• Workspace-level agents.db — the file itself is not created. Its contents (channels, conversations, scratchpads) are folded into inklings.db under the Agent Runtime family, except that scratchpads is removed: scratch-level agent state is a LangGraph state channel with a simple reducer, checkpointed into langgraph_checkpoints / langgraph_writes, not a standalone table.

The removals are enabled by ADR-020’s rebuild posture: no data migration, no legacy-schema adapters, no backfill scripts. The only continuity with prior workspaces is conceptual.

---

FTS5 Virtual Tables

pages_fts

A contentless FTS5 virtual table with 3 columns and BM25 weighting. Unchanged from prior schema.

Column	Weight	Source
title	10.0	pages.title
content	1.0	pages.raw_markdown
tags	5.0	Aggregated tags.name via page_tags join

Configuration: content='' (contentless), contentless_delete=1.

The FTS index is maintained by 5 triggers:

Trigger	Fires On	Purpose
pages_fts_insert	AFTER INSERT ON pages	Index new non-deleted pages
pages_fts_update	AFTER UPDATE ON pages	Re-index on page content/title change
pages_fts_delete	BEFORE DELETE ON pages	Remove from index on permanent delete
page_tags_fts_insert	AFTER INSERT ON page_tags	Re-index page when tag is added
page_tags_fts_delete	AFTER DELETE ON page_tags	Re-index page when tag is removed
tags_fts_name_update	AFTER UPDATE OF name ON tags	Re-index all pages with renamed tag

Soft-deleted pages (is_deleted = 1) are excluded from the FTS index. The {{property:value}} syntax is tokenized naturally by FTS5’s unicode61 tokenizer — braces and colons act as separators.

---

Seed Data

The V001 migration seeds two system types and four system properties:

System types: Page (slug: page) and Folder (slug: folder), both with is_system = 1.

System properties (linked to Page type): summary, cover-image, tags, aliases.

Default channel: One row in channels with name='General', is_default=1, created_by='system'. The workspace’s World Agent uses this as its initial surface; additional channels are user-created.

---

Connection Pool Model

The WorkspaceDatabase struct (crates/infrastructure/sqlite/src/connection.rs) implements a writer + reader pool architecture. Unchanged from prior schema.

Component	Count	Purpose
Writer	1	All writes and transactions (SQLite allows one writer at a time)
Readers	4	Concurrent reads via WAL mode, round-robin selection

Connection Configuration

Every connection (writer and readers) is configured with:

Pragma	Value	Purpose
busy_timeout	5000ms	Wait instead of failing immediately on lock contention
journal_mode	WAL	Enable concurrent reads during writes
foreign_keys	ON	Enforce foreign key constraints
synchronous	NORMAL	Balance durability and performance
cache_size	-2000 (2 MB)	Per-connection page cache

API

• with_reader(f) — execute read-only closure on a reader connection (round-robin)
• with_writer(f) — execute write closure on the writer connection
• with_transaction(f) — execute closure within a transaction (always uses writer)
• with_connection(f) — deprecated alias for with_reader

The Python sidecar accesses inklings.db through its own reader/writer via LangGraph’s checkpointer and the four-tier memory tools. Concurrency between the Rust main process and the sidecar is mediated by SQLite’s WAL mode and busy_timeout; write ordering between the two processes is not orchestrated by this layer and is not required to be — every cross-boundary write originates from a tool call that serializes through the MCP surface.

---

Account-Level Database

A single account-level SQLite at {tauri_data_dir}/agents.db holds data that is not workspace-scoped. Under the new schema this is narrow in scope — the account tier of agent_memory and any account-scoped operator state that does not belong inside any single workspace.

Source: crates/infrastructure/sqlite/src/agents/migrations.rs (account-level schema)

The account-level database uses the same agent_memory schema as the workspace-level database. Rows in the account-level database have tier='account' and scope_id=NULL. The workspace-level database’s account-tier agent memory is not present; account-tier memory is read from the account-level file.

---

See Also

• Domain Rules — invariants enforced on every row in the world-model primitive tables
• Architecture Overview — how storage sits under the domain and the sidecar
• systems/world — conceptual model for provenance, derivation, deviation, re-validation
• systems/world/provenance — the two stored axes behind the origin and lifecycle columns
• systems/world/derivation-links — derivation-link semantics and the standing signal
• systems/world/deviation-records — deviation records, triage inbox, inline markers
• systems/world/retroactive-revision — re-validation-flag mechanics
• systems/world/submit-boundary — the single domain construct every write funnels through
• systems/agent/agent-memory-system — four-tier memory tools and retrieval semantics
• systems/agent/mcp-system — how the sidecar calls Rust tools
• Page System — page and block entity design, domain rules
• Sync System — sync engine state machine, cursor safety, LWW patterns
• Embedding System — embedding pipeline, model details, search integration
• Event Log System — event recording, timeline queries, history collapse
• Source: crates/infrastructure/sqlite/src/migrations/mod.rs — full workspace migration SQL
• Source: crates/infrastructure/sqlite/src/agents/migrations.rs — account-level migration SQL
• Source: crates/infrastructure/sqlite/src/connection.rs — WorkspaceDatabase implementation