A Loyalty Points System Customers Trust: Design and Test

A loyalty points system customers trust is built on an append-only ledger, not an editable balance. Every earn, redeem, expiry and adjustment is one immutable entry with a signed amount, and the balance is the sum of the entries. That makes the balance defensible when a customer disputes it, stops two tabs spending the same points, and makes expiry a scheduled entry instead of a number someone quietly overwrites.

If you would rather have it built for you, see how RAITHub would build this below.

Why should a points balance never be a single editable number?

Because the moment a customer says "I had more points than this", a stored balance cannot tell you what happened. An append-only ledger can: it shows every point earned and spent, when and why. It also prevents the two bugs that erode trust fastest. The first is drift, where earning and redeeming happen in different code paths and one forgets to update the balance. The second is double-spend, where two requests read the same balance and both redeem it. A ledger whose balance is the sum of its rows cannot drift, and a row lock at redemption time cannot double-spend.

Entry type	Sign	When it is written
earn	+	When an order completes (not when it is placed, or a cancellation gives free points)
redeem	-	At checkout, inside the order transaction, after a balance check with a lock
expiry	-	By a scheduled job, when points pass their expiry date
reversal	-	When an order that earned points is returned or cancelled
adjustment	+ or -	A manual correction by staff, with a reason and an actor

What does the ledger look like?

CREATE TABLE points_entries (
  id          bigserial PRIMARY KEY,
  customer_id bigint      NOT NULL REFERENCES customers(id),
  amount      integer     NOT NULL,            -- signed: +earn, -redeem, -expiry
  type        text        NOT NULL CHECK (type IN ('earn','redeem','expiry','reversal','adjustment')),
  order_id    bigint      REFERENCES orders(id),
  expires_at  timestamptz,                     -- set on earn entries only
  actor       text        NOT NULL,            -- customer, a system job, or a staff user
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX points_by_customer ON points_entries (customer_id, created_at);

-- Balance: always the sum of the rows, never a stored column.
-- SELECT COALESCE(SUM(amount), 0) FROM points_entries WHERE customer_id = $1;

This is the same shape as a store-credit ledger, and for the same reasons; if your store also issues store credit on returns, the two can share the pattern, covered in a returns system that does not lose money.

How do earning and burning work as rules?

Keep both as explicit rules the customer can understand, and compute them on the server:

• Earning: award points when the order is actually completed, not when it is placed, or a customer who places and cancels orders farms points. Base the earn on the amount paid after discounts, so a fully discounted order does not mint points, and reverse the earn if the order is later returned.
• Burning: fix the redemption value (for example a number of points per unit of currency) and the minimum and maximum a customer can redeem per order. Decide whether points can pay tax and shipping or only goods, and apply that rule on the server.

Because earning depends on the paid amount and redeeming reduces it, settle the order money first, then write the earn, so points are never awarded on a value the customer never paid. Server-side pricing is the same rule as in the coupon and promotions engine.

How do you stop points being spent twice?

Lock the customer's points before redeeming, check the balance, then insert the negative entry inside the order transaction. Two concurrent checkouts cannot both pass the check, because the first holds the lock until it commits.

// Redeem points inside the order transaction. The advisory lock (or a
// SELECT ... FOR UPDATE on the customer row) serialises a customer's redemptions.
export async function redeemPoints(tx: Tx, customerId: number, points: number, orderId: number) {
  await tx.query('SELECT pg_advisory_xact_lock($1)', [customerId])          // one redemption at a time
  const { rows } = await tx.query(
    'SELECT COALESCE(SUM(amount), 0) AS balance FROM points_entries WHERE customer_id = $1',
    [customerId],
  )
  if (Number(rows[0].balance) < points) throw new Error('Not enough points')
  await tx.query(
    "INSERT INTO points_entries (customer_id, amount, type, order_id, actor) VALUES ($1, $2, 'redeem', $3, 'customer')",
    [customerId, -points, orderId],
  )
}

This is the same concurrency discipline as stock and store credit: the check and the write happen together, under a lock, so there is no window between them. The general pattern is in preventing overselling inside the order transaction.

How should points expire?

Set an expires_at on each earn entry, and run a scheduled job that writes a negative expiry entry for points past their date that have not already been spent. Expiring oldest-first (the earn entries closest to their date go first) is the fair and common rule, and it means a redemption today consumes the points nearest to expiring. Never expire points by editing a balance; write an entry, so the history still adds up and a customer can see exactly what expired and when.

How do you test a points system?

import { describe, it, expect } from 'vitest'

describe('points ledger', () => {
  it('balance equals the sum of entries after earn, redeem and expiry', async () => {
    await earn(c, 100); await redeem(c, 30); await expire(c, 20)
    expect(await balance(c)).toBe(50)
  })

  it('never lets two concurrent redemptions overspend', async () => {
    await earn(c, 100)
    const results = await Promise.allSettled([redeem(c, 80), redeem(c, 80)])
    const ok = results.filter((r) => r.status === 'fulfilled').length
    expect(ok).toBe(1)                 // only one 80-point redemption can succeed
    expect(await balance(c)).toBe(20)
  })

  it('reverses the earn when the order is returned', async () => {
    const order = await completeOrder(c, { paid: 100 })   // earns 100
    await returnOrder(order)
    expect(await balance(c)).toBe(0)   // the earn is reversed
  })
})

The concurrency test is the one that matters: a balance that is correct single-threaded can still be overspent under load. Run it against a real database, not a mock.

Buy, build or hire?

Option	Choose this when	The catch
A loyalty app (Smile.io, LoyaltyLion and similar)	A standard hosted store wanting points, tiers and referrals out of the box	Bespoke earn and burn rules, local payments or tying points into your own checkout often need custom work
A simple earn-only scheme	You just want to reward spend with no redemption yet	Adding redemption later means retrofitting the ledger and concurrency you skipped
Custom build	Points interact with your own pricing, store credit and checkout, and must be auditable	You own the ledger and the tests that keep the balance honest

How long does it take to build yourself, and what is the risk?

For an experienced backend developer adding a points system to an existing store, our estimate is 2 to 4 weeks: the ledger, earn and burn rules, expiry, reversal and the test suite. The main risk is double-spend under load, which looks fine until a popular reward launches and two tabs redeem at once; write the concurrency test before you trust the balance. The second risk is awarding points at order placement rather than completion, which lets a customer farm points by placing and cancelling.

Why RAITHub for this

• Ledgers in production. Sundor Skin, a B2B wholesale platform RAITHub built, runs credit and adjustments as append-only entries under row-level security, across 146 PostgreSQL tables and 530+ tests. See the Sundor Skin case study.
• Concurrency is tested. Redemptions get race tests against a real database, gated in CI, so a reward launch cannot overspend a balance.
• Server-side value. On TheSkinProof, the founder's own venture built and run by RAITHub, money and balances change inside row-locked transactions across 217 endpoints and 750+ tests.

When you don't need us

• A loyalty app fits. If a hosted points app covers your rules, configure it.
• Earn-only, for now. A simple rewards scheme with no redemption does not need a ledger yet.
• You only need the ledger. The model above is a fair start for your own developer.

How RAITHub would build this

• Points ledger: append-only entries, balance by sum, earn on completion, reversal on return.
• Earn and burn rules: server-side earning on the paid amount, fixed redemption value with limits, clear rules for tax and shipping.
• Expiry: per-entry expiry dates and a scheduled job that writes expiry entries oldest-first.
• Concurrency and tests: locked redemption inside the order transaction, with unit and race tests gated in CI.

Timeline: adding a points system to an existing store is backend work, typically 6 to 12 weeks; inside a new store MVP a focused version fits the 4 to 6 week fixed-scope range. See API and backend development, SaaS development and the ecommerce industry page.

You receive: automated tests and CI including concurrency tests for redemption, handover docs, and full IP in your name under NDA.

Next step: book the free 15-minute technical audit with your earn and burn rules, and we will follow up with a written fixed quote.

Frequently asked questions

How should a loyalty points balance be stored?

As an append-only ledger of signed entries: earn, redeem, expiry, reversal and adjustment. The balance is the sum of the rows. Never keep a single editable balance, because it drifts and cannot explain itself in a dispute.

When should points be awarded?

When the order is completed, not when it is placed, and on the amount paid after discounts. Reverse the earn if the order is later returned. Awarding at placement lets customers farm points by placing and cancelling.

How do I stop points being spent twice?

Lock the customer's points, check the balance, and insert the redemption entry inside the order transaction. The first redemption holds the lock until it commits, so two concurrent checkouts cannot both spend the same points.

How should points expire?

Give each earn entry an expiry date and run a scheduled job that writes a negative expiry entry for points past their date that were not spent. Expire oldest-first, and never by editing a balance, so the history still adds up.

Can points pay for tax and shipping?

That is your decision, but make it explicit and enforce it on the server. Many stores let points pay for goods only. Whatever you choose, apply the rule server-side so a tampered request cannot redeem against amounts you excluded.

Can I use a loyalty app instead of building one?

Often yes. Hosted loyalty apps cover points, tiers and referrals for standard stores. Build custom when points interact with your own pricing, store credit and checkout, or must be fully auditable.