Sync Infrastructure: SurrealDB Cloud Integration

[malibio]

Overview

Implement sync infrastructure for SurrealDB Cloud integration, enabling multi-device access with local-first performance. This includes LIVE SELECT subscriptions, conflict detection, and offline operation queuing.

Architecture Reference

See docs/architecture/architecture-target-state.md Section 6: Sync Architecture
See docs/architecture/experiments/surrealdb-cloud-poc-plan.md for PoC details

Architecture

┌──────────────────┐         WebSocket          ┌──────────────────┐
│   Desktop App    │◄─────────────────────────►│  SurrealDB Cloud │
│  (Local RocksDB) │       LIVE SELECT          │  (Coordination)  │
└──────────────────┘                            └──────────────────┘
        │                                               │
        │ Source of Truth                               │ RLS Enforcement
        │ (user's data)                                 │ Rate Limiting
        ▼                                               ▼
   Full Offline                                   Managed Scaling
   Functionality                                  (their problem)

Components

1. SurrealDB Cloud Connection

• WebSocket connection to cloud instance
• JWT authentication with $auth claims
• Automatic reconnection handling

2. LIVE SELECT Subscriptions

Real-time sync via SurrealDB's LIVE SELECT:

LIVE SELECT * FROM node;
LIVE SELECT * FROM edge;

3. Conflict Resolution: Conflicted Copies

Using the Dropbox model—don't auto-merge, create copies:

1. Detection: OCC via version field
2. Rejection: If cloud version higher, update fails
3. Resolution: Create new node with (Conflicted Copy YYYY-MM-DD) suffix
4. User Action: Manual merge, user stays in control

4. Offline Operation Queue

Queue operations when offline, replay when reconnected:

struct OfflineOperation {
    id: Uuid,
    operation: Operation,  // Create, Update, Delete
    node_id: String,
    payload: Value,
    created_at: DateTime<Utc>,
    retry_count: u32,
}

Acceptance Criteria

Phase 1: Cloud Connection

• SurrealDB Cloud WebSocket connection
• JWT authentication flow
• Connection state management (connected, disconnected, reconnecting)
• Automatic reconnection with exponential backoff

Phase 2: LIVE SELECT Integration

• Subscribe to node changes
• Subscribe to edge changes
• Handle CREATE, UPDATE, DELETE events
• Apply remote changes to local DB

Phase 3: Conflict Detection

• Version field checked on every update
• OCC rejection when version mismatch
• Conflict event emitted for UI handling

Phase 4: Conflict Resolution

• Conflicted copy creation
• Naming convention: {title} (Conflicted Copy {date})
• UI indicator for conflicted nodes
• Manual merge UI (future, can be separate issue)

Phase 5: Offline Queue

• Queue operations when offline
• Persist queue to survive app restart
• Replay queue on reconnection
• Handle replay failures gracefully

Phase 6: Quality

• Integration tests with mock cloud
• Conflict scenario tests
• Offline/online transition tests
• Code passes bun run quality:fix
• Tests pass (bun run test:all)

Technical Notes

Authentication Flow

1. User logs in via OAuth/email
2. App receives JWT with claims: id, role, type_grants
3. JWT used for SurrealDB Cloud connection
4. RLS evaluated on every query/subscription

Sync Policy Integration

Sync behavior controlled by policies (#784):

• What to pull (lifecycle states, age, node types)
• What to push (immediate vs batched)
• Local eviction strategy

Prerequisites

• SurrealDB Cloud PoC completed (validates LIVE SELECT, ABAC)
• Security layer (Security Layer: ABAC, RLS, and Encryption #789) for authentication

Dependencies

• Security Layer: ABAC, RLS, and Encryption #789 - Security Layer (authentication)
• Sync Policy Infrastructure #784 - Sync Policy Infrastructure
• SurrealDB Cloud PoC (separate repo)

Related Issues

• Add lifecycle_status as Core Node Property with Semantic Search Exclusion #755 - Lifecycle Status (affects sync filtering)
• Policy Evaluation Engine #785 - Policy Evaluation Engine

[malibio]

Race Condition Fix - Phase 1 Complete (PR #861)

We've implemented Phase 1 of the race condition fixes identified by senior architect review:

✅ Phase 1 - Completed

• moveNode now returns updated Node - Backend returns node with new version, frontend syncs from response instead of guessing version+1. This fixes ~90% of version conflicts during rapid indent/outdent.

📋 Phase 2 - Operation Queue (Recommended for Phase 2 of Sync)

Problem: Current indent/outdent awaits each moveNode sequentially for OCC correctness. This serializes rapid operations.

Solution:

• Operation queue with dependency tracking
• Transactional batch operations
• Allow concurrent independent moves, sequence dependent ones

📋 Phase 3 - Cloud Sync Preparation

For proper multi-device sync:

• Add Hybrid Logical Clocks (HLC) for causality tracking
• Operation log for sync replay
• Formalize fractional indexing as proper CRDT

These align with the existing acceptance criteria in this issue (Phases 2-5).