I Built a Coin Ledger That Guilts Customers Into Coming Back 🧇

A few months ago I sat down to add "loyalty points" to Glazed, my waffle kiosk brand. Simple, right? Add a points column to the users table, increment it on checkout, done by lunch.

Three weeks and six tables later, I had something that looks less like a points system and more like a tiny accounting engine. 🧾

Here's why it ended up that way — and why I think every "simple" points feature secretly wants to be a ledger.

The Problem We Were Actually Solving

Glazed runs on Next.js + Supabase. Ordering, payments, menu, analytics — all solid. Loyalty was the missing piece, but the real problem wasn't "give people points."

It was: how do we get someone who bought one waffle to come back for a second one?

That's a retention problem, not a points problem. And retention problems have a dirty little secret — they respond way better to loss than to gain.

"You've earned 10 coins!" is a shrug. "Your ₹47 disappears in 7 days" is a reason to open WhatsApp at 11pm and order a waffle you didn't need. 🧇

So before writing a line of SQL, I locked in four rules:

1. Coins are money, not points. ₹1 spent = 1 coin, 1 coin = ₹1. No mental math for the customer.
2. Loss beats gain. Every message is framed around what's about to vanish, not what could be earned.
3. The ledger is the truth. The cached balance is just a cache. If it disagrees with the transaction log, the log wins. Always.
4. Rolling expiry, not batch expiry. No "all coins expire Dec 31." Every earn event gets its own 21-day countdown, so there's always something about to expire for somebody.

That last point is the one that shaped the entire schema.

Why an Append-Only Ledger (And Not Just a Balance Column)

My first instinct was a single current_balance integer on the user row. Increment on earn, decrement on redeem. It would've worked for about a week, until the first support ticket: "I had 80 coins yesterday, now I have 12, what happened?"

With a single mutable number, the answer is "I have no idea." There's no history, no audit trail, no way to reconstruct what happened. So the balance became a cache, and the real system became a coin_ledger table where rows are never updated or deleted — only inserted.

CREATE TYPE coin_event_type AS ENUM (
  'earn', 'redeem', 'expire', 'bonus', 'tier_upgrade', 'admin_adjust'
);

CREATE TABLE public.coin_ledger (
  id                uuid            PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id           uuid            NOT NULL REFERENCES users(id),
  order_id          uuid            REFERENCES orders(id),
  event_type        coin_event_type NOT NULL,
  coins             numeric         NOT NULL,
  expiry_at         timestamptz,
  source_ledger_id  uuid            REFERENCES coin_ledger(id),
  balance_after     numeric         NOT NULL,
  note              text,
  created_at        timestamptz     NOT NULL DEFAULT now(),

  CONSTRAINT earn_and_bonus_must_have_expiry CHECK (
    event_type NOT IN ('earn', 'bonus') OR expiry_at IS NOT NULL
  )
);

A few decisions worth calling out:

• coins is signed. Earn and bonus are positive, redeem and expire are negative. Summing the column gives you the balance — for free.
• source_ledger_id links a redeem or expire row back to the earn row it consumed. Full traceability: every coin that ever leaves an account can be traced to the exact order it was earned from.
• balance_after is a snapshot taken at insert time, mostly there to make debugging painless.
• The CHECK constraint physically prevents anyone from inserting an earn row without an expiry date. The database enforces the business rule, not just the app code.

Now that support ticket becomes a one-line query: filter coin_ledger by user_id, and you get a complete, chronological, tamper-evident story of every coin that's ever touched that account. Refunds, disputes, "where did my coins go" — all answerable without guessing.

Rolling Expiry: The PostgreSQL View Doing the Heavy Lifting

Batch expiry ("everything expires Dec 31") is simpler to build, but it's bad psychology. Most of the year, nothing is at stake, so there's no urgency. Then on Dec 31 everyone scrambles at once — or doesn't, and you've just burned a pile of goodwill for nothing.

Rolling expiry fixes this: every earn row carries its own expiry_at, set to 21 days from the order. That means on any given day, someone is close to losing coins, which means the "your coins expire soon" message is always true for a meaningful slice of customers — not a once-a-year blast.

The catch is that "soon" now means computing per-user expiry windows on the fly, across potentially thousands of ledger rows. That's where this view comes in:

CREATE VIEW public.coins_expiring_soon AS
SELECT
  cl.user_id,
  u.phone_number,
  u.name,
  SUM(cl.coins) FILTER (WHERE cl.expiry_at <= now() + interval '14 days') AS coins_expiring_14d,
  SUM(cl.coins) FILTER (WHERE cl.expiry_at <= now() + interval '7 days')  AS coins_expiring_7d,
  SUM(cl.coins) FILTER (WHERE cl.expiry_at <= now() + interval '1 day')   AS coins_expiring_1d,
  MIN(cl.expiry_at) AS earliest_expiry
FROM coin_ledger cl
JOIN users u ON u.id = cl.user_id
WHERE cl.event_type = 'earn'
  AND cl.expiry_at > now()
  AND cl.coins > 0
GROUP BY cl.user_id, u.phone_number, u.name;

The FILTER clause is doing the trick here. ✨ Without it, getting three different time-window aggregates (14-day, 7-day, 1-day) would mean three separate queries — or one query with three CASE WHEN expressions buried inside SUM(), which works but reads badly. FILTER lets you say "sum this column, but only for rows matching this condition" right next to the aggregate, in plain SQL, in a single pass over the table.

It's a regular view, not materialized, on purpose. The dataset is a few thousand users, not a few million, so recomputing it live every time the cron runs costs nothing — and it guarantees the cron is always reading the current truth, never a stale snapshot.

To keep this cheap as the ledger grows, I added two partial indexes scoped to earn rows only, since that's the only event type these expiry queries ever touch:

CREATE INDEX idx_coin_ledger_expiry      ON coin_ledger(expiry_at) WHERE event_type = 'earn';
CREATE INDEX idx_coin_ledger_user_expiry ON coin_ledger(user_id, expiry_at) WHERE event_type = 'earn';

redeem, expire, and admin_adjust rows never show up in these indexes at all, so they stay small no matter how large the ledger gets.

The Cron Job: One Route, Four Jobs

Everything ties together in a single daily cron route, hit by an external scheduler (cron-job.org) once a day. Conceptually it does four things in order: expire old coins, find coins about to expire and warn people, find inactive users and nudge them, then fan all those messages out over WhatsApp.

The tricky part is that this is a Vercel serverless function, and serverless functions don't love long-running background work. cron-job.org expects a fast response, but expiring coins for thousands of rows and sending hundreds of messages is not fast.

The fix is Next.js's after():

export async function GET(req: NextRequest) {
  const authHeader = req.headers.get("authorization");
  const cronSecret = process.env.CRON_SECRET;

  if (cronSecret && authHeader !== `Bearer ${cronSecret}`) {
    return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  }

  // Return 200 immediately, process in background
  after(async () => {
    // steps 1-4 happen here
  });

  return NextResponse.json({ success: true, message: "Cron job triggered" });
}

after() lets the function return its 200 response right away — so the cron scheduler is satisfied and moves on — while the actual work keeps executing in the background on Vercel's infrastructure. The four steps inside it:

1. Expire old coins. Find every earn row where expiry_at has passed and coins > 0 (meaning it hasn't been fully consumed by a redemption), and insert a matching negative expire row.
2. Query coins_expiring_soon and build a list of who needs a 7-day warning.
3. Query inactive users — no earn activity in 10+ days, with a non-zero balance — for a re-engagement nudge.
4. Queue everything through QStash, chunked into batches of 20.

Notice step 1 never deletes or updates the original earn row. It just adds an expire row that cancels it out in the sum. The ledger stays append-only even when coins die of old age.

The Dedup Trick: One Upsert, Zero Duplicate Messages

Here's the part I'm most pleased with. 😌

Cron jobs that send messages have an obvious failure mode: what if the job runs twice, or retries after a partial failure? Without protection, customers get the same "your coins are expiring" text three times and start tuning out your brand entirely.

The naive fix is "check if we already sent this, then send if not" — a read, then a write, with a race condition sitting right between them. Two cron invocations close together can both read "not sent yet" and both send.

Instead, there's an idempotency table:

CREATE TABLE public.whatsapp_trigger_log (
  id            uuid                  PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       uuid                  NOT NULL REFERENCES users(id),
  trigger_type  whatsapp_trigger_type NOT NULL,
  dedup_key     text                  NOT NULL UNIQUE,
  sent_at       timestamptz           NOT NULL DEFAULT now(),
  metadata      jsonb
);

And the whole dedup check becomes one batch upsert:

const { data: insertedLogs } = await supabase
  .from("whatsapp_trigger_log")
  .upsert(warningsToLog, {
    onConflict: "dedup_key",
    ignoreDuplicates: true,
  })
  .select("dedup_key");

// Only messages that were ACTUALLY inserted (not duplicates) get sent
if (insertedLogs) {
  for (const log of insertedLogs) {
    const msg = warningMap.get(log.dedup_key);
    if (msg) {
      messagesToQueue.push(msg);
    }
  }
}

upsert with ignoreDuplicates: true tries to insert every row in the batch, and silently skips any that collide on the unique dedup_key. It then returns only the rows that actually got inserted. Cross-reference those returned keys against the original message map, and you've got your "definitely new, definitely send these" list — computed in a single round trip to the database, no race condition possible, no separate read-then-write.

The dedup_key design is the other half of the trick. Each trigger type builds its key so that "duplicate" means exactly what you want it to mean:

The database's unique constraint is doing all the deduplication work. No application-level locking, no Redis, no extra infrastructure.

Why QStash Instead of Just Promise.all

For the actual sending, the obvious move is Promise.all over every message and call the WhatsApp gateway directly. I do exactly that — as a fallback. But it's not the primary path, for three reasons.

First, the after() callback still runs inside a Vercel function with a bounded execution window. If a slow day produces 300 expiry warnings, firing 300 concurrent HTTP requests and waiting on all of them risks running past that window — and unlike a normal request, there's no client waiting to retry for you.

Second, plain Promise.all has no retry story. If the WhatsApp gateway hiccups on request #47, that message is just gone.

Third, hammering a third-party API with 300 simultaneous requests is a great way to get rate-limited or banned.

So messages get chunked into batches of 20 and pushed onto Upstash QStash instead:

const chunkSize = 20;
const qstashMessages = [];

for (let i = 0; i < messagesToQueue.length; i += chunkSize) {
  const chunk = messagesToQueue.slice(i, i + chunkSize);
  qstashMessages.push({
    url: workerUrl,
    body: { messages: chunk.map((m) => ({
      phoneNumber: m.phoneNumber,
      firstName: m.firstName,
      coins: m.coins,
      days: m.days,
    })) },
    headers: { "Content-Type": "application/json" },
  });
}

await qstash.batchJSON(qstashMessages);

QStash takes over the delivery problem entirely: it retries failed deliveries automatically, and the batches naturally throttle how hard the WhatsApp gateway gets hit. The cron route's job shrinks down to "figure out who needs a message" — the actual sending becomes someone else's problem, with retries built in.

The Worker Route: Where Signature Verification Earns Its Keep

QStash delivers each batch by POSTing it to a worker route, which is just a normal Next.js API route — except it can't be a normal open API route, because anyone who finds that URL could spam your entire customer list with WhatsApp messages on your dime.

// app/api/admin/loyalty/cron/worker/route.ts
import { verifySignatureAppRouter } from "@upstash/qstash/dist/nextjs";

async function handler(request: Request) {
  const { messages } = await request.json();

  const results = await Promise.all(
    messages.map(async (msg) => {
      try {
        await axios.get(buildWhatsAppUrl(msg));
        return { phoneNumber: msg.phoneNumber, status: "success" };
      } catch (waErr: any) {
        return { phoneNumber: msg.phoneNumber, status: "failed", error: waErr.message };
      }
    }),
  );

  return NextResponse.json({ success: true, processed: messages.length, results });
}

export const POST = verifySignatureAppRouter(handler);

verifySignatureAppRouter wraps the handler and checks a cryptographic signature that QStash attaches to every request it makes, using a shared signing key. If the signature doesn't check out, the request never reaches the handler. So even if someone discovers the worker URL, they can't trigger it — only QStash, holding the matching key, can.

Inside the worker itself, Promise.all is exactly the right tool — this isn't the unbounded fan-out from before, it's a pre-sized batch of 20 that QStash handed over specifically because it's a safe size to run concurrently.

FIFO Redemption: Spend the Oldest Coins First

When a customer redeems coins at checkout, which coins actually get spent? With rolling expiry, this isn't cosmetic — it changes who's at risk of losing coins next.

The redemption logic always consumes the oldest earn rows first (FIFO), tracked through source_ledger_id:

calculateMaxRedeemable(orderTotal: number, currentBalance: number, config: LoyaltyConfig): number {
  if (currentBalance < 10) return 0;  // minimum 10 coins to redeem

  const maxByPct = orderTotal * config.max_redemption_pct;       // 20% of order
  const maxRedeemable = Math.min(config.max_redemption_amount, maxByPct);  // lower of ₹50 or 20%

  return Math.min(Math.floor(maxRedeemable), currentBalance);
}

FIFO does double duty here. It maximizes the chance that newer coins — the ones less likely to have been spent down yet — are the ones left sitting around close to expiry, which keeps the "your coins expire soon" messaging honest and frequent. And from a cash-flow angle, it defers the liability: the coins still on the books skew newer, further from their redemption deadline.

The Business Case: Breakage Is the Whole Point

This is the part that makes the loss-framing decision make sense as a business choice, not just a UX trick.

That's the cost if every coin gets redeemed. But the industry-standard breakage rate — coins earned and never redeemed before they expire — sits around 40-50%. Factor that in, and the real effective cost lands closer to 60% of the table above. A 10% earn rate doesn't cost you 5% of revenue; it costs closer to 3%.

This is exactly why rolling expiry and urgent messaging aren't just "growth hacks" bolted onto a loyalty program — they're load-bearing parts of the financial model. A points system with no expiry and no urgency drives breakage toward zero, which means the loyalty program quietly turns into a permanent 5-7.5% discount on every order, forever. Rolling expiry plus loss-framed nudges keeps breakage where the business case assumes it'll be.

Edge Cases, Because They Always Show Up

That second-to-last row took a real bug to discover. Calculating the redemption cap off the pre-discount total let students stack a 20%-of-order coin redemption on top of an already-discounted total, pushing the effective discount well past what the business model assumed.

And because the cached current_balance can drift from reality (a failed update here, a race condition there), there's a standing weekly reconciliation query:

SELECT
  la.user_id,
  la.current_balance AS cached,
  SUM(cl.coins)      AS ledger_sum,
  la.current_balance - SUM(cl.coins) AS drift
FROM loyalty_accounts la
JOIN coin_ledger cl ON cl.user_id = la.user_id
GROUP BY la.user_id, la.current_balance
HAVING la.current_balance != SUM(cl.coins);

Any row returned here means the cache lied. The ledger is what gets trusted to fix it.

What I'd Do Differently

A few honest notes, since "everything went perfectly" posts are boring and also usually false.

FIFO isn't actually atomic. The redemption flow conceptually consumes the oldest earn rows first, but in the current implementation, source_ledger_id linking and balance updates happen as separate sequential writes rather than inside a single transaction or stored procedure. Under normal load this is fine — but it leaves a narrow window where a concurrent redemption could read a stale balance. For a kiosk doing dozens of orders a day, the odds of hitting that window are low, but it's not zero, and I know it.

I considered pushing the whole earn/redeem flow into a Postgres RPC (a plpgsql function) instead of doing it in application code across multiple round trips. That would make the balance update, ledger insert, and tier check happen atomically inside the database, closing the race condition above for good. I didn't do it because the app-code version was faster to iterate on and easier to debug while the schema was still changing. Now that the schema's stable, this is the first thing on my list — wrapping earnCoins and redeemCoins in RPCs with proper row locking.

The reconciliation query is manual. It tells you when balances have drifted, but someone still has to run it and fix things by hand. A cron that runs it weekly and auto-corrects drift (by inserting an admin_adjust row to match the ledger) would close the loop.

The whatsapp_trigger_log table grows forever. It's small per row, but at scale it's an unbounded table with no archival strategy. Probably fine for years, but worth a TTL-based cleanup eventually.

None of these are "the system is broken" problems — they're "I know exactly where I cut a corner and why" problems. Which, honestly, might be the most useful thing a system design post can tell you. 🧇

If you're building something similar: start with the ledger. Everything else — expiry, dedup, messaging, even the business model — falls out of getting that one append-only table right.