Designing a ledger: balances should be derived from transactions

Short answer: don't store a wallet's balance as the truth. Store every movement of money as an append-only ledger entry and treat the balance as the result of summing those entries. A transfer is two entries that net to zero, a mistake is fixed with a reversing entry, and any balance can be rebuilt and explained from history. A cached balance column is fine as an optimisation, as long as it can be thrown away and recomputed.

A real example: "why is my balance 20?"

A user writes to support: "My balance should be 120, the app says 20." With a single Balance column, the only honest answer is "the database says 20". Something decremented it, at some point, for some reason, but the column doesn't remember.

With a ledger, support runs one query and reads the story: deposit 100, referral credit 20, withdrawal 80, withdrawal 80 a second later with the same gateway reference. The duplicate is visible, the fix is a reversing entry, and the conversation takes minutes instead of days.

That's the real reason to build a ledger. It isn't elegance. It's being able to answer "why?" about money.

The rules

1. Entries are append-only. No UPDATE, no DELETE. Corrections are new entries.
2. Every business operation has an id (TransactionId) shared by all its entries.
3. Transfers net to zero. Money moves between accounts; it is never created or destroyed by a transfer.
4. The balance is a query, not a column you have to remember to update.
5. Amounts are integers in the smallest unit (Rials), signed: credits positive, debits negative.

Runnable code

EF Core with SQL Server or SQLite:

using System.Data;
using Microsoft.EntityFrameworkCore;

public enum EntryType
{
    Deposit,
    Withdrawal,
    TransferIn,
    TransferOut,
}

public sealed class LedgerEntry
{
    public long Id { get; set; }
    public Guid AccountId { get; set; }

    /// <summary>Signed: credits are positive, debits negative.</summary>
    public long AmountRials { get; set; }

    public EntryType Type { get; set; }

    /// <summary>Groups the entries of one business operation (both legs of a transfer).</summary>
    public Guid TransactionId { get; set; }

    public DateTime CreatedAt { get; set; }
}

public sealed class LedgerDb(DbContextOptions<LedgerDb> options) : DbContext(options)
{
    public DbSet<LedgerEntry> Entries => Set<LedgerEntry>();

    protected override void OnModelCreating(ModelBuilder modelBuilder) =>
        modelBuilder.Entity<LedgerEntry>().HasIndex(e => new { e.AccountId, e.Id });
}

public sealed class LedgerService(LedgerDb db)
{
    public Task<long> BalanceAsync(Guid accountId, CancellationToken ct) =>
        db.Entries.Where(e => e.AccountId == accountId).SumAsync(e => e.AmountRials, ct);

    public async Task DepositAsync(Guid accountId, long amount, Guid transactionId, CancellationToken ct)
    {
        ArgumentOutOfRangeException.ThrowIfNegativeOrZero(amount);
        db.Entries.Add(Entry(accountId, amount, EntryType.Deposit, transactionId));
        await db.SaveChangesAsync(ct);
    }

    public async Task TransferAsync(Guid from, Guid to, long amount, Guid transactionId, CancellationToken ct)
    {
        ArgumentOutOfRangeException.ThrowIfNegativeOrZero(amount);

        // Serializable: no other transaction can add entries for "from" between our check and our insert.
        await using var transaction = await db.Database.BeginTransactionAsync(IsolationLevel.Serializable, ct);

        if (await BalanceAsync(from, ct) < amount)
        {
            throw new InvalidOperationException("Insufficient funds.");
        }

        db.Entries.AddRange(
            Entry(from, -amount, EntryType.TransferOut, transactionId),
            Entry(to, amount, EntryType.TransferIn, transactionId));

        await db.SaveChangesAsync(ct);
        await transaction.CommitAsync(ct);
    }

    /// <summary>Transfers that don't net to zero. If this returns anything, stop and investigate.</summary>
    public async Task<IReadOnlyList<Guid>> UnbalancedTransfersAsync(CancellationToken ct) =>
        await db.Entries
            .Where(e => e.Type == EntryType.TransferIn || e.Type == EntryType.TransferOut)
            .GroupBy(e => e.TransactionId)
            .Where(g => g.Sum(e => e.AmountRials) != 0)
            .Select(g => g.Key)
            .ToListAsync(ct);

    private static LedgerEntry Entry(Guid account, long amount, EntryType type, Guid transactionId) => new()
    {
        AccountId = account,
        AmountRials = amount,
        Type = type,
        TransactionId = transactionId,
        CreatedAt = DateTime.UtcNow,
    };
}

What matters here:

• BalanceAsync is the only definition of a balance. No other code path can disagree with it.
• The funds check and the insert share a serializable transaction. Without it, two concurrent transfers could both pass the check: the same race you get with a balance column, just in a different place.
• UnbalancedTransfersAsync is an invariant you can monitor. Run it nightly. It should always return an empty list.
• TransactionId comes from the caller. Make it the request's idempotency key and a retried transfer can't post twice; add a unique index on (TransactionId, AccountId, Type).

When summing gets slow: snapshots

An account with years of entries makes SUM heavier. The usual fix is a snapshot table: "account X had balance B after entry N". The balance becomes B + SUM(entries after N). The snapshot is still derived. You can delete it and rebuild it from the entries, which is what keeps the ledger the single source of truth.

Pros and cons

Pros

• Every balance is explainable and reproducible, which is the difference between a five-minute support answer and a three-day investigation.
• Corrections are visible history, not silent overwrites.
• Reconciliation with banks and gateways becomes a comparison of two lists of entries.

Cons

• More rows and more thought than one column. Reporting needs indexes and, eventually, snapshots.
• Every write path must go through the ledger service; a single "quick fix" UPDATE breaks the model.
• Concurrency still matters: the funds check needs the right isolation or lock.

Designing a wallet, a points system or anything else where a balance has to be explained? See how I can help.