Data Synchronization System: Low-Level Design
A data synchronization system allows multiple devices or replicas to independently modify shared data and then merge their changes without a central coordinator. The core challenges are detecting which changes happened since a device last synced (delta sync), ordering concurrent changes causally (vector clocks), resolving conflicts when two devices modify the same object simultaneously, and recovering from extended offline periods. This article designs the full system from the data model through conflict resolution algorithms to the Python implementation.
Vector Clocks and Causal Ordering
A vector clock is a map from device ID to a logical counter: {"device_A": 3, "device_B": 1}. Every write increments the writing device’s counter. Two vector clocks can be compared: if every entry in clock A is ≤ the corresponding entry in clock B, then A “happened before” B (causal order). If neither dominates, the writes are concurrent and require conflict resolution.
SQL Schema
CREATE TABLE SyncObject (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
object_type VARCHAR(128) NOT NULL, -- e.g. 'note', 'contact', 'task'
object_id VARCHAR(64) NOT NULL, -- client-generated UUID
owner_id BIGINT UNSIGNED NOT NULL,
payload JSON NOT NULL,
vector_clock JSON NOT NULL, -- {"device_id": counter, ...}
last_sync_token VARCHAR(64) NOT NULL, -- opaque cursor for delta sync
deleted_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uq_type_obj (object_type, object_id),
INDEX idx_owner_token (owner_id, last_sync_token),
INDEX idx_updated (updated_at DESC)
) ENGINE=InnoDB;
CREATE TABLE SyncChange (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
object_type VARCHAR(128) NOT NULL,
object_id VARCHAR(64) NOT NULL,
device_id VARCHAR(64) NOT NULL,
operation ENUM('create','update','delete') NOT NULL,
delta JSON NOT NULL, -- field-level diff
vector_clock JSON NOT NULL,
sync_token VARCHAR(64) NOT NULL, -- monotonic token assigned on server receipt
recorded_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (id),
INDEX idx_obj_token (object_type, object_id, sync_token),
INDEX idx_device (device_id, sync_token)
) ENGINE=InnoDB;
CREATE TABLE SyncConflict (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
object_type VARCHAR(128) NOT NULL,
object_id VARCHAR(64) NOT NULL,
winning_change_id BIGINT UNSIGNED NOT NULL,
losing_change_id BIGINT UNSIGNED NOT NULL,
resolution ENUM('lww','merge','manual') NOT NULL,
resolved_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (id),
INDEX idx_obj (object_type, object_id)
) ENGINE=InnoDB;
CREATE TABLE DeviceClock (
device_id VARCHAR(64) NOT NULL,
owner_id BIGINT UNSIGNED NOT NULL,
last_sync_token VARCHAR(64) NOT NULL DEFAULT '0',
vector_clock JSON NOT NULL DEFAULT '{}',
last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (device_id),
INDEX idx_owner (owner_id)
) ENGINE=InnoDB;
Delta Sync: Sending Only Changes
The sync token is a monotonically increasing server-assigned value (e.g., a sequence number or timestamp-based string). Each device stores its last_sync_token. On sync, the device sends its token; the server returns all SyncChange rows with sync_token > last_sync_token for that owner. This is a single index-range scan rather than a full diff.
Python Implementation
import json
import time
import uuid
from datetime import datetime
from typing import Any
import db
def compute_delta(owner_id: int, device_id: str, last_sync_token: str) -> dict:
"""
Return all changes since last_sync_token for the given owner,
excluding changes originating from this device (already applied locally).
"""
rows = db.fetchall(
"""SELECT sc.id, sc.object_type, sc.object_id, sc.operation,
sc.delta, sc.vector_clock, sc.sync_token, sc.device_id
FROM SyncChange sc
WHERE sc.sync_token > %s
AND sc.object_id IN (
SELECT object_id FROM SyncObject WHERE owner_id = %s
)
AND sc.device_id != %s
ORDER BY sc.sync_token ASC
LIMIT 1000""",
(last_sync_token, owner_id, device_id)
)
if not rows:
return {"changes": [], "next_token": last_sync_token}
next_token = rows[-1]["sync_token"]
changes = []
for r in rows:
changes.append({
"object_type": r["object_type"],
"object_id": r["object_id"],
"operation": r["operation"],
"delta": r["delta"] if isinstance(r["delta"], dict) else json.loads(r["delta"]),
"vector_clock": r["vector_clock"] if isinstance(r["vector_clock"], dict) else json.loads(r["vector_clock"]),
"sync_token": r["sync_token"],
})
return {"changes": changes, "next_token": next_token}
def apply_changes(device_id: str, owner_id: int, changes: list[dict]) -> dict:
"""
Server receives a batch of changes from a device.
Each change is validated, conflict-detected, and applied.
Returns a sync token for the device to advance to.
"""
accepted = []
conflicts = []
for change in changes:
obj_type = change["object_type"]
obj_id = change["object_id"]
client_vc = change["vector_clock"]
delta = change["delta"]
op = change["operation"]
server_obj = db.fetchone(
"SELECT id, vector_clock, payload FROM SyncObject WHERE object_type=%s AND object_id=%s",
(obj_type, obj_id)
)
if server_obj:
server_vc = server_obj["vector_clock"] if isinstance(server_obj["vector_clock"], dict)
else json.loads(server_obj["vector_clock"])
relation = _compare_vector_clocks(client_vc, server_vc)
if relation == "before":
# server is ahead; this is a stale write — ignore
continue
elif relation == "concurrent":
winner, loser = resolve_conflict(
server_obj, change, strategy="lww"
)
change_id = _persist_change(device_id, obj_type, obj_id, op, delta, client_vc)
if winner == "client":
_apply_to_object(server_obj["id"], delta, _merge_clocks(client_vc, server_vc))
conflicts.append({"object_id": obj_id, "resolution": "lww_client_wins"})
else:
conflicts.append({"object_id": obj_id, "resolution": "lww_server_wins"})
accepted.append(change_id)
continue
# no conflict: apply directly
change_id = _persist_change(device_id, obj_type, obj_id, op, delta, client_vc)
if server_obj:
merged_vc = _merge_clocks(client_vc,
json.loads(server_obj["vector_clock"]) if isinstance(server_obj["vector_clock"], str)
else server_obj["vector_clock"])
_apply_to_object(server_obj["id"], delta, merged_vc)
else:
_create_object(owner_id, obj_type, obj_id, delta, client_vc, change_id)
accepted.append(change_id)
new_token = _current_sync_token()
db.execute(
"""INSERT INTO DeviceClock (device_id, owner_id, last_sync_token)
VALUES (%s, %s, %s)
ON DUPLICATE KEY UPDATE last_sync_token = VALUES(last_sync_token), last_seen_at = NOW()""",
(device_id, owner_id, new_token)
)
return {"accepted": len(accepted), "conflicts": conflicts, "sync_token": new_token}
def resolve_conflict(server_obj: dict, client_change: dict, strategy: str = "lww") -> tuple[str, str]:
"""
Returns ('client','server') or ('server','client') indicating winner.
lww: last-write-wins based on wall-clock timestamp in vector clock payload.
"""
if strategy == "lww":
server_ts = server_obj.get("updated_at") or datetime.min
client_ts = client_change.get("timestamp") or datetime.min
if isinstance(server_ts, str):
server_ts = datetime.fromisoformat(server_ts)
if isinstance(client_ts, (int, float)):
client_ts = datetime.utcfromtimestamp(client_ts)
if client_ts >= server_ts:
return "client", "server"
return "server", "client"
if strategy == "merge":
# field-level merge: for each field take the value with the higher VC
# (not shown in full; application-specific merge logic goes here)
return "client", "server"
return "server", "client"
def _compare_vector_clocks(vc_a: dict, vc_b: dict) -> str:
"""Returns 'before', 'after', 'equal', or 'concurrent'."""
all_keys = set(vc_a) | set(vc_b)
a_leq_b = all(vc_a.get(k, 0) <= vc_b.get(k, 0) for k in all_keys)
b_leq_a = all(vc_b.get(k, 0) dict:
all_keys = set(vc_a) | set(vc_b)
return {k: max(vc_a.get(k, 0), vc_b.get(k, 0)) for k in all_keys}
def _persist_change(device_id, obj_type, obj_id, op, delta, vc) -> int:
token = _current_sync_token()
return db.execute(
"""INSERT INTO SyncChange (object_type, object_id, device_id, operation, delta, vector_clock, sync_token)
VALUES (%s,%s,%s,%s,%s,%s,%s)""",
(obj_type, obj_id, device_id, op, json.dumps(delta), json.dumps(vc), token)
)
def _current_sync_token() -> str:
return str(int(time.time() * 1000))
def _apply_to_object(obj_db_id: int, delta: dict, merged_vc: dict) -> None:
db.execute(
"""UPDATE SyncObject
SET payload = JSON_MERGE_PATCH(payload, %s),
vector_clock = %s,
last_sync_token = %s,
updated_at = NOW()
WHERE id = %s""",
(json.dumps(delta), json.dumps(merged_vc), _current_sync_token(), obj_db_id)
)
def _create_object(owner_id, obj_type, obj_id, payload, vc, change_id) -> None:
token = _current_sync_token()
db.execute(
"""INSERT INTO SyncObject (object_type, object_id, owner_id, payload, vector_clock, last_sync_token)
VALUES (%s,%s,%s,%s,%s,%s)""",
(obj_type, obj_id, owner_id, json.dumps(payload), json.dumps(vc), token)
)
Offline Queue and Replay
When a device is offline, it buffers all writes to a local queue with locally-incremented vector clock entries. On reconnect, the queue is replayed through apply_changes() in order. Because the vector clock captures device causality, the server can detect which changes are stale (already superseded by server state) and skip them, avoiding duplicate application.
Conflict Resolution Strategies
Last-Write-Wins (LWW): compare wall-clock timestamps; the newer write wins. Simple but loses data if clocks are skewed. Field-level merge: for each field, take the value with the higher vector clock entry for the writing device. Preserves more data but requires application-specific merge logic (e.g., merging a list of tags vs. replacing a string field). The SyncConflict table records every conflict and its resolution for audit and potential manual review.
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “What is a vector clock and how does it enable conflict detection in sync systems?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A vector clock is a map from device ID to a logical counter, e.g., {device_A: 3, device_B: 1}. Each write increments the writing device’s counter. Two clocks can be compared: if every entry in clock A is last_sync_token for that owner. This is a bounded index-range scan. The device advances its token after successfully applying the returned changes.”
}
},
{
“@type”: “Question”,
“name”: “What is last-write-wins conflict resolution and when should you use it?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Last-write-wins (LWW) resolves conflicts by picking the change with the later wall-clock timestamp. It is simple to implement and works well for objects where the latest value is always correct (e.g., a user’s profile photo or a setting toggle). It is inappropriate when both concurrent changes contain unique data that should be preserved (e.g., two users appending to a shared list), where field-level merge or operational transformation is needed.”
}
},
{
“@type”: “Question”,
“name”: “How does an offline device replay queued changes on reconnect?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “The device buffers writes locally with locally-incremented vector clock entries. On reconnect, it submits the queued changes in order via apply_changes(). The server compares each change’s vector clock against the current server object clock. Changes the server has already superseded (client clock is ‘before’ server clock) are skipped. Concurrent changes are conflict-resolved. This design is idempotent: replaying the same change twice is safe because the second replay will be detected as ‘before’ or ‘equal’.”
}
}
]
}
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do vector clocks detect conflicts in data sync?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Each device maintains a counter per known device; on sync, the receiving node compares clocks—if neither clock dominates the other, the changes are concurrent and a conflict is flagged.”}},{“@type”:”Question”,”name”:”How does delta sync minimize bandwidth?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Each sync request includes a last_sync_token (a server-side cursor); the server returns only SyncChange rows created after that token, avoiding full object retransmission.”}},{“@type”:”Question”,”name”:”How is the offline queue replayed on reconnect?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The client queues mutations locally with a pending_sync flag; on reconnect, it sends a batch of pending changes to the server and updates the local sync token after acknowledgment.”}},{“@type”:”Question”,”name”:”What conflict resolution strategies are supported?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Last-Write-Wins uses the higher vector clock timestamp; custom merge strategies are registered per object type (e.g., list CRDT for arrays, union merge for sets).”}}]}
See also: Apple Interview Guide 2026: iOS Systems, Hardware-Software Integration, and iCloud Architecture
See also: Meta Interview Guide 2026: Facebook, Instagram, WhatsApp Engineering