Building Resilient Double-Entry Ledgers: ACID Guarantees and Idempotency in Fintech

Building Resilient Double-Entry Ledgers: ACID Guarantees and Idempotency in Fintech

In modern payment engineering, there is one non-negotiable rule: You never store user balances as a single mutable database column.

If your database schema has a table with users.balance = 500.00 and you run UPDATE users SET balance = balance - 50, your system will eventually lose money during concurrent checkouts, race conditions, or network retries.

At Kone Pay, our fintech core relies on immutable, cryptographically verifiable Double-Entry Bookkeeping. Here is how to engineer financial-grade transaction systems.

1. The Fundamental Equation of Double-Entry

Every economic event involves at least two accounts. Money never appears from nothing, and money never disappears.

For every transaction:

• An Origin Account has money deducted.
• A Destination Account has money credited.
• The sum total of debits and credits in the transaction record must strictly equal zero.

The Canonical Schema

-- Immutable Entries Table
CREATE TABLE ledger_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transaction_id UUID NOT NULL REFERENCES transactions(id),
    account_id UUID NOT NULL REFERENCES accounts(id),
    amount_cents BIGINT NOT NULL, -- Never use floating point NUMERIC for money!
    direction VARCHAR(6) CHECK (direction IN ('DEBIT', 'CREDIT')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2. Handling Concurrency with Idempotency Keys

Payment webhooks from Stripe, Paystack, or card networks frequently arrive multiple times due to retry policies. If a webhook retries 3 times, how do you prevent the customer from being credited 3 times?

The Idempotency Layer:

1. Every incoming webhook or transfer request carries a unique Idempotency Key (e.g. req-uuid-8f92b).
2. Wrap the execution in a strict database transaction with unique constraint checks:

sequenceDiagram
    autonumber
    actor Client as Client / Webhook
    participant Gateway as API Gateway / App
    participant Redis as Redis Lock Cache
    participant DB as Postgres ACID Core

    Client->>Gateway: POST /transfers (Idempotency-Key: "idemp_8f92b")
    Gateway->>Redis: SETNX lock:idemp_8f92b (TTL: 10s)
    alt Lock Acquired
        Gateway->>DB: SELECT * FROM transactions WHERE idempotency_key = 'idemp_8f92b'
        alt Already Executed (Cached Result)
            DB-->>Gateway: Existing Transaction Record (Status: COMMITTED)
            Gateway-->>Client: 200 OK (Cached Result, No Double Charge!)
        else New Transaction
            Gateway->>DB: BEGIN TRANSACTION ISOLATION SERIALIZABLE
            Gateway->>DB: SELECT balance FROM accounts WHERE id = source FOR UPDATE
            Gateway->>DB: INSERT INTO ledger_entries (Debit Source, Credit Dest)
            Gateway->>DB: INSERT INTO transactions (idempotency_key, status='COMMITTED')
            Gateway->>DB: COMMIT TRANSACTION
            DB-->>Gateway: Transaction Committed (Zero Sum Verified)
            Gateway->>Redis: DEL lock:idemp_8f92b
            Gateway-->>Client: 201 Created (Transfer Settled)
        end
    else Lock Held (Concurrent Request)
        Gateway-->>Client: 429 Conflict / Retry After
    end

import { PoolClient } from 'pg';

export async function processTransfer(
  client: PoolClient,
  idempotencyKey: string,
  sourceAccountId: string,
  destAccountId: string,
  amountCents: bigint
) {
  try {
    await client.query('BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;');

    // 1. Check idempotency record
    const existing = await client.query(
      'SELECT id, status, response FROM idempotency_keys WHERE key = $1 FOR UPDATE;',
      [idempotencyKey]
    );

    if (existing.rows.length > 0) {
      await client.query('COMMIT;');
      return existing.rows[0].response; // Return exact cached payload
    }

    // 2. Insert transaction header
    const txRes = await client.query(
      'INSERT INTO transactions (idempotency_key, description) VALUES ($1, $2) RETURNING id;',
      [idempotencyKey, 'Peer-to-Peer Transfer']
    );
    const txId = txRes.rows[0].id;

    // 3. Post balanced double-entry splits
    await client.query(
      'INSERT INTO ledger_entries (transaction_id, account_id, amount_cents, direction) VALUES ($1, $2, $3, $4), ($1, $5, $3, $6);',
      [txId, sourceAccountId, amountCents, 'DEBIT', destAccountId, 'CREDIT']
    );

    // 4. Record idempotency completion
    await client.query(
      'INSERT INTO idempotency_keys (key, status, response) VALUES ($1, $2, $3);',
      [idempotencyKey, 'COMPLETED', JSON.stringify({ success: true, txId })]
    );

    await client.query('COMMIT;');
    return { success: true, txId };
  } catch (error) {
    await client.query('ROLLBACK;');
    throw error;
  }
}

3. Integer Arithmetic: Never Use Floats

In JavaScript, 0.1 + 0.2 === 0.30000000000000004. Over a million transactions, floating-point rounding errors lead to discrepancies known as fractional slippage.

• Rule: Always represent monetary currency in the smallest atomic unit (e.g. cents, pesewas, kobo, satoshis) using 64-bit integers (BIGINT or BigInt in TypeScript).
• Format numbers into decimals purely at the UI layer.

Learn how to engineer cryptographically secure ledger gateways in our Fintech & Ledger Gateways Track.