CODITECT Database Purpose Matrix

Governance: ADR-118

Purpose: Quick reference to find the right database and table for your data need.

Task: J.24.5

---

Quick Reference: What Do You Need?

I need to store/query...

What I Need	Database	Table(s)	Example Query
Session messages	sessions.db	messages	SELECT * FROM messages WHERE session_id = ?
Search messages	sessions.db	messages_fts	SELECT * FROM messages_fts WHERE messages_fts MATCH ?
Architecture decisions	org.db	decisions	SELECT * FROM decisions WHERE status = 'accepted'
Skill learnings	org.db	skill_learnings	SELECT * FROM skill_learnings WHERE skill_name = ?
Error solutions	org.db	error_solutions	SELECT * FROM error_solutions WHERE error_pattern LIKE ?
Task tracking	sessions.db	task_tracking	SELECT * FROM task_tracking WHERE status = 'in_progress'
Tool usage stats	sessions.db	tool_analytics	SELECT tool_name, COUNT(*) FROM tool_analytics GROUP BY tool_name
Component registry	platform.db	components	SELECT * FROM components WHERE type = 'agent'
Component capabilities	platform.db	capabilities	SELECT * FROM capabilities WHERE component_id = ?
Knowledge graph nodes	org.db	kg_nodes	SELECT * FROM kg_nodes WHERE node_type = 'decision'
Knowledge graph edges	org.db	kg_edges	SELECT * FROM kg_edges WHERE edge_type = 'references'
Project metadata	projects.db	projects	SELECT * FROM projects WHERE project_id = ?
File embeddings	projects.db	project_embeddings	SELECT * FROM project_embeddings WHERE file_path LIKE ?
Session checkpoints	sessions.db	checkpoints	SELECT * FROM checkpoints WHERE session_id = ?
Token usage	sessions.db	token_economics	SELECT SUM(input_tokens) FROM token_economics WHERE session_id = ?
Offline sync queue	sessions.db	sync_queue	SELECT * FROM sync_queue WHERE retry_count < 3

---

Database Tier Quick Reference

Tier	Database	Purpose	Backup Priority
1	platform.db	Component metadata	Low (regenerable)
2	org.db	CRITICAL: Decisions, learnings	High (irreplaceable)
3	sessions.db	Session data, analytics	Medium (regenerable)
4	projects.db	Project-specific data	Medium

Location: ~/.coditect-data/context-storage/

---

Common Use Case Examples

Example 1: Find All Decisions About Authentication

import sqlite3
from pathlib import Path

ORG_DB = Path.home() / "PROJECTS/.coditect-data/context-storage/org.db"

conn = sqlite3.connect(ORG_DB)
conn.row_factory = sqlite3.Row

decisions = conn.execute("""
    SELECT decision_id, title, status, created_at
    FROM decisions
    WHERE title LIKE '%auth%' OR rationale LIKE '%auth%'
    ORDER BY created_at DESC
""").fetchall()

for d in decisions:
    print(f"{d['decision_id']}: {d['title']} ({d['status']})")

conn.close()

Example 2: Get Recent Session Messages

import sqlite3
from pathlib import Path

SESSIONS_DB = Path.home() / "PROJECTS/.coditect-data/context-storage/sessions.db"

conn = sqlite3.connect(SESSIONS_DB)
conn.row_factory = sqlite3.Row

messages = conn.execute("""
    SELECT role, content, timestamp
    FROM messages
    WHERE timestamp > datetime('now', '-1 day')
    ORDER BY timestamp DESC
    LIMIT 20
""").fetchall()

for m in messages:
    print(f"[{m['role']}] {m['content'][:100]}...")

conn.close()

Example 3: Search for Error Solutions

import sqlite3
from pathlib import Path

ORG_DB = Path.home() / "PROJECTS/.coditect-data/context-storage/org.db"

conn = sqlite3.connect(ORG_DB)
conn.row_factory = sqlite3.Row

error_type = "TypeError"
solutions = conn.execute("""
    SELECT error_pattern, solution, success_count
    FROM error_solutions
    WHERE error_pattern LIKE ?
    ORDER BY success_count DESC
    LIMIT 5
""", (f"%{error_type}%",)).fetchall()

for s in solutions:
    print(f"Pattern: {s['error_pattern']}")
    print(f"Solution: {s['solution']}")
    print(f"Success count: {s['success_count']}")
    print("---")

conn.close()

Example 4: Track Task Progress

import sqlite3
from pathlib import Path

SESSIONS_DB = Path.home() / "PROJECTS/.coditect-data/context-storage/sessions.db"

conn = sqlite3.connect(SESSIONS_DB)
conn.row_factory = sqlite3.Row

# Get task status summary
summary = conn.execute("""
    SELECT status, COUNT(*) as count
    FROM task_tracking
    WHERE track_id = 'J'
    GROUP BY status
""").fetchall()

print("Track J Task Status:")
for s in summary:
    print(f"  {s['status']}: {s['count']}")

conn.close()

Example 5: Find Components by Capability

import sqlite3
from pathlib import Path

PLATFORM_DB = Path.home() / "PROJECTS/.coditect-data/context-storage/platform.db"

conn = sqlite3.connect(PLATFORM_DB)
conn.row_factory = sqlite3.Row

# Find all agents with 'documentation' capability
agents = conn.execute("""
    SELECT c.name, c.description, GROUP_CONCAT(cap.capability, ', ') as capabilities
    FROM components c
    JOIN capabilities cap ON c.id = cap.component_id
    WHERE c.type = 'agent'
    AND c.id IN (
        SELECT component_id FROM capabilities
        WHERE capability LIKE '%documentation%'
    )
    GROUP BY c.id
""").fetchall()

for a in agents:
    print(f"{a['name']}: {a['description']}")
    print(f"  Capabilities: {a['capabilities']}")

conn.close()

Example 6: Query Knowledge Graph

import sqlite3
from pathlib import Path

ORG_DB = Path.home() / "PROJECTS/.coditect-data/context-storage/org.db"

conn = sqlite3.connect(ORG_DB)
conn.row_factory = sqlite3.Row

# Find all nodes related to a decision
decision_content_hash = "abc123..."
related = conn.execute("""
    SELECT n2.node_type, n2.content, e.edge_type
    FROM kg_nodes n1
    JOIN kg_edges e ON n1.id = e.from_node
    JOIN kg_nodes n2 ON e.to_node = n2.id
    WHERE n1.content_hash = ?
""", (decision_content_hash,)).fetchall()

for r in related:
    print(f"[{r['edge_type']}] {r['node_type']}: {r['content'][:50]}...")

conn.close()

Example 7: Get Token Usage Statistics

import sqlite3
from pathlib import Path

SESSIONS_DB = Path.home() / "PROJECTS/.coditect-data/context-storage/sessions.db"

conn = sqlite3.connect(SESSIONS_DB)
conn.row_factory = sqlite3.Row

# Token usage by day
usage = conn.execute("""
    SELECT
        DATE(created_at) as date,
        SUM(input_tokens) as total_input,
        SUM(output_tokens) as total_output,
        SUM(cost_usd) as total_cost
    FROM token_economics
    WHERE created_at > datetime('now', '-7 days')
    GROUP BY DATE(created_at)
    ORDER BY date DESC
""").fetchall()

print("Token Usage (Last 7 Days):")
for u in usage:
    print(f"  {u['date']}: {u['total_input']:,} in / {u['total_output']:,} out (${u['total_cost']:.2f})")

conn.close()

---

Decision Tree: Which Database?

What type of data?
├── Component metadata (agents, skills, commands)?
│   └── platform.db (Tier 1)
│
├── Irreplaceable knowledge?
│   ├── Architecture decisions → org.db:decisions
│   ├── Accumulated learnings → org.db:skill_learnings
│   ├── Error solutions → org.db:error_solutions
│   └── Knowledge graph → org.db:kg_nodes, kg_edges
│
├── Session/conversation data?
│   ├── Messages → sessions.db:messages
│   ├── Task tracking → sessions.db:task_tracking
│   ├── Tool analytics → sessions.db:tool_analytics
│   └── Checkpoints → sessions.db:checkpoints
│
└── Project-specific data?
    ├── Project metadata → projects.db:projects
    ├── File embeddings → projects.db:project_embeddings
    └── Exclude patterns → projects.db:exclude_patterns

---

Related Documentation

• DATA-DICTIONARY.md - Complete field-level documentation
• ER-DIAGRAMS.md - Entity relationship diagrams
• CLOUD-SYNC-DOCUMENTATION.md - Cloud sync details
• DATABASE-SCHEMA.md - Schema overview
• ADR-118 - Four-tier architecture

---

Generated: 2026-02-05 Task: J.24.5.1-J.24.5.2