← back to Stars of Design
scrapers/george-deep-recon.js
221 lines
#!/usr/bin/env node
'use strict';
// DEEP George info@ recon — go way past the first sweep. The gold lives in:
//
// - Steve's outgoing replies from info@designerwallcoverings.com to designers
// who asked questions (designer is in the To: of an info@ outgoing reply)
// - DW's free-sample order shippings (every sample mailing has a real-world
// designer address in the body)
// - Hospitality / commercial project specifiers
// - Wallcovering memo / fabric memo / specification request replies
// - Direct trade-program approvals (we approved them, so we have their info)
//
// This script DOES NOT promote — it only widens sod_email_candidates. The
// next sig-extract pass converts winners to sod_designers/firms.
//
// George's search response has no easy cursor pagination, so we date-slice
// each query into yearly windows (newer_than: + older_than:) to spread out
// the result set.
require('dotenv').config();
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
const args = process.argv.slice(2).reduce((m, a) => {
const [k, v] = a.replace(/^--/, '').split('=');
m[k] = v ?? true; return m;
}, {});
const MAX_PER_Q = parseInt(args['max-per-q'] || '100', 10);
const ACCOUNT = args.account || 'info';
const DRY = !!args.dry;
const GEORGE = process.env.GEORGE_URL || 'http://127.0.0.1:9850';
const GEORGE_AUTH = 'Basic ' + Buffer.from('admin:DWSecure2024!').toString('base64');
const pool = new Pool({
connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
max: 4,
});
const blocklist = JSON.parse(fs.readFileSync(path.join(__dirname, 'vendor-blocklist.json'), 'utf8'));
const VENDOR = new Set(blocklist.vendor_domains.map(d => d.toLowerCase()));
const DEAD = new Set(blocklist.dead_domains.map(d => d.toLowerCase()));
const SELF = new Set([
'designerwallcoverings.com','agentabrams.com','noreply.designerwallcoverings.com',
'gmail.com', // we want gmail addresses BUT only if the name+subject is designer-flavored — handled in scoring
]);
// Year buckets to date-slice. Each bucket runs the full query set.
// --windows=v2 adds the 5y-10y range for the deeper historical sweep.
const WINDOWS_V1 = [
{ name: 'last-30d', newer: '30d', older: null },
{ name: '30d-90d', newer: '90d', older: '30d' },
{ name: '90d-180d', newer: '180d', older: '90d' },
{ name: '180d-1y', newer: '1y', older: '180d' },
{ name: '1y-2y', newer: '2y', older: '1y' },
{ name: '2y-3y', newer: '3y', older: '2y' },
{ name: '3y-5y', newer: '5y', older: '3y' },
];
const WINDOWS_V2 = [
{ name: '5y-7y', newer: '7y', older: '5y' },
{ name: '7y-10y', newer: '10y', older: '7y' },
];
const WINDOWS = (args.windows === 'v2') ? WINDOWS_V2 :
(args.windows === 'all') ? [...WINDOWS_V1, ...WINDOWS_V2] :
WINDOWS_V1;
// Query templates — applied to every window. Each yields a different
// designer-side signal vein.
const Q_TEMPLATES = [
// Steve REPLYING from info@ → To: is a designer asking a question.
{ tag: 'info-replied-to', q: 'from:info@designerwallcoverings.com', score: 0.94 },
{ tag: 'info-replied-steve', q: 'from:steve@designerwallcoverings.com', score: 0.94 },
// Sample / memo requests (any direction).
{ tag: 'sample', q: '(subject:sample OR subject:memo OR subject:swatch OR subject:"free sample")', score: 0.90 },
// Trade-program signals.
{ tag: 'trade', q: '(subject:trade OR subject:wholesale OR subject:"trade account" OR subject:"trade program")', score: 0.93 },
// Custom-quote / pricing requests.
{ tag: 'quote', q: '(subject:quote OR subject:pricing OR subject:custom OR subject:"net price")', score: 0.86 },
// Specifier / project subject patterns.
{ tag: 'project', q: '(subject:project OR subject:residence OR subject:hospitality OR subject:restaurant OR subject:hotel OR subject:loft OR subject:condo)', score: 0.80 },
// Designer-flavored language in the body / subject.
{ tag: 'role-keyword', q: '(subject:designer OR subject:interior OR subject:residential OR subject:specifier OR subject:specification OR subject:"design firm" OR subject:"design studio")', score: 0.80 },
// Direct DW-mailer replies — designers who replied to a campaign.
{ tag: 'mailer-reply', q: '(subject:"Re:" OR subject:"RE:") in:inbox -from:notifications.shopify.com -from:shopifyemail.com', score: 0.78 },
];
function parseFromHeader(raw) {
if (!raw) return null;
const m = raw.match(/^\s*"?([^"<]+?)"?\s*<([^>]+)>\s*$/) || raw.match(/^\s*([^\s<>"]+@[^\s<>"]+)\s*$/);
if (!m) return null;
if (m.length === 3) return { display_name: m[1].trim(), email: m[2].toLowerCase().trim() };
return { display_name: null, email: m[1].toLowerCase().trim() };
}
function parseToHeader(raw) {
// Recipient list — return first non-self
if (!raw) return null;
const parts = raw.split(',');
for (const p of parts) {
const parsed = parseFromHeader(p.trim());
if (parsed && !/designerwallcoverings\.com$/i.test(parsed.email)) return parsed;
}
return null;
}
async function gmailSearch(q, max = 50) {
const url = `${GEORGE}/api/search?account=${ACCOUNT}&q=${encodeURIComponent(q)}&maxResults=${max}`;
const r = await fetch(url, { headers: { Authorization: GEORGE_AUTH } });
if (!r.ok) throw new Error(`george /api/search ${r.status}`);
return (await r.json()).messages || [];
}
async function gmailGet(id) {
const url = `${GEORGE}/api/messages/${id}?account=${ACCOUNT}`;
const r = await fetch(url, { headers: { Authorization: GEORGE_AUTH } });
if (!r.ok) return null;
return r.json();
}
async function upsertCandidate(email, display_name, dateMs, tag, subject, score) {
if (!email || !email.includes('@')) return null;
email = email.toLowerCase().trim();
const domain = email.split('@')[1];
if (/designerwallcoverings\.com$/i.test(domain) || /agentabrams\.com$/i.test(domain)) return 'self';
if (DEAD.has(domain)) return 'dead';
if (VENDOR.has(domain)) {
if (!DRY) await pool.query(`
INSERT INTO sod_email_candidates (email, display_name, domain, first_seen, last_seen, thread_count, steve_replied, signal_score, status, notes)
VALUES ($1,$2,$3,$4,$4,1,0,0,'is_vendor',$5::text)
ON CONFLICT (email) DO UPDATE SET
last_seen = GREATEST(sod_email_candidates.last_seen, EXCLUDED.last_seen),
thread_count = sod_email_candidates.thread_count + 1,
notes = CONCAT_WS(' || ', NULLIF(sod_email_candidates.notes,''), EXCLUDED.notes),
updated_at = NOW()`,
[email, display_name, domain, new Date(dateMs), `deep-recon:${tag}:${subject ? subject.slice(0,60) : ''}`]);
return 'vendor';
}
// Real client
const note = `deep-recon:${tag}${subject ? ' · '+subject.slice(0,80) : ''}`;
if (!DRY) await pool.query(`
INSERT INTO sod_email_candidates (email, display_name, domain, first_seen, last_seen, thread_count, steve_replied, signal_score, status, notes)
VALUES ($1,$2,$3,$4,$4,1,1,$5,'is_designer',$6::text)
ON CONFLICT (email) DO UPDATE SET
display_name = COALESCE(EXCLUDED.display_name, sod_email_candidates.display_name),
first_seen = LEAST(sod_email_candidates.first_seen, EXCLUDED.first_seen),
last_seen = GREATEST(sod_email_candidates.last_seen, EXCLUDED.last_seen),
thread_count = sod_email_candidates.thread_count + 1,
steve_replied = sod_email_candidates.steve_replied + 1,
signal_score = GREATEST(sod_email_candidates.signal_score, EXCLUDED.signal_score),
notes = CONCAT_WS(' || ', NULLIF(sod_email_candidates.notes,''), EXCLUDED.notes),
status = CASE WHEN sod_email_candidates.status IN ('is_vendor','is_noise') THEN sod_email_candidates.status ELSE 'is_designer' END,
updated_at = NOW()`,
[email, display_name, domain, new Date(dateMs), score, note]);
return 'client';
}
async function main() {
const ir = await pool.query(
`INSERT INTO sod_ingest_runs (run_kind, source, status) VALUES ('george-deep-recon', $1, 'running') RETURNING id`,
[`${ACCOUNT}@george`]
);
const runId = ir.rows[0].id;
const tally = { messages_scanned:0, clients_new_or_bumped:0, vendors_caught:0, self:0, dead:0, no_email:0 };
for (const win of WINDOWS) {
for (const tpl of Q_TEMPLATES) {
// Build the windowed query
let q = tpl.q;
if (win.newer) q += ` newer_than:${win.newer}`;
if (win.older) q += ` older_than:${win.older}`;
let msgs;
try { msgs = await gmailSearch(q, MAX_PER_Q); }
catch (e) { console.error(` [${win.name}/${tpl.tag}] search-fail: ${e.message}`); continue; }
if (!msgs.length) continue;
console.log(` [${win.name}/${tpl.tag}] ${msgs.length} msgs`);
for (const m of msgs) {
tally.messages_scanned++;
// For info@ outgoing replies, the designer is in To:. We need full
// message to get To. Use snippet first; pull get_message only when
// From: is DW-self.
const fromIsSelf = /designerwallcoverings\.com|agentabrams\.com/i.test(m.from || '');
let extracted = null;
if (fromIsSelf) {
// For info@ outgoing replies, the designer lives in To:.
// George's /api/messages/:id returns `to` as a flat top-level
// header string.
const full = await gmailGet(m.id);
if (!full || !full.to) { tally.no_email++; continue; }
extracted = parseToHeader(full.to);
} else {
extracted = parseFromHeader(m.from);
}
if (!extracted) { tally.no_email++; continue; }
const result = await upsertCandidate(
extracted.email,
extracted.display_name,
m.date ? Date.parse(m.date) : Date.now(),
tpl.tag,
m.subject,
tpl.score
);
if (result === 'client') tally.clients_new_or_bumped++;
else if (result === 'vendor') tally.vendors_caught++;
else if (result === 'dead') tally.dead++;
else if (result === 'self') tally.self++;
}
}
}
await pool.query(
`UPDATE sod_ingest_runs SET status='ok', finished_at=NOW(), records_in=$1, records_out=$2, notes=$3 WHERE id=$4`,
[tally.messages_scanned, tally.clients_new_or_bumped, JSON.stringify(tally), runId]
);
console.log(`\n[george-deep-recon] tally=${JSON.stringify(tally)}`);
await pool.end();
}
main().catch(e => { console.error('fatal:', e); process.exit(1); });