Product catalogue at scale: variants, bulk pricing, trade pricing and per-branch overrides

Variants are rows, not text fields

A variant is its own record with a label, its own SKU, an optional price override, an optional compare-at price, its own image and a sort order, plus an active flag. Public reads only ever return active variants; only admins can write them.

Because cart lines and order lines reference the variant identifier, an order records exactly which 5-litre-blue-with-lid version was bought. That sounds obvious until you have to resolve a delivery dispute on a platform where variants were stored as a comma-separated string.

Buy-more-save-more is a first-class table

Quantity-break pricing lives in its own table with a minimum quantity, the bundle price, the normal price for comparison, and optional start and end timestamps. Only active tiers are publicly readable, and tiers are attached to products at the point results are returned, including search results, so a shopper sees the bulk price on the listing rather than discovering it at checkout.

Because tiers carry their own dates, a seasonal bulk promotion can be loaded weeks ahead and expires without anyone remembering to switch it off.

Trade pricing is locked at the database, not the interface

Business pricing is a separate table keyed by SKU with its own quantity breaks, tied to business accounts linked to real user records. Read access is granted only to users holding the business role, enforced by row-level security in Postgres.

The distinction matters: hiding trade prices in the front end is a design decision, while enforcing it in the database is a security control. There is also a pending state, so an applicant for a trade account cannot see trade pricing before you approve them.

• Trade prices are invisible to retail shoppers at the data layer
• Business accounts map to real authenticated users
• A pending role exists for accounts awaiting approval
• Quantity breaks apply on top of trade pricing

Per-branch pricing rides on the stock record

Rather than a fourth pricing table, a branch overrides price on its own inventory row for that product. It is the right shape: the branch that holds the stock is the branch that can price it, and if that branch does not stock the product there is nothing to override.

The same record carries quantity on hand, quantity reserved, an in-store-only flag and a low-stock threshold, so pricing and availability stay in one place per branch.

Getting thousands of products in and keeping them fresh

Catalogues at this size are not typed in. Products, promotions, users and suppliers come in by mapped CSV upload, supplier XML feeds are ingested on a schedule with de-duplication, and a supplier portal lets suppliers submit price lists and new products for approval instead of emailing spreadsheets.

Every product also carries its own price history, which is how margin drift and supplier creep get noticed before the annual review rather than after it.