ADR 044 Custom REST Sync Architecture

ADR-044: Custom REST Sync Architecture

Status: Accepted Date: 2025-12-29 Author: Hal Casteel Supersedes: ADR-043 (PowerSync PostgreSQL-SQLite Sync) Stakeholders: Platform Engineering, Backend, Frontend, DevOps, Legal

Decision

CODITECT will use a custom REST API sync architecture instead of PowerSync for bidirectional context memory synchronization.

Key change: Replace PowerSync (FSL license) with custom FastAPI/Django REST endpoints for SQLite ↔ PostgreSQL synchronization.

Context

MoE Evaluation Process

This decision was made through a rigorous Mixture of Experts (MoE) evaluation with 4 specialized judges:

1. Technical Architecture Judge - Evaluated scalability, reliability, integration fit
2. License & Security Judge - Assessed legal risk, compliance, vendor dependency
3. Operations Judge - Scored deployment complexity, maintenance burden
4. Cost-Benefit Judge - Analyzed TCO, ROI, time-to-market

Options Evaluated

Option	Description
A: PostgreSQL-Everywhere	Local postgresql-embedded + Cloud PostgreSQL + pglogical
B: PowerSync	SQLite + PowerSync (FSL license) + PostgreSQL
C: ElectricSQL	SQLite + ElectricSQL (Apache 2.0) + PostgreSQL
D: Custom REST Sync	SQLite + Custom REST API + PostgreSQL

MoE Judge Scores

Consolidated Scoring Matrix

Judge	Option A (PG-Everywhere)	Option B (PowerSync)	Option C (ElectricSQL)	Option D (Custom REST)
Technical Architecture	8.10/10	7.70/10	8.40/10	6.15/10
License & Security	8.80/10	4.65/10 ⛔	8.25/10	8.85/10 ✅
Operations	7.05/10	5.00/10	6.55/10	7.75/10 ✅
Cost-Benefit	4.85/10	6.95/10	5.15/10	7.20/10 ✅
WEIGHTED AVERAGE	7.20/10	6.08/10	7.09/10	7.49/10 ✅

5-Year Total Cost of Ownership

Option	Development	Infrastructure (5yr)	Maintenance (5yr)	Total TCO
A: PostgreSQL-Everywhere	$45,000	$36,000	$60,000	$117,000
B: PowerSync	$15,000	$17,700	$36,000	$54,300
C: ElectricSQL	$30,000	$12,000	$108,000	$150,000
D: Custom REST Sync	$18,000	$3,000	$25,200	$41,400 ✅

Winner: Option D (Custom REST Sync) - $12,900 savings vs PowerSync over 5 years.

Critical Findings

PowerSync License Disqualification

FSL (Functional Source License) "Competing Use" clause is legally problematic:

"You may not use the Software for any Competing Use. 'Competing Use' means making available any product or service that substitutes for the Products."

• JourneyApps (PowerSync owner) offers a no-code mobile app builder with offline sync
• CODITECT's mobile-first development platform could be interpreted as "competing"
• Legal risk of license violation is unacceptable for SaaS product
• FSL converts to Apache 2.0 after 2 years, but we cannot wait

Judge 2 License Score: 4.65/10 - CRITICAL legal risk

ElectricSQL Development Paused

• ElectricSQL paused active development in November 2024
• Apache 2.0 license is safe, but maintenance burden shifts to CODITECT
• Community continuation uncertain
• Not recommended for production dependency

PostgreSQL-Everywhere Overkill

• Client installation: 50-100MB (postgresql-embedded) vs 1MB (SQLite)
• Claude Code already uses SQLite for local storage (~/.claude/*.jsonl → context.db)
• Changing this would break existing workflows
• Resource overhead on developer machines unacceptable

Architecture

Custom REST Sync Data Flow

┌─────────────────────────────────────────────────────────────────────────────┐
│                          LOCAL CLIENT (per machine)                          │
│                                                                              │
│  ┌──────────────────┐    ┌──────────────────┐    ┌──────────────────┐      │
│  │  ~/.claude/      │    │    CODITECT      │    │   Local SQLite   │      │
│  │  projects/       │───▶│    Session       │───▶│   context.db     │      │
│  │  *.jsonl         │    │    Processor     │    │                  │      │
│  │                  │    │                  │    │  - messages      │      │
│  │  (Claude Code    │    │  - Deduplication │    │  - decisions     │      │
│  │   raw sessions)  │    │  - Extraction    │    │  - patterns      │      │
│  └──────────────────┘    │  - Enrichment    │    │  - errors        │      │
│                          └──────────────────┘    └────────┬─────────┘      │
│                                                           │                 │
│                                                    PUSH   │  PULL           │
│                                                    (REST) │  (REST)         │
│                                                           │                 │
└───────────────────────────────────────────────────────────┼─────────────────┘
                                                            │
                              ┌──────────────────────────────┴─────────────────┐
                              │                                                │
                              ▼                                                ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                              GKE CLOUD CLUSTER                               │
│                                                                              │
│  ┌──────────────────┐         ┌──────────────────┐                         │
│  │   CODITECT API   │         │   PostgreSQL     │                         │
│  │   (FastAPI/      │────────▶│   (Cloud SQL)    │                         │
│  │    Django)       │ writes  │                  │                         │
│  │                  │         │  PRIMARY RECORD  │                         │
│  │  POST /api/sync  │         │  OF TRUTH        │                         │
│  │  GET  /api/sync  │         │                  │                         │
│  │  - messages      │         │  - All tenants   │                         │
│  │  - decisions     │         │  - All users     │                         │
│  │  - patterns      │         │  - All machines  │                         │
│  └──────────────────┘         └──────────────────┘                         │
│                                                                              │
└──────────────────────────────────────────────────────────────────────────────┘
                                         │
                                         │ REST API (downstream polling)
                                         │
         ┌───────────────────────────────┼───────────────────────────────┐
         │                               │                               │
         ▼                               ▼                               ▼
┌─────────────────┐            ┌─────────────────┐            ┌─────────────────┐
│   Machine A     │            │   Machine B     │            │   Machine C     │
│   (Desktop)     │            │   (Laptop)      │            │   (Team Member) │
│                 │            │                 │            │                 │
│   SQLite ◀──────┼────────────┼─────────────────┼────────────┼──────▶ SQLite   │
│   context.db    │            │   SQLite        │            │   context.db    │
└─────────────────┘            └─────────────────┘            └─────────────────┘

API Endpoints

Endpoint	Method	Purpose
/api/sync/push	POST	Push local changes to cloud (batch)
/api/sync/pull	GET	Pull changes since last sync cursor
/api/sync/status	GET	Get sync state for client
/api/sync/resolve	POST	Manually resolve conflicts

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 collision	UUID-based IDs prevent collision

Implementation Plan

Phase 1: Backend API (Weeks 1-2)

• FastAPI/Django sync endpoints
• PostgreSQL schema for context tables with tenant_id
• Authentication via existing JWT system

Phase 2: Conflict Resolution (Weeks 3-4)

• Last-write-wins with updated_at timestamps
• Content-hash deduplication
• Soft delete support

Phase 3: Client Library (Weeks 5-6)

• Python sync client with retry logic
• Background sync daemon
• Offline queue management

Phase 4: Testing & Hardening (Weeks 7-8)

• Integration tests
• Load testing (100+ concurrent clients)
• Security review

Total Implementation Time: 8 weeks

Consequences

Positive

• Zero legal risk - No third-party sync library licenses
• Lowest TCO - $41,400 over 5 years ($12,900 less than PowerSync)
• Full control - Custom sync logic, no vendor lock-in
• Simple architecture - 3 services (Django, PostgreSQL, Redis) vs 5 for PowerSync
• Technology familiarity - Standard Django/FastAPI patterns

Negative

• Development time - 8 weeks vs 6 weeks for PowerSync
• Custom code maintenance - ~7 hours/month ongoing
• No real-time sync - Eventual consistency (acceptable for CODITECT use case)
• Manual conflict resolution - Must implement ourselves

Risks & Mitigations

Risk	Probability	Impact	Mitigation
Timeline overrun	Medium	High	Start immediately, reduce scope if needed
Conflict resolution bugs	Medium	Medium	Comprehensive test suite
Scaling bottlenecks	Low	Medium	Cloud Run auto-scales
Security vulnerabilities	Low	High	Third-party pentest ($15-25K)

Migration from ADR-043

ADR-043 (PowerSync architecture) is now superseded. Migration steps:

1. Remove PowerSync dependencies from Kubernetes manifests
2. Remove PowerSync service from config/powersync/kubernetes/
3. Implement REST sync endpoints per this ADR
4. Update client sync logic to use REST instead of PowerSync protocol
5. Archive ADR-043 with status: Superseded by ADR-044

Files

File	Purpose
backend/context/views.py	Sync API endpoints (TODO)
backend/context/models.py	Context tables with tenant_id (TODO)
backend/context/sync_client.py	Client sync library (TODO)
scripts/context-sync.py	CLI sync tool (TODO)

Related ADRs

• ADR-042: Litestream for SQLite backup (standalone mode only)
• ADR-043: PowerSync architecture (SUPERSEDED by this ADR)
• ADR-045: Team/Project Context Sync Architecture (EXTENDS this ADR)
• ADR-007: Django multi-tenant architecture
• ADR-009: Multi-tenant SaaS architecture

References

• MoE Evaluation Session - Full judge outputs
• FSL License - Functional Source License terms
• ElectricSQL Pause Announcement - November 2024

---

Decision Made: 2025-12-29 MoE Process: 4 specialized judges, weighted scoring Confidence Level: 85% (High) Review Date: Q1 2026 (post-pilot evaluation)