Database Schema Design for Zero-Downtime Payment Event Logging and Idempotency

In e-commerce and fintech backend systems, there is no error more expensive than double fulfillment—shipping two physical laptops to a customer who paid for one, or crediting an in-app wallet balance twice because an acquiring bank delivered duplicate webhooks.

Building a payment database that maintains mathematical consistency under extreme concurrency requires rigorous schema design. Here is the production-tested PostgreSQL architecture used by high-throughput payment gateways.

---

The Cardinal Sin of Payment Engineering: Double Fulfillment

Direct Answer: Payment databases must treat webhooks as fundamentally untrusted, duplicate-prone events. Network retries, consumer refresh clicks, and bank switch recon cycles mean your API will inevitably receive identical payment confirmation callbacks simultaneously. Without database-enforced unique constraints and atomic state transitions, race conditions will fulfill orders twice.

The solution relies on two foundational principles:

1. Append-Only Event Ledger: Never mutate historical payment records; append new status events chronologically.
2. Idempotency Keys: Unique compound indexes on (merchant_id, client_txn_id) and (bank_utr) enforced at the database engine level.

---

Production PostgreSQL DDL Schema

Here is the robust, production-grade schema:

-- 1. Custom Enum Types for Strict State Machine Enforcement
CREATE TYPE order_status AS ENUM (
    'PENDING',
    'PROCESSING',
    'COMPLETED',
    'FAILED',
    'EXPIRED',
    'REFUNDED'
);

CREATE TYPE payment_event_type AS ENUM (
    'PAYMENT_ATTEMPTED',
    'WEBHOOK_RECEIVED',
    'STATUS_VERIFIED',
    'REFUND_INITIATED',
    'REFUND_SETTLED'
);

-- 2. Core Orders Table
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    merchant_id VARCHAR(64) NOT NULL,
    client_txn_id VARCHAR(64) NOT NULL,
    amount NUMERIC(12, 2) NOT NULL CHECK (amount > 0),
    currency CHAR(3) DEFAULT 'INR',
    status order_status NOT NULL DEFAULT 'PENDING',
    customer_mobile VARCHAR(15),
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_merchant_order UNIQUE (merchant_id, client_txn_id)
);

-- 3. Append-Only Payment Transactions & Webhook Ledger
CREATE TABLE payment_transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
    bank_utr VARCHAR(64) UNIQUE, -- NPCI 12-digit UTR (Unique across entire database)
    gateway_order_id VARCHAR(64) NOT NULL,
    amount_paid NUMERIC(12, 2) NOT NULL,
    payer_vpa VARCHAR(128),
    event_type payment_event_type NOT NULL,
    raw_payload JSONB NOT NULL,
    idempotency_hash VARCHAR(64) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Fast Lookups for Webhook Handlers
CREATE INDEX idx_orders_client_txn ON orders(merchant_id, client_txn_id);
CREATE INDEX idx_transactions_order_id ON payment_transactions(order_id);
CREATE INDEX idx_transactions_bank_utr ON payment_transactions(bank_utr);

---

The Strict State Machine Pattern

An order can only transition forward along defined regulatory paths:

[ PENDING ] ──► [ COMPLETED ] ──► [ REFUNDED ]
     │                ▲
     ▼                │ (Delayed Webhook)
 [ EXPIRED ] ─────────┘

If an order is already marked COMPLETED, an incoming webhook must never re-trigger order fulfillment:

-- Atomic State Transition
UPDATE orders
SET status = 'COMPLETED', updated_at = NOW()
WHERE id = :order_id 
  AND status IN ('PENDING', 'EXPIRED') -- Guards against double fulfillment
RETURNING id;

If the query returns zero rows, another concurrent process has already completed the order, and the application immediately stops further fulfillment tasks.

---

Implementing Atomic Idempotency in Code

In your backend application logic (Node.js or Python):

async function processPaymentWebhook(client, eventData) {
  // 1. Compute Idempotency Hash of the incoming payload
  const idempotencyHash = crypto
    .createHash('sha256')
    .update(`${eventData.client_txn_id}_${eventData.utr}_${eventData.amount}`)
    .digest('hex');

  // 2. Start PostgreSQL Transaction
  await client.query('BEGIN');
  try {
    // 3. Attempt inserting into payment ledger with ON CONFLICT DO NOTHING
    const insertResult = await client.query(
      `INSERT INTO payment_transactions 
         (order_id, bank_utr, gateway_order_id, amount_paid, event_type, raw_payload, idempotency_hash)
       VALUES ($1, $2, $3, $4, 'WEBHOOK_RECEIVED', $5, $6)
       ON CONFLICT (idempotency_hash) DO NOTHING
       RETURNING id;`,
      [eventData.orderId, eventData.utr, eventData.gatewayOrderId, eventData.amount, JSON.stringify(eventData), idempotencyHash]
    );

    // If duplicate event, exit immediately and return 200 OK
    if (insertResult.rows.length === 0) {
      await client.query('COMMIT');
      return { status: 'already_processed' };
    }

    // 4. Update parent order status atomically
    await client.query(
      `UPDATE orders SET status = 'COMPLETED', updated_at = NOW() 
       WHERE id = $1 AND status != 'COMPLETED';`,
      [eventData.orderId]
    );

    await client.query('COMMIT');
    // 5. Trigger external side-effects (Email, SMS, Warehouse shipment)
    await dispatchOrderFulfillment(eventData.orderId);

  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  }
}

---

Partitioning and Archiving for Multi-Million Order Scale

For businesses processing tens of thousands of daily payments:

• Table Partitioning by Range (created_at): Partition the payment_transactions ledger into monthly tables (transactions_2026_10).
• Cold Storage Archival: After 180 days, move completed raw JSON payloads into compressed Parquet files on S3/Cloud Storage, keeping database indexes lean and queries fast.

Read our developer guide on Handling Webhook Delivery Failures to complete your resilient backend setup.