ADR-192: Bidirectional Context Memory Sync Architecture
Status: Accepted SUPERSEDED
Date: 2025-12-29
Superseded By: ADR-044 - Custom REST Sync Architecture
Author: Hal Casteel
Stakeholders: Platform Engineering, Backend, Frontend, DevOps
⚠️ SUPERSESSION NOTICE (2025-12-29)
This ADR has been superseded by ADR-044 due to FSL license concerns.
Key Finding: PowerSync uses the Functional Source License (FSL) which includes a "Competing Use" restriction. JourneyApps (PowerSync owner) offers a mobile app builder with offline sync - potentially in competition with CODITECT's development platform.
Legal Risk: Using PowerSync in CODITECT SaaS could constitute license violation.
Replacement Architecture: Custom REST API sync (see ADR-044)
- Same SQLite client-side storage
- Django/FastAPI REST endpoints for push/pull
- PostgreSQL as source of truth
- Zero third-party sync dependencies
5-Year TCO Savings: $12,900 (Custom REST $41,400 vs PowerSync $54,300)
Context
CODITECT manages AI session context memory across multiple machines, teams, and tenants. The context memory originates from Claude Code sessions stored locally as JSONL files, processed into a local SQLite database, and must be synchronized bidirectionally with a central PostgreSQL database that serves as the primary record of truth.
The Problem
Users run Claude Code on multiple machines (desktop, laptop, cloud workstations). Each machine generates:
~/.claude/projects/*/*.jsonl- Raw Claude Code session files- CODITECT processes these into unique messages in local databases (ADR-118 Four-Tier:
org.dbfor decisions,sessions.dbfor messages)
Without synchronization:
- Context memory is fragmented across machines
- Teams cannot share learned patterns and decisions
- Multi-tenant SaaS requires centralized data management
Requirements
- Local → Cloud Push: SQLite changes push to PostgreSQL (cloud master)
- Cloud → Local Sync: PostgreSQL syncs to all SQLite clients
- PostgreSQL as Source of Truth: Central authority for all context data
- Multi-client Merge: Multiple SQLite instances write to same PostgreSQL
- Multi-tenant Isolation: Tenant data completely separated
- Offline Support: Local-first with sync on reconnect
- Conflict Resolution: Handle concurrent writes from multiple clients
Decision
Implement a bidirectional sync architecture using:
- PowerSync for PostgreSQL → SQLite downstream sync
- REST API for SQLite → PostgreSQL upstream writes
- PostgreSQL as the central source of truth
Architecture
Complete Data Flow
┌─────────────────────────────────────────────────────────────────────────────┐
│ LOCAL CLIENT (per machine) │
│ │
│ ┌──────────────────┐ ┌──────────────────┐ ┌──────────────────┐ │
│ │ ~/.claude/ │ │ CODITECT │ │ Local SQLite │ │
│ │ projects/ │───▶│ Session │───▶│ (ADR-118) │ │
│ │ *.jsonl │ │ Processor │ │ │ │
│ │ │ │ │ │ - org.db │ │
│ │ (Claude Code │ │ - Deduplication │ │ - sessions.db │ │
│ │ raw sessions) │ │ - Extraction │ │ - platform.db │ │
│ └──────────────────┘ │ - Enrichment │ └────────┬─────────┘ │
│ └──────────────────┘ │ │
│ PUSH │ PULL │
│ (API) │ (PowerSync) │
│ │ │
└───────────────────────────────────────────────────────────┼─────────────────┘
│
┌──────────────────────────────┴─────────────────┐
│ │
▼ ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│ GKE CLOUD CLUSTER │
│ │
│ ┌──────────────────┐ ┌──────────────────┐ │
│ │ CODITECT API │ │ PostgreSQL │ │
│ │ (Backend) │────────▶│ (Cloud SQL) │◀────┐ │
│ │ │ writes │ │ │ │
│ │ POST /api/sync │ │ PRIMARY RECORD │ │ Logical │
│ │ - messages │ │ OF TRUTH │ │ Replication │
│ │ - decisions │ │ │ │ │
│ │ - patterns │ │ - All tenants │ │ │
│ └──────────────────┘ │ - All users │ │ │
│ │ - All machines │ │ │
│ │ - Merged data │ │ │
│ └────────┬─────────┘ │ │
│ │ │ │
│ ▼ │ │
│ ┌──────────────────┐ │ │
│ │ PowerSync │─────┘ │
│ │ Service │ │
│ │ │ │
│ │ sync.coditect.ai│ │
│ └────────┬─────────┘ │
│ │ │
└────────────────────────────────────────┼────────────────────────────────────┘
│
│ Sync Protocol (downstream)
│
┌───────────────────────────────┼───────────────────────────────┐
│ │ │
▼ ▼ ▼
┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐
│ Machine A │ │ Machine B │ │ Machine C │
│ (Desktop) │ │ (Laptop) │ │ (Team Member) │
│ │ │ │ │ │
│ SQLite ◀──────┼────────────┼─────────────────┼────────────┼──────▶ SQLite │
│ (ADR-118) │ │ SQLite │ │ (ADR-118) │
│ │ │ (ADR-118) │ │ │
│ Same user, │ │ Same user, │ │ Same tenant, │
│ different │ │ different │ │ different │
│ machine │ │ machine │ │ user │
└─────────────────┘ └─────────────────┘ └─────────────────┘
Implementation Details
1. Upstream Push (SQLite → PostgreSQL)
Endpoint: POST /api/sync/push
# Client-side push logic
async def push_to_cloud(local_db: sqlite3.Connection):
# Get unpushed changes since last sync
changes = local_db.execute("""
SELECT * FROM messages
WHERE updated_at > ? AND sync_status = 'pending'
ORDER BY updated_at
LIMIT 1000
""", [last_sync_timestamp])
# Push to cloud API
response = await api.post('/api/sync/push', {
'tenant_id': config.tenant_id,
'user_id': config.user_id,
'machine_id': config.machine_id,
'changes': [dict(row) for row in changes]
})
# Mark as synced
for change in changes:
local_db.execute("""
UPDATE messages SET sync_status = 'synced',
cloud_id = ? WHERE id = ?
""", [response.cloud_ids[change.id], change.id])
2. Conflict Resolution Strategy
| Scenario | Resolution |
|---|---|
| Same message from multiple machines | Deduplicate by content_hash |
| Concurrent edits to same record | Last-write-wins by updated_at |
| Delete vs Update conflict | Update wins (soft delete) |
| New record with same ID | Use UUID, no collision |
Component Roles
| Component | Role | Direction |
|---|---|---|
| Claude Code | Generate raw session data | Source |
| CODITECT Processor | Deduplicate, extract, enrich | Local transform |
| Local SQLite | Local context storage (ADR-118) | Local cache |
| CODITECT API | Handle push requests | Upstream gateway |
| PostgreSQL | Primary record of truth | Central authority |
| PowerSync | Stream changes to clients | Downstream sync |
| Litestream | Backup local SQLite to GCS | Disaster recovery |
Consequences
Positive
- Unified context across all user machines
- Team collaboration via shared patterns
- Multi-tenant isolation at database level
- Offline-first with eventual consistency
- Scalable with PostgreSQL + PowerSync
Negative
- Eventual consistency (not real-time for writes)
- Complexity of sync state management
- Infrastructure cost (PostgreSQL + PowerSync + MongoDB)
- License risk (FSL "Competing Use" clause)
Risks & Mitigations
| Risk | Mitigation |
|---|---|
| Data loss during sync | Litestream backup, sync acknowledgments |
| Sync conflicts | Content-hash dedup, last-write-wins |
| Network partitions | Queue writes locally, sync on reconnect |
| Large datasets | Pagination, incremental sync |
| FSL License violation | Superseded by ADR-044 |
Related ADRs
- ADR-042: Litestream for SQLite backup to GCS
- ADR-044: Custom REST Sync Architecture (supersedes this)
- ADR-118: Four-Tier Database Architecture
- ADR-CX: Context Extraction System