Bidirectional Data Sync Dashboard

0) Problem Restatement

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.

1) Requirements

• Create, update and delete records on either side, and propagate within seconds to minutes.
• Map IDs between systems (our id ↔ their external_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.

2) Architecture

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 --- INW

3) Data 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_at

• last_synced_* remembers what both sides looked like at the last successful sync. That is the base for detecting conflicts.

4) Flows

4.1 Outbound (our change → external)

1. The user edits a record. We save it with version + 1 and write an outbox row in the same transaction.
2. 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.
3. On success, update sync_state with both versions and the content hash.

4.2 Inbound (external change → us)

1. A webhook arrives. Verify its signature, deduplicate by the event ID, and enqueue.
2. The worker fetches the current external record (webhooks can arrive out of order, so re-read the source).
3. Loop check: if the external content hash equals last_synced_hash, this is the echo of our own update, so ignore it.
4. Otherwise, apply it to our DB (see conflicts), and update sync_state.

5) Conflicts

A conflict happens when both sides changed since the last sync (local.version > last_synced_local_version and remote.version > last_synced_remote_version).

Resolution options (pick one and state it):

• Field-level three-way merge: compare each field to the base. If only one side changed a field, take that change. If both changed the same field differently, it's a true conflict.
• For true conflicts: a source-of-truth per field (e.g., status comes from the external system, priority from ours), last-writer-wins by timestamp (simple, may lose edits), or mark as conflict and show both values in the dashboard for a human to choose.
• Deletes: use soft deletes (tombstones), so a delete on one side isn't "resurrected" by a stale update from the other.

6) 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 error shown 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.

7) Trade-offs & Alternatives

8) Wrap-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.