How do I build a reliable invoice sync with retries and reconciliation?

A reliable invoice sync usually needs three layers:

1. Idempotent writes so retries don’t create duplicates
2. A retry queue for transient failures
3. A reconciliation loop to detect and fix drift between systems

Here’s a practical pattern.

---

1) Define the sync contract

Before code, decide:

• System of record: where invoices are created/updated first
• Target system: where they must be replicated
• Conflict policy: last-write-wins, source-wins, or manual review
• Identity mapping: how you match the same invoice across systems
  • Usually store external_invoice_id, source_system, and a stable internal sync_key

---

2) Make every write idempotent

Retries only work safely if the operation can be repeated.

Use:

• Idempotency keys for create requests
• Upserts instead of blind inserts
• A dedupe table or unique constraint on:
  • (source_system, external_invoice_id)
  • or (target_system, idempotency_key)

Example

If invoice INV-123 is sent twice:

• First request creates it
• Second request detects it already exists and returns the existing record

Important

Never rely on “check then insert” without a uniqueness constraint. That race condition will bite you.

---

3) Put sync work onto a durable queue

Don’t sync inline in the user request path if you want reliability.

Flow

1. Invoice changes in source system
2. Write an event/job to a durable queue/table
3. A worker processes the job
4. On failure, retry with backoff

Why

• Prevents user-facing timeouts
• Lets you retry safely
• Survives app restarts

Queue options

• SQS / RabbitMQ / Kafka
• Database-backed job table
• Workflow engines like Temporal, Sidekiq, Celery, BullMQ

---

4) Use retry rules that distinguish failure types

Not all errors should be retried.

Retryable

• Network timeouts
• 429 rate limits
• 5xx server errors
• transient dependency outages

Non-retryable

• Validation errors
• Unauthorized/forbidden
• Bad schema/data
• Duplicate invoice with conflicting data

Recommended retry policy

• Exponential backoff
• Jitter
• Max attempts
• Dead-letter queue after exhaustion

Example:

• Retry after 1 min, 5 min, 15 min, 1 hr, 6 hr
• Stop after 10 attempts
• Send to DLQ and alert

---

5) Track sync state explicitly

Each invoice should have sync metadata, something like:

• sync_status: pending | synced | failed | needs_reconciliation
• last_synced_at
• last_sync_attempt_at
• retry_count
• last_error_code
• last_error_message
• source_version or updated_at watermark
• target_version or remote revision if available

This makes debugging and reconciliation much easier.

---

6) Use optimistic concurrency where possible

If the target system supports versioning, use it.

Pattern

• Read invoice from target with version/etag
• Update only if version matches
• If version changed, detect conflict and reconcile

This prevents overwriting newer changes made elsewhere.

---

7) Build a reconciliation job

Retries handle transient failures. Reconciliation handles silent drift.

Drift examples

• Job was never created
• Job succeeded partially
• Target data changed manually
• Source and target got out of sync after retries exhausted

Reconciliation strategy

Run a periodic job that:

1. Compares source invoices and target invoices
2. Detects missing/changed records
3. Requeues repair jobs or flags conflicts

Common approaches

• Watermark-based sync
  • Track the last successful updated_at
  • Re-scan changed invoices since then
• Full checksum reconciliation
  • Compare hashes of normalized invoice payloads
• Spot checks / daily full audit
  • Good when volume is manageable

Strong recommendation

Use:

• Incremental sync for normal flow
• Periodic full reconciliation for correctness

---

8) Make reconciliation idempotent too

The reconciliation job should not create duplicate repair actions.

Use:

• a reconciliation run ID
• unique repair tasks keyed by (invoice_id, discrepancy_type, run_id) or similar
• upserts for repair state

---

9) Handle partial failures carefully

An invoice sync often has multiple steps:

• create invoice
• create line items
• attach payment terms
• mark status
• upload tax info

If step 3 fails, the earlier steps may already have succeeded.

Best practice

• Make each step idempotent
• Persist step progress
• Resume from the failed step
• Or use a single atomic API if available

---

10) Alert on systemic failure, not every retry

Retries are normal. Alert when patterns suggest a real issue.

Alert on:

• High retry rate
• DLQ growth
• Reconciliation mismatch rate above threshold
• Same error repeated across many invoices
• Sync lag exceeding SLA

Don’t alert on:

• One-off transient network errors

---

11) A good reference architecture

Source app

• Writes invoice
• Stores invoice version + sync state
• Emits event/job

Durable queue

• Holds sync tasks

Sync worker

• Pulls job
• Calls target API with idempotency key
• Updates sync state
• Retries on transient errors

Reconciler

• Periodically scans source vs target
• Requeues missing/out-of-date invoices
• Escalates conflicts

Monitoring

• Metrics, logs, DLQ, alerting

---

12) Practical data model example

invoices
- id
- source_system
- external_invoice_id
- amount
- currency
- status
- updated_at
- sync_status
- last_synced_at
- sync_version
- last_error

invoice_sync_jobs
- job_id
- invoice_id
- job_type (create/update/reconcile)
- status
- attempt_count
- next_attempt_at
- idempotency_key
- last_error

Unique constraint:

UNIQUE(source_system, external_invoice_id)

---

13) Sync logic pseudocode

function syncInvoice(invoice):
    key = hash(invoice.source_system, invoice.external_invoice_id, invoice.updated_at)

    if alreadyProcessed(key):
        return success

    try:
        targetRecord = findTargetInvoice(invoice.external_invoice_id)

        if not targetRecord:
            createTargetInvoice(invoice, idempotencyKey=key)
        else:
            updateTargetInvoice(targetRecord.id, invoice, etag=targetRecord.version)

        markProcessed(key)
        updateSyncState(invoice.id, "synced")
    catch transientError:
        scheduleRetry(invoice.id, exponentialBackoff())
    catch conflictError:
        markSyncState(invoice.id, "needs_reconciliation")
    catch validationError:
        markSyncState(invoice.id, "failed")

---

14) What “good” looks like

A reliable invoice sync system should be able to answer:

• Which invoices are unsynced?
• Why did a sync fail?
• Was the target already updated?
• Can I safely retry this job?
• What drift exists right now?
• How do I repair it without duplication?

If you can answer those, you’re in good shape.

---

15) Recommended minimal implementation

If you want the simplest solid version:

• Unique constraint on invoice identity
• Queue-based async sync
• Idempotency key on target API calls
• Exponential backoff retries
• Dead-letter queue
• Nightly reconciliation scan
• Metrics + alerts

That’s usually enough to be robust in production.

---

If you want, I can also give you:

1. a PostgreSQL schema for this,
2. a Python/Node worker implementation, or
3. a step-by-step reconciliation algorithm.