Designing and implementing a Point System with Cash-Out Support using Event-Based Architecture, Hono, and DrizzleORM

Introduction

About a year ago, I had the opportunity to design and implement a point reward system where points held real monetary value and could be used in various financially meaningful ways.

This wasn’t your typical loyalty system. The points had to be trackable, tamper-proof, and auditable, since they represented real economic value to users. They could be earned, transferred, or spent in ways that required full integrity and transparency.

To meet those requirements, I chose to structure the system around immutable event logs, inspired by Event Sourcing principles. In this article, I’ll share the architecture and reasoning behind it, and walk through how I handled challenges like FIFO consumption, expiration tracking, and real-time balance computation.

---

Why an Event-Based Architecture?

At first glance, a point system seems simple:

• Users earn points
• Users spend points
• You show their current balance

But once you introduce cash-out, expiration, and transferability, complexity skyrockets.

In our case, we needed to support:

• Multiple types of point acquisition (referral bonuses, visit rewards, promotional campaigns)
• Peer-to-peer transfers between users
• Cash-out requests with minimum thresholds
• FIFO consumption — oldest points are used first
• 180-day expiration policy
• Full transaction history and audit trail
• Fraud prevention and balance validation

Trying to manage this with a single balance field would’ve been risky and error-prone:

• It’s hard to ensure consistency with parallel operations
• There’s no audit trail for how the balance changed
• You lose the ability to reconstruct past states or investigate issues

Instead, I designed the system to:

Store every point-related event (both acquisition and usage) immutably.
The current balance is always derived — never stored.

This gave us a consistent, auditable, and extensible system that scaled well even as usage grew. In the next section, I’ll walk through the system's structure at a high level.

---

System Overview

The point system was part of a broader application where users could earn, send, and redeem points in various ways. Here's an overview of the main business flows:

✅ Point Earning

• Users received points from actions like:
  • Signing up (e.g., 500 points)
  • Referring friends (e.g., 5,000 points for successful invites)
  • Making in-store reservations or purchases
• Each point reward had an expiration date (typically 180 days)

♻ Point Usage

• Points could be:
  • Sent to another user (peer-to-peer transfer)
  • Used at participating stores
  • Redeemed as real money through a bank transfer request
• All point deductions followed a FIFO consumption strategy:
  • The oldest unexpired points were used first

🧾 Audit & Integrity

• Every point acquisition and usage event was logged immutably
• The current balance was never stored — it was computed in real-time by subtracting total deductions from total acquisitions
• The system supported:
  • Real-time balance validation
  • Transaction history views
  • Strict referential integrity between event types

🔐 Atomic Transactions

• All multi-step point operations (e.g., transfers, redemptions) were wrapped in DB transactions
• Any failure during deduction or event logging would roll back the entire operation

---

Event Store Design: Modeling Points as Events

The core idea behind this system was simple:

Never store balances — only store events.

Instead of mutating a user’s total balance directly, we created a set of immutable, append-only event tables:

✅ 1. point_credits — Point Acquisition Events

Every time a user earned points, we inserted a row into point_credits.

{
  id: 101,
  userId: 1,
  amount: 500,
  expiresAt: '2025-12-01',
  createdAt: '2025-06-01',
  source: 'referral_bonus',
}

• Each row is a single “credit event”
• Points expire after a fixed period (e.g., 180 days)
• No updates — only inserts

✅ 2. point_usages — Point Deduction Events

When users used points — for redemption, transfer, or purchase — we inserted a row into point_usages.

{
  id: 201,
  userId: 1,
  amount: 1000,
  reason: 'cashout_request',
  createdAt: '2025-06-15',
}

This records the intention to use N points for some action. But it doesn’t specify which earned points were used — that’s handled by the next table.

✅ 3. point_consumption_log — FIFO Breakdown of Usage

To enforce FIFO (first-in, first-out) consumption, we maintained a third table that linked each usage event to the specific credits it consumed.

{
  id: 301,
  usageId: 201,
  creditId: 101,
  amount: 500
}

• This table represents the actual “application” of usage
• It allows us to:
  • Calculate remaining amounts on each credit
  • Track exactly which points were consumed and when
  • Handle expiration by ignoring expired credits during deduction

♻ Example Flow: Spending 1,000 Points

Let’s say a user has two credits:

• 500 points from referral (expires in 30 days)
• 1,000 points from a visit (expires in 180 days)

They spend 1,000 points.

Here’s what happens:

1. Insert into point_usages — usage of 1,000 points
2. FIFO logic triggers:
   • 500 consumed from the first credit
   • 500 from the second
3. Insert two rows into point_consumption_log reflecting this breakdown

🔒 Benefits of This Model

• Auditability: Every transaction is traceable
• Expiration support: Easy to filter out expired credits
• Balance integrity: No risk of double-spending or mismatched states
• Real-time computation: Balances are calculated on-the-fly from event logs

---

Balance Calculation & FIFO Logic

With all point events stored immutably, we needed a way to compute the current balance on the fly, accurately and efficiently. Here's how we approached it.

🧾 Balance Calculation from Events

The core formula is simple:

current_balance = sum(point_credits.amount) - sum(point_consumption_log.amount)

We only consider unexpired credits, and we join the tables to subtract what's already been used.

await db
  .select({
    userId: pointCredits.userId,
    balance: sql<number>`SUM(${pointCredits.amount}) - COALESCE(SUM(${pointConsumption.amount}), 0)`
  })
  .from(pointCredits)
  .leftJoin(pointConsumption, eq(pointCredits.id, pointConsumption.creditId))
  .where(gt(pointCredits.expiresAt, sql`CURRENT_TIMESTAMP`))
  .groupBy(pointCredits.userId)

♻ FIFO Deduction Algorithm (Drizzle ORM style)

export const generatePointConsumptionLog = (
  usageId: number,
  amount: number,
  availableCredits: { id: number; amount: number }[]
): InsertPointConsumption[] => {
  const result: InsertPointConsumption[] = []
  let remaining = amount

  for (const credit of availableCredits) {
    if (remaining <= 0) break

    const consume = Math.min(credit.amount, remaining)
    result.push({
      usageId,
      creditId: credit.id,
      amount: consume,
    })
    remaining -= consume
  }

  return result
}

⚖️ Why No Snapshots or CQRS?

We deliberately avoided introducing snapshots or separate query models, for a few reasons:

1. Simplicity
   The real-time queries were fast enough at our expected scale. Adding snapshot logic would complicate maintenance.

2. Data freshness
   Since the balance could change at any time, deriving it live guaranteed consistency without syncing snapshot states.

3. Auditability
   With the full event trail, we could reconstruct any past state without needing snapshot checkpoints.

That said, we left the door open for future optimization using materialized views or caching — but we didn’t need them initially.

---

Conclusion

Designing a point system with real-world financial implications forced me to think beyond simple balance += x logic.

By treating each point action as an immutable event, I gained:

• Confidence in data correctness
• Transparency in user histories
• Flexibility to extend features without breaking invariants

This was my first experience with an event-sourced model, and it taught me that you don’t need a full CQRS stack to get the core benefits. With a few well-designed tables and clean logic, even a modest team can build robust systems that stand up to financial scrutiny.

If you're building a system where integrity and auditability matter, I highly recommend thinking in events.

Thanks for reading! 🚀