Idempotency keys for the ERP order feed

What actually happened

A customer service agent flagged it: one customer, six identical orders, ninety seconds apart, all paid once. Support had already refunded five before anyone looked at the feed.

The ERP pushes orders to us over a REST endpoint. Its HTTP client had a 30 second read timeout and a three attempt retry policy with fixed backoff. Our consumer took roughly 34 seconds to build the order under load, because the price rules re-index synchronously on save.

So the ERP timed out, retried, and we built a second order. The response it eventually got for attempt one, a 201 with an increment ID, arrived after it had already given up reading. Nothing in either system was broken. Both were doing exactly what they were configured to do.

Three fixes that don't work

Raising the timeout. It moves the cliff. The next slow index, the next Black Friday queue depth, and you are back here with a longer number in a config file.

Hashing the whole payload. Tempting, and wrong in both directions. The ERP stamps a fresh exported_at on every attempt, so identical orders hash differently, and two genuinely separate orders for the same customer, same lines, same day, hash identically.

A unique index on reserved_order_id. The quote reserves that ID at a different point in the flow, and any order created outside the quote path, which is most of what an ERP feed does, never populates it usefully.

Choosing the key

An idempotency key has to be produced by the sender, be stable across retries and be unique per business event. The last two are in tension only if you let the sender include anything mutable.

// Sender-supplied where available; derived where it isn't.
$key = $request->getHeader('Idempotency-Key')
    ?: hash('sha256', implode(':', [
        $payload['erp_order_id'],
        $payload['customer_email'],
        $this->lineHash($payload['items']),   // sku:qty:price, sorted
    ]));

Note what is not in there: timestamps, attempt counters, request IDs, anything the sender regenerates. The line hash sorts before hashing, because the ERP does not guarantee item order between attempts. That one detail cost half a day.

Storing it

The key lives in its own table with a unique index, not as an attribute on the order. You need a row the moment the request arrives, before an order exists, so the second attempt has something to collide with.

<table name="de_erp_order_idempotency" resource="default" engine="innodb">
    <column xsi:type="varchar" name="idempotency_key" length="64" nullable="false"/>
    <column xsi:type="int" name="order_id" unsigned="true" nullable="true"/>
    <column xsi:type="timestamp" name="created_at" default="CURRENT_TIMESTAMP"/>
    <constraint xsi:type="unique" referenceId="DE_ERP_ORDER_IDEMPOTENCY_KEY">
        <column name="idempotency_key"/>
    </constraint>
</table>

MySQL caps identifier names at 64 characters, so a long table name plus a long constraint name fails at setup:upgrade, not in review. Prefix the constraint, keep it short and keep db_schema_whitelist.json in step.

The insert is the lock. Attempt two hits the unique constraint, catches it and returns the order the first attempt created: a 200 carrying the original increment ID, not a 409. The ERP is not interested in our opinion about duplicates. It wants the order reference it failed to read the first time.

Where the guard belongs

At the edge, not in the order placement service. The controller does three things and nothing else: validate, claim the key, enqueue. It answers in about twenty milliseconds, which removes the timeout that started all of this.

public function execute(): ResultInterface
{
    $key = $this->keyFor($this->request);

    if ($existing = $this->claims->find($key)) {
        return $this->json(['order' => $existing->getIncrementId()]); // replay
    }

    $this->claims->claim($key);          // unique insert; throws on collision
    $this->publisher->publish('erp.order.import', $this->request->getContent());

    return $this->json(['status' => 'accepted', 'key' => $key], 202);
}

Processing moves to a queue consumer. If the import fails, the claim row is released and the message goes to a dead-letter queue with the original payload attached, so a replay is a support action rather than an engineering one.

An integration you cannot safely replay is an integration you will eventually be replaying by hand, at eight on a Friday, from a Slack thread.

Checklist

• Does the sender emit a stable key, and have you read their retry policy, attempts, backoff and timeout, with your own eyes?
• If you derive the key, does it exclude every value the sender regenerates?
• Is the uniqueness enforced by the database, not by a SELECT before an INSERT?
• Does a replay return the original reference, rather than an error the sender will retry?
• Does the endpoint acknowledge fast enough that the sender never times out under peak load?
• Is there a dead-letter queue, and does someone who is not you know how to drain it?