Why should you care?

Wallet app on DataFlow must never create money — debits and credits balance per txn_ref with SERIALIZABLE or careful locking.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

CREATE TABLE accounts (
  account_id INT UNSIGNED PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  balance_inr DECIMAL(14,2) NOT NULL DEFAULT 0,
  CHECK (balance_inr >= 0)
);
CREATE TABLE ledger_entries (
  entry_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id INT UNSIGNED NOT NULL,
  txn_ref VARCHAR(40) NOT NULL,
  amount_inr DECIMAL(14,2) NOT NULL,
  UNIQUE KEY uq_ledger (account_id, txn_ref, amount_inr)
);
SELECT txn_ref, SUM(amount_inr) AS net
FROM ledger_entries GROUP BY txn_ref HAVING net <> 0;

What happened?

• Accounts hold current balance with non-negative CHECK.
• Ledger records legs; reconciliation query finds unbalanced txn_ref — should return zero rows in healthy system.

Practice next

1. Create accounts and ledger_entries.
2. Run transfer transaction from banking lesson.
3. Run HAVING net <> 0 audit — expect empty.
4. Daily job SUM ledger vs accounts balance per account_id.
5. Partition ledger by month for retention.

Remember

Double-entry ledger + balance column. Reconciliation SQL finds drift. Transactions + locks on hot accounts.

DataFlow wallet lab

Learners implement UPI transfer with txn_ref idempotency.

Outcome: NPCI-style audit query returns no imbalances.