Why Every D2C Wallet Should Be a Ledger, Not a Counter

Friday post-mortem: when we deleted 30,000 customer wallets by accident.

Then realized we didn't.

Because we'd built the wallet as a ledger, not a counter.

This is one of those engineering choices that feels overcautious in week one and saves your business in month nine. At Xenotix Labs we've shipped wallet systems for D2C dairy commerce (Veda Milk), subscription marketplaces (Prepe), service marketplaces (Cremaster, Housecare), insurance survey payouts (ClaimsMitra), and crypto MLM (BullBot). Different industries, same wallet architecture pattern. Here's why.

The two ways to model a wallet

The counter approach. A users table has a wallet_balance column. Every credit and debit updates the column with UPDATE users SET wallet_balance = wallet_balance + ? WHERE id = ?. Simple, fast, easy to query.

The ledger approach. A wallet_ledger table records every credit and debit as an immutable row. The user's "balance" is computed at read time as SUM(amount) over their ledger entries. Slightly more storage, slightly more compute on read, but with a critical property: history is preserved.

Most teams ship the counter approach because it looks simpler. Then they spend the next two years answering customer-support tickets like "why is my balance off by ₹12?" with no way to answer.

What the ledger gives you

Auditability. Every change is a row with a timestamp, a reason code (signup_credit, order_debit, refund, manual_adjustment), an actor (user, system, admin), and a reference (which order, which subscription, which support ticket). When a customer disputes a balance, you have the receipts.

Reversibility. When a bug double-charges customers, you don't fix it by manually editing balances. You insert reverse entries with reason_code = 'reversal_of_X' linking to the bad rows. The reversal itself is now an audit-trail entry. You can prove what happened to anyone who asks.

Re-derivability. If your wallet_balance cache (yes, you can still cache the computed balance) gets corrupted by a bad migration, you re-derive it from the ledger in one query. We've done this in production. It's a non-event when the ledger exists.

Concurrency safety. Two simultaneous debits from the same user can't race when each is its own row. With a counter, you're relying on database-level locking which is fragile across multiple services.

The schema

Here's a stripped-down version of what we ship:

wallet_ledger
  id              (uuid, primary key)
  user_id         (foreign key)
  amount          (integer, in paise; positive = credit, negative = debit)
  reason_code     (enum: signup_credit, order_debit, refund, ...)
  reference_type  (string: 'order', 'subscription', 'support_ticket', ...)
  reference_id    (uuid, points at the entity that caused this entry)
  idempotency_key (uuid, prevents duplicate inserts)
  created_at      (timestamp)
  metadata        (jsonb, free-form for analytics)

No updated_at. Rows are never updated, only inserted.

The balance query

SELECT COALESCE(SUM(amount), 0) AS balance_in_paise
FROM wallet_ledger
WHERE user_id = $1;

Fast on indexed user_id even with millions of rows. If it gets slow at scale, materialize a wallet_balance_cache table that stores the computed balance per user and gets updated by an after-insert trigger. The ledger remains the source of truth; the cache is just an optimization.

Idempotency, always

Every wallet write must be idempotent. Networks fail. Workers retry. If the same idempotency_key is inserted twice, the second insert is a no-op (we use INSERT ... ON CONFLICT (idempotency_key) DO NOTHING).

This costs one indexed column. It saves you from being the engineer at 2 a.m. who has to figure out whether the customer was double-charged.

The transactional wrapper

Wallet debits never live alone. They're paired with the operation they pay for: an order placement, a subscription renewal, a service booking. We always wrap both in a single Postgres transaction:

BEGIN;
INSERT INTO orders (...) VALUES (...);
INSERT INTO wallet_ledger (..., amount = -order.amount, ...);
COMMIT;

If either insert fails, both roll back. There's no state where the order exists but the wallet wasn't charged, or vice versa.

For cross-service flows (order service writes the order; wallet service writes the ledger), we use the outbox pattern: the order service writes the order + an outbox row in the same transaction, and a worker picks up the outbox row and tells the wallet service to debit. Eventually consistent, never inconsistent.

Refunds

A refund is a positive ledger entry with reason_code = 'refund' and reference_id pointing at the original debit. We never "reverse" a debit by editing it. We compensate with a new entry. The customer's balance updates correctly and the audit trail shows exactly what happened.

Reporting

With a ledger, financial reporting is trivial. "How much did we credit users last month?" is SUM(amount) WHERE amount > 0 AND reason_code = 'signup_credit' AND created_at IN (...). Counter-based wallets can't answer that without a separate analytics system you forgot to build.

Lessons from production

• Use an integer paise/cent column, never a float. Floating-point arithmetic in money columns is how you get ₹0.0000001 errors that compound.
• Snapshot balances daily. Even with a fast SUM query, a daily wallet_balance_snapshot table lets you do historical analytics ("what was the balance on March 1?") without scanning the whole ledger.
• Rate-limit manual_adjustment writes. This is the only way for non-systematic balance changes to enter the ledger. Audit it heavily.
• Don't delete ledger entries, ever. If a row was inserted by mistake, insert a compensating reversal. Deletion breaks the audit trail forever.

Building a wallet, points system, or money-handling product?

Whether it's subscription wallets, marketplace earnings, escrow, points, or refunds, Xenotix Labs has shipped ledgers that survive real customer load and real edge cases. Reach out at https://xenotixlabs.com.