dark-inventory scan (c36): only 1,062 sellable-ready archived; cost is the limiter

cb534c8b51ff853d1c563dfc8c3a1bccf9ff39d7 · 2026-06-16 13:56:35 -0700 · Steve Abrams

Read-only. Of 85,993 archived/draft, only 1,062 are fully sellable-ready
(imaged+real-priced>4.25+costed) — because cost coverage on archived is just
1.4% (1,221 rows) and 79% have null/zero price. So the 82k archived CANNOT be
bulk-reactivated without cost re-enrichment. Split: 916 non-April + 146 April.
Top vendors Brunschwig 257/Jeffrey Stevens 249/Lee Jofa 224/Thibaut 119. EXCLUDE
before reactivating: Phillip Jeffries 18 (display-excluded), Nicolette Mayer 6
(hard-archived stay-off), Jeffrey Stevens 249 (likely disco per c8). Mirror-
approximate (c23 status lag).

Files touched

A scripts/dark-inventory/dark-inventory-scan.sh

Diff

commit cb534c8b51ff853d1c563dfc8c3a1bccf9ff39d7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jun 16 13:56:35 2026 -0700

    dark-inventory scan (c36): only 1,062 sellable-ready archived; cost is the limiter
    
    Read-only. Of 85,993 archived/draft, only 1,062 are fully sellable-ready
    (imaged+real-priced>4.25+costed) — because cost coverage on archived is just
    1.4% (1,221 rows) and 79% have null/zero price. So the 82k archived CANNOT be
    bulk-reactivated without cost re-enrichment. Split: 916 non-April + 146 April.
    Top vendors Brunschwig 257/Jeffrey Stevens 249/Lee Jofa 224/Thibaut 119. EXCLUDE
    before reactivating: Phillip Jeffries 18 (display-excluded), Nicolette Mayer 6
    (hard-archived stay-off), Jeffrey Stevens 249 (likely disco per c8). Mirror-
    approximate (c23 status lag).
---
 scripts/dark-inventory/dark-inventory-scan.sh | 22 ++++++++++++++++++++++
 1 file changed, 22 insertions(+)

diff --git a/scripts/dark-inventory/dark-inventory-scan.sh b/scripts/dark-inventory/dark-inventory-scan.sh
new file mode 100755
index 0000000..7b6ab4c
--- /dev/null
+++ b/scripts/dark-inventory/dark-inventory-scan.sh
@@ -0,0 +1,22 @@
+#!/bin/sh
+# dark-inventory-scan (c36) — READ-ONLY: products that are sellable-READY
+# (imaged AND real-priced>$4.25 AND costed) but NOT active = hidden revenue.
+# Split April-2026-archive vs other (avoid overlapping the April catastrophe).
+# Mirror-approximate: shopify_products.status LAGS canonical (c23) — re-validate
+# before any reactivation (which is GATED, a prod write). $0 local.
+PSQL=/opt/homebrew/opt/postgresql@14/bin/psql
+DB="postgresql:///dw_unified?host=/tmp"
+echo "## gate analysis (why the pool is small)"
+$PSQL "$DB" -c "select count(*) not_active,
+  count(*) filter (where image_url>'' ) has_image,
+  count(*) filter (where greatest(coalesce(cost,0),coalesce(cost_price,0),coalesce(net_price,0))>0) has_cost,
+  count(*) filter (where greatest(coalesce(price,0),coalesce(retail_price,0))>4.25) real_price
+  from shopify_products where lower(status) in ('archived','draft');"
+echo "## dark inventory by vendor (imaged+real-priced+costed)"
+$PSQL "$DB" -c "select vendor, count(*) dark,
+  count(*) filter (where date(updated_at_shopify) between '2026-04-15' and '2026-04-22') from_april
+  from shopify_products
+  where lower(status) in ('archived','draft') and image_url>''
+    and greatest(coalesce(price,0),coalesce(retail_price,0))>4.25
+    and greatest(coalesce(cost,0),coalesce(cost_price,0),coalesce(net_price,0))>0
+  group by vendor order by dark desc;"

← 7131130 Kravet split-brain fix: copy kravet_authoritative_pricing (4 · back to Dw Yolo Loop · null-price live-check (c37): mirror price column unreliable; ccfaa8a →