Database Documentation - AI Agent Context

Purpose

This folder contains schema documentation for CODITECT databases. ADR-118 establishes a four-tier architecture:

Tier	Database	Purpose	Populated By
1	platform.db	Component data (agents, skills, commands)	component-indexer.py
2	org.db	Decisions, skill_learnings (CRITICAL)	/cx command
3	sessions.db	Messages, token_usage	/cx command
4	project.db	Project-specific	Project setup

Use this documentation when working with:

• Database queries and migrations
• Knowledge extraction scripts
• Session analysis tools
• Context and component indexing operations

Specification

Configuration Options

Option	Type	Default	Description
option1	string	"default"	First option
option2	int	10	Second option
option3	bool	true	Third option

API Reference

Endpoint Overview

Method	Endpoint	Description
GET	/api/v1/resource	List resources
POST	/api/v1/resource	Create resource
PUT	/api/v1/resource/:id	Update resource
DELETE	/api/v1/resource/:id	Delete resource

Key Files

File	Use Case
DATABASE-SCHEMA.md	Human-readable complete schema reference
DATABASE-SCHEMA.json	Programmatic schema access for tools/scripts

Common Operations

Query the Database (ADR-118)

# Direct SQLite - Tier 3 (messages)
sqlite3 context-storage/sessions.db "YOUR QUERY"

# Direct SQLite - Tier 2 (decisions)
sqlite3 context-storage/org.db "YOUR QUERY"

# Via /cxq command (auto-routes)
/cxq "search term"
/cxq --recent 100
/cxq --decisions --limit 20

Schema Inspection

# List all tables (Tier 3)
sqlite3 context-storage/sessions.db ".tables"

# Get table schema
sqlite3 context-storage/sessions.db ".schema messages"

# Table row counts
sqlite3 context-storage/sessions.db "SELECT name, (SELECT COUNT(*) FROM [name]) FROM sqlite_master WHERE type='table'"

Full-Text Search

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

-- Search decisions
SELECT * FROM decisions
WHERE id IN (SELECT rowid FROM decisions_fts WHERE decisions_fts MATCH 'architecture');

Table Categories

Core Tables (Start Here)

• messages - All extracted session messages
• decisions - Extracted architectural decisions
• code_patterns - Reusable code patterns
• error_solutions - Known error/solution pairs
• components - Framework component registry
• doc_index - Indexed documentation

Support Tables

• *_fts tables - Full-text search indexes
• *_embeddings tables - Vector embeddings for semantic search
• Views (*_progress, *_stats) - Aggregated data

Important Notes

1. Never modify the database directly during active sessions
2. Backup before migrations: ./scripts/backup-context-db.sh
3. FTS tables are content-synced - don't insert directly
4. Use transactions for bulk operations

Related Documentation

• Memory Management Guide
• Context Extraction Commands
• Query Commands

---

Database Locations (ADR-118): context-storage/{platform,org,sessions}.db Last Updated: 2026-01-26