ADR-053: Cloud Context Sync Architecture

Status

ACCEPTED (2026-02-03)

Updated: Reflects ADR-118 Four-Tier Database Architecture (replaces deprecated context.db)

Context

CODITECT's local-first architecture stores session context in SQLite databases on user machines (ADR-118). However, enterprise customers require:

1. Multi-device sync - Same user, different machines
2. Multi-user collaboration - Team members sharing context
3. Multi-tenant isolation - Complete data separation between customers (ADR-012)
4. Audit trails - Compliance and security requirements
5. Single source of truth - Avoid conflicts and data loss
6. Offline capability - Work continues without network

Local-First Limitations

The local SQLite approach fails for:

• Contractors working across multiple client projects
• Team members on different workstations
• Auditors reviewing project history
• Disaster recovery and backup
• Enterprise compliance requirements

Decision

Implement a hybrid local-cloud sync architecture with:

1. Local SQLite as cache/offline fallback (ADR-118 Four-Tier)
2. Cloud PostgreSQL as single source of truth
3. django-multitenant for tenant isolation (ADR-009)
4. Cursor-based polling for sync
5. Content-hash deduplication

Architecture Overview

┌─────────────────────────────────────────────────────────────────────────────┐
│                    CODITECT CLOUD CONTEXT SYNC                               │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   LOCAL (Claude Code + Hooks)                                               │
│   ┌─────────────────────────────────────────────────────────────────────┐   │
│   │  TodoWrite ──▶ dispatcher.sh ──▶ task-plan-sync.py                  │   │
│   │                                         │                            │   │
│   │                                         ▼                            │   │
│   │                              cloud_sync_client.py                    │   │
│   │                                         │                            │   │
│   │   ┌─────────────────┐                   │                            │   │
│   │   │ Local SQLite    │◄──────────────────┤ (offline cache)            │   │
│   │   │ ADR-118 Tiers:  │                   │                            │   │
│   │   │ • org.db (T2)   │  Critical data    │                            │   │
│   │   │ • sessions.db   │  Regenerable      │                            │   │
│   │   │   (T3)          │                   │                            │   │
│   │   └─────────────────┘                   │                            │   │
│   └─────────────────────────────────────────┼────────────────────────────┘   │
│                                             │                                │
│                                             ▼                                │
│   CLOUD (api.coditect.ai)                                                   │
│   ┌─────────────────────────────────────────────────────────────────────┐   │
│   │                                                                      │   │
│   │   auth.coditect.ai                                                  │   │
│   │   ┌─────────────────────────────────────────────────────────────┐   │   │
│   │   │  License Key ──▶ Validate ──▶ JWT + tenant_id               │   │   │
│   │   └─────────────────────────────────────────────────────────────┘   │   │
│   │                              │                                       │   │
│   │                              ▼                                       │   │
│   │   Django REST Framework + django-multitenant                        │   │
│   │   ┌─────────────────────────────────────────────────────────────┐   │   │
│   │   │                                                              │   │   │
│   │   │   POST /api/v1/context/push     ──▶  ContextMessage.create  │   │   │
│   │   │   GET  /api/v1/context/pull     ──▶  ContextMessage.filter  │   │   │
│   │   │   GET  /api/v1/context/status   ──▶  SyncStats.aggregate    │   │   │
│   │   │   POST /api/v1/context/tasks/   ──▶  TaskTracking.create    │   │   │
│   │   │                                                              │   │   │
│   │   │   set_current_tenant(tenant)  ──▶  Auto-filter all queries  │   │   │
│   │   │                                                              │   │   │
│   │   └─────────────────────────────────────────────────────────────┘   │   │
│   │                              │                                       │   │
│   │                              ▼                                       │   │
│   │   PostgreSQL (Cloud SQL) + RLS (ADR-012)                            │   │
│   │   ┌─────────────────────────────────────────────────────────────┐   │   │
│   │   │                                                              │   │   │
│   │   │   Row-Level Security enforces tenant isolation              │   │   │
│   │   │                                                              │   │   │
│   │   │   ┌──────────────┐ ┌──────────────┐ ┌──────────────┐        │   │   │
│   │   │   │ Tenant: Acme │ │ Tenant: Beta │ │ Tenant: Corp │        │   │   │
│   │   │   │ messages: 5K │ │ messages: 2K │ │ messages: 10K│        │   │   │
│   │   │   │ tasks: 500   │ │ tasks: 200   │ │ tasks: 1000  │        │   │   │
│   │   │   └──────────────┘ └──────────────┘ └──────────────┘        │   │   │
│   │   │                                                              │   │   │
│   │   └─────────────────────────────────────────────────────────────┘   │   │
│   │                                                                      │   │
│   └─────────────────────────────────────────────────────────────────────┘   │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

Local Database Mapping (ADR-118)

Local Tier	Database	Synced Data	Sync Direction
Tier 1	platform.db	Components	Cloud → Local (read-only)
Tier 2	org.db	Decisions, skill_learnings, error_solutions	Bidirectional
Tier 3	sessions.db	Messages, task_tracking, tool_analytics	Bidirectional
Tier 4	projects.db	Project-specific context	Bidirectional

Note: context.db is DEPRECATED per ADR-118. Use org.db for critical data, sessions.db for regenerable data.

Cloud Sync Client Implementation

# scripts/core/cloud_sync_client.py
from dataclasses import dataclass
from scripts.core.paths import get_org_db_path, get_sessions_db_path

@dataclass
class TaskEvent:
    """Task sync payload."""
    task_id: str
    description: str
    status: str
    outcome: str = ""
    project_id: str = ""
    tool_success_count: int = 0
    tool_error_count: int = 0
    user_corrections: int = 0

class CloudSyncClient:
    """
    Sync local context to cloud PostgreSQL.

    ADR-053: Hybrid local-cloud architecture
    ADR-118: Uses org.db (Tier 2) for critical data
    """

    def __init__(self, api_url: str = "https://api.coditect.ai"):
        self.api_url = api_url
        self.org_db = get_org_db_path()      # Tier 2: Critical
        self.sessions_db = get_sessions_db_path()  # Tier 3: Regenerable
        self._token = None
        self._tenant_id = None

    def authenticate(self, license_key: str) -> bool:
        """Authenticate via license key, receive JWT with tenant_id."""
        response = requests.post(
            f"{self.api_url}/api/v1/auth/license/",
            json={"license_key": license_key}
        )
        if response.ok:
            data = response.json()
            self._token = data["access_token"]
            self._tenant_id = data["tenant_id"]
            return True
        return False

    def push_task(self, task: TaskEvent) -> bool:
        """Push task tracking to cloud."""
        if not self._token:
            return self._queue_for_later(task)

        response = requests.post(
            f"{self.api_url}/api/v1/context/tasks/",
            headers={"Authorization": f"Bearer {self._token}"},
            json=asdict(task)
        )
        return response.ok

    def pull_decisions(self, since_cursor: int = 0) -> List[dict]:
        """Pull decisions from cloud to local org.db."""
        response = requests.get(
            f"{self.api_url}/api/v1/context/decisions/",
            headers={"Authorization": f"Bearer {self._token}"},
            params={"cursor": since_cursor}
        )
        if response.ok:
            decisions = response.json()["decisions"]
            self._store_to_org_db(decisions)
            return decisions
        return []

    def _queue_for_later(self, task: TaskEvent) -> bool:
        """Queue sync when offline (ADR-053 offline mode)."""
        with sqlite3.connect(self.sessions_db) as conn:
            conn.execute("""
                INSERT INTO sync_queue (payload, created_at)
                VALUES (?, datetime('now'))
            """, [json.dumps(asdict(task))])
        return True

Sync Flow

1. TodoWrite triggered in Claude Code
   │
2. PostToolUse hook: task-plan-sync.py
   │
3. cloud_sync_client.py
   │
   ├── Online: POST /api/v1/context/tasks/
   │   │
   │   ├── auth.coditect.ai validates license
   │   │
   │   ├── set_current_tenant(tenant) from JWT
   │   │
   │   └── TaskTracking.objects.create(...)
   │       │
   │       └── RLS ensures tenant isolation (ADR-012)
   │
   └── Offline: Queue to sessions.db sync_queue table
       │
       └── Later: process_sync_queue() → POST /api/v1/context/tasks/

API Endpoints

Endpoint	Method	Purpose	Auth
/api/v1/auth/license/	POST	License → JWT	License key
/api/v1/context/push	POST	Push messages	JWT
/api/v1/context/pull	GET	Pull messages (cursor-based)	JWT
/api/v1/context/status	GET	Sync statistics	JWT
/api/v1/context/tasks/	POST	Push task tracking	JWT
/api/v1/context/tasks/	GET	Query tasks	JWT
/api/v1/context/decisions/	GET	Pull decisions	JWT
/api/v1/context/learnings/	GET	Pull skill learnings	JWT

Multi-Tenant Isolation

django-multitenant provides automatic query filtering:

# In view after authentication:
set_current_tenant(tenant)

# All subsequent queries automatically filtered:
TaskTracking.objects.all()  # Only returns tasks for current tenant
ContextMessage.objects.filter(user_id=user_id)  # Scoped to tenant

# RLS enforces at database level (ADR-012)
# Even raw SQL is filtered by tenant_id

Offline Mode

class OfflineSyncManager:
    """
    Manage offline queue for eventual sync.

    ADR-053: Offline-capable architecture
    """

    def __init__(self):
        self.sessions_db = get_sessions_db_path()

    def queue_item(self, action: str, payload: dict):
        """Queue item for later sync."""
        with sqlite3.connect(self.sessions_db) as conn:
            conn.execute("""
                INSERT INTO sync_queue (action, payload, created_at, attempts)
                VALUES (?, ?, datetime('now'), 0)
            """, [action, json.dumps(payload)])

    def process_queue(self, client: CloudSyncClient) -> int:
        """Process queued items when online."""
        synced = 0
        with sqlite3.connect(self.sessions_db) as conn:
            items = conn.execute("""
                SELECT id, action, payload FROM sync_queue
                WHERE attempts < 3
                ORDER BY created_at ASC
                LIMIT 100
            """).fetchall()

            for item_id, action, payload in items:
                if self._sync_item(client, action, json.loads(payload)):
                    conn.execute("DELETE FROM sync_queue WHERE id = ?", [item_id])
                    synced += 1
                else:
                    conn.execute(
                        "UPDATE sync_queue SET attempts = attempts + 1 WHERE id = ?",
                        [item_id]
                    )
        return synced

Deduplication Strategy

Content-hash deduplication prevents duplicate syncs:

import hashlib

def compute_content_hash(content: dict) -> str:
    """Compute SHA-256 hash for deduplication."""
    canonical = json.dumps(content, sort_keys=True)
    return hashlib.sha256(canonical.encode()).hexdigest()

# On push, server checks hash
class ContextPushView(APIView):
    def post(self, request):
        content_hash = compute_content_hash(request.data)

        # Check for existing (idempotent)
        existing = ContextMessage.objects.filter(
            content_hash=content_hash
        ).first()

        if existing:
            return Response({"status": "duplicate", "id": existing.id})

        # Create new
        msg = ContextMessage.objects.create(
            content_hash=content_hash,
            **request.data
        )
        return Response({"status": "created", "id": msg.id})

Deployment Modes

┌─────────────────────────────────────────────────────────────────────────────┐
│                    DEPLOYMENT MODES                                          │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│   MODE 1: Local Claude Code (Current - Implemented)                         │
│   ┌─────────────────────────────────────────────────────────────────────┐   │
│   │  User Machine ──▶ SQLite (ADR-118) ──▶ REST API ──▶ PostgreSQL     │   │
│   └─────────────────────────────────────────────────────────────────────┘   │
│                                                                              │
│   MODE 2: Licensed Docker (Future)                                          │
│   ┌─────────────────────────────────────────────────────────────────────┐   │
│   │  Docker Container ──▶ License check ──▶ REST API ──▶ PostgreSQL    │   │
│   └─────────────────────────────────────────────────────────────────────┘   │
│                                                                              │
│   MODE 3: Cloud Workstations (Future - ADR-010)                             │
│   ┌─────────────────────────────────────────────────────────────────────┐   │
│   │  GCP Workstation ──▶ Direct DB (same VPC) ──▶ PostgreSQL           │   │
│   └─────────────────────────────────────────────────────────────────────┘   │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

Consequences

Positive

1. True multi-device sync - Work continues seamlessly across machines
2. Team collaboration - Shared context for team members
3. Complete isolation - django-multitenant + RLS ensures data separation
4. Offline-capable - Local queue ensures no data loss
5. Audit compliance - Cloud storage enables audit trails
6. Disaster recovery - Cloud backups protect against local failures
7. ADR-118 compliant - Uses proper database tiers

Negative

1. Network dependency - Requires connectivity for real-time sync
2. Latency - Cloud round-trip adds delay (mitigated by async)
3. Cost - Cloud storage and compute costs
4. Complexity - More moving parts than local-only

Mitigations

1. Offline queue - Local SQLite queues syncs when offline
2. Async sync - Non-blocking background sync
3. Cost optimization - Retention policies, compression
4. Monitoring - Comprehensive observability

Implementation Status

Phase 1: Local Integration ✅ Complete

• Create cloud_sync_client.py
• Create dispatcher.sh for hook routing
• Update hooks for cloud sync
• ADR-118 database tier integration

Phase 2: Cloud Endpoints ✅ Complete

• TaskTracking model in context app
• Task sync endpoints
• URL routing
• Deploy to GKE

Phase 3: End-to-End Testing 🔄 In Progress

• Test push from local to cloud
• Test pull from cloud to local
• Test offline queue processing
• Test multi-tenant isolation

Phase 4: Advanced Features 📋 Planned

• Team-level context sharing (ADR-045)
• Project-level isolation
• Real-time sync (WebSocket)
• Conflict resolution UI

Related

• ADR-009: Multi-Tenant Architecture
• ADR-012: Data Isolation Strategy
• ADR-044: Custom REST Sync Architecture
• ADR-045: Team/Project Context Sync
• ADR-052: Intent-Aware Context Management
• ADR-089: Two-Database Architecture (superseded by ADR-118)
• ADR-103: Four-Database Separation (superseded by ADR-118)
• ADR-118: Four-Tier Database Architecture

References

• Cloud Backend: coditect-cloud-infra/backend/context/
• Local Client: coditect-core/scripts/core/cloud_sync_client.py
• Hooks: coditect-core/hooks/task-plan-sync.py

---

Track: J (Memory Intelligence) Task: F.12.2.5