Data Ingestion Recipe Parsing Workflows

Menu Schema Normalization

Every POS vendor models a menu differently — nested modifier groups, combo bundles, size variants, promotional overlays — and none of them match the flat, canonical shape a cost engine needs to join against a recipe. This guide, part of the data ingestion and recipe parsing workflows domain, isolates the sub-problem of normalizing those heterogeneous payloads into one schema, deterministically, so that a menu item means the same thing regardless of which system it came from. The failure this eliminates is the per-vendor special case metastasizing through the codebase: costing logic that has to know whether it is looking at a Toast combo or a Square variant, instead of a single canonical menu row.

The menu schema normalization pipeline Heterogeneous menu payloads from different POS vendors enter a Pydantic validation contract. Valid payloads pass into a decomposition stage that breaks combos and modifier groups into component menu items, then a flattening stage that turns nested structures into canonical relational rows, which land in a canonical menu table. Payload shapes the contract does not model are routed to a quarantine queue for a mapping rule to be added. unmodelled Vendor payload nested · varied Validate Pydantic contract Decompose combo · modifier Flatten canonical rows Canonical menu table Quarantine

Concept Definition and Data Contract

Normalization is a transform from a vendor-specific payload to a set of canonical rows. The input is whatever a POS exports; the output contract is fixed: a menu_items row per sellable item with menu_item_id, name, base_price, and status, plus a menu_components row per constituent linking an item to its modifiers and combo parts with signed quantities. Costs are not attached here — normalization resolves structure, and the money multiply happens later in the variance mapping methodologies layer. The vendor-specific decoding of one system’s taxonomy is handled in mapping POS taxonomies to ingredients; this layer produces the canonical shape that mapping targets.

A combo is decomposed into its component items so each can be costed independently; a modifier becomes a signed component (add avocado is +1, no onion is -1). The canonical schema is deliberately flat and relational because a nested menu cannot be joined to a recipe without exploding it first.

Architecture Decision Rationale

One canonical schema over per-vendor branches. The alternative — costing logic that switches on vendor — turns every new POS integration into a change across the whole pipeline. Normalizing to one schema at ingestion means downstream code sees one shape forever, and a new vendor is a new adapter, not a new branch in the cost engine.

Decompose structure at ingestion, cost later. Resolving a combo into its parts is a structural operation that belongs at the boundary; attaching cost there would couple normalization to pricing and duplicate the money path. Keeping the two separate means a pricing change never requires re-normalizing the menu.

Quarantine unmodelled shapes. A payload the contract does not recognize — a new bundle type, an unexpected nesting — is routed to review so a mapping rule can be added, rather than silently coerced into the nearest known shape. A guessed decomposition produces a plausible-looking menu row that costs wrong. The modifier and combo pricing mechanics are detailed in normalizing modifier and combo pricing.

Phase 1 — The Pydantic Menu Contract

The first phase validates a payload against a strict model. Extra fields are forbidden so a vendor schema drift surfaces as a validation error, not a silently ignored field.

from __future__ import annotations

from decimal import Decimal

from pydantic import BaseModel, ConfigDict, Field


class MenuComponent(BaseModel):
    model_config = ConfigDict(extra="forbid")
    component_ref: str
    kind: str                     # "combo_part" | "modifier"
    signed_qty: Decimal = Field(default=Decimal("1"))


class CanonicalMenuItem(BaseModel):
    model_config = ConfigDict(extra="forbid")
    menu_item_id: str
    name: str
    base_price: Decimal
    is_active: bool = True
    components: list[MenuComponent] = Field(default_factory=list)

Phase 2 — Decompose Combos and Modifiers

The validated item is decomposed: combo parts become independent items, modifiers become signed components. This is where nested vendor structure becomes flat canonical structure.

import pandas as pd


def decompose(items: list[CanonicalMenuItem]) -> tuple[pd.DataFrame, pd.DataFrame]:
    item_rows, comp_rows = [], []
    for it in items:
        item_rows.append({"menu_item_id": it.menu_item_id, "name": it.name,
                          "base_price": it.base_price, "status": "active" if it.is_active else "archived"})
        for c in it.components:
            comp_rows.append({"menu_item_id": it.menu_item_id,
                              "component_ref": c.component_ref,
                              "kind": c.kind, "signed_qty": c.signed_qty})
    return pd.DataFrame(item_rows), pd.DataFrame(comp_rows)

Phase 3 — Flatten and Upsert the Canonical Table

The flattened rows upsert idempotently into the canonical menu table, keyed on the item id, so re-ingesting the same menu produces no duplicates and archived items are preserved for historical reconciliation.

INSERT INTO menu_items (menu_item_id, name, base_price, status, ingested_at)
VALUES (:id, :name, :price, :status, NOW())
ON CONFLICT (menu_item_id) DO UPDATE
   SET name = EXCLUDED.name,
       base_price = EXCLUDED.base_price,
       status = EXCLUDED.status,
       ingested_at = EXCLUDED.ingested_at;

Archived items are updated in place with status='archived', never deleted — a dish sold last month under a now-retired id must still resolve when historical sales are reconciled. The canonical menu_components table is what mapping POS taxonomies to ingredients joins against to attach recipes.

Production Hardening

  • extra="forbid". Reject unknown fields so a vendor schema change is a loud validation failure, not a silently dropped attribute.
  • Idempotent upsert. Key the canonical table on menu_item_id so re-ingesting a menu is a no-op, never a duplicate.
  • Preserve archived items. Mark retired items archived rather than deleting them, so historical sales still resolve to a menu row.
  • Quarantine, don’t coerce. Route unmodelled payload shapes to review; never map an unknown structure to the nearest known one.
  • Structure without cost. Keep normalization free of pricing so a cost change never forces a re-normalization and the two concerns evolve independently.

Failure Modes and Troubleshooting

Symptom Likely cause Detection / fix
A new vendor field silently missing Model permitted extra fields Set extra="forbid"; a dropped field then raises.
A combo costed as one opaque item Decomposition skipped Break combos into component items before costing.
Historical sales fail to resolve Archived item deleted Mark items archived, keep the row.
Re-ingest duplicated the menu Insert without idempotency key Upsert on menu_item_id.
A modifier double-counted Modifier not modelled as signed component Represent add/remove as +1 / -1 signed quantities.

FAQ

Why normalize to one schema instead of handling each vendor where it is used?

Because per-vendor handling spreads through every downstream stage — costing, reconciliation, reporting all have to know the vendor’s quirks. Normalizing once at ingestion means everything after sees a single canonical shape, and integrating a new POS becomes writing one adapter rather than adding branches across the whole pipeline.

Why separate structure from cost during normalization?

Resolving a combo into its parts is structural; attaching prices is a separate concern that belongs in the costing layer. Keeping them apart means a pricing change never requires re-normalizing the menu, and the money path stays in one place instead of being duplicated at the ingestion boundary.

How are modifiers represented canonically?

As signed components against the base item: adding an ingredient is a positive quantity, removing one is negative. This makes downstream costing a simple additive resolution and prevents the double-counting that occurs when modifiers are treated as opaque strings appended to an item name.

What happens to a payload shape the contract does not model?

It is quarantined for review so a mapping rule can be added, not coerced into the nearest known shape. A guessed decomposition yields a canonical row that looks valid but costs wrong, which is harder to detect than an explicit quarantine. The quarantine queue is where new vendor bundle types get modelled deliberately.

For library specifics, see the official Pydantic documentation and pandas documentation.