Can timestamps be used as synchronization checkpoints?
Yes, but include an overlap window and deduplication because clocks, pagination, and equal timestamps can cause gaps. Provider cursors are preferable when they have documented guarantees.
Automation & Integrations
Designing a Reliable Data Sync Between Two Business Systems
Use stable identity, field ownership, idempotent writes, checkpoints, and reconciliation to keep two connected systems consistent.
How the workflow connects
- 1Read changes from the authoritative source
- 2Resolve the cross-system identity
- 3Transform and validate owned fields
- 4Write idempotently to the destination
- 5Checkpoint and reconcile
Connecting two APIs is often straightforward. The harder work is keeping their records aligned through retries, edits, deletions, rate limits, and partial outages. A CRM-to-accounting customer sync provides a practical example of how identity, ownership, and recovery work together.
Create a durable cross-system identity map
Store the source identifier, destination identifier, entity type, connection, creation time, and last confirmed versions. Do not rely only on email, name, or SKU because those values can change or collide. When matching existing records is necessary, place uncertain matches in a review queue.
Assign ownership at the field level
The CRM may own customer contact details while accounting owns tax status and credit terms. Write a table that names the authority, update direction, transformation, and behaviour when the destination was edited manually. Bidirectional sync without ownership rules produces loops and lost changes.
- Business field and source field
- Authoritative system
- Transformation and validation
- Conflict behaviour
- Permitted synchronization delay
Make every write safe to repeat
Use a stable idempotency key or upsert identity for create and update requests. Store the outgoing request, attempt, response, and resulting destination identifier. A timeout means the outcome is unknown; check the destination before sending another create request.
Checkpoint progress and reconcile drift
A checkpoint records how far a change feed or batch progressed. Advance it only after the corresponding records are durably processed or queued for explicit recovery. Run periodic comparisons for missing links, changed owned fields, and records stuck in retry so silent gaps become actionable work.
Apply ownership rules to a CRM-accounting record
Apply the field-level model to one customer record shared by a CRM and accounting system. The CRM can own sales contact details while accounting owns tax treatment and credit status; support notes may remain private to the CRM. The result is a deliberate exchange of specific fields, not a broad promise to keep every copy identical.
For each mapped field, record what happens when someone edits a destination copy, when the authoritative value is blank, and when the destination cannot represent the source value. An explicit no-sync decision is safer than an unexplained bidirectional update.
| Field | Authority | Sync direction | Conflict response |
|---|---|---|---|
| Customer identity | Identity map | Linked once, then verified | Queue uncertain matches |
| Primary contact | CRM | CRM → accounting | Restore CRM value and log change |
| Tax status | Accounting | Accounting → CRM | Block CRM overwrite |
| Internal notes | Neither shared | Do not sync | Keep inside source system |
Design the outage and recovery path
Suppose the accounting API times out after a customer-create request. The integration cannot tell whether the destination accepted the record. Retrying immediately with a new request can create a duplicate. Store the request with its idempotency key, mark the result unknown, and query by that key or another safe reference before deciding whether to retry.
When the destination remains unavailable, retain the work in a retryable queue with backoff and an attempt limit. Continue accepting source changes only if the business can tolerate the delay. Once connectivity returns, process the backlog in order where sequence matters, then reconcile recently changed records to find anything the event stream missed.
Visual guide
The reliability layers around an API write
Each layer answers a different operational question; none is replaced by simply retrying the request.
- 1
Identity
Which two records represent the same business entity?
- 2
Validation
Is the transformed record complete and permitted?
- 3
Idempotency
Can the same intended change be submitted safely again?
- 4
Checkpoint
How far has durable processing progressed?
- 5
Retry queue
Which temporary failures still need another attempt?
- 6
Reconciliation
What differs between the systems after processing?
Practical questions
Questions that often come up
How should deletions be synchronized?
Define archive, deactivate, or delete behaviour by entity and policy. Hard deletion can be unsafe when downstream records or audit requirements remain.
Continue exploring
Related projects and tutorials
Work with Sun Cluster
Planning a similar system for your organization?
Sun Cluster connects business systems with explicit data ownership, secure access, recovery, logging, and reconciliation.