refresh-backlog.js
1 #!/usr/bin/env node 2 /** 3 * refresh-backlog.js — PostgreSQL replacement for inline SQLite backlog query 4 * 5 * Outputs KEY=VALUE lines to stdout for eval in claude-orchestrator.sh. 6 * Replaces the former better-sqlite3 inline script that broke after DR-104 PG migration. 7 * 8 * Usage (from orchestrator): 9 * eval "$(node scripts/refresh-backlog.js \ 10 * --skip-methods=sms \ 11 * --blocked-countries=fr,it \ 12 * --sms-blocked-countries=de,es \ 13 * --low-score-cutoff=82 \ 14 * --project-root=/home/jason/code/333Method)" 15 */ 16 17 import { readFileSync, existsSync, readdirSync, writeFileSync } from 'fs'; 18 import { join, dirname } from 'path'; 19 import { fileURLToPath } from 'url'; 20 import { getAll, getOne, closePool } from '../src/utils/db.js'; 21 import '../src/utils/load-env.js'; 22 23 const __dirname = dirname(fileURLToPath(import.meta.url)); 24 25 // Parse CLI args 26 const args = process.argv.slice(2); 27 function getArg(name) { 28 const arg = args.find(a => a.startsWith(`--${name}=`)); 29 return arg ? arg.split('=').slice(1).join('=') : ''; 30 } 31 32 const skipMethods = getArg('skip-methods').split(',').map(s => s.trim().toLowerCase()).filter(Boolean); 33 const blockedCountries = getArg('blocked-countries').split(',').map(s => s.trim().toUpperCase()).filter(Boolean); 34 const smsBlockedCountries = getArg('sms-blocked-countries').split(',').map(s => s.trim().toUpperCase()).filter(Boolean); 35 const lowScoreCutoff = parseFloat(getArg('low-score-cutoff')) || 82; 36 const projectRoot = getArg('project-root') || join(__dirname, '..'); 37 38 async function main() { 39 // Countries with active templates (direct email.json only) 40 const templatesDir = join(projectRoot, 'data', 'templates'); 41 const templateCountries = new Set( 42 existsSync(templatesDir) 43 ? readdirSync(templatesDir).filter(cc => 44 existsSync(join(templatesDir, cc, 'email.json')) 45 ).map(cc => cc.toUpperCase()) 46 : [] 47 ); 48 49 // Active country codes = all pipeline countries NOT blocked 50 const allCodeRows = await getAll( 51 `SELECT DISTINCT UPPER(country_code) as cc FROM sites 52 WHERE status IN ('found','assets_captured','enriched','proposals_drafted', 53 'semantic_scored','prog_scored','vision_scored') 54 AND country_code IS NOT NULL` 55 ); 56 const allCodes = allCodeRows.map(r => r.cc); 57 const blockedSet = new Set(blockedCountries); 58 const activeCodes = allCodes.filter(cc => !blockedSet.has(cc)); 59 const activeCount = activeCodes.length; 60 61 // Eligible channels = email/sms minus any in OUTREACH_SKIP_METHODS 62 const eligibleChannels = ['email', 'sms'].filter(ch => !skipMethods.includes(ch)); 63 64 // Gate 1: sendable outreach — approved, queued, template country, gdpr_verified where 65 // required, AND past the 3-day per-site cooldown. 66 const GDPR_COUNTRY_CODES = new Set(['DE','UK','FR','IT','ES','NL','PL','BE','SE','AT','NO','DK','IE']); 67 let eligibleCount = 0; 68 const eligibleCodes = activeCodes.filter(cc => templateCountries.has(cc)); 69 if (eligibleChannels.length > 0 && eligibleCodes.length > 0) { 70 let paramIdx = 1; 71 const params = []; 72 73 const chPlaceholders = eligibleChannels.map(() => `$${paramIdx++}`).join(','); 74 params.push(...eligibleChannels); 75 76 const ccPlaceholders = eligibleCodes.map(() => `$${paramIdx++}`).join(','); 77 params.push(...eligibleCodes); 78 79 const gdprEligible = eligibleCodes.filter(cc => GDPR_COUNTRY_CODES.has(cc)); 80 let gdprClause = ''; 81 if (gdprEligible.length > 0) { 82 const gdprPlaceholders = gdprEligible.map(() => `$${paramIdx++}`).join(','); 83 params.push(...gdprEligible); 84 gdprClause = `AND NOT (UPPER(s.country_code) IN (${gdprPlaceholders}) AND (s.gdpr_verified IS NULL OR s.gdpr_verified = false))`; 85 } 86 87 const smsBlockedSet = new Set(smsBlockedCountries); 88 const smsBlockedInTemplate = eligibleCodes.filter(cc => smsBlockedSet.has(cc)); 89 let smsBlockedClause = ''; 90 if (smsBlockedInTemplate.length > 0) { 91 const smsPlaceholders = smsBlockedInTemplate.map(() => `$${paramIdx++}`).join(','); 92 params.push(...smsBlockedInTemplate); 93 smsBlockedClause = `AND NOT (m.contact_method = 'sms' AND UPPER(s.country_code) IN (${smsPlaceholders}))`; 94 } 95 96 const row = await getOne( 97 `SELECT COUNT(*) as n FROM messages m JOIN sites s ON m.site_id=s.id 98 WHERE m.direction='outbound' AND m.approval_status='approved' 99 AND m.delivery_status IS NULL 100 AND m.contact_method IN (${chPlaceholders}) 101 AND UPPER(s.country_code) IN (${ccPlaceholders}) 102 AND (s.last_outreach_at IS NULL OR s.last_outreach_at < NOW() - INTERVAL '3 days') 103 ${gdprClause} 104 ${smsBlockedClause}`, 105 params 106 ); 107 eligibleCount = parseInt(row?.n, 10) || 0; 108 } 109 110 // Gate 2: actionable proposals_drafted — sites that need pipeline work (proofreading/rewording) 111 let actionableProposals = 0; 112 if (eligibleChannels.length > 0 && eligibleCodes.length > 0) { 113 let paramIdx = 1; 114 const params = []; 115 116 const chPlaceholders = eligibleChannels.map(() => `$${paramIdx++}`).join(','); 117 params.push(...eligibleChannels); 118 119 const ccPlaceholders = eligibleCodes.map(() => `$${paramIdx++}`).join(','); 120 params.push(...eligibleCodes); 121 122 const row = await getOne( 123 `SELECT COUNT(DISTINCT m.site_id) as n FROM messages m JOIN sites s ON m.site_id=s.id 124 WHERE s.status = 'proposals_drafted' 125 AND m.direction='outbound' AND m.message_type='outreach' 126 AND m.approval_status IN ('pending', 'rework') 127 AND m.sent_at IS NULL 128 AND m.delivery_status IS NULL 129 AND m.contact_method IN (${chPlaceholders}) 130 AND UPPER(s.country_code) IN (${ccPlaceholders})`, 131 params 132 ); 133 actionableProposals = parseInt(row?.n, 10) || 0; 134 } 135 136 // Gate 3: actionable enriched — in template countries, score below cutoff 137 let actionableEnriched = 0; 138 if (eligibleCodes.length > 0) { 139 let paramIdx = 1; 140 const params = []; 141 142 params.push(lowScoreCutoff); 143 const cutoffParam = `$${paramIdx++}`; 144 145 const ccPlaceholders = eligibleCodes.map(() => `$${paramIdx++}`).join(','); 146 params.push(...eligibleCodes); 147 148 const row = await getOne( 149 `SELECT COUNT(*) as n FROM sites 150 WHERE status = 'enriched' 151 AND (score IS NULL OR score < ${cutoffParam}) 152 AND UPPER(country_code) IN (${ccPlaceholders})`, 153 params 154 ); 155 actionableEnriched = parseInt(row?.n, 10) || 0; 156 } 157 158 // Rolling 7-day average daily send rate 159 const sendAvgRow = await getOne( 160 `SELECT COUNT(*)::float / 7.0 AS avg FROM messages 161 WHERE direction='outbound' AND sent_at > NOW() - INTERVAL '7 days'` 162 ); 163 const dailySendAvg = Math.round(parseFloat(sendAvgRow?.avg) || 0); 164 165 // Display-only counts (all channels, all countries) 166 const stages = await getAll( 167 `SELECT status, COUNT(*) as n FROM sites 168 WHERE status IN ('enriched','proposals_drafted','semantic_scored','vision_scored', 169 'prog_scored','assets_captured','found') 170 GROUP BY status` 171 ); 172 const m = Object.fromEntries(stages.map(r => [r.status, parseInt(r.n, 10)])); 173 174 const approved = await getOne( 175 `SELECT COUNT(*) as n FROM messages 176 WHERE direction='outbound' AND message_type='outreach' 177 AND approval_status='approved' AND sent_at IS NULL` 178 ); 179 const pending = await getOne( 180 `SELECT COUNT(*) as n FROM messages 181 WHERE direction='outbound' AND message_type='outreach' 182 AND approval_status='pending'` 183 ); 184 185 // Data-loss canary: total site count + catastrophic drop detection 186 const totalRow = await getOne(`SELECT COUNT(*) as n FROM sites`); 187 const totalSites = parseInt(totalRow?.n, 10) || 0; 188 const canaryFile = join(projectRoot, 'logs', 'site-count-canary.json'); 189 let previousCount = 0; 190 try { 191 previousCount = JSON.parse(readFileSync(canaryFile, 'utf8')).count || 0; 192 } catch { /* first run or missing file */ } 193 194 writeFileSync(canaryFile, JSON.stringify({ count: totalSites, ts: new Date().toISOString() })); 195 196 // Output shell variable assignments 197 console.log(`CANARY_SITE_COUNT=${totalSites}`); 198 console.log(`CANARY_PREVIOUS_COUNT=${previousCount}`); 199 console.log(`BACKLOG_FOUND=${m.found || 0}`); 200 console.log(`BACKLOG_ASSETS=${m.assets_captured || 0}`); 201 console.log(`BACKLOG_SCORED=${m.scored || 0}`); 202 console.log(`BACKLOG_RESCORED=${m.rescored || 0}`); 203 console.log(`BACKLOG_ENRICHED=${actionableEnriched}`); 204 console.log(`BACKLOG_PROPOSALS=${actionableProposals}`); 205 console.log(`BACKLOG_APPROVED_UNSENT=${parseInt(approved?.n, 10) || 0}`); 206 console.log(`BACKLOG_PENDING_APPROVAL=${parseInt(pending?.n, 10) || 0}`); 207 console.log(`BACKLOG_ELIGIBLE_OUTREACH=${eligibleCount}`); 208 console.log(`BACKLOG_ACTIVE_COUNTRIES=${activeCount}`); 209 console.log(`BACKLOG_DAILY_SEND_AVG=${dailySendAvg}`); 210 } 211 212 main() 213 .catch(err => { 214 process.stderr.write(`refresh-backlog: ${err.message}\n`); 215 process.exit(1); 216 }) 217 .finally(() => closePool());