`README.md`

104 lines
# dw-unbuyable-recovery-pilot — TK-10875

Classify the DW "unbuyable" catalog and produce a **bounded, dry-run** sellable-variant
recovery plan. **Read-only. No Shopify writes. No dw_unified writes. No catalog activation.**

An *unbuyable* product = an **ACTIVE** product flagged with **no sellable product variant**
(`has_product_variant IS NOT TRUE`) — nominally only a $4.25 memo Sample. In principle the
customer can see it but can't add a sellable roll/yard to cart. **This pilot's headline
result is that that flag is not trustworthy** — see below.

## What this repo does
| File | Role |
|---|---|
| `classify.mjs` | Refreshes the classification against the live `dw_unified` mirror → `data/classification-summary.json` |
| `pilot.mjs` | Classifies the Wolf Gordon cohort and emits a **dry-run** plan → `data/wolf-gordon-dryrun.{json,csv}` |
| `lib/db.mjs` | Read-only psql access (see **Safety**) |
| `sql/*.sql` | The queries, documented, runnable by hand |

Run: `node classify.mjs && node pilot.mjs`

## Findings (live `dw_unified` mirror, 2026-08-30)

- **25,214** ACTIVE products carry the unbuyable flag (down from the ticket's original
  29,561 — 583 recovered + 125 RR/CS repaired earlier this ticket).
- Vendor concentration: Phillipe Romano 8,274 (32.8%), Koroseal 2,448, Phillip Jeffries
  2,338, China Seas 1,776, Coordonné 1,299 — top-5 ≈ 64%.

The pilot's job was to test whether the "quick win" cost-recovery cohorts (Wolf Gordon /
Coordonné / Malibu, flagged by prior read-only cycles) are actually recoverable. **Two
independent layers of the prior estimate fail under validation:**

### Layer 1 — the join-key column-swap
`shopify_products` stores the *numeric* DW-SKU in **`sku`** (`DWWG-535357`) and the
mfr-code form in `dw_sku` (`DWWG-ACT-5067`). The staging `*_catalog` tables store the
numeric DW-SKU in **`dw_sku`**. So the only join that yields cost is:

```
shopify_products.sku  =  <vendor>_catalog.dw_sku
```

`dw_sku↔dw_sku` / `mfr_sku↔mfr_sku` yield **0** — which is what earlier cycles hit,
forcing them onto unreliable staging **table-total** counts (Wolf Gordon "5,135 rows w/
cost"). Corrected join yields **Wolf Gordon 224/269**. Coordonné still yields **0** — but
the deeper cause (per red-team) is a *prefix* mismatch (`coordonne_catalog` is `DWCO2-*`,
Shopify is `DWDC-*`) on top of an empty `price_trade`; its 1,299 are a **prefix-reconcile
job**, not a permanent dead end. Malibu is a genuine dead end here (Shopify line is `CDA-*`
Colour & Design, unrelated to `wallquest_catalog`'s `DWQW-*`).

### Layer 2 — the flag itself is STALE (the decisive one)
All 224 cost-joined Wolf Gordon products have `variant_count = 1`, a **non-sample** variant
SKU, and `min_variant_price` of **$52–$344** (not $4.25). That is the signature of a
product that **already has a correctly-priced sellable roll variant in Shopify** — the
mirror's `has_product_variant` flag is stale because the `dw-shopify-products-sync-hourly`
job is dead. Building a roll variant would **double-add / SKU-collide**, not recover
revenue.

Fleet-wide this (hardened: `variant_count=1` + non-null non-sample SKU + price>$4.50)
stale-flag class is **224 of 25,214** — and **all 224 ARE the Wolf Gordon cohort**, i.e.
the entire "quick win" was already-sellable false positives.

| Cohort | Flagged unbuyable | Cost-joined | **Truly recoverable** |
|---|---:|---:|---:|
| Wolf Gordon | 269 | 224 | **0** (all already-sellable, stale flag) |
| Coordonné | 1,299 | 0 | 0 (needs DWCO2↔DWDC prefix reconcile + cost) |
| Malibu Wallpaper | 339 | 1 | 1 (incidental) |

**Net:** truly-recoverable-from-staging-cost ≈ **0** for these cohorts. The real revenue
hole is the **24,990 genuinely-sample-only** products, and those are **cost-blocked**
(need vendor cost sourcing) — a sourcing project, not a variant-build quick win.

## Recommended next steps (all GATED — none executed here)
1. **Fix the dead `dw-shopify-products-sync-hourly` job and re-sync
   `has_product_variant`.** Until then the 25,214 headline is contaminated by ≥224
   already-sellable false positives, and — since the sync is dead — *no* mirror field is
   fully trustworthy; confirm any candidate against live Shopify before acting.
2. Treat Coordonné's 1,299 as a **prefix-reconcile** task (`vendor_registry` DWCO2↔DWDC),
   then re-test the join.
3. The genuine hole (24,985 sample-only) is **cost-sourcing** — rank by which vendors'
   staging can actually supply *cost* (not just retail).

Any real recovery is a customer-facing Shopify write → route to `vp-dw-commerce` for a
tested, reversible, ledgered variant build (the pattern used for the earlier 583 + 125).

## A note on the pricing cross-check (do not over-trust it)
Retail = `trade_cost / 0.65 / 0.85`. The pilot compares this to the catalog's own
`price_retail` — but that column was **itself** computed as `trade/0.65/0.85`, so a match
is **tautological internal consistency, not price validation**. Real validation needs a
vendor-authoritative source (invoice/portal/authoritative-pricing table). The code and
report label this honestly.

## Safety
`lib/db.mjs` wraps every query as `BEGIN READ ONLY; <select>; ROLLBACK`, fed to `psql`
via **stdin** (not `-c`). Two traps found + fixed while building:
1. `SET default_transaction_read_only=on` in-line protects only *future* transactions —
   `BEGIN READ ONLY` protects the current one.
2. `psql -c "a; b; c"` (PQexec) returns only the **last** statement's result, silently
   dropping the SELECT. Feeding via stdin runs each statement and preserves output while
   READ ONLY persists.

Verified: a persistent `UPDATE` inside `BEGIN READ ONLY` raises
`cannot execute UPDATE in a read-only transaction` before touching a row. The
orchestrators only ever issue SELECTs. Connects to the **local `dw_unified` mirror** over
the `/tmp` socket — never Kamatera-canonical.