Discount redemption limits are derived from orders, not a counter or ledger

Context

Discount codes need caps: a global usage limit ("first 100 orders"), a per-customer limit ("once per customer"), and the guarantee that an abandoned or refunded order does not permanently burn a redemption. An early version kept a usage_count column incremented at order-create, which had three problems:

• It never released. A code was consumed the moment an order was created, even if the customer never paid or was later refunded — so a limited code could be exhausted by carts that produced no revenue.
• It only counted globally. A per-customer cap had no home in a single counter.
• A running counter can drift from what actually happened, with no way to reconcile.

The key realization: a redemption is already recorded. Every order line carries the discount id, and the order carries the customer and its status. The orders are the ledger.

Decision

Do not keep a redemption counter, and do not add a redemption ledger table. Derive live redemptions by counting the orders a code is recorded on, filtered by order status.

• Counting is by exclusion: an order counts unless it has reached a terminal status that releases the slot (errored, cancelled, returned, refunded, stale). A pending checkout therefore holds its slot as a reservation, paid and post-payment orders count, and a newly added status counts by default — a status can never silently free a redeemed slot.
• The global cap counts all of a code's non-released orders; the per-customer cap counts the same for one customer (resolved by account, looked up by email). A code's id is unique to one merchant, so counting by it is inherently tenant-safe.
• Enforcement runs inside the order-create transaction under a row lock on the code, so two concurrent redemptions of the same limited code are serialised and cannot exceed the cap; a lost race rolls the order back rather than honouring a spent code. Unlimited codes skip the lock.
• A slow payment (invoice, bank transfer) keeps its slot because a pending order stays counted until it either pays or is swept to a terminal status — there is no fixed reservation clock to expire it early.

Consequences

• Positive: refunds and cancellations release the slot automatically; the count reflects reality with no reconciliation; no new table.
• Neutral / to revisit: the standalone usage_count column becomes redundant — kept in place for now, removed in a later release per the deploy constraint below.
• Neutral / to revisit: correctness relies on the default (READ COMMITTED) isolation plus the row lock; raising the global isolation level would require revisiting this.
• Cost: for a limited code the lock is held for the duration of the order-create transaction, which includes payment-provider setup — acceptable for scarce/promotional codes, and a candidate for a later refactor if a very high-volume limited code appears.
• Deploy constraint: commerce-api runs migrations at image-build time, so the database is migrated before the new code is live. Every migration must be expand/contract — add columns now, drop superseded ones in a later release — or the still-running previous release breaks against the new schema.

Alternatives considered

• A usage_count counter incremented at order-create (with a conditional guard): rejected — it consumes a redemption on unpaid/abandoned orders and never releases on refund, and models only the global cap.
• A dedicated redemptions ledger table (customer × code): rejected — the order already links the code to the customer, so a parallel table would only drift; counting the source of truth is simpler and cannot disagree with the orders.