How to Secure Wallet Funds at Billion‑Scale Through Reconciliation: Balance Checks, Channel Matching, and Auto‑Repair

Why Wallet Systems Must Implement Reconciliation

When transaction volume grows to millions or billions, relying solely on successful database writes is insufficient; risks arise from the "world outside the DB transaction" such as lost callbacks, status disorder, duplicate entries, and concurrency overwrites, any of which can become real financial loss.

Core Responsibilities of a Reconciliation System

Balance Validation : Verify that internal account balances and transaction ledgers obey conservation laws.

Channel Matching : Ensure internal transaction records align with external channel statements.

Auto‑Repair : Within a safe boundary, automatically correct deterministic, idempotent discrepancies.

These responsibilities are hierarchical: balance validation fixes "my own books", channel matching fixes "my books vs the outside world", and auto‑repair fixes "how to restore the correct state after a difference is found".

Underlying Principles

2.1 Wallet as a Funds State Machine

A wallet tracks multiple sub‑balances: available_balance: usable funds frozen_balance: funds locked for pending operations pending_balance: amount in processing total_balance: sum of all sub‑balances

Business actions trigger state transitions, e.g., a successful recharge increases both available_balance and total_balance, while a withdrawal request moves amount from available_balance to frozen_balance.

2.2 Balance Validation Invariant

opening_balance + sum(positive_flows) - sum(negative_flows) = closing_balance

In practice this expands to three independent equations for available, frozen, and total balances; checking only a single balance field would miss many errors.

2.3 Channel Matching as Dual‑Event Alignment

Channel statements differ in field names, status values, business types, and timestamps, so a standardization layer is required before comparison.

2.4 Auto‑Repair as Controlled Compensation

Root cause must be identifiable.

Repair actions must be replayable (idempotent).

Repair must be auditable.

High‑risk scenarios require manual intervention.

Auto‑repair never updates balances directly; it creates a compensating transaction (e.g., a reversal or a confirm‑income operation).

System Architecture and Layered Design

The reconciliation system is split into six layers:

Data Collection Layer – gathers internal ledgers, channel bills, callbacks, and audit logs.

Data Standardization Layer – normalizes bill structures, currencies, statuses, and business types.

Reconciliation Calculation Layer – performs balance validation, channel matching, and custom rule checks.

Difference Center – archives differences, classifies them, tracks retry status, and records risk level.

Repair Execution Layer – issues compensating transactions, ensures idempotence, and enforces risk thresholds.

Governance & Operations Layer – handles task orchestration, reporting, alerts, manual review, and audit trails.

Key Database Schemas

Core tables: wallet_account: stores sub‑balances, version for optimistic locking, and status. wallet_ledger: records each balance delta with a global idempotent request_no. internal_trade_order: internal transaction record. channel_bill_detail: raw channel statement. recon_batch: batch metadata (batch_no, type, status, counts). recon_diff: each discovered discrepancy with risk level and repair status. recon_repair_record: audit of every repair action.

Balance Validation Implementation

Validation runs as a batch job that shards accounts by hash or modulo to avoid full‑table scans. Example service:

@Service
public class BalanceRecheckService {
    private final JdbcTemplate jdbcTemplate;
    private final ReconDiffRepository reconDiffRepository;
    public void check(LocalDate billDate, int shardIndex, int shardTotal) {
        String start = billDate + " 00:00:00";
        String end = billDate.plusDays(1) + " 00:00:00";
        String sql = """
            SELECT a.account_no, a.available_balance, a.frozen_balance, a.total_balance,
                   COALESCE(SUM(l.delta_available),0) AS delta_available_sum,
                   COALESCE(SUM(l.delta_frozen),0)   AS delta_frozen_sum,
                   COALESCE(SUM(l.delta_total),0)    AS delta_total_sum
            FROM wallet_account a
            LEFT JOIN wallet_ledger l ON l.account_no = a.account_no
                AND l.created_at >= ? AND l.created_at < ?
            WHERE MOD(a.id, ?) = ?
            GROUP BY a.account_no, a.available_balance, a.frozen_balance, a.total_balance
            HAVING COALESCE(SUM(l.delta_total),0) <> 0
               AND ABS(COALESCE(SUM(l.delta_available),0) + COALESCE(SUM(l.delta_frozen),0) - COALESCE(SUM(l.delta_total),0)) > 0.000001
            """;
        jdbcTemplate.query(sql, rs -> {
            ReconDiff diff = new ReconDiff();
            diff.setReconType("BALANCE");
            diff.setBizOrderNo(rs.getString("account_no"));
            diff.setBizType("ACCOUNT_BALANCE");
            diff.setDiffType("ACCOUNT_NOT_BALANCED");
            diff.setRiskLevel("HIGH");
            reconDiffRepository.saveIfAbsent(diff);
        }, start, end, shardTotal, shardIndex);
    }
}

Channel Matching Workflow

A ChannelBillAdapter interface abstracts each channel's parsing and status normalization. Example for Alipay:

public interface ChannelBillAdapter {
    String channel();
    Stream<StandardBill> parse(InputStream inputStream);
    String normalizeStatus(String rawStatus);
    String normalizeBizType(String rawBizType);
}

@Component
public class AlipayBillAdapter implements ChannelBillAdapter {
    @Override public String channel() { return "ALIPAY"; }
    @Override public Stream<StandardBill> parse(InputStream inputStream) {
        BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8));
        return reader.lines().skip(1).map(this::toStandardBill);
    }
    @Override public String normalizeStatus(String raw) {
        return switch (raw) {
            case "TRADE_SUCCESS", "TRADE_FINISHED" -> "SUCCESS";
            case "WAIT_BUYER_PAY" -> "PROCESSING";
            case "TRADE_CLOSED" -> "FAILED";
            default -> "UNKNOWN";
        };
    }
    @Override public String normalizeBizType(String raw) {
        return switch (raw) {
            case "PAYMENT" -> "PAY";
            case "REFUND" -> "REFUND";
            default -> "UNKNOWN";
        };
    }
    private StandardBill toStandardBill(String line) {
        String[] cols = line.split(",");
        return StandardBill.builder()
            .channel("ALIPAY")
            .channelOrderNo(cols[0])
            .merchantOrderNo(cols[1])
            .bizType(normalizeBizType(cols[2]))
            .amount(new BigDecimal(cols[3]))
            .fee(new BigDecimal(cols[4]))
            .normalizedStatus(normalizeStatus(cols[5]))
            .tradeTime(LocalDateTime.parse(cols[6], DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")))
            .build();
    }
}

The engine processes internal trades in pages, fetches the corresponding channel bills in bulk, performs in‑memory matching, classifies differences (e.g., MATCH, OURS_ONLY, CHANNEL_ONLY, AMOUNT_MISMATCH, FEE_MISMATCH, STATUS_MISMATCH, DUPLICATED_BILL, AMBIGUOUS_MATCH), and writes the results to recon_diff.

Auto‑Repair Decision Logic

Only discrepancies that satisfy both "channel final state is clear" and "internal compensation is idempotent" are auto‑repaired. Typical eligible scenarios include:

Internal PROCESSING while channel reports SUCCESS → confirm success.

Internal PROCESSING while channel reports FAILED → rollback/freeze release.

Internal FAILED while channel reports SUCCESS → trigger inbound compensation.

High‑risk cases (amount mismatch, currency conversion, large amounts, ambiguous status) are routed to manual review.

Repair Execution Engine

Repair follows "rule routing + strategy execution + idempotent record + audit". Example strategy for the first scenario:

public class ProcessingButChannelSuccessRepairStrategy implements RepairStrategy {
    private final ChannelQueryGateway channelQueryGateway;
    private final TradeOrderService tradeOrderService;
    private final LedgerCommandService ledgerCommandService;
    @Override public boolean supports(ReconDiff diff) {
        return "STATUS_MISMATCH".equals(diff.getDiffType()) &&
               "PROCESSING".equals(diff.getInternalStatus()) &&
               "SUCCESS".equals(diff.getChannelStatus());
    }
    @Transactional
    @Override public RepairResult repair(ReconDiff diff) {
        ChannelOrderSnapshot snapshot = channelQueryGateway.queryLatest(diff.getChannel(), diff.getBizOrderNo());
        if (!snapshot.isSuccess()) {
            return RepairResult.skipped("channel latest status is not success");
        }
        String requestNo = "AUTO_CONFIRM_" + diff.getId();
        tradeOrderService.markSuccessIfProcessing(diff.getBizOrderNo(), requestNo);
        ledgerCommandService.confirmIncomeIfAbsent(diff.getBizOrderNo(), requestNo);
        return RepairResult.success(requestNo, "confirmed by latest channel success status");
    }
}

The executor obtains a distributed lock per difference, checks for existing successful repairs, selects the appropriate strategy, runs it, records the outcome, and finally releases the lock.

End‑to‑End Business Example

A user initiates a 1,000 CNY withdrawal. The wallet freezes the amount, marks the internal order as PROCESSING, and calls the bank. The bank times out, later marks the transaction as failed, but the callback is lost. The next day the internal order is still PROCESSING, leaving the user's funds frozen.

During the T+1 channel reconciliation, the system discovers a STATUS_MISMATCH (internal PROCESSING vs channel FAILED). The auto‑repair strategy re‑queries the bank, confirms the failure, and triggers a balance release via a compensating transaction. The order status updates to FAILED, the frozen amount is returned to the available balance, and an audit record is stored.

Process Flow Overview

The full pipeline is:

Channel bill → Bill ingestion → Raw bill storage → Standardization → Reconciliation batch creation → Internal trade sharding → Dual‑side matching → Difference classification → Auto‑repair rule filtering → Auto‑repair execution → Manual review (remaining diffs) → Financial closure & batch archiving

Scalability & High‑Concurrency Practices

Shard ledger and account tables by account_no or biz_order_no.

Asynchronously import large bill files (stream → bounded queue → batch insert).

Split reconciliation tasks by channel, date, and hash‑based shard.

Read from read‑replicas or data‑warehouse copies.

Batch write differences and repair records.

Apply back‑pressure between parsing, loading, and reconciliation threads.

Ensure task idempotency (unique batch_no, diff unique key, repair requestNo).

From T+1 to Near‑Real‑Time

Teams often evolve from nightly batch to hourly incremental, then to minute‑level streaming using Kafka/Flink. A Flink SQL example for minute‑level diff detection:

INSERT INTO recon_diff_stream
SELECT COALESCE(i.biz_order_no, c.merchant_order_no) AS biz_order_no,
       i.amount AS internal_amount,
       c.amount AS channel_amount,
       i.status AS internal_status,
       c.normalized_status AS channel_status
FROM internal_trade_stream i
FULL OUTER JOIN channel_bill_stream c
  ON i.biz_order_no = c.merchant_order_no
 AND i.biz_type = c.biz_type
WHERE i.biz_order_no IS NULL
   OR c.merchant_order_no IS NULL
   OR i.amount <> c.amount
   OR i.status <> c.normalized_status;

Observability & Auditing

Metrics: bill import success rate, batch success rate, diff rate, auto‑repair hit & success rates, manual backlog size, channel query latency, per‑minute diff volume.

Audit logs: batch lifecycle events, per‑diff generation rationale, repair request details (operator, requestNo, before/after snapshots), risk‑gate decisions.

Practical Checklist for Architects

Define a multi‑state account model (available/frozen/pending/total).

Include version/optimistic‑lock fields in account tables.

Store immutable ledger entries with a global idempotent key.

Build a channel‑bill standardization layer.

Design a fine‑grained diff classification model.

Restrict auto‑repair to deterministic, idempotent, auditable scenarios.

Implement batch state machine with clear statuses (INIT, BILL_DOWNLOADING, RECONCILING, AUTO_REPAIRING, WAIT_MANUAL_REVIEW, FINISHED, FAILED).

Provide a manual review console that shows internal snapshots, raw bills, diff classification, and allows re‑query & approval.

Monitor diff rate, repair rate, and backlog; set alerts.

Plan a migration path from nightly batch to near‑real‑time streaming.