If you allow users to store balances (e.g., in virtual wallets, gift cards, or promotional credits), you are running a miniature bank. Simply updating a balance column on a user table is not sufficient. If a database transaction fails halfway or a column is edited directly, there is no way to audit what went wrong.

Financial ledger systems require Double-Entry Bookkeeping. Balances are never updated directly; instead, they are calculated by aggregating an immutable stream of credit and debit transactions.

1. Designing the Ledger Schema

A minimal ledger schema consists of two tables: accounts (defining wallet type and owner) and ledger_entries (storing transactions):

CREATE TABLE ledger_entries (
    id UUID PRIMARY KEY,
    wallet_id UUID REFERENCES wallets(id),
    amount INT NOT NULL, -- Stored in cents (positive for credit, negative for debit)
    transaction_type VARCHAR(30) NOT NULL, -- e.g., PURCHASE, REFUND, ADJUSTMENT
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

2. Atomic Balance Updates

To calculate a wallet's current balance, we sum the entries. To optimize performance, we maintain a cached balance table, but the source of truth is always the sum of the ledger entries. We wrap entry insertion and cached balance updates in an atomic database transaction:

func (l *LedgerRepository) PostTransaction(ctx context.Context, walletID uuid.UUID, amount int, txType string) error {
    tx, err := l.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()
    
    // 1. Insert immutable entry
    _, err = tx.ExecContext(ctx, 
        "INSERT INTO ledger_entries (id, wallet_id, amount, transaction_type) VALUES ($1, $2, $3, $4)",
        uuid.New(), walletID, amount, txType)
    if err != nil {
        return err
    }
    
    // 2. Update cached balance
    _, err = tx.ExecContext(ctx,
        "UPDATE wallets SET balance = balance + $1 WHERE id = $2",
        amount, walletID)
    if err != nil {
        return err
    }
    
    return tx.Commit()
}

This design makes auditing balance changes straightforward and prevents money from "appearing" or "disappearing" due to application errors.