Designing Predictable Pricing Rules for POS Systems

Pricing became one of the most important workflows in a retail POS project I worked on. A product could have a variant-specific price, a member offer, an active promotion, and a quantity tier at the same time.

The problem became clear during user testing. A cashier pointed at two buttons on the screen—“Apply Member Price” and “Apply Discount Price”—and asked:

Hey, when a member buys something that’s also on sale, which price do I use?

The cashier had to interpret the pricing policy during checkout. That slowed the transaction and allowed two cashiers to produce different results from the same inputs.

What I Learned at the Store

I spent time watching cashiers work and listening to their complaints. In the sessions I observed, a normal transaction usually took around 30–60 seconds: scan the product, scan a member card when needed, enter the quantity, take payment, and continue to the next customer.

There was little time to compare competing offers. Customers also expected an eligible member or promotion price to be applied consistently rather than only when a cashier noticed it.

That changed the requirement. The POS needed to evaluate pricing policy automatically and show how it reached the result.

Separate the Baseline from the Rules

The retailer used several price concepts:

• Cost price was an internal margin reference, not a selling candidate.
• Base price was the normal selling price.
• Variant price established the price of a specific color, size, or other sellable variant.
• Member, promotion, and quantity prices were eligible rules evaluated after the baseline was known.

I initially treated every price as one item in a priority list. That was simple to implement, but it mixed concepts that behaved differently.

For example, a selected variant establishes the starting price:

Plain T-Shirt
├─ Size S   = Rp 100.000
├─ Size M   = Rp 100.000
├─ Size L   = Rp 110.000
└─ Size XL  = Rp 120.000

If the customer selects XL, later rules must start from Rp 120,000. A member rule then asks whether the customer is eligible. A promotion checks its active window. A quantity rule selects the matching tier:

Buy 1-4 units   = Rp 100.000 each
Buy 5-10 units  = Rp 95.000 each
Buy 11+ units   = Rp 90.000 each

Separating the baseline, eligibility, and calculation made those decisions easier to explain than one long priority list.

Make Compatibility Explicit

Priority still matters, but it does not answer whether two rules may be combined. The retailer needs an explicit policy, for example:

• calculate each exclusive offer and choose the best eligible price;
• select the first eligible rule in a configured order; or
• apply compatible adjustments in a defined sequence.

A rule therefore needs to state whether it is exclusive, which group it belongs to, and when it runs relative to another adjustment.

Time boundaries also need to be explicit. I encountered a promotion that remained active after its intended end date. After that bug, start and end boundaries, time zones, and inclusive end-date behavior became part of the tests rather than assumptions hidden in the code.

Store Rule Behavior, Not Only a Type

I moved from a simple list of price types to rules with explicit calculation and validity fields. This is a small MySQL 8.4 illustration; targets and detailed conditions belong in related tables. An older MySQL version needs its CHECK enforcement verified before relying on the constraints.

CREATE TABLE pricing_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    rule_type VARCHAR(30) NOT NULL,
    priority INT UNSIGNED NOT NULL DEFAULT 100,
    stack_group VARCHAR(30),
    calculation_type VARCHAR(20) NOT NULL,
    amount DECIMAL(15, 2) NULL,
    percentage DECIMAL(5, 2) NULL,
    starts_at_utc DATETIME NULL,
    ends_at_utc DATETIME NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,

    CONSTRAINT chk_pricing_rules_type CHECK (
        calculation_type IN ('fixed_price', 'amount_off', 'percent_off')
    ),
    CONSTRAINT chk_pricing_rules_value CHECK (
        (calculation_type IN ('fixed_price', 'amount_off')
            AND amount IS NOT NULL
            AND amount >= 0
            AND percentage IS NULL)
        OR
        (calculation_type = 'percent_off'
            AND percentage IS NOT NULL
            AND percentage BETWEEN 0 AND 100
            AND amount IS NULL)
    ),
    CONSTRAINT chk_pricing_rules_window CHECK (
        ends_at_utc IS NULL
        OR starts_at_utc IS NULL
        OR ends_at_utc >= starts_at_utc
    )
);

Application validation is still required. These constraints only protect the basic calculation shape and time window. They do not define stacking, target eligibility, currency rounding, or whether an amount discount may reduce the final price below zero.

I left an index out of this minimal table because it should follow the actual eligibility query and be checked with EXPLAIN. A composite active/start/end index is not automatically effective for every combination of range and NULL predicates.

Calculate and Explain the Price at Checkout

The checkout flow follows a predictable sequence:

1. Resolve the barcode to a product unit or variant
2. Establish the baseline selling price
3. Collect member, location, quantity, channel, and time context
4. Find eligible rules
5. Reject expired or incompatible rules
6. Apply the configured selection or stacking policy
7. Display and persist the calculation breakdown

The cashier sees the result rather than choosing between ordinary rules:

PRODUCT: Plain T-Shirt - Black - L
──────────────────────────────────
Base Price      Rp 210.000  [Black, size L]
Member Rule     -Rp 21.000  [10%]
──────────────────────────────────
TOTAL           Rp 189.000

An authorized manual override remains a separate action with a reason and an approver. It is not another pricing rule hidden in the normal flow.

Preserve the Decision with the Sale

After implementation, cashiers no longer had to choose between ordinary member and promotion rules. The same inputs also produced the same documented calculation, which made pricing behavior easier to test.

The completed transaction keeps the context that produced its total:

• selected variant and baseline price;
• applied and rejected rule IDs;
• quantity, membership, location, channel, and evaluation time;
• calculation order and intermediate values;
• override reason and approver, when present.

Recalculating an old sale from rules that have since changed can produce a different answer. Keeping the original calculation protects receipts, refunds, and later investigation.

I also verify the boundaries that caused the most risk in this workflow:

• promotion start and end times;
• overlapping member, promotion, and quantity rules;
• incompatible stacking groups;
• identical inputs producing an identical breakdown;
• checkout retries not creating another sale;
• refunds using the stored transaction values after rules change.

What I Learned

Pricing was not a sorted list of prices. It was a set of eligibility, compatibility, and calculation rules built on one baseline.

Making those rules explicit removed a decision from the cashier, made the result understandable on screen, and gave the application enough information to reproduce the price later. That was more useful than adding another price button or another number to a priority column.