CODITECT Cloud Sync Documentation

Governance: ADR-053, ADR-118

Purpose: Document the local SQLite to cloud PostgreSQL synchronization architecture. Covers table mapping, sync direction, PostgreSQL schemas, and troubleshooting.

Task: J.24.4

Overview

CODITECT uses a hybrid local-cloud sync architecture:

Local: SQLite databases (ADR-118 Four-Tier) for offline capability
Cloud: PostgreSQL with django-multitenant for enterprise features
Sync: REST API with cursor-based polling and content-hash deduplication

┌─────────────────────────────────────────────────────────────────┐
│  LOCAL (Claude Code)                   CLOUD (api.coditect.ai)  │
├─────────────────────────────────────────────────────────────────┤
│                                                                  │
│  ┌──────────────┐      REST API       ┌──────────────────────┐  │
│  │ platform.db  │ ←─────────────────→ │ components table     │  │
│  │ (Tier 1)     │                     │ (read-only sync)     │  │
│  └──────────────┘                     └──────────────────────┘  │
│                                                                  │
│  ┌──────────────┐      REST API       ┌──────────────────────┐  │
│  │ org.db       │ ←─────────────────→ │ decisions,           │  │
│  │ (Tier 2)     │   BIDIRECTIONAL     │ skill_learnings,     │  │
│  │ CRITICAL     │                     │ error_solutions      │  │
│  └──────────────┘                     └──────────────────────┘  │
│                                                                  │
│  ┌──────────────┐      REST API       ┌──────────────────────┐  │
│  │ sessions.db  │ ─────────────────→  │ context_messages,    │  │
│  │ (Tier 3)     │   LOCAL → CLOUD     │ task_tracking,       │  │
│  │              │                     │ tool_analytics       │  │
│  └──────────────┘                     └──────────────────────┘  │
│                                                                  │
│  ┌──────────────┐      REST API       ┌──────────────────────┐  │
│  │ projects.db  │ ←─────────────────→ │ projects,            │  │
│  │ (Tier 4)     │   BIDIRECTIONAL     │ project_embeddings   │  │
│  └──────────────┘                     └──────────────────────┘  │
│                                                                  │
└─────────────────────────────────────────────────────────────────┘

J.24.4.1: Local → Cloud Table Mapping

Tier 1: platform.db (Read-Only Sync)

Local Table	Cloud Table	Sync Direction	Notes
`components`	`platform_components`	Cloud → Local	Component registry
`capabilities`	`platform_capabilities`	Cloud → Local	Capability tags
`triggers`	`platform_triggers`	Cloud → Local	Activation patterns
`component_relationships`	`platform_relationships`	Cloud → Local	Dependencies
`component_usage_stats`	`platform_usage_stats`	Local → Cloud	Usage analytics

Sync Frequency: On startup + daily refresh

Tier 2: org.db (Bidirectional - CRITICAL)

Local Table	Cloud Table	Sync Direction	Notes
`decisions`	`org_decisions`	Bidirectional	CRITICAL - Architecture decisions
`skill_learnings`	`org_skill_learnings`	Bidirectional	CRITICAL - Accumulated learnings
`error_solutions`	`org_error_solutions`	Bidirectional	CRITICAL - Known fixes
`kg_nodes`	`org_kg_nodes`	Bidirectional	Knowledge graph nodes
`kg_edges`	`org_kg_edges`	Bidirectional	Knowledge graph edges
`calibration_history`	`org_calibration_history`	Local → Cloud	Model calibration
`quality_metrics`	`org_quality_metrics`	Local → Cloud	Component health
`projects`	`org_projects`	Bidirectional	Project registry
`project_tracks`	`org_project_tracks`	Bidirectional	Track assignments

Sync Frequency: Real-time (on change) + every 5 minutes

Tier 3: sessions.db (Local → Cloud Primary)

Local Table	Cloud Table	Sync Direction	Notes
`sessions`	`context_sessions`	Local → Cloud	Session metadata
`messages`	`context_messages`	Local → Cloud	Session messages
`tool_analytics`	`context_tool_analytics`	Local → Cloud	Tool usage stats
`token_economics`	`context_token_economics`	Local → Cloud	Token tracking
`task_tracking`	`context_task_tracking`	Bidirectional	Task status
`task_messages`	`context_task_messages`	Local → Cloud	Task-message links
`activity_associations`	`context_activity_associations`	Local → Cloud	Activity grouping
`checkpoints`	`context_checkpoints`	Local → Cloud	Session checkpoints
`sync_queue`	N/A	Local only	Offline queue

Sync Frequency: On session end + every 15 minutes

Tier 4: projects.db (Bidirectional)

Local Table	Cloud Table	Sync Direction	Notes
`projects`	`projects_projects`	Bidirectional	Project metadata
`content_hashes`	`projects_content_hashes`	Local → Cloud	File hashes
`project_embeddings`	`projects_embeddings`	Bidirectional	Semantic embeddings
`exclude_patterns`	`projects_exclude_patterns`	Bidirectional	Ignore rules
`sub_projects`	`projects_sub_projects`	Bidirectional	Sub-project hierarchy
`sprints`	`projects_sprints`	Bidirectional	Sprint planning

Sync Frequency: On project change + every 30 minutes

J.24.4.2: Sync Direction and Frequency

Sync Direction Matrix

Direction	Description	Use Case
Cloud → Local	Cloud is source of truth	Component registry, shared team data
Local → Cloud	Local generates, cloud stores	Session data, analytics
Bidirectional	Both can modify	Decisions, projects, tasks

Sync Frequency Schedule

Tier	Trigger	Interval	Priority
Tier 1	Startup, daily	24h	Low
Tier 2	On change	5 min	Critical
Tier 3	Session end	15 min	Medium
Tier 4	Project change	30 min	Medium

Sync Events

# Sync triggers in order of priority
SYNC_EVENTS = {
    "on_decision_created": ["org.db:decisions"],      # Immediate
    "on_learning_added": ["org.db:skill_learnings"],  # Immediate
    "on_session_end": ["sessions.db:*"],              # On event
    "on_task_complete": ["sessions.db:task_tracking"], # On event
    "scheduled_5min": ["org.db:*"],                   # Background
    "scheduled_15min": ["sessions.db:*"],             # Background
    "scheduled_30min": ["projects.db:*"],             # Background
    "on_startup": ["platform.db:*"],                  # Startup
}

Offline Mode

When network unavailable:

All syncs queue to sessions.db:sync_queue
Maximum queue age: 72 hours (configurable)
Retry attempts: 3 (then manual intervention)
On reconnect: Process queue in FIFO order

J.24.4.3: PostgreSQL Equivalent Schemas

Cloud Database Schema

-- Multi-tenant schema with django-multitenant
-- All tables include tenant_id for RLS

-- ============================================
-- CONTEXT APP (sessions.db equivalent)
-- ============================================

CREATE TABLE context_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    session_id VARCHAR(255) UNIQUE NOT NULL,
    project_id VARCHAR(255),
    machine_id VARCHAR(255),
    llm_provider VARCHAR(50),
    start_time TIMESTAMP WITH TIME ZONE,
    end_time TIMESTAMP WITH TIME ZONE,
    message_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE context_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    message_id VARCHAR(255) UNIQUE NOT NULL,
    session_id VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL,  -- 'user', 'assistant', 'system'
    content TEXT,
    content_hash VARCHAR(64),  -- SHA-256 for deduplication
    project_id VARCHAR(255),
    timestamp TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_messages_session ON context_messages(session_id);
CREATE INDEX idx_messages_hash ON context_messages(content_hash);

CREATE TABLE context_task_tracking (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    task_id VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    status VARCHAR(50) DEFAULT 'pending',  -- pending, in_progress, completed
    track_id VARCHAR(50),  -- ADR-054 track letter
    outcome TEXT,
    outcome_score FLOAT,
    tool_success_count INTEGER DEFAULT 0,
    tool_error_count INTEGER DEFAULT 0,
    user_corrections INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE context_tool_analytics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    tool_name VARCHAR(255) NOT NULL,
    session_id VARCHAR(255),
    duration_ms INTEGER,
    success BOOLEAN,
    error_message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================
-- ORG APP (org.db equivalent - CRITICAL)
-- ============================================

CREATE TABLE org_decisions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    decision_id VARCHAR(255) UNIQUE NOT NULL,
    title VARCHAR(500) NOT NULL,
    rationale TEXT,
    status VARCHAR(50) DEFAULT 'proposed',  -- proposed, accepted, superseded
    message_id VARCHAR(255),
    project_id VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE org_skill_learnings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    skill_name VARCHAR(255) NOT NULL,
    learning TEXT NOT NULL,
    category VARCHAR(100),
    confidence FLOAT DEFAULT 0.5,
    usage_count INTEGER DEFAULT 1,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE org_error_solutions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    error_pattern TEXT NOT NULL,
    solution TEXT NOT NULL,
    success_count INTEGER DEFAULT 1,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE org_kg_nodes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    node_type VARCHAR(100) NOT NULL,
    content_hash VARCHAR(64) UNIQUE,
    content TEXT,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE org_kg_edges (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    from_node UUID REFERENCES org_kg_nodes(id),
    to_node UUID REFERENCES org_kg_nodes(id),
    edge_type VARCHAR(100) NOT NULL,
    weight FLOAT DEFAULT 1.0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================
-- PROJECTS APP (projects.db equivalent)
-- ============================================

CREATE TABLE projects_projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    project_id VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    path TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE projects_embeddings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    project_id VARCHAR(255) NOT NULL,
    file_path TEXT NOT NULL,
    embedding_vector VECTOR(1536),  -- pgvector extension
    model VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================
-- ROW-LEVEL SECURITY (ADR-012)
-- ============================================

ALTER TABLE context_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE context_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE context_task_tracking ENABLE ROW LEVEL SECURITY;
ALTER TABLE org_decisions ENABLE ROW LEVEL SECURITY;
ALTER TABLE org_skill_learnings ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects_projects ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only access their tenant's data
CREATE POLICY tenant_isolation ON context_messages
    FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid);

Data Type Mappings

SQLite Type	PostgreSQL Type	Notes
`INTEGER`	`INTEGER` / `BIGINT`	Direct mapping
`TEXT`	`TEXT` / `VARCHAR`	Direct mapping
`REAL`	`FLOAT` / `DOUBLE PRECISION`	Direct mapping
`BLOB`	`BYTEA`	Binary data
`datetime('now')`	`TIMESTAMP WITH TIME ZONE`	Timezone-aware
FTS tables	`tsvector` + GIN index	Full-text search
JSON text	`JSONB`	Native JSON support

J.24.4.4: Sync Troubleshooting Guide

Common Issues

1. Authentication Failures

Symptoms:

401 Unauthorized errors
Authentication failed messages
Sync queue growing

Diagnosis:

# Test connection
python3 scripts/core/cloud_sync_client.py --test

# Check config
cat ~/.coditect/config.json | jq '.cloud'

Solutions:

# Re-authenticate
export CODITECT_EMAIL="user@example.com"
export CODITECT_PASSWORD="password"
python3 scripts/core/cloud_sync_client.py --test

# Or use access token
export CODITECT_ACCESS_TOKEN="your-token"

2. Sync Queue Backup

Symptoms:

queue_depth increasing in status
Old items in sync_queue table
Data not appearing in cloud

Diagnosis:

# Check queue status
python3 scripts/core/cloud_sync_client.py --status

# Direct query
sqlite3 ~/.coditect-data/context-storage/sync_queue.db \
  "SELECT COUNT(*), MAX(retry_count) FROM sync_queue"

Solutions:

# Process queue manually
python3 scripts/core/cloud_sync_client.py --process-queue

# Clear stuck items (after backup)
sqlite3 ~/.coditect-data/context-storage/sync_queue.db \
  "DELETE FROM sync_queue WHERE retry_count >= 5"

3. Content Hash Duplicates

Symptoms:

duplicate responses from API
Same content synced multiple times
Higher than expected API calls

Diagnosis:

-- Check for duplicates locally
SELECT content_hash, COUNT(*)
FROM messages
GROUP BY content_hash
HAVING COUNT(*) > 1;

Solutions:

Duplicates are handled server-side (idempotent)
No action needed - deduplication is working

4. Offline Mode Stuck

Symptoms:

offline_mode: true in status
No sync attempts
Manual sync works

Diagnosis:

# Check offline flag
python3 scripts/core/cloud_sync_client.py --status | jq '.offline_mode'

# Test network
curl -s https://api.coditect.ai/health/

Solutions:

# Clear offline mode
export CODITECT_OFFLINE=false
python3 scripts/core/cloud_sync_client.py --test

5. Tenant Mismatch

Symptoms:

403 Forbidden errors
Data visible in wrong tenant
Cross-tenant leakage (security issue)

Diagnosis:

# Check tenant ID
python3 scripts/core/cloud_sync_client.py --status | jq '.tenant_id'

# Verify in JWT
echo $CODITECT_ACCESS_TOKEN | cut -d. -f2 | base64 -d | jq '.tenant_id'

Solutions:

# Set correct tenant
export CODITECT_TENANT_ID="correct-tenant-uuid"

# Re-authenticate to get correct tenant
python3 scripts/core/cloud_sync_client.py --test

Diagnostic Commands

# Full status report
python3 scripts/core/cloud_sync_client.py --status

# Test authentication
python3 scripts/core/cloud_sync_client.py --test

# Process offline queue
python3 scripts/core/cloud_sync_client.py --process-queue

# Force sync local → cloud
python3 scripts/core/cloud_sync_client.py --sync-local

# Check local database health
sqlite3 ~/.coditect-data/context-storage/org.db "PRAGMA integrity_check"
sqlite3 ~/.coditect-data/context-storage/sessions.db "PRAGMA integrity_check"

Log Files

Log	Location	Purpose
Sync client	`~/.coditect-data/logs/cloud-sync.log`	API calls, auth
Hooks	`~/.coditect-data/logs/hooks.log`	Hook-triggered syncs
Errors	`~/.coditect-data/logs/sync-errors.log`	Failed syncs

Health Checks

# 1. Local database health
for db in platform.db org.db sessions.db projects.db; do
  echo "=== $db ==="
  sqlite3 ~/.coditect-data/context-storage/$db "PRAGMA integrity_check"
done

# 2. Sync queue health
sqlite3 ~/.coditect-data/context-storage/sync_queue.db \
  "SELECT event_type, COUNT(*), AVG(retry_count) FROM sync_queue GROUP BY event_type"

# 3. Cloud connectivity
curl -s -o /dev/null -w "%{http_code}" https://api.coditect.ai/health/

# 4. Authentication status
python3 scripts/core/cloud_sync_client.py --test | jq '.success'

DATA-DICTIONARY.md - Local table documentation
ER-DIAGRAMS.md - Entity relationship diagrams
DATABASE-SCHEMA.md - Schema overview
ADR-053 - Cloud sync architecture
ADR-118 - Four-tier database architecture
ADR-012 - Multi-tenant isolation

Generated: 2026-02-05 Task: J.24.4.1-J.24.4.4

Overview​

J.24.4.1: Local → Cloud Table Mapping​

Tier 1: platform.db (Read-Only Sync)​

Tier 2: org.db (Bidirectional - CRITICAL)​

Tier 3: sessions.db (Local → Cloud Primary)​

Tier 4: projects.db (Bidirectional)​

J.24.4.2: Sync Direction and Frequency​

Sync Direction Matrix​

Sync Frequency Schedule​

Sync Events​

Offline Mode​

J.24.4.3: PostgreSQL Equivalent Schemas​

Cloud Database Schema​

Data Type Mappings​

J.24.4.4: Sync Troubleshooting Guide​

Common Issues​

1. Authentication Failures​

2. Sync Queue Backup​

3. Content Hash Duplicates​

4. Offline Mode Stuck​

5. Tenant Mismatch​

Diagnostic Commands​

Log Files​

Health Checks​

Related Documentation​

Overview

J.24.4.1: Local → Cloud Table Mapping

Tier 1: platform.db (Read-Only Sync)

Tier 2: org.db (Bidirectional - CRITICAL)

Tier 3: sessions.db (Local → Cloud Primary)

Tier 4: projects.db (Bidirectional)

J.24.4.2: Sync Direction and Frequency

Sync Direction Matrix

Sync Frequency Schedule

Sync Events

Offline Mode

J.24.4.3: PostgreSQL Equivalent Schemas

Cloud Database Schema

Data Type Mappings

J.24.4.4: Sync Troubleshooting Guide

Common Issues

1. Authentication Failures

2. Sync Queue Backup

3. Content Hash Duplicates

4. Offline Mode Stuck

5. Tenant Mismatch

Diagnostic Commands

Log Files

Health Checks

Related Documentation