← back to Dw Add Sellable Variant Tk10902

tk10875-cost-source-map/analyze-cost-sources.mjs

89 lines

#!/usr/bin/env node
// TK-10875: read-only discovery of exact-key, positive-cost staging coverage.
// It cannot write to PostgreSQL: only SELECT/WITH statements are accepted and
// every call is wrapped in an explicit READ ONLY transaction plus ROLLBACK.

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';
const ident = value => {
  if (!/^[a-z_][a-z0-9_]*$/i.test(value)) throw new Error(`unsafe SQL identifier: ${value}`);
  return `"${value}"`;
};

function query(sql) {
  if (!/^\s*(select|with)\b/i.test(sql)) throw new Error('only SELECT/WITH is permitted');
  const script = `BEGIN READ ONLY;\nSELECT coalesce(json_agg(row_to_json(q)), '[]'::json) FROM (${sql}) q;\nROLLBACK;\n`;
  const raw = execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-X', '-tA', '-v', 'ON_ERROR_STOP=1'], {
    input: script, encoding: 'utf8', maxBuffer: 64 * 1024 * 1024
  });
  const payload = raw.split('\n').filter(line => !['BEGIN', 'ROLLBACK'].includes(line.trim())).join('\n').trim();
  return payload && payload !== 'null' ? JSON.parse(payload) : [];
}

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

const costPreference = ['net_cost', 'price_trade', 'cost_price', 'wholesale_price', 'wholesale', 'cost'];
const metadata = query(`
  SELECT table_name, array_agg(column_name ORDER BY ordinal_position) AS columns
  FROM information_schema.columns
  WHERE table_schema='public'
  GROUP BY table_name
  HAVING bool_or(column_name IN ('dw_sku','mfr_sku','shopify_product_id'))
     AND bool_or(column_name IN (${costPreference.map(x => `'${x}'`).join(',')}))
  ORDER BY table_name
`);

const scope = `upper(s.status)='ACTIVE' AND s.has_product_variant IS NOT TRUE`;
const sources = [];
for (const meta of metadata) {
  const columns = new Set(meta.columns);
  const costColumn = costPreference.find(column => columns.has(column));
  if (!costColumn) continue;
  const keys = ['dw_sku', 'mfr_sku', 'shopify_product_id'].filter(column => columns.has(column));
  const table = ident(meta.table_name);
  const cost = ident(costColumn);
  const matches = [];
  if (keys.includes('dw_sku')) {
    matches.push(`SELECT s.shopify_id, s.vendor, 'sku=dw_sku'::text match_key FROM shopify_products s JOIN ${table} c ON upper(trim(s.sku))=upper(trim(c.dw_sku::text)) WHERE ${scope} AND c.${cost}>0`);
    matches.push(`SELECT s.shopify_id, s.vendor, 'dw_sku=dw_sku'::text match_key FROM shopify_products s JOIN ${table} c ON upper(trim(s.dw_sku))=upper(trim(c.dw_sku::text)) WHERE ${scope} AND c.${cost}>0`);
  }
  if (keys.includes('mfr_sku')) matches.push(`SELECT s.shopify_id, s.vendor, 'mfr_sku=mfr_sku'::text match_key FROM shopify_products s JOIN ${table} c ON upper(trim(s.mfr_sku))=upper(trim(c.mfr_sku::text)) WHERE ${scope} AND c.${cost}>0`);
  if (keys.includes('shopify_product_id')) matches.push(`SELECT s.shopify_id, s.vendor, 'shopify_id'::text match_key FROM shopify_products s JOIN ${table} c ON regexp_replace(s.shopify_id,'^.*/','')=regexp_replace(c.shopify_product_id::text,'^.*/','') WHERE ${scope} AND nullif(trim(c.shopify_product_id::text),'') IS NOT NULL AND c.${cost}>0`);
  if (!matches.length) continue;
  const rows = query(`WITH hits AS (${matches.join('\nUNION ALL\n')}) SELECT vendor, count(DISTINCT shopify_id)::int products, string_agg(DISTINCT match_key, '+' ORDER BY match_key) match_keys FROM hits GROUP BY vendor ORDER BY products DESC`);
  const positive = query(`SELECT count(*)::int rows FROM ${table} WHERE ${cost}>0`)[0]?.rows ?? 0;
  if (rows.length) sources.push({ table: meta.table_name, cost_column: costColumn, positive_cost_rows: positive, exact_matches: rows });
}

const fleet = query(`SELECT count(*)::int products FROM shopify_products s WHERE ${scope}`)[0].products;
const covered = query(`SELECT vendor, count(*)::int products FROM shopify_products s WHERE ${scope} GROUP BY vendor ORDER BY products DESC`);
const result = {
  ticket: 'TK-10875', generated_at: new Date().toISOString(), mode: 'local-mirror-read-only',
  scope: "ACTIVE AND has_product_variant IS NOT TRUE",
  cost_semantics: { accepted: costPreference, rejected: ['price_retail', 'retail_price', 'msrp', 'price'] },
  active_unbuyable: fleet, vendor_denominator: covered, sources,
  caveats: [
    'Exact identifier joins only; fuzzy title matching is excluded.',
    'A source match narrows cost sourcing but does not authorize pricing or a Shopify write.',
    'Mirror variant state is stale; every future executor must verify live Shopify first.',
    'Cost-column names are candidates whose semantics still require vendor-level provenance confirmation.'
  ]
};
mkdirSync(here, { recursive: true });
writeFileSync(join(here, 'cost-source-map.json'), JSON.stringify(result, null, 2) + '\n');
console.log(JSON.stringify({ active_unbuyable: fleet, candidate_tables: metadata.length, matching_sources: sources.length, sources }, null, 2));