Wallets and stored balance: patterns, pitfalls and fixes

Wallets and stored balance: patterns, pitfalls and fixes

When your platform holds customer money—whether as credits, gift cards, or prepaid balances—you're running a financial system that demands absolute precision. A single race condition, one missing ledger entry, or an overlooked compliance requirement can turn a feature launch into a regulatory investigation or a customer trust crisis. Over sixty days into payment engineering, we're moving beyond transaction processing into the realm of stored value management, where patterns matter as much as performance.

Why stored balance systems fail

Most teams start with the same mistake: they treat wallets as a single number in a database column. UPDATE users SET balance = balance - 100 WHERE id = 123. This approach collapses under real-world load, audit requirements, and dispute scenarios. When a customer claims they had 50 credits and you deducted 100, you have no forensic trail. When regulators ask for every transaction on every account in 2023, you have nothing. When concurrent requests hit your system, the balance might go negative.

Wallets and stored balance systems work best when built on immutable ledger entries. Every credit and debit is a permanent record. The balance is always a projection—a sum of all ledger entries since account creation.

Ledger first — Every credit and debit as a ledger entry

A ledger entry is a write-once record of a balance change. It has a type (credit, debit, refund, expiry), an amount, a reason, a timestamp, and ideally a reference to the triggering event (order ID, refund ID, gift card ID).

interface LedgerEntry {
  id: string;           // UUID, primary key
  userId: string;       // Foreign key to users
  type: 'credit' | 'debit' | 'refund' | 'expiry' | 'adjustment';
  amount: number;       // In cents
  currency: string;     // 'USD', 'EUR', etc.
  reason: string;       // 'purchase', 'refund', 'gift_card_activation'
  metadata: {
    orderId?: string;
    refundId?: string;
    giftCardId?: string;
    adminNotes?: string;
  };
  createdAt: Date;
  expiresAt?: Date;     // For promotional credits
}

interface WalletBalance {
  userId: string;
  totalBalance: number;
  availableBalance: number;  // Excludes holds
  holds: number;              // Pending transactions
  lastUpdatedAt: Date;
}

The schema is simple: two tables. ledger_entries is append-only. wallet_balances is a materialized view—it gets updated after every ledger entry, but it's not the source of truth. The balance can always be recalculated by summing the ledger.

Why this matters in production: when a customer disputes a charge and you process a reversal, you don't modify the original entry. You add a type: 'refund' entry with a negative amount. Your audit log is complete. When a regulator audits you, every penny is traceable to its source event.

Top-ups — Payments create credit entries

When a customer pays to load their wallet, the payment processor (Stripe, PayPal, or your gateway) creates a payment intent or charge. Once that charge succeeds, you create a ledger credit entry. The key is idempotency: if the webhook fires twice, you should only add one ledger entry.

async function topUpWallet(userId: string, amountCents: number, stripeChargeId: string) {
  const trx = await db.transaction();
  
  try {
    // Check if this charge already created a ledger entry
    const existing = await trx.query(
      'SELECT id FROM ledger_entries WHERE metadata->>\'chargeId\' = $1',
      [stripeChargeId]
    );
    
    if (existing.length > 0) {
      console.log(`Charge ${stripeChargeId} already processed`);
      await trx.rollback();
      return { idempotent: true };
    }
    
    // Insert ledger entry
    const entryId = crypto.randomUUID();
    await trx.query(
      `INSERT INTO ledger_entries (id, user_id, type, amount, reason, metadata, created_at)
       VALUES ($1, $2, $3, $4, $5, $6, NOW())`,
      [entryId, userId, 'credit', amountCents, 'payment_topup', JSON.stringify({ chargeId: stripeChargeId })]
    );
    
    // Update balance projection
    const newBalance = await trx.query(
      `UPDATE wallet_balances 
       SET total_balance = total_balance + $1, last_updated_at = NOW()
       WHERE user_id = $2
       RETURNING total_balance`,
      [amountCents, userId]
    );
    
    await trx.commit();
    return { success: true, newBalance: newBalance[0].total_balance };
  } catch (err) {
    await trx.rollback();
    throw err;
  }
}

The transaction wraps the idempotency check, ledger insert, and balance update. If any part fails, we roll back and the webhook can safely retry. The metadata field holds the Stripe charge ID, so we detect duplicates by looking it up.

One detail: always store both total_balance and available_balance. When a customer initiates a purchase, you immediately create a hold—a pending debit. This reduces availableBalance but not total balance. If they abandon the cart, the hold expires and the balance recovers. This prevents overselling.

Spend — Debit within a transaction with balance checks

When a customer uses their wallet to pay for an order, you need to:

1. Check that availableBalance >= amount
2. Create a hold entry if you want to reserve the funds
3. Only debit after the order ships or is confirmed
4. Release the hold if the order is cancelled

async function reserveWalletBalance(userId: string, amountCents: number, orderId: string) {
  const trx = await db.transaction();
  
  try {
    // Lock the row to prevent concurrent updates
    const balance = await trx.query(
      'SELECT total_balance, available_balance FROM wallet_balances WHERE user_id = $1 FOR UPDATE',
      [userId]
    );
    
    if (balance.length === 0) {
      throw new Error('Wallet not found');
    }
    
    const availableBalance = balance[0].available_balance;
    
    if (availableBalance < amountCents) {
      await trx.rollback();
      throw new Error(`Insufficient balance. Available: ${availableBalance}, Required: ${amountCents}`);
    }
    
    // Create a hold entry (negative amount, not yet debited)
    const holdId = crypto.randomUUID();
    await trx.query(
      `INSERT INTO ledger_entries (id, user_id, type, amount, reason, metadata, created_at)
       VALUES ($1, $2, $3, $4, $5, $6, NOW())`,
      [holdId, userId, 'hold', -amountCents, 'order_placement', JSON.stringify({ orderId })]
    );
    
    // Reduce available balance
    await trx.query(
      `UPDATE wallet_balances 
       SET available_balance = available_balance - $1, last_updated_at = NOW()
       WHERE user_id = $2`,
      [amountCents, userId]
    );
    
    await trx.commit();
    return { holdId, success: true };
  } catch (err) {
    await trx.rollback();
    throw err;
  }
}

async function confirmWalletDebit(userId: string, orderId: string, holdId: string, amountCents: number) {
  const trx = await db.transaction();
  
  try {
    // Create the actual debit entry
    await trx.query(
      `INSERT INTO ledger_entries (id, user_id, type, amount, reason, metadata, created_at)
       VALUES ($1, $2, $3, $4, $5, $6, NOW())`,
      [crypto.randomUUID(), userId, 'debit', -amountCents, 'order_payment', JSON.stringify({ orderId, holdId })]
    );
    
    // Release the hold by cancelling it
    await trx.query(
      `INSERT INTO ledger_entries (id, user_id, type, amount, reason, metadata, created_at)
       VALUES ($1, $2, $3, $4, $5, $6, NOW())`,
      [crypto.randomUUID(), userId, 'hold_release', amountCents, 'order_confirmed', JSON.stringify({ holdId })]
    );
    
    // Update balance
    await trx.query(
      `UPDATE wallet_balances 
       SET total_balance = total_balance - $1, available_balance = available_balance - $1, last_updated_at = NOW()
       WHERE user_id = $2`,
      [amountCents, userId]
    );
    
    await trx.commit();
    return { success: true };
  } catch (err) {
    await trx.rollback();
    throw err;
  }
}

Notice the FOR UPDATE lock. This serializes concurrent operations on the same wallet. Without it, two requests could both read availableBalance = 50, both think they can spend 40, and both succeed—leaving you -30.

Also notice that we create separate ledger entries for the hold, the debit, and the hold release. This creates a complete audit trail and makes it easy to handle edge cases (order cancelled before confirmation, payment failed, refund requested later).

Compliance — Stored value may have legal requirements

Depending on your jurisdiction and business model, wallets may be regulated as stored value or prepaid instruments. In the US, the FTC has rules around expiration dates and fees. In the EU, PSD2 and GDPR impose specific requirements. The safest approach: consult legal counsel and then bake compliance into your schema.

• Expiration dates: Gift cards and promotional credits often expire. Always store expiresAt on ledger entries. At the end of each day, create expiry entries to reduce the balance of expired credits.
• Audit trails: Every modification to a customer's balance must be traceable. Your ledger table is your proof.
• Segregation: Keep a separate table for funds you hold on behalf of the customer (e.g., merchant reserves, escrow). This is not customer-owned balance.
• Reporting: Regulators may request a breakdown of total stored value by category (gift cards, credits, refunds) on a specific date. Your ledger makes this query trivial.

async function getStoredValueBreakdown(asOfDate: Date) {
  const query = `
    SELECT
      type,
      COUNT(*) as count,
      SUM(amount) as total_amount,
      currency
    FROM ledger_entries
    WHERE created_at <= $1
      AND (expires_at IS NULL OR expires_at > $1)
    GROUP BY type, currency
  `;
  
  return db.query(query, [asOfDate]);
}

Common mistakes

• Balance as a single column: If you treat balance as a mutable field, you lose auditability and introduce race conditions. Always derive balance from ledger entries.
• No holds or reservations: Debiting immediately opens you to overselling. Implement a hold-then-confirm flow, especially for orders with asynchronous fulfillment.
• Ignoring expiration: If your jurisdiction allows expired credits, you must enforce expiration. Create explicit expiry ledger entries so your balance is always correct.
• Weak idempotency: Webhook retries are inevitable. Every external event (Stripe charge, refund, etc.) needs a unique identifier in your ledger. Check for duplicates before inserting.
• No reconciliation process: Over time, bugs and edge cases can drift your balance projection from the true ledger sum. Run a daily reconciliation job that recalculates balance from scratch and alerts if there's a discrepancy.
• Concurrent access without locks: FOR UPDATE in PostgreSQL or equivalent locking is non-negotiable. Without it, you will eventually oversell.

Key takeaways

• Wallets and stored balance systems must be built on immutable ledger entries, not mutable balance fields. Every credit, debit, hold, and release is a separate record.
• Implement a hold-then-confirm flow to prevent overselling. Reserve funds when an order is placed; debit only when it's confirmed. Release holds if the order is cancelled.
• Use row-level locking (e.g., FOR UPDATE) when checking and modifying balances in a single transaction. This prevents race conditions under concurrent load.
• Make your ledger entries idempotent by storing a unique external reference (Stripe charge ID, refund ID, etc.). Check for duplicates before inserting.
• Stored value systems have compliance obligations. Enforce expiration dates, maintain audit trails, segregate fund types, and be ready to produce regulatory reports at any time.
• Materialized balance views (wallet_balances table) are useful for performance, but never the source of truth. Recalculate from the ledger daily to catch drift.