Database Schema Reference

Last Updated: 2026-03-07 Schema Version: V001 (consolidated baseline) Source: crates/infrastructure/sqlite/src/migrations/mod.rs

---

Overview

Each workspace has its own SQLite database at {workspace_path}/inklings.db. The database uses WAL (Write-Ahead Logging) mode for concurrent read access and stores all workspace content: pages, blocks, tags, attachments, sync state, embeddings, event history, and more.

The schema is managed by rusqlite_migration using PRAGMA user_version for version tracking. The current schema is a single consolidated baseline (WORKSPACE_V001). Future migrations are added as WORKSPACE_V002, etc.

---

Entity Relationship Diagram

---

Table Reference

Content Tables

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	id, slug (unique), parent_slug (FK self), title, raw_markdown, page_type, is_deleted, layout	idx_pages_slug, idx_pages_parent_slug, idx_pages_page_type, idx_pages_is_deleted, idx_pages_deleted_at (partial: is_deleted=1)
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)

Type System Tables

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

Tag System Tables

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

Reference Tracking

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

Attachment Tables

Table	Purpose	Key Columns	Notable Indexes
attachments	Uploaded file metadata	id, original_filename, file_extension, content_type, size_bytes, content_hash (unique), sync_status	idx_attachments_hash (unique), idx_attachments_sync_status, idx_attachments_pending_sync (partial: sync_status IN ('local_only', 'failed'))
attachment_references	Junction: which pages reference which attachments	attachment_id (FK attachments), page_id (FK pages), block_id	idx_attachment_refs_page; PK on (attachment_id, page_id)
attachment_sync_queue	Pending attachment uploads to cloud	id (autoincrement), attachment_id (FK attachments), retry_count, synced_at	idx_attachment_sync_queue_pending (partial: synced_at IS NULL)

Sync Infrastructure Tables

Table	Purpose	Key Columns	Notable Indexes
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)	idx_sync_queue_created
sync_dead_letters	Failed sync entries exceeding retry cap	id (autoincrement), block_id, update_bytes (BLOB), retry_count, last_error	idx_sync_dead_letters_failed_at
page_metadata_sync	Per-field LWW state for metadata sync	page_id, field, value, changed_at, device_id	PK on (page_id, field)
metadata_sync_queue	Pending metadata field changes for push	id (autoincrement), page_id, field, value, changed_at, device_id, synced_at	idx_metadata_sync_queue_pending (partial), idx_metadata_sync_queue_page_field (partial)
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	idx_deletion_sync_queue_pending (partial), idx_deletion_sync_queue_page (partial)

Embedding Table

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

Event Log and History Tables

Table	Purpose	Key Columns	Notable Indexes
event_log	Append-only structural change log	id, entity_type, entity_id, event_type, before_value, after_value, device_id, timestamp	idx_event_log_entity, idx_event_log_timestamp, idx_event_log_entity_id, idx_event_log_timestamp_desc
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	idx_event_log_summaries_entity
page_history	Legacy audit shadow table (read-only, no write triggers)	id (autoincrement), page_id, field, old_value, new_value, changed_at	idx_page_history_page_id, idx_page_history_restore

ref_code Columns (V002)

Schema V002 adds a ref_code column to four tables for external addressing (deep links, MCP API, share URLs):

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)

The partial unique index (WHERE ref_code != '') allows the backfill UPDATE (hex(randomblob(8))) to run on existing rows without triggering false uniqueness violations. All rows created after V002 receive a proper 11-character base62 ref_code generated via RefCode::generate().

See Identifier Strategy for the full three-tier model.

---

FTS5 Virtual Tables

pages_fts

A contentless FTS5 virtual table with 3 columns and BM25 weighting:

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.

---

Connection Pool Model

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

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

---

Agent Database (agents.db)

The agent database is separate from inklings.db. Each workspace has a workspace-level agents.db at {workspace_path}/agents.db, and an account-level agents.db at {tauri_data_dir}/agents.db.

Source: crates/infrastructure/sqlite/src/agents/migrations.rs

channels table (workspace-level agents.db)

Column	Type	Notes
id	TEXT PK	UUID
name	TEXT	Display name (UNIQUE constraint)
description	TEXT	Optional description
workspace_id	TEXT	UUID of the owning workspace (added in V007)
is_default	INTEGER	1 for the default channel; exactly one per workspace (added in V007)
created_at	TEXT	ISO-8601 timestamp
created_by	TEXT	Creator identifier (e.g., "system")

conversations table (workspace-level agents.db)

Column	Type	Notes
id	TEXT PK	UUID
ref_code	TEXT	11-char base62 stable identifier (added in V007, backfilled in Rust)
channel_id	TEXT FK	References channels(id) ON DELETE CASCADE
started_at	TEXT	ISO-8601 timestamp
ended_at	TEXT	ISO-8601 timestamp; null while Active
status	TEXT	"active", "idle", or "archived"

Indexes: idx_conversations_channel (channel_id), idx_conversations_status (status), idx_conversations_ref_code (ref_code, partial — excludes empty-string sentinel).

scratchpads table (workspace-level agents.db)

Column	Type	Notes
conversation_id	TEXT	UUID; part of composite primary key
key	TEXT	Entry key; part of composite primary key
value	TEXT	JSON-encoded value
updated_at	TEXT	ISO-8601 timestamp
created_at	TEXT	ISO-8601 timestamp

Primary key: (conversation_id, key). Index: idx_scratchpads_conversation_created (conversation_id, created_at) for TTL-based cleanup queries.

---

See Also

• 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
• Tag System — tag CRUD, groups, FTS integration
• Attachment System — upload, storage, sync queue
• Wiki-Link System — reference tracking, backlinks, rename propagation
• Layout System — CSS Grid templates, builtin layouts
• CRDT BLOB Passthrough Pipeline — binary pass-through design for content_loro
• SQLite WAL Reader/Writer Pool — connection pool rationale
• Agent Memory System — memory hierarchy, scratchpad, channels, conversations
• Source: crates/infrastructure/sqlite/src/migrations/mod.rs — full workspace migration SQL
• Source: crates/infrastructure/sqlite/src/agents/migrations.rs — full agents.db migration SQL
• Source: crates/infrastructure/sqlite/src/connection.rs — WorkspaceDatabase implementation