price-coverage: add Shopify roll-price outcome layer (correct vs broken @ $4.25)

47bef875a72f213cb1a04166ac9589d50ab35138 · 2026-06-11 11:14:19 -0700 · SteveStudio2

Counts non-sample roll variants per Shopify vendor: priced (>$4.25) vs stuck at the
$4.25 sample price. Sample variants correctly excluded. Writes shopify_price_outcome
+ roll_priced/roll_broken history cols. Surfaces the customer-facing outcome (1269
priced / 4943 broken) alongside the cost-coverage input.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>

Files touched

M shopify/scripts/cadence/compute-price-coverage.js

Diff

commit 47bef875a72f213cb1a04166ac9589d50ab35138
Author: SteveStudio2 <stevestudio2@SteveStudio2s-Mac-Studio.local>
Date:   Thu Jun 11 11:14:19 2026 -0700

    price-coverage: add Shopify roll-price outcome layer (correct vs broken @ $4.25)
    
    Counts non-sample roll variants per Shopify vendor: priced (>$4.25) vs stuck at the
    $4.25 sample price. Sample variants correctly excluded. Writes shopify_price_outcome
    + roll_priced/roll_broken history cols. Surfaces the customer-facing outcome (1269
    priced / 4943 broken) alongside the cost-coverage input.
    
    Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
---
 shopify/scripts/cadence/compute-price-coverage.js | 51 +++++++++++++++++++++--
 1 file changed, 47 insertions(+), 4 deletions(-)

diff --git a/shopify/scripts/cadence/compute-price-coverage.js b/shopify/scripts/cadence/compute-price-coverage.js
index 96073702..41d468c7 100644
--- a/shopify/scripts/cadence/compute-price-coverage.js
+++ b/shopify/scripts/cadence/compute-price-coverage.js
@@ -59,8 +59,22 @@ CREATE TABLE IF NOT EXISTS vendor_price_coverage_history (
   vendors_green     INTEGER,
   vendors_active    INTEGER,
   vendors_confirmed INTEGER
+);
+ALTER TABLE vendor_price_coverage_history ADD COLUMN IF NOT EXISTS roll_priced INTEGER;
+ALTER TABLE vendor_price_coverage_history ADD COLUMN IF NOT EXISTS roll_broken INTEGER;
+-- OUTCOME layer: live Shopify roll-variant price correctness, per Shopify vendor.
+-- "roll" = non-sample variant. priced = price > sample $4.25 (correct); broken =
+-- non-sample variant stuck at the $4.25 sample price (the real damage). Unpriced
+-- (null/0) are drafts and excluded from the %.
+CREATE TABLE IF NOT EXISTS shopify_price_outcome (
+  vendor       TEXT PRIMARY KEY,
+  roll_priced  INTEGER NOT NULL DEFAULT 0,
+  roll_broken  INTEGER NOT NULL DEFAULT 0,
+  computed_at  TIMESTAMPTZ NOT NULL DEFAULT now()
 );`;
 
+const SAMPLE_PRICE = 4.25;   // correct Sample-variant price; non-sample rows at this value are broken rolls
+
 // safe identifier (table name) check — defends the dynamic SQL below
 const SAFE_IDENT = s => typeof s === 'string' && /^[a-z_][a-z0-9_]*$/i.test(s);
 
@@ -182,7 +196,36 @@ async function upsert(client, row) {
       nOther++;
     }
 
-    // 4) aggregate trend snapshot
+    // 4) OUTCOME layer — live Shopify roll-price correctness (non-sample variants)
+    const rollFilter = `(variant_sku IS NULL OR variant_sku NOT ILIKE '%sample%')`;
+    let rollPriced = 0, rollBroken = 0;
+    try {
+      const fleet = (await client.query(
+        `SELECT count(*) FILTER (WHERE price::numeric > $1)::int  AS priced,
+                count(*) FILTER (WHERE price::numeric = $1)::int  AS broken
+           FROM shopify_products WHERE ${rollFilter}`, [SAMPLE_PRICE])).rows[0];
+      rollPriced = fleet.priced; rollBroken = fleet.broken;
+      // per-Shopify-vendor (only vendors with any priced or broken roll)
+      const perVendor = (await client.query(
+        `SELECT vendor,
+                count(*) FILTER (WHERE price::numeric > $1)::int AS roll_priced,
+                count(*) FILTER (WHERE price::numeric = $1)::int AS roll_broken
+           FROM shopify_products
+           WHERE vendor IS NOT NULL AND ${rollFilter}
+           GROUP BY vendor
+           HAVING count(*) FILTER (WHERE price::numeric >= $1) > 0`, [SAMPLE_PRICE])).rows;
+      await client.query('TRUNCATE shopify_price_outcome');
+      for (const v of perVendor) {
+        await client.query(
+          `INSERT INTO shopify_price_outcome (vendor, roll_priced, roll_broken, computed_at)
+           VALUES ($1,$2,$3, now())`, [v.vendor, v.roll_priced, v.roll_broken]);
+      }
+      log(`  [outcome] live rolls: ${rollPriced} priced / ${rollBroken} broken @ $${SAMPLE_PRICE} across ${perVendor.length} vendors`);
+    } catch (e) {
+      log(`  [outcome] skipped: ${e.message}`);
+    }
+
+    // 5) aggregate trend snapshot
     const agg = await client.query(
       `SELECT COALESCE(SUM(total_skus),0)::int  AS total_skus,
               COALESCE(SUM(costed_skus),0)::int AS costed_skus,
@@ -193,9 +236,9 @@ async function upsert(client, row) {
     const a = agg.rows[0];
     await client.query(
       `INSERT INTO vendor_price_coverage_history
-         (snapshot_at, total_skus, costed_skus, vendors_green, vendors_active, vendors_confirmed)
-       VALUES (now(), $1,$2,$3,$4,$5)`,
-      [a.total_skus, a.costed_skus, a.vendors_green, a.vendors_active, a.vendors_confirmed]);
+         (snapshot_at, total_skus, costed_skus, vendors_green, vendors_active, vendors_confirmed, roll_priced, roll_broken)
+       VALUES (now(), $1,$2,$3,$4,$5,$6,$7)`,
+      [a.total_skus, a.costed_skus, a.vendors_green, a.vendors_active, a.vendors_confirmed, rollPriced, rollBroken]);
 
     const pct = a.total_skus > 0 ? ((a.costed_skus / a.total_skus) * 100).toFixed(1) : '0.0';
     log(`\n✅ coverage recomputed: ${nImportable} importable + ${nOther} other vendors`);

← 430f4bcb Stage launchd template for optional hands-off Romo push resu · back to Designer Wallcoverings · Repurpose dw-pricing-resume loop → Schumacher API cadence (b 0ed8df7d →