A customer clicks Place Order once, the browser times out, and the customer clicks again. Now the database has two orders, or worse, one order and two payment attempts.
This is a common failure mode in an Ordering System when retries are treated as new requests. The problem gets harder when the order touches PostgreSQL, a payment provider, inventory services, ERP, and fulfillment systems.
The fix is not simply adding a unique constraint.
We need to make the complete order flow idempotent. That means the same request can be retried without creating another business transaction.
This article walks through that design using PostgreSQL and HTTP APIs. We will start with the naive implementation, identify where duplication occurs, and then build an Ordering System around idempotency keys, database constraints, transactions, and a transactional outbox.
Why an Ordering System Can Create Two Orders
The failure starts with a simple assumption: one HTTP request equals one order.
Consider this implementation:
-- Naive Ordering System insert: a network retry can create another row.
INSERT INTO orders (customer_id, total_amount, status)
VALUES (42, 129.00, 'created')
RETURNING id;
The SQL itself is valid.
The problem is everything around it.
Suppose the server commits the transaction, but the response never reaches the browser. The client sees a timeout and retries the request.
The database cannot know that the second request represents the same customer action.
The result can be:
Request #1
|
v
Create Order
|
v
Database COMMIT
|
X
Response lost
|
v
Client timeout
|
v
Request #2
|
v
Create Order again
The Ordering System has no way to distinguish a retry from a genuinely new order.
That distinction becomes critical when payment and inventory are involved.
PostgreSQL's documentation confirms that ON CONFLICT can provide an atomic insert-or-update outcome under concurrency. It is therefore useful for enforcing business uniqueness at the database layer rather than relying only on application checks.
Step 1: Give Every Ordering System Request an Idempotency Key
The first change is to make the business operation identifiable.
The client generates an idempotency key and sends it with the request:
POST /api/orders
Idempotency-Key: 8d5d3a6b-7b4f-4e1c-91f7-6d4b4c8e2f11
Content-Type: application/json
The key should represent the customer's attempt to create one order.
The Ordering System stores that key with the order:
-- The unique constraint makes duplicate business requests visible to PostgreSQL.
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
idempotency_key UUID NOT NULL UNIQUE,
customer_id BIGINT NOT NULL,
total_amount NUMERIC(12, 2) NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Now a second request carrying the same key cannot silently create another order.
Stripe uses the same general principle for API requests. Its documentation recommends idempotency keys for safely retrying operations after connection errors, with subsequent requests using the same key returning the original result.
The important detail is that the key belongs to the business operation, not the TCP connection.
Step 2: Let PostgreSQL Enforce the Uniqueness
The next problem is concurrency.
Two identical requests can arrive almost simultaneously:
Request A ───────┐
├──> PostgreSQL
Request B ───────┘
A naive application check looks like this:
-- This check is unsafe by itself because another request can insert after the SELECT.
SELECT id
FROM orders
WHERE idempotency_key = '8d5d3a6b-7b4f-4e1c-91f7-6d4b4c8e2f11';
Both requests could see no row before either inserts.
The database constraint should therefore remain the final guard:
-- PostgreSQL handles the concurrent conflict atomically.
INSERT INTO orders (
idempotency_key,
customer_id,
total_amount,
status
)
VALUES (
'8d5d3a6b-7b4f-4e1c-91f7-6d4b4c8e2f11',
42,
129.00,
'created'
)
ON CONFLICT (idempotency_key)
DO UPDATE SET idempotency_key = EXCLUDED.idempotency_key
RETURNING id, idempotency_key, status;
This is where the Ordering System moves from application-level hope to database-enforced behavior.
PostgreSQL documents ON CONFLICT DO UPDATE as an atomic insert-or-update operation. The documentation also notes that PostgreSQL's default READ COMMITTED behavior provides a defined outcome for conflicting inserts.
The exact conflict strategy can vary. The important part is that the uniqueness rule lives in the database.
For teams designing a broader multi-channel Ordering System architecture, this database-level guarantee becomes especially important when orders arrive from websites, mobile applications, marketplaces, or internal sales tools.
Step 3: Keep the Order and Event in One Transaction
Preventing duplicate rows solves only half the problem.
An Ordering System usually needs to publish an event after creating the order:
Order Created
|
+----> ERP
|
+----> Inventory
|
+----> Payment
|
+----> Fulfillment
A dangerous implementation is:
BEGIN
INSERT order
COMMIT
Publish OrderCreated
What happens if the database commits but the application crashes before publishing the event?
The order exists, but downstream systems never receive it.
The reverse is also dangerous. If the event is published first and the database transaction rolls back, another service can process an order that does not exist.
The fix is a transactional outbox:
-- The order and its event are committed together.
BEGIN;
INSERT INTO orders (
idempotency_key,
customer_id,
total_amount,
status
)
VALUES (
'8d5d3a6b-7b4f-4e1c-91f7-6d4b4c8e2f11',
42,
129.00,
'created'
)
ON CONFLICT (idempotency_key)
DO NOTHING;
INSERT INTO outbox_events (
event_type,
aggregate_type,
aggregate_id,
payload
)
VALUES (
'OrderCreated',
'order',
12345,
'{"orderId":12345}'
);
COMMIT;
The event publisher can then read committed outbox records and send them to the message broker.
AWS Prescriptive Guidance describes the transactional outbox pattern specifically for the dual-write problem. It recommends storing the database change and event in the same transaction, then processing the outbox separately. AWS also notes that consumers should be idempotent because duplicate event delivery can still occur.
That last point matters.
An outbox does not magically provide exactly-once processing.
It gives the Ordering System a safer path from database state to event publication.
Step 4: Make Downstream Consumers Idempotent
Once an order event leaves the Ordering System, another failure mode appears.
The message broker may deliver the same event twice.
The consumer therefore needs its own deduplication rule:
-- Store processed event IDs so a repeated delivery becomes harmless.
CREATE TABLE processed_events (
event_id UUID PRIMARY KEY,
processed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
The consumer can attempt to register the event before applying its business action:
-- A duplicate event hits the primary key instead of executing the business action twice.
INSERT INTO processed_events (event_id)
VALUES ('1f3d2c91-6e4c-4d75-8c9e-2b7d8c2a6e12')
ON CONFLICT (event_id) DO NOTHING;
If the insert succeeds, process the event.
If the insert conflicts, the event has already been handled.
This gives the Ordering System an important property: retries are expected rather than treated as exceptional behavior.
We Implemented This Around a Customer Ordering Flow
That consumer-side trade-off becomes important once an Ordering System connects the customer experience to operational systems.
We encountered the same architectural concern while working around the Lala's Kitchen ordering flow. The customer-facing journey was only the first part of the transaction. The backend still needed to turn that action into an operational order that could eventually interact with payment, availability, and fulfillment processes.
We therefore treated order creation as a state transition rather than a simple checkout insert.
The architecture separates order creation from downstream processing. The order becomes the durable source record, while subsequent actions can consume explicit order events.
Our broader work at Oodles follows the same architecture-first approach, where customer-facing ordering flows are connected to operational systems through clearly defined order states and integration boundaries.
For the implementation, the exact production metric was not provided in the project brief, so we will not invent one.
The technical lesson remains useful even without a fabricated benchmark: an Ordering System should assume that requests and events can be retried.
What This Changes in Production
Once these controls are in place, the Ordering System behaves differently during failures.
A client timeout does not automatically mean another order.
A database retry does not automatically create another business record.
A committed order does not depend on one successful message publish.
A duplicate event does not automatically trigger another inventory or fulfillment operation.
The architecture becomes:
Client
|
| Idempotency-Key
v
Ordering API
|
v
PostgreSQL
|
+---- Orders
|
+---- Outbox
|
v
Message Broker
|
+----+----+
| |
v v
Inventory ERP
|
v
Fulfillment
This is the difference between retrying an HTTP request and safely retrying a business operation.
Conclusion: Build the Ordering System Around Retries
A production Ordering System should assume that networks fail, clients retry, workers restart, and messages arrive more than once.
The key design decisions are:
- Give every order-creation operation an idempotency key.
- Enforce uniqueness with a database constraint.
- Use transactions for related database changes.
- Use a transactional outbox for reliable event publication.
- Make downstream consumers idempotent.
- Track explicit order and event states so failures can be recovered.
The result is not an Ordering System that never fails. It is one where common failures do not automatically become duplicate business transactions.
FAQ
Why does an Ordering System need idempotency?
An Ordering System needs idempotency because clients can retry requests after timeouts or connection failures. Without an idempotency mechanism, one customer action can create multiple orders.
Is a unique database constraint enough?
No. A unique constraint can prevent duplicate order records, but it does not solve payment retries, duplicate events, or downstream processing. Those operations need their own idempotency controls.
Does a transactional outbox guarantee exactly-once delivery?
No. The outbox protects the database-to-event transition, but downstream consumers can still receive duplicate messages. Consumers should therefore handle duplicate events safely.
Should every service own its own idempotency key?
Not necessarily. The key should represent the business operation being protected. Different downstream operations may require separate deduplication identifiers.
When should an Ordering System use PostgreSQL transactions?
Use transactions when multiple related database changes must either commit together or roll back together. This is especially important when creating an order and its corresponding outbox event.
If you are dealing with duplicate orders, retry-related failures, or inconsistent downstream order states, our Ordering System architecture work provides a useful starting point for reviewing the transaction flow.
What retry or idempotency failure has caused the most trouble in your ordering stack? Share the pattern you used to solve it.
Top comments (0)