Building a Robust Member Points System with Spring Boot: Account Model, Idempotency & Reconciliation

1. Business Context: Points, Growth Value, Levels & Mall

Points, growth value, and levels are three distinct dimensions. Points are spendable assets with credit/debit, batch-based expiration, and strict reconciliation. Growth value is cumulative contribution (usually rolling 12 months or calendar-year reset). Level is a derived mapping from growth value, refreshed asynchronously.

Sources: payment rebate, daily sign-in, content interaction, friend invitation, ops compensation. Consumption: mall redemption, order deduction (1 CNY = 100 points), lottery. Expiration uses FIFO per batch at 23:59:59. Freeze occurs on order placement: points frozen, then deducted on payment success or released on timeout/cancel. Level perks (discounts, free shipping, birthday gifts, dedicated support) are separate from the points ledger.

Challenges: zero tolerance for point discrepancies; burst writes during promotions (sign-in, payment rebate); fraud scripts for mass registration and simulated sign-ins requiring upfront risk control; complex state machines for expiration, freeze, unfreeze needing compensation logic.

2. Account Model: Account, Flow, Batch, Rules & Benefits

2.1 Modeling Principles

Account table holds only balance snapshots; the true ledger is the flow table — every balance must be reconstructible from flows.

Batch table is the sole authority for expiration: each credit creates a batch with its own expiry; never expire against total account balance.

All amounts stored as int/bigint (cents or points), never double/float.

2.2 Table Structures

t_point_account — one row per member: available, frozen, total_earned, total_consumed, total_expired, version for optimistic locking, updated_at.

t_point_flow — immutable ledger, insert-only: flow_no (unique), idem_key (unique, format bizType:memberId:bizNo), biz_type (ORDER_EARN, SIGN, EXCHANGE, EXPIRE, ADJUST...), biz_no, direction (1 in, -1 out), change_amount (impact on available balance; 0 when deducting from frozen), balance_before, balance_after, batch_id, expire_at, remark. Indexes on member_id, created_at and biz_type, biz_no.

t_point_batch — one batch per credit: batch_no (unique), earn_flow_no (unique), total_amount, remain_amount, expire_at, status (1 valid, 2 exhausted, 3 expired), version. Indexes for expiration scanning.

Unique indexes on flow_no and earn_flow_no are mandatory to prevent duplicate flows or duplicate batches from the same credit.

2.3 Levels & Benefits

t_level_rule

maps level_code to min_growth. t_level_benefit stores per-level perks (DISCOUNT, FREE_SHIP, BIRTHDAY). t_growth_account tracks current_growth, level_code, level_expire_at. Daily queries served from Redis cache point:acc:{memberId}; DB remains source of truth.

3. Points Credit & Debit: Event-Driven, Idempotency Keys & Transaction Boundaries

3.1 Event-Driven Credit

External facts (payment success, review completion) publish to RocketMQ; consumer writes to local DB in a single transaction.

@RocketMQMessageListener(topic = "order_paid", consumerGroup = "point_earn_group")
@Component
public class OrderPaidConsumer implements RocketMQListener<OrderPaidEvent> {
    private final PointEarnService earnService;
    @Override
    public void onMessage(OrderPaidEvent event) {
        earnService.earn(PointEarnCmd.builder()
            .memberId(event.getMemberId())
            .bizType("ORDER_EARN")
            .bizNo(event.getOrderNo())
            .amount(calcPoints(event))
            .expireDays(365)
            .build());
    }
}

3.2 Idempotency Key Design

Key format: bizType:memberId:bizNo. Enforced by unique index uk_idem on t_point_flow.idem_key. Business natural keys survive MQ redelivery, duplicate clicks, job retries; UUIDs do not convey "same business once" semantics.

public final class IdemKeys {
    public static String of(String bizType, Long memberId, String bizNo) {
        return bizType + ":" + memberId + ":" + bizNo;
    }
}

3.3 Credit Transaction

Because flow rows contain balance_before/balance_after, the account row must be locked first to read current balance, then flow inserted, then account updated. Pseudocode:

@Transactional(rollbackFor = Exception.class)
public EarnResult earn(PointEarnCmd cmd) {
    String idemKey = IdemKeys.of(cmd.getBizType(), cmd.getMemberId(), cmd.getBizNo());
    if (flowMapper.existsByIdemKey(idemKey)) return EarnResult.duplicated(idemKey);
    PointAccount acc = accountMapper.selectForUpdate(cmd.getMemberId());
    if (acc == null) throw new BizException("Account not found");
    PointFlow flow = buildFlow(cmd, idemKey, acc.getAvailable(), acc.getAvailable() + cmd.getAmount());
    try { flowMapper.insert(flow); }
    catch (DuplicateKeyException e) { return EarnResult.duplicated(idemKey); }
    accountMapper.increase(flow.getMemberId(), cmd.getAmount());
    if (cmd.getAmount() > 0) batchMapper.insert(buildBatch(cmd, flow));
    growthService.addGrowth(cmd.getMemberId(), cmd.getGrowthDelta()); // local only
    return EarnResult.success(flow.getFlowNo());
}

Mapper update uses conditional increment:

<update id="increase">
  UPDATE t_point_account
  SET available = available + #{amount},
      total_earned = total_earned + #{amount},
      version = version + 1,
      updated_at = NOW(3)
  WHERE member_id = #{memberId}
</update>

3.4 Transaction Boundaries

One local transaction touches only local tables (account, flow, batch, growth if local). No RPC or MQ sends inside — otherwise rollback leaves ghost messages. Use local message table or transactional messages for cross-service events.

Cross-service accepts eventual consistency: after commit, publish point_changed to notify marketing/notification centers; rely on MQ retry.

Redis is an acceleration layer, DB is truth. They will drift; compensation paths are essential (see §5).

4. Expiration & Freeze: FIFO Deduction, Expiration Job & Exception Compensation

4.1 FIFO Batch Deduction

Consumption must deduct from earliest-expiring batches first. A single consumption may span multiple batches; idempotency key must be at the "consumption order" level ( bizType:memberId:bizNo), while batch-level flows use bizNo:batchId to avoid unique-index collisions.

@Transactional(rollbackFor = Exception.class)
public void consume(Long memberId, int amount, String bizType, String bizNo) {
    String consumeIdem = IdemKeys.of(bizType, memberId, bizNo);
    if (flowMapper.existsByIdemKey(consumeIdem)) return;
    PointAccount acc = accountMapper.selectForUpdate(memberId);
    if (acc == null || acc.getAvailable() < amount) throw new BizException("Insufficient points");
    if (flowMapper.existsByIdemKey(consumeIdem)) return; // re-check under lock
    int remain = amount;
    boolean firstFlow = true;
    List<PointBatch> batches = batchMapper.listValidAsc(memberId);
    for (PointBatch b : batches) {
        if (remain <= 0) break;
        int take = Math.min(remain, b.getRemainAmount());
        if (batchMapper.deduct(b.getId(), take) == 0) continue; // CAS failed
        String flowIdem = firstFlow ? consumeIdem : consumeIdem + ":" + b.getId();
        flowMapper.insert(buildConsumeFlow(memberId, b, take, bizType, bizNo, flowIdem));
        firstFlow = false;
        remain -= take;
    }
    if (remain > 0) throw new BizException("Valid batches exhausted");
    accountMapper.decrease(memberId, amount);
}

Batch deduction uses conditional update:

UPDATE t_point_batch
SET remain_amount = remain_amount - #{take},
    status = IF(remain_amount - #{take} = 0, 2, 1),
    version = version + 1
WHERE id = #{id} AND status = 1 AND remain_amount >= #{take}

Account deduction similarly guarded:

UPDATE t_point_account
SET available = available - #{amount},
    total_consumed = total_consumed + #{amount},
    version = version + 1,
    updated_at = NOW(3)
WHERE member_id = #{memberId} AND available >= #{amount}

4.2 Expiration Job

Runs daily at 00:10, sharded (16 shards), cursor-based pagination (500 rows per batch), each batch processed in its own transaction. expireOne does three things atomically: mark batch expired ( WHERE status = 1 CAS), deduct account available, write EXPIRE flow. Idempotency key EXPIRE:{batchNo} ensures re-runs don't double-deduct.

Expiration time unified to expire_at = credit_time + N days at 23:59:59 using calendar-day boundary. Off-by-one seconds cause user complaints.

4.3 Freeze & Unfreeze

Order deduction flow:

Place order : Freeze → available -N, frozen +N

Payment success : Deduct → frozen -N (batch actually deducted)

Timeout/Cancel : Unfreeze → frozen -N, available +N

t_point_freeze table: freeze_no (unique), member_id, amount, biz_no (order no), status (1 frozen, 2 deducted, 3 unfrozen), expire_at (auto-unfreeze deadline). Unique index on member_id, biz_no prevents duplicate freezes.

Critical detail: batches frozen may expire. Freeze records which batches are occupied; frozen batches excluded from expiration scan. On unfreeze, if batch already expired, handle per policy (treat as expired or reallocate). Must define upfront.

Compensation job scans status=1 AND expire_at < now(), checks order state: if paid → confirm deduct; else → unfreeze. All state transitions use UPDATE ... WHERE status = 1 CAS.

5. High-Concurrency Design: Redis Pre-Deduction, Eventual Consistency, Hotspot Sharding & Write Merging

5.1 Why Redis Pre-Deduction

Sign-in, limited-time redemption can see 200 req/sec per member. Direct DB hits cause row-lock contention. Redis acts as gatekeeper; DB remains truth.

Lua script (atomic in Redis):

-- KEYS[1] = point:acc:{memberId}
-- KEYS[2] = idem:{idemKey}
-- ARGV[1] = deduct amount
local idemExists = redis.call('EXISTS', KEYS[2])
if idemExists == 1 then return -1 end -- duplicate
local avail = tonumber(redis.call('HGET', KEYS[1], 'available') or '0')
local amount = tonumber(ARGV[1])
if avail < amount then return 0 end -- insufficient
redis.call('HINCRBY', KEYS[1], 'available', -amount)
redis.call('SET', KEYS[2], '1', 'EX', 172800)
return 1 -- pre-deduction success

Java side calls script, on success publishes async point_deduct message for DB persistence.

5.2 DB Eventual Consistency

Async consumer does real DB deduction with same idempotency key. On failure, throw to trigger MQ retry; exhausted retries go to DLQ for compensation job.

Consistency safety net: delayed check (5 min later) replays "Redis deducted, DB not" keys; reverse compensation for "DB deducted, Redis not synced". DB is ultimate truth.

5.3 Hotspot Account Sharding

Real hotspots: platform pool, activity prize pool, merchant accounts. Shard by hash(memberId) % N into t_point_account_shard (PK member_id, shard_no). Deduction picks a shard with sufficient available; aggregate balance via SUM(available), cached in Redis, refreshed async. Ordinary C-end accounts need not shard — overdesign doubles reconciliation complexity.

5.4 Async Write Merging

High-frequency low-value points (sign-in, browse) can merge by member: Redis HINCRBY accumulates for 5 sec or 100 events, then batch-insert single summary flow. Only valid for same batch/expiry policy; cross-batch merging breaks FIFO order.

6. Anti-Fraud: Rate Limiting, Blacklists, Device Fingerprint & Manual Review

6.1 Four-Layer Funnel

Request → ① Rate limit (Redis token bucket) → ② Blacklist (user/IP/device)
       → ③ Device fingerprint & behavior → ④ Rule/model scoring → Pass / Manual review

6.2 Rate Limiting

Per-scene counters in Redis ( rl:{scene}:{memberId}) with TTL. Note: increment + expire not atomic; for strictness use Lua or SET key 1 NX EX ttl for first request. Separate thresholds per scene: sign-in 1/day, invite 20/day, interaction 100/day.

6.3 Device Fingerprint

Frontend collects deviceId + screen params + font list + Canvas fingerprint → hash to fp. Risk signals: fp linked to >5 memberIds; same IP segment registers >20 accounts/hour; same device signs in >X times/hour.

if (riskClient.countMembersOfDevice(fp, 1) > 5) {
    riskRecordService.markSuspicious(memberId, "DEVICE_MULTI_ACCOUNT");
    return RiskDecision.REVIEW;
}

6.4 Anomaly Detection Rules

Sign-in within 5 min of registration : 100% trigger → Tag

Same IP registers >20 accounts/hour : Trigger → Rate limit + review

Earn/burn ratio abnormal (only in, no out) for 7 days : Continuous 7 days → Review

Redemption addresses clustered in same neighborhood : Trigger → Review

On hit, delay credit (e.g., 24h) pending manual review. Legitimate users unaffected; fraudsters lose instant gratification — more effective than outright ban.

public void earnWithRisk(PointEarnCmd cmd) {
    RiskDecision d = riskService.evaluate(cmd);
    switch (d) {
        case PASS     -> earnService.earn(cmd);
        case REVIEW   -> earnService.earnPending(cmd); // pending table, no available balance increase
        case REJECT   -> log.warn("reject earn, member={}, reason={}", cmd.getMemberId(), d.getReason());
    }
}

7. Reconciliation System: Flow Aggregation, Balance Snapshots, Diff Detection & Auto-Repair

Points are assets; reconciliation is mandatory. Three core equations:

① Yesterday balance + Today flow net change = Today balance
② Σ Batch remain = Account available + Frozen
③ Σ Flow credits - Σ Flow debits = Current account balance (from zero)

Flow change_amount definition frozen: only reflects available balance impact. Freeze records -N, unfreeze +N, payment success deducting from frozen records 0 (or omitted from available flow). Inconsistent definitions break equation ①.

7.1 Daily Snapshot

Snapshot table mirrors account plus snap_date. Must cover ALL accounts with non-zero balance (available ≠ 0 OR frozen ≠ 0), not just changed ones, otherwise baseline missing for unchanged accounts.

INSERT INTO t_point_account_snapshot(member_id, available, frozen, total_earned, snap_date)
SELECT member_id, available, frozen, total_earned, CURDATE()
FROM t_point_account
WHERE available <> 0 OR frozen <> 0;

Large tables: batch load, monthly archive, daily partitioning.

7.2 Diff Detection

SQL compares snapshot balance + today's flow net vs current account balance; returns mismatches.

7.3 Diff Classification & Auto-Repair

Account changed but flows don't match → likely missing flow; manual confirm then insert.

Flows complete but balance wrong → trust flows, recalc balance.

Batch remain sum ≠ available + frozen → batch corrupted; reallocate from earliest valid batch.

Freeze record stuck but order terminal → apply order state (deduct or unfreeze).

Repair must be replayable:

@Transactional
public void repairByFlow(Long memberId) {
    if (!lock.tryLock("recon:" + memberId, 30, TimeUnit.SECONDS)) return;
    long netSum = flowMapper.sumChange(memberId);
    PointAccount acc = accountMapper.selectForUpdate(memberId);
    if (acc.getAvailable() == netSum) return;
    int delta = (int)(netSum - acc.getAvailable());
    flowMapper.insert(buildAdjustFlow(memberId, delta, "RECON_AUTO_FIX"));
    accountMapper.setAvailable(memberId, (int)netSum);
    batchMapper.adjustFirstValidBatch(memberId, delta);
    reconLogMapper.insert(...);
}

Historical flows never modified; only append ADJUST flows. Altering original evidence makes future reconciliation worse.

Lock must be outside transaction (or use programmatic transaction) to avoid reading stale data after lock release but before commit.

7.4 Alerting

Metrics to Prometheus: diff count, max diff, repair success rate. Alert on any diff (should be 0 daily), single diff >1000 points → P1, auto-repair failure rate >5% → circuit breaker to manual. Don't wait for month-end close.

8. Testing & Production Pitfalls

8.1 Must-Test Scenarios (Real MySQL via Testcontainers)

Concurrent deduction no oversell : 100 threads × 20 deductions of 1 point from 1000 initial; assert success count = 1000, available = 0, batch remain sum = available + frozen.

Idempotency works once : 50 threads same idempotency key; assert balance = 100, flow count = 1.

Reconciliation equations hold : for sample members, flow sum change == account available.

H2 differs from MySQL on ON DUPLICATE KEY and row-lock behavior; "tests pass, production fails" often stems from this.

8.2 Production Lessons (by pain level)

Never unconditional SET available = available - n; must have WHERE available >= n, zero rows → exception.

No RPC/MQ in transaction. Ghost points from rolled-back tx with sent message. Use local message table or transactional messages.

Idempotency key missing memberId → two users collide on same order number, one's points overwritten.

Expiration job full-table scan → replica lag explosion. Batch ≤500 rows, transaction ≤50 rows.

Redis is gatekeeper only; must have compensation for "Redis deducted, DB not".

Freeze record must have expire_at; missing timeout = asset black hole.

Reconciliation job on replica, not primary; daytime peak + recon = cluster stall.

Level up/down needs debounce: 24h cooldown on growth-value fluctuation to avoid perk thrashing.

Design reconciliation FIRST, then ledger. Ledger structure determines reconcilability; retrofitting is agony.

8.3 Closing Thoughts

The deepest lesson: points systems are hard because accounts must balance and errors must be traceable; issuing points is easy. Account holds balance, flow holds truth, batch handles expiry — three tables in one transaction, plus daily reconciliation job — that's the backbone. Idempotency relies on business natural keys + unique indexes, not MQ guarantees. High concurrency: Redis pre-deduction + async persistence + delayed check + DLQ fallback. Risk control front-loaded, review back-loaded; delay fraudster payout rather than ban. Once this skeleton runs, coupons, red packets, growth value, virtual currency all fit — just rename and change rules. The real value of a member middle platform is this ledger + idempotency + reconciliation skeleton.