Customer-Keyed Cross-Reference Bootstrap from History

May contain stale references

This document was audited on 2026-06-22 and contains references that need updating: schema fields (confirmation_count, last_seen_at) not in current schema. Cross-check critical details against the current codebase before relying on them.

Status: Design v2 — re-reviewed 2026-06-15 against current tree; corrections applied below Owner: David Boone Trigger: During Phase A diagnosis we discovered that CK has 0 customer-keyed xrefs across 77 customers despite 1,055+ validated POs in history. The Phase A matching unlock works for any customer but every customer still starts cold. This design wires the historical bootstrap. Supersedes: sections of docs/designs/typesense-matching-architecture.md (Phase B). Phase A still as-shipped in PR #932. Repo paths in this doc are relative to repo root.

0. CORRECTIONS APPLIED (2026-06-15 re-review — read first)

A fresh eng pass against the current tree found this doc had drifted. Four corrections, all code-verified:

1. Use backfill-xrefs-from-golden.ts, NOT -from-history.ts. The -from-history script filters customer_part_number IS NOT NULL, but the extractor never populates that field for CK (codes land in item_ids[]; only the VMS profile sets it). It is inert for CK — the author already pivoted (backfill-xrefs-from-golden.ts:5-7 header). The go-forward source keys on item_ids + normalized item_name, mirroring findItemPattern’s tiers. §7 A.5 below is updated.
2. Add a cross_references reconcile to the dagster asset (§3/§8 #5). Today apps/dagster/erp_pipeline/assets/typesense.py reconciles the products collection (reconcile_documents) but the cross_references asset is pure upsert — stale xrefs (e.g. SKUs killed by the two-series collapse) linger forever and are only masked at query time by the matcher self-gate. Mirror reconcile_documents for cross_references so dead rows get swept at the data layer.
3. Confirmation capture is missing (§7 Phase B step 3). confirmItem (the Confirm button + J/Enter, orderActions.ts:313) writes nothing today — only the ⌘K manual-modal path (assignErpMatch→savePartNumberMapping) captures. So “review-v2 confirm feeds the loop” is a real new write, not a hook on an existing one.
4. Gate the persist/autoReprice path. persist-auto-priced-matches.ts auto-applies matches with isAutoValidated:true and no review surface (the review-v2 confirm/reject UI only fires in the auto-validate path). History auto-apply there must be gated to suggested/needs-review.
5. Stale refs: this doc + both scripts referenced the retired CK Demo connection 093e4161; current sandbox is 4f234677. Updated throughout this doc (the scripts still need the same refresh at build time).

Confirmed sound (no change): the cross_references P1 bucket is customer+connection-scoped and self-gates stale IDs (resolves the xref’s target product; drops the hit if SKU absent/delete_flag; + Jaccard guard) — typesense-search-service.ts:1821-1885. Companion UX work (matched-name + ERP-price clarity, ship-first) lives in history-aware-matching-and-match-clarity.md §4.

---

1. Problem

Auto-validation in apps/webapp/src/services/search/typesense-search-service.ts filters cross_references by customer_id:=[…]. For Cleveland Kitchen connection 4f234677:

• 347 existing xrefs, all customer_id = __UNIVERSAL__
• 0 customer-specific xrefs for any of CK’s 77 customers
• 1,055 validated POs sit in Postgres extracted_order_items and packages/eval/test-data/golden-dataset/cleveland-kitchen-golden-dataset.json — none mined into xrefs

Phase A (PR #932) fixes “matcher never had a chance” via UPC variants + name+description sub-search + Jaccard guard. It is customer-agnostic and helps every customer immediately. But: every customer still has a cold matching surface. Each unique Giant Eagle / Dot Foods / Misfits catalog code has to fight through fuzzy heuristics on every PO, even when the right answer is sitting in our own validated history.

Effect on the kickoff: the Phase A code will likely lift Giant Eagle from 3/19 → ~12/19 via name bucket. Customer-keyed xrefs would push that to ~17/19 by short-circuiting the fuzzy search with operator-confirmed history.

2. Source survey

Source	What it preserves	Customer-keyed code xrefs?	Volume (CK)
Postgres extracted_order_items	clerkOrganizationId, customerPartNumber (customer’s original catalog code), itemName, itemDescription, erpItemId, isValidated, isAutoValidated, joined to extracted_orders.erp_customer_id	✅ Yes — direct	~4,770 validated lines, growing
cleveland-kitchen-golden-dataset.json	customer_name, _wherefourMatch.customerId, order_items.item_ids (post-validation: CK SKU + UPC), item_name. Customer’s original code stripped during validation snapshot	❌ No — only UPC↔SKU and name↔SKU triples	1,055 POs across 77 customers
Live xref-learner (Phase A goes forward)	Review-v2 operator confirms → write xref in real time	✅ Yes — going forward only	0 today, ~N/day after ship

Source A is primary. Source B is a precision audit overlay only (if A says “Giant Eagle 2725 → SKU X” but B’s UPC chain disagrees, quarantine). They are NOT merged into the same upsert.

3. Architecture

                ┌─────────────────────────────────┐
                │  Postgres `extracted_order_items`│
                │  (4,770+ validated lines)        │
                └────────────────┬─────────────────┘
                                 │
                                 │ JOIN extracted_orders.erp_customer_id
                                 │
                  ┌──────────────▼──────────────┐
                  │  xref-learner.ts (module)   │
                  │  upsert(row, source, conf)  │
                  └──────┬──────────────┬───────┘
                         │              │
            ┌────────────▼────┐    ┌────▼──────────────┐
            │ backfill-xrefs- │    │ review-v2 confirm │
            │ from-history.ts │    │  + auto-validate  │
            │ (one-shot)      │    │  (real-time)      │
            └────────┬────────┘    └─────────┬─────────┘
                     │                       │
                     └───────────┬───────────┘
                                 │
                  ┌──────────────▼──────────────┐
                  │  Postgres                   │
                  │  `manual_cross_references`  │ ← single source of truth
                  └──────┬──────────────┬───────┘
                         │              │
              direct push │              │ Phase B+: dbt UNION
                         ▼              ▼
              ┌───────────────────┐  ┌─────────────────┐
              │ Typesense         │  │ Iceberg → dbt   │
              │ cross_references  │  │ (analytics)     │
              │ (matching path)   │  │                 │
              └───────────────────┘  └─────────────────┘

Key design tenets (all from independent eng review):

1. Postgres is authoritative. Typesense is a derived index. The “writer of truth was dbt” mistake that got us into the current hole is not repeated.
2. One code path, two drivers. Backfill script and the live xref-learner invoke the same upsert function. Different drivers = same write contract.
3. Source A only for actual writes. Source B is contradiction-detection only.

4. Schema additions to manual_cross_references

Drizzle schema lives at packages/db/src/schema/manual-cross-references.ts. Add these fields (Phase B prereq):

source:              text('source').notNull(),              // 'operator_selection' | 'auto_validated' | 'order_history' | 'spreadsheet_upc'
confidence:          decimal('confidence', { precision: 4, scale: 3 }).notNull().default('1.0'),
confirmation_count:  integer('confirmation_count').notNull().default(1),
last_seen_at:        timestamp('last_seen_at', { withTimezone: true }).notNull().defaultNow(),
quarantined:         boolean('quarantined').notNull().default(false),
quarantine_reason:   text('quarantine_reason'),

Composite uniqueness on (connection_id, customer_id, item_id, their_item_id). Upsert increments confirmation_count, bumps last_seen_at, updates source priority (operator_selection > auto_validated > order_history > spreadsheet_upc).

5. Confidence model (recency-decay + compound gate)

Auto-fire (matcher accepts as exact xref) when:

• quarantined = false AND
• confidence >= 0.7 (after decay) AND
• (source = operator_selection OR confirmation_count >= 2 OR source = order_history AND last_seen_at within 365 days)

Suggest only (surfaces in review-v2 as “we think this might be it”) when:

• confidence in [0.3, 0.7) OR
• source = auto_validated and confirmation_count = 1 OR
• last_seen_at older than 365 days

Quarantine (excluded from auto-fire, flagged in review-v2):

• Same (customer_id, their_item_id) resolves to two different item_ids in history → catalog change or operator error. Hard-stop, surface for human resolution.

Decay: linear confidence = clamp(1.0 - 0.5 * age_days / 730, 0.3, 1.0). 90 days → 0.94. 365 days → 0.75. 730 days → 0.5. Older still rules out auto-fire by the >= 0.7 gate. Re-confirmation in review-v2 resets last_seen_at and snaps confidence back up.

6. Per-customer cohort eval (required ship gate)

Aggregate eval can hide cohort regressions. packages/eval/src/runners/auto-validation-runner.ts already exists and calls the production validator directly. Extensions:

1. Emit per-erp_customer_id scores (already grouped by entry; just aggregate by _wherefourMatch.customerId).
2. Ship gate: top 10 customers by line volume must each be ≥ 80% gross price accuracy AND no cohort regresses > 2 pts vs Phase A baseline (taken as the eval baseline AFTER PR #932 merges).
3. Counterfactual eval: run with manual_cross_references rows hidden vs visible. The delta is the work’s value. If any cohort gets <5 pt lift, the operational risk on that cohort is not worth the bootstrap.
4. Precision@auto-fire: of rows we auto-fired, what % were correct (matched the golden truth). Separate from recall.

7. Migration plan (revised vs old design)

Phase A.5 — TODAY (hours, ship for kickoff)

Single, narrow goal: get CK’s most-active customers a bootstrap before kickoff. Defer the formal xref-learner to Phase B; today we ship a thin backfill script that writes directly to Postgres + Typesense (mirroring import-upc-mapping.py’s already-proven pattern).

1. Use packages/db/scripts/backfill-xrefs-from-golden.ts (NOT -from-history.ts — see Correction #1; the history script’s customer_part_number IS NOT NULL filter is inert for CK).
   • --dry-run (default), --connection-id, --customer-id, --min-confirmations
   • Source the customer code from item_ids[] + normalized item_name (the -from-golden keys), NOT customer_part_number. This mirrors findItemPattern’s tiered identity (erp_item_id → item_ids → item_name) and covers description-only lines.
   • Builds (connection_id, customer_id, their_code/normalized_name → erp_item_id) rows with source='order_history' and last_seen_at = max(extracted_order.order_date, extracted_order_items.updated_at)
   • Hard-quarantines (customer_id, their_item_id) resolving to >1 distinct item_id (present today via active=false)
   • Upsert to manual_cross_references (existing schema; new fields are Phase B)
   • Direct push to Typesense cross_references collection (same shape as import-upc-mapping.py:418). The matcher self-gate drops any pushed xref whose erp_item_id is no longer a live product, so stale-collapse rows can’t mis-match — but add the dagster reconcile (Correction #2) to sweep them.
2. Spot-check: confirm top-3 customers’ xref counts and visually scan a sample of 20 rows
3. Smoke-test: re-open Giant Eagle PO in review-v2, auto-validate, expect ≥ 15/19
4. NOT-shipping in A.5: cohort eval gate, schema additions, confidence decay, xref-learner module, quarantine UI

Phase B — this week (1 week, the real version)

1. Schema additions to manual_cross_references (source, confidence, confirmation_count, last_seen_at, quarantined, quarantine_reason) — Drizzle migration
2. apps/webapp/src/services/matching/xref-learner.ts — the shared upsert module
3. Hook xref-learner into review-v2 confirm + auto-validate success. NOTE (Correction #3): confirmItem (Confirm button + J/Enter, orderActions.ts:313) writes nothing today — only assignErpMatch (⌘K modal) captures. This is a NET-NEW write on the confirm path, not a hook onto an existing one. Also gate the persist/autoReprice path (persist-auto-priced-matches.ts) — it auto-applies with no review surface (Correction #4): history auto-apply there → suggested/needs-review until reviewed.
4. Refactor backfill-xrefs-from-golden.ts (the adopted script, Correction #1) to call xref-learner instead of inline SQL
5. Add Postgres → Iceberg bronze asset for manual_cross_references; add UNION in raw_cross_references.sql (analytics parity only — the matching path does NOT need it; direct Typesense push covers matching)
6. Add a cross_references reconcile to apps/dagster/erp_pipeline/assets/typesense.py (Correction #2) — mirror the products asset’s reconcile_documents so xrefs whose erp_item_id died (SKU collapse) get swept, not just runtime-masked by the matcher self-gate.
7. Cohort eval extension to auto-validation-runner.ts + counterfactual mode + precision@auto-fire
8. Run cohort eval, must clear ship gate
9. Roll out to all 77 CK customers + every other connection with is_validated=true history (system-wide, not CK-specific)
10. Review-v2 surfaces quarantined xrefs for resolution

Phase C — week+ (was already Phase C, unchanged)

Vector / hybrid name_embedding search + upc_variants: string[] schema field + Typesense Synonyms API + curation overrides.

8. Failure modes

#	Failure	Test?	Error handling?	User-visible?
1	Backfill writes wrong xrefs from past operator errors	A.5: dry-run + manual spot-check. B: precision@auto-fire eval	Quarantine on (customer, code) → multi-item conflict	Yes — surfaced as needs-resolution in review-v2
2	Race during backfill — auto-validate firing in parallel produces conflicting upserts	A.5: run backfill during quiet hour. B: single-writer lock per (connection_id)	Postgres unique constraint + upsert resolves	No (Postgres serializes)
3	Typesense lags Postgres mid-backfill — matcher sees half-populated xrefs	A.5: batched upsert, run during quiet hour. B: explicit sync barrier between Postgres write and Typesense push	None today	Yes — degraded matching during backfill window
4	Operator confirms in review-v2, next item in same PO doesn’t benefit (no cache invalidation)	B: session-scoped xref overlay in review-v2 store	None today	Yes — confusing UX
5	Catalog change / SKU collapse (CK two-series) — old xref points at a dead erp_item_id	A.5: matcher self-gate drops it at query time. B: decay + quarantine + cross_references reconcile (Correction #2)	Runtime self-gate today; data-layer reconcile in B	No — self-gate prevents wrong match; reconcile removes the dead row
6	Backfill re-run produces duplicates	A.5: ON CONFLICT DO UPDATE upsert. Tested via dry-run twice	Idempotent upsert	No

Critical gaps for A.5 (acknowledged, deferred to B): #3, #4, #5 partial. Ship with documented limitations + a kill switch (Typesense collection alias swap can revert).

9. Test plan

Phase A.5 (today)

• Backfill script --dry-run against staging connection 4f234677, output saved to file, spot-check 20 rows manually
• Re-run with --dry-run --customer-id=415230 (Giant Eagle Bedford) and confirm at least 5 xrefs would be written (we have at least 5 validated Giant Eagle line items in CK history if any)
• Live run with --connection-id=4f234677 (CK only, not all connections)
• Smoke: Giant Eagle PO #406206 re-validate, expect named auto-matched count > Phase A baseline

Phase B (this week)

• Unit tests for xref-learner (upsert + decay + quarantine + conflict resolution)
• Cohort eval baseline → with-bootstrap delta per customer
• Counterfactual eval: empty vs populated manual_cross_references
• Precision@auto-fire: 95% threshold (an auto-fired xref must be right 95% of the time on the golden set)
• Replay test: 100 fake review-v2 confirms in parallel, verify Postgres upserts are correct and Typesense ends up consistent
• Migration test: schema additions backward-compatible, dbt graph rebuilds cleanly with UNION

10. What’s NOT in scope

• Source B (golden dataset JSON) as a writer. Used only for contradiction detection.
• Vector embeddings / hybrid search. Phase C.
• Multi-org cross-pollination. xrefs are connection-scoped; no signal sharing across orgs.
• Spreadsheet-driven xref uploads via UI. Could be a Phase C if a customer requests it; today the import-upc-mapping.py pattern is sufficient.
• Automatic xref invalidation when a product is deleted in WhereFour. Phase C.

11. What already exists (don’t rebuild)

• scripts/import-upc-mapping.py:418 — direct-to-Typesense cross_references import. Reuse the pattern in Phase A.5, don’t write from scratch.
• packages/eval/src/runners/auto-validation-runner.ts — already calls findProductMatchesBatch + findFuzzyMatch directly (no HTTP). Extend, don’t replace.
• packages/eval/src/scorers/auto-validation-scorer.ts — already produces aggregate + per-line scores. Add cohort grouping by _wherefourMatch.customerId.
• extracted_order_items Postgres + extracted_orders.erp_customer_id join — direct query, no new ingestion needed.
• manual_cross_references Drizzle schema — Phase B adds columns, A.5 uses as-is.

12. Open questions for the user (final gate decisions)

1. Phase A.5 today + Phase B this week vs commit to the full Phase B today (1-week scope)
2. Source A only vs Source A + Source B fusion
3. Same code path (xref-learner) for backfill + live vs separate one-shot script + Phase B learner later
4. Cohort eval as hard ship gate for Phase B vs soft signal only

13. Cross-phase themes (autoplan)

• CEO + Eng both flagged: scope risk if “end to end” means 1 week of work; recommend two-step landing (A.5 today, B this week) so kickoff isn’t blocked.
• Independent eng voice flagged: Postgres-as-truth and one-code-path are non-negotiable; the design doc already aligns. No cross-phase disagreement.
• Mismatch with original (this morning’s) design doc: that doc framed Phase B as “this week, optional, behind feature flag.” The autoplan output upgrades it to “this week, required for sustained value, no feature flag — direct rollout per-connection.”