How to Build Product-wise Coupon Allocation in PostgreSQL

Cart-level coupons are easy to build: knock a flat amount or percentage off the whole order total. They're also easy to abuse, hard to analyze, and impossible to control with precision. Once a coupon needs to apply to specific products — not the whole cart — the schema has to change shape.

Core Schema

A product-wise coupon system needs three tables instead of one:

Coupons — code, discount type, discount value, and expiry.
Coupon product rules — links a coupon to specific product IDs or category IDs, with optional quantity minimums and per-product discount caps.
Usage log — records which coupon fired, on which order, against which product, and how much discount was applied.

Validation at Checkout

Instead of validating the coupon code once against the whole cart, the checkout flow checks whether the code is valid for each specific product in the cart. Products covered by a matching rule get the discount; everything else in the cart is skipped.

Analytics Benefits

Because every application of a coupon is logged at the product level, you can query total discount per product, average discount per order, and how often a coupon actually triggers. That's what tells you whether a coupon is moving the inventory it was meant to move, or quietly eroding margin somewhere else.

Three Design Considerations

Coupon conflicts — when more than one rule could apply to the same product, you need a priority system: highest discount wins, first rule applied wins, or one coupon per product per order.
Category reassignment — if a product moves categories after a rule was written against that category, the rule can silently stop matching. An audit trail on rule changes keeps that failure from going unnoticed.
Margin checks — discount logic can (and should) check margin thresholds before applying, so a coupon can't fire below a price floor you didn't intend to allow.

Conclusion

The question changes from "is this code valid?" to "is this code valid for this specific product, given current inventory, margin, and business intent?" The schema changes required are minor, but they unlock a lot more control than a single cart-level discount field ever could.