ERP Integration Patterns

Connect ERP systems with modern applications using proven integration patterns. Covers point-to-point, middleware, API gateway, event-driven integration, master data management, and the patterns that prevent ERP integrations from becoming brittle spaghetti.

ERP integration patterns describe how enterprise resource planning systems exchange data with external systems—whether finance, supply chain, customer service, or analytics platforms. These patterns emerge when systems must synchronize data across boundaries, often in real time, with minimal latency and high reliability. They matter when a business process spans multiple systems: when a sales order in a CRM triggers a purchase order in the ERP, or when inventory updates in the warehouse system reflect immediately in the financial ledger. The patterns are essential when integration is not a one-off data transfer but a continuous, resilient, and observable flow.

Real-Time Synchronous Integration

Data flows between systems with immediate response, where the initiating system waits for the target system to complete the operation before proceeding. This pattern is used when a business process requires immediate confirmation—such as creating a customer in the ERP during a sales onboarding workflow.

The integration typically uses HTTP-based APIs, with the ERP exposing endpoints via REST or SOAP. A request to POST /api/v1/customers might include:

{
  "customerNumber": "CUST-000123",
  "name": "Acme Inc.",
  "billingAddress": {
    "street": "123 Main St",
    "city": "Anytown",
    "postalCode": "90210",
    "country": "US"
  },
  "contact": {
    "name": "Jane Doe",
    "email": "[email protected]"
  }
}

The ERP returns a 201 Created response with the new customer’s ID:

{
  "customerId": "CUST-000123",
  "externalId": "EXT-CUST-456",
  "createdTimestamp": "2025-04-05T12:00:00Z"
}

A failure in this pattern is a 503 Service Unavailable due to the ERP being overwhelmed. The caller must retry with exponential backoff. The most common silent failure is the idempotency key mismatch: a client sends a request with X-Idempotency-Key: 12345, but the ERP fails to store the key in a cache or database. On retry, the ERP processes the same request again, creating duplicate customers. The solution is to enforce idempotency at the ERP layer: upon receiving a request, check for the key in a idempotency_keys table with columns key, result, and created_at. If the key exists, return the stored result without processing the payload.

The most common configuration mistake is configuring the HTTP client to use Keep-Alive but not setting Connection: keep-alive in the request headers. Without this, the connection is closed after each request, negating the performance benefit.

Batch-Driven Asynchronous Integration

When real-time responsiveness is less critical, and data volumes are high, this pattern uses batch processing to transfer large sets of records—such as daily sales orders, monthly inventory counts, or end-of-month financial close data.

Data is collected in a staging area, often a database table or file system, and processed in batches at scheduled intervals. A typical workflow uses a file-based pipeline:

  1. The source system exports data to a CSV file in a shared directory.
  2. A scheduled job (e.g., cron) runs every 15 minutes:
   0,15,30,45 * * * * /opt/erp-integration/bin/process-batch.sh --source /data/incoming --target /data/processed
  1. The script reads the CSV, validates each row, transforms fields using a configuration file transforms.json:
   {
     "salesOrders": {
       "mapping": {
         "orderNumber": "OrderID",
         "customerId": "CustomerID",
         "totalAmount": {
           "source": "Amount",
           "transform": "multiply(1.08)"
         },
         "orderDate": {
           "source": "OrderDate",
           "format": "YYYY-MM-DD"
         }
       },
       "validation": {
         "required": ["orderNumber", "customerId"],
         "rules": [
           "orderNumber must be unique",
           "customerId must exist in ERP"
         ]
       }
     }
   }
  1. After processing, the script uploads the transformed data to the ERP via an API endpoint, POST /api/v1/batch/sales-orders.

The most common failure is file corruption during transfer. The source system writes the file to disk, but the network drops before the file is fully uploaded. The ERP receives a partial file and fails to parse it. The solution is to use a two-phase upload: first, upload the file with a .pending suffix, then rename it to .completed when done. The ERP watches the directory and processes only files ending in .completed.

Another silent failure is missing or misnamed files. The scheduler expects a file named sales-orders-2025-04-05.csv, but the source system generates sales_orders_2025-04-05.csv. The integration fails silently because the script uses glob patterns that are case-sensitive on Linux. The fix is to use find with -iname or configure the script to normalize filenames using rename or sed.

Change Data Capture (CDC)

This pattern tracks changes in the ERP’s database and propagates them to downstream systems in near real time. It is used when the ERP is the source of truth for master data (e.g., customers, products, vendors) and other systems must reflect changes without polling.

The ERP maintains a change log table—change_log—with columns: id, table_name, record_id, operation, data, timestamp. Each insert, update, or delete on a core entity triggers a row in this table. A consumer system, such as a data warehouse or a CRM, polls this table or subscribes to it via a message queue.

Example change record:

{
  "id": 4231,
  "table_name": "Customer",
  "record_id": "CUST-000123",
  "operation": "UPDATE",
  "data": {
    "name": "Acme Inc. (Updated)",
    "creditLimit": 50000,
    "lastUpdated": "2025-04-05T13:22:10Z"
  },
  "timestamp": "2025-04-05T13:22:11Z"
}

The consumer reads the change_log table in batches, using a cursor-based query:

SELECT id, table_name, record_id, operation, data, timestamp
FROM change_log
WHERE timestamp > '2025-04-05T13:22:10Z'
ORDER BY id
LIMIT 1000;

The most common failure is cursor drift: the consumer reads a batch of changes, processes them, and updates its last checkpoint (last_read_timestamp), but the database is not updated atomically. If the consumer crashes after processing the batch but before updating the checkpoint, it may reprocess the same changes on restart. To prevent this, the consumer uses a two-phase checkpoint update: first, it writes the new checkpoint to a integration_checkpoints table, then updates the last_read_timestamp in a transaction.

A subtle issue is change record duplication. The ERP writes a change record, but the database transaction fails before committing the change to the source table. The result is a change log entry with a record_id that does not yet exist in the target table. The consumer must handle this by validating the existence of the record before applying the change.

Event-Driven Integration with Message Queues

When integration must scale to high volumes and decouple systems, this pattern uses message queues to buffer events between systems. The ERP publishes events to a queue (e.g., Kafka, RabbitMQ, or AWS SQS), and other systems consume them asynchronously.

An event is a JSON object describing a business action:

{
  "eventId": "EVT-20250405-12345",
  "eventType": "SalesOrderCreated",
  "timestamp": "2025-04-05T12:00:00Z",
  "data": {
    "orderId": "SO-1001",
    "customerId": "CUST-000123",
    "orderDate": "2025-04-05",
    "items": [
      {
        "productId": "PROD-101",
        "quantity": 5,
        "unitPrice": 19.99
      }
    ],
    "totalAmount": 99.95
  }
}

Consumers subscribe to topics or queues using standard message protocols. A typical consumer uses kafka-consumer with a configuration file consumer.yml:

bootstrap.servers: "kafka1:9092,kafka2:9092"
group.id: "sales-integration"
auto.offset.reset: "latest"
enable.auto.commit: true
max.poll.records: 1000

The consumer polls the queue and processes each message in a loop:

for message in consumer:
    event = json.loads(message.value)
    if event["eventType"] == "SalesOrderCreated":
        db.execute(
            "INSERT INTO sales_orders (id, customer_id, order_date) VALUES (%s, %s, %s)",
            (event["data"]["orderId"], event["data"]["customerId"], event["data"]["orderDate"])
        )
    # Send confirmation to another queue
    producer.send("order-processed", {
        "orderId": event["data"]["orderId"],
        "status": "processed",
        "timestamp": datetime.now().isoformat()
    })

The most common failure is message loss during processing. The consumer reads a message, processes it, but crashes before committing the offset. The next consumer sees the message again and processes it a second time. The solution is to use idempotent processing: each event type has a unique eventId, and the target system checks for existence before processing.

Another failure mode is backpressure due to slow consumers. The ERP publishes 10,000 events per minute, but the consumer can only process 1,000 per minute. The queue grows, and messages age. The consumer must monitor lag—the difference between the latest message and the last processed message. A high lag triggers alerts and may require scaling the consumer group.

Idempotent and Reconcilable Integration

When integration must handle partial failures and data drift, this pattern ensures that operations can be safely retried, and systems can be reconciled to a consistent state.

The core idea is to assign a unique, stable identifier to every integration event—integration_id. This ID is used across systems to track the flow of data and to coordinate retries.

For example, when processing a purchase order, the integration writes:

{
  "integration_id": "INT-PO-20250405-1234",
  "source_system": "CRM",
  "operation": "CreatePurchaseOrder",
  "status": "completed",
  "timestamp": "2025-04-05T12:15:30Z",
  "payload": {
    "poNumber": "PO-1001",
    "vendorId": "VEND-001",
    "items": [...]
  },
  "external_references": {
    "erp_order_id": "ERP-PO-20250405-1234",
    "audit_log_id": "LOG-1024"
  }
}

This record is stored in a integration_events table, which is used for reconciliation and monitoring.

The most common failure is duplicate event processing. A system sends an event with integration_id, but the ERP receives it twice due to network retries. The solution is to use the integration_id as a primary key in the integration_events table and to enforce uniqueness. On insert, the ERP checks for the ID and, if it exists, returns 200 OK without processing the payload.

Another failure is out-of-order processing. The ERP receives events in the order: PO-1, PO-2, PO-3, but processes them in reverse. The result is mismatched data. The solution is to use sequence_number fields and ensure that each message includes a sequence value. Consumers can then re-order events and process them in sequence.

Reconciliation is done by periodically comparing the state of the source and target systems. A reconciliation script runs nightly:

#!/bin/bash

set -euo pipefail

DATE=$(date +%Y-%m-%d)

# Find all integration events for the last day
psql -c "
SELECT integration_id, source_system, operation, status
FROM integration_events
WHERE timestamp >= '$DATE 00:00:00'
  AND status = 'completed'
" | while read integration_id source_system operation status; do
    # Query ERP for the record
    ERP_RECORD=$(curl -s "https://erp.example.com/api/v1/$operation/$integration_id" | jq -c '.')
    
    # Query source system
    SOURCE_RECORD=$(curl -s "https://source.example.com/api/v1/$operation/$integration_id" | jq -c '.')

    # Compare
    if ! diff <(echo "$ERP_RECORD") <(echo "$SOURCE_RECORD"); then
        echo "Mismatch: $integration_id"
        # Log to reconciliation log
        echo "$DATE $integration_id $source_system $operation" >> /var/log/reconciliation.log
    fi
done

This script identifies drifts and provides a foundation for audit and debugging.

This page was rewritten on 18 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.