BriefTechNews
API Idempotency, Webhooks, and Ledger Design: How to Build Financial Software That Never Double-Charges

API Idempotency, Webhooks, and Ledger Design: How to Build Financial Software That Never Double-Charges

In consumer web applications, an intermittent network timeout or an unintended user double-click means a minor UI glitch. In financial systems, that same network timeout can result in double-charging a customer’s corporate credit card, disbursing duplicate vendor payouts, or drifting your internal account balances out of reconciliation.

If your database model represents user balances as a mutable balance column on an accounts table:

-- The single most dangerous query in financial software
UPDATE accounts SET balance = balance - 100 WHERE id = 'usr_123';

…you are one concurrent request away from catastrophic accounting drift.

Building financial systems that guarantee consistency across unreliable networks requires three fundamental architectural primitives:

  1. API Idempotency Keys: Guaranteeing that duplicate HTTP requests execute exactly once.
  2. Immutable Double-Entry Ledgers: Ensuring every movement of funds is an immutable transaction where debits and credits strictly balance to zero.
  3. Hardened Webhook Ingestion: Ingesting asynchronous payment state changes reliably with HMAC signature validation and replay defense.

Here is the exact implementation playbook for engineering a zero-drift payment engine.


1. API Idempotency: The Idempotency-Key Protocol

Distributed networks are inherently unreliable. When a client sends a POST /v1/charges request, three failure states can occur:

  • The request drops before reaching your server (unprocessed).
  • The server crashes mid-execution (partially processed).
  • The server processes the charge, but the response network packet drops before reaching the client (processed, but the client assumes a timeout).

If the client retries naively without idempotency guarantees, the customer gets billed twice.

The Idempotency Workflow

[Client Application]
        │
        │ 1. POST /v1/charges (Header: Idempotency-Key: f9a2-48c1-...)
        ▼
[API Gateway / Idempotency Middleware]
        │
        ├──> Check Redis / Key Store
        │
        ├─ [Key Exists & Status == 'COMPLETED']
        │    └─> Return cached response payload immediately (HTTP 200)
        │
        ├─ [Key Exists & Status == 'PROCESSING']
        │    └─> Return HTTP 409 Conflict ("Concurrent request in-flight")
        │
        └─ [Key Missing]
             │
             ├──> Insert Idempotency Record (Status: 'PROCESSING', Lock TTL: 60s)
             ├──> Execute Core Payment Business Logic within DB Transaction
             ├──> Update Idempotency Record (Status: 'COMPLETED', Response Body, HTTP Status)
             │
             └─> Return Response to Client

PostgreSQL Idempotency Schema

Do not store idempotency records purely in in-memory Redis instances without persistence. If Redis evicts keys under memory pressure, duplicate requests can slip through. A persistent PostgreSQL table with an in-memory Redis lock provides optimal durability.

CREATE TABLE idempotency_keys (
    key VARCHAR(255) PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    request_path VARCHAR(255) NOT NULL,
    request_params_hash CHAR(64) NOT NULL, -- SHA-256 hash of payload
    response_code INT,
    response_body JSONB,
    status VARCHAR(32) NOT NULL DEFAULT 'PROCESSING', -- 'PROCESSING', 'COMPLETED', 'FAILED'
    locked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_idempotency_lookup ON idempotency_keys(key, user_id);

Robust Implementation (Node.js / Express Middleware)

import { Request, Response, NextFunction } from 'express';
import crypto from 'crypto';
import { db } from './database';

export async function idempotencyMiddleware(req: Request, res: Response, next: NextFunction) {
  const idempotencyKey = req.header('Idempotency-Key');
  if (!idempotencyKey) {
    return next(); // Non-mutating or non-idempotent endpoints can skip
  }

  const userId = req.user.id;
  const path = req.path;
  const payloadHash = crypto.createHash('sha256').update(JSON.stringify(req.body)).digest('hex');

  // Atomic insert to acquire lock
  const insertQuery = `
    INSERT INTO idempotency_keys (key, user_id, request_path, request_params_hash, status)
    VALUES ($1, $2, $3, $4, 'PROCESSING')
    ON CONFLICT (key) DO NOTHING
    RETURNING status, response_code, response_body, request_params_hash;
  `;

  const result = await db.query(insertQuery, [idempotencyKey, userId, path, payloadHash]);

  if (result.rowCount === 0) {
    // Key already exists; fetch existing record
    const existing = await db.query(
      'SELECT status, response_code, response_body, request_params_hash FROM idempotency_keys WHERE key = $1',
      [idempotencyKey]
    );

    const record = existing.rows[0];

    // Payload verification: prevent key reuse with different parameters
    if (record.request_params_hash !== payloadHash) {
      return res.status(422).json({
        error: 'Idempotency key reused with mismatched request parameters'
      });
    }

    if (record.status === 'PROCESSING') {
      return res.status(409).json({
        error: 'A request with this idempotency key is currently processing. Retry shortly.'
      });
    }

    // Return the previously generated response verbatim
    return res.status(record.response_code).json(record.response_body);
  }

  // Hook into response stream to persist result on completion
  const originalJson = res.json.bind(res);
  res.json = (body: any) => {
    db.query(
      `UPDATE idempotency_keys 
       SET status = 'COMPLETED', response_code = $1, response_body = $2 
       WHERE key = $3`,
      [res.statusCode, JSON.stringify(body), idempotencyKey]
    ).catch(err => console.error('Failed to commit idempotency cache', err));

    return originalJson(body);
  };

  next();
}

2. Double-Entry Ledger Database Design

In a production accounting engine, balances are never stored as mutable numbers. Balance is an ephemeral projection calculated by aggregating all historical debit and credit ledger entries.

Core Rules of Double-Entry Bookkeeping

  1. Every financial transaction consists of at least two ledger entries.
  2. For every transaction, Total Debits must equal Total Credits.
  3. Ledger rows are strictly immutable append-only logs. Never issue an UPDATE or DELETE against a ledger line.
  4. Correcting an erroneous transaction requires writing a new reversal transaction.

Standard Account Types

  ASSETS + EXPENSES  =  LIABILITIES + EQUITY + REVENUES
       (Debit +)                 (Credit +)
  • Debit increases: Assets (cash, receivables), Expenses (server costs, fees).
  • Credit increases: Liabilities (customer deposits held on platform), Equity, Revenue (subscription fees earned).

Relational Schema (PostgreSQL DDL)

-- 1. Chart of Accounts
CREATE TABLE accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(64) UNIQUE NOT NULL, -- e.g., '1001-CASH', '2001-USER-LIABILITY'
    name VARCHAR(255) NOT NULL,
    type VARCHAR(32) NOT NULL CHECK (type IN ('ASSET', 'LIABILITY', 'EQUITY', 'REVENUE', 'EXPENSE')),
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. Transaction Group (The atomic event)
CREATE TABLE transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reference_id VARCHAR(255) UNIQUE NOT NULL, -- External payment ID or internal invoice ID
    description TEXT NOT NULL,
    posted_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. Ledger Entries (Debits & Credits)
CREATE TABLE ledger_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID NOT NULL REFERENCES transactions(id) ON DELETE RESTRICT,
    account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
    direction VARCHAR(6) NOT NULL CHECK (direction IN ('DEBIT', 'CREDIT')),
    amount_cents BIGINT NOT NULL CHECK (amount_cents > 0), -- Store whole cents to avoid IEEE-754 float drift
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ledger_account ON ledger_entries(account_id);
CREATE INDEX idx_ledger_tx ON ledger_entries(transaction_id);

Atomic Ledger Invariant Check

To guarantee that no transaction can ever be committed where debits do not equal credits, implement a PostgreSQL constraint trigger:

CREATE OR REPLACE FUNCTION verify_transaction_balance() 
RETURNS TRIGGER AS $$
DECLARE
    v_debit_sum BIGINT;
    v_credit_sum BIGINT;
BEGIN
    SELECT 
        COALESCE(SUM(CASE WHEN direction = 'DEBIT' THEN amount_cents ELSE 0 END), 0),
        COALESCE(SUM(CASE WHEN direction = 'CREDIT' THEN amount_cents ELSE 0 END), 0)
    INTO v_debit_sum, v_credit_sum
    FROM ledger_entries
    WHERE transaction_id = NEW.transaction_id;

    IF v_debit_sum <> v_credit_sum THEN
        RAISE EXCEPTION 'Ledger transaction % is out of balance! Debits: %, Credits: %', 
            NEW.transaction_id, v_debit_sum, v_credit_sum;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Constraint triggers evaluate at COMMIT time, allowing multiple inserts in a single transaction
CREATE CONSTRAINT TRIGGER trg_ensure_ledger_balanced
AFTER INSERT OR UPDATE ON ledger_entries
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION verify_transaction_balance();

Practical Example: Customer Deposits $100 via Stripe

When a user deposits $100 into your platform, cash arrives at your bank (Asset), but you now owe $100 in platform credit to the user (Liability):

BEGIN;

WITH new_tx AS (
    INSERT INTO transactions (reference_id, description)
    VALUES ('ch_3Mv8sP...', 'User deposit via Stripe')
    RETURNING id
)
INSERT INTO ledger_entries (transaction_id, account_id, direction, amount_cents)
VALUES 
    -- Debit Cash Asset (Increases our cash)
    ((SELECT id FROM new_tx), '11111111-0000-0000-0000-000000000001', 'DEBIT', 10000),
    -- Credit User Balances Liability (Increases what we owe the user)
    ((SELECT id FROM new_tx), '22222222-0000-0000-0000-000000000002', 'CREDIT', 10000);

COMMIT;

If anyone attempts to credit $100 without debiting $100, PostgreSQL rejects the transaction immediately at commit time.


3. Reliable Webhook Ingestion Architecture

Payment gateways (Stripe, Adyen, Circle) inform you of asynchronous payment status updates via webhooks. Webhooks are notorious for three failure modes:

  1. Network out-of-order delivery: A charge.refunded event arrives before charge.succeeded.
  2. At-least-once duplicate delivery: The gateway does not receive your 200 OK within 5 seconds and resends the event 5 times.
  3. Spoofing attacks: Malicious actors sending forged HTTP requests to your endpoint.

Production Webhook Architecture

[Stripe / Payment Gateway]
            │
            │ HTTPS POST (Signature Header)
            ▼
[Fast Ingestion Endpoint (API Gateway)]
    │
    ├── 1. Verify HMAC Signature
    │
    ├── 2. Write raw payload to `webhook_events` table (Status: 'RECEIVED')
    │      ON CONFLICT (gateway_event_id) DO NOTHING
    │
    ├── 3. Return HTTP 200 OK immediately (<50ms)
    │
    ▼
[Async Background Worker (SQS / BullMQ / pg_boss)]
    │
    ├── Fetch unprocessed events with row-level lock (`FOR UPDATE SKIP LOCKED`)
    ├── Verify event sequence / timestamp
    ├── Execute Double-Entry Ledger entries
    └── Mark event 'PROCESSED'

Signature Verification & De-duplication Schema

CREATE TABLE webhook_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    provider VARCHAR(32) NOT NULL, -- 'stripe', 'circle', 'adyen'
    external_event_id VARCHAR(255) NOT NULL,
    event_type VARCHAR(128) NOT NULL,
    payload JSONB NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'PENDING', -- 'PENDING', 'PROCESSED', 'FAILED'
    attempts INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_provider_event UNIQUE (provider, external_event_id)
);

Implementation (Node.js / Express Webhook Handler)

import { Request, Response } from 'express';
import stripe from 'stripe';
import { db } from './database';

export async function handleStripeWebhook(req: Request, res: Response) {
  const sig = req.headers['stripe-signature'];
  const endpointSecret = process.env.STRIPE_WEBHOOK_SECRET!;

  let event: stripe.Event;

  // 1. Cryptographic HMAC validation
  try {
    event = stripe.webhooks.constructEvent(req.rawBody, sig!, endpointSecret);
  } catch (err: any) {
    console.error(`⚠️ Webhook signature verification failed: ${err.message}`);
    return res.status(400).send(`Webhook Error: ${err.message}`);
  }

  // 2. Immediate persistent storage with deduplication
  try {
    const insertQuery = `
      INSERT INTO webhook_events (provider, external_event_id, event_type, payload)
      VALUES ('stripe', $1, $2, $3)
      ON CONFLICT (provider, external_event_id) DO NOTHING
      RETURNING id;
    `;
    
    const result = await db.query(insertQuery, [event.id, event.type, JSON.stringify(event)]);

    if (result.rowCount === 0) {
      // Event has already been received. Acknowledge immediately to stop gateway retries.
      return res.status(200).json({ received: true, deduplicated: true });
    }

    // 3. Acknowledge receipt within <50ms. Never do heavy processing synchronously in the HTTP handler.
    res.status(200).json({ received: true });

  } catch (error) {
    console.error('Failed to ingest webhook event', error);
    res.status(500).send('Internal Server Error');
  }
}

4. Production Checklist for Fintech Engineering

Before shipping your billing or payment service to production, audit your stack against this architectural checklist:

CheckpointRequirementFailure Mode If Omitted
Idempotency LockingUse DB/Redis lock with parameter hashing (SHA-256).Client retry on timeout double-charges customer.
Whole Cent Integer MathStore all currency values as BIGINT in minor units (cents/micros).IEEE-754 floating-point rounding errors (0.1 + 0.2 = 0.30000000000000004).
No Mutable BalancesBalance is a computed view from immutable ledger rows.Concurrent transactions overwrite balances (race conditions).
Deferred ConstraintsDEFERRABLE INITIALLY DEFERRED triggers on debits == credits.Asymmetrical transactions written to DB during unexpected errors.
Async Webhook HandlersWrite raw payload to DB and return HTTP 200 within 100ms.Webhook timeouts cause provider to backoff and throttle notifications.
Reconciliation JobsRun nightly batch scripts comparing gateway logs with internal ledger.Silent settlement drift goes undetected until bank statements fail.

Engineering for Zero Drift

Financial software is defined by how it handles degraded networks and distributed state conflicts. By pairing client-supplied idempotency keys at the network edge with immutable double-entry constraints at the database storage engine, your system guarantees financial consistency regardless of how many times a payment retry fires.

(Staying ahead of database engineering patterns, distributed systems postmortems, and fintech infrastructure shifts requires continuous signal. Read the daily technical briefs on BriefTechNews to stay informed on core backend architecture.)

Get the brief

Liked this one? Pick the beats you care about and get just those, five minutes a morning. Free, no hype, one click to leave.

About Me Author

My name is

BriefTechNews

A daily digest of what actually moved in AI, tech, crypto and fintech, assembled and written with AI, with every item linked to its source. Read More
Tags

You May Also Like