Customer-Specific Pricing That Survives 40,000 SKUs

The instinct is to precompute every customer-product price. With 800 accounts and 40,000 SKUs that is 32 million rows that go stale the moment a contract changes. The alternative is to store rules and resolve them — with caching designed around how buyers actually browse.

Ask a team how they handle contract pricing and you will usually hear some version of "we sync the price list". Push a little and the design turns out to be a table of customer-product-price rows, generated nightly from the ERP.

That works at small scale and fails predictably at real scale. Eight hundred accounts against forty thousand SKUs is thirty-two million rows. Regenerating that nightly is slow, regenerating it on every contract amendment is slower, and in between the storefront is confidently showing prices that are no longer correct.

Store rules, resolve on demand

Real commercial agreements are not thirty-two million independent facts. They are a few hundred rules with a precedence order: a list price, a customer group discount, a contract override on certain ranges, a volume break, maybe a promotional floor. The row count you actually need to store is small. The work is deciding which rule wins.

The resolver

Expressed as code, the resolver is short. That is the point — the complexity belongs in the data, where the business can change it, not in branching logic that requires a deployment.

type Rule =
  | { kind: 'group_discount'; percentOff: number }
  | { kind: 'contract_price'; price: number; skus: string[] }
  | { kind: 'volume_break'; minQty: number; percentOff: number }
  | { kind: 'floor'; price: number };

const PRECEDENCE = ['contract_price', 'group_discount', 'volume_break', 'floor'] as const;

export function resolvePrice(
  listPrice: number,
  sku: string,
  quantity: number,
  rules: Rule[]
): { price: number; applied: string[] } {
  let price = listPrice;
  const applied: string[] = [];

  for (const kind of PRECEDENCE) {
    const rule = rules.find(r => r.kind === kind);
    if (!rule) continue;

    switch (rule.kind) {
      case 'contract_price':
        if (rule.skus.includes(sku)) {
          price = rule.price;               // replaces, does not stack
          applied.push('contract_price');
        }
        break;

      case 'group_discount':
        // Skipped when a contract price already won — the agreed precedence.
        if (!applied.includes('contract_price')) {
          price = round2(price * (1 - rule.percentOff / 100));
          applied.push('group_discount');
        }
        break;

      case 'volume_break':
        if (quantity >= rule.minQty) {
          price = round2(price * (1 - rule.percentOff / 100));
          applied.push('volume_break');
        }
        break;

      case 'floor':
        if (price < rule.price) {
          price = rule.price;
          applied.push('floor');
        }
        break;
    }
  }

  return { price, applied };
}

const round2 = (n: number) => Math.round(n * 100) / 100;

Caching around how buyers actually browse

Resolution per line is cheap; resolution for a 200-product category page on every request is not. But B2B browsing has a property that makes caching unusually effective: a given buyer sees the same prices repeatedly, and those prices change rarely — typically when a contract is amended, which is a discrete, observable event.

• Cache by account and SKU, not by user. Everyone at the same company sees the same price, so the cache key is the company, not the individual, which multiplies your hit rate.
• Invalidate on the event, not on a timer. A contract amendment in the ERP should publish an event that evicts exactly the affected account, not expire everything hourly.
• Precompute only the hot set. The first screen of the top categories for the most active accounts covers the overwhelming majority of views. Resolve the long tail on demand.
• Never cache across accounts. This sounds obvious and is nonetheless the most damaging bug in this area — one buyer seeing another’s negotiated price is a commercial incident, not a technical one.

Keep one source of truth

Where the ERP owns commercial terms, it must own them entirely. The storefront should hold a derived, invalidatable copy and nothing more. The moment someone adds a discount rule directly in the platform admin "just for this promotion", you have two systems disagreeing about price and no way to say which is right.