Designing a Bidirectional Data Sync Engine: Conflict Resolution, Change Detection, and Multi-System Consistency at Scale

Keeping a local database in sync with Salesforce, Booking.com, and an internal ERP simultaneously is not a data pipeline problem. It is a distributed systems problem with multiple writers, no global coordinator, and failure modes that compound across boundaries you do not control.

The challenge is specific: any of these systems can mutate the same record at the same time. Your application updates a hotel room’s availability at 14:03:12 UTC. Booking.com ingests a new reservation at 14:03:11 UTC that it delivers to your webhook at 14:03:14 UTC. Which write wins? What does “wins” even mean when both are legitimate? How do you ensure partial failures in a five-system fan-out do not leave half your systems with one version and half with another?

This article covers the full engine: change detection, conflict resolution strategies, the sync protocol, idempotency and cursor-based state tracking, and partial failure recovery. Each section maps to patterns you can implement with a real system in mind.

Change Detection: Three Mechanisms and When to Use Each

Before you can sync changes, you need to know they happened. There are three practical approaches, and the right choice depends on what access you have to each external system.

Polling with Cursor-Based Pagination

Polling is the fallback when a system offers no push mechanism. The key is avoiding duplicate processing and missed records. Use an updatedAt cursor, not an offset, and make the cursor durable.

interface SyncCursor {
  systemId: string;
  resourceType: string;
  lastProcessedAt: Date;
  lastProcessedId: string; // tie-breaker when timestamps collide
}

interface PolledRecord {
  id: string;
  updatedAt: Date;
  payload: Record<string, unknown>;
  checksum: string; // SHA-256 of canonical JSON
}

async function pollChanges(
  cursor: SyncCursor,
  fetcher: (since: Date, afterId: string, limit: number) => Promise<PolledRecord[]>
): Promise<{ records: PolledRecord[]; nextCursor: SyncCursor }> {
  const limit = 500;
  const records = await fetcher(cursor.lastProcessedAt, cursor.lastProcessedId, limit);

  if (records.length === 0) {
    return { records: [], nextCursor: cursor };
  }

  const last = records[records.length - 1];
  const nextCursor: SyncCursor = {
    ...cursor,
    lastProcessedAt: last.updatedAt,
    lastProcessedId: last.id,
  };

  return { records, nextCursor };
}

Persist the cursor to durable storage before marking records as processed. If your process crashes between fetching and processing, the next run re-fetches from the last safe cursor position. Never update the cursor atomically with processing: update it only after successful processing of the batch.

Webhook-Driven Updates

Webhooks flip the polling model: the external system pushes changes to you. The ingestion pattern is covered in depth elsewhere, but the key constraint for a sync engine is that webhooks arrive out of order and can arrive more than once.

Your sync engine must treat every inbound webhook as a candidate change, not a definitive write. Store the raw event, extract the record identifier and the event timestamp, then feed it into your conflict resolution layer. Never apply a webhook payload directly to your local record without running it through conflict resolution first.

Change Data Capture with Debezium

When you control the source database, CDC is the most complete solution. Debezium captures every row-level change from the Postgres write-ahead log and publishes it to Kafka as a structured event. Unlike polling, you get deletes and field-level diffs, not just updated rows.

interface DebeziumChangeEvent {
  op: "c" | "u" | "d" | "r"; // create, update, delete, read (snapshot)
  ts_ms: number;
  source: {
    db: string;
    table: string;
    txId: number;
    lsn: number; // log sequence number
  };
  before: Record<string, unknown> | null;
  after: Record<string, unknown> | null;
}

function extractChangeFromDebezium(event: DebeziumChangeEvent): SyncChange | null {
  if (event.op === "d") {
    return {
      type: "delete",
      entityId: String(event.before?.id),
      occurredAt: new Date(event.ts_ms),
      lsn: event.source.lsn,
      payload: null,
    };
  }

  if (event.op === "c" || event.op === "u") {
    return {
      type: event.op === "c" ? "create" : "update",
      entityId: String(event.after?.id),
      occurredAt: new Date(event.ts_ms),
      lsn: event.source.lsn,
      payload: event.after,
    };
  }

  return null; // snapshot reads during initial load, handled separately
}

The LSN (log sequence number) gives you total ordering within a single Postgres instance. This matters for conflict resolution when multiple changes arrive close together.

Conflict Resolution: Three Strategies with Real Tradeoffs

Conflict resolution is the core of a bidirectional sync engine. The strategy you choose determines what “correct” means when two systems disagree.

Last-Write-Wins with Vector Clocks

Simple LWW using wall-clock timestamps breaks immediately when clocks skew across systems. A record updated at 14:03:11 on a system with a 2-second NTP drift will lose to a record updated at 14:03:10 on a system with a correct clock, even though the first update was causally later.

Vector clocks solve this by tracking causality rather than wall-clock time.

type VectorClock = Record<string, number>;

interface VersionedRecord {
  id: string;
  payload: Record<string, unknown>;
  vectorClock: VectorClock;
  wallClock: Date; // fallback only
  sourceSystemId: string;
}

function compareVectorClocks(a: VectorClock, b: VectorClock): "a-wins" | "b-wins" | "concurrent" {
  const allSystems = new Set([...Object.keys(a), ...Object.keys(b)]);
  let aAhead = false;
  let bAhead = false;

  for (const sys of allSystems) {
    const aVal = a[sys] ?? 0;
    const bVal = b[sys] ?? 0;
    if (aVal > bVal) aAhead = true;
    if (bVal > aVal) bAhead = true;
  }

  if (aAhead && !bAhead) return "a-wins";
  if (bAhead && !aAhead) return "b-wins";
  return "concurrent"; // both have advanced; requires merge
}

function incrementClock(clock: VectorClock, systemId: string): VectorClock {
  return { ...clock, [systemId]: (clock[systemId] ?? 0) + 1 };
}

When the result is concurrent, you cannot determine which version is “newer” from causality alone. This is where your domain model has to make a decision.

Field-Level Merge Functions

For most business records, the right approach is not to pick one version wholesale but to merge them at the field level. Different fields have different ownership semantics.

type FieldMergeStrategy =
  | "source-wins" // external system is authoritative for this field
  | "local-wins"  // local database is authoritative
  | "latest-updater" // whoever updated this field most recently wins
  | "union"          // set union, for arrays
  | "custom";

interface FieldPolicy {
  fieldName: string;
  strategy: FieldMergeStrategy;
  customMerge?: (local: unknown, remote: unknown, localClock: VectorClock, remoteClock: VectorClock) => unknown;
}

function mergeRecords(
  local: VersionedRecord,
  remote: VersionedRecord,
  policies: FieldPolicy[]
): Record<string, unknown> {
  const merged: Record<string, unknown> = { ...local.payload };

  for (const policy of policies) {
    const localVal = local.payload[policy.fieldName];
    const remoteVal = remote.payload[policy.fieldName];

    switch (policy.strategy) {
      case "source-wins":
        merged[policy.fieldName] = remoteVal;
        break;
      case "local-wins":
        merged[policy.fieldName] = localVal;
        break;
      case "latest-updater": {
        const cmp = compareVectorClocks(local.vectorClock, remote.vectorClock);
        merged[policy.fieldName] = cmp === "b-wins" ? remoteVal : localVal;
        break;
      }
      case "union":
        if (Array.isArray(localVal) && Array.isArray(remoteVal)) {
          merged[policy.fieldName] = [...new Set([...localVal, ...remoteVal])];
        }
        break;
      case "custom":
        if (policy.customMerge) {
          merged[policy.fieldName] = policy.customMerge(
            localVal, remoteVal, local.vectorClock, remote.vectorClock
          );
        }
        break;
    }
  }

  return merged;
}

A HospitalityTech channel manager syncing room availability across Airbnb and Booking.com is a concrete example. The basePrice field might use local-wins because the property management system owns pricing. The available field might use latest-updater because either system can close availability when a booking arrives. The amenities array might use union because adding an amenity in one system should not remove it from another.

Operational Transforms for Multi-System Sync

When you have more than two systems, and updates from multiple systems need to be applied in sequence, field-level merge is not enough. You need to reason about operations, not just states.

Operational transforms represent changes as reversible operations. Instead of storing the new state, you store what changed and compose operations from multiple sources.

type SyncOperation =
  | { type: "set"; field: string; value: unknown; prevValue: unknown }
  | { type: "array-insert"; field: string; index: number; value: unknown }
  | { type: "array-remove"; field: string; index: number; value: unknown }
  | { type: "delete-record" };

function transformOperation(
  op: SyncOperation,
  concurrentOp: SyncOperation
): SyncOperation {
  // If both ops set the same field, the one with the later vector clock wins.
  // The earlier op is transformed into a no-op.
  if (op.type === "set" && concurrentOp.type === "set" && op.field === concurrentOp.field) {
    // Return a no-op set (value equals current value after concurrentOp applied)
    return { ...op, value: op.prevValue };
  }
  // If concurrent op deleted the record, convert any set into a no-op
  if (concurrentOp.type === "delete-record") {
    return { type: "set", field: "", value: undefined, prevValue: undefined };
  }
  return op;
}

Operational transforms are used in practice by e-commerce platforms syncing inventory across Amazon, eBay, and their own storefront. When a sale arrives from Amazon and reduces inventory by 1, and a manual adjustment on the storefront also reduces inventory by 1, the correct result is to apply both decrements, not treat the later-arriving event as the final state.

The Sync Protocol: Tombstones and Soft Deletes

Deletes are the hardest part of bidirectional sync. If a record is deleted in one system and the sync engine simply removes it locally, there is no record left to prevent re-creation when the deleted system polls again or another system pushes a reference to the same entity.

Tombstones solve this. A tombstone is a record that marks an entity as deleted without removing it from storage.

interface Tombstone {
  entityId: string;
  entityType: string;
  deletedAt: Date;
  deletedBy: string; // which system initiated the delete
  ttl: Date; // when the tombstone itself can be cleaned up
  vectorClock: VectorClock;
}

interface SyncRecord {
  id: string;
  entityType: string;
  payload: Record<string, unknown> | null;
  vectorClock: VectorClock;
  syncedAt: Date;
  tombstone: Tombstone | null;
  version: number; // monotonic, per-record
}

async function applyRemoteDelete(
  entityId: string,
  entityType: string,
  remoteClock: VectorClock,
  deletedBy: string,
  db: Database
): Promise<void> {
  const existing = await db.findSyncRecord(entityId, entityType);

  if (existing) {
    const cmp = compareVectorClocks(existing.vectorClock, remoteClock);
    if (cmp === "a-wins") {
      // Local version is causally ahead of the delete — conflict.
      // Do not apply the delete. Log it for manual review or policy resolution.
      await db.logConflict({ entityId, entityType, type: "delete-vs-update", remoteClock });
      return;
    }
  }

  const tombstone: Tombstone = {
    entityId,
    entityType,
    deletedAt: new Date(),
    deletedBy,
    ttl: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000), // 30 days
    vectorClock: remoteClock,
  };

  await db.upsertSyncRecord({
    id: entityId,
    entityType,
    payload: null,
    vectorClock: remoteClock,
    syncedAt: new Date(),
    tombstone,
    version: (existing?.version ?? 0) + 1,
  });
}

When a sync engine encounters a tombstone for an entity it is about to create or update, it suppresses the operation. The tombstone’s TTL controls how long this suppression lasts. Set it to at least the maximum polling interval of any connected system, plus a safety margin.

Idempotent Sync Operations with Cursor-Based State Tracking

Every sync operation must be idempotent. Network retries, at-least-once delivery from webhooks, and process crashes during fan-out all mean the same change will be applied multiple times. The engine must produce the same result whether a change is applied once or ten times.

interface SyncJob {
  jobId: string; // caller-generated UUID
  entityId: string;
  entityType: string;
  operation: "upsert" | "delete";
  payload: Record<string, unknown> | null;
  vectorClock: VectorClock;
  sourceSystemId: string;
  targetSystems: string[];
}

interface SyncJobResult {
  jobId: string;
  status: "completed" | "partial" | "failed";
  systemResults: Record<string, "ok" | "conflict" | "error">;
  appliedAt: Date | null;
}

async function executeSyncJob(job: SyncJob, db: Database, clients: SystemClients): Promise<SyncJobResult> {
  // Check if job already completed successfully (idempotency gate)
  const existing = await db.findSyncJobResult(job.jobId);
  if (existing?.status === "completed") {
    return existing;
  }

  const systemResults: Record<string, "ok" | "conflict" | "error"> = {};

  await Promise.allSettled(
    job.targetSystems.map(async (systemId) => {
      try {
        const client = clients.get(systemId);
        if (!client) throw new Error(`No client for ${systemId}`);

        if (job.operation === "delete") {
          await client.delete(job.entityId, job.entityType, job.vectorClock);
        } else if (job.payload) {
          await client.upsert(job.entityId, job.entityType, job.payload, job.vectorClock);
        }

        systemResults[systemId] = "ok";
      } catch (err) {
        if (err instanceof ConflictError) {
          systemResults[systemId] = "conflict";
        } else {
          systemResults[systemId] = "error";
        }
      }
    })
  );

  const allOk = Object.values(systemResults).every((r) => r === "ok");
  const anyOk = Object.values(systemResults).some((r) => r === "ok");

  const result: SyncJobResult = {
    jobId: job.jobId,
    status: allOk ? "completed" : anyOk ? "partial" : "failed",
    systemResults,
    appliedAt: allOk || anyOk ? new Date() : null,
  };

  await db.upsertSyncJobResult(result);
  return result;
}

The jobId is the idempotency key. Store the result before returning. If the same job arrives again, return the stored result immediately without re-executing.

Error Recovery and Partial Failure Handling

In a five-system fan-out, three systems confirming a write and two failing is normal. The engine needs to track divergence and recover, not just log errors and move on.

interface DivergenceRecord {
  entityId: string;
  entityType: string;
  divergedSystemId: string;
  expectedVectorClock: VectorClock;
  lastAttemptAt: Date;
  attemptCount: number;
  nextRetryAt: Date;
}

function computeNextRetry(attemptCount: number): Date {
  // Exponential backoff: 30s, 2min, 8min, 32min, 2h, cap at 4h
  const delayMs = Math.min(30_000 * Math.pow(4, attemptCount - 1), 4 * 60 * 60 * 1000);
  return new Date(Date.now() + delayMs);
}

async function reconcileDivergence(
  record: DivergenceRecord,
  db: Database,
  clients: SystemClients
): Promise<void> {
  const canonical = await db.findSyncRecord(record.entityId, record.entityType);
  if (!canonical) return; // entity no longer exists, divergence is moot

  const client = clients.get(record.divergedSystemId);
  if (!client) return;

  try {
    if (canonical.tombstone) {
      await client.delete(record.entityId, record.entityType, canonical.vectorClock);
    } else if (canonical.payload) {
      await client.upsert(record.entityId, record.entityType, canonical.payload, canonical.vectorClock);
    }

    await db.deleteDivergenceRecord(record.entityId, record.entityType, record.divergedSystemId);
  } catch {
    await db.updateDivergenceRecord({
      ...record,
      lastAttemptAt: new Date(),
      attemptCount: record.attemptCount + 1,
      nextRetryAt: computeNextRetry(record.attemptCount + 1),
    });
  }
}

Run a divergence reconciler on a schedule (every 5 minutes is reasonable). It scans DivergenceRecord rows where nextRetryAt is past, fetches the current canonical state, and attempts to push it to the diverged system. This is a pull-based self-healing pattern: the canonical store is always the source of truth, and diverged systems are repaired toward it.

Sync Strategy Tradeoffs

Strategy	Latency	Reliability	External Requirements	Conflict Visibility	Operational Cost
Polling (cursor-based)	Minutes	High (idempotent by design)	Read API with updatedAt filter	Low (detect by checksum diff)	Low
Webhook-driven	Seconds	Medium (requires retry infrastructure)	Push endpoint + retry from source	Medium (out-of-order delivery)	Medium
CDC (Debezium)	Sub-second	High (WAL guarantees, LSN ordering)	Direct DB access, Kafka cluster	High (before/after per field)	High
Event-driven (outbox)	Seconds	High (transactional outbox pattern)	Shared event broker	High (explicit change events)	Medium

Polling is the right default when you are integrating with SaaS APIs you do not control. It is predictable, re-runnable, and degrades gracefully. Add webhooks on top of polling for latency, not instead of it: webhooks deliver fast, but polling catches what the webhook missed.

CDC is the right choice when you control the source database and latency requirements are tight, but it adds real operational weight: you need Kafka, a Debezium connector cluster, and observability into connector lag. For a CRM bidirectional sync with Salesforce or HubSpot, CDC on your internal database is appropriate; for the Salesforce side, you will use their streaming API or polling.

Production Considerations

Sync topology is a directed graph, not a star. Do not let every system push to every other system directly. Route all writes through a canonical sync node. This limits conflict surfaces: every write arrives at one place, goes through conflict resolution once, and fans out as a single decided state.

Track sync generation per entity. Add a syncGeneration integer to every synced record. Increment it on each fan-out. When a remote system pushes a change back to you that you originated (echo detection), compare generations. If the remote clock matches a generation you already applied, discard it. This prevents sync loops where a write echoes back through the system indefinitely.

Cap your divergence queue by age, not just count. A divergence record older than 7 days with more than 20 failed retries indicates a systemic failure in the connection to that system, not a transient error. Alert on this separately and stop retrying automatically to avoid masking the real problem.

Test partial failure explicitly. In staging, inject failures on 2 of 5 target systems during a fan-out. Verify that the divergence records are created, the reconciler repairs the diverged systems, and the final state matches the canonical record. Most sync engine bugs live in this path.

Separate sync reads from application reads. If your application and sync engine share the same database connection pool, a sync storm (mass re-sync after a schema migration) will degrade application latency. Give the sync engine its own pool with a hard cap.

Monitor sync lag per entity type, not just per system. Aggregate lag across all entities hides that room availability records are 40 minutes behind while reservation records are current. Per-entity-type lag metrics let you tune polling intervals and detect stuck cursors before they become customer-visible.

---

The conflict resolution strategy you choose matters less than the discipline of applying it consistently. Pick a strategy that matches your domain, make it explicit in code, and instrument every conflict that gets resolved. Systems that sync silently are systems that diverge invisibly.