Imagine you’re building an order API.
A customer clicks Pay. Your server creates an order in PostgreSQL, but the HTTP response gets lost because of a network timeout.
The client retries.
Your server creates another order.
You now have two orders for one customer action.
The problem is that the client doesn’t know whether the original request failed or succeeded without delivering a response.
This is where idempotency comes in.Phase 1: Implement core idempotency with PostgreSQL
Phase 1: Implement core idempotency with PostgreSQL
Goal: Prevent duplicate orders when clients retry requests.
Let’s start with the simplest version of the problem.
A client sends:
POST /v1/orders
Idempotency-Key: order_abc123
Content-Type: application/json
{
"amountCents": 100000,
"currency": "INR"
}The server should create the order once and return the same result when the client retries with the same key and payload.
Step 1: Store the idempotency record
We need to associate each key with the request and its eventual response.
Add these models to your Prisma schema:
enum IdempotencyStatus {
IN_PROGRESS
COMPLETED
}
model IdempotencyRecord {
id String @id @default(cuid())
scope String
key String
requestHash String
status IdempotencyStatus @default(IN_PROGRESS)
responseStatus Int?
responseBody String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
@@unique([scope, key], name: "scope_key")
}
model Order {
id String @id @default(cuid())
userId String
amountCents Int
currency String
createdAt DateTime @default(now())
}The most important part is:
@@unique([scope, key], name: "scope_key")
This tells PostgreSQL that a given idempotency key can only be claimed once within its scope.
The scope might represent a particular user and endpoint:
const scope = `${userId}:POST:/v1/orders`;This prevents different users from colliding just because they happened to generate the same key.
Step 2: Detect conflicting requests
Suppose the original request contains:
{
"amountCents": 100000,
"currency": "INR"
}But the client reuses the key with:
{
"amountCents": 500000,
"currency": "INR"
}These are not retries of the same logical operation.
We need a request fingerprint so that we can detect the difference.
Install a stable JSON serializer:
npm install express @prisma/client fast-json-stable-stringify
Then calculate a hash:
import { createHash } from "node:crypto";
import stableStringify from "fast-json-stable-stringify";
function hashRequest(input) {
return createHash("sha256")
.update(stableStringify(input))
.digest("hex");
}The input should be validated and normalized before hashing. Include every field that changes the meaning of the operation.
If the same key is reused with a different hash, respond with 409 Conflict.
Step 3: Implement the endpoint
Assume authentication middleware sets req.user.id, and request validation has been performed as shown below.
function sendExistingResponse(record, requestHash, res) {
if (record.requestHash !== requestHash) {
res.status(409).json({
error: "idempotency_key_reused",
message: "This key was used for a different request.",
});
return;
}
if (
record.status !== "COMPLETED" ||
record.responseStatus == null ||
record.responseBody == null
) {
res.status(409).set("Retry-After", "1").json({
error: "request_in_progress",
});
return;
}
res
.status(record.responseStatus)
.type("application/json")
.send(record.responseBody);
}
app.post("/v1/orders", async (req, res, next) => {
const key = req.get("Idempotency-Key")?.trim();
if (!key || key.length > 200) {
return res.status(400).json({
error: "invalid_idempotency_key",
});
}
const userId = req.user.id;
const { amountCents, currency } = req.body;
if (
!Number.isSafeInteger(amountCents) ||
amountCents <= 0 ||
typeof currency !== "string" ||
currency.toUpperCase() !== "INR"
) {
return res.status(400).json({
error: "invalid_request",
});
}
const input = {
amountCents,
currency: currency.toUpperCase(),
};
const scope = `${userId}:POST:/v1/orders`;
const requestHash = createHash("sha256")
.update(stableStringify(input))
.digest("hex");
const where = {
scope_key: { scope, key },
};
try {
// Fast path: replay an existing result.
const existing = await prisma.idempotencyRecord.findUnique({
where,
});
if (existing) {
sendExistingResponse(existing, requestHash, res);
return;
}
// Claim the key, create the order and persist its response
// in one atomic PostgreSQL transaction.
const result = await prisma.$transaction(async (tx) => {
await tx.idempotencyRecord.create({
data: {
scope,
key,
requestHash,
status: "IN_PROGRESS",
},
});
const order = await tx.order.create({
data: {
userId,
amountCents: input.amountCents,
currency: input.currency,
},
});
const responseBody = JSON.stringify({
id: order.id,
amountCents: order.amountCents,
currency: order.currency,
status: "created",
});
await tx.idempotencyRecord.update({
where,
data: {
status: "COMPLETED",
responseStatus: 201,
responseBody,
},
});
return {
status: 201,
body: responseBody,
};
});
return res
.status(result.status)
.type("application/json")
.send(result.body);
} catch (error) {
if (error?.code === "P2002") {
// A concurrent request may have claimed this key first.
// Resolve it by loading the winning record.
try {
const existing = await prisma.idempotencyRecord.findUnique({
where,
});
if (existing) {
sendExistingResponse(existing, requestHash, res);
return;
}
} catch (lookupError) {
return next(lookupError);
}
}
return next(error);
}
});Why does this work?
Three things are important.
First, the database enforces uniqueness. Checking whether a key exists before inserting it is not enough. Two concurrent requests could both pass that check. The unique constraint closes this race.
Second, we use one transaction. The idempotency record, order, and completed response are committed together. If the transaction fails, they all roll back.
Third, we persist the original response. If the order is committed but the HTTP response is lost, the next request retrieves the stored response instead of creating another order.
In this design, the intermediate IN_PROGRESS state exists only inside the transaction. Other transactions generally won't see it before the completed record commits. This is intentional for a short-running database-only operation.
Test the edge cases
New key -> Create an order
Same key and payload -> Return the stored result
Same key, different payload -> 409 Conflict
Two simultaneous requests with the same key -> One order
Server crashes before transaction commit -> Transaction rolls back
Server crashes after commit but before HTTP response -> Retry replays the result
This is a good starting point for many APIs.
At this stage, you do not need Redis or Kafka.
However, this design assumes the work can be completed within a short database transaction. It becomes more complicated when an operation involves external services or long-running work.
Phase 2: Add Redis only when there is a reason
Goal: Reduce duplicate work or improve coordination under contention.
PostgreSQL already gives us durable idempotency. So why add Redis?
Redis can provide fast coordination or caching. For example, it can prevent multiple application instances from eagerly starting the same expensive workflow.
A simple lock can be acquired with SET NX:
import { randomUUID, createHash } from "node:crypto";
const RELEASE_LOCK = `
if redis.call("GET", KEYS[1]) == ARGV[1] then
return redis.call("DEL", KEYS[1])
end
return 0
`;
async function acquireLock(scope, key) {
const lockId = createHash("sha256")
.update(`${scope}:${key}`)
.digest("hex");
const lockKey = `idem-lock:${lockId}`;
const token = randomUUID();
const acquired = await redis.set(lockKey, token, {
NX: true,
PX: 30_000,
});
if (acquired !== "OK") {
return null;
}
return { lockKey, token };
}
async function releaseLock(lock) {
if (!lock) return;
await redis.eval(RELEASE_LOCK, {
keys: [lock.lockKey],
arguments: [lock.token],
});
}Here, redis is an initialized node-redis client.
The random token ensures that a worker releases only the lock it acquired. Otherwise, an expired lock could be acquired by another worker, and the original worker could accidentally delete the new worker’s lock.
There is an important limitation: the lock expires after 30 seconds. The original operation could still be running when that happens.
Therefore:
- Keep PostgreSQL’s unique constraint.
- Keep the database transaction as the correctness boundary.
- Treat Redis as a coordination optimization.
- Have a clear policy when a lock cannot be acquired.
For most early-stage applications, skip Redis until measurement shows that it solves a real problem.
Phase 3: Introduce Kafka when asynchronous processing is needed
Goal: Let other services react to an order without making the HTTP request wait for all of them.
Suppose an order now needs to trigger:
- Fulfillment.
- Email notifications.
- Analytics.
- Inventory updates.
One option is to call every service directly after creating the order. But that makes the request slower and couples order creation to downstream availability.
Kafka lets the order service publish an event such as:
{
"eventId": "evt_123",
"type": "order.created",
"payload": {
"orderId": "ord_123",
"userId": "usr_456"
}
}Other services can consume the event independently.
A KafkaJS producer can look like this:
import { Kafka } from "kafkajs";
const kafka = new Kafka({
clientId: "order-service",
brokers: process.env.KAFKA_BROKERS.split(","),
});
const producer = kafka.producer({
idempotent: true,
maxInFlightRequests: 1,
});
await producer.connect();But we now have a new problem
What if the order is saved in PostgreSQL, but Kafka is unavailable?
Or what if Kafka receives the event, but the database transaction later rolls back?
PostgreSQL and Kafka are two independent systems. A normal PostgreSQL transaction cannot atomically commit writes to both.
This is called the dual-write problem.
Adding Kafka gives us asynchronous communication, but it also creates a reliability requirement: when an order commits, we must reliably remember to publish its event.
That’s the motivation for the next phase.
Phase 4: Add the transactional outbox
Goal: Ensure a committed database change and the intention to publish its event are recorded together.
The outbox pattern is simpler than it sounds.
Instead of trying to save the order and publish to Kafka as unrelated steps, insert an event record into PostgreSQL during the same transaction as the order.
Add this model:
model OutboxEvent {
id String @id @default(cuid())
type String
aggregateId String
payload Json
attempts Int @default(0)
claimedAt DateTime?
claimToken String?
publishedAt DateTime?
createdAt DateTime @default(now())
@@index([publishedAt, claimedAt, createdAt])
}Extend the transaction from Phase 1:
const result = await prisma.$transaction(async (tx) => {
await tx.idempotencyRecord.create({
data: {
scope,
key,
requestHash,
status: "IN_PROGRESS",
},
});
const order = await tx.order.create({
data: {
userId,
amountCents: input.amountCents,
currency: input.currency,
},
});
await tx.outboxEvent.create({
data: {
type: "order.created",
aggregateId: order.id,
payload: {
orderId: order.id,
userId,
amountCents: order.amountCents,
currency: order.currency,
},
},
});
const responseBody = JSON.stringify({
id: order.id,
amountCents: order.amountCents,
currency: order.currency,
status: "created",
});
await tx.idempotencyRecord.update({
where: {
scope_key: { scope, key },
},
data: {
status: "COMPLETED",
responseStatus: 201,
responseBody,
},
});
return { status: 201, body: responseBody };
});We now commit three things atomically:
- The order.
- The completed idempotency response.
- The outbox event.
If the transaction fails, all three roll back.
If it succeeds, the outbox event remains in PostgreSQL even if Kafka is unavailable.
The API no longer needs to wait for Kafka. A separate worker can publish the event later.
Build the outbox dispatcher
A background worker claims unpublished events, sends them to Kafka, and marks them as published.
Multiple workers must not freely claim the same rows at the same time. PostgreSQL’s FOR UPDATE SKIP LOCKED can help coordinate claims.
For example, a dispatcher can claim a batch in a short transaction:
import { randomUUID } from "node:crypto";
async function claimOutboxBatch(limit = 100) {
const token = randomUUID();
const events = await prisma.$transaction((tx) =>
tx.$queryRaw`
WITH candidates AS (
SELECT "id"
FROM "OutboxEvent"
WHERE "publishedAt" IS NULL
AND (
"claimedAt" IS NULL
OR "claimedAt" < NOW() - INTERVAL '1 minute'
)
ORDER BY "createdAt"
LIMIT ${limit}
FOR UPDATE SKIP LOCKED
)
UPDATE "OutboxEvent" AS e
SET
"claimedAt" = NOW(),
"claimToken" = ${token},
"attempts" = e."attempts" + 1
FROM candidates
WHERE e."id" = candidates."id"
RETURNING
e."id",
e."type",
e."aggregateId",
e."payload"
`
);
return { token, events };
}After claiming the batch, publish each event:
async function dispatchOutboxBatch() {
const { token, events } = await claimOutboxBatch();
for (const event of events) {
try {
await producer.send({
topic: "orders",
acks: -1,
messages: [{
key: event.aggregateId,
value: JSON.stringify({
eventId: event.id,
type: event.type,
payload: event.payload,
}),
}],
});
await prisma.outboxEvent.updateMany({
where: {
id: event.id,
claimToken: token,
},
data: {
publishedAt: new Date(),
claimedAt: null,
claimToken: null,
},
});
} catch (error) {
// Keep the event unpublished so a later attempt can retry.
console.error("Outbox publication failed", {
eventId: event.id,
error,
});
}
}
}The one-minute lease is illustrative. Production implementations need bounded retries, backoff, monitoring, and lease management appropriate to the workload.
One more edge case
Imagine:
- The worker publishes the event successfully.
- Kafka acknowledges it.
- The worker crashes before setting publishedAt.
The worker eventually retries the event, so Kafka may receive it twice.
This is expected.
The outbox prevents the intent to publish from being lost when the database transaction commits. It does not guarantee that each event is published only once.
For that, we need one more safeguard.
Phase 5: Make Kafka consumers idempotent
Goal: Prevent repeated event delivery from repeating business effects.
Suppose the fulfillment service receives order.created and creates a fulfillment job.
If Kafka delivers the event twice, the service must not create two jobs.
Add two models:
model ProcessedEvent {
eventId String @id
processedAt DateTime @default(now())
}
model FulfillmentJob {
id String @id @default(cuid())
orderId String @unique
createdAt DateTime @default(now())
}Now process the event in a transaction:
async function handleOrderCreated(event) {
try {
await prisma.$transaction(async (tx) => {
// Claim this event exactly once in our database.
await tx.processedEvent.create({
data: {
eventId: event.eventId,
},
});
// Apply the corresponding business effect.
await tx.fulfillmentJob.create({
data: {
orderId: event.payload.orderId,
},
});
});
} catch (error) {
if (error?.code === "P2002") {
const previous = await prisma.processedEvent.findUnique({
where: {
eventId: event.eventId,
},
});
if (previous) {
return; // Already applied successfully.
}
}
throw error;
}
}The event marker and fulfillment job commit together.
If fulfillment creation fails, the processed-event marker rolls back too. If the handler succeeds but the consumer crashes before its Kafka offset is committed, Kafka may redeliver the event. The processed-event table prevents that delivery from repeating the database effect.
This example assumes duplicate deliveries retain the same stable eventId and that the business effect occurs in the same database. External side effects, such as sending an email or calling another service, need their own reliability strategy.
Phase 6: Know when to stop adding complexity
Basic API with ordinary CRUD -> PostgreSQL and Prisma
Safe retries for order creation -> PostgreSQL idempotency records and a transaction
Faster coordination under high contention -> Consider Redis
Multiple services need asynchronous events -> Consider Kafka
Events must reliably follow committed database changes -> Add an outbox
Duplicate event delivery must not repeat business effects -> Idempotent consumers
One special case deserves extra care: payments.
If an external payment provider successfully charges a customer but your service times out before receiving the response, a local PostgreSQL transaction cannot undo that charge. Use provider-side idempotency keys where supported, persist operation state, and reconcile uncertain outcomes instead of blindly retrying.
Final thoughts
Idempotency isn’t about adding as many reliability tools as possible.
It’s about identifying failure modes and choosing the simplest architecture that handles them.
PostgreSQL protects the core database operation. Redis can improve coordination. Kafka enables asynchronous communication. The outbox records the intention to publish an event alongside the business change. Idempotent consumers protect downstream business effects.
Each layer should exist because there’s a requirement it satisfies.
Start simple. Introduce complexity when the system gives you a reason to.
Good system design isn’t about using every tool. It’s about understanding which guarantee you need, where that guarantee comes from, and what can still fail.