Discount formulas are only one part of a promotion system. A usable model also needs to answer who owns a coupon, whether it is active, what counts as a use, and what cancellation should restore.
I use shared coupon definitions and separate records for assigned entitlements and order usage. The public examples below describe the model without reproducing client-specific promotion rules.
Put shared invariants in relational fields
Values used for filtering, joins and constraints belong in explicit fields: activation, validity, type, ownership and usage limits. Formula-specific parameters can use JSONB when they are interpreted by the relevant calculator.
| Responsibility | Representation |
|---|---|
| What the promotion offers | Definition and calculation parameters |
| Who may redeem an assigned benefit | Account entitlement |
| Whether a use is still counted | Order state and the redemption policy |
| What the customer actually received | Amount and discount snapshot on the order |
JSONB is useful for varying parameters, but does not remove the need to validate them. A new calculator must accept a known shape and reject unsupported combinations.
Count business state, not historical mentions
A cancelled order should not consume the same entitlement as a completed purchase. An abandoned checkout also needs a defined reservation lifetime. The implementation derives usage from qualifying order records, using a shared filter for customer limits and administrative totals.
This avoids maintaining a separate counter that every cancellation and refund path would have to repair. It still requires one consistent definition of which order states count.
Serialize the limit check with the purchase
Two requests can both observe the last available use. Reading the count is insufficient if each request can then create an order independently.
The checkout path acquires a transaction-scoped advisory lock for the coupon, checks usage and creates the order within that transaction. Requests for the same coupon therefore serialize at the decision that consumes the entitlement. Unrelated coupons use different lock keys.
Assigned coupons add an ownership check. Possession of a valid code does not establish that the caller owns a particular issued benefit.
Preserve the price the order accepted
Operators can edit a promotion after an order is created. The order keeps the accepted price and discount outcome instead of recalculating its historical total from today’s definition.
Cancellation and refund handling must coordinate the order’s state with the assigned benefit’s state. The data model is complete only when those reverse paths are defined alongside redemption.