db6b97186a
- Add OPN-based equivalence matcher robot (7,245 manufacturer-confirmed matches, confidence=1.0) - Add spec-based equivalence matcher robot (683 matches, confidence=0.85) - Matches by form_factor + speed_gbps + reach_tier + wavelength ±10nm - Safety cap: skip FX products matching >30 competitors (too generic) - Daily schedule: 04:30 UTC via pg-boss - SQL migrations 116 (OPN) + 117 (spec) with tip_extract_wavelength_nm() + tip_reach_tier() helpers - Fix tenGtek.ts: add 3 missing 400G categories (QSFP-DD, QSFP112) — closes pricing gap - Generate tip-llm-pricing-v1.jsonl: 80 DB-grounded QA pairs (pricing, equivalences, 400G) - Rebuild TIP_LLM training pool: 11,999 pairs (+127 vs prev), deployed to Erik - FX product equivalence coverage: 88.1% (959/1089)
86 lines
3.6 KiB
SQL
86 lines
3.6 KiB
SQL
-- Migration 116: OPN-Based Equivalence Matcher
|
|
-- Uses the manufacturer-provided compatibility matrix (fx_compatibilities) to
|
|
-- create high-confidence equivalences between Flexoptix products and their
|
|
-- exact OEM counterparts in competitor catalogs.
|
|
--
|
|
-- Source of truth: FX API `fx_compatibilities` field — the vendor explicitly
|
|
-- states "this FX product replaces [vendor] [part_number]".
|
|
--
|
|
-- Match quality: confidence=1.0, match_basis='{opn}' (OEM Part Number)
|
|
-- These are better than spec-based matches because they are manufacturer-confirmed.
|
|
--
|
|
-- Rules:
|
|
-- - Only inserts NEW pairs (skips existing approved, auto_approved, rejected)
|
|
-- - Skips MSA Standard and Flexoptix entries (not real competitors)
|
|
-- - Case-insensitive part_number match
|
|
-- - Target must be a competitor vendor (is_competitor = true)
|
|
|
|
-- ── Insert new OPN-based equivalences ────────────────────────────────────────
|
|
|
|
INSERT INTO transceiver_equivalences (
|
|
flexoptix_id,
|
|
competitor_id,
|
|
confidence,
|
|
status,
|
|
match_basis,
|
|
match_notes,
|
|
created_at,
|
|
updated_at
|
|
)
|
|
SELECT DISTINCT
|
|
fx.id AS flexoptix_id,
|
|
comp.id AS competitor_id,
|
|
1.0 AS confidence,
|
|
'auto_approved' AS status,
|
|
ARRAY['opn'] AS match_basis,
|
|
'Manufacturer-confirmed: FX compatibility matrix lists ' ||
|
|
COALESCE(compat->>'compatible_to_vendor', '?') || ' OPN ' ||
|
|
COALESCE(compat->>'original_part_number', '?') AS match_notes,
|
|
NOW() AS created_at,
|
|
NOW() AS updated_at
|
|
FROM transceivers fx
|
|
JOIN vendors vfx ON vfx.id = fx.vendor_id AND UPPER(vfx.name) LIKE '%FLEXOPTIX%'
|
|
CROSS JOIN LATERAL jsonb_array_elements(fx.fx_compatibilities) AS compat
|
|
JOIN transceivers comp
|
|
ON UPPER(comp.part_number) = UPPER(compat->>'original_part_number')
|
|
JOIN vendors vcomp ON vcomp.id = comp.vendor_id AND vcomp.is_competitor = true
|
|
WHERE fx.fx_compatibilities IS NOT NULL
|
|
AND compat->>'original_part_number' IS NOT NULL
|
|
AND length(trim(compat->>'original_part_number')) >= 4 -- ignore very short/empty OPNs
|
|
AND compat->>'compatible_to_vendor' NOT IN ('MSA Standard (Default)', 'Flexoptix')
|
|
-- Skip pairs that already have ANY equivalence (approved, auto_approved, rejected)
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM transceiver_equivalences e
|
|
WHERE e.flexoptix_id = fx.id
|
|
AND e.competitor_id = comp.id
|
|
)
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- ── Statistics ────────────────────────────────────────────────────────────────
|
|
DO $$
|
|
DECLARE
|
|
new_cnt INTEGER;
|
|
fx_covered INTEGER;
|
|
comp_covered INTEGER;
|
|
total_approved INTEGER;
|
|
BEGIN
|
|
SELECT COUNT(*) INTO new_cnt
|
|
FROM transceiver_equivalences WHERE 'opn' = ANY(match_basis);
|
|
|
|
SELECT COUNT(DISTINCT flexoptix_id) INTO fx_covered
|
|
FROM transceiver_equivalences WHERE 'opn' = ANY(match_basis);
|
|
|
|
SELECT COUNT(DISTINCT competitor_id) INTO comp_covered
|
|
FROM transceiver_equivalences WHERE 'opn' = ANY(match_basis);
|
|
|
|
SELECT COUNT(*) INTO total_approved
|
|
FROM transceiver_equivalences WHERE status = 'auto_approved';
|
|
|
|
RAISE NOTICE 'Migration 116 complete: OPN-Based Equivalence Matcher';
|
|
RAISE NOTICE ' New OPN equivalences inserted: %', new_cnt;
|
|
RAISE NOTICE ' FX products covered: %', fx_covered;
|
|
RAISE NOTICE ' Competitor products matched: %', comp_covered;
|
|
RAISE NOTICE ' Total auto_approved: %', total_approved;
|
|
END $$;
|