CODITECT Database Schema

Architecture: Two-Database (ADR-089) Engine: SQLite 3 with FTS5 full-text search Updated: 2026-01-20

Two-Database Architecture (ADR-089)

CODITECT uses two separate databases to separate platform data from customer data:

Database	Location	Purpose	Access
platform.db	context-storage/platform.db	Component data (H.P.001-AGENTS, H.P.003-SKILLS, H.P.002-COMMANDS)	Read-only
context.db	context-storage/context.db	Customer data (sessions, messages, decisions)	Read/write

platform.db (Component Data)

Category	Tables	Row Count
Components	4	2,306
Capabilities	1	44,431
Triggers	1	16,635
Relationships	2	10,513
Embeddings	1	-
FTS Support	4	-
Total	13 tables	~74K rows

Populated by: python3 H.P.004-SCRIPTS/component-indexer.py

context.db (Customer Data)

Category	Tables	Views	Row Count
Messages & Context	8	1	279,711
Knowledge Extraction	5	4	12,577
Document Index	4	1	4,348
Project Management	6	6	1,364
Learning System	8	0	158,359
Token Usage	1	4	568
Workspace	1	0	3
FTS Support	48	0	-
System	2	2	-
Total	~129 tables	19 views	~456K rows

Note: Component tables (components, capabilities, triggers, etc.) have been moved to platform.db as of 2026-01-20.

---

Quick Start

# Database location
context-storage/context.db

# Auto-created on first /cx or /cxq command
/cx    # Populates database
/cxq   # Queries database

---

1. Messages & Context

messages

Primary message store for extracted session content.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT	Unique identifier
hash	TEXT	UNIQUE NOT NULL	SHA256 content hash for deduplication
content	TEXT	NOT NULL	Full message content
role	TEXT	NOT NULL	'user', 'assistant', or 'system'
source_type	TEXT		'jsonl' or 'export'
source_file	TEXT		Original file path
session_id	TEXT		Claude Code session ID
checkpoint	TEXT		Associated checkpoint name
timestamp	TEXT		When message occurred
extracted_at	TEXT		When extracted to database
content_length	INTEGER		Character count
has_code	BOOLEAN		Contains code blocks
has_markdown	BOOLEAN		Contains markdown formatting
agent_context	TEXT		JSON: Agent-specific context
tool_use	TEXT		JSON: Tool invocation data
tool_result	TEXT		JSON: Tool result with status
export_metadata	TEXT		JSON: Export header metadata

Indexes: role, timestamp, session_id, source_type, content_length, tool_status, export_model FTS: messages_fts (content, role) Row Count: 30,609

entries

Comprehensive entry store for all 6 Claude Code entry types (ADR-025).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
hash	TEXT	UNIQUE NOT NULL	Content hash
entry_type	TEXT	NOT NULL	user, assistant, system, summary, queue-operation, file-history-snapshot
uuid	TEXT		Entry UUID
parent_uuid	TEXT		Parent entry UUID
logical_parent_uuid	TEXT		Logical parent UUID
session_id	TEXT		Session identifier
agent_id	TEXT		Agent identifier
slug	TEXT		Entry slug
cwd	TEXT		Working directory
git_branch	TEXT		Git branch
version	TEXT		Claude Code version
timestamp	TEXT		Entry timestamp
extracted_at	TEXT		Extraction timestamp
is_sidechain	BOOLEAN	DEFAULT FALSE	Is sidechain entry
is_meta	BOOLEAN	DEFAULT FALSE	Is meta entry
user_type	TEXT		User type
content	TEXT		Entry content
role	TEXT		Entry role
raw_json	TEXT	NOT NULL	Original JSON
source_file	TEXT		Source file path
source_line	INTEGER		Source line number

Indexes: entry_type, uuid, parent_uuid, session_id, agent_id, timestamp, role FTS: entries_fts Row Count: 247,792

embeddings

Vector embeddings for semantic search.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
message_id	INTEGER	UNIQUE NOT NULL, FK messages
embedding	BLOB	NOT NULL	Vector embedding data
model	TEXT	NOT NULL	Embedding model used
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Row Count: 0 (unpopulated)

entries_embeddings

Vector embeddings for entries semantic search.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
entry_id	INTEGER	FK entries
embedding	BLOB	NOT NULL	Vector embedding
model	TEXT	NOT NULL	Embedding model
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Row Count: 0 (unpopulated)

session_links

Relationships between sessions.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
session_a	TEXT	NOT NULL	First session ID
session_b	TEXT	NOT NULL	Second session ID
relationship	TEXT	NOT NULL	continues, references, supersedes, fixes
notes	TEXT		Additional context
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Row Count: 635

summaries

Session summaries extracted from Claude Code.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
entry_id	INTEGER	FK entries
summary_text	TEXT	NOT NULL	Summary content
leaf_uuid	TEXT	UNIQUE	UUID reference

FTS: summaries_fts Row Count: 675

message_threading

Message threading relationships.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
entry_id	INTEGER	FK entries
uuid	TEXT		Message UUID
parent_uuid	TEXT		Parent UUID
logical_parent_uuid	TEXT		Logical parent

session_insights

Session analytics and insights.

Column	Type	Constraints	Description
session_id	TEXT	NOT NULL
insight_type	TEXT	NOT NULL	summary, topic, etc.
content	TEXT		JSON insight data

---

2. Knowledge Extraction

decisions

Extracted architectural and design decisions.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
message_id	INTEGER	FK messages	Source message
project_path	TEXT		Project context
decision_type	TEXT	NOT NULL	architecture, technology, api, database, testing, deployment, security, general
decision	TEXT	NOT NULL, UNIQUE	Decision text
rationale	TEXT		Reasoning
alternatives_considered	TEXT		Other options
confidence	REAL	DEFAULT 0.5	Confidence score (0-1)
tags	TEXT		JSON tags array
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Indexes: decision_type, project_path, created_at, confidence FTS: decisions_fts Row Count: 1,856

code_patterns

Reusable code patterns extracted from sessions.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
message_id	INTEGER	FK messages	Source message
language	TEXT	NOT NULL	Programming language
pattern_name	TEXT	NOT NULL	Pattern identifier
pattern_type	TEXT		test, async, class, error_handling, database, api, imports, general
code	TEXT	NOT NULL, UNIQUE	Code snippet
description	TEXT		Pattern description
tags	TEXT		JSON tags
usage_count	INTEGER	DEFAULT 1	Times used
last_used	TEXT	DEFAULT CURRENT_TIMESTAMP
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Indexes: language, pattern_type, pattern_name, usage_count, created_at Row Count: 10,246

error_solutions

Cached error signatures with proven solutions.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
error_hash	TEXT	UNIQUE	Error signature hash
error_type	TEXT	NOT NULL	Error, TypeError, ValueError, etc.
error_signature	TEXT	NOT NULL	Error message pattern
error_context	TEXT		Surrounding context
solution	TEXT	NOT NULL	Solution description
solution_code	TEXT		Code fix
language	TEXT		Programming language
success_count	INTEGER	DEFAULT 1	Successful uses
failure_count	INTEGER	DEFAULT 0	Failed attempts
last_used	TEXT	DEFAULT CURRENT_TIMESTAMP
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Indexes: error_type, error_hash, language FTS: errors_fts Row Count: 475

knowledge_graph

Entity relationship graph (schema ready, unpopulated).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
source_entity_id	INTEGER	FK knowledge_entities
target_entity_id	INTEGER	FK knowledge_entities
relationship_type	TEXT		Relationship type
confidence	REAL		Confidence score
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Row Count: 0

knowledge_entities

Named entities for knowledge graph (schema ready, unpopulated).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
entity_type	TEXT	NOT NULL	Entity category
name	TEXT	NOT NULL	Entity name
description	TEXT		Entity description
metadata	TEXT		JSON metadata
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Row Count: 0

---

3. Components

components

Framework component registry (H.P.001-AGENTS, H.P.002-COMMANDS, H.P.003-SKILLS, H.P.004-SCRIPTS, H.P.005-HOOKS, H.P.006-WORKFLOWS).

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Component identifier
type	TEXT	NOT NULL	agent, command, skill, script, hook, workflow, workflow-json
name	TEXT	NOT NULL	Display name
version	TEXT		Component version
status	TEXT	DEFAULT 'operational'	operational, active, draft, archived, deprecated
path	TEXT	NOT NULL	File path
category	TEXT		Category
subcategory	TEXT		Subcategory
description	TEXT		Component description
complexity	TEXT	DEFAULT 'medium'	Complexity level
maturity	TEXT	DEFAULT 'production'	Maturity level
confidence	REAL	DEFAULT 0.5	Documentation confidence
documentation_quality	TEXT	DEFAULT 'partial'	complete, comprehensive, partial, stub
content_hash	TEXT		Content hash for change detection
indexed_at	TEXT		Last indexed
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP
updated_at	TEXT	DEFAULT CURRENT_TIMESTAMP
llm_provider	TEXT		A2A: LLM provider (anthropic-claude)
llm_model	TEXT		A2A: Model (sonnet, opus, haiku)
llm_temperature	REAL	DEFAULT 0.7	A2A: Temperature
llm_max_tokens	INTEGER	DEFAULT 4096	A2A: Max tokens
tools_list	TEXT		A2A: JSON array of tools
a2a_schema	TEXT		A2A: Schema version
token_budget_recommended	INTEGER	DEFAULT 0	Recommended token budget
token_budget_maximum	INTEGER	DEFAULT 0	Maximum token budget
invocation_method	TEXT		Task, direct, etc.
parallel_safe	INTEGER	DEFAULT 1	Boolean: can run in parallel
vendor_name	TEXT		Vendor name
vendor_url	TEXT		Vendor URL

Indexes: type, category, status, llm_model, llm_provider, type_status, active FTS: component_search Row Count: 2,069

capabilities

Component capabilities and tags.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
component_id	TEXT	FK components ON DELETE CASCADE
capability	TEXT	NOT NULL	Capability description
capability_type	TEXT	NOT NULL	primary, tag, domain, action

Indexes: component_id, capability_type Row Count: 40,311

component_relationships

Inter-component relationships.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
source_id	TEXT	FK components ON DELETE CASCADE
target_id	TEXT		Target component
relationship_type	TEXT	NOT NULL	invokes, invoked_by, alternative, complement
notes	TEXT		Additional notes

Indexes: source_id, target_id Row Count: 8,873

component_frontmatter

Parsed YAML frontmatter from component files.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
component_id	TEXT	FK components
frontmatter	TEXT		Raw YAML
parsed_json	TEXT		Parsed JSON

FTS: component_frontmatter_fts Row Count: 775

component_composability

A2A orchestration mappings.

Column	Type	Description
id	INTEGER	PRIMARY KEY
orchestrator_id	TEXT	Orchestrator component
member_id	TEXT	Member component
role	TEXT	Role in composition

component_embeddings

Semantic embeddings for component search.

Column	Type	Description
id	INTEGER	PRIMARY KEY
component_id	TEXT	FK components
embedding	BLOB	Vector embedding
model	TEXT	Embedding model

component_usage_stats

Component usage tracking.

Column	Type	Description
component_id	TEXT	PRIMARY KEY
invocation_count	INTEGER	Times invoked
last_invoked	TEXT	Last invocation

component_schema_info

Schema versioning for components.

Column	Type	Description
version	TEXT	Schema version
updated_at	TEXT	Last update

---

4. Document Index

doc_index

Indexed documentation across all workspaces.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
doc_hash	TEXT	UNIQUE NOT NULL	Document hash
doc_type	TEXT	NOT NULL	markdown, claude_md, etc.
title	TEXT	NOT NULL	Document title
file_path	TEXT	NOT NULL	Relative path
absolute_path	TEXT		Full path
submodule	TEXT		Submodule name
category	TEXT		Document category
subcategory	TEXT		Subcategory
tags	TEXT		JSON tags
content_hash	TEXT		Content hash
word_count	INTEGER	DEFAULT 0	Word count
line_count	INTEGER	DEFAULT 0	Line count
has_code_blocks	BOOLEAN	DEFAULT FALSE	Contains code
has_mermaid	BOOLEAN	DEFAULT FALSE	Contains diagrams
frontmatter	TEXT		YAML frontmatter
first_heading	TEXT		First heading
summary	TEXT		Document summary
created_at	TEXT		Creation date
modified_at	TEXT		Last modified
indexed_at	TEXT	DEFAULT CURRENT_TIMESTAMP
workspace_id	TEXT		Workspace ID
workspace_name	TEXT		Workspace name

Indexes: workspace_id, workspace_name, doc_type, category, submodule, modified_at, indexed_at FTS: doc_search Row Count: 4,345

doc_embeddings

Document semantic embeddings.

Column	Type	Description
id	INTEGER	PRIMARY KEY
doc_id	INTEGER	FK doc_index
embedding	BLOB	Vector embedding
model	TEXT	Embedding model

workspace_registry

Registered workspace H.P.009-CONFIGurations.

Column	Type	Constraints	Description
workspace_id	TEXT	PRIMARY KEY	Unique workspace ID
name	TEXT	NOT NULL	Workspace name
workspace_type	TEXT		master, submodule, standalone
root_path	TEXT		Root directory
doc_count	INTEGER	DEFAULT 0	Document count
last_indexed	TEXT		Last index time

Row Count: 3

---

5. Project Management

work_items

Project work items (epics, features, tasks, subtasks).

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY	Work item ID
type	TEXT	NOT NULL	epic, feature, task, subtask
title	TEXT	NOT NULL	Item title
description	TEXT		Description
acceptance_criteria	TEXT		Acceptance criteria
project_id	TEXT	FK projects
sub_project_id	TEXT	FK sub_projects
parent_id	TEXT	FK work_items
sprint_id	TEXT	FK sprints
status	TEXT	DEFAULT 'backlog'	backlog, planned, in_progress, blocked, review, completed, cancelled
priority	INTEGER	DEFAULT 50	Priority (0-100)
sort_order	INTEGER	DEFAULT 0	Display order
estimate_points	INTEGER		Story points
estimate_hours	REAL		Hour estimate
actual_hours	REAL		Actual hours
assignee	TEXT		Assigned to
labels	TEXT		JSON labels
depends_on	TEXT		JSON dependency IDs
blocks	TEXT		JSON blocked IDs
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP
updated_at	TEXT	DEFAULT CURRENT_TIMESTAMP
started_at	TEXT		Start time
completed_at	TEXT		Completion time
due_date	TEXT		Due date
source_file	TEXT		Source file
source_line	INTEGER		Source line

Indexes: type, parent_id, project_id, sub_project_id, sprint_id, status, assignee, due_date Row Count: 1,361

task_tracking

TodoWrite task status tracking (ADR-053).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
task_id	TEXT	NOT NULL	Task identifier
task_description	TEXT		Task description
source	TEXT	DEFAULT 'extraction'	Source system
status	TEXT	DEFAULT 'unknown'	pending, in_progress, completed
started_at	TEXT		Start time
completed_at	TEXT		Completion time
message_id_start	INTEGER		First message ID
message_id_end	INTEGER		Last message ID
session_export_file	TEXT		Export file
outcome	TEXT		Task outcome
outcome_score	REAL		Outcome score
tool_success_count	INTEGER	DEFAULT 0	Successful tool calls
tool_error_count	INTEGER	DEFAULT 0	Failed tool calls
user_corrections	INTEGER	DEFAULT 0	User corrections
created_at	TEXT	DEFAULT datetime('now')
updated_at	TEXT	DEFAULT datetime('now')

Indexes: task_id, status Row Count: 713

projects

Top-level project definitions.

Column	Type	Description
id	TEXT	PRIMARY KEY
name	TEXT	Project name
description	TEXT	Description
status	TEXT	Project status

sub_projects

Sub-project definitions.

Column	Type	Description
id	TEXT	PRIMARY KEY
project_id	TEXT	FK projects
name	TEXT	Name
path	TEXT	Path

sprints

Sprint definitions.

Column	Type	Description
id	TEXT	PRIMARY KEY
name	TEXT	Sprint name
start_date	TEXT	Start date
end_date	TEXT	End date
status	TEXT	Status
goal	TEXT	Sprint goal

task_messages

Task-to-message relationships.

Column	Type	Description
id	INTEGER	PRIMARY KEY
task_id	TEXT	FK task_tracking
message_id	INTEGER	FK messages

---

6. Learning System

learning_modules

Available learning modules.

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
module_key	TEXT	UNIQUE NOT NULL	Module identifier
module_type	TEXT	NOT NULL	Module type
category	TEXT	NOT NULL	Category
title	TEXT	NOT NULL	Display title
description	TEXT		Description
difficulty_level	TEXT		beginner, intermediate, advanced
estimated_duration_minutes	INTEGER		Time estimate
prerequisite_modules	TEXT		JSON prerequisites
learning_objectives	TEXT		JSON objectives
content_path	TEXT		Content file path
has_quiz	BOOLEAN	DEFAULT 0	Has quiz
has_exercise	BOOLEAN	DEFAULT 0	Has exercise
badge_id	TEXT		Associated badge
sort_order	INTEGER	DEFAULT 0	Display order
is_active	BOOLEAN	DEFAULT 1	Is active
created_at	TEXT	DEFAULT CURRENT_TIMESTAMP
updated_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Indexes: module_type, category, difficulty_level, sort_order Row Count: 11

learning_users

User learning profiles (schema ready).

Column	Type	Description
id	INTEGER	PRIMARY KEY
user_id	TEXT	User identifier
created_at	TEXT	Registration date

Row Count: 0

learning_progress

User progress per module.

Column	Type	Description
id	INTEGER	PRIMARY KEY
user_id	TEXT	FK learning_users
module_key	TEXT	FK learning_modules
status	TEXT	not_started, in_progress, completed
progress_percent	REAL	Completion percentage
started_at	TEXT	Start time
completed_at	TEXT	Completion time

Row Count: 0

learning_badges

Available badges.

Column	Type	Description
id	TEXT	PRIMARY KEY
name	TEXT	Badge name
description	TEXT	Description
icon	TEXT	Icon identifier

learning_achievements

User earned badges.

Column	Type	Description
id	INTEGER	PRIMARY KEY
user_id	TEXT	FK learning_users
badge_id	TEXT	FK learning_badges
earned_at	TEXT	Earned timestamp

learning_analytics_events

Learning event log.

Column	Type	Description
id	INTEGER	PRIMARY KEY
user_id	TEXT	User identifier
event_type	TEXT	Event type
event_data	TEXT	JSON event data
timestamp	TEXT	Event time

skill_learnings

Skill effectiveness tracking (Continual Learning System).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
session_id	TEXT	NOT NULL	Session ID
skill_name	TEXT	NOT NULL	Skill identifier
outcome	TEXT		Outcome description
effectiveness_score	INTEGER		Score (0-100)
errors	TEXT		JSON errors
analyzed_at	TEXT	NOT NULL	Analysis timestamp

Unique: (session_id, skill_name) Row Count: 158,337

skill_improvements

Tracked skill improvements.

Column	Type	Description
id	INTEGER	PRIMARY KEY
skill_name	TEXT	Skill identifier
improvement	TEXT	Improvement description
created_at	TEXT	Timestamp

---

7. Token Usage

token_usage

API call token tracking (ADR-005).

Column	Type	Constraints	Description
id	INTEGER	PRIMARY KEY AUTOINCREMENT
message_id	INTEGER	FK messages	Source message
session_id	TEXT	NOT NULL	Session ID
project_path	TEXT		Project path
input_tokens	INTEGER	DEFAULT 0	Fresh input tokens
output_tokens	INTEGER	DEFAULT 0	Output tokens
cache_read_input_tokens	INTEGER	DEFAULT 0	Cached tokens read
cache_creation_input_tokens	INTEGER	DEFAULT 0	Tokens cached
cost_usd	REAL	DEFAULT 0.0	Actual cost
estimated_cost_usd	REAL		Estimated cost
duration_ms	INTEGER	DEFAULT 0	API latency
model	TEXT		Model used
timestamp	TEXT	NOT NULL	Call timestamp
indexed_at	TEXT	DEFAULT CURRENT_TIMESTAMP

Indexes: session_id, project_path, timestamp, model Row Count: 568

---

8. Views

Message & Session Views

View	Description
session_activity	Message counts and duration per session
sessions_by_topic	Sessions grouped by topic
today_activity	Activity from last 24 hours

Knowledge Views

View	Description
knowledge_stats	Row counts for all knowledge tables
recent_decisions	High-confidence decisions (7 days)
patterns_by_language	Code patterns by language
error_frequency	Error types with occurrence counts

Component Views

View	Description
component_summary	Components by type and status
docs_by_category	Documents by category

Project Views

View	Description
project_progress	Project completion metrics
sub_project_progress	Sub-project completion
epic_progress	Epic completion metrics
sprint_burndown	Sprint burndown data
work_item_progress	Work item completion
work_item_children_stats	Child item statistics

Token Usage Views

View	Description
token_usage_by_session	Tokens per session
token_usage_by_project	Tokens per project
token_usage_by_date	Tokens per day
token_usage_by_model	Tokens per model

---

9. FTS5 Full-Text Search Tables

FTS Table	Source	Searchable Columns
messages_fts	messages	content, role
entries_fts	entries	content, entry_type
decisions_fts	decisions	decision, rationale
errors_fts	error_solutions	error_signature, solution
summaries_fts	summaries	summary_text
doc_search	doc_index	title, summary, tags
component_search	components	name, description
component_frontmatter_fts	component_frontmatter	frontmatter

Each FTS table has supporting tables: *_H.P.009-CONFIG, *_data, *_docsize, *_idx

---

10. System Tables

Table	Description
_litestream_lock	Litestream replication lock
_litestream_seq	Litestream sequence tracking
sqlite_sequence	Auto-increment tracking
sqlite_stat1	Query optimizer statistics

---

11. Entity Relationship Diagram

┌─────────────────────────────────────────────────────────────────────┐
│                    CODITECT Context Database                         │
├─────────────────────────────────────────────────────────────────────┤
│                                                                      │
│  CONVERSATION LAYER                                                  │
│  ┌───────────┐    ┌───────────┐    ┌───────────┐    ┌───────────┐  │
│  │ entries   │───▶│ summaries │    │ messages  │    │token_usage│  │
│  │ 247,792   │    │    675    │    │  30,609   │    │    568    │  │
│  └───────────┘    └───────────┘    └───────────┘    └───────────┘  │
│       │                                   │                          │
│       ▼                                   ▼                          │
│  KNOWLEDGE LAYER                                                     │
│  ┌───────────┐    ┌───────────┐    ┌───────────┐    ┌───────────┐  │
│  │ decisions │    │   code_   │    │  error_   │    │ knowledge_│  │
│  │   1,856   │    │ patterns  │    │ solutions │    │   graph   │  │
│  └───────────┘    │  10,246   │    │    475    │    │    (0)    │  │
│                   └───────────┘    └───────────┘    └───────────┘  │
│                                                                      │
│  COMPONENT LAYER                                                     │
│  ┌───────────┐    ┌───────────┐    ┌───────────┐    ┌───────────┐  │
│  │components │───▶│capabilities│   │component_ │    │component_ │  │
│  │   2,069   │    │  40,311   │    │relationships│  │frontmatter│  │
│  └───────────┘    └───────────┘    │   8,873   │    │    775    │  │
│                                    └───────────┘    └───────────┘  │
│                                                                      │
│  DOCUMENT LAYER                                                      │
│  ┌───────────┐    ┌───────────┐                                     │
│  │ doc_index │    │workspace_ │                                     │
│  │   4,345   │    │ registry  │                                     │
│  └───────────┘    │     3     │                                     │
│                   └───────────┘                                     │
│                                                                      │
│  PROJECT LAYER                                                       │
│  ┌───────────┐    ┌───────────┐    ┌───────────┐    ┌───────────┐  │
│  │work_items │    │  sprints  │    │ projects  │    │  task_    │  │
│  │   1,361   │    │    (N)    │    │    (N)    │    │ tracking  │  │
│  └───────────┘    └───────────┘    └───────────┘    │    713    │  │
│                                                      └───────────┘  │
│                                                                      │
│  LEARNING LAYER                                                      │
│  ┌───────────┐    ┌───────────┐    ┌───────────┐                    │
│  │ learning_ │    │  skill_   │    │ learning_ │                    │
│  │  modules  │    │ learnings │    │  progress │                    │
│  │    11     │    │  158,337  │    │    (0)    │                    │
│  └───────────┘    └───────────┘    └───────────┘                    │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

---

12. Common Queries

Search Messages

SELECT * FROM messages
WHERE id IN (SELECT rowid FROM messages_fts WHERE messages_fts MATCH 'search term');

Find Components

SELECT id, name, type, description
FROM components
WHERE type = 'agent' AND status = 'operational';

Get Recent Decisions

SELECT * FROM recent_decisions LIMIT 10;

Token Usage Summary

SELECT * FROM token_usage_by_model;

Project Progress

SELECT * FROM project_progress;

---

13. Maintenance

Backup

./H.P.004-SCRIPTS/backup-context-db.sh

Optimize

VACUUM;
ANALYZE;

Rebuild FTS Index

INSERT INTO messages_fts(messages_fts) VALUES('rebuild');

Check Integrity

PRAGMA integrity_check;

---

Generated: 2026-01-06 Schema Version: 2.0.0 Related ADRs: ADR-020 (Context Extraction), ADR-021 (Context Query), ADR-025 (Comprehensive Entry Schema), ADR-053 (Cloud Sync)