`scripts/broken-image-check.js`

156 lines
#!/usr/bin/env node
/**
 * dw-monitoring · daily broken-image regression guard
 *
 * Counts shopify_products whose image_url maps to an image_hashes row with
 * status != 'success'. If today's count grows >=5% above the stored baseline,
 * writes an alert file (and attempts SMTP if creds are present).
 *
 * Read-only against dw_unified. Never modifies shopify_products / image_hashes.
 */

'use strict';

const fs = require('fs');
const path = require('path');

// Load secrets-manager .env (canonical) without overriding existing process env.
require('dotenv').config({
  path: path.join(process.env.HOME, 'Projects/secrets-manager/.env'),
});

const { Client } = require('pg');

const ROOT = path.resolve(__dirname, '..');
const DATA_DIR = path.join(ROOT, 'data');
const ALERTS_DIR = path.join(ROOT, 'alerts');
const BASELINE_FILE = path.join(DATA_DIR, 'broken-image-baseline.json');
const HISTORY_FILE = path.join(DATA_DIR, 'broken-image-history.jsonl');

const GROWTH_THRESHOLD = 1.05; // 5% above baseline triggers alert

const QUERY = `
  SELECT COUNT(*)::int AS broken
  FROM shopify_products sp
  JOIN image_hashes ih ON sp.image_url = ih.url
  WHERE ih.status != 'success'
`;

function ensureDirs() {
  for (const d of [DATA_DIR, ALERTS_DIR]) {
    if (!fs.existsSync(d)) fs.mkdirSync(d, { recursive: true });
  }
}

function readBaseline() {
  if (!fs.existsSync(BASELINE_FILE)) {
    const init = { count: 4989, asOf: '2026-05-19' };
    fs.writeFileSync(BASELINE_FILE, JSON.stringify(init, null, 2) + '\n');
    return init;
  }
  return JSON.parse(fs.readFileSync(BASELINE_FILE, 'utf8'));
}

function appendHistory(entry) {
  fs.appendFileSync(HISTORY_FILE, JSON.stringify(entry) + '\n');
}

function writeAlert(payload) {
  const ts = new Date().toISOString().replace(/[:.]/g, '-');
  const file = path.join(ALERTS_DIR, `${ts}.txt`);
  const body = [
    'DW-MONITORING · BROKEN-IMAGE REGRESSION ALERT',
    '='.repeat(60),
    `Detected at:   ${payload.ts}`,
    `Current count: ${payload.count}`,
    `Baseline:      ${payload.baseline.count} (asOf ${payload.baseline.asOf})`,
    `Growth:        ${(payload.ratio * 100 - 100).toFixed(2)}% above baseline`,
    `Threshold:     +${((GROWTH_THRESHOLD - 1) * 100).toFixed(0)}%`,
    '',
    'Read-only monitoring. Investigate via:',
    `  psql -d dw_unified -c "SELECT COUNT(*) FROM shopify_products sp`,
    `    JOIN image_hashes ih ON sp.image_url = ih.url`,
    `    WHERE ih.status != 'success';"`,
    '',
  ].join('\n');
  fs.writeFileSync(file, body);
  return { file, body };
}

async function trySmtpSend(subject, body) {
  // Look for optional Gmail SMTP creds. If absent, return false (file-only mode).
  const user = process.env.GMAIL_USER || process.env.GEORGE_GMAIL_USER;
  const pass = process.env.GMAIL_APP_PASSWORD || process.env.GEORGE_PASSWORD;
  if (!user || !pass) return { sent: false, reason: 'no SMTP creds' };

  let nodemailer;
  try {
    nodemailer = require('nodemailer');
  } catch (e) {
    return { sent: false, reason: 'nodemailer not installed' };
  }
  const to = process.env.MONITORING_ALERT_TO || 'info@designerwallcoverings.com';
  const transporter = nodemailer.createTransport({
    host: 'smtp.gmail.com',
    port: 465,
    secure: true,
    auth: { user, pass },
  });
  try {
    await transporter.sendMail({ from: user, to, subject, text: body });
    return { sent: true };
  } catch (err) {
    return { sent: false, reason: err.message };
  }
}

async function main() {
  ensureDirs();
  const baseline = readBaseline();

  // Explicit conn config so the pool ignores stray env from secrets-manager .env.
  // Local pg uses peer auth as the OS user — no password needed.
  const client = new Client({
    host: process.env.DW_PGHOST || '/tmp',
    user: process.env.DW_PGUSER || process.env.USER,
    database: 'dw_unified',
    password: undefined,
  });
  await client.connect();
  let count;
  try {
    const res = await client.query(QUERY);
    count = res.rows[0].broken;
  } finally {
    await client.end();
  }

  const ts = new Date().toISOString();
  const ratio = count / baseline.count;
  const entry = { ts, count, baseline: baseline.count, ratio: +ratio.toFixed(4) };
  appendHistory(entry);

  const breach = count >= baseline.count * GROWTH_THRESHOLD;
  if (breach) {
    const alert = writeAlert({ ts, count, baseline, ratio });
    const smtp = await trySmtpSend(
      `[dw-monitoring] broken-image count up ${(ratio * 100 - 100).toFixed(2)}% (now ${count})`,
      alert.body,
    );
    console.log(
      `[ALERT] count=${count} baseline=${baseline.count} ratio=${ratio.toFixed(4)} ` +
        `alert=${alert.file} smtp=${smtp.sent ? 'sent' : 'skipped: ' + smtp.reason}`,
    );
  } else {
    console.log(
      `[ok] count=${count} baseline=${baseline.count} ratio=${ratio.toFixed(4)} ` +
        `(threshold ${GROWTH_THRESHOLD.toFixed(2)})`,
    );
  }
}

main().catch((err) => {
  console.error('[ERROR]', err.message);
  process.exit(1);
});