# Playbook Amendments — Schema Behaviors Not in MISE_ENGINE_PLAYBOOK.md §9

**For:** Edith · **From:** QueryMan (via Vash) · **Updated:** 2026-07-13
Every item below was verified empirically on live hosted customer databases: Day-1 deep verification on one mid-large site ("the reference site" — 6 profit centers, 406K purchase lines, 28.5K item rows), then confirmed across a 14-site fleet sweep (11 dedicated instances + 3 shared-instance tenants). Format per item: **the trap → the evidence → the rule your translator/engine should implement.** Companion data: `DATA_VALUE_RECON.md` (fleet numbers), `SCHEMA_MAP.md` (verified schema).

---

### 1. `VENDOR_INHOUSE` is not a constant — it's data, and it isn't even one vendor

**Trap:** The playbook implies sub-recipe pseudo-purchases in `Trans` are marked by a well-known in-house vendor ID. They aren't. The in-house vendor is an ordinary `Vendor` row *named* "In-house" whose ID varies per site — and pseudo-purchases also flow through *other* internal vendors (profit-center bakery/catering-department vendors via requisitions) and occasionally through real vendors (items with genuine purchase history that later became sub-recipes).
**Evidence:** Reference site: sub-recipe items' `Trans` rows sit under an internal bakery vendor (2,560 rows), "In-house" = VendorID 17 (2,178), a catering-dept vendor, *and* 57 rows under Sysco/Gordon/Baldor. Fleet: one college-dining site has **21.8% of its entire `Trans` table** as internal/requisition flows.
**Rule:** Classify a `Trans` row as a pseudo-purchase **iff** its item joins to `Inv.SubRecipe = 1` on `(ProfCentID, ItemID)`. Never classify by VendorID. Import pseudo-purchases into staging (they carry sub-recipe cost history) but exclude them from all price derivation.

### 2. `Inv` is composite-keyed per profit center *within one database*

**Trap:** §9 covers site-scoped keys *across* sites; it misses that inside a single DB the same `ItemID` exists once per profit center. Any join on `ItemID` alone silently multiplies rows and corrupts every count and cost lookup.
**Evidence:** Reference site: 28,550 `Inv` rows = 12,626 distinct ItemIDs × 6 PCs. Fleet: up to 21 PCs per DB.
**Rule:** Every join touching `Inv`, `Trans`, `InvCat`, `Recipe` uses `(ProfCentID, ItemID)` / `(ProfCentID, RecipeID)`. Exception: `UsFacts` is item-scoped (no PC column) — one yield factor per item, shared across PCs.

### 3. `Trans` has a per-line `UsableFactor` column — fleet-wide unused, but stage it

**Trap:** A third potential yield store (line-level) beyond `UsFacts` and nothing.
**Evidence:** 100% NULL at 13 of 14 sites; the 14th has 66 non-NULL rows, every one exactly 1.0.
**Rule:** Copy the column into staging verbatim; no precedence logic needed today. If a future site shows real values, that's a review item, not a silent merge.

### 4. Unit IDs above the master list are site-local inventions

**Trap:** The 61-unit master list is real, but every site defines custom units above it; the same UnitID ≥ ~500 means different things at different sites.
**Evidence:** Units tables range 67–191 rows across the fleet, max UnitID seen 668. Reference site's highest-frequency purchase unit is a custom ID (502).
**Rule:** Never map units by raw UnitID across sites. Per site: resolve UnitID → that site's `Units` row (name + `UnitCatID`), then map to canonical units by name/category. Conversion facts referencing custom units travel with their site's unit definitions.

### 5. Vendor unit-string aliases are keyed by import format ("Maker"), not vendor

**Trap:** The playbook's `VendorUnitAlias(vendor, text) → unit` shape doesn't match the source. Legacy stores aliases in `ImpOrdUnits(MakerID, UnitDesc, UnitID)` where `MakerID → Makers` is the invoice-import *format provider* ("CSS ChefTec Format", Sysco = 10000, US Foods = 10001, Baldor, "ChefTec AI").
**Evidence:** Reference site: 75 alias rows spanning makers, e.g. "#10 can"→29, "case"→30, "clove"→1. Fleet: 0–133 rows per site.
**Rule:** Model the alias source as import-format (maker), with vendor derivable from the format where 1:1. Migrate all alias rows — they're the seed of the vendor-unit-string memory — and audit for poisoned entries before trusting.

### 6. `ConvMissing` — a persistent conversion-failure log the playbook doesn't know exists

**Trap (missed asset, not a hazard):** Every conversion failure is logged to `ConvMissing(ItemID, VendorID, UnitID1, UnitID2, OperationID)` and never cleaned.
**Evidence:** 62.7K failure rows across ~24K items on 14 sites; at the reference site the log (5,396) is as large as the fact store (5,387).
**Rule:** Import it. Each distinct (item, unit-pair) failure is a pre-validated, pre-prioritized `generate_questions` seed and a per-site conversion-gap metric. This is the ask-once loop's starter inventory.

### 7. `RecpCost` (the diff-harness answer key) has no timestamp and carries failure codes

**Trap:** The cache can't tell you when it was computed, whether it's stale, or — unless you read `RecpCostResult` — whether the cached number is even complete.
**Evidence:** Columns: `RecipeID, NumPortions, PortionPrice, RecipeCost, RecpCostResult, QFactorAmt` — no date. Fleet: coverage of *active* recipes ranges 6%–100%; incomplete-flag (`RecpCostResult = 1`) share ranges 1.3%–52%. Reference site: 21.6% incomplete, and ~1,071 of 2,500 cache rows belong to inactive/deleted recipes.
**Rule:** The diff harness diffs **only `RecpCostResult = 0` rows of active recipes**, buckets code-1 rows as "legacy-incomplete" (expected mismatches), and uses `Recipe.UpdatedDate` / `HistRecipes` as the staleness proxy. Best answer-key sites are the small well-kept books (three sites have 99–100% coverage with <11% incomplete).

### 8. Separator rows are physical `Inv` rows — the line model needs a third case

**Trap:** The playbook's `RecipeLine` check constraint is "exactly one of (ingredient | sub_recipe)". Separators (section headings) are real `Inv` rows (`IsSeparator = 1`) and recipe lines do reference them.
**Evidence:** Reference site: 180 separator items; 7 recipe lines point at them.
**Rule:** Line constraint becomes ingredient XOR sub-recipe XOR separator. Separator lines carry no quantity/unit and never cost.

### 9. `RecpItems` looks like recipe lines and isn't

**Trap:** Two similar tables: `RecpInv` (authoritative ingredient lines) and `RecpItems` (larger, with `QtyInProd`, `QtyReq`, `IsConverted` columns) — a derived production/requisition rollup.
**Evidence:** Reference site: `RecpInv` 34,071 vs `RecpItems` 37,940.
**Rule:** Stage recipe composition from `RecpInv` only. `RecpItems` may be imported for production-history analytics but must never feed `RecipeLine`.

### 10. The SKU story: `Inv.Sku` is empty; `ProdCo` is the real store — and it's a cross-site join key

**Trap/Asset:** An `Inv.Sku` column exists (empty at every site checked). The real vendor-code store is `ProdCo(VendorID, ItemID, UnitID, ProdCode, IsPurchUnit, SplitUnitID, …)`, and `Trans.ProdCodeID` links purchase lines to it.
**Evidence:** Median **80% of purchase lines carry a ProdCodeID** (fleet); Sysco codes are stable 7-digit SUPC identifiers — two unrelated sites share 562 identical codes.
**Rule:** Import `ProdCo` fully; treat (canonical vendor, ProdCode) as a first-class cross-site item-match key, ahead of fuzzy names. Vendor names need canonicalization first ("Sysco" / "SYSCO" / "Nobel/Sysco" / "Sysco Riverside, Inc").

### 11. A seed starter catalog gives you cross-site ItemID joins — with one verification step

**Trap/Asset (new, Day-3 finding):** Low ItemIDs (< ~2000) are a shared factory catalog: the same ID is the same item across unrelated sites (`134` = romaine, `974` = yellow onion, `546` = heavy cream, `179` = AP flour, `372` = unsalted butter — verified at up to 6 sites each). But sites can rename seed items: ItemID `386` is "canola oil" at two sites and "olive blend 90/10" at a third.
**Rule:** Use seed-ID equality as a high-precision match *candidate*, auto-link only when names also agree (trigram or token match), route disagreements to the review queue. Ordering: seed-ID+name → vendor SKU → fuzzy name.

### 12. `IgnoreCost` exists and is ~dead; the zero-row filters do the real work

**Evidence:** 1–24 flagged rows per site out of hundreds of thousands. Zero-qty (median 0.4%), zero-cost (0.4%), no-unit (1.3%) rows are the actual exclusions; quantity float-dust (>3 dp) ~1.4%.
**Rule:** Port the flag faithfully (it's in the legacy WHERE clause), but don't build UX around it. Keep the zero-qty/zero-cost/no-unit exclusions exactly as the playbook already specifies — fleet data confirms their magnitudes.

### 13. Whole feature families are empty or monocultural — port-vs-stub decisions, now with data

**Evidence (14 sites):**
- **Cost methods:** >99.9% of items on method 0 (default) at every site; methods 1/2/3/5 total ~33 items fleet-wide. → **Port the default strategy + per-item dispatch; stub the rest.** (Playbook's "~3 methods" was generous — it's 1.)
- **Menu-price slots 2/3: zero rows at all 14 sites.** Slot 1 fill: 0.1%–81%. → **Migrate slot 1 only**; the "three unlabeled prices" question answers itself.
- **Q-factors: alive** at 4/14 sites (up to 2,246 rows). → **Port** (the math is one line).
- **Bids:** 3/14 sites (up to 532 rows). → Import rows; stub bid strategies.
- **Labor:** 1/14 sites, 15 rows. → **Stub.**
- **Waste events (`WasteRecp`):** one site logs heavily (225K rows). → Future fact stream; import raw.
- **Menu-price history (`RecpPrice`):** rich where used (up to 772K rows). → Harvest for price-change features.

### 14. Yields: "1.0 = never entered" is the whole table, and factors >1.0 are semantics

**Evidence:** 575 real (non-1.0) `UsFacts` entries in the entire 14-site fleet (~0.4% of items); 5 sites have zero. 71% of the fleet's yield knowledge sits at 3 sites. Factors >1.0 occur in clusters that are *deliberate*: Dry Goods 2.0–3.0 and Pasta 3.0 at one site (hydration/cooked gains).
**Rule:** Import 1.0 as NULL (playbook already says this — fleet data says it's 99.6% of rows). Allow factors >1.0 in the schema; ledger-annotate them as yield gains rather than rejecting. Corpus reference-yield seeding starts from the three seed books (`STAPLES_AND_YIELD_SEEDS.md` §3–4, chef review list in `CHEF_REVIEW_YIELDS_1600B.md`).

### 15. One site's books can be partially synthetic — dedupe by profit center before the corpus

**Trap:** A multi-PC contract-feeder site has 16 profit centers holding ~106K purchase rows *each* with near-identical row counts and zero product codes — cloned template books. Its recipe library is cloned the same way (21,709 "active" recipes, 6% cached).
**Rule:** The importer computes a per-PC fingerprint (row count, distinct items, code coverage, date profile); PCs that duplicate another PC's fingerprint are imported but **quarantined from corpus publication**. Without this, one site poisons aggregate price facts.

### 16. Purchase depth is real but "recent activity" must be measured, not inferred

**Evidence:** 20+ year histories at 10/14 sites (one site's `Trans` reaches back to 1988). But last-purchase-date misleads: two sites show June-2026 dates with <160 lines in 12 months.
**Rule:** Site activity = trailing-12-month line volume, not max date. For trend features: ~6,600 items fleet-wide have 6+ purchases/12m — enough for per-item trend series at the 10 genuinely active sites.