← back to Dw Add Sellable Variant Tk10902

tk10875-codex/analyze-readonly.mjs

178 lines

#!/usr/bin/env node
// TK-10875 independent Codex classifier.
// READ-ONLY: every database call starts an explicit READ ONLY transaction.
// This script only writes local report artifacts in this directory.

import { execFileSync } from 'node:child_process';
import { mkdirSync, writeFileSync } from 'node:fs';
import { dirname, join } from 'node:path';
import { fileURLToPath } from 'node:url';

const here = dirname(fileURLToPath(import.meta.url));
const db = 'host=/tmp dbname=dw_unified';

function queryJson(sql) {
  if (!/^\s*(select|with)\b/i.test(sql)) throw new Error('classifier permits SELECT/WITH only');
  const wrapped = `SET TRANSACTION READ ONLY; SELECT coalesce(json_agg(row_to_json(q)), '[]'::json) FROM (${sql}) q;`;
  const raw = execFileSync('psql', [db, '-X', '--single-transaction', '-At', '-v', 'ON_ERROR_STOP=1', '-c', wrapped], {
    encoding: 'utf8', maxBuffer: 32 * 1024 * 1024
  }).trim();
  const jsonLine = raw.split('\n').find(line => line.startsWith('['));
  if (!jsonLine) throw new Error(`database returned no JSON payload: ${raw.slice(0, 300)}`);
  return JSON.parse(jsonLine);
}

if (process.argv.includes('--probe-write-guard')) {
  try {
    queryJson('UPDATE shopify_products SET status=status');
    throw new Error('write guard failed open');
  } catch (error) {
    if (!String(error.message).includes('SELECT/WITH only')) throw error;
    console.log('PASS: mutation statement rejected before database execution');
    process.exit(0);
  }
}

const scope = `upper(status)='ACTIVE' AND has_product_variant IS NOT TRUE`;
const summary = queryJson(`
  SELECT
    count(*)::int AS active_unbuyable,
    count(*) FILTER (WHERE has_product_variant IS FALSE)::int AS explicit_false,
    count(*) FILTER (WHERE has_product_variant IS NULL)::int AS flag_null,
    count(*) FILTER (WHERE coalesce(cost,cost_price,net_price,0)>0)::int AS product_cost_present,
    count(*) FILTER (WHERE coalesce(cost,cost_price,net_price,0)<=0)::int AS product_cost_missing,
    max(synced_at) AS mirror_max_synced
  FROM shopify_products WHERE ${scope}
`)[0];

const classes = queryJson(`
  SELECT CASE
    WHEN has_product_variant IS NULL THEN 'flag_null'
    WHEN variant_count=1 AND has_sample_variant IS TRUE AND min_variant_price=4.25 THEN 'clean_sample_only'
    WHEN variant_count=1 THEN 'one_variant_irregular'
    WHEN variant_count>1 THEN 'multi_variant_no_sellable'
    ELSE 'other'
  END AS class, count(*)::int AS products
  FROM shopify_products WHERE ${scope}
  GROUP BY 1 ORDER BY 2 DESC
`);

const vendors = queryJson(`
  SELECT vendor, count(*)::int AS products,
         count(*) FILTER (WHERE coalesce(cost,cost_price,net_price,0)>0)::int AS product_cost_present
  FROM shopify_products WHERE ${scope}
  GROUP BY vendor ORDER BY count(*) DESC, vendor LIMIT 40
`);

// Exact-key joins only. No fuzzy title/pattern matching is allowed in the safe pilot.
// price_kind controls whether a match can feed cost-based auto-pricing.
const staging = queryJson(`
  WITH u AS (
    SELECT shopify_id, regexp_replace(shopify_id, '^.*[/]', '') AS numeric_product_id,
           vendor, title, mfr_sku, dw_sku, variant_count, min_variant_price,
           has_sample_variant
    FROM shopify_products
    WHERE ${scope} AND vendor IN ('Wolf Gordon','Coordonné','Malibu Wallpaper')
  ), catalog AS (
    SELECT 'Wolf Gordon'::text vendor, id::text catalog_id, mfr_sku, dw_sku,
           shopify_product_id, price_trade AS source_value,
           'verified_trade_cost'::text price_kind, 'wolf_gordon_catalog.price_trade'::text source
    FROM wolf_gordon_catalog WHERE price_trade>0
    UNION ALL
    SELECT 'Coordonné', id::text, mfr_sku, dw_sku, shopify_product_id, price_retail,
           'retail_evidence_only', 'coordonne_catalog.price_retail'
    FROM coordonne_catalog WHERE price_retail>0
    UNION ALL
    SELECT 'Malibu Wallpaper', id::text, mfr_sku, dw_sku, shopify_product_id, net_cost,
           'verified_net_cost', 'wallquest_catalog.net_cost'
    FROM wallquest_catalog WHERE net_cost>0
  ), hits AS (
    SELECT u.*, c.catalog_id, c.source_value, c.price_kind, c.source,
      CASE
        WHEN nullif(trim(u.mfr_sku),'') IS NOT NULL AND upper(trim(c.mfr_sku))=upper(trim(u.mfr_sku)) THEN 'mfr_sku'
        WHEN nullif(trim(u.dw_sku),'') IS NOT NULL AND upper(trim(c.dw_sku))=upper(trim(u.dw_sku)) THEN 'dw_sku'
        WHEN nullif(trim(c.shopify_product_id),'') IS NOT NULL
          AND regexp_replace(c.shopify_product_id, '^.*[/]', '')=u.numeric_product_id THEN 'shopify_product_id'
      END AS match_key
    FROM u JOIN catalog c ON c.vendor=u.vendor AND (
      (nullif(trim(u.mfr_sku),'') IS NOT NULL AND upper(trim(c.mfr_sku))=upper(trim(u.mfr_sku))) OR
      (nullif(trim(u.dw_sku),'') IS NOT NULL AND upper(trim(c.dw_sku))=upper(trim(u.dw_sku))) OR
      (nullif(trim(c.shopify_product_id),'') IS NOT NULL AND regexp_replace(c.shopify_product_id, '^.*[/]', '')=u.numeric_product_id)
    )
  ), per_product AS (
    SELECT vendor, shopify_id, numeric_product_id, title, mfr_sku, dw_sku,
           variant_count, min_variant_price, has_sample_variant,
           count(DISTINCT catalog_id)::int AS catalog_matches,
           min(source_value) AS min_source_value, max(source_value) AS max_source_value,
           min(price_kind) AS price_kind, min(source) AS source,
           string_agg(DISTINCT match_key, '+' ORDER BY match_key) AS match_keys
    FROM hits GROUP BY vendor,shopify_id,numeric_product_id,title,mfr_sku,dw_sku,
                       variant_count,min_variant_price,has_sample_variant
  )
  SELECT vendor, price_kind, source,
         count(*)::int AS exact_join_products,
         count(*) FILTER (WHERE catalog_matches=1)::int AS unique_exact_join,
         count(*) FILTER (WHERE catalog_matches>1)::int AS ambiguous_exact_join,
         count(*) FILTER (WHERE catalog_matches=1 AND variant_count=1
                            AND has_sample_variant IS TRUE AND min_variant_price=4.25)::int AS clean_unique_pilot
  FROM per_product GROUP BY vendor,price_kind,source ORDER BY vendor
`);

const pilots = queryJson(`
  WITH u AS (
    SELECT shopify_id, regexp_replace(shopify_id, '^.*[/]', '') AS product_id,
           vendor,title,mfr_sku,dw_sku
    FROM shopify_products
    WHERE ${scope} AND variant_count=1 AND has_sample_variant IS TRUE AND min_variant_price=4.25
      AND vendor IN ('Wolf Gordon','Coordonné','Malibu Wallpaper')
  ), c AS (
    SELECT 'Wolf Gordon'::text vendor,id::text catalog_id,mfr_sku,dw_sku,shopify_product_id,
           price_trade source_value,'verified_trade_cost'::text price_kind,'wolf_gordon_catalog.price_trade'::text source
      FROM wolf_gordon_catalog WHERE price_trade>0
    UNION ALL SELECT 'Coordonné',id::text,mfr_sku,dw_sku,shopify_product_id,price_retail,
           'retail_evidence_only','coordonne_catalog.price_retail' FROM coordonne_catalog WHERE price_retail>0
    UNION ALL SELECT 'Malibu Wallpaper',id::text,mfr_sku,dw_sku,shopify_product_id,net_cost,
           'verified_net_cost','wallquest_catalog.net_cost' FROM wallquest_catalog WHERE net_cost>0
  ), matched AS (
    SELECT u.*, c.catalog_id,c.source_value,c.price_kind,c.source,
      CASE WHEN nullif(trim(u.mfr_sku),'') IS NOT NULL AND upper(trim(c.mfr_sku))=upper(trim(u.mfr_sku)) THEN 'mfr_sku'
           WHEN nullif(trim(u.dw_sku),'') IS NOT NULL AND upper(trim(c.dw_sku))=upper(trim(u.dw_sku)) THEN 'dw_sku'
           ELSE 'shopify_product_id' END match_key
    FROM u JOIN c ON c.vendor=u.vendor AND (
      (nullif(trim(u.mfr_sku),'') IS NOT NULL AND upper(trim(c.mfr_sku))=upper(trim(u.mfr_sku))) OR
      (nullif(trim(u.dw_sku),'') IS NOT NULL AND upper(trim(c.dw_sku))=upper(trim(u.dw_sku))) OR
      (nullif(trim(c.shopify_product_id),'') IS NOT NULL AND regexp_replace(c.shopify_product_id, '^.*[/]', '')=u.product_id))
  ), unique_hits AS (
    SELECT *,count(*) OVER(PARTITION BY shopify_id) match_count FROM matched
  ), ranked AS (
    SELECT *,row_number() OVER(PARTITION BY vendor ORDER BY product_id) vendor_rank FROM unique_hits WHERE match_count=1
  )
  SELECT vendor,product_id,title,mfr_sku,dw_sku,match_key,source,source_value,price_kind,
         CASE WHEN price_kind='retail_evidence_only' THEN 'HOLD_COST_SEMANTICS'
              ELSE 'DRY_RUN_ELIGIBLE_COST_INPUT' END AS pilot_verdict
  FROM ranked WHERE vendor_rank<=10 ORDER BY vendor,product_id
`);

const result = {
  ticket: 'TK-10875', model: 'codex', generated_at: new Date().toISOString(),
  mode: 'read-only-local-mirror', production_writes: 0,
  definition: "upper(status)='ACTIVE' AND has_product_variant IS NOT TRUE",
  summary, classes, top_vendors: vendors, staging_exact_join: staging,
  decision: {
    coordonne_price_retail: 'retail_evidence_only',
    dtd_vote: 'B (5/5 valid; Muse unavailable)',
    post_decision_codex: 'KEEP'
  },
  caveats: [
    'Local dw_unified is a mirror, not the canonical Shopify catalog.',
    'Exact joins use only mfr_sku, dw_sku, or Shopify product id; fuzzy joins are excluded.',
    'A positive retail price is not promoted to cost without verified semantics.',
    'Pilot rows are plans only; no activation, variant creation, or catalog update occurs.'
  ]
};

mkdirSync(here, { recursive: true });
writeFileSync(join(here, 'classification.json'), JSON.stringify(result, null, 2) + '\n');
writeFileSync(join(here, 'pilot-sample.json'), JSON.stringify(pilots, null, 2) + '\n');
console.log(JSON.stringify({ summary, classes, staging, pilot_rows: pilots.length }, null, 2));