status.js
1 #!/usr/bin/env node 2 /** 3 * Pipeline Status CLI — npm run status 4 * 5 * Prints a tree-view breakdown of pipeline and outreach statuses with 6 * totals, 24h/1h throughput, days-to-clear backlog, API cost estimates, 7 * inbound reply funnel, and account health. 8 */ 9 10 import { getAll, getOne } from '../utils/db.js'; 11 import { readdirSync, existsSync } from 'fs'; 12 import { join, dirname } from 'path'; 13 import { fileURLToPath } from 'url'; 14 import https from 'https'; 15 import { 16 buildStatusTree, 17 buildOutreachTree, 18 buildConversationsTree, 19 } from '../utils/error-categories.js'; 20 import { getHaltedChannels } from '../utils/outreach-guard.js'; 21 import { getGDPRCountries } from '../config/countries.js'; 22 import '../utils/load-env.js'; 23 24 const __filename = fileURLToPath(import.meta.url); 25 const __dirname = dirname(__filename); 26 const projectRoot = join(__dirname, '../..'); 27 28 // ────────────────────────────────────────────────────────────────────────────── 29 // ANSI colors 30 // ────────────────────────────────────────────────────────────────────────────── 31 const C = { 32 reset: '\x1b[0m', 33 bold: '\x1b[1m', 34 dim: '\x1b[2m', 35 green: '\x1b[32m', 36 red: '\x1b[31m', 37 cyan: '\x1b[36m', 38 yellow: '\x1b[33m', 39 grey: '\x1b[90m', 40 magenta: '\x1b[35m', 41 white: '\x1b[97m', 42 }; 43 44 const GOOD_SITE_STATUSES = new Set([ 45 'enriched', 46 'proposals_drafted', 47 'outreach_partial', 48 'outreach_sent', 49 'high_score', 50 ]); 51 const BAD_SITE_STATUSES = new Set(['failing', 'failed']); 52 const DIM_SITE_STATUSES = new Set(['ignored']); 53 const GOOD_OUTREACH_STATUSES = new Set(['sent', 'delivered', 'opened', 'clicked']); 54 const BAD_OUTREACH_STATUSES = new Set(['failed', 'bounced', 'rejected']); 55 const RETRY_OUTREACH_STATUSES = new Set(['retry_later']); 56 57 function colorForSiteStatus(status) { 58 if (GOOD_SITE_STATUSES.has(status)) return C.green; 59 if (BAD_SITE_STATUSES.has(status)) return C.red; 60 if (DIM_SITE_STATUSES.has(status)) return C.dim; 61 return C.cyan; 62 } 63 64 function colorForOutreachStatus(status) { 65 if (GOOD_OUTREACH_STATUSES.has(status)) return C.green; 66 if (BAD_OUTREACH_STATUSES.has(status)) return C.red; 67 if (RETRY_OUTREACH_STATUSES.has(status)) return C.yellow; 68 if (status === 'gdpr_blocked' || status === 'rejected') return C.magenta; 69 return C.cyan; 70 } 71 72 // ────────────────────────────────────────────────────────────────────────────── 73 // Formatting helpers 74 // ────────────────────────────────────────────────────────────────────────────── 75 const COL_LABEL = 40; 76 const COL_TOTAL = 9; 77 const COL_CUMUL = 11; // "(N,NNN)" 78 const COL_THRU = 8; // →24h / →1h 79 const COL_ACTQ = 9; // actionable queue 80 81 function rpad(str, width) { 82 return String(str).padEnd(width); 83 } 84 85 function lpad(str, width) { 86 return String(str).padStart(width); 87 } 88 89 function fmtNum(n) { 90 return n === null || n === undefined ? '' : Number(n).toLocaleString('en-US'); 91 } 92 93 function fmtThru(n, color) { 94 if (!n || n === 0) return C.dim + lpad('+0', COL_THRU) + C.reset; 95 return color + lpad(`+${fmtNum(n)}`, COL_THRU) + C.reset; 96 } 97 98 function fmtDaysToGlear(total, thru24) { 99 if (!total || total === 0) return ''; 100 if (!thru24 || thru24 === 0) return `${C.dim} (stalled)${C.reset}`; 101 const days = total / thru24; 102 if (days < 1) return `${C.green} (<1d)${C.reset}`; 103 if (days < 3) return `${C.yellow} (${days.toFixed(1)}d)${C.reset}`; 104 return `${C.red} (${days.toFixed(1)}d)${C.reset}`; 105 } 106 107 function header( 108 title, 109 { showCumulative = false, showThroughput = false, show1h = true, showActionable = false } = {} 110 ) { 111 let width = COL_LABEL + COL_TOTAL; 112 if (showCumulative) width += 2 + COL_CUMUL; 113 if (showThroughput) { 114 width += 2 + COL_THRU; 115 if (show1h) width += 2 + COL_THRU; 116 width += 10; // +10 for "(Nd)" suffix 117 } 118 if (showActionable) width += 2 + COL_ACTQ; 119 console.log(''); 120 let line = C.bold + C.white + title.padEnd(COL_LABEL) + rpad('Total', COL_TOTAL); 121 if (showCumulative) line += ` ${rpad('Cumul', COL_CUMUL)}`; 122 if (showThroughput) { 123 line += ` ${rpad('→24h', COL_THRU)}`; 124 if (show1h) line += ` ${rpad('→1h', COL_THRU)}`; 125 line += ` ${rpad('clear', 8)}`; 126 } 127 if (showActionable) line += ` ${rpad('actable', COL_ACTQ)}`; 128 console.log(line + C.reset); 129 console.log('─'.repeat(width)); 130 } 131 132 function rowTop(status, total, opts = {}) { 133 const { 134 cumulative = null, 135 thru24 = null, 136 thru1 = undefined, 137 color = C.reset, 138 daysToClear = null, 139 actionable = undefined, 140 } = opts; 141 const label = rpad(`▸ ${status}`, COL_LABEL); 142 const tot = lpad(fmtNum(total), COL_TOTAL); 143 let line = color + label + C.reset + tot; 144 if (cumulative !== null) { 145 line += ` ${C.dim + lpad(`(${fmtNum(cumulative)})`, COL_CUMUL) + C.reset}`; 146 } 147 if (thru24 !== null) { 148 line += ` ${fmtThru(thru24, C.yellow)}`; 149 if (thru1 !== undefined) line += ` ${fmtThru(thru1, C.yellow)}`; 150 } 151 if (daysToClear !== null) { 152 line += fmtDaysToGlear(total, daysToClear); 153 } 154 if (actionable !== undefined) { 155 const actStr = actionable === null ? '' : fmtNum(actionable); 156 line += ` ${C.cyan + lpad(actStr, COL_ACTQ) + C.reset}`; 157 } 158 console.log(line); 159 } 160 161 function rowChild(prefix, label, total, opts = {}) { 162 const { isLast = false, color = C.dim, showCumulative = false, showThroughput = false } = opts; 163 const tree = isLast ? '└─' : '├─'; 164 const pad = rpad(` ${prefix}${tree} ${label}`, COL_LABEL); 165 const tot = lpad(fmtNum(total), COL_TOTAL); 166 let line = color + pad + C.reset + tot; 167 if (showCumulative) line += ` ${rpad('', COL_CUMUL)}`; 168 if (showThroughput) line += ` ${rpad('', COL_THRU)} ${rpad('', COL_THRU)}`; 169 console.log(line); 170 } 171 172 function rowGroup(label) { 173 console.log(`${C.dim} ${label}${C.reset}`); 174 } 175 176 // ────────────────────────────────────────────────────────────────────────────── 177 // Render helpers 178 // ────────────────────────────────────────────────────────────────────────────── 179 180 function renderErrorChildren( 181 children, 182 parentColor, 183 { showCumulative = false, showThroughput = false } = {} 184 ) { 185 if (!children) return; 186 187 if (children.type === 'channels') { 188 const { rows } = children; 189 rows.forEach((r, i) => { 190 const isLast = i === rows.length - 1; 191 rowChild('', r.label, r.total, { 192 isLast, 193 color: parentColor, 194 showCumulative, 195 showThroughput, 196 }); 197 }); 198 return; 199 } 200 201 if (children.type === 'errors') { 202 const { retriable, terminal, unknown } = children; 203 const hasRetriable = retriable.length > 0; 204 const hasTerminal = terminal.length > 0; 205 const hasUnknown = unknown.length > 0; 206 207 if (hasRetriable) { 208 const retriableIsLast = !hasTerminal && !hasUnknown; 209 rowGroup(`${retriableIsLast ? '└─' : '├─'} Retriable`); 210 retriable.forEach((r, i) => { 211 const isLast = i === retriable.length - 1; 212 rowChild(retriableIsLast ? ' ' : '│ ', r.label, r.total, { 213 isLast, 214 color: C.cyan, 215 showCumulative, 216 showThroughput, 217 }); 218 }); 219 } 220 221 if (hasTerminal) { 222 const terminalIsLast = !hasUnknown; 223 rowGroup(`${terminalIsLast ? '└─' : '├─'} Terminal`); 224 terminal.forEach((r, i) => { 225 const isLast = i === terminal.length - 1; 226 rowChild(terminalIsLast ? ' ' : '│ ', r.label, r.total, { 227 isLast, 228 color: C.red, 229 showCumulative, 230 showThroughput, 231 }); 232 }); 233 } 234 235 if (hasUnknown) { 236 unknown.forEach((r, i) => { 237 const isLast = i === unknown.length - 1; 238 rowChild('', r.label, r.total, { isLast, color: C.yellow, showCumulative, showThroughput }); 239 }); 240 } 241 } 242 } 243 244 // ────────────────────────────────────────────────────────────────────────────── 245 // Main sections 246 // ────────────────────────────────────────────────────────────────────────────── 247 248 // Pipeline stages that have a meaningful backlog (in pipeline order) 249 const BACKLOG_STAGES = new Set([ 250 'found', 251 'assets_captured', 252 'prog_scored', 253 'semantic_scored', 254 'vision_scored', 255 'enriched', 256 'proposals_drafted', 257 ]); 258 259 /** 260 * Compute the actionable queue size for each pipeline stage. 261 * "Actionable" = sites/messages that can actually flow through to a sent outreach, 262 * after applying all downstream filters (blocked countries, skipped channels, 263 * score cutoff, delivery_status). 264 * 265 * These are the same numbers the orchestrator uses for throttle gates. 266 * Returns a map of stage → count. 267 */ 268 async function getActionableQueues() { 269 const blockedCountries = (process.env.OUTREACH_BLOCKED_COUNTRIES || '') 270 .split(',') 271 .map(s => s.trim().toUpperCase()) 272 .filter(Boolean); 273 const skipMethods = (process.env.OUTREACH_SKIP_METHODS || '') 274 .split(',') 275 .map(s => s.trim().toLowerCase()) 276 .filter(Boolean); 277 const englishOnlyMarkets = (process.env.ENGLISH_ONLY_MARKETS || '') 278 .split(',') 279 .map(s => s.trim().toUpperCase()) 280 .filter(Boolean); 281 const lowScoreCutoff = parseFloat(process.env.LOW_SCORE_CUTOFF || '82'); 282 const eligibleChannels = ['email', 'sms'].filter(ch => !skipMethods.includes(ch)); 283 284 const blockedSet = new Set(blockedCountries); 285 286 // Template countries (have email.json) = countries with working outreach capability 287 const templatesDir = join(projectRoot, 'data', 'templates'); 288 const templateCountries = new Set( 289 existsSync(templatesDir) 290 ? readdirSync(templatesDir) 291 .filter(cc => existsSync(join(templatesDir, cc, 'email.json'))) 292 .map(cc => cc.toUpperCase()) 293 : [] 294 ); 295 296 // eligibleCodes: template country + not blocked = can actually receive outreach 297 const eligibleCodes = [...templateCountries].filter(cc => !blockedSet.has(cc)); 298 299 // GDPR countries require gdpr_verified=1 before any outreach can be sent 300 const gdprCountryCodes = new Set(getGDPRCountries().map(c => c.code.toUpperCase())); 301 const englishActiveCodes = englishOnlyMarkets.filter(cc => !blockedSet.has(cc)); 302 303 const result = {}; 304 305 // Scoring stages (semantic_scored, vision_scored, prog_scored): 306 // actable = sites in ENGLISH_ONLY_MARKETS that are not blocked 307 if (englishActiveCodes.length > 0) { 308 const ccParams = englishActiveCodes.map((_, i) => `$${i + 2}`).join(','); 309 for (const status of ['semantic_scored', 'vision_scored', 'prog_scored']) { 310 const row = await getOne( 311 `SELECT COUNT(*) as n FROM sites 312 WHERE status = $1 313 AND UPPER(country_code) IN (${ccParams})`, 314 [status, ...englishActiveCodes] 315 ); 316 result[status] = Number(row?.n || 0); 317 } 318 } 319 320 // Enriched intermediate stages (enriched_regex, enriched_llm): 321 // actable = in ENGLISH_ONLY_MARKETS, not blocked, score below cutoff (or unscored) 322 if (englishActiveCodes.length > 0) { 323 const ccParams = englishActiveCodes.map((_, i) => `$${i + 3}`).join(','); 324 for (const status of ['enriched_regex', 'enriched_llm']) { 325 const row = await getOne( 326 `SELECT COUNT(*) as n FROM sites 327 WHERE status = $1 328 AND (score IS NULL OR score < $2) 329 AND UPPER(country_code) IN (${ccParams})`, 330 [status, lowScoreCutoff, ...englishActiveCodes] 331 ); 332 result[status] = Number(row?.n || 0); 333 } 334 } 335 336 // Gate 3: actionable enriched (score below cutoff, eligible country) 337 if (eligibleCodes.length > 0) { 338 const ccParams = eligibleCodes.map((_, i) => `$${i + 2}`).join(','); 339 const row = await getOne( 340 `SELECT COUNT(*) as n FROM sites 341 WHERE status = 'enriched' 342 AND (score IS NULL OR score < $1) 343 AND UPPER(country_code) IN (${ccParams})`, 344 [lowScoreCutoff, ...eligibleCodes] 345 ); 346 result['enriched'] = Number(row?.n || 0); 347 } else { 348 result['enriched'] = 0; 349 } 350 351 // Gate 2: actionable proposals_drafted (unreworded, unsent, not failed, eligible country) 352 if (eligibleChannels.length > 0 && eligibleCodes.length > 0) { 353 const chParams = eligibleChannels.map((_, i) => `$${i + 1}`).join(','); 354 const ccParams = eligibleCodes.map((_, i) => `$${i + 1 + eligibleChannels.length}`).join(','); 355 const row = await getOne( 356 `SELECT COUNT(DISTINCT m.site_id) as n FROM messages m JOIN sites s ON m.site_id=s.id 357 WHERE s.status = 'proposals_drafted' 358 AND m.direction='outbound' AND m.message_type='outreach' 359 AND m.reworded_at IS NULL AND m.sent_at IS NULL 360 AND m.delivery_status IS NULL 361 AND m.contact_method IN (${chParams}) 362 AND UPPER(s.country_code) IN (${ccParams})`, 363 [...eligibleChannels, ...eligibleCodes] 364 ); 365 result['proposals_drafted'] = Number(row?.n || 0); 366 } else { 367 result['proposals_drafted'] = 0; 368 } 369 370 // Gate 1: truly sendable outreach (approved, queued, template country, cooldown passed, 371 // gdpr_verified=1 for GDPR countries, no SMS block) 372 if (eligibleChannels.length > 0 && eligibleCodes.length > 0) { 373 const chParams = eligibleChannels.map((_, i) => `$${i + 1}`).join(','); 374 const ccParams = eligibleCodes.map((_, i) => `$${i + 1 + eligibleChannels.length}`).join(','); 375 // GDPR countries in our eligible set that require verification 376 const gdprEligible = eligibleCodes.filter(cc => gdprCountryCodes.has(cc)); 377 const baseParamCount = eligibleChannels.length + eligibleCodes.length; 378 const gdprClause = 379 gdprEligible.length > 0 380 ? `AND NOT (UPPER(s.country_code) IN (${gdprEligible.map((_, i) => `$${i + 1 + baseParamCount}`).join(',')}) AND (s.gdpr_verified IS NULL OR s.gdpr_verified = false))` 381 : ''; 382 const row = await getOne( 383 `SELECT COUNT(*) as n FROM messages m JOIN sites s ON m.site_id=s.id 384 WHERE m.direction='outbound' AND m.approval_status='approved' 385 AND m.delivery_status IS NULL 386 AND m.contact_method IN (${chParams}) 387 AND UPPER(s.country_code) IN (${ccParams}) 388 AND (s.last_outreach_at IS NULL OR s.last_outreach_at < NOW() - INTERVAL '3 days') 389 ${gdprClause}`, 390 [...eligibleChannels, ...eligibleCodes, ...gdprEligible] 391 ); 392 result['outreach'] = Number(row?.n || 0); 393 } else { 394 result['outreach'] = 0; 395 } 396 397 return result; 398 } 399 400 function renderLegend() { 401 console.log(''); 402 console.log(`${C.bold + C.white}Column Guide${C.reset}`); 403 console.log('─'.repeat(62)); 404 console.log(` ${C.bold}Total${C.reset} — sites currently sitting at this status`); 405 console.log( 406 ` ${C.bold}Cumul${C.reset} — sites at this stage or further along (pipeline depth)` 407 ); 408 console.log( 409 ` ${C.bold}→24h / →1h${C.reset} — sites that entered this stage in the last 24h / 1h` 410 ); 411 console.log( 412 ` ${C.bold}(Nd)${C.reset} — days to clear backlog at current 24h throughput rate` 413 ); 414 const mult = parseFloat(process.env.STAGE_THROTTLE_MULTIPLIER || '3'); 415 const g1 = Math.round( 416 mult * 417 Math.max( 418 parseInt(process.env.REWORD_EMAIL_BATCH || '10'), 419 parseInt(process.env.REWORD_SMS_BATCH || '50') 420 ) 421 ); 422 const g2 = Math.round( 423 mult * 424 (parseInt(process.env.PROPOSALS_EMAIL_BATCH || '15') + 425 parseInt(process.env.PROPOSALS_SMS_BATCH || '15')) 426 ); 427 const g3 = Math.round(mult * parseInt(process.env.ENRICH_SITES_BATCH || '5')); 428 console.log(` ${C.bold}actable${C.reset} — items that can actually flow to a sent outreach`); 429 console.log(` scoring: English-only markets, not GDPR-blocked`); 430 console.log( 431 ` enriched*/proposals_drafted: above + score<${process.env.LOW_SCORE_CUTOFF || '82'} + template country + unreworded+unsent` 432 ); 433 console.log( 434 ` outreach: above + approved + cooldown passed (3d) + gdpr_verified where required (sends once business hours open in recipient timezone) — gates: G1>${g1} G2>${g2} G3>${g3}` 435 ); 436 } 437 438 function renderStatusTree(tree, actionableQueues = {}) { 439 header('Pipeline Status', { showCumulative: true, showThroughput: true, showActionable: true }); 440 for (const row of tree) { 441 const color = colorForSiteStatus(row.status); 442 const showDays = BACKLOG_STAGES.has(row.status); 443 // Show actionable count only for stages that have a gate 444 const actionable = row.status in actionableQueues ? actionableQueues[row.status] : null; 445 rowTop(row.status, row.total, { 446 cumulative: row.cumulative, 447 thru24: row.thru_24h ?? null, 448 thru1: row.thru_1h ?? null, 449 color, 450 daysToClear: showDays ? row.thru_24h : null, 451 actionable, 452 }); 453 if (row.children) { 454 renderErrorChildren(row.children, color, { showCumulative: true, showThroughput: true }); 455 } 456 } 457 } 458 459 async function renderOutreachTree(tree) { 460 // Cross-tab query: contact_method × status 461 const crossRows = await getAll( 462 `SELECT contact_method, 463 COALESCE(delivery_status, approval_status) AS status, 464 COUNT(*) AS total 465 FROM messages 466 WHERE direction = 'outbound' 467 GROUP BY contact_method, COALESCE(delivery_status, approval_status)` 468 ); 469 470 // Sent in last 24h for throughput / days-to-clear 471 const sentTodayRow = await getOne( 472 `SELECT COUNT(*) AS n FROM messages 473 WHERE direction = 'outbound' 474 AND delivery_status IN ('sent','delivered','opened','clicked') 475 AND updated_at > NOW() - INTERVAL '24 hours'` 476 ); 477 const sentToday = Number(sentTodayRow?.n || 0); 478 479 const sent7dRow = await getOne( 480 `SELECT COUNT(*) AS n FROM messages 481 WHERE direction = 'outbound' 482 AND delivery_status IN ('sent','delivered','opened','clicked') 483 AND updated_at > NOW() - INTERVAL '7 days'` 484 ); 485 const sent7dAvg = Math.round(Number(sent7dRow?.n || 0) / 7); 486 const clearRate = sent7dAvg > 0 ? sent7dAvg : sentToday; 487 488 // Per-method 24h sent counts for the →24h column 489 const method24hRows = await getAll( 490 `SELECT contact_method, COUNT(*) AS n 491 FROM messages 492 WHERE direction='outbound' 493 AND delivery_status IN ('sent','delivered','opened','clicked') 494 AND updated_at > NOW() - INTERVAL '24 hours' 495 GROUP BY contact_method` 496 ); 497 const method24hMap = {}; 498 for (const r of method24hRows) method24hMap[r.contact_method] = Number(r.n); 499 500 // Build cross-tab map: method -> status -> count 501 const crossMap = {}; // { email: { pending: N, approved: N, ... }, ... } 502 const presentStatuses = new Set(); 503 const presentMethods = new Set(); 504 for (const r of crossRows) { 505 if (!crossMap[r.contact_method]) crossMap[r.contact_method] = {}; 506 crossMap[r.contact_method][r.status] = Number(r.total); 507 presentStatuses.add(r.status); 508 presentMethods.add(r.contact_method); 509 } 510 511 // Status column order — filter to only those with data 512 const STATUS_COL_ORDER = [ 513 'pending', 514 'approved', 515 'scheduled', 516 'sent', 517 'delivered', 518 'opened', 519 'clicked', 520 'rework', 521 'rejected', 522 'retry_later', 523 'failed', 524 'bounced', 525 'gdpr_blocked', 526 ]; 527 // Status display abbreviations for narrow columns 528 const STATUS_ABBR = { 529 pending: 'pending', 530 approved: 'approved', 531 scheduled: 'sched', 532 sent: 'sent', 533 delivered: 'delivrd', 534 opened: 'opened', 535 clicked: 'clicked', 536 rework: 'rework', 537 rejected: 'rejectd', 538 retry_later: 'retry', 539 failed: 'failed', 540 bounced: 'bounced', 541 gdpr_blocked: 'gdpr', 542 }; 543 const METHOD_ORDER = ['email', 'sms', 'form', 'x', 'linkedin']; 544 const statuses = STATUS_COL_ORDER.filter(s => presentStatuses.has(s)); 545 const methods = [ 546 ...METHOD_ORDER.filter(m => presentMethods.has(m)), 547 ...[...presentMethods].filter(m => !METHOD_ORDER.includes(m)).sort(), 548 ]; 549 550 // Column widths 551 const COL_METHOD = 10; 552 const COL_STATUS = 9; // per status column 553 const COL_TOT = 8; 554 const COL_24H = 7; // →24h column 555 556 const tableWidth = COL_METHOD + statuses.length * COL_STATUS + COL_TOT + 2 + COL_24H + 2; 557 console.log(''); 558 console.log(`${C.bold + C.white}Outreach Breakdown${C.reset}`); 559 console.log('─'.repeat(tableWidth)); 560 561 // Header row: status names 562 let hdr = `${C.bold + C.white} ${''.padEnd(COL_METHOD)}`; 563 for (const s of statuses) { 564 const abbr = STATUS_ABBR[s] || s; 565 const col = colorForOutreachStatus(s); 566 hdr += col + abbr.padStart(COL_STATUS) + C.reset + C.bold + C.white; 567 } 568 hdr += ` ${'Total'.padStart(COL_TOT - 2)} ${C.yellow}${'→24h'.padStart(COL_24H)}${C.reset}`; 569 console.log(hdr); 570 console.log(` ${'─'.repeat(COL_METHOD + statuses.length * COL_STATUS + COL_TOT + COL_24H + 2)}`); 571 572 // Data rows: one per contact method 573 const methodTotals = {}; // status -> grand total across methods 574 for (const s of statuses) methodTotals[s] = 0; 575 let grand24h = 0; 576 577 for (const method of methods) { 578 const counts = crossMap[method] || {}; 579 const rowTotal = statuses.reduce((sum, s) => sum + (counts[s] || 0), 0); 580 const row24h = method24hMap[method] || 0; 581 grand24h += row24h; 582 let line = ` ${C.dim}${method.padEnd(COL_METHOD)}${C.reset}`; 583 for (const s of statuses) { 584 const n = counts[s] || 0; 585 methodTotals[s] += n; 586 const col = n > 0 ? colorForOutreachStatus(s) : C.dim; 587 line += col + String(n).padStart(COL_STATUS) + C.reset; 588 } 589 const col24h = row24h > 0 ? C.yellow : C.dim; 590 line += ` ${C.bold}${String(rowTotal).padStart(COL_TOT - 2)}${C.reset}`; 591 line += ` ${col24h}${(row24h > 0 ? `+${row24h}` : '+0').padStart(COL_24H)}${C.reset}`; 592 console.log(line); 593 } 594 595 // Totals row 596 let totLine = ` ${C.bold}${C.white}${'Total'.padEnd(COL_METHOD)}`; 597 let grandTotal = 0; 598 for (const s of statuses) { 599 grandTotal += methodTotals[s]; 600 totLine += String(methodTotals[s]).padStart(COL_STATUS); 601 } 602 const tot24hColor = grand24h > 0 ? C.yellow : C.dim; 603 totLine += ` ${String(grandTotal).padStart(COL_TOT - 2)}${C.reset}`; 604 totLine += ` ${tot24hColor}${(grand24h > 0 ? `+${grand24h}` : '+0').padStart(COL_24H)}${C.reset}`; 605 console.log(` ${'─'.repeat(COL_METHOD + statuses.length * COL_STATUS + COL_TOT + COL_24H + 2)}`); 606 console.log(totLine); 607 608 // Throughput / days-to-clear summary line 609 const approvedTotal = methodTotals['approved'] || 0; 610 if (approvedTotal > 0 || sentToday > 0) { 611 const clearStr = 612 approvedTotal > 0 && clearRate > 0 ? fmtDaysToGlear(approvedTotal, clearRate) : ''; 613 console.log( 614 `\n ${C.dim}sent today: ${C.yellow}+${sentToday.toLocaleString('en-US')}${C.reset}${ 615 approvedTotal > 0 616 ? ` ${C.dim}approved queue: ${C.cyan}${approvedTotal.toLocaleString('en-US')}${C.reset}${clearStr}` 617 : '' 618 }` 619 ); 620 } 621 622 // Error/retry breakdown (secondary section below the table) 623 const retryEntry = tree.find(r => r.status === 'retry_later'); 624 const failedEntry = tree.find(r => r.status === 'failed'); 625 626 if (retryEntry?.retryStats && (methodTotals['retry_later'] || 0) > 0) { 627 const { due_now, next_retry_at } = retryEntry.retryStats; 628 const dueStr = 629 due_now > 0 630 ? `${C.yellow}${due_now.toLocaleString('en-US')} due now${C.reset}` 631 : next_retry_at 632 ? `next retry ${new Date(next_retry_at).toLocaleString()}` 633 : 'no retry scheduled'; 634 console.log(` ${C.dim}retry_later: ${dueStr}`); 635 } 636 637 if (failedEntry?.children) { 638 console.log(` ${C.dim}failed errors:${C.reset}`); 639 renderErrorChildren(failedEntry.children, C.red); 640 } 641 if (retryEntry?.children) { 642 console.log(` ${C.dim}retry errors:${C.reset}`); 643 renderErrorChildren(retryEntry.children, C.yellow); 644 } 645 } 646 647 async function renderConversationsTree(tree) { 648 if (tree.length === 0) return; 649 650 // Channel breakdown (sms / email) and replied ratio 651 const channelRows = await getAll( 652 `SELECT 653 contact_method AS channel, 654 COUNT(*) AS total 655 FROM messages 656 WHERE direction = 'inbound' 657 GROUP BY contact_method 658 ORDER BY total DESC` 659 ); 660 661 const totalInbound = channelRows.reduce((s, r) => s + Number(r.total), 0); 662 663 console.log(''); 664 console.log(`${C.bold + C.white}Inbound Replies${C.reset}`); 665 console.log('─'.repeat(62)); 666 667 // Channel summary line 668 if (channelRows.length > 0) { 669 console.log( 670 ` ${C.dim}channels:${C.reset} ${channelRows 671 .map(r => `${r.channel} ${r.total}`) 672 .join(' · ')} ${C.cyan}total ${totalInbound}${C.reset}` 673 ); 674 } 675 676 // Build columnar table: rows=intents, columns=sentiments 677 const SENTIMENT_ORDER_CONV = ['positive', 'neutral', 'negative', 'objection']; 678 const SENTIMENT_COLOR = { 679 positive: C.green, 680 neutral: C.cyan, 681 negative: C.red, 682 objection: C.yellow, 683 }; 684 685 // 24h inbound counts per intent 686 const inbound24hRows = await getAll( 687 `SELECT intent, COUNT(*) AS n 688 FROM messages 689 WHERE direction='inbound' 690 AND created_at > NOW() - INTERVAL '24 hours' 691 GROUP BY intent` 692 ); 693 const inbound24hMap = {}; 694 for (const r of inbound24hRows) inbound24hMap[r.intent] = Number(r.n); 695 696 // Collect all intents and their counts per sentiment 697 const intentMap = {}; // intent -> { sentiment -> count } 698 const presentSentiments = new Set(tree.map(r => r.sentiment)); 699 for (const row of tree) { 700 for (const intent of row.intents) { 701 if (!intentMap[intent.label]) intentMap[intent.label] = {}; 702 intentMap[intent.label][row.sentiment] = intent.total; 703 } 704 } 705 706 const sentiments = SENTIMENT_ORDER_CONV.filter(s => presentSentiments.has(s)); 707 const intents = Object.keys(intentMap).sort((a, b) => { 708 const totA = sentiments.reduce((s, sent) => s + (intentMap[a][sent] || 0), 0); 709 const totB = sentiments.reduce((s, sent) => s + (intentMap[b][sent] || 0), 0); 710 return totB - totA; 711 }); 712 713 if (intents.length === 0) return; 714 715 // Column widths 716 const COL_INTENT = 20; 717 const COL_SENT = 10; 718 const COL_TOT = 8; 719 const COL_24H_CONV = 7; 720 721 // Header row 722 let hdr = `${C.bold + C.white} ${'Intent'.padEnd(COL_INTENT)}`; 723 for (const s of sentiments) { 724 hdr += (SENTIMENT_COLOR[s] || C.cyan) + s.padStart(COL_SENT) + C.reset + C.bold + C.white; 725 } 726 hdr += ` ${'Total'.padStart(COL_TOT - 2)} ${C.yellow}${'→24h'.padStart(COL_24H_CONV)}${C.reset}`; 727 console.log(hdr); 728 console.log( 729 ` ${'─'.repeat(COL_INTENT + sentiments.length * COL_SENT + COL_TOT + COL_24H_CONV + 2)}` 730 ); 731 732 // Data rows 733 for (const intent of intents) { 734 const counts = intentMap[intent]; 735 const rowTotal = sentiments.reduce((s, sent) => s + (counts[sent] || 0), 0); 736 const row24h = inbound24hMap[intent] || 0; 737 let line = ` ${C.dim}${intent.padEnd(COL_INTENT)}${C.reset}`; 738 for (const s of sentiments) { 739 const n = counts[s] || 0; 740 const col = n > 0 ? SENTIMENT_COLOR[s] || C.cyan : C.dim; 741 line += col + String(n).padStart(COL_SENT) + C.reset; 742 } 743 const col24h = row24h > 0 ? C.yellow : C.dim; 744 line += ` ${C.bold}${String(rowTotal).padStart(COL_TOT - 2)}${C.reset}`; 745 line += ` ${col24h}${(row24h > 0 ? `+${row24h}` : '+0').padStart(COL_24H_CONV)}${C.reset}`; 746 console.log(line); 747 } 748 749 // Totals row 750 let totLine = ` ${C.bold}${C.white}${'Total'.padEnd(COL_INTENT)}`; 751 let grandTotal = 0; 752 let grandTotal24h = 0; 753 for (const s of sentiments) { 754 const n = tree.find(r => r.sentiment === s)?.total || 0; 755 grandTotal += n; 756 totLine += String(n).padStart(COL_SENT); 757 } 758 for (const n of Object.values(inbound24hMap)) grandTotal24h += n; 759 const totCol24h = grandTotal24h > 0 ? C.yellow : C.dim; 760 totLine += ` ${String(grandTotal).padStart(COL_TOT - 2)}${C.reset}`; 761 totLine += ` ${totCol24h}${(grandTotal24h > 0 ? `+${grandTotal24h}` : '+0').padStart(COL_24H_CONV)}${C.reset}`; 762 console.log( 763 ` ${'─'.repeat(COL_INTENT + sentiments.length * COL_SENT + COL_TOT + COL_24H_CONV + 2)}` 764 ); 765 console.log(totLine); 766 } 767 768 function renderHaltedChannels() { 769 let halted; 770 try { 771 halted = getHaltedChannels(); 772 } catch { 773 return; 774 } 775 if (!halted || Object.keys(halted).length === 0) return; 776 777 console.log(''); 778 console.log(`${C.red + C.bold}⛔ Halted Channels (reputation guard)${C.reset}`); 779 console.log('─'.repeat(62)); 780 for (const [channel, info] of Object.entries(halted)) { 781 console.log(` ${C.red}${channel}${C.reset}: ${info.count}× "${info.error}" in last 2h`); 782 } 783 } 784 785 // ────────────────────────────────────────────────────────────────────────────── 786 // API Cost section 787 // ────────────────────────────────────────────────────────────────────────────── 788 789 // Unit cost estimates (configurable via env) 790 const RESEND_COST_PER_EMAIL = parseFloat(process.env.RESEND_COST_PER_EMAIL || '0.001'); 791 const TWILIO_COST_PER_SMS = parseFloat(process.env.TWILIO_COST_PER_SMS || '0.0079'); 792 // ZenRows is a monthly subscription ($69.99/mo), not per-request billing 793 const ZENROWS_MONTHLY_COST = parseFloat(process.env.ZENROWS_MONTHLY_COST || '69.99'); 794 // ZenRows billing cycle day-of-month (e.g. 3 = renews on the 3rd) 795 const ZENROWS_BILLING_DAY = parseInt(process.env.ZENROWS_BILLING_DAY || '3', 10); 796 // Twilio: pre-tracking SMS spend not recorded in DB (add offset to lifetime estimate) 797 const TWILIO_COST_OFFSET_USD = parseFloat(process.env.TWILIO_COST_OFFSET_USD || '7.30'); 798 799 function fmtCost(usd) { 800 if (usd === null || usd === undefined) return 'n/a'; 801 if (usd < 0.01) return `$${usd.toFixed(4)}`; 802 return `$${usd.toFixed(2)}`; 803 } 804 805 async function renderApiCosts() { 806 // LLM costs by pipeline stage (today + lifetime) 807 const llmRows = await getAll( 808 `SELECT 809 stage, 810 SUM(estimated_cost) AS lifetime, 811 SUM(CASE WHEN created_at >= CURRENT_DATE THEN estimated_cost ELSE 0 END) AS today 812 FROM tel.llm_usage 813 GROUP BY stage 814 ORDER BY lifetime DESC` 815 ); 816 817 // Agent LLM costs (grouped as a single "agents" entry) 818 const agentRow = await getOne( 819 `SELECT 820 SUM(cost_usd) AS lifetime, 821 SUM(CASE WHEN created_at >= CURRENT_DATE THEN cost_usd ELSE 0 END) AS today 822 FROM tel.agent_llm_usage` 823 ); 824 825 // Outreach volume for cost estimates 826 const outreachVolume = await getAll( 827 `SELECT 828 contact_method, 829 COUNT(*) AS lifetime, 830 SUM(CASE WHEN updated_at >= CURRENT_DATE THEN 1 ELSE 0 END) AS today 831 FROM messages 832 WHERE direction = 'outbound' 833 AND delivery_status IN ('sent','delivered','opened','clicked','bounced','failed') 834 GROUP BY contact_method` 835 ); 836 837 // ZenRows: count SERP scrapes — sites that progressed beyond 'found'. 838 const [zenrowsTotalRow, zenrowsFoundRow, zenrowsTodayRow] = await Promise.all([ 839 getOne(`SELECT COUNT(*) AS n FROM sites`), 840 getOne(`SELECT COUNT(*) AS n FROM sites WHERE status = 'found'`), 841 getOne(`SELECT COUNT(*) AS n FROM site_status WHERE status != 'found' AND created_at >= CURRENT_DATE`), 842 ]); 843 const zenrowsRow = { 844 lifetime: Number(zenrowsTotalRow?.n || 0) - Number(zenrowsFoundRow?.n || 0), 845 today: Number(zenrowsTodayRow?.n || 0), 846 }; 847 848 const emailVol = outreachVolume.find(r => r.contact_method === 'email') || { 849 lifetime: 0, 850 today: 0, 851 }; 852 const smsVol = outreachVolume.find(r => r.contact_method === 'sms') || { lifetime: 0, today: 0 }; 853 854 const llmLifetime = llmRows.reduce((s, r) => s + Number(r.lifetime || 0), 0); 855 const llmToday = llmRows.reduce((s, r) => s + Number(r.today || 0), 0); 856 857 console.log(''); 858 console.log(`${C.bold + C.white}API Costs (estimated)${C.reset}`); 859 console.log('─'.repeat(62)); 860 861 // Header 862 console.log( 863 ` ${C.dim}${'Service'.padEnd(22)} ${'Today'.padStart(10)} ${'Lifetime'.padStart(12)}${C.reset}` 864 ); 865 866 // LLM total with per-stage breakdown 867 const llmTodayColor = llmToday > 5 ? C.red : llmToday > 1 ? C.yellow : C.green; 868 console.log( 869 ` ${'LLM (OpenRouter)'.padEnd(22)} ` + 870 `${llmTodayColor}${fmtCost(llmToday).padStart(10)}${C.reset}` + 871 ` ${C.dim}${fmtCost(llmLifetime).padStart(12)}${C.reset}` 872 ); 873 for (const r of llmRows) { 874 const todayColor = Number(r.today || 0) > 1 ? C.yellow : C.dim; 875 console.log( 876 ` ${C.dim}${`└ ${r.stage}`.padEnd(20)} ` + 877 `${todayColor}${fmtCost(Number(r.today)).padStart(10)}${C.reset}` + 878 ` ${C.dim}${fmtCost(Number(r.lifetime)).padStart(12)}${C.reset}` 879 ); 880 } 881 if (Number(agentRow?.lifetime || 0) > 0) { 882 const agentTodayColor = Number(agentRow.today || 0) > 0.5 ? C.yellow : C.dim; 883 console.log( 884 ` ${C.dim}${'└ agents'.padEnd(20)} ` + 885 `${agentTodayColor}${fmtCost(Number(agentRow.today)).padStart(10)}${C.reset}` + 886 ` ${C.dim}${fmtCost(Number(agentRow.lifetime)).padStart(12)}${C.reset}` 887 ); 888 } 889 890 // Resend estimate 891 const resendToday = Number(emailVol.today) * RESEND_COST_PER_EMAIL; 892 const resendLifetime = Number(emailVol.lifetime) * RESEND_COST_PER_EMAIL; 893 console.log( 894 ` ${'Resend (email est.)'.padEnd(22)} ` + 895 `${C.dim}${fmtCost(resendToday).padStart(10)}${C.reset}` + 896 ` ${C.dim}${fmtCost(resendLifetime).padStart(12)}${C.reset}` + 897 ` ${C.dim}${emailVol.today} today / ${Number(emailVol.lifetime).toLocaleString('en-US')} lifetime${C.reset}` 898 ); 899 900 // Twilio estimate (+ offset for pre-tracking spend) 901 const twilioToday = Number(smsVol.today) * TWILIO_COST_PER_SMS; 902 const twilioTracked = Number(smsVol.lifetime) * TWILIO_COST_PER_SMS; 903 const twilioLifetime = twilioTracked + TWILIO_COST_OFFSET_USD; 904 const twilioOffsetNote = 905 TWILIO_COST_OFFSET_USD > 0 906 ? ` ${C.dim}(+$${TWILIO_COST_OFFSET_USD.toFixed(2)} pre-tracking)${C.reset}` 907 : ''; 908 console.log( 909 ` ${'Twilio (SMS est.)'.padEnd(22)} ` + 910 `${C.dim}${fmtCost(twilioToday).padStart(10)}${C.reset}` + 911 ` ${C.dim}${fmtCost(twilioLifetime).padStart(12)}${C.reset}` + 912 ` ${C.dim}${smsVol.today} today / ${Number(smsVol.lifetime).toLocaleString('en-US')} tracked${C.reset}${twilioOffsetNote}` 913 ); 914 915 // ZenRows: flat monthly subscription ($69.99/mo), no daily limit — show cycle progress + today's requests 916 const now = new Date(); 917 const billingDay = ZENROWS_BILLING_DAY; 918 const cycleStart = new Date(now.getFullYear(), now.getMonth(), billingDay); 919 if (cycleStart > now) cycleStart.setMonth(cycleStart.getMonth() - 1); 920 const daysIntoCycle = Math.floor((now - cycleStart) / 86400000); 921 const daysInMonth = Math.round( 922 (new Date(cycleStart.getFullYear(), cycleStart.getMonth() + 1, billingDay) - cycleStart) / 923 86400000 924 ); 925 const cycleUsedFraction = Math.min(daysIntoCycle / daysInMonth, 1); 926 const zenrowsCycleSpend = ZENROWS_MONTHLY_COST * cycleUsedFraction; 927 const zenrowsUsedToday = zenrowsRow?.today || 0; 928 const zenrowsUsedMonth = zenrowsRow?.lifetime || 0; 929 console.log( 930 ` ${'ZenRows (sub $69.99/mo)'.padEnd(22)} ` + 931 `${C.dim}${fmtCost(zenrowsCycleSpend).padStart(10)}${C.reset}` + 932 ` ${C.dim}${'(subscription)'.padStart(12)}${C.reset}` + 933 ` ${C.dim}${zenrowsUsedToday.toLocaleString()} today / ${zenrowsUsedMonth.toLocaleString()} lifetime${C.reset}` + 934 ` ${C.dim}day ${daysIntoCycle}/${daysInMonth} of cycle${C.reset}` 935 ); 936 937 // Grand total today 938 const grandToday = 939 llmToday + Number(agentRow?.today || 0) + resendToday + twilioToday + zenrowsCycleSpend; 940 const grandLifetime = 941 llmLifetime + Number(agentRow?.lifetime || 0) + resendLifetime + twilioLifetime; 942 const grandColor = grandToday > 10 ? C.red : grandToday > 3 ? C.yellow : C.green; 943 console.log(` ${'─'.repeat(58)}`); 944 console.log( 945 ` ${'Total (est.)'.padEnd(22)} ` + 946 `${grandColor}${fmtCost(grandToday).padStart(10)}${C.reset}` + 947 ` ${C.dim}${fmtCost(grandLifetime).padStart(12)}${C.reset}` 948 ); 949 } 950 951 // ────────────────────────────────────────────────────────────────────────────── 952 // Account Health — Resend + Twilio 953 // ────────────────────────────────────────────────────────────────────────────── 954 955 function httpsGet(url, headers = {}) { 956 return new Promise((resolve, reject) => { 957 const req = https.get(url, { headers, agent: new https.Agent({ keepAlive: false }) }, res => { 958 let data = ''; 959 res.on('data', chunk => (data += chunk)); 960 res.on('end', () => { 961 try { 962 resolve({ status: res.statusCode, body: JSON.parse(data) }); 963 } catch { 964 resolve({ status: res.statusCode, body: data }); 965 } 966 }); 967 }); 968 req.on('error', reject); 969 req.setTimeout(3000, () => { 970 req.destroy(); 971 reject(new Error('timeout')); 972 }); 973 }); 974 } 975 976 async function fetchResendHealth() { 977 const apiKey = process.env.RESEND_API_KEY; 978 if (!apiKey) return null; 979 try { 980 const { status, body } = await httpsGet('https://api.resend.com/domains', { 981 Authorization: `Bearer ${apiKey}`, 982 }); 983 if (status !== 200 || !body?.data) return { error: `HTTP ${status}` }; 984 return { domains: body.data }; 985 } catch (e) { 986 return { error: e.message }; 987 } 988 } 989 990 async function fetchTwilioHealth() { 991 const sid = process.env.TWILIO_ACCOUNT_SID; 992 const token = process.env.TWILIO_AUTH_TOKEN; 993 if (!sid || !token) return null; 994 const auth = Buffer.from(`${sid}:${token}`).toString('base64'); 995 const headers = { Authorization: `Basic ${auth}` }; 996 try { 997 const [acctRes, balRes] = await Promise.all([ 998 httpsGet(`https://api.twilio.com/2010-04-01/Accounts/${sid}.json`, headers), 999 httpsGet(`https://api.twilio.com/2010-04-01/Accounts/${sid}/Balance.json`, headers), 1000 ]); 1001 return { 1002 status: acctRes.body?.status, 1003 type: acctRes.body?.type, 1004 balance: balRes.body?.balance ? parseFloat(balRes.body.balance) : null, 1005 currency: balRes.body?.currency, 1006 error: acctRes.status !== 200 ? `HTTP ${acctRes.status}` : null, 1007 }; 1008 } catch (e) { 1009 return { error: e.message }; 1010 } 1011 } 1012 1013 async function getDeliveryStats() { 1014 const [emailRow, complaintsRow, zbRow, smsRow, email1d, complaints1dRow, sms1d] = 1015 await Promise.all([ 1016 getOne( 1017 `SELECT 1018 SUM(CASE WHEN delivery_status IN ('sent','delivered','opened','clicked') THEN 1 ELSE 0 END) as sent, 1019 SUM(CASE WHEN delivery_status='bounced' THEN 1 ELSE 0 END) as bounced, 1020 SUM(CASE WHEN opened_at IS NOT NULL THEN 1 ELSE 0 END) as opened 1021 FROM messages 1022 WHERE direction = 'outbound' 1023 AND contact_method='email' 1024 AND updated_at > NOW() - INTERVAL '30 days'` 1025 ), 1026 getOne( 1027 `SELECT COUNT(*) as n FROM unsubscribed_emails WHERE source='complaint'` 1028 ), 1029 getOne( 1030 `SELECT 1031 SUM(CASE WHEN zb_status='catch-all' THEN 1 ELSE 0 END) as catch_all, 1032 SUM(CASE WHEN zb_status='unknown' THEN 1 ELSE 0 END) as unknown, 1033 SUM(CASE WHEN zb_status='valid' THEN 1 ELSE 0 END) as valid, 1034 COUNT(CASE WHEN zb_status IS NOT NULL THEN 1 END) as total_validated 1035 FROM messages 1036 WHERE direction = 'outbound' 1037 AND contact_method='email' 1038 AND approval_status NOT IN ('pending','rework')` 1039 ), 1040 getOne( 1041 `SELECT 1042 SUM(CASE WHEN delivery_status IN ('sent','delivered') THEN 1 ELSE 0 END) as sent, 1043 SUM(CASE WHEN delivery_status='failed' THEN 1 ELSE 0 END) as failed, 1044 SUM(CASE WHEN delivery_status='skipped' THEN 1 ELSE 0 END) as skipped 1045 FROM messages 1046 WHERE direction = 'outbound' 1047 AND contact_method='sms' 1048 AND updated_at > NOW() - INTERVAL '30 days'` 1049 ), 1050 getOne( 1051 `SELECT 1052 SUM(CASE WHEN delivery_status IN ('sent','delivered','opened','clicked') THEN 1 ELSE 0 END) as sent, 1053 SUM(CASE WHEN delivery_status='bounced' THEN 1 ELSE 0 END) as bounced, 1054 SUM(CASE WHEN opened_at IS NOT NULL THEN 1 ELSE 0 END) as opened 1055 FROM messages 1056 WHERE direction = 'outbound' 1057 AND contact_method='email' 1058 AND updated_at > NOW() - INTERVAL '1 day'` 1059 ), 1060 getOne( 1061 `SELECT COUNT(*) as n FROM unsubscribed_emails WHERE source='complaint' AND created_at > NOW() - INTERVAL '1 day'` 1062 ), 1063 getOne( 1064 `SELECT 1065 SUM(CASE WHEN delivery_status IN ('sent','delivered') THEN 1 ELSE 0 END) as sent, 1066 SUM(CASE WHEN delivery_status='failed' THEN 1 ELSE 0 END) as failed, 1067 SUM(CASE WHEN delivery_status='skipped' THEN 1 ELSE 0 END) as skipped 1068 FROM messages 1069 WHERE direction = 'outbound' 1070 AND contact_method='sms' 1071 AND updated_at > NOW() - INTERVAL '1 day'` 1072 ), 1073 ]); 1074 1075 return { 1076 email: emailRow, 1077 complaints: Number(complaintsRow?.n || 0), 1078 zb: zbRow, 1079 sms: smsRow, 1080 email1d, 1081 complaints1d: Number(complaints1dRow?.n || 0), 1082 sms1d, 1083 }; 1084 } 1085 1086 function pct(num, denom) { 1087 if (!denom || denom === 0) return null; 1088 return ((num / denom) * 100).toFixed(2); 1089 } 1090 1091 function riskColor(value, warnThreshold, badThreshold) { 1092 if (value === null || value === undefined) return C.dim; 1093 if (value >= badThreshold) return C.red; 1094 if (value >= warnThreshold) return C.yellow; 1095 return C.green; 1096 } 1097 1098 async function renderAccountHealth(healthPromise) { 1099 const [resend, twilio] = await (healthPromise ?? 1100 Promise.all([fetchResendHealth(), fetchTwilioHealth()])); 1101 const { email, complaints, zb, sms, email1d, complaints1d, sms1d } = await getDeliveryStats(); 1102 1103 console.log(''); 1104 console.log(`${C.bold + C.white}Account Health${C.reset}`); 1105 console.log('─'.repeat(62)); 1106 1107 // ── Resend ── 1108 console.log(` ${C.bold}Resend (Email)${C.reset}`); 1109 1110 if (resend?.error) { 1111 console.log(` ${C.red}API error: ${resend.error}${C.reset}`); 1112 } else if (resend?.domains) { 1113 for (const domain of resend.domains) { 1114 const ok = domain.status === 'verified'; 1115 const dot = ok ? `${C.green}●` : `${C.red}●`; 1116 console.log(` ${dot}${C.reset} ${domain.name} ${C.dim}(${domain.status})${C.reset}`); 1117 } 1118 } 1119 1120 const emailSent = Number(email?.sent || 0); 1121 const emailBounced = Number(email?.bounced || 0); 1122 const bounceRate = pct(emailBounced, emailSent + emailBounced); 1123 const complaintRate = pct(complaints, emailSent + emailBounced); 1124 const openRate = pct(Number(email?.opened || 0), emailSent); 1125 1126 const bColor = riskColor(parseFloat(bounceRate), 2, 5); 1127 const cColor = riskColor(parseFloat(complaintRate), 0.05, 0.08); 1128 1129 console.log( 1130 ` Bounce rate (30d): ${bColor}${bounceRate ?? 'n/a'}%${C.reset}` + 1131 ` ${C.dim}(${emailBounced.toLocaleString()} bounced / ${(emailSent + emailBounced).toLocaleString()} sent)${C.reset}` + 1132 ` ${C.dim}warn≥2% bad≥5%${C.reset}` 1133 ); 1134 console.log( 1135 ` Complaint rate (all): ${cColor}${complaintRate ?? 'n/a'}%${C.reset}` + 1136 ` ${C.dim}(${complaints} complaints)${C.reset}` + 1137 ` ${C.dim}warn≥0.05% bad≥0.08%${C.reset}` 1138 ); 1139 if (openRate !== null) { 1140 console.log(` Open rate (30d): ${C.cyan}${openRate}%${C.reset}`); 1141 } 1142 1143 // 1d email rates 1144 { 1145 const sent1d = Number(email1d?.sent || 0); 1146 const bounced1d = Number(email1d?.bounced || 0); 1147 const opened1d = Number(email1d?.opened || 0); 1148 const denom1d = sent1d + bounced1d; 1149 const bounce1dRate = pct(bounced1d, denom1d); 1150 const complaint1dRate = pct(complaints1d, denom1d); 1151 const open1dRate = pct(opened1d, sent1d); 1152 const bColor1d = riskColor(parseFloat(bounce1dRate), 2, 5); 1153 const cColor1d = riskColor(parseFloat(complaint1dRate), 0.05, 0.08); 1154 const bounceStr = 1155 denom1d > 0 1156 ? `${bColor1d}${bounce1dRate}%${C.reset} ${C.dim}(${bounced1d}/${denom1d})${C.reset}` 1157 : `${C.dim}--${C.reset}`; 1158 const complaintStr = 1159 denom1d > 0 1160 ? `${cColor1d}${complaint1dRate}%${C.reset} ${C.dim}(${complaints1d}/${denom1d})${C.reset}` 1161 : `${C.dim}--${C.reset}`; 1162 const openStr = 1163 sent1d > 0 1164 ? `${C.cyan}${open1dRate}%${C.reset} ${C.dim}(${opened1d}/${sent1d})${C.reset}` 1165 : `${C.dim}--${C.reset}`; 1166 console.log( 1167 ` Bounce rate (1d): ${bounceStr}` + 1168 ` Complaint rate (1d): ${complaintStr}` + 1169 ` Open rate (1d): ${openStr}` 1170 ); 1171 } 1172 1173 if (Number(zb?.total_validated || 0) > 0) { 1174 const catchAllPct = pct(Number(zb.catch_all), Number(zb.total_validated)); 1175 const unknownPct = pct(Number(zb.unknown), Number(zb.total_validated)); 1176 console.log( 1177 ` ZB validated: ${C.dim}${Number(zb.total_validated).toLocaleString('en-US')} addresses — ` + 1178 `${zb.valid} valid, ${zb.catch_all} catch-all (${catchAllPct}%), ` + 1179 `${zb.unknown} unknown (${unknownPct}%)${C.reset}` 1180 ); 1181 } 1182 1183 // ── Twilio ── 1184 console.log(''); 1185 console.log(` ${C.bold}Twilio (SMS)${C.reset}`); 1186 1187 if (twilio?.error) { 1188 console.log(` ${C.red}API error: ${twilio.error}${C.reset}`); 1189 } else if (twilio) { 1190 const statusColor = twilio.status === 'active' ? C.green : C.red; 1191 const typeLabel = twilio.type === 'Trial' ? ` ${C.yellow}[TRIAL]${C.reset}` : ''; 1192 console.log( 1193 ` Account: ${statusColor}${twilio.status || 'unknown'}${C.reset}${typeLabel}${ 1194 twilio.balance !== null 1195 ? ` Balance: ${twilio.balance < 5 ? C.red : C.green}$${twilio.balance?.toFixed(2)} ${twilio.currency}${C.reset}` 1196 : '' 1197 }` 1198 ); 1199 } 1200 1201 const smsSent = Number(sms?.sent || 0); 1202 const smsFailed = Number(sms?.failed || 0); 1203 const smsDeliveryRate = pct(smsSent, smsSent + smsFailed); 1204 const smsFailRate = pct(smsFailed, smsSent + smsFailed); 1205 const sColor = riskColor(parseFloat(smsFailRate), 5, 10); 1206 1207 console.log( 1208 ` Delivery rate (30d): ${C.green}${smsDeliveryRate ?? 'n/a'}%${C.reset}` + 1209 ` Fail rate: ${sColor}${smsFailRate ?? 'n/a'}%${C.reset}` + 1210 ` ${C.dim}(${smsFailed} failed / ${(smsSent + smsFailed).toLocaleString()} sent)${C.reset}` 1211 ); 1212 1213 // 1d SMS rates 1214 { 1215 const smsSent1d = Number(sms1d?.sent || 0); 1216 const smsFailed1d = Number(sms1d?.failed || 0); 1217 const smsDenom1d = smsSent1d + smsFailed1d; 1218 const smsFailRate1d = pct(smsFailed1d, smsDenom1d); 1219 const sColor1d = riskColor(parseFloat(smsFailRate1d), 5, 10); 1220 const failStr = 1221 smsDenom1d > 0 1222 ? `${sColor1d}${smsFailRate1d}%${C.reset} ${C.dim}(${smsFailed1d}/${smsDenom1d})${C.reset}` 1223 : `${C.dim}--${C.reset}`; 1224 console.log(` Fail rate (1d): ${failStr}`); 1225 } 1226 } 1227 1228 async function main() { 1229 // Fire API health checks immediately so they run in parallel with DB rendering 1230 const healthPromise = Promise.all([fetchResendHealth(), fetchTwilioHealth()]); 1231 1232 const [siteTree, outreachTree, conversationsTree, actionableQueues] = await Promise.all([ 1233 buildStatusTree(), 1234 buildOutreachTree(), 1235 buildConversationsTree(), 1236 getActionableQueues(), 1237 ]); 1238 1239 renderLegend(); 1240 renderStatusTree(siteTree, actionableQueues); 1241 await renderOutreachTree(outreachTree); 1242 await renderConversationsTree(conversationsTree); 1243 renderHaltedChannels(); 1244 await renderApiCosts(); 1245 await renderAccountHealth(healthPromise); 1246 1247 console.log(''); 1248 console.log(`${C.dim}Generated at ${new Date().toLocaleString()}${C.reset}`); 1249 console.log(''); 1250 1251 // Destroy keep-alive sockets so the process exits immediately 1252 https.globalAgent.destroy(); 1253 } 1254 1255 main().catch(err => { 1256 console.error(err); 1257 process.exit(1); 1258 });