Core Architecture Cost Mapping Systems

Automating SKU Substitution Across Locations

This page shows a food-tech developer how to automate ingredient substitution across a multi-location estate without corrupting cost, yield, or allergen data — the availability overlay that complements per-location pricing in a multi-location cost center architecture. Read that guide first, then follow the steps here to build a substitution resolver that swaps a SKU while carrying its full metadata, not just its price.

The failure this prevents is the naked price swap: replacing an out-of-stock ingredient with a cheaper-looking alternative in the cost table while leaving its yield profile and allergen tags behind. That is how silent recipe drift and compliance failures enter a portfolio. A correct substitution inherits the original’s yield, unit rules, and allergens, and logs itself so variance can attribute the change.

Prerequisites and Data Contract

  • Python 3.11+ with pandas 2.x; a substitution_rules table and an availability feed per location.
  • Each rule carries original_sku, substitute_sku, location_id, a trigger (out-of-stock or cost-ceiling), and the substitute’s yield_factor, allergen_tags, and unit.
  • Output is a resolved BOM where substituted lines are flagged and fully re-costed against the substitute’s metadata.
Metadata-preserving substitution resolution A bill-of-materials line enters an availability and cost-ceiling check. If the original SKU is available and within its ceiling it passes through unchanged. If it is out of stock or over its ceiling, a substitution rule swaps in the substitute SKU, inheriting the substitute's yield factor, allergen tags and unit, and writes an audit record so the variance engine can attribute the cost change to the swap. available unavailable / over ceiling BOM line Availability + ceiling check Original passes Substitute + inherit yield · allergen · log route

Step-by-Step Implementation

Step 1 — Model substitution rules with full metadata

The rule carries everything a correct swap needs — never just a replacement SKU.

from __future__ import annotations

from dataclasses import dataclass
from decimal import Decimal


@dataclass(frozen=True)
class SubstitutionRule:
    location_id: str
    original_sku: str
    substitute_sku: str
    trigger: str                    # "out_of_stock" | "cost_ceiling"
    yield_factor: Decimal
    allergen_tags: frozenset[str]
    unit: str

Step 2 — Decide whether a line needs substitution

from decimal import Decimal

import pandas as pd


def needs_substitution(line: pd.Series, availability: dict[str, bool],
                       ceiling: dict[str, Decimal]) -> bool:
    sku = line["ingredient_sku"]
    if not availability.get(sku, True):
        return True                                   # out of stock
    cap = ceiling.get(sku)
    return cap is not None and Decimal(str(line["unit_cost"])) > cap

Step 3 — Apply the swap, inheriting metadata and logging it

from datetime import datetime, timezone

import pandas as pd


def apply_substitution(line: pd.Series, rule: SubstitutionRule,
                       audit: list[dict]) -> pd.Series:
    out = line.copy()
    out["ingredient_sku"] = rule.substitute_sku
    out["yield_factor"] = rule.yield_factor           # inherit, do not keep original's
    out["allergen_tags"] = rule.allergen_tags
    out["unit"] = rule.unit
    out["substituted"] = True
    audit.append({
        "location_id": rule.location_id,
        "original_sku": rule.original_sku,
        "substitute_sku": rule.substitute_sku,
        "trigger": rule.trigger,
        "at": datetime.now(timezone.utc).isoformat(),
    })
    return out

Step 4 — Re-cost the substituted BOM

Substituted lines are re-costed against the substitute’s yield and price, so the per-location cost reflects reality and the audit lets variance attribute the delta.

import pandas as pd


def recost(resolved: pd.DataFrame, prices: pd.DataFrame) -> pd.DataFrame:
    merged = resolved.merge(prices, on="ingredient_sku", how="left")
    merged["usable_qty"] = merged["canonical_quantity"] * merged["yield_factor"]
    merged["line_cost"] = merged["usable_qty"] * merged["unit_cost"]
    return merged

Verification and Validation

  • Metadata inheritance. After a swap, assert the line’s yield_factor, allergen_tags, and unit match the substitute’s — not the original’s. A retained original yield is the classic bug.
  • Allergen safety. Confirm a substitute that introduces a new allergen (peanut oil for canola) is flagged, and that a rule adding an allergen not present in the dish requires explicit approval.
  • Audit completeness. Every swap must produce one audit row with trigger and timestamp, so variance can attribute the cost change to substitution rather than phantom waste.
  • Idempotency. Re-running resolution with the same availability produces the same substitutions and the same audit keys.

Gotchas and Edge Cases

  • Naked price swap. Replacing only the price while keeping the original’s yield and allergens corrupts both cost and compliance. Always inherit the full metadata bundle.
  • Allergen introduction. A cheaper substitute can carry an allergen the menu claims to exclude. Treat any allergen the substitute adds as a hard stop requiring human sign-off, never an automatic swap.
  • Substitution chains. A substitute that is itself out of stock can trigger another rule; bound the chain depth and quarantine a line that cannot resolve rather than looping.
  • Unlogged swaps. A substitution without an audit row makes the resulting variance look like waste or theft. The log is what lets the variance engine say “this cost change was a sanctioned swap.”

For library specifics, see the official pandas documentation.