Integration Patterns

Most ERP development is integration. An ERP with 40 interfaces is normal; 150 is common in a large enterprise. This is where you’ll spend most of your career.

The integration landscape

                              ┌─────────┐
   CRM ────────┐              │   ERP   │            ┌──── Banks
   E-commerce ─┤              │         │            ├──── Tax authority
   Marketplace ┼─────────────▶│         │◀───────────┼──── Customs
   POS ────────┤              │         │            ├──── Carriers
   Supplier    │              │         │            ├──── 3PL / WMS
   portals ────┘              │         │            ├──── MES
                              │         │            ├──── Payroll provider
   Warehouse ◀───────────────▶│         │◀──────────▶├──── Tax engine
   MES       ◀───────────────▶│         │            ├──── Credit bureau
   PLM       ◀───────────────▶│         │            └──── BI / warehouse
                              └─────────┘

The four styles

Style	Latency	Coupling	Use
File transfer	Hours	Loosest	Bulk, legacy, banks, payroll
Shared database	Instant	Tightest	Avoid. Two systems owning one schema
RPC / API	Milliseconds	Medium	Real-time queries and commands
Messaging / events	Seconds	Loose	Async notification, decoupled processing

Shared database integration is an anti-pattern in almost every case: neither system can change its schema, transactions cross ownership boundaries, and there’s no contract. The exception is a read-only replica for reporting.

Synchronous vs asynchronous

SYNCHRONOUS
  Caller ──request──▶ ERP ──▶ process ──▶ response ──▶ Caller
  ✅ Immediate result, simple error handling
  ❌ Caller blocked; ERP downtime breaks the caller; slow calls cascade

ASYNCHRONOUS
  Caller ──message──▶ Queue ──▶ ERP processes later
                       │
                       └──▶ callback / webhook / polling for the result
  ✅ Resilient, absorbs load spikes, ERP can be down briefly
  ❌ No immediate answer; needs status tracking and correlation

The rule: synchronous for queries and for things a human is waiting on (stock check, price check, credit check). Asynchronous for commands that change state (create order, post invoice, confirm shipment).

Never make document posting depend on a synchronous external call. Post first, then submit asynchronously with retries. An outage at a tax portal or a carrier API should not stop your ledger.

The canonical integration flow

Source ──▶ Extract ──▶ Transform ──▶ Validate ──▶ Load ──▶ Confirm
                                          │
                                     Error queue ──▶ Human ──▶ Reprocess

Every one of those steps needs to exist. The most commonly skipped are validate (garbage arrives and posts) and confirm (nobody knows whether it worked).

Idempotency and ordering

Two properties every interface needs:

Idempotency — see Concurrency and Idempotency. Derive the key from the business event, never from a per-attempt UUID.

Ordering — some messages must be processed in sequence:

CustomerCreated → CustomerUpdated → OrderCreated

Options: a single partition per key (all messages for customer 100 in order), sequence numbers with buffering for out-of-order arrivals, or designing messages to be order-independent (full-state snapshots rather than deltas).

Full-state messages are usually better than deltas for master data. “Here is customer 100 as it now stands” is idempotent and order-tolerant. “Increment the credit limit by 5,000” is neither.

Error handling

Transient (network, timeout, 503)     → retry with exponential backoff + jitter
Validation (bad data)                 → dead letter, notify the source, do NOT retry
Business (period closed, credit block)→ dead letter with a business-readable reason
Unknown                               → dead letter, alert, investigate

Classify errors. Retrying a validation error 500 times helps nobody and hides the real problem. Retrying a transient error zero times loses data.

Dead letter queues need: the payload, the error, the retry history, an owner, an alert, and a reprocess action. A DLQ nobody monitors is data loss with extra steps.

Reconciliation

Every interface needs a reconciliation process. This is the most-skipped and most-valuable practice in ERP integration.

Daily:
  Source sent:      1,247 orders
  Target received:  1,247
  Target processed: 1,243
  Errors:               4  ← listed, with reasons and owners
  Value sent:      4,882,140
  Value processed: 4,871,320
  Difference:         10,820  ← must be explained

Publish it every morning. Without reconciliation, an interface that silently drops 2% of messages runs for months before anyone notices — usually when someone asks why a customer’s account doesn’t balance.

Middleware

Approach	When
Point-to-point	1–5 interfaces. Simple, becomes spaghetti beyond that
ESB / iPaaS	10+ interfaces. Central routing, transformation, monitoring
Event broker (Kafka, RabbitMQ, EventBridge, Pub/Sub)	Event-driven architecture, many consumers per event
API gateway	Managing inbound APIs: auth, rate limiting, versioning

Common ERP middleware: SAP PI/PO and Integration Suite (CPI), MuleSoft, Dell Boomi, Azure Integration Services (Logic Apps + Service Bus), Oracle Integration Cloud, Workato, Celigo, TIBCO, webMethods.

POINT TO POINT (n² connections)      HUB AND SPOKE (n connections)
  A ──── B                              A ──┐
  │ ╲  ╱ │                              B ──┼──▶ HUB ◀──┬── D
  │  ╳   │                              C ──┘           └── E
  C ──── D

The n² problem is real: 10 systems point-to-point is up to 45 interfaces to build and maintain. It’s the standard argument for middleware, and it’s a good one past about 8 systems.

Master data integration

The hardest integration problem, because it’s about authority, not plumbing.

Who owns the customer record?
  CRM creates prospects → ERP creates the billing account?
  Or ERP is the master and CRM subscribes?
  What if both can create?

Patterns:

Pattern	Description
Single master	One system creates; all others subscribe read-only. Cleanest
Federated	Different systems own different attributes (CRM owns contact preferences, ERP owns credit terms)
MDM hub	A dedicated system owns the golden record; all others subscribe
Bidirectional sync	Both can create/edit. Avoid — conflict resolution is unsolvable in general

Decide master data ownership on day one of the project. Every unresolved ownership question becomes a data quality problem within six months.

Cross-reference tables are mandatory:

external_id_map(
  entity_type,       -- CUSTOMER | MATERIAL | ORDER
  internal_id,
  external_system,   -- SALESFORCE | SHOPIFY | WMS
  external_id,
  synced_at,
  PRIMARY KEY (entity_type, external_system, external_id)
);

Never assume IDs match across systems. They never do.

Common ERP integrations and their traps

Integration	Trap
E-commerce → ERP orders	Order volume spikes; payment status vs order status; guest checkout has no customer master
ERP → WMS	Partial picks; the WMS must work offline; batch determination ownership
ERP → MES	High-frequency confirmations swamping the ERP; aggregate first
Bank statements → ERP	Format variety; text parsing for references; a missing day
ERP → payment file	Signature, dual control, no post-approval edits
CRM ↔ ERP	Master data ownership; duplicate customers
ERP → tax engine	Synchronous latency; must have a fallback rate
ERP → e-invoicing portal	Government API downtime; clearance status machine
Carrier / 3PL	Every carrier’s API is different; status code mapping
Payroll provider	Sensitive data; strict encryption; monthly cadence with a hard deadline
PLM → ERP	eBOM vs mBOM transformation; engineering change effectivity

Security

Authentication: OAuth 2.0 client credentials or mTLS. Not basic auth over the internet
Named service accounts per interface, least privilege, never shared
Secrets in a vault, rotated, never in config files or source control
Transport: TLS 1.2+ everywhere; SFTP with key auth for files
Payload encryption for sensitive data (payroll, personal data) even over TLS
IP allowlisting where the partner supports it
Signature verification on inbound webhooks
Rate limiting on inbound APIs
Audit log of every message: who, when, what, and the outcome

Interface service accounts are a common audit finding. One shared “API user” with wide permissions, credentials in a config file, no rotation. Name them, scope them, vault them.

Monitoring

Signal	Alert on
Message throughput	Sudden drop (a silent interface is worse than a failing one)
Error rate	Above threshold
Queue depth	Growing
Processing lag	Above SLA
DLQ depth	Anything > 0 for critical interfaces
Reconciliation difference	Non-zero
Partner API availability	Down
Certificate expiry	30 days out — expired certs are a top cause of interface outages