Coin ledger: double-entry

TwistCue’s coin economy uses a double-entry ledger. Every wallet change is a group of two (or more) rows that sum to zero across an event_id. Balance is derived, never stored as source of truth.

Why

• Auditability: every event has a trail; you can always reconstruct any user’s balance for any moment.
• Idempotency: the event_id prevents double-crediting when a webhook fires twice.
• Trust: reconciliation with providers is a matter of matching source_iap_* totals to the provider report.

Table shape

coin_ledger:

Column	Type	Notes
id	UUID	Row id.
user_id	UUID	The user this row affects.
account	string	The account moved (see below).
amount	int (signed)	Coins. Positive credits the account; negative debits.
reason	string	Free-tagged reason (unlock, refund, promo_grant, iap, …).
ref_type	string	e.g. episode, purchase, promo.
ref_id	string	e.g. the episode id.
event_id	UUID	Groups all rows of one operation.
created_at	timestamp	UTC.

The accounts

User-side wallet accounts (owned by the user; balance = sum of these):

• wallet_paid: coins the user paid real money for (IAP, card, dLocal, pawaPay, VAS).
• wallet_bonus: coins granted for free (promos, subscription bonus, support comp, refunds landing back).

Sources (where new coins come from, the “credit” side of a mint):

• source_iap_apple, source_iap_google
• source_card_web
• source_dlocal_* (source_dlocal_fawry_eg, source_dlocal_ovo_id, …)
• source_pawapay_*
• source_vas_*
• source_promo
• source_support_grant
• source_subscription_bonus
• source_refund (unusual: used when refunds credit coins, e.g. rewarded ad rollback)
• source_test (dev-only)

Sinks (where coins go to, the “debit” side of a spend):

• sink_unlock: episode unlock burn
• sink_refund: coins going back out on a refund
• sink_fraud: support/finance recouping from a fraudster

The invariant

For any given event_id:

SUM(amount) = 0

Enforced by the ledger service. If this ever breaks, Economy monitor flags a red banner and finance escalates.

Balance derivation

A user’s balance is:

SELECT
  SUM(CASE WHEN account = 'wallet_paid'  THEN amount ELSE 0 END) AS balance_paid,
  SUM(CASE WHEN account = 'wallet_bonus' THEN amount ELSE 0 END) AS balance_bonus,
  SUM(CASE WHEN account LIKE 'wallet_%'  THEN amount ELSE 0 END) AS balance_total
FROM coin_ledger
WHERE user_id = ?;

Computed on the fly for admin display; cached in Redis with a short TTL for hot-path mobile reads.

Worked examples

Example 1: User buys a 500-coin pack for $4.99 via Apple IAP

event_id = e1. Package includes 50 bonus coins.

account	amount	reason
source_iap_apple	-550	iap (representing the coins-worth minted)
wallet_paid	+500	iap
source_bonus	-50	iap_bonus
wallet_bonus	+50	iap_bonus

Sum: -550 + 500 - 50 + 50 = 0. ✓

Also written: purchases row with amount_cents=499, coins_granted=500, bonus_coins=50.

Example 2: User unlocks a 30-coin episode

event_id = e2. User has 50 bonus + 100 paid = 150 balance. Bonus is spent first.

account	amount	reason	ref
wallet_bonus	-30	unlock	episode:0194...
sink_unlock	+30	unlock	episode:0194...

Sum: -30 + 30 = 0. ✓ New balances: bonus 20, paid 100, total 120.

Example 3: Refund the earlier purchase

event_id = e3. Full refund of $4.99 = 550 coins.

But the user already spent 30 bonus coins on the unlock, so wallet_bonus has only 20 left. The service clamps the deduction and logs underpaid_cents:

account	amount	reason	ref
wallet_bonus	-20	refund	purchase:...
wallet_paid	-100	refund	purchase:... (user’s paid balance is 100, refund can only pull what exists)
sink_refund	+120	refund	purchase:...

Sum: 0. ✓ But underpaid_cents = (550 - 120) × $0.01 = $4.30 logged on the refund. Finance eats the delta.

Also: if reverseUnlocks=true, the episode_unlock is soft-deleted.

Example 4: Support grants 200 coins as apology

event_id = e4.

account	amount	reason
source_support_grant	-200	apology_bad_video
wallet_bonus	+200	apology_bad_video

Sum: 0. ✓ Increments the support agent’s daily 500-coin cap counter.

Example 5: 3000-coin grant → approval-pending

Support tries to grant 3000 coins. Threshold exceeded (2000). Service creates an admin_approval with the payload, no ledger rows written yet. Response: { kind: 'approval_pending', approvalId }.

When finance approves, the approval-execution path writes the ledger rows as in Example 4 (with amount=3000).

Do not

• ❌ Write to coin_ledger outside the ledger service. Always go through WalletService.adjust(), LedgerService.record(), etc.
• ❌ Update coins_wallets for balances. That table is deprecated for reads.
• ❌ Skip the event_id. Every event must have one.
• ❌ Split zero-sum across timestamps. All rows in one event write in one transaction.

Reconciliation

• Sum of source_* for a period = total minted → reconcile against provider reports.
• Sum of sink_* for a period = total burned.
• Difference = net movement into user wallets = bonus liability growth.

See Economy monitor guide for how this rolls up on the finance dashboard.