Why Wallet Balance Shouldn't Be Calculated On‑The‑Fly: Double‑Entry Accounting, Snapshots, and Immutable Ledger Practices

Conclusion: Do not compute wallet balance on‑the‑fly

Early‑stage demos often use SELECT SUM(amount) FROM wallet_flow WHERE account_id = ?. This works while data is tiny, but as the flow table grows to millions or billions of rows the linear scan becomes a CPU bottleneck and the query cost grows linearly. The correct rule is:

Write the balance snapshot at accounting time, never calculate it at query time.

Why wallet systems break under high concurrency

Flow table is append‑only and continuously grows.

Balance queries are hot (home page, order page, withdrawal page, risk chain).

Aggregating the whole history on each query makes the cost linear in history size.

Concurrent writes, retries, callbacks and cross‑service calls easily produce inconsistent balances.

Core production‑grade wallet principles

1. Flow is fact, balance is snapshot

Flow records what happened and is append‑only.

Balance stores the current amount for fast reads.

2. Use double‑entry accounting

Every business transaction generates at least two entries (debit and credit) with equal amounts. The system validates that the sum of DR equals the sum of CR for each txn_no.

3. Balance, flow, transaction and outbox must commit in the same local transaction

Insert transaction master record.

Insert double‑entry records.

Update balance snapshot.

Write an outbox event.

All four steps are executed inside a single database transaction.

4. Prioritize correctness over throughput

No lost updates.

No missing entries.

Full traceability.

Repairability.

5. All external retries must be idempotent

Unique keys such as biz_no, request_id and txn_no are enforced at the interface, transaction and consumer layers.

6. Built‑in reconciliation and compensation

Never assume a transaction is stable; always provide a reconciliation module that can generate compensating entries.

Clarifying core concepts: account, entry, balance, freeze

Account is not a single balance column

available_balance

: funds that can be spent or withdrawn. frozen_balance: funds locked for pending withdrawals, guarantees or risk control. total_balance: usually available + frozen. status: ACTIVE, FROZEN, CLOSED, etc.

Transaction (business) vs. Entry (accounting)

A business transaction (e.g., a withdrawal request) is stored in wallet_txn. The actual money movement is recorded in wallet_entry with debit/credit directions.

Double‑entry example for a transfer

entry_no | account_id | direction | amount | meaning
E1       | A          | DR        | 100    | A funds out
E2       | B          | CR        | 100    | B funds in

The system validates that the sum of DR equals the sum of CR for the same txn_no.

Why the balance table exists

Audit trace – can be slow but must be complete (use flow entries).

Online balance – must be fast and stable (use snapshot table).

Deployable layered architecture

Typical layers (left to right): Business services → Wallet API → Idempotency → Transaction orchestration → Account lock & balance check → Accounting engine → Outbox → Kafka → Downstream services (order, risk, reconciliation, notifications).

Core data model (SQL DDL)

wallet_account (balance snapshot)

CREATE TABLE wallet_account (
    account_id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    account_type VARCHAR(32) NOT NULL COMMENT 'USER_AVAILABLE/USER_FROZEN/...',
    currency CHAR(3) NOT NULL DEFAULT 'CNY',
    available_balance BIGINT NOT NULL DEFAULT 0 COMMENT 'unit: cent',
    frozen_balance BIGINT NOT NULL DEFAULT 0 COMMENT 'unit: cent',
    version BIGINT NOT NULL DEFAULT 0,
    status VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_type_currency (user_id, account_type, currency),
    KEY idx_user_id (user_id)
) ENGINE=InnoDB COMMENT='wallet account snapshot';

wallet_txn (transaction master)

CREATE TABLE wallet_txn (
    txn_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    txn_no VARCHAR(64) NOT NULL,
    biz_no VARCHAR(64) NOT NULL COMMENT 'business order number',
    biz_type VARCHAR(32) NOT NULL COMMENT 'PAY/REFUND/TRANSFER/WITHDRAW/...',
    request_id VARCHAR(64) NOT NULL COMMENT 'idempotent request id',
    source_system VARCHAR(32) NOT NULL,
    amount BIGINT NOT NULL COMMENT 'unit: cent',
    currency CHAR(3) NOT NULL DEFAULT 'CNY',
    status VARCHAR(16) NOT NULL COMMENT 'INIT/SUCCESS/FAILED',
    remark VARCHAR(256) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_txn_no (txn_no),
    UNIQUE KEY uk_request_id (request_id),
    KEY idx_biz_no (biz_no)
) ENGINE=InnoDB COMMENT='wallet transaction master';

wallet_entry (accounting entries)

CREATE TABLE wallet_entry (
    entry_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    txn_no VARCHAR(64) NOT NULL,
    entry_no VARCHAR(64) NOT NULL,
    account_id BIGINT NOT NULL,
    related_account_id BIGINT DEFAULT NULL,
    direction CHAR(2) NOT NULL COMMENT 'DR/CR',
    amount BIGINT NOT NULL COMMENT 'unit: cent',
    balance_after BIGINT NOT NULL COMMENT 'snapshot after this entry',
    frozen_after BIGINT NOT NULL DEFAULT 0 COMMENT 'frozen snapshot after this entry',
    currency CHAR(3) NOT NULL DEFAULT 'CNY',
    biz_type VARCHAR(32) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_entry_no (entry_no),
    KEY idx_txn_no (txn_no),
    KEY idx_account_time (account_id, created_at),
    KEY idx_account_txn (account_id, txn_no)
) ENGINE=InnoDB COMMENT='wallet accounting entries';

wallet_outbox (local message table)

CREATE TABLE wallet_outbox (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    event_no VARCHAR(64) NOT NULL,
    aggregate_type VARCHAR(32) NOT NULL DEFAULT 'WALLET_TXN',
    aggregate_id VARCHAR(64) NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    payload JSON NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'PENDING',
    retry_count INT NOT NULL DEFAULT 0,
    next_retry_time DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_event_no (event_no),
    KEY idx_status_retry (status, next_retry_time)
) ENGINE=InnoDB COMMENT='wallet local outbox';

wallet_recon_diff (reconciliation differences)

CREATE TABLE wallet_recon_diff (
    diff_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    recon_date DATE NOT NULL,
    diff_type VARCHAR(32) NOT NULL COMMENT 'MISSING_ENTRY/BALANCE_MISMATCH/CHANNEL_DIFF',
    ref_no VARCHAR(64) NOT NULL,
    account_id BIGINT DEFAULT NULL,
    expected_amount BIGINT DEFAULT NULL,
    actual_amount BIGINT DEFAULT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'NEW',
    detail JSON DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_recon_date_type (recon_date, diff_type),
    KEY idx_status (status)
) ENGINE=InnoDB COMMENT='wallet reconciliation diff';

Transaction flow example (transfer)

Transfer command model

public record TransferCommand(
    String requestId,
    String bizNo,
    Long fromUserId,
    Long toUserId,
    Long amount,
    String currency,
    String operator
) {}

Service implementation (key steps)

@Service
@RequiredArgsConstructor
@Transactional(rollbackFor = Exception.class, isolation = Isolation.READ_COMMITTED)
public class WalletTransferService {
    private final WalletAccountRepository accountRepository;
    private final WalletTxnRepository txnRepository;
    private final WalletEntryRepository entryRepository;
    private final WalletOutboxRepository outboxRepository;
    private final TxnNoGenerator txnNoGenerator;

    public String transfer(TransferCommand cmd) {
        // 1. Idempotent check
        WalletTxn existed = txnRepository.findByRequestId(cmd.requestId());
        if (existed != null) return existed.getTxnNo();

        // 2. Resolve account IDs
        Long fromAccountId = accountRepository.findAccountId(cmd.fromUserId(), "USER_AVAILABLE", cmd.currency());
        Long toAccountId   = accountRepository.findAccountId(cmd.toUserId(),   "USER_AVAILABLE", cmd.currency());

        // 3. Order IDs and lock rows (prevent deadlock)
        List<Long> orderedIds = Stream.of(fromAccountId, toAccountId).sorted().toList();
        Map<Long, WalletAccount> locked = accountRepository.lockByIds(orderedIds);
        WalletAccount from = locked.get(fromAccountId);
        WalletAccount to   = locked.get(toAccountId);
        if (from == null || to == null) throw new BizException("ACCOUNT_NOT_FOUND");
        if (cmd.amount() <= 0) throw new BizException("INVALID_AMOUNT");
        if (from.getAvailableBalance() < cmd.amount()) throw new BizException("INSUFFICIENT_BALANCE");

        // 4. Generate txn_no and insert master record
        String txnNo = txnNoGenerator.next("WT");
        WalletTxn txn = WalletTxn.success(txnNo, cmd.bizNo(), cmd.requestId(), "TRANSFER", cmd.amount(), cmd.currency(), "wallet-api");
        txnRepository.insert(txn);

        // 5. Update balances
        long fromAfter = from.getAvailableBalance() - cmd.amount();
        long toAfter   = to.getAvailableBalance() + cmd.amount();
        int updated1 = accountRepository.updateAvailableBalance(from.getAccountId(), fromAfter, from.getVersion());
        int updated2 = accountRepository.updateAvailableBalance(to.getAccountId(),   toAfter,   to.getVersion());
        if (updated1 != 1 || updated2 != 1) throw new BizException("ACCOUNT_VERSION_CONFLICT");

        // 6. Insert double‑entry records
        WalletEntry debit  = WalletEntry.debit(txnNo, from.getAccountId(), to.getAccountId(), cmd.amount(), fromAfter, from.getFrozenBalance(), "TRANSFER", cmd.currency());
        WalletEntry credit = WalletEntry.credit(txnNo, to.getAccountId(),   from.getAccountId(), cmd.amount(), toAfter,   to.getFrozenBalance(),   "TRANSFER", cmd.currency());
        entryRepository.batchInsert(List.of(debit, credit));

        // 7. Verify debit/credit balance
        long debitTotal  = entryRepository.sumAmountByTxnNoAndDirection(txnNo, "DR");
        long creditTotal = entryRepository.sumAmountByTxnNoAndDirection(txnNo, "CR");
        if (debitTotal != creditTotal) throw new IllegalStateException("ENTRY_NOT_BALANCED");

        // 8. Write outbox event
        WalletTxnCreatedEvent event = new WalletTxnCreatedEvent(txnNo, cmd.bizNo(), cmd.fromUserId(), cmd.toUserId(), cmd.amount(), cmd.currency());
        outboxRepository.insert(OutboxMessage.pending(txnNo, "WALLET_TRANSFER_SUCCEEDED", JsonUtils.toJson(event)));

        return txnNo;
    }
}

Step‑by‑step reasoning:

Idempotent check using request_id.

Lock both accounts in ascending account_id order to avoid deadlock (A→B vs B→A).

Validate balance before proceeding.

Insert transaction master, then update the snapshot balances.

Insert debit and credit entries.

Sum DR and CR amounts; abort if they differ.

Persist an outbox record; the outbox publisher will send the Kafka event after the DB commit.

Why lock accounts in ascending order

Two concurrent transfers A→B and B→A would deadlock if one thread locks A then B while the other locks B then A. Sorting IDs before locking eliminates this classic deadlock pattern.

Why keep both FOR UPDATE and version

FOR UPDATE

prevents concurrent writes within the same transaction. version enables optimistic‑lock detection, audit assistance and compatibility with optimistic‑update scenarios.

Withdrawal, freeze and unfreeze design

Withdrawal request is not a direct debit

User requests withdrawal of 100 units.

Available balance decreases by 100.

Frozen balance increases by 100.

Channel processes payout.

If the channel fails, the frozen amount is returned to available.

Account state example

Initial: available 10,000 cents, frozen 0.

After request of 3,000 cents: available 7,000 cents, frozen 3,000 cents.

After success: available 7,000 cents, frozen 0.

After failure: available 10,000 cents, frozen 0.

Production‑level freeze entry

User available account: DR 3000 User frozen account: CR 3000 When withdrawal succeeds, the frozen account becomes DR 3000 and the platform payout account receives CR 3000. Freeze is a true accounting migration, not just a status flag.

Ensuring correctness and throughput under high concurrency

Idempotency – three layers

Interface layer: unique request_id.

Transaction layer: unique txn_no.

Consumer layer: unique event_no or message key.

Hot‑account governance

Hot accounts (e.g., red‑packet pool, platform main account) cause row‑level lock contention. Common mitigations:

Account sharding – split a logical account into many buckets.

Day‑time aggregation + night‑time reconciliation.

Pre‑allocation + batch settlement for large‑amount accounts.

Decouple high‑frequency marketing grants from real outflows.

Example: a single red‑packet pool is split into 128 sub‑buckets and routed by user or activity ID to avoid a single‑row lock bottleneck.

Sharding design

wallet_account

sharded by user_id or account_id. wallet_entry sharded by account_id or date+hash(account_id). wallet_txn sharded by txn_no or biz_no.

If a transfer touches two different shards, it is no longer a simple single‑DB transaction; the system should keep related accounts in the same shard or use a clearing‑center approach.

Why distributed two‑phase commit is rarely used

High complexity.

Reduced availability.

Difficult recovery.

Instead, keep ACID within a single accounting domain and achieve eventual consistency across domains via event‑driven messages and compensation.

Redis as cache, not source of truth

MySQL stores the authoritative ledger.

Redis caches balance snapshots for fast reads.

On transaction commit, delete or asynchronously refresh the cache to avoid stale reads.

Message consistency – Outbox pattern

Wrong way: send Kafka inside transaction

If the message is sent before the DB transaction commits, a rollback leaves downstream consumers with a phantom event; if the send fails after commit, downstream never sees the event.

Correct way – write to Outbox table then publish

@Scheduled(fixedDelay = 500)
public void publishOutbox() {
    List<OutboxMessage> messages = outboxRepository.findPending(200);
    for (OutboxMessage message : messages) {
        try {
            kafkaTemplate.send("wallet-events", message.getAggregateId(), message.getPayload()).get();
            outboxRepository.markSuccess(message.getId());
        } catch (Exception ex) {
            outboxRepository.markRetry(message.getId(), message.getRetryCount() + 1, nextRetryTime(message.getRetryCount()));
        }
    }
}

Consumer idempotency

Consumers must deduplicate using event_no or txn_no to handle retries, out‑of‑order delivery or duplicate pushes.

Balance query design – fast and stable

Typical read path:

Client → Wallet Query API → Redis → MySQL wallet_account

.

Prefer reading from Redis.

On cache miss, read from DB and back‑fill.

After a successful write, delete the cache or asynchronously refresh it.

Why delete‑cache instead of write‑through

Updating Redis inside the transaction can fail, leaving DB correct but cache stale. Deleting the cache guarantees the next read fetches the fresh DB value.

Point‑in‑time balance queries

Two common solutions:

Replay wallet_entry.balance_after up to the target timestamp.

Maintain a daily balance snapshot table ( wallet_balance_snapshot).

Production typically uses both: real‑time reads from the snapshot table, daily analysis from the daily snapshot, and deep audit from entry replay.

Standard process flows

Recharge : create order → redirect to payment channel → channel callback success → wallet entry → outbox → notify downstream → end‑of‑day reconciliation.

Payment deduction : place order → query available balance → pre‑freeze → order confirmation → final deduction → merchant pending settlement entry → emit payment‑success event.

Withdrawal : submit request → available → frozen → risk review → call payout channel → success: frozen → payout account; failure: frozen → available; generate notification.

Refund : trigger refund → verify original payment → create refund transaction → merchant debit / user credit → emit refund event → channel/internal reconciliation.

Reconciliation & compensation : pull external statements → standardize → internal balance verification → channel‑diff comparison → generate diff records → auto‑compensate or manual review → close diff.

Compensation must be a formal transaction (new entry), never a direct UPDATE on the balance.

Reconciliation – the wallet’s lifeline

Internal balance check

SELECT txn_no FROM wallet_entry GROUP BY txn_no HAVING
    SUM(CASE WHEN direction='DR' THEN amount ELSE 0 END) <>
    SUM(CASE WHEN direction='CR' THEN amount ELSE 0 END);

Balance snapshot verification

SELECT a.account_id, a.available_balance, e.balance_after
FROM wallet_account a
JOIN (
    SELECT t1.account_id, t1.balance_after
    FROM wallet_entry t1
    JOIN (
        SELECT account_id, MAX(entry_id) max_id
        FROM wallet_entry
        GROUP BY account_id
    ) t2 ON t1.account_id = t2.account_id AND t1.entry_id = t2.max_id
) e ON a.account_id = e.account_id
WHERE a.available_balance <> e.balance_after;

Channel reconciliation

Compare internal flow tables with external channel statements for recharge success vs. wallet entry, withdrawal success vs. wallet outflow, etc. Diff types include missing entry, extra entry, amount mismatch, status mismatch.

Post‑diff repair

Automatic compensation transactions.

Automatic reversal transactions.

Automatic frozen‑balance rollback.

Escalate to manual ticket when needed.

All repairs are recorded as new accounting entries.

Exception scenarios and recovery

Deduction succeeds but client times out

Front‑end shows "processing".

Client can query by request_id to get the final status.

Server returns the already‑successful transaction.

Transaction commits but Kafka message fails

Outbox retry mechanism handles the resend; no manual intervention required.

Duplicate channel callbacks

Idempotent handling using channel_txn_no or request_id ensures the same result is returned.

Reconciliation finds a missing entry

Generate a compensation transaction (type RECON_COMPENSATE) that inserts the missing entry and updates the snapshot.

Database master‑slave switch or failure recovery

Retain binlog for point‑in‑time recovery.

Regular backups.

Disaster‑recovery drills.

Replay capability for balance reconstruction.

Evolution roadmap – from monolith to distributed

Stage 1: Single‑DB single service

Suitable for early product.

Single MySQL, single wallet service, local transactions.

Stage 2: Read‑write split + cache + full reconciliation

Introduce Redis cache for balances.

Kafka for event distribution.

Daily reconciliation, alerts, reporting.

Stage 3: Sharding + hot‑spot governance

Shard accounts and entries.

Logical bucket for hotspot accounts.

Separate accounting domain from business domain.

CDC to build analytical and reconciliation stores.

Stage 4: Centralized clearing center

Multiple wallet products converge to a unified accounting center.

Business systems submit only accounting commands.

Standardized ledger for wallet, payment, settlement and audit.

Governance capabilities beyond code

Monitoring metrics

Wallet accounting success rate.

Accounting latency (P99).

Account lock wait time.

Deadlock count.

Idempotency hit rate.

Outbox backlog size.

Kafka publish failures.

Reconciliation diff count.

Automatic compensation success rate.

Audit log fields

request_id, biz_no, txn_no, account_id, amount, operator, source_system, trace_id.

Permission & risk controls

Large‑payment secondary verification.

Risk‑account freezing.

Manual adjustment approval workflow.

Operational subsidy limits.

Stress testing & failure drills

Concurrent deductions on the same account.

Bidirectional transfer deadlock scenarios.

MQ duplicate messages.

Redis full eviction.

Outbox massive backlog.

DB master‑slave switch.

Bulk reconciliation compensation.

Common pitfalls

Storing amounts as double – use BIGINT (cents) or BigDecimal.

Allowing updates on flow entries – flow must be immutable; corrections use reverse entries.

Direct UPDATE wallet_account for repairs – always create a compensating entry.

Coarse lock granularity – lock the specific account row, not the whole user.

Single platform total account as hotspot – split into buckets or use a clearing layer.

Missing business state machine (e.g., withdrawal statuses) – leads to ambiguous error handling.

API surface for a real project

Write APIs

POST /api/wallet/recharge

POST /api/wallet/pay

POST /api/wallet/transfer

POST /api/wallet/refund

POST /api/wallet/freeze

POST /api/wallet/unfreeze

POST /api/wallet/withdraw/apply

POST /api/wallet/withdraw/confirm

POST /api/wallet/withdraw/fail

Query APIs

GET /api/wallet/accounts/{userId}

GET /api/wallet/transactions/{txnNo}

GET /api/wallet/entries?accountId=xxx

GET /api/wallet/balance/snapshot?userId=xxx&date=yyyy-MM-dd

Ops & reconciliation APIs

POST /api/wallet/recon/run

GET /api/wallet/recon/diffs

POST /api/wallet/recon/compensate

POST /api/wallet/admin/adjust

(requires approval, operator, audit trail).

Real‑world end‑to‑end scenario (e‑commerce)

Initial user available balance: 50,000 cents. Merchant pending settlement: 0.

Step 1 – Purchase (20,000 cents)

User available: DR 20,000 Merchant pending: CR 20,000 Result: User 30,000 cents, Merchant 20,000 cents.

Step 2 – Refund (5,000 cents)

Merchant pending: DR 5,000 User available: CR 5,000 Result: User 35,000 cents, Merchant 15,000 cents.

Step 3 – Withdrawal request (10,000 cents)

User available: DR 10,000 User frozen: CR 10,000 Result: User available 25,000 cents, frozen 10,000 cents.

Step 4 – Withdrawal success

User frozen: DR 10,000 Platform payout account: CR 10,000 Result: User frozen 0, platform payout increased by 10,000 cents.

Every step is recorded as a formal double‑entry transaction, making the whole flow auditable and reversible.

Final takeaway

Never modify a balance directly; every fund movement must be represented by a traceable, auditable and compensatable accounting entry.

When balance is treated as a snapshot, flow as immutable facts, and double‑entry as the truth, the wallet system gains the reliability needed for production.