Problem RestatementProblem
Design a dashboard (asked at NVIDIA) whose data is kept in sync in both directions with one or more external systems. For example, tickets or assets edited in our dashboard must appear in an external tracker (like Jira or a CMDB), and edits made there must flow back into the dashboard. The difficult parts are conflicts (both sides edit the same record), loops (our update triggers their webhook, which triggers our update...), failures and retries, and keeping everything consistent.
RequirementsRequirements
- Create, update and delete records on either side, and propagate within seconds to minutes.
- Map IDs between systems (our
id↔ theirexternal_id). - Detect and resolve conflicts predictably.
- Never loop, and never duplicate records.
- Show sync status per record (synced, pending, conflict, error).
- A periodic full reconciliation to fix any drift.
ArchitectureArchitecture
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
UI["Dashboard UI"] --> API["Dashboard API"]
API --> DB[("Dashboard DB + outbox")]
DB -->|"outbound changes"| OUT["Outbound sync worker"]
OUT -->|"API calls - rate limited"| EXT["External system"]
EXT -->|"webhooks"| IN["Inbound receiver - verify, dedupe"]
IN --> Q[("Inbound queue")]
Q --> INW["Inbound sync worker"]
INW --> DB
REC["Nightly reconciliation"] --> DB
REC --> EXT
MAP[("ID map + sync state")] --- OUT
MAP --- INWData ModelData model
records: id, fields..., version (our counter), updated_at, updated_by
sync_state: id, system, external_id, last_synced_local_version, last_synced_remote_version,
last_synced_hash, status (synced|pending|conflict|error), error, updated_atlast_synced_*remembers what both sides looked like at the last successful sync. That is the base for detecting conflicts.
FlowsFlows
4.1 Outbound (our change → external)
- The user edits a record. We save it with
version + 1and write an outbox row in the same transaction. - The outbound worker reads the outbox and calls the external API (create if there's no
external_id, else update), with retries and rate limits. - On success, update
sync_statewith both versions and the content hash.
4.2 Inbound (external change → us)
- A webhook arrives. Verify its signature, deduplicate by the event ID, and enqueue.
- The worker fetches the current external record (webhooks can arrive out of order, so re-read the source).
- Loop check: if the external content hash equals
last_synced_hash, this is the echo of our own update, so ignore it. - Otherwise, apply it to our DB (see conflicts), and update
sync_state.
Deep Dive — When both sides changedDeep dive
Sync is easy until a ticket is edited in our dashboard and in the external tracker between two sync runs. Everything interesting is in what happens next.
Whoever syncs last wins
Each run copies the other side's current state over ours, and ours over theirs.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
L["We set priority = High"] --> S["Sync run"]
R["They set status = Done"] --> S
S --> OVER["Remote record copied over ours"]
OVER --> LOST["priority = High silently discarded"]
S --> PING["Next run copies ours back - status reverts"]
PING --> LOOP["Fields flip back and forth every cycle"]Two edits to different fields destroy each other, because the unit of copying is the whole record. Worse, each side keeps re-asserting its version, so the record oscillates on every sync — the classic sync loop.
Last writer wins by timestamp
Compare updated_at on both sides and let the newer record win.
The oscillation stops, because the comparison is now stable rather than depending on run order. But the loss is unchanged: the newer record still overwrites the older one wholesale, so an edit to priority is discarded by a later edit to status. It also assumes two systems' clocks are comparable, which across a SaaS boundary they are not.
Three-way merge against the last synced state
Keep what was synced last time as a base, and compare both sides to it rather than to each other.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
BASE["Base - state at last sync"] --> CMP["Compare each field"]
LOC["Local now"] --> CMP
REM["Remote now"] --> CMP
CMP -->|"only local changed"| TL["Take local"]
CMP -->|"only remote changed"| TR["Take remote"]
CMP -->|"both changed, same value"| OK["No conflict"]
CMP -->|"both changed, different values"| TRUE["True conflict"]
TRUE --> RULE{"Resolution policy"}
RULE --> OWN["Field owner - status from the tracker, priority from us"]
RULE --> FLAG["Or flag it and show both values for a human"]The base is what makes "who changed what" answerable. Most concurrent edits touch different fields and merge cleanly; only genuine same-field disagreements reach the policy.
Three rules to state:
- A source of truth per field beats a global one. Status is owned by the tracker, priority by us — most "conflicts" are then decided by the schema, not by a timestamp race.
- True conflicts can be surfaced, not resolved. Marking the record and showing both values is a legitimate answer and often the right one; silently picking a winner is how data goes missing.
- Deletes need tombstones. A soft delete with a version lets the other side distinguish "deleted" from "not yet seen", which is what stops a stale update from resurrecting a deleted record.
Reliability
- Idempotency: outbound creates carry our record ID as an idempotency key or stored in a custom external field, so a retry can find the existing record instead of creating a duplicate.
- Retries with backoff, with a dead-letter state
errorshown in the UI, plus a "retry" button. - Ordering per record: process one record's events sequentially (partition queues by record ID).
- Reconciliation: nightly, list both sides (or use updated-since queries), compare hashes, and fix differences. It catches missed webhooks.
- Status UI: show counts of pending, conflict and error records, and lag metrics.
Trade-offs & AlternativesTrade-offs
| Decision | Choice | Why | Alternative |
|---|---|---|---|
| Change capture | Outbox (ours), webhooks + re-read (theirs) | Reliable, order-safe | Polling only: slower, costly |
| Loop prevention | Compare with last synced hash | Simple and robust | Origin flags: may be dropped by external systems |
| Conflicts | Three-way merge + per-field owner + manual queue | Few lost edits | Blind last-writer-wins: silent data loss |
| Drift | Nightly reconciliation | Catches missed events | Trust events forever |
Wrap-UpWrap-up
Keep an ID map and per-record sync state (the last synced versions and hash). Push our changes out via a transactional outbox, pull theirs in via verified, deduplicated webhooks that re-read the source, and ignore echoes by comparing to the last synced hash. Resolve conflicts with a three-way field merge plus per-field ownership or a human conflict queue, keep operations idempotent and ordered per record, and run nightly reconciliation with a clear sync-status view.