Point Audit Logs — History Design for Fraud and Failures

Introduction

A loyalty program is a powerful way to keep customers coming back. But from the design side, it's the kind of area that makes you sit up straight — because you quickly realize these points are effectively money.

I worked on a project that ran points across both a POS system (Smaregi) and an in-house EC site. Points earned in-store could be spent online, and points earned online could be spent in-store. Convenient, yes — but it also means the answer to "how many points does this customer have right now?" lives in two separate systems.

In this article, I'll explain the audit log design I built as the foundation for running that kind of point operation safely, in a way that makes sense even if you're not an engineer.

Why Point Operations Need an Audit Log

Because Points Are Effectively Money

A point carries monetary value — one point equals one yen, and so on. That means when the system adds a point, it's almost the same as issuing money on the books.

Cash is managed physically in a safe or a register, but points are nothing more than numbers. That's exactly why you have to keep records that let you trace, after the fact, "who increased (or decreased) this, when, and why." Without that traceable state, you can't catch mistakes or fraud. Creating that traceability is the job of the audit log.

You Need Records That Can't Be "Erased"

Most systems keep only the latest balance, overwriting it each time. When a balance changes from 1,000 to 500, the reason for that difference is left nowhere.

So instead of touching the balance directly, I designed the system to stack up every increase and decrease as an individual "event." The balance is derived as the sum of those events. This approach is known as event sourcing.

Crossing POS and EC Splits the State

The trickiest part is that the "correct value" for points doesn't live in one place. The POS has a balance, and the EC site has a balance. A momentary connection drop is enough to make the two numbers drift apart.

Rather than having the two systems talk to each other directly, every operation is first written to the log. With that in place, even when a drift happens, you can return to the "correct history" and recompute.

What to Record in the Audit Log

One Event = "When, Who, What, How Many Points"

A single row in the audit log is one thing that happened to a customer's points. The key is to record, without gaps, the information that will still make sense later. In particular, recording the "balance after the change" alongside makes reconciliation surprisingly easy.

Controlling the Grant Source to Prevent Double Grants

A common point-related problem is the "double grant" — for some reason, points get added twice for the same purchase. The cause is usually a resend between systems or a double-tap of a button.

The property that "running the same operation any number of times yields one result" is called idempotency. Building this into the audit log means resends won't throw the balance off.

Record Spends and Conversions at the Same Granularity

Not just grants — when points are used (a spend) or exchanged for a coupon (a conversion), they're recorded at exactly the same granularity. It's precisely when points "disappear" that customer inquiries spike.

When someone says "my points from last week are gone," a single conversion event in the log lets you answer immediately: "They were exchanged for a coupon on such-and-such date." Keeping the granularity consistent turns straight into a weapon for customer support.

Three Situations Where the Audit Log Pays Off

Detecting Fraud and Mistakes

Recording the grant source makes anomalies visible — "manual grants are concentrated with one particular staff member," or "large grants are running late at night." It's the same idea as recording who goes in and out of a safe with a surveillance camera.

Stopping everything to monitor in real time isn't realistic, but as long as traceable records remain, you can comb out suspicious activity even after the fact. It works as a deterrent, too.

Reconciliation and Recovery During Failures

When the POS and EC balances drift, an audit log lets you answer not "which one is right?" but "what do you get when you total the log?" Instead of a human adjusting things by intuition, you rebuild the correct state from the fact of the history.

The concrete steps for this reconciliation and recovery are covered in the sub-article "Logs You Can Use for Reconciliation and Recovery."

Retention Periods and Privacy

Because the audit log contains customer IDs and operation histories, it is personal data itself. "Keep everything forever for the sake of auditing" is undesirable from a privacy standpoint. The design must hold data safely only for the period it's needed, and appropriately delete or anonymize it when that period ends.

Conclusion

Points are effectively money. That's exactly why an audit log that stacks up every change as an event — rather than holding only "the current balance" — becomes a foundation that works against both fraud and failure.

This hub article covered the big picture. Each detail is explored further in the following three articles.

• Designing Event Records for Grants, Spends, and Conversions — what to put into a single event, with the specifics of the schema.
• Logs You Can Use for Reconciliation and Recovery — how to rebuild the correct state from logs when balances drift.
• Log Retention Periods and Privacy Considerations — how long to keep logs, and how to delete them safely.