How do I keep duplicate records out of synced systems

To keep duplicate records out of synced systems, you need identity rules + matching + idempotent sync behavior + conflict handling.

1) Define a single “source of truth” for identity

Pick what makes a record unique:

• A stable external ID from the master system
• Or a composite key like email + tenant_id
• Avoid using mutable fields like name or phone alone

2) Use upserts, not blind inserts

When syncing:

• Check if the record already exists
• If yes, update
• If no, insert

This is usually done with an upsert or merge operation.

3) Store cross-system ID mappings

Keep a mapping table like:

• source_system
• source_record_id
• target_system
• target_record_id
• last_synced_at

This prevents the same record from being created twice in different systems.

4) Make sync jobs idempotent

A sync should be safe to run multiple times without creating duplicates:

• Use deterministic keys
• Deduplicate incoming events
• Ignore already-processed event IDs
• Track sync checkpoints

5) Match on more than one field if needed

If no stable ID exists, use a match strategy:

• Exact match on email / external ID
• Fuzzy match on name + DOB + address
• Then route ambiguous matches to manual review

6) Normalize data before matching

Duplicates often appear because of formatting differences:

• Trim spaces
• Lowercase emails
• Standardize phone numbers
• Normalize addresses
• Remove punctuation where appropriate

7) Put uniqueness constraints in the database

At the data layer, enforce:

• Unique index on the chosen business key
• Unique constraint on external IDs

This is your last line of defense.

8) Handle race conditions

Two syncs can try to create the same record at once. Prevent that with:

• Database uniqueness constraints
• Distributed locks if needed
• Transactional upserts

9) Reconcile periodically

Even with good sync logic, duplicates can happen. Run:

• Duplicate detection reports
• Merge/reconciliation workflows
• Audit logs for traceability

10) Decide how to resolve conflicts

If records differ:

• Prefer the authoritative system
• Use latest update time
• Merge only safe fields
• Escalate conflicting cases

Practical pattern

A strong setup usually looks like:

1. Receive change event
2. Check event ID has not been processed
3. Look up cross-system ID mapping
4. If mapping exists, update target record
5. If not, match on business key
6. If unique match found, update and store mapping
7. If no match, create record and store mapping
8. If ambiguous, flag for review

If you want, I can also give you:

• a database schema for deduplication
• an API sync pattern
• or example code in SQL, Python, or JavaScript.