Product Complementarity Engine — Tech-Spec Layer + v2 Merge (v1)

# Product Complementarity Engine — Tech-Spec Layer + v2 Merge (v1)

Design spec — 2026-07-01. Builds on [2026-06-08 Compléments v2](2026-06-08-product-complements-v2-design.md).

1. Purpose & success criteria

Add a persistent, catalog-level product-complementarity attribute to Lyréco's products: for a given product, the set of products likely to be functionally complementary (accessories / consumables / required companions) — driven primarily from structured technical characteristics ("requires AA batteries", "has RJ-45", "compatible printer = HP M404"), and completed by the existing co-purchase / family engine where tech specs don't apply.

Business goal: complete Marie's complementarity coverage. Only 5,458 of her 18,183 in-scope products have complements today; v1 fills the ~12,725 still missing.

Success (v1):

Every in-scope anchor is tagged complement_scope = 'marie-2026-06-25' on the product table.
For the qualifying subset (products with usable tech-spec / co-purchase signal), a

ranked complement list is produced and stored (table + JSON column) and exported in Marie's wide format so it merges into her file.

The tech-spec "semantic layer" (label→signal map) is human-reviewable, not a black box.
Deterministic + cheap: LLM used only at label grain and for the co-purchase/family

fallback — never a per-product LLM call over the catalog.

Non-goals (v1): WISE/GB (attribute table exists for WISE — a later country swap); webshop UI integration; replacing the v2 engine (this reuses it).

2. Scope & inputs

| Input | Source | Role | |---|---|---| | Anchor scope (18,183) | imports/marie/Datas pour produits associés 20260625.xlsx (SAP code, web title, HTML descriptif, brand, mfr code, GTIN×5) | products to enrich | | Already-linked (5,458) | imports/marie/Produits complémentaires déjà liés 20260526.xlsx (wide: SAP + 20×[Consumable Web, Local SAP Description, Local Status, Display Sequence]) | dedup + ground-truth + output shape | | Tech specs (~2.1M FR rows) | Oracle FRANCE.T_PRODUCT_ATTRIBUTE (PRODUCT_REFERENCE, ATTRIBUTE_LABEL, ATTRIBUTE_VALUE, VALUE_UNIT, LANGUAGE_CODE, DL_DELETION_FLAG) | Type A/B signal | | Free-text fallback | T_PRODUCT_ATTRIBUTE label Technical Details; T_PRODUCT_MASTER.WEB_EXTENDED_DESCRIPTION (CLOB) | battery-type + long-tail | | Co-purchase / family | product_co_occurrence + family-LLM graph (v2 engine, core/ai_typing.py) | fallback for non-device anchors | | Sales (rank) | timescale ecom_order_lines, 365d (reused from v2) | rank complements by sales | | Family master + status | ecom_products (family_code, subcategory_code, brand, not_salable/not_visible, status) | family top-N + gating |

Join key: anchor SAP code (plain, e.g. 5978056) → product_reference = LPAD(sap,18,'0') for FR (per data-lineage: FR products keyed on 18-digit padded reference, not LPAD-10). Country: source_country='FR' / COUNTRY_CODE='FR'. Hygiene on Oracle pull: DL_DELETION_FLAG='N' (+ standard scope filters where present).

3. Signal lanes & merge

Per anchor, produce candidate complements from three lanes, in priority order:

1. Type A — tech-spec direct compatibility (exact SKU links). Highest priority. 2. Type B — tech-spec requirement→family (family top-N). 3. Fallback — co-purchase + family-LLM (v2 engine), for anchors with no tech signal.

Merge algorithm (pure): concatenate lanes in priority order → dedup by complement_ref (first lane wins) → dedup against the anchor's existing déjà liés links → apply gates (is_in_perimeter, active, brand_compatible for durables) → cap at N=20 → assign display_sequence 1..N. Each surviving row records its signal_type (A_compat | B_family | cooc | family_llm) and signal_label (e.g. Compatible Printer, Sheet Format=A4) for auditability.

4. Label → signal map (the semantic layer, built once)

A versioned config (config/complement_label_map.json) mapping each relevant ATTRIBUTE_LABEL (bilingual FR/EN) → a rule:

`json { "Compatible Printer": {"signal": "A_compat", "match": "printer_model"}, "Compatible OEM Part Number": {"signal": "A_compat", "match": "oem_ref"}, "Compatible OEM Brand": {"signal": "A_compat", "match": "oem_brand"}, "Maximum Screen Size Supported": {"signal": "A_compat", "match": "screen_size"}, "Batteries Included": {"signal": "B_family", "target": "battery", "needs_type": true}, "Alimentation": {"signal": "B_family", "target": "battery", "needs_type": true}, "Cable Type": {"signal": "B_family", "target": "cable"}, "Connector on First End": {"signal": "B_family", "target": "cable"}, "Sheet Format": {"signal": "B_family", "target": "paper", "by_value": true}, "Host Interface": {"signal": "B_family", "target": "cable_hub", "when_value": "USB"} } `

Built once from the label vocabulary (~few hundred distinct labels; top labels

already surveyed). LLM-assisted to classify/extend ambiguous labels, but the output is a static, human-reviewable file — no per-product LLM.

target names map to (sub)family_code sets resolved from ecom_products (a small

target → family/subcategory lookup, also in config).

Ignored labels (Marque, CO2, packaging, warranty…) are simply absent from the map.

5. Resolution

5a. Type A — compatibility graph + directionality

Consumables declare their fit (Compatible OEM Part Number = CE259A, Compatible Printer = HP LaserJet Pro M404). Build a compat index across the catalog keyed on normalized oem_ref → GTIN → manufacturer_code → model_text.

Directionality (emit the device→consumable direction as primary):

anchor = device (printer): complements = consumables whose compat index matches

this device (reverse lookup on model / OEM ref).

anchor = consumable (cartridge): complements = the device(s) it fits (+ optionally

co-fitting consumables, flagged lower priority).

Match precedence: OEM part-number (most reliable) → GTIN → manufacturer code → model text (fuzzy, last resort). brand_compatible gate applies for captive/durable pairs.

5b. Type B — requirement → family top-N

requirement label (+value) → target (sub)family → top products via rank_family_skus (sales-desc, perimeter/active-gated) composed with the Community 5-SKU selector shape (3 main-family + 2 adjacent). Examples: Sheet Format=A4 → A4 paper subcat; Batteries Included=Yes + type → AA/AAA battery subcat; Cable Type=RJ-45 → network-cable subcat.

Battery-type fallback: when a battery/power label is present but the value doesn't name the type, parse Technical Details / WEB_EXTENDED_DESCRIPTION for \bAA\b|\bAAA\b|LR6|LR03|\b9\s?V\b|CR20\d\d to pick the correct battery subfamily; if still unknown, fall back to the generic battery family.

5c. Fallback — co-purchase / family-LLM

For anchors that yield no Type A/B candidates, reuse the v2 engine: product_co_occurrence lift (build_subcat_cooc_map) + the family-LLM complement graph (core/ai_typing.py), expanded to SKUs via assemble_anchor_blocks. Same gates, same cap.

6. Storage & outputs

1. Scope flag — ecom_products.complement_scope TEXT set to 'marie-2026-06-25' for all 18,183 anchors (named, so multiple lists coexist; enables coverage tracking). 2. Source of truth — new Postgres table product_complements: `anchor_ref, complement_ref, complement_name, family_code, signal_type, signal_label, rank, source_country, generated_at (PK (anchor_ref, complement_ref, source_country)`). Auditable + reverse-queryable ("what needs AA?"). 3. Catalog column — JSON struct view/materialized column on ecom_products: the full ranked list (up to N=20), [{"name","sku","family_code","signal","rank"}] (the "struct column on products"). The 5-SKU offer (3 main + 2 adjacent) is a downstream slice of this list, not a separate store. 4. Marie deliverable — wide-format XLSX via existing WIDE_HEADER/build_wide_rows, so it merges into Produits complémentaires déjà liés.

7. Modules, refresh flow, testing

core/complement_specs.py (new, pure, no I/O): apply label map; build + resolve

Type A compat graph; resolve Type B; battery-type parse; merge/dedup/cap/rank. TDD.

config/complement_label_map.json — versioned label→signal map + target→family lookup.
scripts/refresh_product_complements_specs.py (new orchestrator):

read anchors → dedup vs déjà-liés → pull T_PRODUCT_ATTRIBUTE (+ free-text) → pull sales + family products → run A/B lanes → v2 fallback → merge → write complement_scope flag + product_complements + JSON column + Marie XLSX.

Reuses: core/complement_families.py (rank_family_skus, assemble_anchor_blocks,

build_wide_rows, WIDE_HEADER, is_in_perimeter, brand_compatible), core/ai_typing.py, scripts/refresh_product_complements_v2.py machinery.

DB migration: create product_complements; add ecom_products.complement_scope;

create JSON view.

tests/test_complement_specs.py (TDD): label-map application; Type A directionality &

match precedence (OEM ref > GTIN > model); Type B family resolution + battery-type parse; merge priority/dedup(-vs-existing)/cap/rank; perimeter/brand gating.

8. Open questions / to confirm during planning

Anchor refresh of the existing 5,458: v1 leaves them as-is (dedup only). Confirm we

don't re-derive/overwrite Marie's curated links.

Type A coverage: measure how many of the 12,725 missing anchors carry qualifying

compat/requirement attributes (quantifies device-subset reach vs fallback reliance).

complement_scope vs bare flag: named tag chosen; confirm value convention

(marie-2026-06-25).