When the device holds the authoritative copy until it can sync, the data model has to survive concurrent edits, crashes mid-write, and deletes that must propagate. This spec defines identity, versioning, tombstones, and a deterministic conflict resolution so two devices editing the same record converge on the same answer — and nothing acknowledged is lost.
Entity model
Entity-relationship model
Every syncable record carries a globally-unique ID, a version, timestamps, a soft-delete tombstone, and a dirty flag marking local changes not yet pushed. A per-device sync checkpoint records how far the last successful sync reached.
Definition
Identity and versioning
IDs are globally unique and generated on the client — UUIDs, not sequential integers or timestamp-plus-hash — so two offline devices never mint the same ID for different records. Each record carries a version that increments on every local change, which is what makes a concurrent edit detectable rather than silently overwritten.
Definition
Deterministic conflict resolution
When two versions of a record meet, the winner is chosen by a fixed, total ordering — version, then server-authoritative timestamp, then a stable tiebreak (device ID) — so every peer resolves the conflict identically and the outcome is independent of sync order. Where field-level merge is required, merges are commutative so order still doesn't matter.
-- Winner = higher version; ties broken by server time, then device id.
-- Every device applies the same rule and converges on the same result.
SELECT * FROM candidates
ORDER BY version DESC, server_updated_at DESC, device_id DESC
LIMIT 1;Definition
Deletes are tombstones
A delete sets `deleted_at` rather than removing the row, so the deletion can propagate to peers that still have the record. Rows are only physically purged after every device has acknowledged the tombstone past its sync cursor — a hard delete before sync is an un-propagatable change that resurrects on the next pull.
Contract
Sync protocol
| Phase | Rule |
|---|---|
| Pull | Fetch server changes since the stored cursor |
| Resolve | Apply the deterministic conflict rule locally |
| Push | Send dirty records; clear dirty only on server ack |
| Advance | Move the cursor only after a fully-acked round |
Invariants this spec guarantees
- Record IDs are globally unique and client-generated; two offline devices never collide.
- Conflict resolution is deterministic and order-independent — every peer converges on the same record.
- Deletes propagate as tombstones; a row is never hard-deleted before every device has synced past it.
- The dirty flag clears and the cursor advances only on server acknowledgement, so a crash mid-sync loses nothing acknowledged.