Offline-first sync and conflict model

Entity model

Entity-relationship model

Every syncable record carries a globally-unique ID, separate client and server versions, 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

Record IDs are generated on the client and MUST be collision-resistant across devices — UUIDv4/v7 or equivalent, never sequential integers or timestamp-plus-hash — so two devices editing offline do not mint the same ID for different records. (A UUID collision is not literally impossible; it is negligible at any realistic scale, and that negligibility is the property relied on here.)

Definition

Versioning strategy

Each record's version is a monotonically increasing integer per record. Client and server versions are tracked separately: client_version advances on every local edit; server_version is the version the server assigned when it last accepted the record. The two diverge exactly while the device holds unpushed local edits.

A push request carries the base_version — the server_version the client last saw for that record. The server detects a conflict by comparing base_version against the record's current server_version. If they match, the write applies cleanly and the server assigns the next server_version; if base_version is behind, another device wrote in between and the change enters conflict resolution rather than blindly overwriting.

-- Applies only if the client's base_version is still current.
-- Zero rows affected => a conflict; route to conflict resolution.
UPDATE records
SET    payload = :payload,
       server_version = server_version + 1,
       updated_at = now()
WHERE  id = :id
  AND  server_version = :base_version;

A push MUST include the base_version; a write received without one MUST be treated as a conflict, never a blind overwrite.

Definition

Conflict resolution

On a detected conflict, resolution is either whole-record or field-level. Whole-record picks one winner by a fixed total order — server_version, then server-authoritative timestamp, then a stable tiebreak (device id) — so every peer reaches the same result independent of sync order. It is simple and lossy: the loser's fields are discarded. Use it where a record's fields are not independently meaningful.

Field-level merge resolves each field independently and preserves concurrent edits to different fields, but it is safe only for field types with a well-defined merge. Commutativity is a property of the field type, not a blanket guarantee: text and counters merge order-independently; an arbitrary scalar does not. For a field without a safe automatic merge — a status enum, a price, anything governed by business rules — a custom resolver MUST be defined, otherwise the field falls back to whole-record last-writer-wins.

A custom resolver is a deterministic pure function `(base, local, remote) => resolved`, registered per field or record type and run identically on the server and every client so all peers converge. A resolver that is non-deterministic, or differs across peers, breaks convergence and MUST NOT be used.

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.

Delete versus concurrent modify resolves delete-wins: a tombstone MUST take precedence over a concurrent field modification, and the modification is rejected rather than resurrecting the row. A genuine undelete is an explicit new write with a higher version, never an automatic side effect of a late edit arriving.

Contract

Sync scope

A client does not sync the whole database — it syncs its scope: the set of records its identity is entitled to and interested in. Scope is determined by user identity and tenant (the authorization floor), and optionally narrowed by a time window or explicit subscriptions (record sets the client has asked for). The server MUST enforce the authorization part regardless of what the client requests — a client MAY narrow its scope but MUST NOT widen it beyond what its identity permits, and the server filters every pull accordingly.

When a record leaves a client's scope — reassigned, archived, or aged past the time window — the server MUST push a scope tombstone so the client drops its now-orphaned copy, rather than leaving stale data on the device. A scope tombstone is distinct from a delete: the record still exists server-side; it is simply no longer in this client's scope.

Scope is enforced server-side; client-supplied filters MAY narrow a pull but MUST NOT be trusted to widen it.

Contract

Sync protocol

Contract

Failure handling

A rejected push is not a lost change. When the server rejects a push, the client MUST keep the record dirty and retry: a conflict rejection routes the record through conflict resolution; a validation rejection that cannot be auto-resolved MUST surface to the user rather than being silently dropped; a transient error retries with exponential backoff.

The acknowledgement path MUST be idempotent. A client that crashes after the server committed a push but before recording the ack will re-send on restart; the server MUST detect the duplicate — by (record id, version) or a client-supplied idempotency key — and treat the re-send as a no-op returning the same ack, so a crash mid-ack never double-applies or double-counts.

A batch push MUST be acknowledged per record, not all-or-nothing, so one bad record does not force the whole batch to re-send.

Contract

Scalability

Pull operations MUST be paginated. A pull returns at most a page of changes (default 500 records) plus a cursor; the client repeats until the cursor is drained, so a large initial sync streams in bounded pages rather than one unbounded response. The cursor is the server high-water mark from the checkpoint and is opaque to the client.

Push operations SHOULD be batched (default 100 records per request) to amortise round-trips, subject to the per-record acknowledgement rule above. For large datasets, sync deltas rather than snapshots — only records changed since the cursor — and keep large binary blobs in a separate table or object store, synced by reference, so a metadata sync is never blocked behind multi-megabyte payloads.