ERP Integration Services: Fixing Lost Events

A common ERP integration services failure looks harmless in application logs:

Order 18492 updated successfully
HTTP 500: downstream inventory service unavailable

The ERP transaction committed. The event did not.

That leaves two systems disagreeing about the same order. A retry might create a duplicate. A manual sync might overwrite a newer value. A nightly reconciliation job might eventually hide the original failure.

This is where ERP integration services need more than API calls between systems. The integration needs a reliable boundary between the database transaction and the event delivery mechanism.

If you are dealing with ERP-to-CRM, accounting, inventory, ecommerce, or logistics integrations, the implementation details behind that boundary become important. Our ERP Integration Services work around these integration patterns, but this article focuses on one specific engineering problem: preventing a successful ERP write from becoming a lost integration event.

1. Start by reproducing the dual-write failure

The failure begins with a simple sequence:

1. Update the local database.
2. Publish an integration event.
3. Hope both operations succeed.

The naive implementation often looks like this:

// ERP integration services: the database commit can succeed while publish fails.
await db.query(
  "UPDATE orders SET status = $1 WHERE id = $2",
  ["confirmed", orderId]
);

await broker.publish("order.confirmed", {
  orderId
});

There is no atomic transaction across PostgreSQL and the broker.

If PostgreSQL commits and the broker is temporarily unavailable, the order is confirmed but no event exists.

This is the dual-write problem. AWS documents the transactional outbox pattern specifically for cases where a database update and event notification need to remain consistent.

The problem therefore changes from "How do we retry the API?" to "How do we guarantee that every committed business change produces a durable event?"

2. Put the event inside the database transaction

Once the failure is reproduced, the key change is to store the event before committing the transaction.

Create an outbox table:

-- The outbox makes the ERP change and event record part of one PostgreSQL transaction.
CREATE TABLE integration_outbox (
    id BIGSERIAL PRIMARY KEY,
    event_type TEXT NOT NULL,
    aggregate_id BIGINT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    published_at TIMESTAMPTZ
);

Then update the order and insert its event in the same transaction:

// Node.js + PostgreSQL: both writes commit or roll back together.
await db.query("BEGIN");

try {
  await db.query(
    "UPDATE orders SET status = $1 WHERE id = $2",
    ["confirmed", orderId]
  );

  await db.query(
    `INSERT INTO integration_outbox
     (event_type, aggregate_id, payload)
     VALUES ($1, $2, $3)`,
    [
      "order.confirmed",
      orderId,
      JSON.stringify({ orderId })
    ]
  );

  await db.query("COMMIT");
} catch (error) {
  await db.query("ROLLBACK");
  throw error;
}

Now the order update and event record share the same database transaction.

If the transaction rolls back, neither is committed.

If the transaction commits, the outbox record exists even when the message broker is temporarily unavailable.

AWS describes this same transactional outbox approach for maintaining consistency between application state and published events.

For Odoo integrations, the API layer is another important consideration. Odoo 19 provides the external JSON-2 API, so integrations should be designed around the API version actually deployed rather than assumptions from older RPC implementations.

3. Publish asynchronously instead of blocking the ERP request

With the event safely stored, the application no longer needs to keep the ERP request open while another system is contacted.

A worker can read unpublished events:

// The worker reads pending ERP integration events outside the original ERP request.
const { rows } = await db.query(`
  SELECT id, event_type, aggregate_id, payload
  FROM integration_outbox
  WHERE published_at IS NULL
  ORDER BY id
  LIMIT 100
`);

The worker publishes each event and marks it complete only after successful delivery:

// Mark published_at only after broker acknowledgement to support retry after failures.
for (const event of rows) {
  await broker.publish(event.event_type, event.payload);

  await db.query(
    `UPDATE integration_outbox
     SET published_at = NOW()
     WHERE id = $1`,
    [event.id]
  );
}

Multiple workers need coordination in production.

PostgreSQL provides FOR UPDATE SKIP LOCKED, which can help workers claim different pending rows without waiting on rows already locked by another worker:

-- Multiple workers can claim different pending events without waiting on locked rows.
SELECT id, event_type, payload
FROM integration_outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 100
FOR UPDATE SKIP LOCKED;

The exact queue strategy depends on throughput, broker semantics, retry policy, and whether duplicate delivery is acceptable.

The important boundary remains unchanged: the ERP transaction owns event creation, while the worker owns event delivery.

At this point, the architecture starts looking less like a collection of API calls and more like an integration platform. This is also where teams working across ERP, CRM, ecommerce, and finance systems need to think about observability, retries, authentication, and data mapping together rather than treating each API connection independently.

For examples of how these broader enterprise systems can be connected, you can also explore Oodles and its wider engineering capabilities.

4. Make the receiving system idempotent

The worker solves one side of the problem, but it introduces another reality: delivery can happen more than once.

Suppose the broker accepts an event. The worker publishes successfully. Before published_at is updated, the worker crashes.

The event can be delivered again.

The receiving system should therefore store an event identifier:

-- Consumers use this key to ignore an already-applied ERP integration event.
CREATE TABLE processed_events (
    event_id BIGINT PRIMARY KEY,
    processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The consumer checks this table before applying the business operation.

This gives the receiving system a simple rule:

Event received
      |
      v
Already processed?
   /         \
 Yes          No
 |             |
Ignore       Process
               |
               v
        Store event ID

The distinction matters because the transactional outbox pattern provides reliable event creation, but it does not magically provide exactly-once processing across independent systems.

The consumer still needs to tolerate retries.

5. We implemented this around an ERP-to-accounting integration

The remaining trade-off was latency versus consistency. A synchronous integration looked simpler, but it made ERP requests dependent on the availability of the accounting system.

We encountered this pattern while working on an ERP-to-accounting integration where business records had to move between systems without creating duplicate updates during temporary downstream failures.

We first considered direct API-to-API calls from the ERP transaction. That approach coupled the ERP request to the accounting API's response time and failure state.

We then separated persistence from delivery. The ERP-side transaction stored the business change and integration event together. A background worker handled downstream delivery, while the receiving side used an event identifier to make processing idempotent.

The result was a cleaner failure boundary. A temporary accounting-system outage no longer meant that the ERP transaction itself had to fail.

That measurement should come from project monitoring rather than an assumed benchmark. The architecture is reusable, but the performance result is specific to the deployment.

Key Takeaways

• ERP integration services should not rely on two independent writes. Database commits and event publication can fail independently.
• A transactional outbox makes event creation part of the database transaction. This prevents committed business changes from silently losing their integration event.
• Asynchronous workers isolate ERP requests from downstream outages. Failed deliveries can be retried without blocking the original transaction.
• Consumers must be idempotent. A worker can crash after publishing but before marking an event as complete.
• Production measurements matter. Track latency, failed deliveries, retries, duplicate events, and reconciliation volume instead of relying on generic performance claims.

How are you handling retries and duplicate events in your ERP integrations? If you're working through a similar integration architecture, share your approach or discuss the implementation with the Oodles team.

Frequently Asked Questions

What are ERP integration services?

ERP integration services connect an ERP system with other business applications such as CRM, accounting, ecommerce, inventory, logistics, and payment platforms. They typically use APIs, middleware, event-driven architecture, or scheduled synchronization to exchange business data.

Why do ERP integrations lose data?

A common cause is the dual-write problem. The ERP integration services database transaction can succeed while publishing the corresponding event fails. Without a transactional outbox or another durable integration mechanism, the downstream system may never receive the change.

What is a transactional outbox in ERP integration?

A transactional outbox stores the integration event in the same database transaction as the business change. A separate worker then publishes that event to the target system or message broker. This prevents an event from being lost after the ERP transaction has already committed.

Are ERP integrations always real-time?

No. The appropriate synchronization model depends on the business requirement. Real-time events work well for time-sensitive updates, while scheduled or batch synchronization can be suitable for reporting, reconciliation, and high-volume data transfers.

How do you prevent duplicate ERP integration events?

The receiving system should use idempotent processing. Each event can have a unique identifier that the consumer stores after successful processing. If the same event arrives again, the consumer can recognize it and avoid applying the business operation twice.

What systems can be connected through ERP integration services?

ERP integrations can connect systems such as CRM, accounting software, ecommerce platforms, warehouse management systems, payment gateways, logistics platforms, marketplaces, and custom business applications. The integration architecture depends on the APIs, data models, authentication methods, and synchronization requirements of each system.

When should an ERP integration use middleware?

Middleware becomes useful when multiple systems need to exchange data or when integrations require transformation, routing, validation, retries, authentication, monitoring, and centralized error handling. It can prevent business logic from becoming tightly coupled to individual ERP or third-party APIs.