Core Architecture Cost Mapping Systems

Location-Specific Pricing Overrides

This page shows a food-tech developer how to give each location its own ingredient prices without forking the recipe database — the core mechanism behind a multi-location cost center architecture. Read that guide first for the one-graph-N-overlays principle, then follow the steps here to build an effective-dated override table and a resolver that computes a per-location theoretical cost from the single shared BOM.

The operational failure this prevents is recipe duplication: copying the master recipe per store so each can carry its own prices, then watching those copies desynchronize the first time a recipe changes. The correct model is one recipe graph and a thin per-location price overlay.

Prerequisites and Data Contract

  • PostgreSQL for the tables, Python 3.11+ with pandas 2.x for the roll-up.
  • A base_prices table of (ingredient_sku, unit_cost, effective_date) and a price_overrides table of (location_id, ingredient_sku, unit_cost, effective_date, expiration_date).
  • Prices as NUMERIC / Decimal; the resolver returns a per-(location_id, ingredient_sku) effective cost as of a date.

The override table is sparse — it holds only the ingredients a location actually prices differently. Everything else resolves to the base price, so adding a location that matches base pricing adds zero override rows.

Per-location price resolution and roll-up For a given location and ingredient at a date, the resolver checks the override table for an effective row. If one exists its unit cost is used; otherwise the resolver falls back to the base price. The resolved per-location price feeds the single shared bill-of-materials roll-up to produce a location-specific theoretical cost. Override table sparse · effective-dated Base prices fallback Resolve price override else base Shared BOM one graph Per-location cost

Step-by-Step Implementation

Step 1 — Model the override table

CREATE TABLE price_overrides (
    location_id      TEXT       NOT NULL,
    ingredient_sku   TEXT       NOT NULL,
    unit_cost        NUMERIC(12,4) NOT NULL,
    effective_date   DATE       NOT NULL,
    expiration_date  DATE,
    PRIMARY KEY (location_id, ingredient_sku, effective_date)
);

Step 2 — Resolve the effective price as of a date

Prefer the most recent effective, unexpired override; fall back to the base price.

from __future__ import annotations

from datetime import date
from decimal import Decimal

import pandas as pd


def resolve_prices(base: pd.DataFrame, overrides: pd.DataFrame,
                   location_id: str, as_of: date) -> pd.DataFrame:
    """Return one effective unit_cost per ingredient_sku for a location."""
    ov = overrides[
        (overrides["location_id"] == location_id)
        & (overrides["effective_date"] <= as_of)
        & (overrides["expiration_date"].isna() | (overrides["expiration_date"] > as_of))
    ]
    ov = ov.sort_values("effective_date").groupby("ingredient_sku", as_index=False).last()

    merged = base.merge(
        ov[["ingredient_sku", "unit_cost"]], on="ingredient_sku",
        how="left", suffixes=("_base", "_ov"),
    )
    merged["unit_cost"] = merged["unit_cost_ov"].where(
        merged["unit_cost_ov"].notna(), merged["unit_cost_base"]
    )
    return merged[["ingredient_sku", "unit_cost"]]

Step 3 — Roll up the shared BOM with resolved prices

Feed the resolved prices into the same roll-up every location uses; nothing about the recipe graph changes per store.

from decimal import Decimal

import pandas as pd


def location_cost(bom: pd.DataFrame, prices: pd.DataFrame) -> pd.DataFrame:
    merged = bom.merge(prices, on="ingredient_sku", how="left")
    merged["line_cost"] = merged.apply(
        lambda r: Decimal(str(r["canonical_quantity"])) * Decimal(str(r["unit_cost"])),
        axis=1,
    )
    return merged.groupby("menu_item_id", as_index=False)["line_cost"].sum()

Verification and Validation

  • Fallback correctness. For an ingredient with no override at a location, confirm the resolved price equals the base price exactly.
  • Effective dating. Insert an override effective next week; resolving as of today must still return the base price, and resolving next week must return the override.
  • One graph. Change a recipe once and confirm every location’s cost reflects it on the next roll-up — no per-store recipe copy exists to update.
  • Reconciliation. Sum a location’s line costs in Decimal and confirm it matches the ledger to the cent.

Gotchas and Edge Cases

  • Orphaned overrides. An override for an ingredient no longer in any recipe silently lingers. Periodically anti-join overrides against active BOM leaves and flag rows that match nothing.
  • Overlapping effective ranges. Two overrides effective on the same date for one (location, sku) make resolution ambiguous. The primary key on effective_date plus groupby(...).last() disambiguates, but validate that ranges do not overlap on insert.
  • Stale overrides. An override with a long-past effective_date and no expiration keeps applying forever. Diff each override against the current base price and flag ones that have drifted implausibly for review.
  • Float in the join. NUMERIC columns arrive as float via some drivers; wrap in Decimal(str(...)) before multiplying so per-location costs reconcile.

For engine specifics, see the official PostgreSQL documentation.