CODITECT Data Dictionary
Purpose: Field-level documentation for all tables in CODITECT's four-tier database architecture. For high-level architecture overview, see DATABASE-SCHEMA.md.
Auto-generated by:
scripts/generate-data-dictionary.py(J.24.1.3)
Quick Reference
| Database | Tier | Tables | Backup | Documentation |
|---|---|---|---|---|
| platform.db | 1 | 12 | Regenerable | Jump |
| org.db | 2 | 15 | DAILY | Jump |
| sessions.db | 3 | 82 | Regenerable | Jump |
| projects.db | 4 | 11 | With code | Jump |
Total: 120 tables across 4 databases
Legend
| Symbol | Meaning |
|---|---|
| PK | Primary Key |
| FK | Foreign Key |
| UQ | Unique Constraint |
| NN | NOT NULL |
| FTS | Full-Text Search Index |
Tier 1: platform.db
Purpose: Component metadata index (agents, skills, commands, scripts)
Location: ~/.coditect-data/context-storage/platform.db
Backup: Regenerable
Tables: 12
Table: capabilities
Purpose: Component capability tags for semantic search
Related Tables:
components(FK: component_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
component_id | TEXT | FK→components | - | - |
capability | TEXT | NN | - | - |
capability_type | TEXT | NN | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_capabilities_type | capability_type | - |
idx_capabilities_component | component_id | - |
Table: component_composability
Purpose: Table in platform.db
Related Tables:
components(FK: component_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
component_id | TEXT | FK→components | - | - |
target_id | TEXT | - | - | - |
relationship | TEXT | NN | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_composability_target | target_id | - |
idx_composability_component | component_id | - |
Table: component_relationships
Purpose: Dependencies between components
Related Tables:
components(FK: source_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
source_id | TEXT | FK→components | - | - |
target_id | TEXT | - | - | - |
relationship_type | TEXT | NN | - | - |
notes | TEXT | - | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_comp_rel_target | target_id | - |
idx_comp_rel_source | source_id | - |
Table: component_search
Purpose: FTS5 full-text search index for components
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | - | - | - |
name | TEXT | - | - | - |
type | TEXT | - | - | - |
description | TEXT | - | - | - |
capabilities | TEXT | - | - | - |
triggers | TEXT | - | - | - |
Table: component_search_config
Purpose: Table in platform.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
k | TEXT | PK, NN | - | - |
v | TEXT | - | - | - |
Table: component_search_content
Purpose: Table in platform.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
c0 | TEXT | - | - | - |
c1 | TEXT | - | - | - |
c2 | TEXT | - | - | - |
c3 | TEXT | - | - | - |
c4 | TEXT | - | - | - |
c5 | TEXT | - | - | - |
Table: component_search_data
Purpose: Table in platform.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
block | BLOB | - | - | - |
Table: component_search_docsize
Purpose: Table in platform.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
sz | BLOB | - | - | - |
Table: component_search_idx
Purpose: Table in platform.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
segid | TEXT | PK, NN | - | - |
term | TEXT | PK, NN | - | - |
pgno | TEXT | - | - | - |
Table: component_usage_stats
Purpose: Table in platform.db
Related Tables:
components(FK: component_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
component_id | TEXT | FK→components | - | - |
invocation_count | INTEGER | - | 0 | - |
success_count | INTEGER | - | 0 | - |
failure_count | INTEGER | - | 0 | - |
avg_duration_ms | INTEGER | - | - | - |
last_used | TEXT | - | - | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: components
Purpose: Master registry of all CODITECT framework components
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
type | TEXT | NN | - | - |
name | TEXT | NN | - | - |
version | TEXT | - | - | - |
status | TEXT | - | 'operational' | - |
path | TEXT | NN | - | - |
category | TEXT | - | - | - |
subcategory | TEXT | - | - | - |
description | TEXT | - | - | - |
complexity | TEXT | - | 'medium' | - |
maturity | TEXT | - | 'production' | - |
confidence | REAL | - | 0.5 | - |
documentation_quality | TEXT | - | 'partial' | - |
content_hash | TEXT | - | - | - |
indexed_at | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
llm_provider | TEXT | - | - | - |
llm_model | TEXT | - | - | - |
llm_temperature | REAL | - | 0.7 | - |
llm_max_tokens | INTEGER | - | 4096 | - |
tools_list | TEXT | - | - | - |
a2a_schema | TEXT | - | - | - |
token_budget_recommended | INTEGER | - | 0 | - |
token_budget_maximum | INTEGER | - | 0 | - |
invocation_method | TEXT | - | - | - |
parallel_safe | INTEGER | - | 1 | - |
vendor_name | TEXT | - | - | - |
vendor_url | TEXT | - | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_components_llm_provider | llm_provider | - |
idx_components_llm_model | llm_model | - |
idx_components_status | status | - |
idx_components_category | category | - |
idx_components_type | type | - |
Table: triggers
Purpose: Usage triggers that activate components
Related Tables:
components(FK: component_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
component_id | TEXT | FK→components | - | - |
trigger_type | TEXT | NN | - | - |
description | TEXT | NN | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_triggers_component | component_id | - |
Tier 2: org.db (CRITICAL)
Purpose: Organizational knowledge (decisions, learnings, error solutions)
Location: ~/.coditect-data/context-storage/org.db
Backup: DAILY (CRITICAL)
Tables: 15
Table: cloud_sync_state
Purpose: Table in org.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
table_name | TEXT | NN | - | - |
last_sync_cursor | TEXT | - | - | - |
last_push_at | TEXT | - | - | - |
last_pull_at | TEXT | - | - | - |
pending_push_count | INTEGER | - | 0 | - |
conflict_count | INTEGER | - | 0 | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: decisions
Purpose: Architectural and design decisions with rationale
Related Tables:
messages(FK: message_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
message_id | INTEGER | FK→messages | - | - |
project_path | TEXT | - | - | - |
decision_type | TEXT | NN | - | - |
decision | TEXT | NN | - | - |
rationale | TEXT | - | - | - |
alternatives_considered | TEXT | - | - | - |
confidence | REAL | - | 0.5 | - |
tags | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
scope | TEXT | - | 'global' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_decisions_project_id | project_id | - |
idx_decisions_scope | scope | - |
idx_decisions_cloud_id | cloud_id | - |
idx_decisions_sync | sync_status, synced_at | - |
idx_decisions_project | tenant_id, project_id | - |
idx_decisions_user | tenant_id, user_id | - |
idx_decisions_tenant | tenant_id | - |
Table: error_solutions
Purpose: Known error patterns and their solutions
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
error_hash | TEXT | - | - | - |
error_type | TEXT | NN | - | - |
error_signature | TEXT | NN | - | - |
error_context | TEXT | - | - | - |
solution | TEXT | NN | - | - |
solution_code | TEXT | - | - | - |
language | TEXT | - | - | - |
success_count | INTEGER | - | 1 | - |
failure_count | INTEGER | - | 0 | - |
last_used | TEXT | - | CURRENT_TIMESTAMP | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
scope | TEXT | - | 'global' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_error_solutions_project_id | project_id | - |
idx_error_solutions_scope | scope | - |
idx_error_solutions_cloud_id | cloud_id | - |
idx_error_solutions_sync | sync_status, synced_at | - |
idx_error_solutions_project | tenant_id, project_id | - |
idx_error_solutions_user | tenant_id, user_id | - |
idx_error_solutions_tenant | tenant_id | - |
Table: kg_edges
Purpose: Knowledge graph edges (ADR-151)
Related Tables:
kg_nodes(FK: to_node → id)kg_nodes(FK: from_node → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
edge_type | TEXT | NN | - | - |
from_node | TEXT | NN, FK→kg_nodes | - | - |
to_node | TEXT | NN, FK→kg_nodes | - | - |
properties | TEXT | - | - | - |
tenant_id | TEXT | - | - | - |
created_at | TEXT | NN | strftime('%Y-%m-%dT%H:%M:%SZ', 'now') | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_kg_edges_pair | from_node, to_node | - |
idx_kg_edges_tenant | tenant_id | - |
idx_kg_edges_to | to_node | - |
idx_kg_edges_from | from_node | - |
idx_kg_edges_type | edge_type | - |
Table: kg_nodes
Purpose: Knowledge graph nodes (ADR-151)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
node_type | TEXT | NN | - | - |
subtype | TEXT | - | - | - |
name | TEXT | NN | - | - |
properties | TEXT | - | - | - |
embedding | BLOB | - | - | - |
tenant_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
created_at | TEXT | NN | strftime('%Y-%m-%dT%H:%M:%SZ', 'now') | - |
updated_at | TEXT | NN | strftime('%Y-%m-%dT%H:%M:%SZ', 'now') | - |
source_table | TEXT | - | - | - |
source_id | TEXT | - | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_kg_nodes_updated | updated_at | - |
idx_kg_nodes_source | source_table, source_id | - |
idx_kg_nodes_project | tenant_id, project_id | - |
idx_kg_nodes_tenant | tenant_id | - |
idx_kg_nodes_subtype | subtype | - |
idx_kg_nodes_type | node_type | - |
Table: kg_nodes_fts
Purpose: FTS5 full-text search index for kg_nodes
Type: FTS5 Virtual Table
Indexed Columns
nameproperties
Table: kg_nodes_fts_config
Purpose: FTS5 full-text search index for kg_nodes_config
Type: FTS5 Virtual Table
Indexed Columns
kv
Table: kg_nodes_fts_data
Purpose: FTS5 full-text search index for kg_nodes
Type: FTS5 Virtual Table
Indexed Columns
idblock
Table: kg_nodes_fts_docsize
Purpose: FTS5 full-text search index for kg_nodes_docsize
Type: FTS5 Virtual Table
Indexed Columns
idsz
Table: kg_nodes_fts_idx
Purpose: FTS5 full-text search index for kg_nodes_idx
Type: FTS5 Virtual Table
Indexed Columns
segidtermpgno
Table: local_identity
Purpose: Table in org.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
tenant_id | TEXT | NN | - | - |
user_id | TEXT | NN | - | - |
team_id | TEXT | - | - | - |
default_project_id | TEXT | - | - | - |
auth_source | TEXT | - | 'local' | - |
token_expires_at | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: project_tracks
Purpose: Table in org.db
Related Tables:
projects(FK: project_id → project_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
project_id | TEXT | PK, NN, FK→projects | - | - |
track_letter | TEXT | PK, NN | - | - |
track_name | TEXT | NN | - | - |
track_file | TEXT | NN | - | - |
tier | INTEGER | NN | - | - |
progress_pct | REAL | - | 0.0 | - |
status | TEXT | NN | 'active' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_project_tracks_letter | track_letter | - |
idx_project_tracks_project | project_id | - |
Table: projects
Purpose: Registered project metadata
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
project_id | TEXT | PK | - | - |
slug | TEXT | NN | - | - |
name | TEXT | NN | - | - |
description | TEXT | - | - | - |
scope | TEXT | NN | - | - |
tenant_id | TEXT | - | - | - |
owner | TEXT | NN | - | - |
plan_location | TEXT | NN | - | - |
tracks_config | TEXT | - | - | - |
status | TEXT | NN | 'active' | - |
created_at | TEXT | NN | strftime('%Y-%m-%dT%H:%M:%SZ', 'now') | - |
updated_at | TEXT | NN | strftime('%Y-%m-%dT%H:%M:%SZ', 'now') | - |
metadata | TEXT | - | - | - |
cloud_synced_at | TEXT | - | - | - |
cloud_project_uuid | TEXT | - | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_projects_slug | slug | - |
idx_projects_tenant | tenant_id | - |
idx_projects_status | status | - |
idx_projects_scope | scope | - |
Table: schema_migrations
Purpose: Table in org.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
migration_name | TEXT | NN | - | - |
version | TEXT | NN | - | - |
applied_at | TEXT | NN | - | - |
status | TEXT | NN | 'applied' | - |
Table: skill_learnings
Purpose: Accumulated skill improvement learnings
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
session_id | TEXT | NN | - | - |
skill_name | TEXT | NN | - | - |
outcome | TEXT | - | - | - |
effectiveness_score | INTEGER | - | - | - |
errors | TEXT | - | - | - |
analyzed_at | TEXT | NN | - | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
scope | TEXT | - | 'global' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_skill_learnings_project_id | project_id | - |
idx_skill_learnings_scope | scope | - |
idx_skill_learnings_cloud_id | cloud_id | - |
idx_skill_learnings_sync | sync_status, synced_at | - |
idx_skill_learnings_project | tenant_id, project_id | - |
idx_skill_learnings_user | tenant_id, user_id | - |
idx_skill_learnings_tenant | tenant_id | - |
Tier 3: sessions.db
Purpose: Session data (messages, tool analytics, activity associations)
Location: ~/.coditect-data/context-storage/sessions.db
Backup: Regenerable
Tables: 82
Table: activity_feed
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | NN | - | - |
project_id | TEXT | NN | - | - |
activity_type | TEXT | NN | - | - |
title | TEXT | NN | - | - |
description | TEXT | - | - | - |
source_type | TEXT | - | - | - |
source_id | TEXT | - | - | - |
metadata | TEXT | - | - | - |
occurred_at | TEXT | NN | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
content_hash | TEXT | NN | - | - |
sync_cursor | INTEGER | - | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_activity_feed_project_id | project_id | - |
Table: agent_sessions
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
task_id | TEXT | - | - | - |
agent_type | TEXT | NN | - | - |
loop_type | TEXT | - | 'ralph' | - |
started_at | TEXT | - | datetime('now') | - |
completed_at | TEXT | - | - | - |
status | TEXT | - | 'running' | - |
total_iterations | INTEGER | - | 0 | - |
max_iterations | INTEGER | - | 100 | - |
config | TEXT | - | - | - |
final_outcome | TEXT | - | - | - |
synced | INTEGER | - | 0 | - |
Table: auto_checkpoints
Purpose: Table in sessions.db
Related Tables:
sessions(FK: session_id → session_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
session_id | TEXT | NN, FK→sessions | - | - |
trigger | TEXT | NN | - | - |
file_path | TEXT | - | - | - |
summary | TEXT | - | - | - |
message_count | INTEGER | - | 0 | - |
decision_count | INTEGER | - | 0 | - |
context_percent | REAL | - | - | - |
resume_context | TEXT | - | - | - |
created_at | TEXT | NN | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_auto_checkpoints_session | session_id, created_at | - |
Table: budgets
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
scope | TEXT | NN | - | - |
scope_id | TEXT | NN | - | - |
parent_budget_id | TEXT | - | - | - |
limit_type | TEXT | NN | - | - |
limit_value | REAL | NN | - | - |
current_value | REAL | - | 0 | - |
period | TEXT | - | - | - |
period_start | TEXT | - | - | - |
action | TEXT | - | 'alert_only' | - |
alert_threshold_percent | INTEGER | - | 80 | - |
status | TEXT | - | 'active' | - |
created_at | TEXT | - | datetime('now') | - |
updated_at | TEXT | - | datetime('now') | - |
synced | INTEGER | - | 0 | - |
Table: call_graph_edges
Purpose: Table in sessions.db
Related Tables:
call_graph_functions(FK: caller_id → node_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
caller_id | TEXT | NN, FK→call_graph_functions | - | - |
callee_name | TEXT | NN | - | - |
call_line | INTEGER | NN | - | - |
call_file | TEXT | NN | - | - |
arguments | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_cge_callee | callee_name | - |
idx_cge_caller | caller_id | - |
Table: call_graph_fts
Purpose: FTS5 full-text search index for call_graph
Type: FTS5 Virtual Table
Indexed Columns
namesignaturedocstring
Table: call_graph_fts_config
Purpose: FTS5 full-text search index for call_graph_config
Type: FTS5 Virtual Table
Indexed Columns
kv
Table: call_graph_fts_data
Purpose: FTS5 full-text search index for call_graph
Type: FTS5 Virtual Table
Indexed Columns
idblock
Table: call_graph_fts_docsize
Purpose: FTS5 full-text search index for call_graph_docsize
Type: FTS5 Virtual Table
Indexed Columns
idsz
Table: call_graph_fts_idx
Purpose: FTS5 full-text search index for call_graph_idx
Type: FTS5 Virtual Table
Indexed Columns
segidtermpgno
Table: call_graph_functions
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
node_id | TEXT | PK | - | - |
name | TEXT | NN | - | - |
file_path | TEXT | NN | - | - |
start_line | INTEGER | NN | - | - |
end_line | INTEGER | NN | - | - |
language | TEXT | NN | - | - |
signature | TEXT | - | - | - |
docstring | TEXT | - | - | - |
class_name | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_cgf_name | name | - |
idx_cgf_file | file_path | - |
Table: call_graph_memory
Purpose: Table in sessions.db
Related Tables:
messages(FK: message_id → id)call_graph_functions(FK: node_id → node_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
node_id | TEXT | NN, FK→call_graph_functions | - | - |
message_id | INTEGER | FK→messages | - | - |
session_id | TEXT | - | - | - |
change_type | TEXT | - | - | - |
timestamp | TEXT | - | CURRENT_TIMESTAMP | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_cgm_session | session_id | - |
idx_cgm_node | node_id | - |
Table: capabilities
Purpose: Component capability tags for semantic search
Related Tables:
components(FK: component_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
component_id | TEXT | FK→components | - | - |
capability | TEXT | NN | - | - |
capability_type | TEXT | NN | - | - |
Table: checkpoints
Purpose: Table in sessions.db
Related Tables:
task_tracking(FK: task_id → task_id)agent_sessions(FK: session_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
session_id | TEXT | FK→agent_sessions | - | - |
task_id | TEXT | NN, FK→task_tracking | - | - |
agent_id | TEXT | NN | - | - |
agent_type | TEXT | NN | - | - |
iteration | INTEGER | - | 1 | - |
phase | TEXT | NN | - | - |
completed_items | TEXT | - | - | - |
pending_items | TEXT | - | - | - |
blocked_items | TEXT | - | - | - |
current_focus | TEXT | - | - | - |
context_summary | TEXT | - | - | - |
tokens_consumed | INTEGER | - | 0 | - |
tools_invoked | INTEGER | - | 0 | - |
files_modified | TEXT | - | - | - |
tests_passed | INTEGER | - | 0 | - |
tests_failed | INTEGER | - | 0 | - |
tests_skipped | INTEGER | - | 0 | - |
coverage_percent | REAL | - | - | - |
last_successful_state | TEXT | - | - | - |
rollback_instructions | TEXT | - | - | - |
continuation_prompt | TEXT | - | - | - |
hash | TEXT | NN | - | - |
signature | TEXT | - | - | - |
created_at | TEXT | - | datetime('now') | - |
synced | INTEGER | - | 0 | - |
Table: cloud_sync_state
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
table_name | TEXT | NN | - | - |
last_sync_cursor | TEXT | - | - | - |
last_push_at | TEXT | - | - | - |
last_pull_at | TEXT | - | - | - |
pending_push_count | INTEGER | - | 0 | - |
conflict_count | INTEGER | - | 0 | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: code_patterns
Purpose: Table in sessions.db
Related Tables:
messages(FK: message_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
message_id | INTEGER | FK→messages | - | - |
language | TEXT | NN | - | - |
pattern_name | TEXT | NN | - | - |
pattern_type | TEXT | - | - | - |
code | TEXT | NN | - | - |
description | TEXT | - | - | - |
tags | TEXT | - | - | - |
usage_count | INTEGER | - | 1 | - |
last_used | TEXT | - | CURRENT_TIMESTAMP | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_code_patterns_cloud_id | cloud_id | - |
idx_code_patterns_sync | sync_status, synced_at | - |
idx_code_patterns_project | tenant_id, project_id | - |
idx_code_patterns_user | tenant_id, user_id | - |
idx_code_patterns_tenant | tenant_id | - |
Table: component_composability
Purpose: Table in sessions.db
Related Tables:
components(FK: component_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
component_id | TEXT | FK→components | - | - |
target_id | TEXT | - | - | - |
relationship | TEXT | NN | - | - |
Table: component_relationships
Purpose: Dependencies between components
Related Tables:
components(FK: source_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
source_id | TEXT | FK→components | - | - |
target_id | TEXT | - | - | - |
relationship_type | TEXT | NN | - | - |
notes | TEXT | - | - | - |
Table: component_search
Purpose: FTS5 full-text search index for components
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | - | - | - |
name | TEXT | - | - | - |
type | TEXT | - | - | - |
description | TEXT | - | - | - |
capabilities | TEXT | - | - | - |
triggers | TEXT | - | - | - |
Table: component_search_config
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
k | TEXT | PK, NN | - | - |
v | TEXT | - | - | - |
Table: component_search_content
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
c0 | TEXT | - | - | - |
c1 | TEXT | - | - | - |
c2 | TEXT | - | - | - |
c3 | TEXT | - | - | - |
c4 | TEXT | - | - | - |
c5 | TEXT | - | - | - |
Table: component_search_data
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
block | BLOB | - | - | - |
Table: component_search_docsize
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
sz | BLOB | - | - | - |
Table: component_search_idx
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
segid | TEXT | PK, NN | - | - |
term | TEXT | PK, NN | - | - |
pgno | TEXT | - | - | - |
Table: component_usage_stats
Purpose: Table in sessions.db
Related Tables:
components(FK: component_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
component_id | TEXT | FK→components | - | - |
invocation_count | INTEGER | - | 0 | - |
success_count | INTEGER | - | 0 | - |
failure_count | INTEGER | - | 0 | - |
avg_duration_ms | INTEGER | - | - | - |
last_used | TEXT | - | - | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: components
Purpose: Master registry of all CODITECT framework components
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
type | TEXT | NN | - | - |
name | TEXT | NN | - | - |
version | TEXT | - | - | - |
status | TEXT | - | 'operational' | - |
path | TEXT | NN | - | - |
category | TEXT | - | - | - |
subcategory | TEXT | - | - | - |
description | TEXT | - | - | - |
complexity | TEXT | - | 'medium' | - |
maturity | TEXT | - | 'production' | - |
confidence | REAL | - | 0.5 | - |
documentation_quality | TEXT | - | 'partial' | - |
content_hash | TEXT | - | - | - |
indexed_at | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
llm_provider | TEXT | - | - | - |
llm_model | TEXT | - | - | - |
llm_temperature | REAL | - | 0.7 | - |
llm_max_tokens | INTEGER | - | 4096 | - |
tools_list | TEXT | - | - | - |
a2a_schema | TEXT | - | - | - |
token_budget_recommended | INTEGER | - | 0 | - |
token_budget_maximum | INTEGER | - | 0 | - |
invocation_method | TEXT | - | - | - |
parallel_safe | INTEGER | - | 1 | - |
vendor_name | TEXT | - | - | - |
vendor_url | TEXT | - | - | - |
Table: context_graph_nodes
Purpose: Table in sessions.db
Related Tables:
context_graphs(FK: context_graph_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
context_graph_id | TEXT | PK, NN, FK→context_graphs | - | - |
node_id | TEXT | PK, NN | - | - |
relevance_score | REAL | - | 1.0 | - |
depth | INTEGER | - | 0 | - |
is_seed | INTEGER | - | 0 | - |
include_properties | INTEGER | - | 1 | - |
token_estimate | INTEGER | - | 0 | - |
included_at | TEXT | - | strftime('%Y-%m-%dT%H:%M:%SZ', 'now') | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_context_graph_nodes_depth | context_graph_id, depth | - |
idx_context_graph_nodes_relevance | context_graph_id, relevance_score | - |
idx_context_graph_nodes_node | node_id | - |
idx_context_graph_nodes_graph | context_graph_id | - |
Table: context_graph_usage
Purpose: Table in sessions.db
Related Tables:
context_graphs(FK: context_graph_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
context_graph_id | TEXT | NN, FK→context_graphs | - | - |
session_id | TEXT | - | - | - |
message_id | TEXT | - | - | - |
agent_id | TEXT | - | - | - |
tokens_used | INTEGER | - | - | - |
retrieval_time_ms | INTEGER | - | - | - |
was_helpful | INTEGER | - | - | - |
feedback_text | TEXT | - | - | - |
used_at | TEXT | NN | strftime('%Y-%m-%dT%H:%M:%SZ', 'now') | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_context_graph_usage_time | used_at | - |
idx_context_graph_usage_session | session_id | - |
idx_context_graph_usage_graph | context_graph_id | - |
Table: context_graphs
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
name | TEXT | - | - | - |
task_description | TEXT | - | - | - |
seed_nodes | TEXT | - | - | - |
seed_strategy | TEXT | - | 'anchor' | - |
token_budget | INTEGER | - | 4000 | - |
max_depth | INTEGER | - | 3 | - |
max_nodes | INTEGER | - | 128 | - |
relevance_threshold | REAL | - | 0.5 | - |
node_count | INTEGER | - | 0 | - |
edge_count | INTEGER | - | 0 | - |
tokens_estimated | INTEGER | - | 0 | - |
tenant_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
session_id | TEXT | - | - | - |
created_at | TEXT | NN | strftime('%Y-%m-%dT%H:%M:%SZ', 'now') | - |
expires_at | TEXT | - | - | - |
build_time_ms | INTEGER | - | - | - |
builder_version | TEXT | - | '1.0.0' | - |
policies_applied | TEXT | - | - | JSON array of governance policies applied during graph construction (J.25.4.4) |
phi_node_count | INTEGER | - | 0 | Count of nodes with potential PHI indicators for HIPAA compliance (J.25.4.3) |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_context_graphs_expires | expires_at | - |
idx_context_graphs_created | created_at | - |
idx_context_graphs_session | session_id | - |
idx_context_graphs_project | tenant_id, project_id | - |
idx_context_graphs_tenant | tenant_id | - |
Table: doc_embeddings
Purpose: Table in sessions.db
Related Tables:
doc_index(FK: doc_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
doc_id | INTEGER | NN, FK→doc_index | - | - |
embedding | BLOB | NN | - | - |
model | TEXT | NN | - | - |
chunk_index | INTEGER | - | 0 | - |
chunk_text | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: doc_index
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
doc_hash | TEXT | NN | - | - |
doc_type | TEXT | NN | - | - |
title | TEXT | NN | - | - |
file_path | TEXT | NN | - | - |
absolute_path | TEXT | - | - | - |
submodule | TEXT | - | - | - |
category | TEXT | - | - | - |
subcategory | TEXT | - | - | - |
tags | TEXT | - | - | - |
content_hash | TEXT | - | - | - |
word_count | INTEGER | - | 0 | - |
line_count | INTEGER | - | 0 | - |
has_code_blocks | BOOLEAN | - | FALSE | - |
has_mermaid | BOOLEAN | - | FALSE | - |
frontmatter | TEXT | - | - | - |
first_heading | TEXT | - | - | - |
summary | TEXT | - | - | - |
created_at | TEXT | - | - | - |
modified_at | TEXT | - | - | - |
indexed_at | TEXT | - | CURRENT_TIMESTAMP | - |
workspace_id | TEXT | - | - | - |
workspace_name | TEXT | - | - | - |
Table: doc_search
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
title | TEXT | - | - | - |
file_path | TEXT | - | - | - |
category | TEXT | - | - | - |
tags | TEXT | - | - | - |
summary | TEXT | - | - | - |
first_heading | TEXT | - | - | - |
Table: doc_search_config
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
k | TEXT | PK, NN | - | - |
v | TEXT | - | - | - |
Table: doc_search_data
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
block | BLOB | - | - | - |
Table: doc_search_docsize
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
sz | BLOB | - | - | - |
Table: doc_search_idx
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
segid | TEXT | PK, NN | - | - |
term | TEXT | PK, NN | - | - |
pgno | TEXT | - | - | - |
Table: embeddings
Purpose: Table in sessions.db
Related Tables:
messages(FK: message_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
message_id | INTEGER | NN, FK→messages | - | - |
embedding | BLOB | NN | - | - |
model | TEXT | NN | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_embeddings_cloud_id | cloud_id | - |
idx_embeddings_sync | sync_status, synced_at | - |
idx_embeddings_project | tenant_id, project_id | - |
idx_embeddings_user | tenant_id, user_id | - |
idx_embeddings_tenant | tenant_id | - |
Table: entries
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
hash | TEXT | NN | - | - |
entry_type | TEXT | NN | - | - |
uuid | TEXT | - | - | - |
parent_uuid | TEXT | - | - | - |
logical_parent_uuid | TEXT | - | - | - |
session_id | TEXT | - | - | - |
agent_id | TEXT | - | - | - |
slug | TEXT | - | - | - |
cwd | TEXT | - | - | - |
git_branch | TEXT | - | - | - |
version | TEXT | - | - | - |
timestamp | TEXT | - | - | - |
extracted_at | TEXT | - | - | - |
is_sidechain | BOOLEAN | - | FALSE | - |
is_meta | BOOLEAN | - | FALSE | - |
user_type | TEXT | - | - | - |
content | TEXT | - | - | - |
role | TEXT | - | - | - |
raw_json | TEXT | NN | - | - |
source_file | TEXT | - | - | - |
source_line | INTEGER | - | - | - |
Table: entries_embeddings
Purpose: Table in sessions.db
Related Tables:
entries(FK: entry_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
entry_id | INTEGER | NN, FK→entries | - | - |
embedding | BLOB | NN | - | - |
model | TEXT | NN | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: file_backup_entries
Purpose: Table in sessions.db
Related Tables:
file_snapshots(FK: snapshot_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
snapshot_id | INTEGER | FK→file_snapshots | - | - |
file_path | TEXT | NN | - | - |
backup_file_name | TEXT | - | - | - |
version | INTEGER | - | - | - |
backup_time | TEXT | - | - | - |
Table: file_snapshots
Purpose: Table in sessions.db
Related Tables:
entries(FK: entry_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
entry_id | INTEGER | FK→entries | - | - |
message_id | TEXT | - | - | - |
snapshot_timestamp | TEXT | - | - | - |
is_snapshot_update | BOOLEAN | - | FALSE | - |
snapshot_data | TEXT | - | - | - |
Table: health_events
Purpose: Table in sessions.db
Related Tables:
agent_sessions(FK: session_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
agent_id | TEXT | NN | - | - |
session_id | TEXT | FK→agent_sessions | - | - |
task_id | TEXT | - | - | - |
timestamp | TEXT | - | datetime('now') | - |
previous_state | TEXT | - | - | - |
new_state | TEXT | NN | - | - |
state_reason | TEXT | - | - | - |
heartbeat_data | TEXT | - | - | - |
intervention_level | TEXT | - | - | - |
intervention_action | TEXT | - | - | - |
intervention_result | TEXT | - | - | - |
circuit_breaker_target | TEXT | - | - | - |
circuit_breaker_state | TEXT | - | - | - |
circuit_breaker_failures | INTEGER | - | - | - |
synced | INTEGER | - | 0 | - |
Table: knowledge_entities
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
entity_type | TEXT | NN | - | - |
entity_id | INTEGER | NN | - | - |
name | TEXT | NN | - | - |
description | TEXT | - | - | - |
keywords | TEXT | - | - | - |
embedding_id | INTEGER | - | - | - |
importance | REAL | - | 0.5 | - |
last_accessed | TEXT | - | - | - |
access_count | INTEGER | - | 0 | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: knowledge_graph
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
source_type | TEXT | NN | - | - |
source_id | INTEGER | NN | - | - |
source_name | TEXT | - | - | - |
relationship | TEXT | NN | - | - |
target_type | TEXT | NN | - | - |
target_id | INTEGER | NN | - | - |
target_name | TEXT | - | - | - |
confidence | REAL | - | 1.0 | - |
extracted_from | TEXT | - | - | - |
context | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: learning_achievements
Purpose: Table in sessions.db
Related Tables:
learning_modules(FK: module_id → id)learning_badges(FK: badge_id → id)learning_users(FK: user_id → user_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
user_id | TEXT | NN, FK→learning_users | - | - |
badge_id | INTEGER | NN, FK→learning_badges | - | - |
earned_at | TEXT | - | CURRENT_TIMESTAMP | - |
module_id | INTEGER | FK→learning_modules | - | - |
notes | TEXT | - | - | - |
Table: learning_analytics_events
Purpose: Table in sessions.db
Related Tables:
learning_badges(FK: badge_id → id)learning_modules(FK: module_id → id)learning_users(FK: user_id → user_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
event_id | TEXT | NN | - | - |
user_id | TEXT | NN, FK→learning_users | - | - |
session_id | TEXT | - | - | - |
event_type | TEXT | NN | - | - |
event_category | TEXT | - | - | - |
module_id | INTEGER | FK→learning_modules | - | - |
badge_id | INTEGER | FK→learning_badges | - | - |
event_data | TEXT | - | - | - |
timestamp | TEXT | - | CURRENT_TIMESTAMP | - |
Table: learning_badges
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
badge_key | TEXT | NN | - | - |
icon | TEXT | NN | - | - |
title | TEXT | NN | - | - |
description | TEXT | - | - | - |
badge_category | TEXT | - | - | - |
rarity | TEXT | - | - | - |
criteria_type | TEXT | - | - | - |
criteria_data | TEXT | - | - | - |
sort_order | INTEGER | - | 0 | - |
is_active | BOOLEAN | - | 1 | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: learning_modules
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
module_key | TEXT | NN | - | - |
module_type | TEXT | NN | - | - |
category | TEXT | NN | - | - |
title | TEXT | NN | - | - |
description | TEXT | - | - | - |
difficulty_level | TEXT | - | - | - |
estimated_duration_minutes | INTEGER | - | - | - |
prerequisite_modules | TEXT | - | - | - |
learning_objectives | TEXT | - | - | - |
content_path | TEXT | - | - | - |
has_quiz | BOOLEAN | - | 0 | - |
has_exercise | BOOLEAN | - | 0 | - |
badge_id | TEXT | - | - | - |
sort_order | INTEGER | - | 0 | - |
is_active | BOOLEAN | - | 1 | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: learning_progress
Purpose: Table in sessions.db
Related Tables:
learning_modules(FK: module_id → id)learning_users(FK: user_id → user_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
user_id | TEXT | NN, FK→learning_users | - | - |
module_id | INTEGER | NN, FK→learning_modules | - | - |
status | TEXT | NN | - | - |
started_at | TEXT | - | - | - |
completed_at | TEXT | - | - | - |
time_spent_seconds | INTEGER | - | 0 | - |
quiz_score | INTEGER | - | - | - |
quiz_max_score | INTEGER | - | - | - |
exercise_completed | BOOLEAN | - | 0 | - |
review_needed | BOOLEAN | - | 0 | - |
notes | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: learning_users
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
user_id | TEXT | NN | - | - |
experience_level | TEXT | - | - | - |
ai_familiarity | TEXT | - | - | - |
learning_goal | TEXT | - | - | - |
learning_style | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
last_activity | TEXT | - | CURRENT_TIMESTAMP | - |
onboarding_completed | BOOLEAN | - | 0 | - |
certification_level | TEXT | - | - | - |
total_time_seconds | INTEGER | - | 0 | - |
total_modules_completed | INTEGER | - | 0 | - |
total_badges_earned | INTEGER | - | 0 | - |
Table: message_component_invocations
Purpose: Table in sessions.db
Related Tables:
messages(FK: message_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
message_id | INTEGER | NN, FK→messages | - | - |
component_name | TEXT | NN | - | - |
component_type | TEXT | NN | - | - |
session_id | TEXT | - | - | - |
timestamp | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: message_threading
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
uuid | TEXT | NN | - | - |
parent_uuid | TEXT | - | - | - |
logical_parent_uuid | TEXT | - | - | - |
session_id | TEXT | - | - | - |
agent_id | TEXT | - | - | - |
entry_type | TEXT | - | - | - |
timestamp | TEXT | - | - | - |
Table: messages
Purpose: Extracted session messages from all LLMs
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
hash | TEXT | NN | - | - |
content | TEXT | NN | - | - |
role | TEXT | NN | - | - |
source_type | TEXT | - | - | - |
source_file | TEXT | - | - | - |
session_id | TEXT | - | - | - |
checkpoint | TEXT | - | - | - |
timestamp | TEXT | - | - | - |
extracted_at | TEXT | - | - | - |
content_length | INTEGER | - | - | - |
has_code | BOOLEAN | - | - | - |
has_markdown | BOOLEAN | - | - | - |
agent_context | TEXT | - | - | - |
tool_use | TEXT | - | - | - |
tool_result | TEXT | - | - | - |
export_metadata | TEXT | - | - | - |
component_invocations | TEXT | - | - | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_messages_project_id | project_id | - |
idx_messages_cloud_id | cloud_id | - |
idx_messages_sync | sync_status, synced_at | - |
idx_messages_project | tenant_id, project_id | - |
idx_messages_user | tenant_id, user_id | - |
idx_messages_tenant | tenant_id | - |
Table: messages_fts
Purpose: FTS5 search index for messages
Type: FTS5 Virtual Table
Indexed Columns
contentrole
Table: messages_fts_config
Purpose: FTS5 full-text search index for messages_config
Type: FTS5 Virtual Table
Indexed Columns
kv
Table: messages_fts_data
Purpose: FTS5 full-text search index for messages
Type: FTS5 Virtual Table
Indexed Columns
idblock
Table: messages_fts_docsize
Purpose: FTS5 full-text search index for messages_docsize
Type: FTS5 Virtual Table
Indexed Columns
idsz
Table: messages_fts_idx
Purpose: FTS5 full-text search index for messages_idx
Type: FTS5 Virtual Table
Indexed Columns
segidtermpgno
Table: model_pricing
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
model_name | TEXT | NN | - | - |
model_tier | TEXT | NN | - | - |
input_per_million | REAL | NN | - | - |
output_per_million | REAL | NN | - | - |
cache_read_per_million | REAL | - | - | - |
cache_write_per_million | REAL | - | - | - |
effective_date | TEXT | NN | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: project_decisions
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
tenant_id | TEXT | - | - | - |
project_id | TEXT | NN | - | - |
decision_id | TEXT | NN | - | - |
title | TEXT | NN | - | - |
context | TEXT | NN | - | - |
decision | TEXT | NN | - | - |
consequences | TEXT | - | - | - |
alternatives | TEXT | - | - | - |
status | TEXT | - | 'proposed' | - |
superseded_by | TEXT | - | - | - |
decided_by | TEXT | - | - | - |
decided_at | TEXT | - | - | - |
session_id | TEXT | - | - | - |
source_file | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
content_hash | TEXT | NN | - | - |
sync_cursor | INTEGER | - | - | - |
Table: project_summaries
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
project_path | TEXT | NN | - | - |
summary | TEXT | - | - | - |
key_decisions | TEXT | - | - | - |
key_patterns | TEXT | - | - | - |
tech_stack | TEXT | - | - | - |
common_errors | TEXT | - | - | - |
message_count | INTEGER | - | 0 | - |
last_updated | TEXT | - | CURRENT_TIMESTAMP | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: projects
Purpose: Registered project metadata
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
name | TEXT | NN | - | - |
description | TEXT | - | - | - |
status | TEXT | - | 'active' | - |
owner | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: queue_operations
Purpose: Table in sessions.db
Related Tables:
entries(FK: entry_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
entry_id | INTEGER | FK→entries | - | - |
operation | TEXT | NN | - | - |
content | TEXT | - | - | - |
timestamp | TEXT | - | - | - |
Table: reasoning_traces
Purpose: Table in sessions.db
Related Tables:
entries(FK: entry_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
session_id | TEXT | NN | - | - |
trace_id | TEXT | NN | - | - |
step_sequence | INTEGER | NN | - | - |
entry_id | INTEGER | FK→entries | - | - |
tool_name | TEXT | - | - | - |
tool_status | TEXT | - | - | - |
confidence_score | REAL | - | - | - |
reasoning_summary | TEXT | - | - | - |
input_context_hash | TEXT | - | - | - |
output_hash | TEXT | - | - | - |
latency_ms | INTEGER | - | - | - |
token_input | INTEGER | - | - | - |
token_output | INTEGER | - | - | - |
error_type | TEXT | - | - | - |
error_recovery_path | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: relationship_types
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
name | TEXT | NN | - | - |
inverse_name | TEXT | - | - | - |
description | TEXT | - | - | - |
source_types | TEXT | - | - | - |
target_types | TEXT | - | - | - |
bidirectional | BOOLEAN | - | FALSE | - |
Table: session_insights
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
session_id | TEXT | NN | - | - |
insight_type | TEXT | NN | - | - |
content | TEXT | NN | - | - |
confidence | REAL | - | - | - |
extracted_at | TEXT | NN | - | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_session_insights_cloud_id | cloud_id | - |
idx_session_insights_sync | sync_status, synced_at | - |
idx_session_insights_project | tenant_id, project_id | - |
idx_session_insights_user | tenant_id, user_id | - |
idx_session_insights_tenant | tenant_id | - |
Table: session_links
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
session_a | TEXT | NN | - | - |
session_b | TEXT | NN | - | - |
relationship | TEXT | NN | - | - |
notes | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_session_links_b | session_b | - |
idx_session_links_a | session_a | - |
Table: session_names
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
session_id | TEXT | PK | - | - |
session_name | TEXT | NN | - | - |
content_type | TEXT | - | - | - |
theme | TEXT | - | - | - |
confidence | REAL | - | - | - |
message_count | INTEGER | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
applied_at | TEXT | - | - | - |
Table: session_summaries
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | NN | - | - |
session_id | TEXT | NN | - | - |
project_id | TEXT | - | - | - |
project_path | TEXT | - | - | - |
title | TEXT | NN | - | - |
summary | TEXT | NN | - | - |
key_decisions | TEXT | - | - | - |
files_modified | TEXT | - | - | - |
tasks_completed | TEXT | - | - | - |
message_count | INTEGER | - | 0 | - |
tool_call_count | INTEGER | - | 0 | - |
duration_seconds | INTEGER | - | - | - |
session_started_at | TEXT | NN | - | - |
session_ended_at | TEXT | - | - | - |
embedding | BLOB | - | - | - |
embedding_model | TEXT | - | - | - |
content_hash | TEXT | NN | - | - |
synced_at | TEXT | - | CURRENT_TIMESTAMP | - |
sync_cursor | INTEGER | - | - | - |
client_version | TEXT | - | - | - |
hardware_id | TEXT | - | - | - |
Table: skill_improvements
Purpose: Table in sessions.db
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
session_id | TEXT | NN | - | - |
skill_name | TEXT | - | - | - |
improvement_type | TEXT | NN | - | - |
priority | TEXT | - | - | - |
recommendation | TEXT | - | - | - |
status | TEXT | - | 'pending' | - |
created_at | TEXT | NN | - | - |
Table: sprints
Purpose: Table in sessions.db
Related Tables:
projects(FK: project_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
project_id | TEXT | NN, FK→projects | - | - |
name | TEXT | NN | - | - |
goal | TEXT | - | - | - |
start_date | TEXT | NN | - | - |
end_date | TEXT | NN | - | - |
status | TEXT | - | 'planned' | - |
velocity_planned | INTEGER | - | - | - |
velocity_actual | INTEGER | - | - | - |
retrospective | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: sub_projects
Purpose: Table in sessions.db
Related Tables:
projects(FK: project_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
project_id | TEXT | NN, FK→projects | - | - |
name | TEXT | NN | - | - |
path | TEXT | - | - | - |
description | TEXT | - | - | - |
status | TEXT | - | 'active' | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: summaries
Purpose: Table in sessions.db
Related Tables:
entries(FK: entry_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
entry_id | INTEGER | FK→entries | - | - |
summary_text | TEXT | NN | - | - |
leaf_uuid | TEXT | - | - | - |
Table: system_events
Purpose: Table in sessions.db
Related Tables:
entries(FK: entry_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
entry_id | INTEGER | FK→entries | - | - |
subtype | TEXT | - | - | - |
level | TEXT | - | - | - |
content | TEXT | - | - | - |
compact_trigger | TEXT | - | - | - |
compact_pre_tokens | INTEGER | - | - | - |
cause | TEXT | - | - | - |
error | TEXT | - | - | - |
max_retries | INTEGER | - | - | - |
retry_attempt | INTEGER | - | - | - |
retry_in_ms | INTEGER | - | - | - |
Table: task_messages
Purpose: Task-to-message associations
Related Tables:
messages(FK: message_id → id)task_tracking(FK: task_tracking_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
task_tracking_id | INTEGER | NN, FK→task_tracking | - | - |
message_id | INTEGER | NN, FK→messages | - | - |
role | TEXT | - | - | - |
tool_status | TEXT | - | - | - |
created_at | TEXT | - | datetime('now') | - |
Table: task_tracking
Purpose: Local task status tracking
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
task_id | TEXT | NN | - | - |
task_description | TEXT | - | - | - |
source | TEXT | - | 'extraction' | - |
status | TEXT | - | 'unknown' | - |
started_at | TEXT | - | - | - |
completed_at | TEXT | - | - | - |
message_id_start | INTEGER | - | - | - |
message_id_end | INTEGER | - | - | - |
session_export_file | TEXT | - | - | - |
outcome | TEXT | - | - | - |
outcome_score | REAL | - | - | - |
tool_success_count | INTEGER | - | 0 | - |
tool_error_count | INTEGER | - | 0 | - |
user_corrections | INTEGER | - | 0 | - |
created_at | TEXT | - | datetime('now') | - |
updated_at | TEXT | - | datetime('now') | - |
Table: token_economics
Purpose: Token usage tracking per session
Related Tables:
entries(FK: entry_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
session_id | TEXT | NN | - | - |
entry_id | INTEGER | FK→entries | - | - |
model_name | TEXT | NN | - | - |
model_tier | TEXT | - | - | - |
token_input | INTEGER | NN | 0 | - |
token_output | INTEGER | NN | 0 | - |
token_cache_read | INTEGER | - | 0 | - |
token_cache_write | INTEGER | - | 0 | - |
cost_input_usd | REAL | - | - | - |
cost_output_usd | REAL | - | - | - |
cost_cache_usd | REAL | - | - | - |
cost_total_usd | REAL | - | - | - |
task_id | TEXT | - | - | - |
agent_name | TEXT | - | - | - |
operation_type | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_token_economics_cloud_id | cloud_id | - |
idx_token_economics_sync | sync_status, synced_at | - |
idx_token_economics_project | tenant_id, project_id | - |
idx_token_economics_user | tenant_id, user_id | - |
idx_token_economics_tenant | tenant_id | - |
Table: token_records
Purpose: Table in sessions.db
Related Tables:
checkpoints(FK: checkpoint_id → id)agent_sessions(FK: session_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | TEXT | PK | - | - |
timestamp | TEXT | - | datetime('now') | - |
session_id | TEXT | FK→agent_sessions | - | - |
task_id | TEXT | - | - | - |
agent_id | TEXT | - | - | - |
iteration | INTEGER | - | - | - |
checkpoint_id | TEXT | FK→checkpoints | - | - |
model | TEXT | NN | - | - |
input_tokens | INTEGER | - | 0 | - |
output_tokens | INTEGER | - | 0 | - |
cache_read_tokens | INTEGER | - | 0 | - |
cache_write_tokens | INTEGER | - | 0 | - |
input_cost_usd | REAL | - | - | - |
output_cost_usd | REAL | - | - | - |
cache_cost_usd | REAL | - | - | - |
total_cost_usd | REAL | - | - | - |
tool_calls | TEXT | - | - | - |
latency_ms | INTEGER | - | - | - |
success | INTEGER | - | 1 | - |
synced | INTEGER | - | 0 | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_token_records_cloud_id | cloud_id | - |
idx_token_records_sync | sync_status, synced_at | - |
idx_token_records_project | tenant_id, project_id | - |
idx_token_records_user | tenant_id, user_id | - |
idx_token_records_tenant | tenant_id | - |
Table: token_usage
Purpose: Table in sessions.db
Related Tables:
messages(FK: message_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
message_id | INTEGER | FK→messages | - | - |
session_id | TEXT | NN | - | - |
project_path | TEXT | - | - | - |
input_tokens | INTEGER | - | 0 | - |
output_tokens | INTEGER | - | 0 | - |
cache_read_input_tokens | INTEGER | - | 0 | - |
cache_creation_input_tokens | INTEGER | - | 0 | - |
cost_usd | REAL | - | 0.0 | - |
estimated_cost_usd | REAL | - | - | - |
duration_ms | INTEGER | - | 0 | - |
model | TEXT | - | - | - |
timestamp | TEXT | NN | - | - |
indexed_at | TEXT | - | CURRENT_TIMESTAMP | - |
Table: tool_analytics
Purpose: Tool usage statistics and performance
Related Tables:
entries(FK: entry_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
session_id | TEXT | NN | - | - |
entry_id | INTEGER | FK→entries | - | - |
tool_name | TEXT | NN | - | - |
tool_category | TEXT | - | - | - |
agent_name | TEXT | - | - | - |
task_id | TEXT | - | - | - |
status | TEXT | NN | - | - |
error_type | TEXT | - | - | - |
error_message | TEXT | - | - | - |
execution_time_ms | INTEGER | - | - | - |
input_size_bytes | INTEGER | - | - | - |
output_size_bytes | INTEGER | - | - | - |
retry_count | INTEGER | - | 0 | - |
context_window_usage | REAL | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
trajectory_hash | TEXT | - | - | - |
tool_id | TEXT | - | - | - |
timestamp | TEXT | - | - | - |
duration_ms | INTEGER | - | - | - |
input_size | INTEGER | - | - | - |
output_size | INTEGER | - | - | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_tool_analytics_project_id | project_id | - |
idx_tool_analytics_cloud_id | cloud_id | - |
idx_tool_analytics_sync | sync_status, synced_at | - |
idx_tool_analytics_project | tenant_id, project_id | - |
idx_tool_analytics_user | tenant_id, user_id | - |
idx_tool_analytics_tenant | tenant_id | - |
Table: workflow_context_snapshots
Purpose: Table in sessions.db
Related Tables:
workflow_states(FK: workflow_id → workflow_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
workflow_id | TEXT | NN, FK→workflow_states | - | - |
session_id | TEXT | NN | - | - |
snapshot_type | TEXT | - | - | - |
context_graph_json | TEXT | - | - | - |
active_policies | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_snapshots_type | snapshot_type | - |
idx_snapshots_session | session_id | - |
idx_snapshots_workflow | workflow_id | - |
Table: workflow_states
Purpose: Cross-session workflow state persistence
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
workflow_id | TEXT | PK | - | - |
workflow_type | TEXT | NN | - | - |
status | TEXT | - | 'active' | - |
anchor_nodes | TEXT | - | - | - |
accumulated_decisions | TEXT | - | - | - |
accumulated_errors | TEXT | - | - | - |
current_step | TEXT | - | - | - |
completed_steps | TEXT | - | - | - |
started_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
session_ids | TEXT | - | - | - |
tenant_id | TEXT | - | - | - |
user_id | TEXT | - | - | - |
team_id | TEXT | - | - | - |
project_id | TEXT | - | - | - |
cloud_id | TEXT | - | - | - |
synced_at | TEXT | - | - | - |
sync_status | TEXT | - | 'pending' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_workflow_states_sync | sync_status, synced_at | - |
idx_workflow_states_user | tenant_id, user_id | - |
idx_workflow_states_tenant | tenant_id | - |
idx_workflow_states_type | workflow_type | - |
idx_workflow_states_status | status | - |
Tier 4: projects.db
Purpose: Project-specific data (project index, embeddings)
Location: ~/.coditect-data/context-storage/projects.db
Backup: With code
Tables: 11
Table: content_hashes
Purpose: File content hashes for change detection
Related Tables:
projects(FK: project_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
project_id | INTEGER | NN, FK→projects | - | - |
relative_path | TEXT | NN | - | - |
file_hash | TEXT | NN | - | - |
file_size | INTEGER | - | - | - |
content_type | TEXT | - | - | - |
language | TEXT | - | - | - |
last_hashed_at | TEXT | NN | - | - |
last_indexed_at | TEXT | - | - | - |
needs_reindex | INTEGER | - | 0 | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_content_hashes_hash | file_hash | - |
idx_content_hashes_needs_reindex | needs_reindex | - |
idx_content_hashes_type | content_type | - |
idx_content_hashes_project | project_id | - |
Table: exclude_patterns
Purpose: File exclusion patterns per project
Related Tables:
projects(FK: project_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
project_id | INTEGER | FK→projects | - | - |
pattern | TEXT | NN | - | - |
pattern_type | TEXT | - | 'glob' | - |
scope | TEXT | - | 'directory' | - |
reason | TEXT | - | - | - |
source | TEXT | - | 'gitignore' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_exclude_patterns_project | project_id | - |
Table: project_activity
Purpose: Table in projects.db
Related Tables:
projects(FK: project_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
project_id | INTEGER | NN, FK→projects | - | - |
activity_type | TEXT | NN | - | - |
session_uuid | TEXT | - | - | - |
details | TEXT | - | - | - |
files_touched | INTEGER | - | 0 | - |
timestamp | TEXT | - | CURRENT_TIMESTAMP | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_activity_timestamp | timestamp | - |
idx_activity_session | session_uuid | - |
idx_activity_type | activity_type | - |
idx_activity_project | project_id | - |
Table: project_embeddings
Purpose: Semantic embeddings for project content
Related Tables:
content_hashes(FK: content_hash_id → id)projects(FK: project_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
project_id | INTEGER | NN, FK→projects | - | - |
content_hash_id | INTEGER | FK→content_hashes | - | - |
chunk_index | INTEGER | NN | - | - |
chunk_text | TEXT | NN | - | - |
chunk_hash | TEXT | NN | - | - |
embedding | BLOB | - | - | - |
embedding_model | TEXT | - | 'all-MiniLM-L6-v2' | - |
embedding_dim | INTEGER | - | 384 | - |
start_line | INTEGER | - | - | - |
end_line | INTEGER | - | - | - |
content_type | TEXT | - | - | - |
language | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_embeddings_chunk_hash | chunk_hash | - |
idx_embeddings_type | content_type | - |
idx_embeddings_content_hash | content_hash_id | - |
idx_embeddings_project | project_id | - |
Table: project_tags
Purpose: Table in projects.db
Related Tables:
projects(FK: project_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
project_id | INTEGER | NN, FK→projects | - | - |
tag | TEXT | NN | - | - |
tag_type | TEXT | - | 'user' | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_project_tags_tag | tag | - |
idx_project_tags_project | project_id | - |
Table: projects
Purpose: Registered project metadata
Related Tables:
projects(FK: parent_project_id → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK | - | - |
project_uuid | TEXT | NN | - | - |
name | TEXT | NN | - | - |
path | TEXT | NN | - | - |
github_owner | TEXT | - | - | - |
github_repo | TEXT | - | - | - |
github_url | TEXT | - | - | - |
default_branch | TEXT | - | 'main' | - |
parent_project_id | INTEGER | FK→projects | - | - |
project_type | TEXT | - | 'standalone' | - |
status | TEXT | - | 'active' | - |
last_indexed_at | TEXT | - | - | - |
last_sync_at | TEXT | - | - | - |
description | TEXT | - | - | - |
primary_language | TEXT | - | - | - |
framework | TEXT | - | - | - |
created_at | TEXT | - | CURRENT_TIMESTAMP | - |
updated_at | TEXT | - | CURRENT_TIMESTAMP | - |
cloud_registered | INTEGER | - | 0 | - |
cloud_project_uuid | TEXT | - | - | - |
pending_sync | INTEGER | - | 0 | - |
Indexes
| Index | Columns | Unique |
|---|---|---|
idx_projects_pending | pending_sync | - |
idx_projects_cloud | cloud_registered | - |
idx_projects_type | project_type | - |
idx_projects_parent | parent_project_id | - |
idx_projects_status | status | - |
Table: projects_fts
Purpose: FTS5 full-text search index for projects
Type: FTS5 Virtual Table
Indexed Columns
namedescriptionpathprimary_languageframework
Table: projects_fts_config
Purpose: FTS5 full-text search index for projects_config
Type: FTS5 Virtual Table
Indexed Columns
kv
Table: projects_fts_data
Purpose: FTS5 full-text search index for projects
Type: FTS5 Virtual Table
Indexed Columns
idblock
Table: projects_fts_docsize
Purpose: FTS5 full-text search index for projects_docsize
Type: FTS5 Virtual Table
Indexed Columns
idsz
Table: projects_fts_idx
Purpose: FTS5 full-text search index for projects_idx
Type: FTS5 Virtual Table
Indexed Columns
segidtermpgno
Inter-Session: messaging.db
Purpose: Inter-session coordination, file locking, task claims
Location: ~/.coditect-data/context-storage/messaging.db
Backup: Optional
Tables: 9
Governance: ADR-160, ADR-173
Table: session_registry
Purpose: Active LLM session registration and heartbeat tracking
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
session_id | TEXT | PK | - | Session identifier (e.g., claude-29583) |
llm_vendor | TEXT | NN | - | LLM provider: claude, codex, gemini |
llm_model | TEXT | - | - | Model: opus-4.6, o3, gemini-2.0-flash |
tty | TEXT | - | - | Terminal device path |
pid | INTEGER | - | - | OS process ID |
project_id | TEXT | - | - | Current project context |
task_id | TEXT | - | - | Currently claimed task |
active_files | TEXT | - | - | JSON array of files being edited |
heartbeat_at | TEXT | NN | - | Last heartbeat timestamp |
registered_at | TEXT | NN | datetime('now') | Registration timestamp |
status | TEXT | - | 'active' | Session status: active, stale |
cwd | TEXT | - | - | Working directory path |
machine_uuid | TEXT | - | - | Machine identifier |
hostname | TEXT | - | - | Machine hostname |
last_active_at | TEXT | - | - | Last activity timestamp |
session_uuid | TEXT | - | - | Unique session UUID |
user_id | TEXT | - | - | User identifier |
Table: session_messages
Purpose: Structured inter-session messages (ADR-173 v2)
Related Tables:
- Self-referential (FK: reply_to → id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK, AUTO | - | Message ID |
sender_id | TEXT | NN | - | Sending session ID |
sender_session_uuid | TEXT | - | - | Sender UUID for dedup |
recipient_id | TEXT | - | - | Target session (null = broadcast) |
channel | TEXT | NN | - | Channel: session_lifecycle, task_broadcast, operator_alert, direct |
message_type | TEXT | NN | - | Type: started, ended, conflict, claim |
priority | INTEGER | NN | 0 | 0=routine, 1=normal, 2=high, 3=critical |
status | TEXT | NN | 'pending' | Delivery: pending, delivered, read |
project_id | TEXT | - | - | Project context |
task_id | TEXT | - | - | Related task ID |
activity | TEXT | - | - | Activity description |
payload | TEXT | - | - | JSON payload (type-specific data) |
reply_to | INTEGER | FK→session_messages | - | Parent message for threading |
created_at | TEXT | NN | datetime('now') | Creation timestamp |
delivered_at | TEXT | - | - | Delivery timestamp |
read_at | TEXT | - | - | Read timestamp |
expires_at | TEXT | NN | - | Message expiration |
Table: file_locks
Purpose: Advisory file locking across sessions
Related Tables:
session_registry(FK: session_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
file_path | TEXT | PK | - | Locked file path |
session_id | TEXT | NN, FK→session_registry | - | Lock holder session |
lock_type | TEXT | - | 'advisory' | Lock type |
locked_at | TEXT | NN | datetime('now') | Lock acquisition time |
Table: task_claims
Purpose: Exclusive task ownership across sessions
Related Tables:
session_registry(FK: session_id)
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
task_id | TEXT | PK | - | Task ID (e.g., H.13.7) |
session_id | TEXT | NN, FK→session_registry | - | Claiming session |
claimed_at | TEXT | NN | datetime('now') | Claim timestamp |
status | TEXT | - | 'claimed' | Claim status |
Table: message_dedup
Purpose: Message deduplication with coalescing window
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
sender_id | TEXT | PK | - | Sender session ID |
channel | TEXT | PK | - | Message channel |
message_type | TEXT | PK | - | Message type |
task_id | TEXT | PK | '' | Task context |
last_message_id | INTEGER | NN | - | Last message in window |
last_sent_at | TEXT | NN | - | Last send timestamp |
coalesce_window_seconds | INTEGER | NN | 30 | Dedup window in seconds |
Table: vacuum_reports
Purpose: Orphan detection and cleanup tracking
Fields
| Field | Type | Constraints | Default | Description |
|---|---|---|---|---|
id | INTEGER | PK, AUTO | - | Report ID |
sweep_type | TEXT | NN | - | Sweep type: scheduled, manual |
created_at | TEXT | NN | - | Report creation time |
task_id | TEXT | NN | - | Related task ID |
orphan_type | TEXT | NN | - | Type: stale_session, orphan_lock, orphan_claim |
last_activity | TEXT | - | - | Last known activity |
last_session_id | TEXT | - | - | Session that owned the orphan |
track | TEXT | - | - | Associated track |
description | TEXT | - | - | Human-readable description |
evidence | TEXT | - | - | Evidence/reasoning |
resolution | TEXT | - | - | Resolution action taken |
resolved_at | TEXT | - | - | Resolution timestamp |
resolved_by | TEXT | - | - | Resolver identifier |
Cloud Sync Mapping
See ADR-053 for cloud sync details.
| Local Table | Cloud Table | Direction | Frequency |
|---|---|---|---|
decisions | Decision | ↑↓ Bidirectional | On change |
skill_learnings | SkillLearning | ↑ Upload | Session end |
task_tracking | TaskTracking | ↑↓ Bidirectional | On change |
messages | Message | ↑ Upload | Batch (daily) |
Purpose Matrix
| I need to... | Table | Database |
|---|---|---|
| Find a component | components | platform.db |
| Search message history | messages_fts | sessions.db |
| Track a decision | decisions | org.db |
| Store skill learning | skill_learnings | org.db |
| Record tool usage | tool_analytics | sessions.db |
| Associate activity | activity_associations | sessions.db |
| Query knowledge graph | kg_nodes, kg_edges | org.db |
| Track project files | content_hashes | projects.db |
| Check active LLM sessions | session_registry | messaging.db |
| Lock a file across sessions | file_locks | messaging.db |
| Claim a task exclusively | task_claims | messaging.db |
| Send inter-session message | session_messages | messaging.db |
Generated: 2026-02-04T23:38:36Z
Script: scripts/generate-data-dictionary.py v1.0.0
Task: J.24.1.3