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.

$$\sum \text{Debits} - \sum \text{Credits} = 0$$

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:

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.