A robust payment gateway database schema for building payment processing platforms like Stripe or PayPal. Includes merchant onboarding and management, multi-method payment processing (cards, bank transfers, digital wallets), detailed transaction tracking with idempotency keys, refund workflow management, and webhook event logging for asynchronous notifications. Ideal for fintech companies, payment aggregators, or e-commerce payment infrastructure.
transactions.idempotency_key is the most important column here. A payment request that times out will be retried, and without a key that the gateway can recognise, the retry charges the customer a second time. Storing it makes the second attempt return the first result instead of creating a new charge.
api_keys carries a mode column, which is what separates test traffic from live. The same merchant holds keys for both, and every transaction made with a test key must be invisible to settlements — the alternative is a sandbox that quietly contaminates real payouts.
Webhook delivery is stored rather than attempted and forgotten. webhook_events keeps the payload, the http_status and the attempts count, because a merchant whose endpoint was down for an hour needs those events replayed, and a support question about a missing notification needs an answer.
Checking for a duplicate in application code leaves a race between two concurrent retries. A unique constraint on the merchant and idempotency_key makes the second insert fail, and the handler returns the original transaction instead. That turns a double charge from a bug that is hard to reproduce into an outcome the database makes impossible.
A separate sandbox database is cleaner and diverges from production within weeks. One schema with mode on the api_key means test traffic exercises exactly the same code path. The burden moves to every query that touches money — settlements, reporting, dashboards must filter correctly, and a single missed filter puts test charges into a real payout.
An HTTP POST that times out is indistinguishable from one that was never sent, and the merchant's reconciliation depends on receiving every event. Storing payload, http_status, attempts and status makes delivery a queue with retries and gives support a record. The cost is a table that grows with every event and needs its own retention policy.
What a merchant is owed could be summed from transactions minus refunds and fees. Once money has actually been sent, that sum is no longer the answer — a later refund or fee change would retroactively alter a completed payout. The settlement row records what was actually transferred for a period, which is the figure the bank statement will match.