Conflicts and Sync

Conflicts are product semantics. The database can detect stale versions, but the product must decide what the user meant.

Conflict Types

Conflict	Example	Good default
duplicate create	same SMS imported twice	stable source ID and idempotent insert
stale update	device edits old row after server changed it	compare version and rebase or prompt
delete vs edit	one device deletes a note, another edits it	tombstone plus recoverable history
order conflict	two devices reorder list items	order keys or server canonical order
field merge	two devices edit different fields	merge per field when safe
semantic conflict	two devices categorize same expense differently	show user-visible conflict

Offline Mutation Shape

CREATE TABLE pending_mutations (
  id TEXT PRIMARY KEY,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  operation TEXT NOT NULL,
  payload_json TEXT NOT NULL,
  idempotency_key TEXT NOT NULL UNIQUE,
  base_version INTEGER,
  attempt_count INTEGER NOT NULL DEFAULT 0,
  next_attempt_at TEXT,
  last_error TEXT,
  created_at TEXT NOT NULL
);

This schema is enough for many apps. It supports persistence, retries, dedupe, debugging, and user-visible pending state.

Merge Rules

Use the simplest rule that matches the data:

stable unique IDs for imported events
last-write-wins only for low-value preferences
per-field merge for independent fields
append-only events for audit logs
tombstones for deletes that sync later
explicit user prompt for semantic conflicts

Server Authority

Server-authoritative sync keeps one final source of truth. The client applies optimistic local mutations, then replays or rebases pending mutations after pulling the server state.

This is easier to debug than peer-to-peer sync, but it requires careful idempotency and server-side validation.