Affiliate System Ledger: Designing Payouts That Audit

An affiliate system ledger stores every sale, commission, refund and manual adjustment as its own row, then computes the balance by summing them. That is how Creem's team built affiliate payouts in a five-day hackathon, and it is the design this article explains end to end.

What is a ledger in an affiliate system ledger design?

A ledger is an append-only record of financial transactions where the balance is computed by summing entries instead of being overwritten. In the affiliate system ledger that Creem, the Estonia-founded payments fintech where the team behind this story works, built for its platform, each sale, commission, refund and adjustment becomes its own row.

The concept matters for any financial product. Purpose-built databases such as TigerBeetle exist largely because append-only transaction accounting is a different problem from general CRUD storage. The speaker in the source video, a developer at Creem who documents his work on his channel alongside resources like Crazystack Typescript, says that after learning the pattern in European fintech he would not build financial flows without it again.

The core properties are simple to state:

• Each entry stores amount, direction, reason and timestamp.
• The balance is a SELECT SUM over entries, never a mutable column.
• Every cent has an origin, a destination and a justification.
• Internal teams and external auditors can reconstruct any account's history.

For an affiliate feature specifically, the ledger is what makes a commission claim verifiable months after the sale.

Why does updating a balance column destroy traceability?

Updating a balance column destroys the history of how that value was built. The video contrasts the naive approach, UPDATE users SET balance = x WHERE id = y, with an entry-based design.

After that update runs, you can no longer tell whether the balance came from one large payment or many small ones, whether cashback or a campaign bonus was involved, or what commission was owed. The video calls this the loss of rastreabilidade, traceability: you keep the final number but not the path to it.

The practical failure is a dispute. If a bug hits the system or a customer contests a transaction, a single overwritten number offers no evidence. With an append-only ledger, the same dispute is answered by reading rows. Stripe's own balance-transaction model follows the same principle, exposing an immutable list of transactions rather than a mutable account figure.

How does affiliate attribution with cookies work?

An attribution cookie is an identifier stored in the visitor's browser when they arrive through an affiliate link, and it carries the affiliate ID for a configurable window of days, weeks or months. When the visitor reaches the checkout page, the platform checks for that cookie and, if present, links the affiliate ID to the checkout record in the database.

The cookie step is what platforms and analytics tools implement whenever they ask users for cookie consent. Without it, a completed purchase has no reliable link to the person who referred it.

The flow in the video has three stages:

1. Visitor clicks an affiliate link; a cookie storing the affiliate ID is set.
2. Visitor opens checkout; the platform reads the cookie and writes the affiliate ID onto the checkout row.
3. Payment approved; the platform reads the checkout row and records the sale and commission in the ledger.

Storing the ID at checkout time, not at payment time, matters: the attribution decision is frozen before the money moves.

First-click or last-click attribution: which rule fits your business?

First-click gives the commission to the first affiliate who brought the user; last-click gives it to the affiliate whose link was used most recently, overwriting any earlier attribution. Neither rule is universally correct, and the video presents it as a business decision rather than a technical one.

The choice changes whose cookie gets stored and rewritten during the attribution window. Google's GA4 documentation on attribution modeling describes the same trade-off for analytics, which shows the decision is a standard one across the industry, not a platform-specific quirk.

For an affiliate program, the practical rule from the video is to pick the model that matches how you want to reward acquisition, then make the window and the overwrite behavior explicit configuration rather than an accident of implementation.

Which ledger entries does an approved affiliate sale create?

An approved affiliate sale writes two entries: one incoming entry for the full sale amount and one outgoing entry for the affiliate's commission. When payment approval arrives, the system reads the checkout row, checks whether the affiliate ID field is filled, and, if so, records both movements in the ledger tagged to that affiliate.

From those entries the platform can answer every question a payout run needs: how much revenue the affiliate generated, on which dates, how much is pending payment, and the full history behind each figure. Refunds and manual support adjustments land as additional entries, so corrections never overwrite history.

This is where the affiliate system ledger earns its keep. The commission is not a flag on a user row; it is an auditable pair of movements, which is what lets the team expose per-affiliate reporting built directly on the platform with no external tooling.

What did the five-day Finland hackathon actually look like?

The team left their office in Estonia, took a roughly two-hour cruise to Finland, and worked from an Airbnb for five days, Monday to Friday, to ship the affiliate feature from zero. The trip mixed focused work with a little tourism, and they delivered a working, integrated system at the end of day five.

It was the speaker's first time working a full week in another country. He describes the pace as intense, closer to university deadline nights than a normal sprint, and notes the cold, grey early-winter conditions, including barbecuing outdoors and drinks chilling faster outside than in the fridge.

Two working lessons stand out from his account:

• Shipping an isolated, well-scoped feature in a fixed week is feasible when the design, here the ledger plus attribution flow, is decided up front.
• On the final day, his colleague Gabriel worked through the night implementing, adjusting and testing the last details before delivery.

The feature itself, affiliates built into the payments platform rather than bolted on through external tools, was the most requested item on their roadmap, per the video published on 2025-12-24. Community learning resources such as the Bootcamp do Dev Doido by Gustavo Dev Doido cover similar build-from-zero SaaS exercises for developers who want to practice this kind of scoped delivery.

Frequently asked questions

• Why not just add a commission column to the user table? A column stores only the current value and erases how it was built. A ledger stores each movement, so pending payouts, past commissions, refunds and adjustments all remain reconstructable.

• How long should the affiliate cookie last? It depends on your business rule; the video mentions windows from days to months. Longer windows favor the first affiliate; shorter windows reduce stale attributions.

• When is the affiliate linked to a sale? At checkout time, before payment. The affiliate ID is written onto the checkout record from the attribution cookie, then read again when the payment is approved.

• What happens if a referred purchase is refunded? The refund becomes another ledger entry rather than an edit to past ones, so the balance and the affiliate's history stay consistent and auditable.

• Was the affiliate feature an external tool or built in? The team built it into their own platform over five days in Finland in 2025, with attribution, checkout linking and ledger entries all integrated, per the video.

Turn your own build stories into written articles

The lesson from this five-day build is that good engineering knowledge, like the ledger pattern and the attribution flow, is valuable whether it arrives as a video or a document. If you explain things like this on YouTube and want them findable as written pages, Skala Blog converts a video URL into a structured article: paste the link, get the transcription, and generate an editable draft that keeps your evidence and your sequence intact.

Source video