​
Sync engine architecture

PocketSync’s sync engine is a lightweight, event-driven system designed specifically for SQLite databases. It efficiently manages data synchronization while ensuring data consistency across devices.

​
Core components

​
1. Database layer

System tables

PocketSync maintains several internal tables:

  • __pocketsync_changes: Records all table modifications (insertions, updates, deletions)
  • __pocketsync_version: Tracks version information for each table
  • __pocketsync_device_state: Manages device sync state and last sync timestamp
  • __pocketsync_processed_changes: Tracks applied remote changes
Change tracking

Automatic triggers attached to tables capture:

  • Insert operations: Track new row insertions
  • Update operations: Capture modifications to existing rows
  • Delete operations: Log deletions for proper propagation

All changes are recorded in the __pocketsync_changes table with appropriate metadata.

Data identification

PocketSync adds a ps_global_id column to each table, ensuring:

  • Unique identification across all devices
  • Proper conflict resolution
  • Consistent data tracking

​
2. Change management

Change detection
  • SQL triggers activate on database operations
  • Changes are logged with metadata
  • Version numbers increment automatically
  • Table version numbers are incremented on each modification
Change structure
{
    "timestamp": 1234567890,
    "version": 1,
    "operation": "INSERT|UPDATE|DELETE",
    "table": "table_name",
    "primaryKey": "row_id",
    "data": {
        "field1": "value1",
        "field2": "value2"
    }
}

​
3. Sync process

Real-time Synchronization
  • Database mutations trigger sync process
  • Changes are extracted from __pocketsync_changes
  • Server processes and acknowledges changes
  • Changes marked as “synced” after confirmation
Offline operation
  • Changes queue locally during offline periods
  • Upon reconnection, device reports its last sync timestamp
  • Server sends all missed changes
  • Changes are processed in correct order

​
4. Server-side processing

Conflict resolution
  • Uses “Last Write Wins” strategy by default
  • Based on change version and timestamp
  • Customizable for specific needs
Change broadcasting
  • Changes are sent to connected devices
  • Uses WebSocket for real-time updates
  • Ensures consistent data state
Data optimization
  • Merges multiple updates to same row
  • Removes redundant operations
  • Maintains operation order