Four-Tier Database Schema Reference

ADRs: ADR-118, ADR-119

Overview

CODITECT uses a four-tier database architecture:

Tier	Database	Location	Purpose	Backup
1	platform.db	~/.coditect/framework-data/	Component registry	NOT REQUIRED
2	org.db	~/PROJECTS/.coditect-data/context-storage/	CRITICAL org data	DAILY
3	sessions.db	~/PROJECTS/.coditect-data/context-storage/	Session data	Optional
4	project.db	<project>/.coditect-data/	Project-specific	With code

Multi-Tenant Columns (ADR-119)

All user data tables include these columns for cloud sync:

-- Multi-tenant hierarchy
tenant_id    TEXT NOT NULL,      -- Organization UUID
user_id      TEXT NOT NULL,      -- User UUID
team_id      TEXT,               -- Team scope (optional)
project_id   TEXT,               -- Project scope (optional)

-- Cloud sync metadata
cloud_id     TEXT UNIQUE,        -- Cloud record UUID
synced_at    TEXT,               -- Last sync timestamp (RFC3339)
sync_status  TEXT DEFAULT 'pending'  -- pending | synced | conflict

---

TIER 2: org.db (CRITICAL)

Location: ~/PROJECTS/.coditect-data/context-storage/org.db Backup: DAILY (GFS retention) Size: ~105 MB

skill_learnings (758K+ rows)

Accumulated AI learning patterns - IRREPLACEABLE

CREATE TABLE skill_learnings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL,
    skill_name TEXT NOT NULL,
    outcome TEXT,
    effectiveness_score INTEGER,
    errors TEXT,
    analyzed_at TEXT NOT NULL,

    -- Multi-tenant (ADR-119)
    tenant_id TEXT,
    user_id TEXT,
    team_id TEXT,
    project_id TEXT,
    cloud_id TEXT,
    synced_at TEXT,
    sync_status TEXT DEFAULT 'pending',

    UNIQUE(session_id, skill_name)
);

-- Indexes
CREATE INDEX idx_skill_learnings_tenant ON skill_learnings(tenant_id);
CREATE INDEX idx_skill_learnings_user ON skill_learnings(tenant_id, user_id);
CREATE INDEX idx_skill_learnings_project ON skill_learnings(tenant_id, project_id);
CREATE INDEX idx_skill_learnings_sync ON skill_learnings(sync_status, synced_at);
CREATE INDEX idx_skill_learnings_cloud_id ON skill_learnings(cloud_id);

decisions (1.8K+ rows)

Architectural decisions - IRREPLACEABLE

CREATE TABLE decisions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER,
    project_path TEXT,
    decision_type TEXT NOT NULL,
    decision TEXT NOT NULL,
    rationale TEXT,
    alternatives_considered TEXT,
    confidence REAL DEFAULT 0.5,
    tags TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,

    -- Multi-tenant (ADR-119)
    tenant_id TEXT,
    user_id TEXT,
    team_id TEXT,
    project_id TEXT,
    cloud_id TEXT,
    synced_at TEXT,
    sync_status TEXT DEFAULT 'pending',

    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE SET NULL
);

-- Indexes
CREATE INDEX idx_decisions_tenant ON decisions(tenant_id);
CREATE INDEX idx_decisions_user ON decisions(tenant_id, user_id);
CREATE INDEX idx_decisions_project ON decisions(tenant_id, project_id);
CREATE INDEX idx_decisions_sync ON decisions(sync_status, synced_at);
CREATE INDEX idx_decisions_cloud_id ON decisions(cloud_id);

error_solutions (475+ rows)

Error-solution pairs - IRREPLACEABLE

CREATE TABLE error_solutions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    error_hash TEXT UNIQUE,
    error_type TEXT NOT NULL,
    error_signature TEXT NOT NULL,
    error_context TEXT,
    solution TEXT NOT NULL,
    solution_code TEXT,
    language TEXT,
    success_count INTEGER DEFAULT 1,
    failure_count INTEGER DEFAULT 0,
    last_used TEXT DEFAULT CURRENT_TIMESTAMP,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,

    -- Multi-tenant (ADR-119)
    tenant_id TEXT,
    user_id TEXT,
    team_id TEXT,
    project_id TEXT,
    cloud_id TEXT,
    synced_at TEXT,
    sync_status TEXT DEFAULT 'pending'
);

-- Indexes
CREATE INDEX idx_error_solutions_tenant ON error_solutions(tenant_id);
CREATE INDEX idx_error_solutions_user ON error_solutions(tenant_id, user_id);
CREATE INDEX idx_error_solutions_project ON error_solutions(tenant_id, project_id);
CREATE INDEX idx_error_solutions_sync ON error_solutions(sync_status, synced_at);
CREATE INDEX idx_error_solutions_cloud_id ON error_solutions(cloud_id);

cloud_sync_state

Tracks sync progress per table

CREATE TABLE cloud_sync_state (
    id INTEGER PRIMARY KEY,
    table_name TEXT UNIQUE NOT NULL,
    last_sync_cursor TEXT,
    last_push_at TEXT,
    last_pull_at TEXT,
    pending_push_count INTEGER DEFAULT 0,
    conflict_count INTEGER DEFAULT 0,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

local_identity

Stores resolved tenant/user identity

CREATE TABLE local_identity (
    id INTEGER PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    user_id TEXT NOT NULL,
    team_id TEXT,
    default_project_id TEXT,
    auth_source TEXT DEFAULT 'local',  -- local | machine_id | cloud | license_key
    token_expires_at TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

---

TIER 3: sessions.db (Regenerable)

Location: ~/PROJECTS/.coditect-data/context-storage/sessions.db Backup: Optional (regenerable from JSONL) Size: ~5.8 GB

messages (251K+ rows)

Session messages - regenerable from unified_messages.jsonl

CREATE TABLE messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    hash TEXT UNIQUE NOT NULL,
    content TEXT NOT NULL,
    role TEXT NOT NULL,
    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,

    -- Multi-tenant (ADR-119)
    tenant_id TEXT,
    user_id TEXT,
    team_id TEXT,
    project_id TEXT,
    cloud_id TEXT,
    synced_at TEXT,
    sync_status TEXT DEFAULT 'pending'
);

-- Indexes
CREATE INDEX idx_messages_tenant ON messages(tenant_id);
CREATE INDEX idx_messages_user ON messages(tenant_id, user_id);
CREATE INDEX idx_messages_project ON messages(tenant_id, project_id);
CREATE INDEX idx_messages_sync ON messages(sync_status, synced_at);
CREATE INDEX idx_messages_cloud_id ON messages(cloud_id);
CREATE INDEX idx_messages_session ON messages(session_id);
CREATE INDEX idx_messages_hash ON messages(hash);

tool_analytics (8.2M+ rows)

Tool usage analytics - regenerable

CREATE TABLE tool_analytics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL,
    entry_id INTEGER REFERENCES entries(id),
    tool_name TEXT NOT NULL,
    tool_category TEXT,
    agent_name TEXT,
    task_id TEXT,
    status TEXT NOT NULL,           -- success | failed | timeout | interrupted
    error_type TEXT,
    error_message TEXT,
    execution_time_ms INTEGER,
    input_size_bytes INTEGER,
    output_size_bytes INTEGER,
    retry_count INTEGER DEFAULT 0,
    context_window_usage REAL,
    trajectory_hash TEXT,
    tool_id TEXT,
    timestamp TEXT,
    duration_ms INTEGER,
    input_size INTEGER,
    output_size INTEGER,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,

    -- Multi-tenant (ADR-119)
    tenant_id TEXT,
    user_id TEXT,
    team_id TEXT,
    project_id TEXT,
    cloud_id TEXT,
    synced_at TEXT,
    sync_status TEXT DEFAULT 'pending'
);

-- Indexes
CREATE INDEX idx_tool_analytics_tenant ON tool_analytics(tenant_id);
CREATE INDEX idx_tool_analytics_user ON tool_analytics(tenant_id, user_id);
CREATE INDEX idx_tool_analytics_project ON tool_analytics(tenant_id, project_id);
CREATE INDEX idx_tool_analytics_sync ON tool_analytics(sync_status, synced_at);
CREATE INDEX idx_tool_analytics_cloud_id ON tool_analytics(cloud_id);
CREATE INDEX idx_tool_analytics_session ON tool_analytics(session_id);
CREATE INDEX idx_tool_analytics_tool ON tool_analytics(tool_name);

token_economics (17.8M+ rows)

Token usage and costs - regenerable

CREATE TABLE token_economics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL,
    entry_id INTEGER REFERENCES entries(id),
    model_name TEXT NOT NULL,
    model_tier TEXT,                -- opus | sonnet | haiku
    token_input INTEGER NOT NULL DEFAULT 0,
    token_output INTEGER NOT NULL DEFAULT 0,
    token_cache_read INTEGER DEFAULT 0,
    token_cache_write INTEGER DEFAULT 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 DEFAULT CURRENT_TIMESTAMP,

    -- Multi-tenant (ADR-119)
    tenant_id TEXT,
    user_id TEXT,
    team_id TEXT,
    project_id TEXT,
    cloud_id TEXT,
    synced_at TEXT,
    sync_status TEXT DEFAULT 'pending'
);

-- Indexes
CREATE INDEX idx_token_economics_tenant ON token_economics(tenant_id);
CREATE INDEX idx_token_economics_user ON token_economics(tenant_id, user_id);
CREATE INDEX idx_token_economics_project ON token_economics(tenant_id, project_id);
CREATE INDEX idx_token_economics_sync ON token_economics(sync_status, synced_at);
CREATE INDEX idx_token_economics_cloud_id ON token_economics(cloud_id);
CREATE INDEX idx_token_economics_session ON token_economics(session_id);
CREATE INDEX idx_token_economics_model ON token_economics(model_name);

session_insights (5.7K+ rows)

Session-level insights - regenerable

CREATE TABLE session_insights (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL,
    insight_type TEXT NOT NULL,
    content TEXT NOT NULL,
    confidence REAL,
    extracted_at TEXT NOT NULL,

    -- Multi-tenant (ADR-119)
    tenant_id TEXT,
    user_id TEXT,
    team_id TEXT,
    project_id TEXT,
    cloud_id TEXT,
    synced_at TEXT,
    sync_status TEXT DEFAULT 'pending',

    UNIQUE(session_id, insight_type, content)
);

-- Indexes
CREATE INDEX idx_session_insights_tenant ON session_insights(tenant_id);
CREATE INDEX idx_session_insights_user ON session_insights(tenant_id, user_id);
CREATE INDEX idx_session_insights_project ON session_insights(tenant_id, project_id);
CREATE INDEX idx_session_insights_sync ON session_insights(sync_status, synced_at);
CREATE INDEX idx_session_insights_cloud_id ON session_insights(cloud_id);

code_patterns (10K+ rows)

Extracted code patterns - regenerable

CREATE TABLE code_patterns (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER,
    language TEXT NOT NULL,
    pattern_name TEXT NOT NULL,
    pattern_type TEXT,
    code TEXT NOT NULL,
    description TEXT,
    tags TEXT,
    usage_count INTEGER DEFAULT 1,
    last_used TEXT DEFAULT CURRENT_TIMESTAMP,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,

    -- Multi-tenant (ADR-119)
    tenant_id TEXT,
    user_id TEXT,
    team_id TEXT,
    project_id TEXT,
    cloud_id TEXT,
    synced_at TEXT,
    sync_status TEXT DEFAULT 'pending',

    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE SET NULL
);

-- Indexes
CREATE INDEX idx_code_patterns_tenant ON code_patterns(tenant_id);
CREATE INDEX idx_code_patterns_user ON code_patterns(tenant_id, user_id);
CREATE INDEX idx_code_patterns_project ON code_patterns(tenant_id, project_id);
CREATE INDEX idx_code_patterns_sync ON code_patterns(sync_status, synced_at);
CREATE INDEX idx_code_patterns_cloud_id ON code_patterns(cloud_id);
CREATE INDEX idx_code_patterns_language ON code_patterns(language);

---

Cloud Sync Tables

These tables exist in both org.db and sessions.db:

cloud_sync_state

CREATE TABLE IF NOT EXISTS cloud_sync_state (
    id INTEGER PRIMARY KEY,
    table_name TEXT UNIQUE NOT NULL,
    last_sync_cursor TEXT,          -- Cursor for incremental pull
    last_push_at TEXT,              -- Last successful push (RFC3339)
    last_pull_at TEXT,              -- Last successful pull (RFC3339)
    pending_push_count INTEGER DEFAULT 0,
    conflict_count INTEGER DEFAULT 0,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

---

Migration Scripts

Script	Purpose	Location
migrate-to-four-tier-db.py	Split context.db → org.db + sessions.db	scripts/
migrate-multi-tenant-schema.py	Add multi-tenant columns (ADR-119)	scripts/

Running Migrations

# 1. Four-tier split (ADR-118)
python3 scripts/migrate-to-four-tier-db.py --dry-run
python3 scripts/migrate-to-four-tier-db.py
python3 scripts/migrate-to-four-tier-db.py --verify

# 2. Multi-tenant schema (ADR-119)
python3 scripts/migrate-multi-tenant-schema.py --dry-run
python3 scripts/migrate-multi-tenant-schema.py
python3 scripts/migrate-multi-tenant-schema.py --verify

---

Cloud Django Models

The cloud PostgreSQL schema mirrors the local SQLite schema with additional fields:

# Django model example (coditect-cloud-infra/backend)
class SkillLearning(TenantModel):
    """Mirrors local skill_learnings table."""

    tenant_id = 'tenant_id'  # django-multitenant

    tenant = models.ForeignKey('tenants.Tenant', on_delete=models.CASCADE)
    user = models.ForeignKey('users.User', on_delete=models.CASCADE)
    team = models.ForeignKey('teams.Team', null=True, on_delete=models.SET_NULL)
    project = models.ForeignKey('projects.Project', null=True, on_delete=models.SET_NULL)

    session_id = models.CharField(max_length=255)
    skill_name = models.CharField(max_length=255)
    outcome = models.TextField(null=True)
    effectiveness_score = models.IntegerField(null=True)
    errors = models.TextField(null=True)
    analyzed_at = models.DateTimeField()

    # Sync tracking
    local_id = models.BigIntegerField(null=True)  # Original local ID
    synced_from_machine = models.CharField(max_length=255, null=True)

    class Meta:
        unique_together = ['tenant', 'session_id', 'skill_name']

---

File Paths Reference

File	Path
org.db	~/PROJECTS/.coditect-data/context-storage/org.db
sessions.db	~/PROJECTS/.coditect-data/context-storage/sessions.db
context.db (legacy)	~/PROJECTS/.coditect-data/context-storage/context.db
ADR-118	internal/architecture/adrs/ADR-118-four-tier-database-architecture.md
ADR-119	internal/architecture/adrs/ADR-119-multi-tenant-local-schema.md
Migration script (4-tier)	scripts/migrate-to-four-tier-db.py
Migration script (multi-tenant)	scripts/migrate-multi-tenant-schema.py
Cloud sync binary	tools/cloud-sync/
Schema reference	docs/reference/database/FOUR-TIER-DATABASE-SCHEMA.md (this file)

---

Row Counts (as of 2026-01-26)

Database	Table	Rows	Status
org.db	skill_learnings	758,358	IRREPLACEABLE
org.db	decisions	1,856	IRREPLACEABLE
org.db	error_solutions	475	IRREPLACEABLE
sessions.db	messages	251,260	Regenerable
sessions.db	tool_analytics	8,200,000	Regenerable
sessions.db	token_economics	17,883,212	Regenerable
sessions.db	session_insights	5,753	Regenerable
sessions.db	code_patterns	10,246	Regenerable
sessions.db	embeddings	143,743	Regenerable

---

Last Updated: 2026-01-26 Version: 1.0.0