Question 1

What is an idempotency key and why is it essential for payments?

Accepted Answer

An idempotency key is a caller-generated UUID included with every payment API request. If the request fails (network timeout, 500 error) and the caller retries, the server checks the key: if already completed, return the original response without charging again; if in-progress, return 409 or wait. Without idempotency keys, a network timeout after a successful charge causes the client to retry and double-charge the customer. Implementation: a payments_requests table with a UNIQUE constraint on idempotency_key. Before processing: INSERT a row with status=processing — if the INSERT fails (key exists), return the stored response. After processing: UPDATE to status=complete with the response body. This atomically prevents duplicate processing: the INSERT succeeds exactly once.

Question 2

How do you design an append-only financial ledger?

Accepted Answer

A financial ledger never updates or deletes records — every transaction is an immutable append. Schema: ledger_entries (id, account_id, amount DECIMAL(18,2) NOT NULL, entry_type (CREDIT/DEBIT), reference_id, description, created_at). Amount is positive for credits (money in) and negative for debits (money out). Current balance = SELECT SUM(amount) FROM ledger_entries WHERE account_id = ?. This full scan is slow for large accounts — precompute and cache balance in an accounts table, updated atomically with each ledger insert in the same transaction. The ledger is the source of truth; the cached balance is a read optimization. Run daily reconciliation: recompute balance from ledger for all accounts and compare against cached balances — any mismatch indicates a bug.

Question 3

How do you prevent double charges at the database level?

Accepted Answer

Application-level checks ('check if charged before charging') are insufficient — concurrent requests can both pass the check before either commits. Prevent double charges at the database level: (1) Idempotency key unique constraint — the UNIQUE constraint on payments_requests.idempotency_key prevents two concurrent requests with the same key from both inserting successfully; only one succeeds, the other gets the existing result. (2) Business-level unique constraint — add UNIQUE (order_id, charge_type) to the charges table. A second charge attempt for the same order fails with a constraint violation rather than creating a duplicate charge. Both constraints together provide defense in depth: idempotency keys handle retry-induced duplicates; the business constraint handles bugs that trigger two independent charge attempts.

Question 4

How do you reconcile local payment records with external payment providers?

Accepted Answer

External payment provider calls (Stripe, Braintree) can fail in ways that leave your local database inconsistent: the provider charged the card but your database never recorded success (network timeout after the provider responded). Daily reconciliation: (1) fetch all transactions from the provider's API for the previous day; (2) compare against your local charges table by provider charge ID; (3) identify discrepancies: local records with no provider record (possible uncaptured authorization), provider records with no local record (payment occurred but wasn't recorded — refund immediately or record the charge), status mismatches (local says pending, provider says completed). Automatically resolve clear cases; alert the payments team for ambiguous ones. Never trust only your local database for financial accuracy — the provider's records are authoritative for what was actually charged.

Payments System: Low-Level Design

Idempotency Keys

Double-Charge Prevention

Ledger Design

Handling External Payment Providers

Refund Design