← back to Harlequin Sample Price Analysis
TK-10870: read-only Harlequin missing-sample + price analysis (corrected)
d5bc8ad11f7ee3ca941b509af7053f8a5b59b455 · 2026-08-30 09:23:15 -0700 · Steve Abrams
- pure pricing lib (retail=cost/0.65/0.85) + 11 passing node:test unit tests
- read-only psql harness (forced read-only session; SELECT-only guard)
- FINDINGS.md: gate premise 'COST=UNKNOWN' is STALE — mirror back-filled 8/30,
price_trade populated 732/842, 31/32 cohort computable, 1 cost gap, 5 null dw_sku
- no Shopify / no dw_unified writes
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A .gitignoreA FINDINGS.mdA README.mdA artifacts/harlequin-analysis.jsonA artifacts/harlequin-analysis.mdA scripts/analyze-harlequin.mjsA scripts/pricing.mjsA tests/pricing.test.mjs
Diff
commit d5bc8ad11f7ee3ca941b509af7053f8a5b59b455
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Aug 30 09:23:15 2026 -0700
TK-10870: read-only Harlequin missing-sample + price analysis (corrected)
- pure pricing lib (retail=cost/0.65/0.85) + 11 passing node:test unit tests
- read-only psql harness (forced read-only session; SELECT-only guard)
- FINDINGS.md: gate premise 'COST=UNKNOWN' is STALE — mirror back-filled 8/30,
price_trade populated 732/842, 31/32 cohort computable, 1 cost gap, 5 null dw_sku
- no Shopify / no dw_unified writes
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
.gitignore | 8 +
FINDINGS.md | 76 ++++++
README.md | 29 +++
artifacts/harlequin-analysis.json | 494 ++++++++++++++++++++++++++++++++++++++
artifacts/harlequin-analysis.md | 24 ++
scripts/analyze-harlequin.mjs | 120 +++++++++
scripts/pricing.mjs | 110 +++++++++
tests/pricing.test.mjs | 96 ++++++++
8 files changed, 957 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..1924158
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,8 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+.next/
diff --git a/FINDINGS.md b/FINDINGS.md
new file mode 100644
index 0000000..ca91e1d
--- /dev/null
+++ b/FINDINGS.md
@@ -0,0 +1,76 @@
+# TK-10870 — Harlequin missing-sample + price: corrected read-only findings
+
+**Agent:** codex-10870 · **Date:** 2026-08-30 · **Lane:** READ-ONLY analysis + artifacts (no Shopify / no dw_unified writes)
+**Source of truth:** `dw_unified.harlequin_catalog` (read-only), cohort `on_shopify = true` (32 rows)
+
+## 1. Headline (reproducible via `node scripts/analyze-harlequin.mjs`)
+
+| metric | value |
+| --- | ---: |
+| on_shopify_total | 32 |
+| sell_price_computable | **31** |
+| cost_gap (not computable) | **1** |
+| dw_sku_gap | 5 |
+| retail_placeholder_$150 | 1 |
+| retail_matches_formula (1.810×) | 31 |
+| below_map_floor (2× cost) | 31 |
+| sample_price | $4.25 |
+
+## 2. The material correction — the gate premise is now STALE
+
+The 8/26 pass and the approval-officer gate both rested on:
+> *"harlequin_catalog price_trade EMPTY on all rows, price_retail flat $150 placeholder … COST=UNKNOWN across all 520 rows."*
+
+**That is no longer true.** As of 2026-08-30 the mirror has been back-filled (both
+`vendor_registry` rows stamped `updated_at = 2026-08-30`), consistent with the epic's
+**S1 HARVEST (TK-10877)** stage:
+
+- `price_trade` (net cost) is populated on **732 / 842** catalog rows (110 NULL, 0 zero).
+- `price_retail` has **27 distinct values, $150–$981**; only **106** rows remain at the $150 placeholder.
+- **`round(price_retail / price_trade, 3) = 1.810` on all 732 priced rows** — i.e.
+ `price_retail = price_trade / 0.65 / 0.85`, the DW-standard markup, exactly.
+
+**For the 32 on-Shopify cohort:** 31 carry a real cost and a formula-correct retail;
+sell prices ARE computable. Only **1** row is a true cost gap.
+
+## 3. The 1 cost gap (needs a real per-roll cost before repricing)
+
+- `cranes-in-flight-marine-haw0065-05` — `DWHA-570138` — retail still $150 placeholder, `price_trade` NULL.
+
+## 4. The 5 null-`dw_sku` rows (priced, but missing the DW SKU)
+
+All Houndstooth colorways + one Flores; each has a real cost → computed retail:
+- `houndstooth-first-light-mist-haw0289-01` → $208.14
+- `houndstooth-seaglass-soft-focus-haw0289-02` → $208.14
+- `houndstooth-taupe-soft-focus-haw0289-03` → $208.14
+- `houndstooth-black-earth-soft-focus-haw0289-04` → $208.14
+- `flores-cornflower-first-light-denim-haw0288-03` → $269.68
+
+## 5. Open questions to surface to Steve (do NOT auto-resolve)
+
+1. **Rollup contradiction.** `vendor_registry.products_with_cost = 0` and `catalog_with_cost = 0`
+ while the table itself has 732 non-null `price_trade`. The rollup is miscounting/stale — verify
+ before trusting any registry-derived "no cost" signal.
+2. **Confirmed 0% discount.** `discount_confirmed = true` (2026-04-14) yet `vendor_discount_pct = 0.00`.
+ If `price_trade` is already the true net cost, the 0% field is moot; if `price_trade` is a list/MSRP
+ figure, the retail is over-derived. Provenance of the S1-harvested `price_trade` should be confirmed.
+3. **MAP-floor vs formula.** The epic's S1 note says `SSP = 2×TRADE`, but the applied retail is
+ `1.810×TRADE` — **below** a 2×-cost floor on all 31 priced rows. If 2× is a real MAP/minimum-sell
+ floor, DW retail undershoots it.
+4. **Tariff not in retail.** `harlequin_catalog.tariff_*` columns are entirely NULL, but the sibling
+ Sanderson registry row notes a 10% Section 122 UK-import tariff (unconfirmed). The current retail
+ carries no tariff line.
+
+## 6. Status of the missing-sample fix (already executed by the prior agent — NOT re-done here)
+
+The 32 Sample variants ($4.25, untracked inventory, `continue` policy) were added to Shopify by the
+prior `vp-dw-commerce` pass and are reversible (restore-maps in
+`~/.claude/yolo-queue/executed-reversible/harlequin-*-restore-map.json`). This read-only lane does
+**not** touch Shopify or the DB — it corrects the analysis and delivers the tooling to re-verify it.
+
+## 7. Recommendation (for the gated owner, not this lane)
+
+- **Un-gate the pricing premise:** the 31 computable rows can be repriced from the mirror's already-correct
+ `price_retail`; this is bounded + internally verifiable now (cost present, formula proven).
+- **Keep gated:** the actual Shopify sell-price write (customer-facing externality) — still Steve's call.
+- **Follow up on:** the 1 cost gap, the 5 null-`dw_sku` rows, and the 4 open questions above.
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..597e1ed
--- /dev/null
+++ b/README.md
@@ -0,0 +1,29 @@
+# harlequin-sample-price-analysis (TK-10870)
+
+Read-only analysis + tooling for the Harlequin (Sanderson Design Group) missing-sample
+and per-roll price question. **This repo never writes to Shopify or dw_unified.**
+
+## What's here
+- `scripts/pricing.mjs` — pure, dependency-free DW pricing + row-classification logic
+ (`retail = cost / 0.65 / 0.85`; MAP-floor + gap flags). No I/O.
+- `scripts/analyze-harlequin.mjs` — read-only harness: reads the on-Shopify cohort from
+ the `dw_unified` mirror via `psql` (forced `default_transaction_read_only=on`), applies
+ `pricing.mjs`, writes `artifacts/harlequin-analysis.{json,md}`.
+- `tests/pricing.test.mjs` — `node --test` unit tests for the money math (no DB, no network).
+- `FINDINGS.md` — the corrected findings that supersede the stale "COST=UNKNOWN" premise.
+- `artifacts/` — generated analysis snapshot (JSON + markdown).
+
+## Run
+```sh
+node --test # prove the pricing math (11 tests)
+node scripts/analyze-harlequin.mjs # live read-only analysis → artifacts/
+```
+Override the source with `PGHOST` / `PGDATABASE`, or run offline:
+`node scripts/analyze-harlequin.mjs --from-json artifacts/harlequin-analysis.json`
+
+## Safety
+- The harness asserts its SQL is SELECT-only and forces a read-only Postgres session
+ (server rejects any write). Verified: `CREATE TABLE` fails with
+ `cannot execute CREATE TABLE in a read-only transaction`.
+- The 32 Sample-variant adds were done by a prior gated pass; this repo does not repeat them.
+- Any live Shopify sell-price write remains Steve-gated.
diff --git a/artifacts/harlequin-analysis.json b/artifacts/harlequin-analysis.json
new file mode 100644
index 0000000..e9db5b8
--- /dev/null
+++ b/artifacts/harlequin-analysis.json
@@ -0,0 +1,494 @@
+{
+ "ticket": "TK-10870",
+ "generated_utc": "2026-08-30T16:19:27.702Z",
+ "source": "dw_unified.harlequin_catalog (read-only mirror)",
+ "cohort": "on_shopify = true",
+ "headline": {
+ "on_shopify_total": 32,
+ "sell_price_computable": 31,
+ "cost_gap": 1,
+ "dw_sku_gap": 5,
+ "retail_placeholder_150": 1,
+ "retail_matches_formula": 31,
+ "below_map_floor_2x": 31,
+ "sample_price": 4.25
+ },
+ "cost_gap_rows": [
+ {
+ "dw_sku": "DWHA-570138",
+ "mfr_sku": "cranes-in-flight-marine-haw0065-05"
+ }
+ ],
+ "dw_sku_gap_rows": [
+ {
+ "mfr_sku": "flores-cornflower-first-light-denim-haw0288-03",
+ "computedRetail": 269.68
+ },
+ {
+ "mfr_sku": "houndstooth-black-earth-soft-focus-haw0289-04",
+ "computedRetail": 208.14
+ },
+ {
+ "mfr_sku": "houndstooth-first-light-mist-haw0289-01",
+ "computedRetail": 208.14
+ },
+ {
+ "mfr_sku": "houndstooth-seaglass-soft-focus-haw0289-02",
+ "computedRetail": 208.14
+ },
+ {
+ "mfr_sku": "houndstooth-taupe-soft-focus-haw0289-03",
+ "computedRetail": 208.14
+ }
+ ],
+ "rows": [
+ {
+ "dw_sku": "DWHA-570020",
+ "mfr_sku": "amazilia-gooseberry-haw0045-04",
+ "cost": 140,
+ "storedRetail": 253.39,
+ "computedRetail": 253.39,
+ "mapFloor": 280,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570138",
+ "mfr_sku": "cranes-in-flight-marine-haw0065-05",
+ "cost": null,
+ "storedRetail": 150,
+ "computedRetail": null,
+ "mapFloor": null,
+ "sellPriceComputable": false,
+ "costGap": true,
+ "dwskuGap": false,
+ "retailPlaceholder": true,
+ "formulaMatch": false,
+ "belowMapFloor": false
+ },
+ {
+ "dw_sku": "DWHA-570157",
+ "mfr_sku": "demoiselle-cornflower-first-light-honey-haw0067-09",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570161",
+ "mfr_sku": "demoiselle-kelly-first-light-blush-haw0067-08",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570163",
+ "mfr_sku": "demoiselle-positano-first-light-taupe-haw0067-07",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570164",
+ "mfr_sku": "demoiselle-seaglass-first-light-sand-haw0067-10",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570211",
+ "mfr_sku": "epitome-copper-gold-sepia-haw0076-01",
+ "cost": 149,
+ "storedRetail": 269.68,
+ "computedRetail": 269.68,
+ "mapFloor": 298,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570213",
+ "mfr_sku": "epitome-mint-duckegg-smoke-haw0076-02",
+ "cost": 149,
+ "storedRetail": 269.68,
+ "computedRetail": 269.68,
+ "mapFloor": 298,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570214",
+ "mfr_sku": "epitome-turquoise-pea-gilver-haw0076-04",
+ "cost": 149,
+ "storedRetail": 269.68,
+ "computedRetail": 269.68,
+ "mapFloor": 298,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570234",
+ "mfr_sku": "fleur-de-joie-chalk-clover-honey-haw0287-02",
+ "cost": 149,
+ "storedRetail": 269.68,
+ "computedRetail": 269.68,
+ "mapFloor": 298,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570249",
+ "mfr_sku": "flores-clover-kelly-aqua-haw0288-02",
+ "cost": 149,
+ "storedRetail": 269.68,
+ "computedRetail": 269.68,
+ "mapFloor": 298,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": null,
+ "mfr_sku": "flores-cornflower-first-light-denim-haw0288-03",
+ "cost": 149,
+ "storedRetail": 269.68,
+ "computedRetail": 269.68,
+ "mapFloor": 298,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": true,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570250",
+ "mfr_sku": "flores-rosewood-baked-terracotta-haw0288-01",
+ "cost": 149,
+ "storedRetail": 269.68,
+ "computedRetail": 269.68,
+ "mapFloor": 298,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570252",
+ "mfr_sku": "flourish-first-light-nectar-haw0250-01",
+ "cost": 149,
+ "storedRetail": 269.68,
+ "computedRetail": 269.68,
+ "mapFloor": 298,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570274",
+ "mfr_sku": "gardinum-chalk-wedgwood-gold-haw0023-03",
+ "cost": 149,
+ "storedRetail": 269.68,
+ "computedRetail": 269.68,
+ "mapFloor": 298,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": null,
+ "mfr_sku": "houndstooth-black-earth-soft-focus-haw0289-04",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": true,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": null,
+ "mfr_sku": "houndstooth-first-light-mist-haw0289-01",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": true,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": null,
+ "mfr_sku": "houndstooth-seaglass-soft-focus-haw0289-02",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": true,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": null,
+ "mfr_sku": "houndstooth-taupe-soft-focus-haw0289-03",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": true,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570330",
+ "mfr_sku": "kamanu-indigo-pebble-haw0097-04",
+ "cost": 140,
+ "storedRetail": 253.39,
+ "computedRetail": 253.39,
+ "mapFloor": 280,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570331",
+ "mfr_sku": "kamanu-mandarin-fig-haw0097-02",
+ "cost": 140,
+ "storedRetail": 253.39,
+ "computedRetail": 253.39,
+ "mapFloor": 280,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570421",
+ "mfr_sku": "lotus-pearl-haw0042-01",
+ "cost": 140,
+ "storedRetail": 253.39,
+ "computedRetail": 253.39,
+ "mapFloor": 280,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570423",
+ "mfr_sku": "lotus-silver-haw0042-04",
+ "cost": 140,
+ "storedRetail": 253.39,
+ "computedRetail": 253.39,
+ "mapFloor": 280,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570427",
+ "mfr_sku": "lucielle-chalk-linen-haw0130-01",
+ "cost": 126,
+ "storedRetail": 228.05,
+ "computedRetail": 228.05,
+ "mapFloor": 252,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570428",
+ "mfr_sku": "lucielle-putty-chalk-haw0130-02",
+ "cost": 126,
+ "storedRetail": 228.05,
+ "computedRetail": 228.05,
+ "mapFloor": 252,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570492",
+ "mfr_sku": "mirabella-pebble-haw0062-03",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570493",
+ "mfr_sku": "mirabella-sky-diffused-light-haw0062-04",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570607",
+ "mfr_sku": "salice-heather-gilver-haw0081-05",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570608",
+ "mfr_sku": "salice-mint-emerald-haw0081-02",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570609",
+ "mfr_sku": "salice-ochre-sepia-haw0081-01",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570610",
+ "mfr_sku": "salice-rose-navy-haw0081-04",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ },
+ {
+ "dw_sku": "DWHA-570611",
+ "mfr_sku": "salice-tangerine-gilver-haw0081-03",
+ "cost": 115,
+ "storedRetail": 208.14,
+ "computedRetail": 208.14,
+ "mapFloor": 230,
+ "sellPriceComputable": true,
+ "costGap": false,
+ "dwskuGap": false,
+ "retailPlaceholder": false,
+ "formulaMatch": true,
+ "belowMapFloor": true
+ }
+ ]
+}
\ No newline at end of file
diff --git a/artifacts/harlequin-analysis.md b/artifacts/harlequin-analysis.md
new file mode 100644
index 0000000..186e440
--- /dev/null
+++ b/artifacts/harlequin-analysis.md
@@ -0,0 +1,24 @@
+# Harlequin missing-sample + price analysis (TK-10870)
+
+Generated: 2026-08-30T16:19:27.702Z · Source: dw_unified.harlequin_catalog (read-only mirror) · Cohort: `on_shopify = true`
+
+| metric | value |
+| --- | ---: |
+| on_shopify_total | 32 |
+| sell_price_computable | 31 |
+| cost_gap | 1 |
+| dw_sku_gap | 5 |
+| retail_placeholder_150 | 1 |
+| retail_matches_formula | 31 |
+| below_map_floor_2x | 31 |
+| sample_price | 4.25 |
+
+## Cost-gap rows (sell price NOT computable — cost follow-up needed)
+- `cranes-in-flight-marine-haw0065-05` (dw_sku DWHA-570138)
+
+## Null dw_sku rows (priced, but missing the DW SKU)
+- `flores-cornflower-first-light-denim-haw0288-03` → computed retail $269.68
+- `houndstooth-black-earth-soft-focus-haw0289-04` → computed retail $208.14
+- `houndstooth-first-light-mist-haw0289-01` → computed retail $208.14
+- `houndstooth-seaglass-soft-focus-haw0289-02` → computed retail $208.14
+- `houndstooth-taupe-soft-focus-haw0289-03` → computed retail $208.14
diff --git a/scripts/analyze-harlequin.mjs b/scripts/analyze-harlequin.mjs
new file mode 100644
index 0000000..d0af3a3
--- /dev/null
+++ b/scripts/analyze-harlequin.mjs
@@ -0,0 +1,120 @@
+#!/usr/bin/env node
+// analyze-harlequin.mjs — READ-ONLY Harlequin missing-sample + price analysis (TK-10870).
+//
+// Reads the on-Shopify Harlequin cohort from the dw_unified mirror via `psql`,
+// applies the pure pricing logic in ./pricing.mjs, and writes:
+// artifacts/harlequin-analysis.json (machine-readable)
+// artifacts/harlequin-analysis.md (human report)
+//
+// SAFETY: this NEVER writes to dw_unified or Shopify.
+// * the SQL is asserted SELECT-only before execution (regex guard);
+// * the psql session is forced read-only via PGOPTIONS=default_transaction_read_only=on,
+// so any accidental write statement would be rejected by the server.
+//
+// Usage:
+// node scripts/analyze-harlequin.mjs # live read from the mirror
+// node scripts/analyze-harlequin.mjs --from-json f # offline, from a saved export
+//
+import { execFileSync } from 'node:child_process';
+import { writeFileSync, readFileSync, mkdirSync } from 'node:fs';
+import { fileURLToPath } from 'node:url';
+import { dirname, join } from 'node:path';
+import { summarize, SAMPLE_PRICE } from './pricing.mjs';
+
+const __dir = dirname(fileURLToPath(import.meta.url));
+const ROOT = join(__dir, '..');
+const ART = join(ROOT, 'artifacts');
+
+// The one and only query. Kept as a constant so the SELECT-only guard is meaningful.
+const QUERY = `
+ SELECT dw_sku, mfr_sku, pattern_name, color_name, collection,
+ price_retail, price_trade, tariff_amount_per_roll,
+ on_shopify, discontinued, shopify_product_id
+ FROM harlequin_catalog
+ WHERE on_shopify = true
+ ORDER BY mfr_sku`;
+
+function assertSelectOnly(sql) {
+ const stripped = sql.replace(/\s+/g, ' ').trim().toLowerCase();
+ if (!stripped.startsWith('select')) throw new Error('refusing: query is not a SELECT');
+ if (/;\s*\S/.test(sql)) throw new Error('refusing: multiple statements');
+ if (/\b(insert|update|delete|drop|alter|truncate|create|grant|copy)\b/.test(stripped))
+ throw new Error('refusing: write keyword detected');
+}
+
+function readLive() {
+ assertSelectOnly(QUERY);
+ const wrapped = `SELECT coalesce(json_agg(t), '[]') FROM (${QUERY}) t`;
+ const out = execFileSync('psql', ['-tAc', wrapped], {
+ env: {
+ ...process.env,
+ PGHOST: process.env.PGHOST || '/tmp',
+ PGDATABASE: process.env.PGDATABASE || 'dw_unified',
+ PGOPTIONS: '-c default_transaction_read_only=on', // server rejects any write
+ },
+ encoding: 'utf8',
+ maxBuffer: 32 * 1024 * 1024,
+ });
+ return JSON.parse(out.trim() || '[]');
+}
+
+function main() {
+ const fromJsonIdx = process.argv.indexOf('--from-json');
+ const rows = fromJsonIdx >= 0
+ ? JSON.parse(readFileSync(process.argv[fromJsonIdx + 1], 'utf8'))
+ : readLive();
+
+ const s = summarize(rows);
+ const costGaps = s.classified.filter((r) => r.costGap);
+ const dwskuGaps = s.classified.filter((r) => r.dwskuGap);
+
+ const report = {
+ ticket: 'TK-10870',
+ generated_utc: new Date().toISOString(),
+ source: 'dw_unified.harlequin_catalog (read-only mirror)',
+ cohort: 'on_shopify = true',
+ headline: {
+ on_shopify_total: s.total,
+ sell_price_computable: s.computable,
+ cost_gap: s.costGap,
+ dw_sku_gap: s.dwskuGap,
+ retail_placeholder_150: s.retailPlaceholder,
+ retail_matches_formula: s.formulaMatch,
+ below_map_floor_2x: s.belowMapFloor,
+ sample_price: SAMPLE_PRICE,
+ },
+ cost_gap_rows: costGaps.map((r) => ({ dw_sku: r.dw_sku, mfr_sku: r.mfr_sku })),
+ dw_sku_gap_rows: dwskuGaps.map((r) => ({ mfr_sku: r.mfr_sku, computedRetail: r.computedRetail })),
+ rows: s.classified,
+ };
+
+ mkdirSync(ART, { recursive: true });
+ writeFileSync(join(ART, 'harlequin-analysis.json'), JSON.stringify(report, null, 2));
+
+ const md = [
+ `# Harlequin missing-sample + price analysis (TK-10870)`,
+ ``,
+ `Generated: ${report.generated_utc} · Source: ${report.source} · Cohort: \`${report.cohort}\``,
+ ``,
+ `| metric | value |`,
+ `| --- | ---: |`,
+ ...Object.entries(report.headline).map(([k, v]) => `| ${k} | ${v} |`),
+ ``,
+ `## Cost-gap rows (sell price NOT computable — cost follow-up needed)`,
+ costGaps.length
+ ? costGaps.map((r) => `- \`${r.mfr_sku}\` (dw_sku ${r.dw_sku ?? 'NULL'})`).join('\n')
+ : '- none',
+ ``,
+ `## Null dw_sku rows (priced, but missing the DW SKU)`,
+ dwskuGaps.length
+ ? dwskuGaps.map((r) => `- \`${r.mfr_sku}\` → computed retail $${r.computedRetail}`).join('\n')
+ : '- none',
+ ``,
+ ].join('\n');
+ writeFileSync(join(ART, 'harlequin-analysis.md'), md);
+
+ console.log(JSON.stringify(report.headline, null, 2));
+ console.error(`\nwrote artifacts/harlequin-analysis.json + .md (${s.total} rows)`);
+}
+
+main();
diff --git a/scripts/pricing.mjs b/scripts/pricing.mjs
new file mode 100644
index 0000000..efa4990
--- /dev/null
+++ b/scripts/pricing.mjs
@@ -0,0 +1,110 @@
+// pricing.mjs — PURE, dependency-free Harlequin pricing + row-classification logic.
+// No DB, no network, no side effects. Unit-tested by tests/pricing.test.mjs.
+//
+// Context (TK-10870): the Harlequin per-roll DW sell price is computed with the
+// DW-standard markup retail = cost / 0.65 / 0.85 (== cost * 1.8100...).
+// Verified against the live dw_unified mirror on 2026-08-30: 732/842 catalog rows
+// and 31/32 on-Shopify rows have round(price_retail / price_trade, 3) == 1.810.
+//
+// This module is READ-ONLY analysis support. It NEVER writes to Shopify or dw_unified.
+
+/** DW standard markup divisors applied to net cost (wholesale). */
+export const DW_MARKUP_DIVISORS = [0.65, 0.85];
+
+/** Fixed memo-sample price on the DW store. */
+export const SAMPLE_PRICE = 4.25;
+
+/** The flat placeholder retail a row carries before a real cost is harvested. */
+export const RETAIL_PLACEHOLDER = 150.0;
+
+/**
+ * Hypothesised vendor MAP / minimum-sell floor multiple, from the epic's S1
+ * coordinator note "SSP=2xTRADE". Used only to FLAG rows whose computed retail
+ * falls below 2x cost — surfaced for Steve, never auto-applied.
+ */
+export const MAP_FLOOR_MULTIPLE = 2.0;
+
+/** Round to cents the way the pipeline does (half-up on 2 decimals). */
+export function roundCents(n) {
+ return Math.round((n + Number.EPSILON) * 100) / 100;
+}
+
+/**
+ * Compute the DW sell price from a net cost.
+ * @param {number} cost net/wholesale cost per single roll
+ * @returns {number|null} rounded retail, or null when cost is missing/non-positive
+ */
+export function computeRetail(cost) {
+ if (cost == null || !(cost > 0)) return null;
+ const retail = DW_MARKUP_DIVISORS.reduce((acc, d) => acc / d, cost);
+ return roundCents(retail);
+}
+
+/** The MAP floor (2x cost) hypothesis, or null when cost is missing. */
+export function computeMapFloor(cost) {
+ if (cost == null || !(cost > 0)) return null;
+ return roundCents(cost * MAP_FLOOR_MULTIPLE);
+}
+
+/**
+ * True when the stored retail equals the formula-derived retail (to the cent).
+ * Tolerance of half a cent absorbs rounding-order differences.
+ */
+export function retailMatchesFormula(cost, storedRetail) {
+ const derived = computeRetail(cost);
+ if (derived == null || storedRetail == null) return false;
+ return Math.abs(derived - Number(storedRetail)) <= 0.005;
+}
+
+/**
+ * Classify one catalog row for the missing-sample + price analysis.
+ * Input row keys: dw_sku, mfr_sku, price_trade (cost), price_retail, on_shopify.
+ * Returns a plain object of booleans + derived numbers. Pure — no I/O.
+ */
+export function classifyRow(row) {
+ const cost = row.price_trade == null ? null : Number(row.price_trade);
+ const storedRetail = row.price_retail == null ? null : Number(row.price_retail);
+
+ const costGap = cost == null || !(cost > 0);
+ const dwskuGap = row.dw_sku == null || String(row.dw_sku).trim() === '';
+ const retailPlaceholder = storedRetail != null && Math.abs(storedRetail - RETAIL_PLACEHOLDER) <= 0.005;
+
+ const computedRetail = computeRetail(cost);
+ const mapFloor = computeMapFloor(cost);
+ const formulaMatch = retailMatchesFormula(cost, storedRetail);
+ const belowMapFloor = computedRetail != null && mapFloor != null && computedRetail < mapFloor;
+
+ return {
+ dw_sku: row.dw_sku ?? null,
+ mfr_sku: row.mfr_sku ?? null,
+ cost,
+ storedRetail,
+ computedRetail, // null when costGap
+ mapFloor, // null when costGap
+ sellPriceComputable: !costGap,
+ costGap,
+ dwskuGap,
+ retailPlaceholder,
+ formulaMatch,
+ belowMapFloor,
+ };
+}
+
+/**
+ * Summarise a set of classified rows into headline counts.
+ * @param {Array} rows raw catalog rows
+ */
+export function summarize(rows) {
+ const classified = rows.map(classifyRow);
+ const count = (pred) => classified.filter(pred).length;
+ return {
+ total: classified.length,
+ computable: count((r) => r.sellPriceComputable),
+ costGap: count((r) => r.costGap),
+ dwskuGap: count((r) => r.dwskuGap),
+ retailPlaceholder: count((r) => r.retailPlaceholder),
+ formulaMatch: count((r) => r.formulaMatch),
+ belowMapFloor: count((r) => r.belowMapFloor),
+ classified,
+ };
+}
diff --git a/tests/pricing.test.mjs b/tests/pricing.test.mjs
new file mode 100644
index 0000000..0090a54
--- /dev/null
+++ b/tests/pricing.test.mjs
@@ -0,0 +1,96 @@
+// pricing.test.mjs — dependency-free unit tests for the pure pricing library.
+// Run: node --test (no DB, no network, no npm install)
+import { test } from 'node:test';
+import assert from 'node:assert/strict';
+import {
+ computeRetail,
+ computeMapFloor,
+ retailMatchesFormula,
+ classifyRow,
+ summarize,
+ roundCents,
+ SAMPLE_PRICE,
+ RETAIL_PLACEHOLDER,
+} from '../scripts/pricing.mjs';
+
+test('computeRetail applies cost/0.65/0.85 == 1.810x and rounds to cents', () => {
+ // Real cohort values verified against the live mirror on 2026-08-30.
+ assert.equal(computeRetail(140), 253.39); // Amazilia / Kamanu / Lotus
+ assert.equal(computeRetail(115), 208.14); // Demoiselle / Salice / Houndstooth
+ assert.equal(computeRetail(149), 269.68); // Epitome / Flores / Gardinum
+ assert.equal(computeRetail(126), 228.05); // Lucielle
+});
+
+test('computeRetail multiplier is exactly the DW 1.8100 factor', () => {
+ const r = computeRetail(100);
+ assert.equal(r, 181.0); // 100/0.65/0.85 = 181.00
+});
+
+test('computeRetail guards missing / non-positive cost', () => {
+ assert.equal(computeRetail(null), null);
+ assert.equal(computeRetail(undefined), null);
+ assert.equal(computeRetail(0), null);
+ assert.equal(computeRetail(-5), null);
+});
+
+test('computeMapFloor is 2x cost (S1 SSP=2xTRADE hypothesis)', () => {
+ assert.equal(computeMapFloor(149), 298.0);
+ assert.equal(computeMapFloor(null), null);
+});
+
+test('DW formula retail is BELOW a 2x-cost MAP floor (margin flag)', () => {
+ // 1.81x < 2.00x always, so every computed retail trips the MAP-floor flag.
+ const cost = 149;
+ assert.ok(computeRetail(cost) < computeMapFloor(cost));
+});
+
+test('retailMatchesFormula tolerates half-cent rounding', () => {
+ assert.equal(retailMatchesFormula(149, 269.68), true);
+ assert.equal(retailMatchesFormula(149, 269.69), false);
+ assert.equal(retailMatchesFormula(null, 150), false);
+});
+
+test('classifyRow flags a real priced on-Shopify row as computable', () => {
+ const c = classifyRow({ dw_sku: 'DWHA-570211', mfr_sku: 'epitome-...-01', price_trade: '149.00', price_retail: '269.68', on_shopify: true });
+ assert.equal(c.sellPriceComputable, true);
+ assert.equal(c.costGap, false);
+ assert.equal(c.dwskuGap, false);
+ assert.equal(c.formulaMatch, true);
+ assert.equal(c.computedRetail, 269.68);
+ assert.equal(c.belowMapFloor, true); // 269.68 < 298.00
+});
+
+test('classifyRow flags the cost-gap placeholder row (Cranes In Flight/Marine)', () => {
+ const c = classifyRow({ dw_sku: 'DWHA-570138', mfr_sku: 'cranes-in-flight-marine-haw0065-05', price_trade: null, price_retail: '150.00', on_shopify: true });
+ assert.equal(c.costGap, true);
+ assert.equal(c.sellPriceComputable, false);
+ assert.equal(c.retailPlaceholder, true);
+ assert.equal(c.computedRetail, null);
+});
+
+test('classifyRow flags a null-dw_sku row (Houndstooth)', () => {
+ const c = classifyRow({ dw_sku: null, mfr_sku: 'houndstooth-taupe-soft-focus-haw0289-03', price_trade: '115.00', price_retail: '208.14', on_shopify: true });
+ assert.equal(c.dwskuGap, true);
+ assert.equal(c.sellPriceComputable, true); // priced, just missing the DW SKU
+});
+
+test('summarize reproduces the 32-row cohort headline counts', () => {
+ // Synthetic mini-cohort mirroring the live shape: 31 priced (1 also null-sku), 1 cost-gap.
+ const rows = [];
+ for (let i = 0; i < 26; i++) rows.push({ dw_sku: `DWHA-${i}`, mfr_sku: `p-${i}`, price_trade: '115.00', price_retail: '208.14', on_shopify: true });
+ for (let i = 0; i < 5; i++) rows.push({ dw_sku: null, mfr_sku: `houndstooth-${i}`, price_trade: '115.00', price_retail: '208.14', on_shopify: true });
+ rows.push({ dw_sku: 'DWHA-570138', mfr_sku: 'cranes', price_trade: null, price_retail: '150.00', on_shopify: true });
+ const s = summarize(rows);
+ assert.equal(s.total, 32);
+ assert.equal(s.computable, 31);
+ assert.equal(s.costGap, 1);
+ assert.equal(s.dwskuGap, 5);
+ assert.equal(s.retailPlaceholder, 1);
+ assert.equal(s.formulaMatch, 31);
+});
+
+test('constants are the DW canonical values', () => {
+ assert.equal(SAMPLE_PRICE, 4.25);
+ assert.equal(RETAIL_PLACEHOLDER, 150.0);
+ assert.equal(roundCents(1.005), 1.01); // EPSILON nudge gives correct half-up rounding (not the naive 1.00)
+});
(oldest)
·
back to Harlequin Sample Price Analysis
·
TK-10870 cycle1: read-only resolution of the 2 blockers + si ff71dd7 →