Model credits, purchases, commissions and refunds as durable postings, then serialize the decisions that spend them.
A reseller wallet looks like a number until someone asks why it changed. A prepaid top-up, an order charge, a commission credit and a refund can all affect the same balance, but they carry different evidence and reversal rules. Updating wallet.balance preserves the answer to “how much?” while discarding much of the answer to “why?”
This article was drafted with AI assistance, then fact-checked and edited by the developer behind eSIMQR.
The balance is an answer, not the record
Consider an illustrative USD wallet. A reseller adds $100, buys a product for $30 and earns a $3 commission credited to the wallet. Its posted balance is now $73. If the purchase is fully refunded and the commission is reversed, the wallet returns to $100.
A mutable balance can represent every one of those outcomes. It cannot, by itself, distinguish the correct sequence from an accidental $27 adjustment. Nor does it tell you whether a second refund notification has already been processed.
An append-only ledger records each accepted financial event and the accounts it affects. The displayed balance is a projection of those postings. A cached balance is still useful, especially for authorization and fast reads, but it must be reproducible from the record underneath it.
I would use balanced entries even for a modest reseller system. They force each movement to have a counterpart. A wallet credit must come from somewhere; a wallet debit must go somewhere. That discipline catches missing legs and makes reconciliation more explicit. It does not prove that the transaction was legitimate: an erroneous transaction can balance perfectly.
Give every movement a counterpart
For a wallet subledger, define a signed movement convention: positive amounts increase an account's tracked position, and negative amounts decrease it. This convention is not a substitute for debit and credit rules in a general ledger. Document how these accounts map into the operator's accounting system.
The example uses four accounts: reseller wallet, funding clearing, order clearing and commission expense. Its postings are:
- Top-up: wallet +$100; funding clearing −$100.
- Purchase: wallet −$30; order clearing +$30.
- Commission: wallet +$3; commission expense −$3.
- Purchase refund: wallet +$30; order clearing −$30.
- Commission reversal: wallet −$3; commission expense +$3.
Clearing accounts identify amounts awaiting reconciliation with other records. They should not become miscellaneous buckets that hide unexplained differences. A payment processor's settlement and fees may require further entries; a wallet top-up alone does not establish that the operator has received settled cash.
The commission policy matters just as much as the arithmetic. Here, the $30 purchase debit and $3 commission credit are separate events, and the commission becomes immediately spendable. Another business might charge a net wholesale price or keep commissions pending until an eligibility date. Choose one policy explicitly. Crediting a commission and also discounting the same purchase by that commission would count the benefit twice.
Keep currency inside the account identity. USD and EUR postings cannot balance each other by adding their integers. Currency conversion needs an explicit exchange transaction, a recorded rate and a rounding policy. For this example, all amounts are integer cents in USD.
A small schema with deliberate limits
A useful starting point separates accounts, journal entries and postings:
CREATE TABLE ledger_account (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
currency text NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
kind text NOT NULL,
UNIQUE (id, currency)
);
CREATE TABLE journal_entry (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
operation_key text NOT NULL UNIQUE,
kind text NOT NULL,
currency text NOT NULL CHECK (currency ~ '^[A-Z]{3}$'),
source_ref text NOT NULL,
reverses_id bigint REFERENCES journal_entry(id),
recorded_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (id, currency)
);
CREATE TABLE posting (
entry_id bigint NOT NULL,
account_id bigint NOT NULL,
currency text NOT NULL,
amount_minor bigint NOT NULL CHECK (amount_minor <> 0),
PRIMARY KEY (entry_id, account_id),
FOREIGN KEY (entry_id, currency)
REFERENCES journal_entry(id, currency),
FOREIGN KEY (account_id, currency)
REFERENCES ledger_account(id, currency)
);
The composite foreign keys keep an entry and its accounts in the same currency. The posting key permits one aggregated movement per account per entry. source_ref connects the financial event to an order, payment or adjustment record; reverses_id preserves the relationship to an earlier entry.
This is a schema skeleton, not a complete enforcement mechanism. It does not enforce a zero sum across postings, require two distinct accounts or prevent later edits. Ordinary row checks cannot validate an aggregate across a posting set. Use a controlled posting function or a deferred constraint trigger to reject incomplete or unbalanced entries at commit, and restrict direct table writes. The application role should not be able to update or delete posted history.
The wallet balance is the sum of its amount_minor values. If you maintain a cached balance, update it in the same database transaction as the postings. Reconciliation should periodically compare that cache with the sum and surface any difference.
Make operation_key identify the business effect, such as order:123:purchase, rather than an individual HTTP delivery. Duplicate requests must return the existing result. Store enough request information to reject reuse of a key with a different amount, currency or source; uniqueness alone cannot detect that mismatch.
Serialize available funds, including reservations
A ledger preserves history. It does not automatically prevent overspending.
Suppose two orders each try to spend $60 from a $100 wallet. If both read the balance before either commits, both may authorize. Their entries can be individually balanced while leaving the wallet overdrawn.
One practical PostgreSQL protocol uses a stable wallet account row as the serialization point. Start a transaction at READ COMMITTED, lock that row with SELECT ... FOR UPDATE, then read the current posted balance and active reservations in subsequent statements. Validate the operation, write its financial or reservation changes, update any cache and commit. PostgreSQL holds the row lock until transaction end, so competing transactions using that same lock wait. See the PostgreSQL documentation on explicit locking.
Every operation that changes spendability must follow this protocol. That includes top-ups, purchases, commissions, refunds and adjustments, plus reservation creation, release and consumption. Locking only ledger writes leaves a race between a new hold and a purchase authorization.
A reservation has three states in this design: active, consumed and released. Available funds equal posted balance minus active reservations. Consuming a $30 hold must atomically create the $30 purchase debit and mark the hold consumed. Otherwise, an intermediate state can either charge and hold the amount simultaneously or make the held funds available before the charge exists. Releasing a hold changes no posted balance, but it still changes authorization capacity and requires the same lock.
Do not hold a database transaction open across a supplier HTTP call. Commit the reservation, perform the external work, then consume or release it in another serialized transaction. An ambiguous timeout needs investigation or an idempotent status check; elapsed time alone is weak evidence that no purchase occurred.
For operations involving multiple wallets, acquire locks in a consistent order. A single wallet lock also imposes a throughput limit on a busy account. That is a concrete tradeoff: straightforward authorization correctness in exchange for serialized mutations per wallet.
Refunds append corrections to history
A refund should add entries rather than delete the purchase. In the example, the refund credits $30 and the commission reversal debits $3, producing a net wallet increase of $27.
If policy requires both effects together, commit the refund and commission reversal in one transaction. Link each to its original entry, and give each effect a stable operation key derived from the refund identity. A duplicate callback then finds the existing refund result instead of minting another credit.
Partial refunds need more than a reversal pointer. Under the wallet lock, check the cumulative refunded amount against the original charge. Calculate the commission reversal from the original commission policy, including its rounding rule, rather than from today's configuration. Preserve that calculation's inputs with the refund record.
The reseller may already have spent the commission. Decide whether a reversal may create debt, whether another refund credit offsets it, or whether the amount becomes a separate receivable. An append-only ledger exposes this business decision; it cannot make it disappear. Silently capping the reversal at the current balance changes the economic result and needs its own explicit policy and record.
Auditability needs evidence outside the postings
Balanced entries answer whether the arithmetic is internally consistent. Reconciliation asks whether those entries agree with independent evidence.
For each top-up, retain the payment identity, currency, amount and relevant payment status. For each order debit, retain the order and fulfillment relationship. For commissions, record the policy version and eligible amount. Administrative adjustments need an actor and reason, not just a free-form “correction” label.
Record when the system accepted an entry separately from when the underlying event occurred. A late payment notification can describe an earlier event; rewriting its timestamp to look timely obscures what the system knew when it authorized spending.
An append-only application interface is not tamper-proof storage. Database administrators can have broader powers. Restricted roles, retained backups and access logs provide additional evidence, while external payment and supplier records provide independent checks. Start with useful reconciliation queries: unbalanced entries, cache differences, unmatched funding, over-refunded orders and consumed reservations without a corresponding charge.
Applying this to a reseller platform
My own product, eSIMQR, is a self-hosted platform for selling travel eSIMs under the operator's brand. Its reseller portal includes prepaid wallets and commissions, making these accounting boundaries directly relevant. Its PostgreSQL backend and built-in mock supplier and payment gateway provide a setting for local end-to-end testing without charging money or issuing a real eSIM.
Those product facts do not establish that the illustrative schema or locking protocol above is its implementation. They define useful acceptance tests for a wallet system: repeated payment delivery, competing purchases, reservation consumption and refunds with commission reversals. Real provider access, approval, funding and production verification remain the operator's responsibility.
Top comments (0)