Loyalty Program & Points Ledger

A design reference for tier-based point accrual and redemption tied to sales documents

1. Requirements

1.1 Functional requirements

A customer enrolls in a Loyalty Program scoped to one Legal Entity, manually or via “auto opt-in” when an active program’s optional customer-group/territory scope (matched hierarchically) includes the customer; multiple matches prompt a manual choice.
A program defines one or more Collection Tiers — a cumulative-spend threshold and a collection factor (currency per point) — with the lowest tier’s threshold fixed at zero, so a customer is always in a tier from enrollment.
Submitting a sales document computes an eligible amount and writes a Points Ledger Entry crediting points at the qualifying tier’s collection factor, stamped with an expiry date computed at accrual time.
A customer may redeem unexpired points against a later sales document, capped by points available and the document’s value. Redemption draws from existing entries soonest-expiry-first across as many entries as needed, posting the redeemed value as a discount against the customer’s receivable and an expense against a program-configured Ledger Account.
Cancelling a document that earned points removes the accrual, unless those points were already redeemed downstream (that redemption must be cancelled first). A return instead recomputes the original invoice’s accrual entry.

1.2 Non-functional requirements

Correctness under reversal: returns and cancellations must not leave balances that no longer match a real purchase.
Auditability: every accrual and redemption is its own append-only row; balances are never mutated in place.
No silent point loss: tier-qualifying spend is tracked independently of expiry, so tier standing survives points expiring.
Embedded, not batch: accrual and redemption run synchronously inside the sales document’s own submit/cancel lifecycle.

1.3 Constraints

A program is scoped to one Legal Entity; there is no construct for sharing one program across entities’ books.
Collection factor varies only at the tier boundary; redemption uses one flat, program-wide conversion factor, not tier-differentiated.
The mechanism serves more than one source-document kind (sales invoice or point-of-sale sale) via a type-plus-id reference, not a fixed foreign key.

2. High-Level Design

2.1 Component diagram

Both paths run inside the same document’s submit/cancel lifecycle rather than as separate services; cancellation reverses accrual unless a later document has already spent those points.

Loyalty Program — the configuration root: a name, a type (single- or multiple-tier), an active from/to window, optional customer-group/territory scoping for auto opt-in, a Legal Entity, one conversion factor (point value at redemption), an expiry duration in days, and a default redemption Ledger Account plus cost center.

Collection Tier — a row on the program: a tier name, a minimum cumulative-spend threshold, and a collection factor. Save-time validation enforces that the lowest-threshold tier equals zero.

Points Ledger Entry — one immutable row per accrual or redemption: the program, the tier name at time of accrual, the customer, a source-document reference as a type/id pair (invoice or point-of-sale sale), a signed point count (positive for accrual, negative for redemption), the purchase amount behind it, expiry date, posting date, Legal Entity, and an optional reason field for manually-created entries. A redemption entry also carries a self-referencing link back to the specific accrual entry it draws down — this is the only mechanism that actually records redemption history; a separate child structure for logging a redemption event exists in the schema but nothing in the accrual or redemption logic populates or reads it.

3.2 Tier selection

Tiers are sorted ascending by threshold; the lowest is the starting assignment. The engine walks upward: whenever cumulative recorded spend plus the current transaction’s amount meets or exceeds the next tier’s threshold, the customer is promoted and the walk continues; it stops at the first tier whose threshold isn’t met — since thresholds are ascending, this is equivalent to picking the highest qualifying tier. Cumulative spend sums every entry ever posted for the customer/program/Legal Entity, including entries whose points have since expired, so an expired point doesn’t also erase the spend that earned it.

Example (Bronze at 0, Silver at 10,000, Gold at 20,000): 18,000 recorded spend plus a 3,000 transaction lands in Gold (21,000 ≥ 20,000); 4,000 recorded spend plus a 500 transaction stays in Bronze.

3.3 Points-earned arithmetic and expiry

The eligible amount is the document’s total, reduced by any redemption discount already applied on it, and further reduced by the grand total of any return filed against it. Points earned equal that eligible amount divided by the qualifying tier’s collection factor, coerced to a whole number — a fractional remainder is not carried into a future purchase. The tier lookup itself uses the total net of its own redemption discount but before the return deduction, so the qualifying tier and the amount actually earning points can differ slightly when a return is involved.

Expiry is computed once, at accrual, as posting date plus the program’s expiry duration in days, and stored on the entry. Nothing later revisits or writes off an expired entry — every balance read, tier recompute, and redemption query simply filters to entries whose expiry date hasn’t passed; an expired entry stops being counted or offered but is never explicitly reversed with its own posting.

3.4 Redemption ordering and monetary conversion

Redemption validates that requested points don’t exceed the available balance and that the resulting value doesn’t exceed the document’s payable total, then pulls unexpired, positive-point entries for the same Legal Entity and program, ordered soonest-expiry-first — not creation order — restricted to the same source-document type as the one redeeming (never against itself). Walking that list, it computes each entry’s still-unredeemed remainder (original points minus prior redemptions linked to it), consumes the smaller of that remainder and the outstanding request, and writes one negative Points Ledger Entry per source entry drawn from — stopping once satisfied, so a single redemption can span several accrual entries.

The redemption amount is requested points times the program’s flat conversion factor. Two Ledger Postings are appended to the document’s normal general-ledger set: a credit to the receivable account and a debit to the program’s redemption Ledger Account, with an optional cost-center override — feeding the posting funnel described in the general-ledger design rather than opening a separate settlement path.

3.5 Error handling

A program with no zero-threshold tier is rejected at save time; a program tied to a different Legal Entity than the document being posted is rejected outright.
Redemption beyond available balance, or beyond the document’s total, is rejected before any entry is written.
Cancelling a document whose points were already redeemed downstream is blocked, naming the downstream document; that redemption must be cancelled first.
A return against an invoice triggers a delete-and-rebuild of that invoice’s accrual entry rather than a compensating negative entry.

4. Scale and Reliability

Accrual and redemption execute synchronously inside document submit/cancel — no queue in the critical path; cost is per-document, not batch.
The redemption walk issues one query for candidate entries and one grouped query for prior redemptions, then iterates in application code — inexpensive at typical customer-history scale, but costlier for a customer who accumulates an unusually large number of small accrual rows.
Because expiry is enforced only by filtering a stored date, the entry table never shrinks on its own; expired rows remain and are still scanned (then excluded) on every query rather than archived or purged.
The source-document reference is a type/id pair rather than a foreign key, which lets one ledger serve more than one kind of sales document, but leaves referential integrity across those types to application logic alone.

5. Trade-off Analysis

Decision	Trade-off
Append-only ledger of signed point entries, not a mutable running balance	Full audit trail, but balance is a derived sum over history, not a cached field — cost grows with entry count.
Expiry as a stored date, enforced only by read-time filtering, no purge/write-off job	No scheduled process to fail silently, but expired rows are retained forever with no explicit reversal posting.
Flat, program-wide conversion factor at redemption vs. tier-scaled collection factor at accrual	Predictable redemption value, but accrual rewards higher tiers with a cheaper cost-per-point while redemption stays uniform.
FIFO-by-expiry redemption ordering, not FIFO-by-earn-date	Protects customers from losing value to expiry, at the cost of “oldest spent first” not meaning “earliest earned.”
Tier-qualifying spend is expiry-independent; point balance is not	Prevents tier demotion from an unrelated expiry event, but a customer can hold top-tier status with zero spendable points.
Unused redemption-log child structure left in the schema	Real audit trail is the self-referencing link on the ledger entry; the unused structure is dead schema weight.

6. What to Revisit as the System Grows

Expiry write-off visibility: no proactive notice or write-off posting fires when points expire — balance simply drops on next query. A scheduled “expiring soon” notice or an explicit write-off posting would make the loss auditable rather than implicit.
Ledger growth and archival: nothing prunes long-expired entries; a long-running program with a large customer base would benefit from an archival policy before read-time filtering becomes a real query-cost concern.
Multi-entity programs: a program cannot span more than one Legal Entity’s books today; sharing one scheme across companies needs a redesign of the scoping model.
Dead schema cleanup: the unused redemption-log child structure should be wired into a real reporting view or removed.

This is a Low-priority document, so it runs somewhat past the nominal target: the module is small in surface area (one configuration entity, one tier list, one ledger entry type), but the accrual, tier-selection, expiry, and FIFO-redemption logic embedded in the sales document lifecycle are each genuinely non-trivial, and each earned its own subsection rather than being padded to reach a length.