sale.order.line with display_type='line_note'. Zero price, zero qty — used as a section header or label (e.g., "IL01", "alternativa"). Not a product line.sale.order in Odoo.Five decisions before Phase 1 starts:
aronlight-quoting-agent), or build inside this research repo? The research scripts (01–06) live here and can be ported directly to Django.The problem: Aronlight receives ~3,000 RFQ emails per month. Half list competitor product codes (Philips, iGuzzini, KATOA) with "ou equivalente." Each rep manually looks up competitor specs, finds the closest Aronlight product, and builds the quote in Odoo line by line. A complex RFQ takes 30-60 min. It is the bottleneck.
What the Adaptto AI Quoting Agent does: Takes the input — email, Excel, PDF, or image — extracts the product line items (regardless of how they are described: Aronlight SKU, Aronlight product name, competitor model, or vague description), finds the right Aronlight product for each line, and generates a draft Odoo sale.order ready for the sales rep to review and approve. The rep goes from reading the email to clicking "Create a proposal in Odoo" in under 5 minutes.
What needs to be taken into consideration: Inputs arrive in multiple formats — email body, Excel BOQ, PDF specification, or image. Each needs a different extraction path before we even start matching. Inside those inputs, customers write SKUs inconsistently ("ILAR01691", "ilar 1691", "IL-AR-01691") vs the catalog format ("ILAR-01691"). Many inputs are also BOQs (Bill of Quantities — see Q5), where one customer line expands to 2-3 Odoo order lines. All three — format handling, SKU normalisation, and BOQ expansion — affect matching accuracy before we even reach the spec comparison step.
What's been verified (this repo):
The three hardest problems:
categ_id="All" with specs buried in free text. This limits automated spec matching until Manel recategorizes them.What is a BOQ? A Bill of Quantities (BOQ) is the document an architect or contractor sends to suppliers when specifying lighting for a construction project. It lists numbered positions — one per fixture type — with a quantity, a competitor brand+model reference, and sometimes specs. Example: "Position 2 — Philips DN142B, 110 units, round recessed downlight, IP20, 3000K, ou equivalente." The BOQ is not an order; it is a specification. Aronlight's job is to propose equivalent products for each position and quote a price. A single BOQ position typically becomes 2-3 lines in Odoo (luminaire + driver + accessory).
Customer email / Excel / image
│
▼
┌─────────────────────────┐
│ Parse input │ Extract line items + detect format
│ (Claude API) │ (email / Excel / PDF / image)
│ │ Attachment-only → request file
└─────────────────────────┘
│
▼
┌─────────────────────────┐
│ Match client │ Email exact → VAT → fuzzy name (WRatio)
│ │ ≥75% auto · <75% ⚠ human confirms
└─────────────────────────┘
│
▼
┌─────────────────────────┐
│ Match each line item │ Exact SKU → alias table → fuzzy name
│ │ → spec match on catalog text
│ │ ≥90% auto · 60-89% ⚠ review · <60% ✗
└─────────────────────────┘
│
▼
┌─────────────────────────┐
│ BOQ expansion │ 1 input line → N Odoo lines
│ │ luminaire + driver (DALI?) + accessory
│ │ + emergency (ILEM-?) per product family
└─────────────────────────┘
│
▼
┌─────────────────────────┐
│ Human review │ Sales rep sees flagged items, confirms
│ (5 gates) │ or corrects. Nothing writes to Odoo yet.
└─────────────────────────┘
│
▼
┌─────────────────────────┐
│ Odoo write │ draft sale.order + sale.order.line
│ (on approval only) │ position labels as line_note
└─────────────────────────┘
│
▼
Sales rep sends
quote to customer
Input types handled:
| Type | Description | Volume | Claude needed? |
|---|---|---|---|
| A | Customer uses Aronlight SKU directly | ~5% | No — regex |
| B | Customer uses Aronlight product name, no SKU | ~15% | Yes — extraction |
| C | Competitor reference + specs, "ou equivalente" | ~50% | Yes — spec match |
| D | Conversational / advisory request in prose | ~10% | Yes — full NLP |
| E | Attachment only, nothing in email body | ~32% | No — escalate |
Question: Can we connect and get the full product catalog?
Answer: YES — clean data, ready for production.
Findings (01_catalog_access.py on staging):
catalog.json (sale_ok=True, active=True). Note: brief previously stated 4,416 — the discrepancy of 92 is unexplained and should be verified against production before build.default_code) — no null or empty valuescateg_id) — 100% coveragePRJP- (223 — likely bespoke project products), PRJE- (81), ARDV- (31), PROD- (17), MKT- (12), and others. The matching engine will not recognize these without explicit handling.list_price (EUR, PVP)[id, "Category Name"] tupleCRITICAL CATALOG GAP (from Raffaello's case analysis):
categ_id = "All" — a catch-all category with no useful signalip_rating, dali, rated_power_w, led_colour_temperature_k, dimensions, …) plus 19 product.attribute records — but they are virtually unpopulated (see callout below). Specs live as free text in the product name.⚠️ Verified 2026-05-31 — structured spec fields exist but are 0% populated
The schema is well-designed; it is simply not filled in. Across 4,416 active products:
| Field | Products with a value |
|---|---|
ip_rating | 2 (0.0%) |
dali | 0 (0.0%) |
rated_power_w / max_led_power | 1 / 0 |
led_colour_temperature_k | 1 |
dimensions / width_mm / height_mm | 1 / 0 / 0 |
colour_rendering_index_cri | 1 |
Decisive contrast: the dali field is populated on 0 products, yet 229 active products mention "dali" in the name. The data exists — only in free text.
Design impact: the matching engine must parse specs from the product name (regex + LLM); it cannot rely on structured attributes. This is more fragile (depends on naming consistency across PT/ES plus abbreviations) and is the single most important constraint for the Q4 SKU-matching design.
Latent opportunity: if Aronlight ever populates these fields (manually, or via a one-time name→field extraction), matching upgrades from "parse fragile prose" to "exact field filter" — high-value, but not available today. Build assuming free text. (Full detail: answered-questions.md, Q-002 / Q-033.)
Staging reliability warning:
customer_rank=0 on accounts with real purchase history (causes false negatives when filtering partners)XML-RPC auth pattern (reuse in Django):
import xmlrpc.client
common = xmlrpc.client.ServerProxy(f"{ODOO_URL}/xmlrpc/2/common")
uid = common.authenticate(DB, USER, PASSWORD, {})
models = xmlrpc.client.ServerProxy(f"{ODOO_URL}/xmlrpc/2/object")
uid is stable within a session — cache it
Multi-company scope:
company_id=3, Aronlight ES = company_id=2Django model recommendation:
class OdooProduct(Model):
odoo_id = IntField(unique=True)
sku = CharField(max_length=50, unique=True) # ILAR-XXXXX or ILDV-XXXXX
name = TextField()
odoo_category = CharField(max_length=200)
norm_category = CharField(max_length=50) # see Q1b
list_price_eur = DecimalField()
company_id = IntField() # 2=ES, 3=PT
raw_description = TextField() # for spec text-parsing
synced_at = DateTimeField()
Gaps:
categ_id = "All" — needs resolution with Maneldali=0, ip_rating=2 of 4,416) → spec matching must parse the product name. See callout above.res.currency modelQuestion: How do we create a normalized category schema for matching?
Answer: YES — 18-category EN schema covers 89% of parseable catalog. Real gap is ~43%.
What the schema is: Aronlight's Odoo uses Portuguese category names ("Tiras LED", "Painéis LED", "Lâmpadas"). The 18-category schema is a static mapping table: Odoo category name → EN key, with keyword fallback on the product name. This translation runs at sync time so Django always stores a consistent English key regardless of Odoo's internal naming.
Findings (02_categorization.py on staging):
18 Normalized Categories (EN):
| Category | Products | Description |
|---|---|---|
| BULBS | 1,098 | A60, G45, GU10, G9, G4, filament, flame |
| LED_STRIPS | 702 | Strip lights, neon flex, RGB |
| DOWNLIGHTS | 583 | Recessed spots, commercial ceiling |
| OUTDOOR_SPOTLIGHTS | 342 | Floodlights, IP65+ projectors |
| ACCESSORIES | 318 | Connectors, profiles, optics |
| DRIVERS_POWER | 248 | Drivers, power supplies, DALI |
| SMART_CONTROL | 207 | WiFi/Zigbee/RF controllers, MiBoxer |
| PANELS | 175 | LED flat panels, slim panels |
| TUBES | 118 | T8/T5 LED tubes |
| INDUSTRIAL | 86 | High-bay, ATEX, linear armatures |
| SENSORS_DETECTORS | 56 | PIR, microwave motion sensors |
| EMERGENCY | 31 | Emergency lighting, exit signs |
| DECORATIVE | 28 | Pendants, domestic decorative |
| OUTDOOR_WALL | 19 | Exterior wall lights |
| SOLAR | 5 | Solar-powered fixtures |
| HORTICULTURE | 5 | Grow lights |
| STREET_LIGHTING | 4 | Street / public lighting |
| TRACKS_RAILS | 5 | Track systems + accessories |
| UNCATEGORIZED | 494+ | Needs manual review (real number likely higher) |
Recommendation for Riccardo: store norm_category on the cached product. Run the mapping at sync time, not at query time. Ship without the "All" category products first — ask Manel to recategorize them in Odoo as a precondition for full coverage.
Question: Can we match a client name from email/Excel to a partner in Odoo?
Answer: YES — 100% match rate on 7 real PT/ES company name variants. Key fix: drop customer_rank filter.
Findings (03_client_matching.py on staging):
customer_rank — only 36% of partners have rank > 0, yet 136 of the 852 partners with real purchase orders have customer_rank=0. The "1,479 customers" figure from script 03 was exactly the count with rank > 0 — it excludes a third of real buyers.CRITICAL FIX from Raffaello (match-orcamento skill):
customer_rank > 0 filter causes false negatives — real accounts with purchase history have customer_rank=0 in stagingMatch strategy (working code in 03_client_matching.py):
res.partner.email (most reliable — use first)res.partner.vat (instant, skip fuzzy)rapidfuzz.fuzz.WRatio on normalized names (primary)rapidfuzz.fuzz.partial_ratio on original names (fallback)Match tiers for the UI:
Quote matching (from Raffaello's /match-orcamento skill):
After client match, rank existing sale.order records by quantity signature overlap to identify the "live version" (latest revision) vs. confirmed sales (state=sale). Useful for detecting if an RFQ is a revision of an existing quote.
Django model:
class OdooPartner(Model):
odoo_id = IntField(unique=True)
name = CharField(max_length=200)
vat = CharField(max_length=50, null=True)
email = EmailField(null=True)
company_id = IntField() # 2=ES, 3=PT
synced_at = DateTimeField()
Question: Can we extract SKUs + quantities from all 3 input types?
Status: COMPLETE — 19 real RFQ emails collected, parsed, and matched. All 5 scripts validated.
Real input type taxonomy (from 19 Aronlight RFQ emails + 6 Raffaello case studies — PT/ES market):
Example: Solicitud saltoki (Saltoki, 2026-03-05)
ILAR-\d+, ILDV-\d+)Examples: Precios Excelsior, Pedido de proposta Carolina Arquitecta (BERG/CUBE/OTTO/LUPO names)
*Examples: Philips (AlpLuz, Alternativas), Aura Light (Pedido URGENTE), Karizmaluce/Castaldi (Centro Náutico),
KATOA/CLIMAR (Centro Saúde Chaves), Performance in Lighting (Editora Barcelos), Iguzzini (San Juan DALI),
Philips BVP527 (Proyectores Campo Futbol), Duralamp (Biobanco)*
Example: Ajuda técnica (Manuel & Sónia, 2026-05-07)
*Examples: Hospital Pediátrico Saurimo ("ver anexos"), Proposta LUMIS DST SA, REQ IP Bragança,
APPACDM Sabrosa ("mapa em anexo"), Metro Madrid (LDV quote), RESIDENCIA DALI (DWG files),
Biobanco Azul (Lum_A01.x codes), Edifício Editora Barcelos (Performance in Lighting)*
Parser validation results (04_input_parser.py against real emails):
| Type | Items extracted | Unclear flags | |
|---|---|---|---|
| A | Saltoki (ILAR- SKUs) | 4 | 2 (ambiguous controller choice, accessory qty) |
| C | AlpLuz (Philips refs) | 9 | 3 (accessory quantities inferred) |
| D | Ajuda técnica | 4 | 1 (ILAR-01993 used twice, role unclear) |
| E | Hospital Saurimo | 1 | 1 (entire list in attachment) |
Aronlight-specific signal patterns:
SKU pattern: /ILAR-\d{5}/ or /ILDV-\d{5}/
Quantity patterns: "(\d+)\s*(un|uni|uds|uds\.|m|ml|mts|metros)"
Competitor brands: Philips, Osram, Aura Light, Karizmaluce, Castaldi, Encapsulite, Borealis,
Schuch, Iguzzini, KATOA, Soneres, Lucens, Schréder, LDV
Spec signals: W (wattage), K (color temp), lm (lumens), IP (protection), IK (impact),
mm/cm (dimensions), deg (beam angle), CRI/Ra (color rendering)
Question: How do we match input SKU (customer's code) to Aronlight's Odoo SKU?
Answer: PARTIAL — exact/fuzzy SKU works, competitor-ref-to-Aronlight-SKU requires a new layer.
Validation results (05_sku_matching.py against real parsed emails):
| Input type | Items | Exact match | Fuzzy match | No match |
|---|---|---|---|---|
| Type A (ILAR- direct) | 4 | 3 (100%) | 0 | 1 (ambiguous) |
| Type C (Philips refs) | 9 | 0 | 0 | 9 (100%) |
| Type B (Aronlight names) | 10 | 0 | 1 (64% — BERG→Trimless) | 9 |
Match tiers:
Match register (data/sku_match_register.json):
Every match is logged. Over time, customer SKU → Aronlight SKU mappings accumulate.
This register becomes the translation layer — no re-matching needed for repeat customers.
Critical gap — Type C (50% of all requests):
Competitor refs (Philips RS150B, Iguzzini, KATOA) return 0 matches because script 05 only
does name/SKU string matching. The Philips specs ARE in the extracted items
(IP65, D78mm, 7.2W, 3000K) but there's no spec-field comparison against the Aronlight catalog.
Required for production — three additional components:
Question: Does 1 BOQ line = 1 Odoo order line?
Answer: NO. Verified with real Aronlight quote PDFs from Odoo staging.
A customer sends a BOQ with numbered positions — e.g., 7 positions (one per product type). When Raffaello pulled the actual Aronlight quote for that RFQ from Odoo, it had 17 lines. But 8 of those 17 are display_type section headers (no product, no price) — not real order lines. Real product lines: 9. Expansion: ~1.3× per position, not 2.4× as previously stated. The 2.4× figure was an artefact of counting section headers as product lines.
Each position expands depending on what the product needs:
Basic — luminaire only (no accessories, no DALI):
[line_note] "10.4.5.1.1 IL01" ← position label, price=0, qty=0
[product] ILAR-01108 ← luminaire
With a decorative accessory (e.g., wall light + ring):
[line_note] "10.4.5.1.5 IL05"
[product] ILAR-02971 ← luminaire (RUMU)
[product] ILAR-02976 ← decorative ring, same qty as luminaire
With two alternatives (specs don't match exactly):
[line_note] "10.4.5.1.4 IL04"
[product] ILAR-00960 ← main option (Fisher IP54)
[line_note] "alternativa"
[product] ILAR-02683 ← alternative (Noa IP65)
DALI project — adds a driver line per position:
[line_note] position code
[product] ILAR-XXXXX ← luminaire
[product] ILDV-XXXXX ← driver (SKU selected by wattage range, reused across positions)
[product] ILEM-XXXXX ← emergency unit if required (distinct prefix)
What this means for the data model:
RfqLineItem (one per customer input line) is not the same as an Odoo order lineRfqOdooLine model: each RfqLineItem can generate N Odoo linesILEM- prefix = emergency unit (distinct from ILAR- luminaires and ILDV- drivers)Product dependency table — design requirement for Riccardo:
Some products are not sold standalone. When the system proposes product X, it must also add product Y. Three types observed:
| Type | Trigger | Companion product | Example |
|---|---|---|---|
| Mandatory accessory | Always | Decorative ring, bezel | RUMU wall light → ring |
| DALI driver | If project specifies DALI | ILDV- driver (selected by wattage range) | Any ILAR- luminaire on a DALI project |
| Emergency unit | If project requires maintained emergency | ILEM- unit | Any luminaire in a public/regulated space |
Critical: these rules are not in Odoo today. The accessory_product_ids field in product.template exists but is almost empty (2 of 4,324 products populated). The expansion rules live in the team's tacit knowledge, not in any structured data source. Manel must define them before the system can generate correct Odoo quotes.
Design implication: the system needs a product dependency table in the database — configurable by Manel or the sales team, not hardcoded. Structure: (product_sku, companion_sku, dependency_type, trigger_condition). Without it, the system can match the right luminaire but will generate an incomplete quote that a sales rep must manually correct every time.
Unit normalization gap:
Key insight: wattage is NOT a hard filter for competitor substitution.
Commercial staff accept wide tolerance: iGuzzini case showed +108% wattage deviation accepted; Philips cases ranged from +11% to +140%. The actual matching priority for competitor substitution is:
Implication for spec matching: filter first by category + dimensions + IP, then rank by lm proximity. Never reject a candidate solely for wattage mismatch.
"No equivalent" is a valid system output.
Non-automatable cases detected in real RFQs:
System must detect these and route to human with explicit "no equivalent found" status — not leave them as unclear.
Alternative proposals pattern (from Raffaello's case 02):
When specs don't match exactly, commercial staff propose:
System should generate paired candidates (primary + fallback), not force a single match.
RfqLineItem status values (revised):
STATUS_CHOICES = [
("auto", "Auto-matched — no review needed"),
("review", "Proposed match — sales rep confirms"),
("unclear", "Needs customer clarification"),
("no_match", "No Aronlight equivalent found"),
("non_automatable", "Requires human expert + photometric docs"),
]
Django models needed:
OdooProduct # cached catalog (sync daily via management command)
OdooPartner # cached customers (sync daily)
ProductAlias # short name → OdooProduct (BERG, CUBE, OTTO, PRADA, LUPO, NEXOR)
BrandLookup # competitor brand prefix → canonical brand name (fix "OH" problem)
SkuMapping # customer SKU / product name → Aronlight SKU register (cumulative)
RfqRequest # inbound request: source, raw content, parsed items, status
RfqLineItem # parsed line + match result + status (auto/review/unclear/no_match/non_automatable)
RfqOdooLine # expanded Odoo line per RfqLineItem (1 RFQ line → N Odoo lines)
Odoo API call pattern:
product.template + res.partner → local DB (no live API during matching)company_id=2 (ES) and company_id=3 (PT) — tag origin on each recordsale.order + sale.order.line only after human approvalInput pipeline:
Email / Excel / Image
↓
Classify input type (A/B/C/D/E/BOQ) — per-line, not per-email
↓
04_input_parser.py logic (Claude API for Type B/C/D)
↓
Structured items: [{description, qty, unit, sku_hint, specs, confidence}]
↓
Unit normalization (m→rolls, etc.)
↓
05_sku_matching.py logic (email → VAT → fuzzy name → spec match)
↓
BOQ expansion: 1 line → [luminaire, driver, accessories, emergency]
↓
RfqLineItem with status: auto | review | unclear | no_match | non_automatable
↓
Human gate → Odoo draft sale.order
Human-in-the-loop gates (5 mandatory):
unclear item → stop, show clarification question for customerno_match or non_automatable item → stop, route to senior salesClaude API usage in production:
Pre-built skills from Raffaello (reuse these):
/assess-email — RFQ decoder, attachment inventory, input type classifier/research-competitor-refs — competitor spec extraction + product image sourcing/match-orcamento — client fuzzy-match + quantity signature ranking (includes customer_rank bug fix)/add-question — disciplined Q-bank capture with relevance filterA sales rep gets an RFQ email. Here's what they do in the agent from start to finish:
Screen 1 — Input
┌─────────────────────────────────────────────────────────────┐
│ Adaptto AI Quoting Agent Aronlight PT │
├─────────────────────────────────────────────────────────────┤
│ │
│ [Paste email text] [Upload Excel] [Upload image/PDF] │
│ │
│ ┌───────────────────────────────────────────────────────┐ │
│ │ Subject: Pedido cotação — AlpLuz — Philips refs │ │
│ │ From: alpluz.lda@gmail.com │ │
│ │ ... │ │
│ └───────────────────────────────────────────────────────┘ │
│ │
│ [Parse →] │
└─────────────────────────────────────────────────────────────┘
Screen 2 — Review: client + line items
┌─────────────────────────────────────────────────────────────┐
│ Adaptto AI Quoting Agent Aronlight PT │
├──────────────────────────────────┬──────────────────────────┤
│ CLIENT │ 7 items · 2 need review │
│ AlpLuz Lda ✓ 98% match │ │
│ partner_id 148126 │ [Create in Odoo ▸] │
├──────────────────────────────────┴──────────────────────────┤
│ # Input Match Status │
│ ──────────────────────────────────────────────────────── ─ │
│ 1 RS150B · 33 un · IP65 3000K Luso 8W ✓ auto │
│ 2 DN142B · 110 un · IP20 3000K Una II 15W ⚠ review│
│ 3 RC132V · 22 un · 300×1200mm Painel Backlit ✓ auto │
│ 4 WL055V · 22 un · wall IP65 Fisher IP54 ⚠ review│
│ + Noa IP65 alt │
│ 5 WL140V · 22 un · circular RUMU + Aro Preto ✓ auto│
│ 6 WL140V · 11 un · no ring RUMU ✓ auto │
│ 7 WT120C · 11 un · 600mm IP65 TITAN 1200 ✓ auto │
└─────────────────────────────────────────────────────────────┘
Screen 3 — Review a flagged item
┌─────────────────────────────────────────────────────────────┐
│ ← Review item 1 of 2 │
├─────────────────────────────────────────────────────────────┤
│ Customer wrote: "DN142B 110 un" │
│ Competitor: Philips DN142B · 9.8W · IP20 · 3000K │
│ round downlight · recessed │
│ │
│ Proposed match: Una II ILAR-01571 │
│ 15W (+53%) · IP65/44 · 3CCT · €29.90/un │
│ Match score: 78% │
│ ⚠ Wattage over-spec · IP upgraded │
│ │
│ [✓ Accept] [Search catalog] [Mark unclear] [No match] │
└─────────────────────────────────────────────────────────────┘
Screen 4 — Odoo preview before write
┌─────────────────────────────────────────────────────────────┐
│ Odoo Preview — AlpLuz Lda draft │
├─────────────────────────────────────────────────────────────┤
│ [note] IL01 │
│ ILAR-01108 Luso 8W D80 IP65 ×33 € 962.61 │
│ [note] IL02 │
│ ILAR-01571 Una II 15W 3CCT IP65/44 ×110 €3,289.00 │
│ [note] IL03 │
│ ILAR-02979 Painel 300×1200 Backlit ×22 € 781.00 │
│ [note] IL04 │
│ ILAR-00960 Fisher 10W IP54 ×22 €1,031.36 │
│ [note] alternativa │
│ ILAR-02683 Noa 12W 3CCT IP65 ×22 € 658.68 │
│ [note] IL05 │
│ ILAR-02971 RUMU 9-16W Switch ×22 € 517.00 │
│ ILAR-02976 Aro Bezel Preto ×22 € 46.64 │
│ [note] IL06 │
│ ILAR-02971 RUMU 9-16W Switch ×11 € 258.50 │
│ [note] IL07 │
│ ILAR-02135 TITAN 1200 IP65 36W ×11 € 471.35 │
│ ─────────────────────────────────────────────────────── │
│ Total €4,732.73 │
│ │
│ [✓ Create in Odoo] [Export PDF] [← Back to review] │
└─────────────────────────────────────────────────────────────┘
The Odoo preview is the real quote structure — position labels as line_note, accessories on their own line, alternatives separated by an "alternativa" note. Exactly what Aronlight's sales team produces manually today.
Findings that must be respected regardless of implementation choices:
customer_rank — 36% of real buyers have customer_rank=0 in staging. Use email exact match → VAT exact match → fuzzy name as the chain instead.name free text — the structured spec fields (ip_rating, dali, rated_power_w, etc.) exist in the Odoo schema but are 0% populated across all 4,416 products. There is no field-query shortcut.company_id=2 (ES) and company_id=3 (PT). Partners are shared; sales reps sometimes create quotes in the wrong entity. Tag origin on every cached record, never filter by company during matching.sale.order. The agent proposes; the sales rep approves. Hard gate, not a preference.accessory_product_ids field is almost empty (2 of 4,324 products). The system needs a configurable product dependency table (product_sku → companion_sku + type + trigger). Without it, the system can match the right luminaire but cannot generate a complete Odoo quote. Manel must supply the rules; the data model must exist before Phase 4.Research is done. Here's the recommended build sequence, scoped to what's verified and unblocked.
| Phase | Can start immediately | Must wait for Manel answer |
|---|---|---|
| Phase 1 — catalog sync | Yes — Django + Odoo connection fully verified | Nothing blocking Phase 1. Note: 1,874 products sit under categ_id="All" with no useful signal — spec matching in Phase 3 will have a ~43% blindspot until these are recategorized in Odoo. |
| Phase 2 — parsing + client matching | Yes — all 5 input types defined, client match logic tested on 19 real cases | Nothing blocking. |
| Phase 3 — SKU matching | Type A (exact SKU) and Type B (Aronlight name) can ship | Q 1-2 (equivalence thresholds — IP, lm, wattage tolerance), Q 1-6 (IP upgrade acceptable?), Q 2-1 (DALI default), Q 2-2 (dimension vs wattage priority). Without these, Type C (50% of volume) matching rules are guesses. Note: spec field format is confirmed — fields exist in schema but are 0% populated; matching must parse product name free text. |
| Phase 4 — BOQ expansion + Odoo write | Nothing — do not build until Manel answers Q 1-8, 1-9, 1-10 | Q 1-8 (what drives 1→N expansion), Q 1-9 (accessory as separate line or bundle), Q 1-10 (driver always separate line?). Getting these wrong means every Odoo write produces the wrong structure. |
Goal: Django talks to Odoo, local DB is populated, sync runs daily.
odoo_client.py already exists in this repo — port to Django settingssync_catalog — pulls product.template + res.partner for both company_id=2 and 3OdooProduct, OdooPartner, SkuMapping, ProductAlias, BrandLookupProductAlias with known short names from cases: BERG, CUBE, OTTO, PRADA, LUPO, NEXOR, LUKE, RUMU, LUSO, UNA, FISHER, NOA, TITAN — map to Odoo SKUs (ask Manel to confirm)categ_id="All" — needs Manel to recategorize in Odoo before sync is useful for spec matchingGoal: sales rep pastes an email, gets a client match and a list of extracted items.
04_input_parser.py into a Django async task (Celery + Claude API)03_client_matching.py)RfqRequest, RfqLineItemGoal: each line item gets a proposed Aronlight match; sales rep can accept, reject, or reassign.
05_sku_matching.py into Django matching serviceProductAlias → fuzzy name → spec text-parse on raw_descriptionSkuMapping register — repeat customers get instant matchGoal: approved matches become a real Odoo draft sale.order.
RfqOdooLine (N lines per RfqLineItem)sale.order + sale.order.line via XML-RPCline_note entries, "alternativa" separator for paired candidatesThese three are NOT decided. They need a call with Manel and Riccardo before Phase 1 starts.
| Decision | Option A | Option B | What it affects |
|---|---|---|---|
| Image/PDF parsing | Skip in MVP — email + Excel only | Include from day one | 20% of volume (Type E). A = faster ship, reps still handle image RFQs manually |
| Multi-company PT/ES | Start PT only, add ES later | Both from day one | Case 06 is ES. A = simpler build; B = only ~1 day extra if sync already tags company_id |
| Type C spec matching | Ship as "review" — manual assignment | Full auto spec match in v1 | 50% of all volume. A = faster ship but half the value proposition is still manual |
The case studies produced 47 competitor product datasheets across 10 cases in docs/email_samples/*/research/*.md. Each one is a structured record: competitor brand + model, specs (dimensions, W, lm, K, CRI, IP, IK, driver type, mounting), and — for cases 02, 04, 06 — the correct Aronlight match is in match_summary.md.
This gives us labeled pairs: (competitor specs) → (Aronlight SKU + reasoning).
Three ways to use them:
Parse all 47 datasheets into a JSON:
[
{
"competitor_ref": "Philips RS150B",
"specs": {"dimensions_mm": 78, "W": 7.2, "IP": 65, "K": 3000, "mounting": "recessed", "shape": "round"},
"correct_aronlight_sku": "ILAR-01108",
"correct_name": "Luso 8W D80 IP65"
},
...
]
Run the spec matching algorithm against all 43 before shipping. Target: ≥80% correct SKU without human intervention. Anything below is a signal to tune the scoring weights, not to ship.
When a new competitor ref arrives, send 2–3 similar examples as context in the Claude prompt:
Match competitor lighting products to Aronlight catalog items.
Example 1:
Input: Philips RS150B · 7.2W · IP65 · ø78mm · 3000K · recessed spot
Match: ILAR-01108 Luso 8W D80 IP65 · reason: ø78≈80mm, IP65, same category
Example 2:
Input: KATOA BOX-E · 42W · IP20 · 597×597mm · 4000K · recessed panel
Match: ILAR-XXXXX [Aronlight panel 600×600] · reason: panel format, dimensions match
Now match: [new competitor ref]
The examples anchor Claude to Aronlight's actual substitution logic (wattage tolerance, IP upgrade acceptable, dimensions primary). Without them, Claude defaults to generic LED knowledge which doesn't match how Aronlight commercial staff actually quote.
The 43 cases reveal that the right spec fields vary by product category — not one universal scoring function:
| Category | Hard filters (must match) | Ranking fields | Ignored |
|---|---|---|---|
| DOWNLIGHTS | cutting dimension ±5mm, mounting type | lm ±20%, K | wattage |
| OUTDOOR (IP65+) | IP rating (exact or higher), category | dimensions, lm | wattage |
| PANELS | panel size (600×600 vs 300×1200) | K, lm | wattage |
| DALI projects | DALI flag (hard) | lm, K | wattage, finish |
| LED_STRIPS | IP, K | W/m | total lumens |
Build the spec matcher as a category-aware scorer, not a flat field comparison. The category is always known from norm_category on OdooProduct.
All 13 RFQ cases were matched against Odoo staging using rapidfuzz + email/VAT/fuzzy-name strategy. Key findings for the build:
1. Email-to-partner mismatch (case 10 — Carolina Arquitecta)
carolinaportugalarq@gmail.com is assigned in Odoo to "Robin Kathuria" (VAT 312288743), not to Carolina Portugal. The email match returns the wrong partner with 100% confidence. The system would silently create a quote under the wrong customer. Action needed: data cleanup in Odoo + detect mismatch when VAT and email owner diverge.
2. VAT duplicate (case 11 — DELPA / Esfera de Fios)
Two different partners share VAT 514865881: DELPA ELECTRICPARTS LDA and Esfera de Fios. VAT is supposed to be a unique identifier in Odoo. Deduplication needed before VAT matching can be trusted for PT companies.
3. @aronlight.es email as CC (case 17 — Jordi Marco)
Every ES customer record in Odoo includes an @aronlight.es address as CC. When the sender is jordi.marco@aronlight.es, domain matching on aronlight.es returns the entire ES distributor network (40+ partners all score 100%). This breaks partner disambiguation for internally-forwarded requests. Action needed: detect @aronlight.es sender as internal channel (Q 2-16); route differently than external client emails.
| Case | Client found | Orders | Best overlap | Verdict |
|---|---|---|---|---|
| 07 Biobanco Azul | L3W (domain) | 40 | 38% | CANDIDATO DÉBIL |
| 08 Editora Barcelos | ARMASUL SA (domain) | 253 | 33% | PEDIDO NO LOCALIZADO |
| 09 Hospital Saurimo | RABISCOS ORDENADOS (exact) | 0 | N/A (E2) | SEM PEDIDOS — BOQ parsed: 2,527 units, 28 types, no brand refs. Critical gap: 658 ASEPTIC panels (A3 + A10R DALI for OR). Largest project in dataset. |
| 10 Carolina Arquitecta | Email mismatch → no client | — | — | NÃO ENCONTRADO |
| 11 LUMIS DST | DELPA (exact + VAT) | 43 | N/A (E2) | ENCONTRADO — BOQ parsed: 586× Hoff Light LIBERTAD_6 (6W IP44 recessed round Ø68mm cutout, CCT-switchable). LUMIS = project name, not brand. |
| 12 Metro Madrid | Francisco Soler (exact) | 1 draft (0 lines) | N/A | 1 PEDIDO DRAFT |
| 13 Ajuda técnica | MANUEL & SÓNIA (exact) | 236 | 100% (degenerate) | OVERLAP DEGENERADO |
| 14 Centro Náutico | RODEL (domain) | 145 | 27% | CANDIDATO DÉBIL |
| 15 Emergências ATEX | A.R.COSTA (exact) | 136 | 71% | ORÇAMENTO PROBABLE |
| 16 Pedido urgente | STRADA (domain) | 188 | 25% | PEDIDO NO LOCALIZADO |
| 17 Precios Excelsior | Aronlight internal CC flood | 40+ | — | CANAL INTERNO |
| 18 Proyectores futbol | SURELECTRIC (exact) | 1 | unrelated | PEDIDO NO LOCALIZADO |
| 19 Solicitud Saltoki | saltoki.es = 40+ branches | 6 | — | PRECISA VAT |
Degenerate overlap (case 13 — Manuel & Sónia):
OR 2026/9807 is an 85-line blanket order. The [1,2,2] quantity signature of the advisory request matches it at 100% — but it is meaningless because small-quantity signatures match almost any large order. Type D advisory cases need a different matching strategy: don't use qty signature at all, match by recency + client relationship instead.
NON-AUTO type finds active client (case 15 — A.R.Costa ATEX):
A.R.Costa is an active, high-value Aronlight client (136 orders, 71% overlap on OR 2026/11485). The system correctly identifies them even though the product request is out-of-scope (ATEX emergency lighting, brand Cronus, Schuch). The right behavior: always do client matching; flag the line items as NON-AUTO but don't drop the client identity.
Saltoki multi-entity (case 19 — ES distributor with 40+ branches):
Large ES distributors (Saltoki, Rexel, Sonepar) have one branch per city in Odoo. A contact-level email (mzambrano@saltoki.es, individual) doesn't appear in Odoo at all. Domain match returns all branches. Resolution requires VAT-level disambiguation or branch-specific email on the RFQ.
Staging recency gap:
Cases 16 and 18 were sent 2026-05-28 — the same day as the match run. Quotes in progress (Flávio / Ricardo working on them) are not yet in staging Odoo. The system will correctly return "no match" for live in-progress quotes. This is expected, not a bug.
All open business policy questions are collected and deployed at:
https://aronlight-questions.pages.dev
The document is organized in three batches:
Key Phase 3 blockers: Q 1-2 (equivalence thresholds), Q 1-6 (IP upgrade tolerance), Q 2-1 (DALI default), Q 2-2 (dimension vs wattage priority).
Key Phase 4 blockers: Q 1-8, 1-9, 1-10 (BOQ expansion rules — now in Batch 1).
Odoo data issues to fix before go-live (for Riccardo, not Manel):
carolinaportugalarq@gmail.com assigned to wrong partner in Odoo ("Robin Kathuria") — would silently create quotes under the wrong customer514865881 duplicated across two distinct partners (DELPA ELECTRICPARTS LDA + Esfera de Fios) — VAT matching unreliable until deduped@aronlight.es CC address — domain match on aronlight.es returns 40+ partners at 100% confidence, breaks disambiguation for internally-forwarded requestsConfirmed:
aronlight-staging-32576046.env (not committed)