Fintech Development

Double-Entry Ledger Schema Prompt

Design a double-entry ledger database schema for a multi-currency wallet system, including balance invariants and schema DDL.

Anselm Fowel / Public collection29 Sept 2026

Your prompt

The prompt

You are a fintech engineer who has designed the core ledger for a multi-currency wallet product moving [SCALE, e.g., $500M+] a year, and you treat the ledger as the single source of truth that every other service reconciles against, never the other way around.

I'm building a wallet system in [DATABASE] that must support balances in [LIST OF CURRENCIES] for [USER TYPE: CONSUMERS / MERCHANTS / BOTH]. Transactions include [TRANSACTION TYPES: DEPOSITS / WITHDRAWALS / P2P TRANSFERS / FX CONVERSIONS]. My stack is [LANGUAGE/FRAMEWORK].

First, design the double-entry schema itself: the accounts table, the ledger entries table, and how each transaction produces at least two balanced entries. Explain why balances should be derived from summed entries rather than stored as a mutable column, and show how you'd still serve fast balance reads without recomputing the full history on every request.

Next, show how the schema enforces the fundamental invariant that debits equal credits for every transaction, including what constraint or application-layer check prevents an unbalanced entry from ever being committed. Address how multi-currency accounts are represented without allowing entries in different currencies to be summed together by mistake.

Then walk through how this schema handles an FX conversion between two currencies as a single atomic transaction touching four ledger entries, and how a failed or partially-applied conversion is either fully rolled back or fully compensated, never left half-done.

Finally, output the complete schema as DDL for [DATABASE] with comments explaining each constraint's purpose, followed by a checklist of the 8 invariants a nightly job should verify (e.g., sum of all entries per currency equals zero, every transaction has exactly balanced debits and credits) to catch ledger corruption before it reaches customers.

1,867 characters

Your prompt

Recent articles