SfSkills Domains Docs GitHub

Home / integration / scheduled-erp-sync-pattern

scheduled-erp-sync-pattern

Scheduled batch ERP sync — timing, idempotency, error queues, Bulk API vs REST tradeoffs. Triggers: ERP sync schedule, nightly integration batch, ERP Salesforce sync. NOT for event-driven sync — use integration/event-driven-architecture-patterns. NOT for Bulk API job design — use data/bulk-api-patterns.

Open the skill folder on GitHub

Scheduled ERP Sync Pattern

Activate this skill when a user wants to set up — or fix — a Salesforce-initiated recurring data exchange with an enterprise ERP system on a fixed cadence (typically 15 minutes, hourly, or nightly). The skill produces an architecture and the Apex skeletons; it does not write the org-specific business logic that maps the staging object into the live data model.

Before Starting

Gather this context before proposing or building anything:

Core Concepts

Concept 1 — Cursor strategy: timestamp vs cursor vs full-refresh

A scheduled poll has to answer "what changed since last time?" The answer drives the entire design. Three modes, with sharply different failure profiles:

ModeHow it worksUse whenRisk
Timestamp (modifiedSince)Store the last-successful-poll UTC timestamp in a Custom Metadata or Custom Setting record. Each cycle queries ?modifiedSince=<watermark> and on success advances the watermark to the cycle start time, not "now".ERP exposes a reliable last-modified timestamp and clock skew between ERP and Salesforce is bounded.Clock skew + missed-records on ERP-side bulk loads that backdate timestamps.
Cursor / opaque tokenERP returns a continuation token (nextCursor, etag, sequence number). Salesforce stores the token and passes it back next cycle.ERP explicitly supports cursors (REST Link header pagination, opaque change-feed token, log-based CDC endpoint).Cursor invalidation if the ERP rotates or expires it; needs reset-to-full-refresh fallback.
Full-refresh with delta computationPull the full data set every cycle, diff against staging, derive a delta.ERP has no modifiedSince, no cursor, and the dataset is small (< a few thousand rows).Volume cost. Becomes infeasible above ~5K rows on a 15-minute cadence.

The biggest mistake here is advancing the watermark to Datetime.now() after the cycle finishes. If the cycle takes 90 seconds and the ERP modifies a record during second 30, that record's modifiedDate may be older than your post-cycle "now" and the next cycle will skip it. Always capture the watermark at cycle start and persist it only after end-to-end success.

Concept 2 — Idempotency: External ID upsert is non-negotiable

Every record fetched from the ERP must arrive at Salesforce with an External_Id__c (or domain-specific equivalent like ERP_Invoice_Number__c) marked as External ID + Unique on the target sObject. The DML is Database.upsert(records, ERP_Id__c) — never insert.

Why this matters in a scheduled pattern more than in a one-shot import:

For bidirectional sync, the conflict-resolution rule must be explicit and documented. The two viable options are (a) ERP wins by default (use LastModifiedDate comparison server-side and skip the SF-side update if ERP timestamp is older), or (b) field-level lineage tracking via a "last-changed-by-system" custom field. Silent last-writer-wins is not a strategy — it is a future data-corruption bug that surfaces at quarter-end.

Concept 3 — Error handling: retry strategy + dead-letter queue + N-failure alerting

A production scheduled-sync pattern has three error layers stacked:

  1. In-cycle retry with exponential backoff. Inside the Queueable, retry transient HTTP failures (5xx, 408, 429, network timeout) up to 3 times with Limits.getCallouts()-aware backoff. Never retry 4xx other than 408/429 — they indicate a permanent payload or auth issue. Important: a Queueable cannot Thread.sleep(); the "backoff" is achieved by re-enqueueing a new Queueable with a jittered delay, or by chaining and letting the platform schedule the next attempt.
  2. Dead-letter custom object (Integration_DLQ__c). When retries are exhausted on a record (not a whole cycle), write the failing payload, the failing endpoint, the HTTP status, the response body, and the cycle ID into Integration_DLQ__c. Do not silently log to debug logs; logs are not queryable, do not survive 24 hours, and cannot drive replay tooling. The DLQ object is the audit trail and the replay queue.
  3. N-consecutive-cycle alerting. A separate scheduled job (or a LimitException-handler in the Schedulable) tracks consecutive failed cycles. When the count exceeds a threshold (typically 3 for a 15-minute job, 1 for a nightly job), send a Messaging.CustomNotification to the integration ops group AND post to the integration-monitoring Slack/Teams channel via an Outbound Message or Apex callout. The threshold is tunable, but the principle is: don't alert on every cycle failure (transient) and don't wait for human discovery (silent).

The DLQ object should have at minimum: Cycle_Id__c, Record_Payload__c (LongTextArea, redact PII), Endpoint__c, Http_Status__c, Response_Body__c (LongTextArea, truncated to 32K), Failed_At__c, Retry_Count__c, Status__c (New | Replayed | Resolved | Permanent).

Concept 4 — Scaling: the volume thresholds that redirect to a different pattern

This skill's pattern is the right answer in a defined volume + cadence band. Outside that band, route to a different pattern explicitly:

Volume per cycleCadenceUse this pattern?If not, use
< 200 recordsAny cadenceYes — vanilla scheduled Apex + Queueable + REST callout—
200–2K records15-min / hourlyYes — but chain Queueables in batches of ~200 records per callout—
2K–10K records15-minBorderline — measure callout time + heap usage; consider stepping cadence to hourlyBulk API 2.0 ingest job from ERP-side; Salesforce becomes the target of an ERP-driven bulk load
> 10K recordsAnyNoBulk API 2.0 (ERP→SF) or platform-events/CDC if the requirement is "near-real-time" rather than "high volume"
Sub-minute latency requirement—NoPlatform Events (push from ERP) or Salesforce Pub/Sub API consumer

Two limits force the upper bound. First, an Apex transaction has a hard cap of 100 callouts per transaction (see Salesforce Apex Limits). Even chained Queueables can only do 100 each, so a single record per callout caps you at 100 records per Queueable; batching multiple records per request is mandatory at scale. Second, total daily callout time and outbound-volume governor limits accumulate across all scheduled jobs; a 15-minute cadence × 96 cycles/day × multiple Queueable chains is a non-trivial slice of org-wide capacity.

When the ERP supports it, Change Data Capture (CDC) consumed via the Pub/Sub API is almost always a better answer for bidirectional, near-real-time, high-volume sync. Polling is a pull pattern and pull patterns waste capacity at low-change times and underprovision at high-change times. Use scheduled poll only when CDC / Platform Events are unavailable on the ERP side, or when the cadence is genuinely slow (nightly / hourly with low volume) and the operational simplicity of polling beats the engineering cost of streaming.

  1. Confirm volume + cadence + cursor support. Use the Concept 4 table to verify the user's scenario fits the polling band. If volume is > 10K per cycle or sub-minute latency is required, escalate to Platform Events / CDC / Bulk API and stop here. If the ERP exposes no modifiedSince and no cursor, route to a full-refresh design and warn explicitly about the volume ceiling.
  2. Set up the Named Credential and External Credential. Choose Auth Provider type (OAuth 2.0 client credentials for server-to-server, JWT bearer for fine-grained service account flows, mTLS for high-security ERPs). Verify token refresh is automatic (Salesforce handles refresh on 401 only if the Named Credential is configured for it — do not store tokens in Custom Settings, that is anti-pattern #4 in references/llm-anti-patterns.md).
  3. Design the watermark store. Create a Custom Metadata Type ERP_Sync_Watermark__mdt with one record per (object, direction) pair, with fields Last_Successful_Cycle_Start__c (DateTime), Cursor_Token__c (Text 255), and Last_Synced_Record_Id__c (Text 36). Use Custom Metadata, not Custom Settings, because Custom Metadata is deployable as part of the package and can be diffed across environments; Custom Settings cannot.
  4. Implement the Schedulable + Queueable chain. The Schedulable's execute() must do zero callouts (see Gotcha 3 — Schedulable contexts cannot make callouts directly). Its only job is to (a) read the watermark, (b) capture the cycle start time, (c) System.enqueueJob() the first Queueable, and (d) return. The Queueable does the callouts, the staging upserts, and chains the next Queueable. See references/examples.md for the canonical skeleton.
  5. Build the staging object + downstream reconciliation. Land all ERP records into ERP_Stage__c first (External ID = ERP_Record_Id__c). A separate, independently-runnable Queueable reconciles staging → live records. Decoupling the callout from the data-model write means you can replay reconciliation from staging without re-calling the ERP.
  6. Implement the DLQ + alerting. Create Integration_DLQ__c with the schema in Concept 3. Implement a separate scheduled job that counts Status__c = 'New' records older than 1 hour and sends a Messaging.CustomNotification when the count is non-zero. Add a threshold for "N consecutive cycle failures" tracked in a Sync_Cycle_Run__c audit object.
  7. Write the test class with HttpCalloutMock. Cover: happy path, 5xx-then-recover, 4xx-permanent, watermark advance, watermark non-advance on failure, DLQ write on retry exhaustion, External ID upsert idempotency. Without a HttpCalloutMock test, the deployment will fail at the org-level test-coverage gate the moment a callout is added.

Review Checklist

Run through these before marking the integration complete:

Salesforce-Specific Gotchas

  1. Schedulable cannot make callouts directly — execute(SchedulableContext) runs in a context that does not permit callouts. The Schedulable must enqueue a Queueable, which does. See Gotcha 3 in references/gotchas.md.
  2. Watermark advanced to Datetime.now() after cycle skips records — capture at cycle start, never at cycle end. See Gotcha 5.
  3. Named Credential token refresh requires Auth Provider, not just header config — the "Per User" / "Named Principal" choice and the "Generate Authorization Header" flag must align. See Gotcha 2.

Output Artifacts

ArtifactDescription
Architecture diagramOne-page diagram naming Schedulable, Queueable, staging object, DLQ, watermark store, alerting hook
Apex skeletonsSchedulable, Queueable chain, DLQ writer, retry harness — see references/examples.md
Custom Metadata definitionERP_Sync_Watermark__mdt and ERP_Field_Mapping__mdt schemas
Test classHttpCalloutMock-backed test covering the 7 scenarios in Workflow step 7
Volume / cadence decision noteOne-line answer: "polling is correct here because…" or "escalate to CDC because…"

Package files