Repo highlights scan — triaged for shared-memory seeding

# Repo highlights scan — triaged for shared-memory seeding

Discovered across briefings/, CLAUDE.md, docs/specs+plans, git log (166 commits), report scripts.

HOLD — DO NOT SHARE (creds / cross-client / personal-IP / infra / access-control)

• (source: briefings/claude_activity_2026-05-31 & 2026-06-01) — Recurring "swarph-cli fix for terminal windows", "you are workstation-lc?", mesh/tmux sessions relate to the separate personal swarph/brainsurfing project — not Lyréco, do not share.
• (source: briefings/claude_activity_2026-05-27 & 2026-05-28) — Prompts about launching the GlobalProtect / PaloAlto VPN ("default VPN for my employer and Lyreco customer") reveal employer/infra access posture — do not share.
• (source: briefings/claude_activity_2026-06-01 & 2026-06-04) — Exchanges about ANTHROPIC_API_KEY (checking presence/length/last-4 chars, handing the key to a session to run the full complements job) touch credentials — do not share.
• (source: briefings/claude_activity_2026-05-26.md) — Session 4a9d5822 opening prompts (adding SSH public keys, darw007d claude-service, mesh peers) mix personal-infra / cross-project setup — do not share.
• (source: CLAUDE.md) — Pierre Samson's personal HubSpot login alias (ext-psamson@lyreco.com) — personal work-account identifier, not for sharing.
• (source: CLAUDE.md) — Dashboard HTTP Basic Auth setup: named users (lyreco, lucas, darshan) with passwords stored in .dashboard_extras.env / set via nssm set ... AppEnvironmentExtra — credential-location/access-control detail, not for sharing outside the team.
• (source: commit df74ba6) — Campaign console memory-refresh logic includes a hard cross-tenant allowlist that drops other-client / non-Lyréco strategic content even when it scores as a top hit — the existence and mechanics of that filter are an internal multi-tenant isolation control, not a Lyréco business finding.
• (source: commit ef73b5e) — Architecture docs formalize a data-sovereignty / trust-boundary + PII-extraction approval gate design between raw client data and the LLM agent layer — internal security-control specifics, not a business/technical learning to redistribute.

TENANT — Lyréco campaign/report/data learnings (candidates to seed lyreco_* shared memory)

• (source: briefings/segmentation_product_recommendation_2026-05-13.md) — HDBSCAN segmentation surface is already in place: 35 segments / 47,523 accounts, with 78% of accounts sitting in the top 5 segments (FR 72%, GB 84%).
• (source: briefings/segmentation_product_recommendation_2026-05-13.md) — segment_product_affinity z-score surfaces popularity not distinctiveness — family 021001 (welfare/coffee/snacks) ranks top-3 for almost every segment (premium z=14.1 AND dormant z=4.3), so it can't be used as-is for differentiated recs; needs a distinctiveness filter (z_score_segment − max(z_score_other_segs) > 1).
• (source: briefings/segmentation_product_recommendation_2026-05-13.md) — Pseudo-product noise pollutes the affinity table: non-products like "FLYERS HAPPY NEW YEAR 26", "FREIGHT CHARGE", "PK25 CATALOGUE DIARIES STOCK" (z=82.5) inflate z-scores — need a product_type_code / SKU-deny-list filter at the serving layer.
• (source: briefings/segmentation_product_recommendation_2026-05-13.md) — No CH (Switzerland) rows exist in segment_product_affinity / product_co_occurrence / emergent_communities — recommender needs a CH backfill (compute_segment_affinity + compute_co_occurrence) before shipping there.
• (source: briefings/segmentation_product_recommendation_2026-05-13.md) — Co-occurrence pairs at lift≥10 are far thinner in FR (28,453 / 2.4%) than GB (40,957 / 7.3%) — GB has headroom for a strict widget threshold, FR would need to relax to lift≥5.
• (source: briefings/prospect_lookalike_v1_2026-05-18.md) — Prospect look-alike v1: for ~26k Eloqua-responder prospects with zero purchase history, soft-match (cosine, top-3 of ~60 KMeans k=60 communities, weight-normalized blend) against firmographic customer communities to predict product-world appetite across Lucas's 9 worlds (BUREAUTIQUE, IT, HYGIENE, CAFETERIA, MOBILIER, LOGISTIQUE, EHS, GOODIES, OTHER).
• (source: briefings/prospect_lookalike_v1_2026-05-18.md) — Look-alike confidence buckets: HIGH = top similarity>0.70 AND community≥30 active members; MEDIUM = similarity>0.40 OR community≥15; else LOW (directional only).
• (source: briefings/prospect_lookalike_v1_2026-05-18.md) — v1 known limits: no Companies House SIC enrichment yet for the 27k UK customer accounts (v2 needs companies_house_enrich.py first); customer spend window is 365 days only; prospect-side Eloqua opens/clicks deliberately excluded to keep matching firmographic + explainable.
• (source: briefings/claude_activity_2026-05-26.md) — Product-tracking baselines settled as a 3-month window vs the test window, and to recompute using business days only ("jours ouvrés") — reinforces the campaign-baseline convention.
• (source: briefings/claude_activity_2026-05-26.md) — Initiative 2.2 "En stock - Livraison 24h" badge is tracked via GA4 dimension25 (not a "badge shown" event); baseline realigned to 2026-05-20 go-live (baseline 2026-02-13→2026-05-19, post window 2026-05-20→2026-05-29) for the cleanest causal story.
• (source: briefings/claude_activity_2026-05-28.md) — Logged-in-session count discrepancy vs Power BI (247K in 2026): PBI defines it as "count of sessions with at least one successful login event" via GA4 — a definitional gap to reconcile when matching warehouse funnel numbers to PBI.
• (source: briefings/claude_activity_2026-05-29.md) — Prospect→product recommendation for prospects with no purchase history routes through SIC/sector meta-buckets (Office services, Manufacturing, Healthcare, Public, Hospitality, Retail…) because it aligns with prospecting logic; summarization target = collapse 60 communities to max 8, each with 5 recommended products + prospect↔community match.
• (source: briefings/claude_activity_2026-05-29.md) — FR has no SIC code available (only W1 workwear scope usable "à l'aveugle"); FR EPI/workwear "white space" study KPI = time-to-purchase + conversion rate in workwear, studying frequency × avg basket (incl. #SKU) per client × sector.
• (source: briefings/claude_activity_2026-05-27.md) — VBAK (SAP sales-doc header) table is NOT available in the warehouse — cross-world basket / order-level work must proceed without the real SAP referential.
• (source: briefings/claude_activity_2026-06-01.md) — Compléments-IA approach chosen: "A — Typage fonctionnel → graphe de compléments par type → croisement ventes" — LLM infers functional complements from the product title (mouse→batteries, shoes→laces, printer→cartridge), then crosses with sales rules; source file imports/marie/Datas_pour_produits_associes.csv.
• (source: briefings/claude_activity_2026-06-02.md) — FR product-reference can be recovered from webshop page source: the productDetails_000000000021119343_10 element id embeds an 18-digit zero-padded product reference (load_digital_behaviour parsing target).
• (source: briefings/claude_activity_2026-06-04.md) — WISE top-15 products by meta-sector wanted as a non-overlap ranking (each product attributed to one meta-sector only) — export wise_top15_by_metasector_nonoverlap_GB.
• (source: briefings/claude_activity_2026-06-11.md) — Compléments-IA v2 scope narrowed to family "A" only (totality of its products), README redone for family A specifically.
• (source: briefings/claude_activity_2026-06-11.md) — New meta-sector taxonomy (ML_Sectors_May26_meta_sectors.xlsx) consolidates raw market_sector values into meta-sectors (1 Office/Pro Services & Finance, 2 Healthcare & Social Care, 3 Manufacturing & Industry, 4 Construction, …).
• (source: briefings/claude_activity_2026-06-11.md) — Pending-cart relance analysis pattern: cross-reference relance-email openers against the still-pending-cart list to find overlap; export needs a 90-day tab (opened + still pending) + methodology README tab.
• (source: briefings/claude_activity_2026-06-11.md) — Workwear opportunity sizing: per industry × region, model revenue from converting the ~80% of workwear-eligible accounts who don't buy to the ~20% buy-rate baseline.
• (source: briefings/claude_activity_2026-06-13.md) — Compléments-IA v2 policy change from Marie's QA: (1) drop the fixed "min 20 co-purchased products" rule — groups of 3/4/10 products should still surface; (2) add a brand/manufacturer accounting layer to the complementarity reasoning.
• (source: briefings/claude_activity_2026-06-13.md) — Data-reproducibility gotcha: re-running the same historical FR Home→category window (P1 20–29 Apr) a week later grew the page-view denominator from 111,938 to ~113K (late-arriving/backfilled data) — historical funnel numbers are not fully stable across re-pulls.
• (source: briefings/claude_activity_2026-06-13.md) — Workwear-vs-papeterie funnel report format: PDP view→cart, PDP view→purchase, Vignette interaction→cart, Vignette cart→conversion, Badge PDP coverage (post), PDP-view sessions/day — each as Baseline / Post / Δ(pp).
• (source: briefings/claude_activity_2026-06-16.md) — Optimizely checkout-test paradox: the variant shows MORE checkout-button clicks than the original, yet overall conversion did not improve correspondingly — click-rate and conversion-rate diverged (investigate post-click drop-off).
• (source: briefings/claude_activity_2026-06-16.md) — Workwear engagement analysis extended with sold-to vs ship-to split, SMB vs Corporate, active vs non-active search, and non-active-viewer page counts (export workwear_engagement_split_FRGB_2026-06-15.xlsx).
• (source: briefings/claude_activity_2026-07-02.md) — WISE UK time-to-first-purchase: of 2,527 new WISE soldto accounts, 93.6% placed a first order; upgraded to a refreshable Excel export with a country-filter tab.
• (source: briefings/claude_activity_2026-07-02.md) — WISE first→second order analysis extended to add customer spend amounts, not just conversion counts.
• (source: briefings/claude_activity_2026-07-02.md) — New "grands comptes mono-catégorie" campaign: target high-potential key accounts whose spend is concentrated in a single product family, to expand basket size (rationale behind score_mono_category_key_accounts.py).
• (source: briefings/claude_activity_2026-07-02.md) — Campaign "FR_2026_Segment_Juin_CrosscatxGant" (cross-category × gloves) generated 31 orders; per-order product breakdown required reconciling to the real SAP order number since the internal order_id alone didn't distinguish products per order.
• (source: briefings/claude_activity_2026-07-02.md) — Marie's complements export lane split into clean vs needs-correction outputs (marie_complements_full / _clean / marie_liens_a_corriger) plus a prioritized review queue (zerocpr_revue_priorisee) — confirms the guardrailed zerocpr_v2 staging-table flip is live and producing differentiated deliverables.
• (source: CLAUDE.md) — GA4 ecom_user_id semantics differ by event source: emails set ecom_user_id=contact_id (UUID), web events set visitor_id (cookie), orders set account_number (soldto); bridge via ecom_users.
• (source: CLAUDE.md) — Cross-DB joins are impossible in one query: ecom_users/accounts/products live in postgres :5433, ga4_*/customer_timeline/ecom_order_lines/ecom_invoice_lines live in timescale :5434; always bridge in 2 steps (pull IDs from one DB, query the other with ANY(%s)).
• (source: CLAUDE.md) — Products are keyed on product_reference (not product_id) + source_country; transactions bridge soldto_number → ecom_accounts.account_number and product_reference → ecom_products.product_reference.
• (source: CLAUDE.md) — HubSpot accounts.top_level_account_number (not account_number) bridges to ecom_accounts.account_number, both LPAD to 10 digits.
• (source: CLAUDE.md) — Eloqua eloqua_campaign_recipients.contact_email (lower) bridges to ecom_users.user_email; eloqua_campaign_responders has no email, resolve via eloqua_responder_bridge (lyreco_118_id → payer_number → ecom_accounts.account_number); a cleaner path is t_campaign_activity.contact_email → t_contact.email → contact_id → t_contact_soldto → soldto.
• (source: CLAUDE.md) — GA4 events join ga4_sessions on visit_unique_id (+visitor_id); journey joins journey_alignment.entity_id(+grain) ↔ visitor_path_sequence.visitor_id.
• (source: CLAUDE.md) — Identity hub is ecom_users (account_number, contact_id, gravitee_user_id); PG has 67 enforced FKs, timescale has 0 (links are logical only).
• (source: CLAUDE.md) — ecom_users.account_number IS a verified soldto (99% SOLDTO_FLAG='Y'); use T_ACCOUNT.{SOLDTO,SHIPTO,BILLTO,PAYER}_FLAG to pin an exact role; org rollup via T_HIERARCHY + leader-number fields.
• (source: CLAUDE.md) — Every Oracle pull needs hygiene filters deletion_flag='N' AND dl_deletion_flag='N' AND business_scope_flag='Y'.
• (source: CLAUDE.md) — soldto/shipto/order/ecom_user_id numbers are NOT globally unique — always partition by country_code.
• (source: CLAUDE.md) — NextGen ecom_user_id on ecom_order_lines is buggy/mixed (sometimes email, sometimes id) — guard joins on it.
• (source: CLAUDE.md) — gravitee_user_id↔last_sso_uuid only exists for NextGen-live countries; contact_id↔last_sap_contact_id coverage is ~87% on ecom_users (UK 2026 logged-in GA4 ~99%).
• (source: CLAUDE.md) — PRODUCT_ROW_ID/SEARCH_ROW_ID are array indices, not unique keys; the real Oracle PK is (SESSION_DATE, PROPERTY_ID, COUNTRY_CODE, EVENT_UNIQUE_ID, *_ROW_ID) — a wrong dedup key collapsed 7.6M events into 30k.
• (source: CLAUDE.md) — page_type in ga4_page_events is kebab-case (product, shopping-cart-page, search-results), not upper-snake.
• (source: CLAUDE.md) — Oracle stock pulls default to --window current (~900k rows/day); --window predict (180 days, ~150M rows) is on-demand only — T_LOG_DAILY_STOCK alone has 425M rows for FR, never pull it in full.
• (source: CLAUDE.md) — visitor_path_sequence.archetype_path letter codes: X=converter, K=checkout-starter, C=cart-builder, S=search+view, V=viewer, L=login-only, B=bounce (highest precedence wins per session).
• (source: CLAUDE.md) — 7 active canonical journeys (b2b_quick, b2b_considered, b2b_search_driven, b2b_wishlist_path, b2b_cart_recovery, b2b_compare_browse, b2b_power_user) each need journey_alignment + step_noise_distribution populated.
• (source: CLAUDE.md) — path_mining_results is NOT canonical-anchored (global grouping only); the Journey Analyzer's canonical-mode panel reuses it without filtering by canonical_code.
• (source: CLAUDE.md) — Campaign baselines = the 3 full months before test start_date (team rule, excludes the test campaign itself), stored as both numeric baseline_value and baseline_text.
• (source: CLAUDE.md) — Lyreco HubSpot portal = 27086448, region = eu1.
• (source: docs/superpowers/specs/2026-06-08-product-complements-v2-design.md) — Complements v1 was rejected by the business because the AI typed products by guessing category from a cryptic abbreviated SAP title (e.g. "dérouleur bobine" for a tape dispenser), producing plausible-but-false links; the fix is to READ the authoritative category hierarchy (section_code→family_code→category_code→subcategory_code) from ecom_products instead of inferring it.
• (source: docs/superpowers/specs/2026-06-08-product-complements-v2-design.md) — family_code alone is globally unique in FR (206 families, 21 sections); status_code on ecom_products is numeric (98≈active), NOT the Continued/New/Delisted vocabulary (that only exists in the baseline import file) — the reliable "active" filter is not_salable_flag IS NOT TRUE AND not_visible_flag IS NOT TRUE.
• (source: docs/superpowers/specs/2026-06-08-product-complements-v2-design.md) — World CAFETERIA = exactly 7 families (snacks 001001, coffee 001002, cold drinks 001003, hygiene-electro 001004, cups/service 001005, recycling bins 001006, +1 special-item family to ignore).
• (source: docs/superpowers/specs/2026-07-03-zerocpr-guardrails-design.md) — The zeroCPR complement fallback measured on FR: 105,297 pairs drawn from only 671 distinct SKUs; top 5 generic SKUs (wipes/rolls/disinfectant) attach to 6,451 anchors each across all 16 merchandising sections; 89% of pairs cross merchandising section; 22% put a Lyréco own-brand complement on a competitor-branded anchor; EPI/workwear section is 41% of all pairs with 78% of its anchors sharing one SKU.
• (source: docs/superpowers/specs/2026-07-03-zerocpr-guardrails-design.md) — Supplier-protection is a distinct "politics" gate from brand-compatibility ("compat"): it blocks proposing a Lyréco own-brand consumable on a competitor-branded durable (e.g. Rapid stapler) in a fit-critical family even when physically compatible, to protect the supplier relationship.
• (source: docs/superpowers/plans/2026-07-03-zerocpr-guardrails.md) — zeroCPR anchors are, by construction, the population that already lacked co-occurrence signal (that's why they fell to the fallback) — so routing them through the existing subcat co-occurrence gate was deliberately skipped (YAGNI); hub-IDF demotion is the mechanism for this population instead.
• (source: docs/superpowers/specs/2026-05-20-basket-bundle-association-rules-design.md) — FR basket-size distribution (90-day window): median 4 SKU, mean 5.85, p95=17, p99=30; only 31,471 distinct SKU appear in multi-line baskets over 90 days (not the full 1.68M catalog); the anti-restock filter (basket size>20 OR world-spread≥5) drops only 5.4% of baskets while preserving the full 1–4-world functional zone.
• (source: docs/superpowers/specs/2026-05-26-initiative-2.2-delivery-eta-design.md) — The "En stock - Livraison 24h" PDP badge is T_ECOM_GA4_PRODUCT.PRODUCT_STOCK value '1_available_En stock - Livraison 24h' (first appears 2026-05-20), not the sibling PRODUCT_DELIVERY_TIME column (a sparse numeric lead-time, mostly NULL) — there is no literal DIMENSION25 column.
• (source: docs/superpowers/specs/2026-05-26-initiative-2.2-delivery-eta-design.md) — The lighter view_item_list GA4 browsing event is deliberately not ingested into the warehouse, so listing-page ("vignette") funnel analysis must proxy the denominator with select_item (tile click), which slightly under-counts true listing views.
• (source: docs/superpowers/specs/2026-05-29-fr-epi-whitespace-design.md) — No French NAF sector code exists in the warehouse and no per-account SIC either; FR sector analysis must use the US SIC 1987 code inherited from the account's firmographic community (cluster-level, not per-account).
• (source: docs/superpowers/specs/2026-06-11-product-buying-intelligence-design.md) — ecom_order_lines.order_number is the true cart/basket id for frequency counting; invoices are not usable for basket grain because they split/merge order lines.
• (source: docs/superpowers/specs/2026-07-01-product-complementarity-design.md) — FR product SAP codes bridge to ecom_products.product_reference via LPAD(sap,18,'0') (18-digit zero-pad), which is a different padding convention than the HubSpot account-number LPAD-10 — don't confuse the two paddings.
• (source: docs/superpowers/specs/2026-06-02-functional-complements-ai-design.md) — Marie's product CSV (imports/marie/Datas_pour_produits_associes.csv) is encoded cp1252, not UTF-8; must be read with encoding="cp1252" or accents corrupt.
• (source: commit c6511d5, 0d01025, 677e710) — zeroCPR (LLM fallback) complement guardrails cut disallowed cross-section links from 57.6% to 0% and the max "hub" (over-generic SKU appearing everywhere) from 6,451 to 200 links, while growing the distinct SKU pool 671→1,056; tightening section adjacency to only genuine functional clusters (printer, stationery) removed loose crossings like an air-freshener showing up as a safety-gear complement.
• (source: commit b8abc2d) — Type-A printer→cartridge matching on free-text model tokens produced false collisions on pure numeric tokens (HP OfficeJet 250 matched Lexmark E250); requiring a letter in the token fixed it.
• (source: commit 8d26df3, 14103b9) — Printer↔cartridge complement matching needed a device-family whitelist (section 004011) rather than a generic "durable anchor" gate, and needed the FULL cartridge catalog (not just the 18k in-scope anchor set) as the compatibility index, else valid pairs silently returned zero.
• (source: commit 48e656b, 692eb9c) — Marie/Lucas's product-complements deliverable reached 100% coverage (18,152 FR anchors); the zeroCPR (LLM-inferred) tail is fixed at 0.6 confidence and flagged for spot-check before customer-facing use.
• (source: commit aa9a72b) — Raw co-purchase counts overstate a complement's validity: a ubiquitous item (e.g. BIC Cristal pen) co-occurs with nearly everything at lift ~1.0. Only lift ≥ 1.5 is treated as "self-validated"; the rest (2,712 "popularity artifacts") were routed back to human review instead of marked done. Anchors were also Pareto-ranked by 90-day PDP views so review effort focuses on the ~2,078 anchors carrying 80% of complement-widget traffic.
• (source: commit b5aecf5) — FR web-funnel "why is CR stuck" root cause: a wholesale cpc→organic attribution shift (cpc touched 23%→6% of converter journeys YoY, mostly reclassified as "(unset)"/organic) with flat converting-customer count — i.e. the channel-attribution shift cost effectively zero real conversions.
• (source: commit b5aecf5) — Omni-channel diagnosis: the web→order path is largely self-contained (~3% offline leakage); the real omni-channel blind spot is Telesales (EUR 11.5M, +38% YoY) which has ~0% overlap with web-identified customers — a digitally invisible revenue base.
• (source: commit 9a56ed5, eee7b48) — WISE onboarding "registered" signal was sharpened from an sso-uuid-gained proxy (which also fires on existing customers logging back in) to the direct GA4 event "Account Creation Success - HubSpot", which only fires on true new-account creation.
• (source: commit 8b87c5a) — WISE true first-purchase cohort analysis: of 9,198 identified onboarding-touched visitors, only 1,465 were genuinely new-customer first purchases vs 3,310 pre-existing customers — gross GA4 order counts had been overstating acquisition because popup traffic is mostly existing customers.
• (source: commit 0e029f4) — WISE incremental new-customer revenue (GBP 243,366, avg first order GBP 166) is roughly a fifth of gross bookings attributed to the campaign (GBP 1.32M); HubSpot itself reports GBP 0 attributed revenue (no deals/forms linked), so the GA4↔ecom stitch is the only real revenue measurement that exists for this campaign.
• (source: commit 4800184) — WISE onboarding reactivation: the highest-value re-engagement pool is the 1,468 REGISTERED cart/checkout abandoners (identified AND contactable), not the larger anonymous drop-off pool (72% of which never engaged past the popup).
• (source: commit abd108b) — WISE Eloqua cohort comparison: the small #1.8 post-registration nurture sequence (73 recipients) had a 8.22% CTR vs 0.46% for the mass-push NextGen Launch UK send (115k recipients) — roughly 17x the per-recipient engagement.
• (source: commit 0b98cb4) — WISE inactivity-popup A/B/C test (1-day early read): Test C ~19% CTR (unconfirmed, single day); A vs B "new link" variant essentially tied (9.5% vs 9.7%); a solo/no-test control ran 11.8%.
• (source: commit 99fdced) — FMP /profile returns UK (LSE) market caps in whole pounds despite labelling the currency 'GBp' (pence); FMP /company-screener returns caps genuinely in pence. Divide /profile cap by 1e6 for GBP millions — no pence conversion needed there.
• (source: commit fbb2890) — FMP's /news/stock endpoint returns zero articles for every UK-listed ticker tested at the current subscription tier (US-only coverage); NewsAPI was wired as a fallback specifically for the UK competitor cohort, bounded to ~15 calls/refresh to stay under the free-tier daily cap.
• (source: commit e6bd6dd) — FR EPI (workwear) whitespace targeting: "should-buy" sectors must be defined by occupational nature (Construction, Manufacturing & Industry, Healthcare & Social Care), not by lift >= 1, because a pure-lift cut wrongly excluded Construction (which is under-penetrated, i.e. has real whitespace, but scores low on lift).
• (source: scripts/analyze_workwear_opportunity.py) — Workwear ("20% buy / 80% gap") opportunity model: opportunity = non-buyers × average workwear revenue-per-buyer, computed per industry × country/region, using billed invoice lines (not orders) as the realized-revenue source of truth.
• (source: scripts/score_mono_category_key_accounts.py) — Mono-category key-account scoring must use the world grain (8 addressable product universes) rather than section_code (21 sections) to define "category" — 6 of the 21 sections are all BUREAUTIQUE (office supplies), so at section grain a plain stationery buyer would misleadingly look "multi-category".
• (source: commit da3c190) — EPI (workwear) viewed/cart intent must be matched SKU-exact against ga4_product_events, not customer_timeline page events, because timeline page events carry no product_reference at all.
• (source: commit 017b3ba) — Optimizely's single Personal Access Token authenticates to the Experimentation REST API only (verified 200 there, 403 on all ODP regions); Commerce/Campaign/ODP each require their own separate credential.
• (source: commit 2e38b46) — Applying the business_scope_flag='Y' hygiene filter (per Lyréco's own data-lineage review) to order/invoice Oracle pulls excludes roughly 2% of rows as out-of-scope (WISE 2.1% / FR 2.1% for orders; WISE 1.9% / FR 2.6% for invoices) — those rows exist in Oracle but shouldn't be counted as real business.
• (source: commit 4dfe16f) — SHARED.T_CAMPAIGN resolves Eloqua CAMPAIGN_ID → human-readable CAMPAIGN_NAME at ~99.8% coverage, closing the long-standing "no campaign name available in Oracle" gap — but its COUNTRY_CODE column is unreliable (NULLs + mistagging) and should never be trusted; join on id only.
• (source: scripts/analyze_wise_first_to_second_order.py) — WISE is billed per-country: UK orders settle in GBP, Ireland orders in EUR — these must never be summed together as a single currency figure.
• (source: commit bcc1321) — The Neo4j graph holds two disjoint account universes sharing the same 10-digit account-number id space (:Account Dynamics/BvD firmographic data, 1.58M nodes, vs :LyrecoAccount real customers, 56k nodes) with no label marking which BvD accounts actually converted — an explicit :Customer enrichment pass was required before the contagion (influence-propagation) engine could be ported and parity-tested.
• (source: commit e3b3947) — The Oracle VPN tunnel (GlobalProtect, interactive 2FA) is frequently not up at a fixed early-morning refresh time, so a fixed daily schedule silently failed; switched to a 30-minute poll that no-ops quietly when the tunnel is down or the day's refresh already succeeded, firing the first time the tunnel comes up.
• (source: commit 41cdaa1) — A per-product-reference filtered aggregate over ecom_order_lines (16.5M rows) with a large ANY(...) array of 100k+ references caused pathological sequential scans and hung for 17 hours; replaced with a single unfiltered GROUP BY pass over the whole table, which completed in 7 seconds.

SWARM — generic technical learnings (candidates for the swarph timeline)

• (source: briefings/segmentation_product_recommendation_2026-05-13.md) — General recsys pitfall: a z-score/affinity computed against a population baseline that itself includes everyone-buys items surfaces popularity, not distinctiveness — penalize items ranked top-N by ≥3 segments before serving.
• (source: briefings/segmentation_product_recommendation_2026-05-13.md) — Non-product rows (freight, catalogue, flyer SKUs) pollute affinity/co-occurrence statistics and produce the highest spurious z-scores — a reusable "deny-list non-transactional SKUs before mining" pattern.
• (source: briefings/prospect_lookalike_v1_2026-05-18.md) — Gate look-alike-model confidence on BOTH similarity AND cluster mass (not either alone): a strong match to a tiny cluster gives an unstable signal.
• (source: briefings/claude_activity_2026-06-13.md) — Reusable LLM cluster-naming pattern: feed the model a strictly factual cluster profile (dominant sector + share, spend tier, size band, region, churn) and ask for a short ≤4-word Title-Case name + one-sentence description — turns unsupervised segments into human-readable labels on any project.
• (source: briefings/claude_activity_2026-06-13.md) — Analytics reproducibility caveat: on warehouses with late-arriving/backfilled rows, re-querying an identical historical window returns a different denominator — snapshot or pin the extract date if numbers must be stable across re-runs.
• (source: CLAUDE.md) — FMP (Financial Modeling Prep) migrated /api/v3/ → /stable/ on 2025-08-31; newer-tier subscriptions get a 403 "Legacy endpoint" on the old path; symbol goes in a symbol= query param, not the URL path.
• (source: CLAUDE.md) — "Massive" is the rebrand of polygon.io (pip install massive), same API surface as polygon-api-client.
• (source: CLAUDE.md) — HubSpot EU accounts: OAuth authorize/token goes to the regional subdomain (mcp-eu1.hubspot.com), but the MCP server itself is always the unified mcp.hubspot.com.
• (source: CLAUDE.md) — HubSpot MCP auth apps have no client-requested scopes — permissions are inherited entirely from the consenting user's HubSpot role.
• (source: CLAUDE.md) — Newer HubSpot API endpoints use a dated path (/crm/objects/2026-03/contacts) instead of /crm/v3/objects/contacts; both work today but the dated path is preferred for new code.
• (source: CLAUDE.md) — FRED/OECD series codes change often; many plausible-looking series-code patterns return empty/Bad-Request — stick to a small confirmed-working catalog rather than guessing codes.
• (source: CLAUDE.md) — Python 3.14 is too new for psycopg2-binary wheels; use Python 3.12 for the venv.
• (source: CLAUDE.md) — PowerShell 5.1 on French-locale Windows reads .ps1 files as Windows-1252 unless a UTF-8 BOM is present — keep script files ASCII-only + UTF-8-BOM-encoded.
• (source: CLAUDE.md) — OneDrive-synced folders cause git weirdness; keep repos outside OneDrive sync (or exclude the folder from sync).
• (source: CLAUDE.md) — Visually-laid-out PDF exports (e.g. Mural boards) produce one giant noisy line per frame via pdftotext; dedup + filter short tokens + skip hex-color/numeric-only lines before treating the text as readable content.
• (source: CLAUDE.md) — openpyxl rejects tz-aware datetimes; strip tzinfo before writing a cell value.
• (source: CLAUDE.md) — A subprocess spawned from a service/dashboard on Windows needs PYTHONIOENCODING=utf-8 in its env — without it, stdout defaults to the system codepage (cp1252 on FR Windows) and any non-cp1252 character printed (arrows, emoji, €, Δ) raises UnicodeEncodeError and crashes the subprocess.
• (source: CLAUDE.md) — Joining a huge hypertable (100M+ rows) against a visitor/cohort-grain unnest can OOM the database container; use a pre-aggregated rollup table instead of the raw event-grain table for cohort-driven analysis.
• (source: CLAUDE.md / general principle behind the cross-DB gotcha) — When two data stores can't be joined in one query (separate connections/engines), bridge in two steps: pull the key set from store A, then query store B with WHERE col = ANY(%s) — never attempt a live cross-connection join.
• (source: CLAUDE.md / general principle behind the row-id gotcha) — Before deduplicating rows from an external system, verify the actual declared primary key rather than assuming a per-row index-looking column is unique — array-position columns are not keys and a wrong dedup key can silently collapse the majority of rows.
• (source: docs/superpowers/specs/2026-06-08-product-complements-v2-design.md) — When an LLM is guessing a product/entity's category from a short or cryptic text field, check first whether a structured, authoritative category field already exists upstream — read it instead of inferring, which removes an entire class of plausible-but-wrong classification errors.
• (source: docs/superpowers/specs/2026-06-08-product-complements-v2-design.md / 2026-07-03-zerocpr-guardrails-design.md) — Ship a risky re-derivation of an existing signal as a new value in the same signal-type/tag column (e.g. signal_type='x_v2') rather than a separate table or destructive rewrite — it's non-destructive, diffable against the old value, and lets you flip consumers over only after validation.
• (source: docs/superpowers/specs/2026-07-03-zerocpr-guardrails-design.md) — When a fallback/exception code path reimplements logic that a primary path already guards, audit whether the fallback silently bypasses those guardrails — the root cause of "bad recommendations from the fallback" was that it called the low-level ranker directly instead of routing through the same gates the main path used, not that the gates themselves were wrong.
• (source: docs/superpowers/specs/2026-07-03-zerocpr-guardrails-design.md) — Guardrail/gate predicates should default to "pass" when the relevant attribute is missing/unknown on either side — never block on absence of data — otherwise coverage collapses and false rejections pile up; log the pass-through so a downstream review step can still flag it.
• (source: docs/superpowers/specs/2026-07-03-zerocpr-guardrails-design.md / plan) — To break a "hub" item that keeps getting recommended everywhere (an over-attached SKU/node), demote it with a continuous cross-population penalty proportional to log1p(times already attached) (IDF-style) plus a hard attachment cap as backstop, rather than a single flat per-item cap alone.
• (source: docs/superpowers/specs/2026-05-20-basket-bundle-association-rules-design.md) — Before mining market-basket association rules, filter out "bulk restock" baskets (abnormally large size or spanning too many product categories) — they inflate co-occurrence with noise unrelated to functional complementarity; measure the filter's basket-loss rate before locking the threshold.
• (source: docs/superpowers/specs/2026-05-20-basket-bundle-association-rules-design.md) — A staged (L1→L2→L3→L4), pure-Python Apriori implementation with anti-monotone pruning is a viable no-new-dependency alternative to mlxtend when only a small frequent-item universe survives support filtering.
• (source: docs/superpowers/specs/2026-05-26-all-channels-prospect-segmentation-design.md) — A naive multi-dimensional segmentation (fit × engagement × channel × product, each an axis) explodes into thousands of sparse, unactionable cells; the fix is volume shrinkage (shrink each cell toward its parent, with per-dimension K set inversely to that dimension's measured persistence), not a bigger/finer cube.
• (source: docs/superpowers/specs/2026-05-26-all-channels-prospect-segmentation-design.md) — Lock canonical, coarse dimension value-sets and normalize every source into them BEFORE building any cube/rollup — retrofitting normalization after the cube exists is the expensive path.
• (source: docs/superpowers/specs/2026-05-26-all-channels-prospect-segmentation-design.md) — When scoring an entity across multiple signal channels with uneven coverage, weight the score only by the channels actually observed and carry a confidence value — an unobserved channel must never be silently scored as "cold"/negative.
• (source: docs/superpowers/specs/2026-05-26-all-channels-prospect-segmentation-design.md) — For an embedded analytics cube (e.g. DuckDB ATTACH over Postgres/Timescale), materialize the expensive rollups on a schedule and reserve live query-time GROUP BY only for cheap interactive cuts; never expose the cube engine directly as an API.
• (source: docs/superpowers/specs/2026-05-26-customer-segmentation-cube-design.md) — Cross-sell, channel-migration, churn, and whitespace/share-of-wallet can all be modeled as the same underlying quantity — a segment-cell's shrunk deviation from its peer-expected baseline — queried as different ranked views over one cube, instead of four separate models.
• (source: docs/superpowers/specs/2026-05-26-chat-widget-design.md) — For a long-lived per-user LLM chat context, accumulate token usage per thread and, at a safety threshold well under the model's real context limit, run a compaction turn that asks the LLM to condense the conversation into a carry-over summary, then start the next turn as a fresh thread seeded with that carry-over — mirrors how Claude's own auto-compaction works.
• (source: docs/superpowers/specs/2026-05-26-chat-widget-design.md) — When an LLM session-resume fails (session expired/aged out), degrade gracefully by reseeding a fresh session with the last known carry-over/summary rather than surfacing a hard error to the user.
• (source: docs/superpowers/specs/2026-06-11-product-buying-intelligence-design.md) — Before designing a new fact table, check whether an existing hypertable/event stream already carries the grain you need (here, a planned purchase_lines table was dropped because the order-lines table plus already-emitted per-line timeline events covered it) — avoid building a duplicate substrate.
• (source: docs/superpowers/specs/2026-06-11-firmographic-cluster-labels-design.md) — Hybrid AI-labeling pattern: compute an entity's defining facts deterministically first, then make a single batched LLM call to turn the facts into human phrasing (name + description); validate the returned JSON's shape and completeness, and fall back to a rule-based generator on any parse/validation failure so no row is ever left unlabeled.
• (source: docs/superpowers/specs/2026-06-11-firmographic-cluster-labels-design.md) — KMeans (and similar re-clustering) cluster IDs are not stable across reruns — don't treat a cluster ID as a durable identity; regenerate any per-cluster labels/metadata every time the clustering reruns.
• (source: docs/superpowers/specs/2026-06-11-buying-profile-explorer-design.md) — For any interactive table endpoint accepting sort/page/filter query params, route them through a pure whitelist/clamp parser before they ever touch SQL (ORDER BY column, OFFSET, etc.) — this is the actual SQL-injection prevention boundary, not string-escaping the raw params.
• (source: docs/superpowers/specs/2026-07-01-product-complementarity-design.md) — Build a versioned, human-reviewable label→rule config (LLM-assisted only at build time to classify ambiguous labels) rather than calling the LLM per-row/per-product at runtime — keeps a rich classification cheap and auditable at scale.
• (source: docs/superpowers/specs/2026-06-02-functional-complements-ai-design.md) — When cross-referencing an AI-derived signal against an observed outcome (e.g. "AI says these are complementary" vs "sales say they're bought together"), the AI step must never see the outcome data (sales-blind) — otherwise the cross-reference becomes circular and can't detect the gap it's meant to find.
• (source: docs/superpowers/specs/2026-06-02-functional-complements-ai-design.md) — Controlled/incremental vocabulary pattern for LLM classification: pass the list of categories already created back into each subsequent batch and instruct the model to reuse an existing category before inventing a new one — prevents near-duplicate category proliferation (e.g. "mouse"/"wireless mouse"/"ergonomic mouse" collapsing into one type).
• (source: commit 7cadd9e, c6511d5) — When adding a stricter/guardrailed version of a generated dataset, a PK collision can make single-table coexistence with the old version impossible; persist the new version to a separate non-destructive staging table (_v2) instead and flip consumers once validated.
• (source: commit 38fd871) — For a pipeline mixing a fast deterministic step and a slow external-call step, split into 3 phases: serial build of shared state (keeps global counters/caps exact), concurrent phase for only the slow independent calls (workers touch no shared state), then serial persist — cut a 17h sequential run to ~9h.
• (source: commit 6bdad35) — Bound external-call timeouts tightly (e.g. 90s not 180s) so a dead socket (post machine-sleep or service restart) recovers fast, and make a resumable driver stop after N consecutive no-progress iterations — otherwise permanent-gap items (0 rows persisted) keep getting retried forever.
• (source: commit 475dcb1) — A service bound to 0.0.0.0 inside WSL is reachable from the Windows host via localhost (through ::1 forwarding), independent of Tailscale/tailnet name — marginally faster and removes a network dependency.
• (source: commit 630186d, d74f38d) — A bulk UPDATE across a compressed TimescaleDB hypertable can exceed max_tuples_decompressed_per_dml_transaction; fix by making the view COALESCE old/new column forms (so history reads correctly with zero chunk mutation) instead of backfilling old rows.
• (source: commit f2d7420) — HubSpot Search API 400s once the after cursor pages past 10,000 total results. Fix: page in fixed-size windows (e.g. 5,000); on filling a window, re-anchor the whole search on hs_lastmodifieddate >= and reset after to 0, sorting ASC — no single search ever crosses the cap. The LIST API has no such cap.
• (source: commit f7815f4) — A Docker Desktop port-proxy can silently cross-route a host port into the wrong container; the symptom (password authentication failed) looks identical to stale credentials. Diagnose with a cross-credential probe (query current_database() against the suspect port) before assuming the password is wrong.
• (source: commit e9296d1, 62be07e, 008b141, 45fb056, 0d51c15) — When porting a query/engine to a new declarative form, lock a parity gate against an independently-written reference query over a CLOSED time window, compared at full result-SET grain (ids), not just aggregate counts — this catches subtler bugs than a count-only check.
• (source: commit 5be0e38) — Script-backed reports invoked as python .py run with sys.path[0] = the script's own directory, not the repo root; missing the repo-root sys.path bootstrap makes from database.x import y fail at runtime. A refresh endpoint that returns HTTP 200 regardless of the script's exit code will mask this failure — check the actual rc, not just the HTTP status.
• (source: commit e041579) — Any DB-query helper that always applies %-substitution on params (even when passed an empty tuple) will misinterpret a literal % in a LIKE pattern as a positional placeholder and raise IndexError. Escape literal % as %% in every such query, and grep the whole codebase for the same pattern once found once.
• (source: commit e4ef615) — On Windows, proc.kill() (TerminateProcess) kills only the direct child, not its own child processes (e.g. a CLI wrapper's node.exe children) — subprocess trees leak on timeout-cancel. Use taskkill /F /T /PID for a real tree-kill, track spawned PIDs in a module-level set, and register an app-shutdown handler to reap any still-tracked PIDs so a service restart doesn't orphan them.
• (source: commit 4a3ecc1) — A synchronous "await-then-render" handler for a long-running LLM/agent task leaves the user staring at a spinner with no feedback, which invites them to re-submit — spawning duplicate overlapping runs. Fix with async submit (background task) + a self-polling status endpoint (e.g. HTMX poll every 2s) that stops polling once done/errored, plus a stale-run guard for runs that outlive a plausible max duration (likely an interrupted process).
• (source: commit 2c738c1) — Batching LLM calls in smaller chunks (e.g. 15 items/call) can be necessary purely to stay under a CLI/API timeout window, independent of any quality concern.
• (source: commit 05249ef, 5a61c35) — A high-volume source-table pull that fits under a step timeout at a small window blows it at a larger --days window; fix by chunking the requested date range into fixed-size windows worked backward from today, each committed in its own transaction — so a timeout or SIGKILL mid-pull loses only the in-flight chunk, not the whole run.
• (source: commit 2a6737c) — File-upload endpoints need explicit path-traversal filtering (strip ../, drive letters, unsafe chars) on user-supplied filenames plus hard caps (size per file, file count) even when the storage target is a per-user subdirectory.
• (source: commit 9bc88fe, a05328e) — Long resumable batch jobs should flush their cache/progress after every batch (or every N items), not only at the very end — an interrupted run otherwise loses all progress since the last checkpoint, even hours of it.
• (source: commit 7b85593) — Make the LLM model tier (e.g. haiku/sonnet/opus) a first-class selectable parameter bound into a closure at call time, rather than hardcoding one model — lets a cheap high-volume task and a low-volume reasoning-heavy task in the same codebase each pick their own cost/quality point.
• (source: commit eaabf71) — Small local models (4B-class: phi3:mini, nemotron-mini:4b) are fast and free but too weak for structured reasoning over a large controlled vocabulary (e.g. picking a correct category from 200+ options) — they template/repeat answers; a 7B+ model was needed for usable output.
• (source: commit 6c9687d) — Trimming an LLM's forced-output JSON schema to only the fields actually used downstream (dropping unused fields like a free-text "reason") cut output tokens ~10x and roughly halved per-call latency.
• (source: commit ccb41cd) — A schema-introspection script (PK/FK/columns across multiple DB engines) that also overlays and verifies documented cross-DB bridge keys catches "the docs say column X exists but it doesn't" drift automatically as the schema evolves — worth re-running periodically rather than trusting stale documentation.
• (source: commit cd1ef7a) — A test/parity gate should read a config value from where the system actually stores it (e.g. a persisted window-length column) rather than hardcoding the same number in the test — otherwise the two can silently drift apart.
• (source: commit 81361bf) — A CSV reader hitting a "real-world" export may contain stray bytes invalid in the declared encoding (e.g. cp1252); use errors="replace" rather than letting the whole load crash on one bad byte.- [tenant] (source: briefings/2026-06-20, session 52228a7e) — FR email-campaign report: campaign segment fields come back empty for some campaigns; fix rule = when campaign NAME contains "SMB", backfill the segment before regenerating.
• [tenant] (source: briefings/2026-06-20, session 52228a7e) — Reusable pattern: break FR email opens/clicks by day-of-week × hour-of-send (+ subject/target/content/category) to derive a recommended send-calendar (best day + hour).