Pricing & Promotional Rules Engine

A design reference for resolving competing price, discount, and freight rules to a single deterministic outcome per transaction line

1. Requirements

1.1 Functional requirements

Let an administrator define many Pricing Rules, scoped to item code, item group, or brand, or to the whole transaction (a document-level discount).
Scope any rule further by legal entity, currency, price list, warehouse, a selling-side dimension (customer, customer group, territory, sales partner, campaign) or buying-side dimension (supplier, supplier group), and a date window.
Let a rule’s effect be a Price effect (rate override, or a discount percentage/amount, optionally with a margin) or a Product effect (a free item — same or different — including a recursive “buy N, get M” progression).
Let applicability be gated further by a min/max quantity, a min/max amount, or an arbitrary boolean expression evaluated against the transaction.
Let several rules coexist for one line and either compete (choose exactly one, deterministically) or combine (stack in sequence), controlled by an explicit flag plus numeric priority.
Provide a Promotional Scheme: one screen defining applicable-for targets and a table of quantity/amount-tiered discount slabs, auto-expanded into individual Pricing Rules kept in sync on every save.
Provide a Coupon Code gating a specific Pricing Rule so it only fires with a matching code; a validity window, usage cap, and running counter, in a multi-use “Promotional” flavor and a single-use, customer-bound “Gift Card” flavor.
Provide a Shipping Rule computing freight independently of the item-level engine — fixed amount, or a function of net total/net weight through non-overlapping bands, optionally restricted by destination country.
Provide a Price List Entry as the base catalog rate a Pricing Rule overrides or discounts, disambiguated by price list, unit of measure, customer/supplier, batch, a packing-quantity break, and validity window.

1.2 Non-functional requirements

Determinism: the same transaction context must always resolve to the same winning rule or rule set.
Guarded, not silent, conflicts: when candidates can’t be reduced to a single winner, the system surfaces the conflict rather than guessing — with one deliberate storefront exception (§3.4).
Idempotent expansion: re-saving a Promotional Scheme updates previously generated rules in place, never duplicates them.
Reversibility: as a line’s inputs change and rules are re-evaluated, the system must undo exactly what one rule most recently contributed, without disturbing others stacked on the same line.
Auditable scheme changes: narrowing what a scheme applies to must not silently orphan a rule a submitted transaction already relied on.

1.3 Constraints

Candidate rules are matched with scoped database queries against per-tier tables; only survivors are ever loaded as full records.
The free-form applicability expression is a boolean Python expression, restricted at authoring time to reject assignment-shaped text (guarding against = typed for ==).
Priority is a fixed enumerated value from a small closed range, not an arbitrary integer, capping how many precedence tiers a design can express.
Recursive product discounts and basket-pooled (“mixed”) conditions are mutually exclusive, both on a rule and inside a scheme’s slabs.

2. High-Level Design

2.2 Data flow — how a Promotional Scheme becomes usable Pricing Rules

This expansion is a short annotated procedure, not a branching graph:

Admin saves a scheme with an applicable-for dimension (say, Customer, listing three customers) and two discount slabs (a “buy 10+” tier and a “buy 50+” tier).
The Scheme Compiler builds a shared argument set from the scheme’s header fields — apply-on level, selling/buying, currency, company, date window, applicable-for list, combinability.
For every slab, for every applicable-for value, one Pricing Rule is generated: 2 slabs x 3 customers = 6 rules; with no applicable-for dimension, one rule per slab.
Each generated rule’s title is set to the scheme’s own name, with item/item-group/brand rows copied from the scheme, so every spawned rule is traceable back to it.
Idempotency on re-save: for each slab/target pair, the compiler finds any previously generated rule for that exact pair and updates it in place; only genuinely new combinations are inserted.
Shrinking the applicable-for list invalidates rules generated for the removed value — deleted silently if never applied to a submitted transaction, or blocked until the scheme is disabled if even one has.
Deleting the scheme deletes every Pricing Rule it ever generated.

Pricing Rule The atomic unit of the engine. Scoped by an apply-on level (Item Code, Item Group, Brand, or Transaction), an optional applicable-for party dimension, currency, price list, warehouse, and validity window. Carries a Price effect (rate, discount percentage, discount amount, margin) or a Product effect (free item, quantity, recursion settings). Two booleans govern how it interacts with siblings: Has Priority (lets its priority number decide the winner even across specificity tiers) and Apply Multiple Pricing Rules (opts it into stacking rather than competing). Priority (a small closed range, blank meaning unset) is the tie-breaker of last resort.

Discount Slab One row of a scheme’s price- or product-discount table: min/max quantity, min/max amount, priority, warehouse, a “suggestion” threshold percentage, and the discount itself. Each slab seeds one or more generated Pricing Rules (§2.2).

Promotional Scheme The compiler’s input: shared scoping fields (apply-on level, selling/buying, party dimensions, dates, company, currency, mixed/cumulative flags) plus a table of Discount Slabs. Never applied directly — only through the Pricing Rules it generates.

Coupon Code A named, typed gate (Promotional or Gift Card) on exactly one Pricing Rule (a Gift Card is bound to one customer, capped at a single use). Carries its own validity window, a maximum-use cap, and a running “used” counter incremented on success and decremented on cancellation. A coupon-gated rule is kept as a candidate only if the transaction’s own code resolves back to that rule.

Applied Rule Record A row written onto the transaction line recording which rule fired, its rate-or-discount mode, margin type, and whether it was applied automatically or only validated — this is what makes reversal precise, letting the system undo exactly what one rule contributed rather than resetting every discount field.

Price List Entry A base catalog rate for one item under one price list, disambiguated by unit of measure, customer or supplier, batch, a packing-quantity break, and a date window. Rows matching every dimension simultaneously are rejected as duplicates at save time; selling/buying and currency are inherited from the linked price list; a template item with variants cannot carry an entry directly.

Shipping Rule An independent document-level engine. Computes freight as a fixed amount, or by locating the value band — sorted ascending, validated for non-overlap, at most one open-ended top band — containing the document’s net total or net weight. Optionally restricted to a list of destination countries.

3.2 The resolution algorithm — how one line’s winning rule (or rules) is chosen

A worked example grounds the branch that matters most — priority overriding specificity, not just breaking ties within one tier:

Rule C: Item Group, "Apply Multiple Pricing Rules" ON, priority 2
Rule D: Item Code (more specific), "Apply Multiple Pricing Rules" OFF, priority 4

Both rules carry an explicit priority, so the top candidate at the
Item Code tier has "Has Priority" set — the engine keeps gathering the
broader Item Group tier instead of stopping early. "Apply Multiple
Pricing Rules" is then not uniformly true across the combined set, so
this is a single-winner resolution: the winner is whichever of C or D
carries the higher priority number, regardless of specificity. Swap
the priorities and the winner swaps too.

When candidates combine instead of compete, stacking order changes the arithmetic: a discount-percentage rule normally adds its converted amount on top of whatever discount earlier rules already accumulated. A rule that instead applies “on the already-discounted rate” compounds multiplicatively against the running discount, and must declare a priority above the lowest tier, since compounding “on top of nothing” would be meaningless.

The Product-effect path (free item / “buy N get M”) works from the same resolved winner: it grants a free quantity of the configured item (or the triggering item itself, if none is configured), and — when recursive — scales that quantity by how far the triggering item’s document quantity has exceeded a configured floor, divided by a “recurse every N units” step, optionally rounded down to whole multiples.

3.3 Coupon and shipping resolution

A coupon-gated Pricing Rule is filtered twice against the same idea — once for line-level rules (the transaction’s coupon code must resolve, via the coupon’s own link, back to exactly this rule) and again for transaction-level rules that set a document-wide discount directly. A Promotional coupon supports many uses up to its cap; a Gift Card is hard-capped at one use, bound to a specific customer. The usage counter increments only after confirming the cap hasn’t been reached, and decrements on cancellation.

Shipping Rule resolution is entirely orthogonal — it never looks at Pricing Rules, coupons, or Price List Entries. It picks a value band from the document’s net total or net weight, converts currency if needed, and writes (or replaces) a single freight line in the document’s charge table.

3.4 Error handling

Unresolved conflicts: when more than one candidate survives every narrowing step in a back-office context, the system throws a named conflict error listing every tied rule — the fix is assigning distinct priorities, not code changes.
The one deliberate exception: in a storefront/shopping-cart context, the same state does not throw; the first candidate by database order is used silently, trading a visible error for an uninterrupted checkout.
No qty/amount match: if a scheme-derived rule’s window doesn’t yet contain the quantity or amount, but is within a configured “suggestion” threshold, nothing is applied — the buyer is only told how much more to add to qualify.
Coupon exhaustion: applying a coupon at its usage cap throws rather than silently over-using it; cancellation always decrements.
Scheme narrowing with live history: removing a target whose generated rule was already used on a submitted transaction blocks the scheme save until it is disabled.
Catalog duplicates: a second Price List Entry matching every disambiguating dimension of an existing one is rejected at save time rather than silently shadowing it.

4. Scale and Reliability

Read-heavy, per-line, per-edit: resolution runs for every line, potentially several times per document, so the candidate-fetch query is scoped — a database-side join against the tier’s own table, filtered by disabled state, transaction direction, and price-list match — rather than loading every rule as a full record.
Cheap early exit: a cached row count is checked first so a system with no active rules pays almost nothing per line.
Memoized tree lookups: recursive ancestor-path lookups for item-group, customer-group, and territory scoping are cached for the duration of a resolution pass.
Unbounded aggregation risk: cumulative and mixed-condition rules recompute running quantity/amount by querying sibling documents or lines live on every evaluation — no cached counter, so a wide date window on a high-volume party means scanning growing history per line.
Counter races: a coupon’s usage counter is read, checked, and incremented as separate steps ending in a full document save, not an atomic increment — a plausible race under concurrent checkouts converging on the last use.
Scheme fan-out: a scheme’s generated-rule count grows combinatorially with slabs times applicable-for targets; hundreds of targets materialize hundreds of independently-editable rules to reconcile on every save.

5. Trade-off Analysis

Decision	Trade-off
Fixed specificity order (Item Code, Item Group, Brand), stopping at the first tier with matches by default	Predictable “most specific wins” out of the box, at the cost of surprising authors who expect a broader item-group rule to still apply alongside an item-code rule — every rule in a tier must opt into combining to keep the search going.
Priority as the final arbiter even across specificity tiers, once tiers are merged	Lets an author deliberately override “more specific wins,” but means specificity is not a safety net — a broad, high-priority rule can beat a narrow, low-priority one for the exact item it targets.
Unresolved conflicts throw in back-office contexts but resolve silently in storefront contexts	Protects operators from silent mispricing while keeping checkout uninterrupted — but the two contexts genuinely differ, and staff must know that to avoid chasing a discrepancy as a bug.
Promotional Scheme as a compiler over ordinary Pricing Rule rows, not a first-class rule type	Reuses the whole resolution/application engine for free, but multiplies row count combinatorially and lets a generated rule quietly diverge from its parent scheme, since each row is independently editable.
Coupon gating applied as a post-match filter rather than a dimension of the initial query	Keeps the core matching query untouched by coupon logic, but an inactive coupon’s rule still competes for priority/tie-break slots before the coupon check excludes it.
Cumulative/mixed-condition thresholds computed by live re-aggregation, not a maintained counter	Always reflects current state with no separate ledger to keep consistent, at the cost of a live aggregate query per re-evaluation of a matching line.
Shipping Rule kept as a wholly separate, document-level engine from the item-level Pricing Rule engine	Clean separation of concerns — but an operator reasoning about everything that can change a customer’s price has two systems with no shared precedence model.

6. What to Revisit as the System Grows

Atomic coupon usage: replace the read-check-increment-then-save sequence with a single atomic, locked update before checkout concurrency turns the current race into over-redemption.
Unify conflict behavior: decide whether storefront checkouts should keep silently resolving ties, or surface the same conflict signal the back office gets — the current split is easy to mistake for a bug.
Cache cumulative/mixed aggregates: once volume or rule count grows, the live re-aggregation per line is a clear candidate for a maintained running total.
Bound scheme fan-out: for very large applicable-for lists, consider whether pre-materializing every slab-x-target combination still holds up, or a lazier resolution path would scale better.
Priority range ceiling: the small enumerated priority range works for a handful of precedence tiers; many overlapping regional/seasonal/loyalty schemes stacked at once may outgrow it.