scheduled-erp-sync-pattern

# 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:

- **Direction of the sync.** Outbound-only (SF→ERP), inbound-only (ERP→SF), or bidirectional. Bidirectional needs a conflict-resolution rule, never silent last-writer-wins.
- **Authentication mechanism the ERP supports.** OAuth 2.0 client credentials, JWT bearer flow for service accounts, mTLS, and (rarely) basic auth all map to a Named Credential + External Credential, but they do *not* configure identically. Skip this and you will hit token-refresh outages later.
- **Whether the ERP supports a `modifiedSince` / cursor parameter.** This decides watermark mode. Some legacy ERP REST APIs only offer full-table endpoints; that pushes you toward CDC or Bulk API patterns instead of polling.
- **Stable ERP-side primary key.** If the ERP record has no immutable ID, you cannot do upsert-by-External-ID and the integration will create duplicates on every retry. This is not a fixable Salesforce-side problem — it is an ERP-side prerequisite.
- **Volume and burst profile.** A "15-minute sync" of 50 records per cycle is a different design than 50K. The volume bands at the bottom of *Concept 4* are the deciding factor between this pattern and the alternatives.
- **What "the integration is broken" should look like.** The DLQ + alerting design (Concept 3) only works if the team has decided what failure looks like — N consecutive cycle failures, a record-count anomaly, a watermark that hasn't advanced, or all three.

---

## 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:

| Mode | How it works | Use when | Risk |
|---|---|---|---|
| **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 token** | ERP 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 computation** | Pull 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:

- A schedulable cycle that throws after partial commit will be retried — either by the platform's retry semantics or by your own DLQ replay. Without an External ID, replays create duplicates indefinitely.
- A bidirectional sync with last-writer-wins logic needs a stable join key on both sides. Without it you cannot tell whether SF Account #5 maps to ERP Customer #12345 — and the wrong join means writing a customer's address onto a different customer's record.
- ERP outages cause partial replication. When the ERP comes back, you re-poll the same `modifiedSince` window. Without idempotency you double-write everything you successfully fetched on the previous attempt.

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 cycle | Cadence | Use this pattern? | If not, use |
|---|---|---|---|
| < 200 records | Any cadence | Yes — vanilla scheduled Apex + Queueable + REST callout | — |
| 200–2K records | 15-min / hourly | Yes — but chain Queueables in batches of ~200 records per callout | — |
| 2K–10K records | 15-min | Borderline — measure callout time + heap usage; consider stepping cadence to hourly | Bulk API 2.0 ingest job from ERP-side; Salesforce becomes the *target* of an ERP-driven bulk load |
| > 10K records | Any | No | Bulk API 2.0 (ERP→SF) or platform-events/CDC if the requirement is "near-real-time" rather than "high volume" |
| Sub-minute latency requirement | — | No | Platform 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.

---

## Recommended Workflow

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:

- [ ] Named Credential created; no hardcoded URLs or tokens in Apex
- [ ] Watermark stored in Custom Metadata (not Custom Setting), captured at *cycle start*, advanced only on end-to-end success
- [ ] Every staged record has an External ID + Unique field for upsert
- [ ] Schedulable does no callouts; Queueable does all callouts (`Database.AllowsCallouts` interface implemented)
- [ ] DLQ object exists with the 8 required fields; retry exhaustion writes to DLQ
- [ ] N-consecutive-failure alert wired to Custom Notification + monitoring channel
- [ ] HttpCalloutMock test covers happy / 5xx-recover / 4xx-permanent / DLQ paths
- [ ] Volume profile re-checked against Concept 4 table

---

## 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

| Artifact | Description |
|---|---|
| Architecture diagram | One-page diagram naming Schedulable, Queueable, staging object, DLQ, watermark store, alerting hook |
| Apex skeletons | Schedulable, Queueable chain, DLQ writer, retry harness — see `references/examples.md` |
| Custom Metadata definition | `ERP_Sync_Watermark__mdt` and `ERP_Field_Mapping__mdt` schemas |
| Test class | `HttpCalloutMock`-backed test covering the 7 scenarios in Workflow step 7 |
| Volume / cadence decision note | One-line answer: "polling is correct here because…" or "escalate to CDC because…" |

---

## Related Skills

- `integration/platform-events-publish-subscribe` — when ERP can publish near-real-time events instead of being polled
- `integration/change-data-capture-consumer-pattern` — when the data direction is ERP→SF and CDC on the ERP side is available
- `apex/apex-queueable-chain-pattern` — deeper coverage of Queueable chaining, governor reset, depth limits
- `data/data-loader-bulk-api` — when the volume tips past 10K records per cycle and the right answer is Bulk API 2.0 ingest
- `architect/mulesoft-vs-native-integration-decision` — when the integration is part of a larger middleware footprint and an iPaaS layer changes the calculus
- `security/named-credentials-and-external-credentials` — depth on auth provider configuration, token refresh, mTLS, JWT bearer