Why I didn't reach for Redis: payment idempotency with PostgreSQL row locks

When I was building the payment backend for Classly, I hit a problem I’d read about but never actually dealt with: duplicate charges.

A user clicks “Enroll.” The request goes out. The network hiccups. The client retries. Both requests reach the server at the same time — one from the original attempt, one from the retry. If I’m not careful, both create a payment order. The user gets charged twice and I get a support ticket I can’t ignore.

The obvious solution everyone reaches for is Redis. Grab a distributed lock, set a TTL, release it when done. Simple. But Classly already has PostgreSQL as its primary store. Adding Redis just for locking means another process to run, another failure mode, and another thing that can go out of sync with the database. I decided to see how far I could get with just Postgres.

The problem in concrete terms

Classly integrates with two payment gateways — Razorpay and a backup. Each payment flow looks roughly like this:

1. Client sends POST /payments/initiate with a course ID and user ID
2. Server creates a payment record, calls the gateway, gets back an order_id
3. Client completes payment on the gateway’s hosted page
4. Gateway fires a webhook to POST /payments/webhook
5. Server verifies the webhook signature, marks the enrollment as active

Steps 2 and 4 are the dangerous ones. If step 2 runs twice for the same user+course, you get two orders on the gateway side. If step 4 runs twice (webhooks will retry), you might double-enroll or corrupt state.

Idempotency keys at the database level

The first thing I did was add an idempotency_key column to the payment_orders table with a UNIQUE constraint:

CREATE TABLE payment_orders (
  id              BIGSERIAL PRIMARY KEY,
  user_id         BIGINT NOT NULL REFERENCES users(id),
  course_id       BIGINT NOT NULL REFERENCES courses(id),
  idempotency_key TEXT NOT NULL,
  gateway         TEXT NOT NULL,
  status          TEXT NOT NULL DEFAULT 'pending',
  gateway_order   JSONB,
  created_at      TIMESTAMPTZ DEFAULT NOW(),

  CONSTRAINT payment_orders_idempotency_key_unique UNIQUE (idempotency_key)
);

The idempotency key is generated on the client side (or derived deterministically from user_id + course_id + timestamp-bucket) and sent with every request.

If two identical requests arrive simultaneously, one of them hits the INSERT first. The other gets a 23505 unique-violation error. That’s a retry-safe failure — I catch it, look up the existing row, and return its current state to the client. No duplicate order created.

But there’s a race condition here. Between the INSERT and the gateway call, another request with the same idempotency key might slip through (if I’m checking existence before inserting rather than relying on the constraint). The fix: always try the INSERT first, catch the constraint violation, then read. Never check-then-insert.

SELECT FOR UPDATE on the webhook path

Webhooks are where it gets messier. Razorpay retries webhooks for up to 24 hours if it doesn’t receive a 200 response. If my server is slow — or if I accidentally return a non-200 — I’ll receive the same webhook multiple times.

Marking an enrollment as active is not idempotent by default. Running it twice might double-credit something, send duplicate emails, or cause a constraint violation downstream. I needed to make the entire webhook handler idempotent.

Here’s the pattern I use:

async function handleWebhook(payload, signature) {
  await verifySignature(payload, signature) // throws if invalid

  const { order_id, payment_id, status } = parsePayload(payload)

  await db.transaction(async (trx) => {
    // Lock the row for this order — any concurrent webhook for the same
    // order_id will block here until this transaction commits or rolls back.
    const order = await trx.raw(`
      SELECT * FROM payment_orders
      WHERE gateway_order->>'id' = ?
      FOR UPDATE
    `, [order_id])

    if (!order.rows.length) return // unknown order, ignore

    const row = order.rows[0]

    // Already processed — return early without doing anything.
    if (row.status === 'completed') return

    // Mark complete and activate enrollment atomically.
    await trx('payment_orders')
      .where({ id: row.id })
      .update({ status: 'completed', gateway_payment_id: payment_id })

    await trx('enrollments').insert({
      user_id: row.user_id,
      course_id: row.course_id,
      activated_at: new Date(),
    }).onConflict(['user_id', 'course_id']).ignore()
  })
}

The SELECT ... FOR UPDATE is the key line. PostgreSQL acquires a row-level lock on that payment order for the duration of the transaction. If two webhook deliveries arrive simultaneously for the same order_id, one of them gets the lock, processes, and commits. The other waits, then sees status === 'completed', and returns early. No double-enrollment.

The .onConflict().ignore() on the enrollments insert is a second safety net — if the row already exists, we skip silently.

SKIP LOCKED for background jobs

There’s a third case: deferred enrollment checks. Some courses have a delayed activation (e.g., cohort-based start dates). I have a background job that polls for status = 'completed' enrollments that haven’t been activated yet.

If I run multiple workers, they’ll all try to grab the same rows. I don’t want two workers activating the same enrollment. SKIP LOCKED solves this cleanly:

SELECT id, user_id, course_id
FROM enrollments
WHERE activated_at IS NULL
  AND scheduled_activation <= NOW()
FOR UPDATE SKIP LOCKED
LIMIT 10;

Each worker grabs up to 10 rows that aren’t already locked by another worker. Rows being processed by worker A are skipped by worker B entirely — no waiting, no deadlocks. When worker A commits, those rows either get marked done or get picked up by the next poll cycle.

This pattern gives you a job queue inside Postgres with zero extra infrastructure. It’s not as featureful as BullMQ or a proper queue, but for a use case this contained, it’s hard to argue for the complexity.

What I’d do differently

The idempotency key derivation is the thing I’d revisit. I’m currently generating it on the server from user_id + course_id + floor(timestamp / 5min) — a time-bucketing approach. This means retries within a 5-minute window are safe, but a retry across the boundary might create a new order. A true client-generated key (UUID the client mints once and resends forever) would be cleaner. I’ll change this in the next refactor.

The advisory lock is another option I considered — pg_try_advisory_xact_lock(user_id) — which would prevent a single user from initiating two payments simultaneously without needing to have a row in the table yet. Worth exploring if the initiation race becomes a real problem at higher concurrency.

For now: no Redis, no external lock service. Just Postgres, used carefully.