Keep credits in a ledger instead of a balance you overwrite

How to do it

A balance column that two requests update at once loses one of the changes, and nobody can say where the credits went. A ledger keeps the history. Give each row the customer, a signed amount, the type, the source ID and the time. Put a unique constraint on the source ID, such as the payment, the usage event or the grant, so a retry never books twice. Corrections are new rows. A balance column may serve as a fast cache, updated in the same transaction as the row. Rebuild it from the rows every night. The pattern is the same as Record every money movement in an append-only ledger.

Examples

SQL example

CREATE TABLE credit_entries (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  text NOT NULL,
  entry_type   text NOT NULL CHECK (entry_type IN ('grant', 'purchase', 'hold', 'release', 'use', 'refund', 'expiry', 'adjustment')),
  credits      bigint NOT NULL,
  source_id    text NOT NULL UNIQUE,
  grant_id     bigint REFERENCES credit_entries (id),
  expires_at   timestamptz,
  created_at   timestamptz NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT ON credit_entries TO app_role;

Python example

def credit_balance(db, customer_id: str) -> int:
    row = db.execute(
        "SELECT coalesce(sum(credits), 0) FROM credit_entries WHERE customer_id = %s",
        (customer_id,),
    ).fetchone()
    return row[0]

JavaScript example

export async function creditBalance(db, customerId) {
  const { rows } = await db.query(
    'SELECT coalesce(sum(credits), 0) AS balance FROM credit_entries WHERE customer_id = $1',
    [customerId],
  )
  return Number(rows[0].balance)
}

How to check

As the app role, run an UPDATE and a DELETE on the credit ledger. Both fail with permission denied. A nightly job compares each cached balance with the sum of its rows and alerts on any difference.

Sources

• Stripe customer balance as a ledger of transactions

Related rules

• PAY-21 Record every money movement in an append-only ledger in Payments and billing