Introduction
The heart of a point audit log is "how you turn a single occurrence into a single row of data." If that design is weak, you won't be able to answer "why did the balance end up like this?" later on.
In this article, I'll explain the event-record schema I actually designed for a project that ran points across both a POS system (Smaregi) and an EC site. Three operations — grant, spend, and convert — are all recorded in the same shape. I'll share the thinking behind that as concretely as I can.
The Idea of Recording Events
Don't "Overwrite" the Balance — "Stack It Up"
Many systems hold a single number per customer — "balance: 1,000" — and overwrite it on every change. It's simple, but overwriting has a weakness: it erases the past.
The design I chose does the opposite. It holds no balance at all and instead stacks up only the records of change, one row at a time. The sum of events like "+100," "-500," "+300" is the balance at that point in time. Just like a bank passbook — as long as you don't delete the rows, the history is traceable forever. This stacking approach is the very starting point of auditability.
Record Three Operations in the Same Shape
Point movement, boiled down, comes in three kinds: grant (increase), spend (decrease), and convert (exchange into a coupon and so on). Managing these in separate tables means comparing three of them every time you reconcile.
So I consolidated all three into one log, distinguished by an "operation type" field. In the same shape, you can derive the balance just by summing, and the flow reads clearly when you scan the list.
Fields to Put in a Single Event
The 5W1H You Want at Minimum
Ideally, a single event row makes sense to anyone reading it later. I decided on the fields as if filling in the 5W1H.
balance_after (the balance after the operation) tends to get omitted, but with it, you can see at a glance "how many there were right after this row," which makes reconciliation during failures far easier.
Always Record the Grant Source
source (the grant source) is a crucial field for spotting fraud and mistakes. Even the same "+100" means something completely different when it comes from a purchase versus a manual grant.
If you can later extract only the manual grants, you can see "who granted how much by hand." Since this is an operation that issues money, you must record its origin. I think of this less as security and more as basic accounting hygiene — something that should simply be there.
Link to the Original Transaction with a Reference Key
An event on its own doesn't tell you "why" it was granted. So I have it carry the originating order number or campaign ID as a reference.
This reference key also helps prevent double grants, discussed below. Because you can uniquely pin down "the grant event for this order," you'll notice when you're about to grant twice for the same order.
Preventing "Double Recording" with Idempotency
Why the Same Event Arrives Twice
In system integrations, network timeouts and resends routinely cause "the same operation's instruction to arrive twice." Record it naively twice, and the points get added twice as well.
Create a key that uniquely represents the operation, like "grant for order 123"
If an event with the same key already exists, treat it as a resend
On a duplicate, quietly ignore it and don't move the balance
Key Design Is the Crux of Idempotency
Whether you can make idempotency work hinges on the design of this unique key. The trick is to build it from a combination that won't duplicate in business terms, like "customer ID + order number + operation type."
Build the key from business meaning
Instead of a random ID, assemble the key from "the unit that should realistically happen only once." That way, even if the senders differ, the same real-world occurrence produces the same key, and duplicates get rejected naturally.
If you configure this key as "no duplicates allowed" on the database side, then even if a check slips through, you can stop the double record at the moment of insertion. Having two layers of protection gives peace of mind.
Conclusion
In designing event records, the three things I valued were:
- Stack up changes instead of overwriting the balance — history never disappears, so you can trace it later
- Record grants, spends, and conversions in the same shape — the balance comes out just by summing
- Guarantee idempotency with a unique key — even resends won't record twice
Events recorded this way become a strong ally during failures. The concrete steps for rebuilding a drifted balance from logs are covered in "Logs You Can Use for Reconciliation and Recovery." For a look back at the big picture, see the hub article "Point Audit Logs" as well.