Deriving a Loyalty Wallet Balance with a Database Trigger

The problem with application-written balances

A loyalty wallet usually needs two views of the same financial state:

• An append-only ledger containing every credit and debit.
• A current balance that can be read quickly for checkout, rewards, or account screens.

The tempting implementation is to insert a ledger row in the application and then update wallet.balance in a second query. We avoid that pattern when the balance is derived entirely from ledger movements.

The application approach has several failure modes:

• The ledger insert succeeds but the process crashes before updating the balance.
• A retry repeats one part of the workflow and creates a double credit.
• Two requests read the same balance and both write a calculated replacement value.
• Different services implement balance rules differently over time.

We prefer to make the database responsible for maintaining the denormalized balance because the database already owns the transaction containing the ledger entry.

Our model: ledger first, balance derived

We treat wallet_ledger as the authoritative record. Each row represents a signed movement:

• Positive amount values are credits.
• Negative amount values are debits.
• A wallet balance is the sum of its posted ledger movements.

The wallets.balance column is therefore a materialized value. It exists for read performance, not as an independently editable source of truth.

A simplified PostgreSQL schema looks like this:

create table wallets (
  id uuid primary key,
  balance bigint not null default 0,
  updated_at timestamptz not null default now()
);

create table wallet_ledger (
  id uuid primary key,
  wallet_id uuid not null references wallets(id),
  amount bigint not null check (amount <> 0),
  status text not null check (status in ('posted', 'voided')),
  idempotency_key text not null,
  created_at timestamptz not null default now(),
  unique (wallet_id, idempotency_key)
);

We use integer minor units rather than floating-point values. For a points-based loyalty wallet, the unit may simply be points. For money-like values, the unit should be the smallest supported denomination.

Updating the balance in a database trigger

The trigger runs in the same transaction as the ledger insert. If the balance update fails, the ledger insert fails too. If the transaction rolls back, neither change remains.

create function apply_wallet_ledger_entry()
returns trigger
language plpgsql
as $$
begin
  if new.status = 'posted' then
    update wallets
    set balance = balance + new.amount,
        updated_at = now()
    where id = new.wallet_id;

    if not found then
      raise exception 'wallet % does not exist', new.wallet_id;
    end if;
  end if;

  return new;
end;
$$;

create trigger wallet_ledger_after_insert
after insert on wallet_ledger
for each row
execute function apply_wallet_ledger_entry();

We use balance = balance + new.amount, not a balance calculated in the application. This matters for concurrency. The update obtains a row lock on the wallet row, and PostgreSQL applies each increment against the most recently committed version of that row.

If two posted credits arrive for the same wallet at nearly the same time, the final balance includes both movements. We do not have two application processes overwriting each other with stale calculated balances.

A trigger does not prevent double credit by itself

A database trigger makes the ledger-to-balance update atomic. It does not determine whether a ledger entry should have been inserted once or twice.

For example, a payment provider may retry a webhook after a timeout. If our application inserts two distinct credit entries, the trigger correctly applies both. That is still a double credit from the business perspective.

We address this with an idempotency key and a unique constraint. The key should identify the external event or business operation that is allowed to create one movement.

insert into wallet_ledger (
  id,
  wallet_id,
  amount,
  status,
  idempotency_key
)
values (
  gen_random_uuid(),
  $1,
  $2,
  'posted',
  $3
)
on conflict (wallet_id, idempotency_key) do nothing;

The application must inspect whether the insert created a row. We do not silently assume that a retry succeeded merely because the SQL statement completed.

We also decide idempotency scope deliberately. A provider event ID is often a better key than an HTTP request ID because multiple HTTP requests can represent the same provider event.

Debits and insufficient funds

For a loyalty wallet where balances must never become negative, the trigger can enforce that rule while it holds the wallet row lock.

create function apply_wallet_ledger_entry()
returns trigger
language plpgsql
as $$
declare
  resulting_balance bigint;
begin
  if new.status = 'posted' then
    update wallets
    set balance = balance + new.amount,
        updated_at = now()
    where id = new.wallet_id
    returning balance into resulting_balance;

    if not found then
      raise exception 'wallet % does not exist', new.wallet_id;
    end if;

    if resulting_balance < 0 then
      raise exception 'insufficient wallet balance';
    end if;
  end if;

  return new;
end;
$$;

The exception rolls back the ledger insert and the balance update together. This is safer than first reading a balance in the application, checking it, and then writing a debit later. That split workflow can approve two concurrent debits against the same available points.

Whether negative balances are prohibited is a product rule. Some wallets allow them for adjustments or credit limits. We should not encode a universal rule in the trigger without making that policy explicit.

Handling reversals instead of editing history

We avoid changing the amount of a posted ledger row after it has affected a balance. Updating or deleting posted records makes audit trails and reconciliation harder.

Instead, we create a compensating ledger entry:

• To reverse a credit of 100, insert a posted debit of -100.
• To reverse a debit of -100, insert a posted credit of 100.
• Link the reversal to the original entry with a reference column if traceability is needed.

This keeps the ledger append-only and allows us to rebuild the balance if we suspect an operational error.

If the system needs a pending state, we decide whether pending rows affect the available balance. In the example trigger, only posted rows do. Moving a row from pending to posted requires an AFTER UPDATE trigger or a separate posting procedure. We do not add that behavior accidentally, because status transitions need their own idempotency and authorization rules.

Reconciliation remains necessary

A database trigger reduces drift, but it does not remove the need to verify derived data. Bugs in migrations, manual database changes, disabled triggers, and historical imports can still produce mismatches.

We keep a reconciliation query that compares the stored balance with the ledger sum:

select
  w.id,
  w.balance as stored_balance,
  coalesce(sum(l.amount) filter (where l.status = 'posted'), 0) as ledger_balance
from wallets w
left join wallet_ledger l on l.wallet_id = w.id
group by w.id, w.balance
having w.balance <> coalesce(sum(l.amount) filter (where l.status = 'posted'), 0);

For a repair, we prefer to understand the cause first. If we intentionally rebuild balances, we perform it in a controlled maintenance operation and preserve evidence of the original mismatch.

Trade-offs we accept

A database trigger puts important business behavior below the application layer. That makes it easier for every writer to preserve the invariant, including scripts and future services. It also makes the behavior less visible to developers who only inspect application code.

We accept that trade-off when these conditions hold:

• The balance is strictly derived from a ledger.
• Multiple code paths can create ledger entries.
• Correctness under concurrency matters more than keeping all logic in one service.
• The team documents, tests, and monitors the trigger as production code.

We keep the trigger narrow. It applies a validated ledger movement to one wallet balance. Idempotency, authorization, reward eligibility, and external side effects remain explicit application or service concerns.

The trigger protects the consistency between a ledger row and its derived balance. It does not replace a well-designed ledger workflow.

That boundary gives us a loyalty wallet that can be read efficiently while retaining an auditable source of truth and a transactional answer to concurrency.