Connecting OpenCart to an ERP: patterns that survive real traffic

The demo integration and the real one

Everybody’s first ERP integration works. It is a function called at the end of checkout that posts the order and returns. It passes review, it runs for a week, and then the ERP has an unannounced maintenance window on a Friday evening and a few dozen orders quietly never arrive — nothing failing loudly enough to notice until Monday, when the warehouse asks why it is idle.

That is the whole difference between a demo integration and a production one: what happens when the other side is down. The patterns below go between OpenCart and any ERP — the product barely matters. They cost about a day up front and save the week you would spend rebuilding a day of orders from e-mail.

Queue table

oc_erp_outbox

Delivery guarantee

at-least-once

Consumer requirement

idempotent

Write to your own table, not to their API

Never call the ERP from inside the request serving a customer. Write a row to a table in your own database and let a separate worker deliver it. If the ERP is down, the row waits. If the ERP is slow, checkout is not. If the payload turns out to be wrong, you still have it, unlike a failed HTTP POST.

CREATE TABLE `oc_erp_outbox` (
  `outbox_id`       INT(11)      NOT NULL AUTO_INCREMENT,
  `entity`          VARCHAR(32)  NOT NULL,   -- order, customer, product
  `entity_id`       INT(11)      NOT NULL,
  `event`           VARCHAR(32)  NOT NULL,   -- created, status_changed
  `idempotency_key` CHAR(64)     NOT NULL,
  `payload`         MEDIUMTEXT   NOT NULL,
  `status`          ENUM('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `attempts`        TINYINT(3)   NOT NULL DEFAULT 0,
  `next_attempt`    DATETIME     NOT NULL,
  `last_error`      VARCHAR(255) NOT NULL DEFAULT '',
  `date_added`      DATETIME     NOT NULL,
  PRIMARY KEY (`outbox_id`),
  UNIQUE KEY `idem` (`idempotency_key`),
  KEY `due` (`status`, `next_attempt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Fill the row from an event handler rather than a patched checkout controller, so the next OpenCart update cannot remove your integration. Then let the worker claim rows in a way that survives two workers running at once:

START TRANSACTION;

SELECT * FROM `oc_erp_outbox`
 WHERE `status` = 'pending'
   AND `next_attempt` <= NOW()
 ORDER BY `outbox_id`
 LIMIT 20
 FOR UPDATE SKIP LOCKED;   -- MySQL 8.0+ / MariaDB 10.6+

• On older MySQL, replace SKIP LOCKED with a claim UPDATE that stamps a worker id and a timestamp, then re-select the rows you stamped.
• Order matters per entity. Two status updates for the same order must not arrive reversed, so process by entity and entity_id in outbox_id order and stop that entity’s queue at its first failure.
• Keep the worker short-lived: a cron job that runs, drains what is due and exits is easier to reason about than a daemon nobody remembers to restart.

An idempotency key on every message

A queue gives you at-least-once delivery, which means you will send the same message twice: a timeout where the ERP actually received it, a crash between the API call and the status update, an operator re-queueing after an incident. So every message carries a key the receiver can deduplicate on — derived from the event, never from the clock or a random value.

$key = hash('sha256', implode('|', [
    'order',            // entity
    (int)$order_id,     // entity id
    'status_changed',   // event
    (int)$order_status_id  // the state being reported
]));

Because the key is deterministic, hashing the same event twice produces the same string, and the UNIQUE index on the outbox rejects the duplicate before it reaches the network. If the ERP also honours the key — most accept one in an Idempotency-Key header — you are protected on both sides. If it does not, ask for an endpoint that accepts your reference and returns the existing document instead of creating a second one.

Backoff, and somewhere to give up

Separate retryable failures from terminal ones before writing the retry logic. A timeout, a connection reset or a 503 will probably succeed later. A 422 about a malformed tax number never will, and retrying it every minute is a busy loop nobody notices until the log partition fills.

// After a failed attempt
$attempts = (int)$row['attempts'] + 1;
$backoff  = min(3600, (int)pow(2, $attempts) * 30);  // 60s, 120s, 240s ... capped at 1h
$delay    = $backoff + random_int(0, (int)($backoff / 5));  // jitter
$status   = $attempts >= 8 ? 'failed' : 'pending';

$this->db->query("UPDATE `oc_erp_outbox`
    SET `attempts` = " . $attempts . ",
        `status` = '" . $status . "',
        `next_attempt` = DATE_ADD(NOW(), INTERVAL " . (int)$delay . " SECOND),
        `last_error` = '" . $this->db->escape(mb_substr($error, 0, 255)) . "'
    WHERE `outbox_id` = " . (int)$row['outbox_id']);

A row that reaches the failed state should reach a human; an e-mail to the operations address is enough. A dead-letter queue nobody reads is no queue at all, only with more storage.

Stock: decide who owns the number

One question decides this design: who owns the stock quantity? If the ERP owns it, OpenCart must never write it except by applying what the ERP sent, and you accept a storefront number a few minutes old. If OpenCart owns it, the ERP is a reporting consumer. What never works is both — that is how a product sells out twice in the same hour.

OpenCart subtracts stock at checkout wherever oc_product.subtract is set, and options carry their own quantities in oc_product_option_value. If the ERP also pushes absolute quantities on a schedule, the two writers race and the winner is whoever wrote last. Three arrangements hold up:

• ERP owns stock and OpenCart keeps subtracting. The ERP pushes absolute values and its number wins at the next sync. Slightly stale, simple, correct enough for most catalogues.
• ERP owns stock and OpenCart’s subtract is switched off, with a push after every order. Accurate, but the storefront is exposed for as long as the ERP takes to answer.
• Reserve on order: the ERP holds a reservation and OpenCart reads availability instead of storing it. The most work, and the right answer for low-stock, high-demand catalogues.

UPDATE `oc_product`
   SET `quantity` = ?, `date_modified` = NOW()
 WHERE `product_id` = ?
   AND `sku` = ?;   -- match the ERP's own key too, not the id alone

Order status without the feedback loop

oc_order_history is the audit trail: one row per status change with a comment and a notify flag, and oc_order.order_status_id follows it. Adding a history row is the correct way to move an order; writing the status column directly leaves the timeline lying to whoever reads it next.

The classic bug here is a loop. The ERP sets a status, OpenCart fires an event, the outbox enqueues a status_changed message, the worker posts it, the ERP echoes it back, and the order collects a hundred identical history rows overnight. Break it by tagging the origin of each write and never enqueueing what came from the ERP.

// Applying a status that came from the ERP - deliberately not enqueued
$this->load->model('checkout/order');

$this->model_checkout_order->addHistory(   // 3.0.x calls this addOrderHistory()
    (int)$order_id,
    (int)$status_map[$erp_status],  // resolved from oc_order_status, never hardcoded
    'ERP ' . $erp_document_no,      // shows up in the order timeline
    false                          // notify: the ERP already mailed the customer
);

Status ids are store data, not constants — read oc_order_status at deploy time and build the map from it, because a store that added “Awaiting stock” will not match anybody’s hardcoded list. And rows in oc_order with order_status_id = 0 are unconfirmed baskets, not sales; they must never enter the ERP feed.

The nightly report that makes silence visible

Every queue eventually loses something: a row removed by a well-meaning cleanup script, a message the ERP accepted and dropped, a week when somebody disabled the cron. The queue cannot tell you about the messages it never held. A reconciliation job can.

Run it nightly and compare yesterday on both sides — order count and revenue total, products with non-zero stock, new customers. Report differences by id, not as a number: “3 orders missing” is an alarm; “orders 10412, 10419 and 10433 are missing” is a task somebody can finish before lunch.

-- OpenCart side of the nightly comparison
SELECT DATE(`date_added`)      AS day,
       COUNT(*)                AS orders,
       ROUND(SUM(`total`), 2)  AS revenue
FROM `oc_order`
WHERE `order_status_id` > 0
  AND `date_added` >= CURDATE() - INTERVAL 1 DAY
  AND `date_added` <  CURDATE()
GROUP BY day;

Send the report even when it is clean. A report that only arrives when something is wrong is a report nobody notices has stopped arriving.

Two directions, two clocks

OpenCart to ERP is your outbox and your worker. ERP to OpenCart is a webhook — a public endpoint, no matter what the vendor calls it. Four rules make it safe:

1. 1Verify an HMAC signature over the raw request body with a shared secret, and compare it using hash_equals() rather than ==.
2. 2Reject anything with a timestamp older than a few minutes, and store message ids long enough to refuse replays.
3. 3Store the payload, answer 2xx immediately, and do the work afterwards through the same queue. A webhook that works inline will eventually time out, the sender will retry, and now you have duplicates.
4. 4Keep every raw body for a bounded retention period. When the vendor says “we definitely sent it”, that log ends the conversation.

OpenCart 4 ships its own cron table and controller, fine for light housekeeping. For an ERP worker we still prefer a system cron running a small CLI script: no HTTP timeout, its own memory limit, no dependence on somebody visiting the site.

# /etc/cron.d/opencart-erp
*  * * * *  store  /usr/bin/php8.1 /home/store/erp/worker.php    >> /home/store/erp/worker.log 2>&1
15 3 * * *  store  /usr/bin/php8.1 /home/store/erp/reconcile.php >> /home/store/erp/recon.log  2>&1

When to write to us

If your store already has an ERP connection that “mostly works”, the useful first step is small: we read the integration and tell you which of these patterns is missing and what each would take. We work at the launch rate of $10 per hour plus VAT, quote the hours before starting, and answer first messages within two hours between 09:00 and 22:00, Monday to Saturday.