CODITECT Database ER Diagrams
Purpose: Visual entity-relationship diagrams for CODITECT's four-tier database architecture. For field-level documentation, see DATA-DICTIONARY.md.
Task: J.24.3
Quick Reference
| Database | Tier | Tables | Primary Entities |
|---|---|---|---|
| platform.db | 1 | 15 | Components, Capabilities, Triggers |
| org.db | 2 | 28 | Decisions, Skill Learnings, Knowledge Graph |
| sessions.db | 3 | 96 | Messages, Sessions, Tool Analytics |
| projects.db | 4 | 12 | Projects, Content Hashes, Embeddings |
| messaging.db | - | 9 | Session Registry, Messages, Locks, Claims |
Tier 1: platform.db ER Diagram
Purpose: Component metadata index (agents, skills, commands, scripts)
Key Relationships:
| From | To | Type | Description |
|---|---|---|---|
capabilities | components | Many-to-One | Component capability tags |
triggers | components | Many-to-One | Usage triggers for activation |
component_relationships | components | Many-to-One | Component dependencies |
component_usage_stats | components | One-to-One | Usage analytics |
Tier 2: org.db ER Diagram
Purpose: Organizational knowledge (decisions, learnings, error solutions) - CRITICAL
Key Relationships:
| From | To | Type | Description |
|---|---|---|---|
kg_edges | kg_nodes (from) | Many-to-One | Edge source node |
kg_edges | kg_nodes (to) | Many-to-One | Edge target node |
project_tracks | projects | Many-to-One | Track assignments |
decisions | messages | Many-to-One | Source message reference |
Tier 3: sessions.db ER Diagram
Purpose: Session data (messages, tool analytics, activity associations)
Core Session Entities
Task Tracking Entities
Knowledge Graph Entities
Component & Analytics Entities
Key Relationships Summary:
| From | To | Type | Description |
|---|---|---|---|
messages | sessions | Many-to-One | Session containment |
task_messages | messages | Many-to-One | Task-message link |
task_messages | task_tracking | Many-to-One | Task association |
checkpoints | task_tracking | Many-to-One | Task checkpoints |
auto_checkpoints | sessions | Many-to-One | Session checkpoints |
context_graph_nodes | context_graphs | Many-to-One | Graph membership |
call_graph_edges | call_graph_functions | Many-to-One | Caller function |
call_graph_memory | messages | Many-to-One | Message context |
Tier 4: projects.db ER Diagram
Purpose: Project-specific data (project index, embeddings)
Key Relationships:
| From | To | Type | Description |
|---|---|---|---|
project_embeddings | projects | Many-to-One | Project file embeddings |
exclude_patterns | projects | Many-to-One | Exclusion rules |
sub_projects | projects | Many-to-One | Sub-project hierarchy |
sprints | projects | Many-to-One | Sprint planning |
Inter-Session: messaging.db ER Diagram
Purpose: Inter-session coordination, file locking, task claims (ADR-160, ADR-173)
Key Relationships:
| From | To | Type | Description |
|---|---|---|---|
file_locks | session_registry | Many-to-One | Session holds file locks |
task_claims | session_registry | Many-to-One | Session claims tasks |
session_messages | session_registry | Many-to-One | Sender session |
session_messages | session_messages | Self-referential | Reply threading |
message_dedup | session_messages | Many-to-One | Dedup tracking |
Cross-Database Relationships
While SQLite databases don't enforce cross-database foreign keys, CODITECT maintains logical relationships:
Logical Cross-Database Links
| Source | Target | Link Field | Description |
|---|---|---|---|
org.decisions | sessions.messages | message_id | Decision source |
sessions.messages | projects.projects | project_id | Project context |
sessions.sessions | projects.projects | project_id | Session project |
sessions.tool_analytics | platform.components | component_name | Tool reference |
org.kg_nodes | sessions.messages | derived | Knowledge extraction |
Diagram Legend
| Symbol | Meaning |
|---|---|
PK | Primary Key |
FK | Foreign Key |
UK | Unique Key |
| ` | |
| ` | |
}o--o{ | Many-to-Many relationship |
Related Documentation
- DATA-DICTIONARY.md - Field-level table documentation
- DATABASE-SCHEMA.md - High-level schema overview
- ADR-118 - Four-tier architecture
- ADR-148 - Documentation standard
Generated: 2026-02-05 Updated: 2026-02-19 Task: J.24.3.1-J.24.3.4, J.21.2.1