← back to Stars of Design
scrapers/george-info-recon.js
192 lines
#!/usr/bin/env node
'use strict';
// Pull DW *client* contacts from George info@designerwallcoverings.com Gmail.
// This is where the actual designer clients live (Shopify orders, sample
// requests, trade signups, custom quotes) — Steve's `from:me` personal inbox
// is vendor-heavy, this one is client-heavy.
//
// We do NOT promote to sod_designers/sod_firms in this script. We populate
// sod_email_candidates with high signal_score and the right notes, then a
// later tick parses signatures + promotes.
//
// Usage:
// node scrapers/george-info-recon.js # default 7 queries × 100
// node scrapers/george-info-recon.js --limit-per-q=50
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 LIMIT_PER_Q = parseInt(args['limit-per-q'] || '100', 10);
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,
});
// Vendor blocklist — never let these through to sod_designers via this path.
const blocklist = JSON.parse(fs.readFileSync(path.join(__dirname, 'vendor-blocklist.json'), 'utf8'));
const VENDOR_DOMAINS = new Set(blocklist.vendor_domains.map(d => d.toLowerCase()));
const DEAD_DOMAINS = new Set(blocklist.dead_domains.map(d => d.toLowerCase()));
const SELF = new Set([
'designerwallcoverings.com','agentabrams.com','noreply.designerwallcoverings.com',
]);
// Gmail searches to walk. Each target is a different kind of client signal.
const SEARCHES = [
// Shopify order notifications. Body contains the customer's billing email.
{ q: 'from:shopifyemail.com OR from:notifications.shopify.com newer_than:5y', tag: 'shopify-order' },
// Trade-program / wholesale / pricing signups.
{ q: '(subject:"trade" OR subject:"wholesale" OR subject:"trade program" OR subject:"trade account") newer_than:5y -from:shopifyemail.com', tag: 'trade-signup' },
// Sample requests (designers requesting memos).
{ q: '(subject:"sample" OR subject:"memo" OR subject:"swatch") newer_than:5y -from:shopifyemail.com', tag: 'sample-request' },
// Custom quote requests.
{ q: '(subject:"quote" OR subject:"custom" OR subject:"pricing") newer_than:5y -from:shopifyemail.com -from:notifications.shopify.com', tag: 'quote-request' },
// Project / specification inquiries.
{ q: '(subject:"project" OR subject:"spec" OR subject:"residence" OR subject:"hospitality") newer_than:5y -from:shopifyemail.com', tag: 'project-inquiry' },
// Designer-flavored subject lines.
{ q: '(subject:"interior" OR subject:"residential" OR subject:"hospitality" OR subject:"design firm") newer_than:5y -from:shopifyemail.com', tag: 'designer-subject' },
];
function parseFromHeader(raw) {
// 'Jane Doe <jane@studio.com>' OR 'jane@studio.com' OR '"Jane Doe" <jane@studio.com>'
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 parseShopifyOrderBody(body) {
// Shopify notifications have structured "Customer information" with email.
// Look for: "Email: <addr>" or "ship-to: ..." or "<email_in_brackets>".
if (!body) return null;
const m = body.match(/(?:Email[:\s]+|Customer[^<]*<)([a-zA-Z0-9._+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})/);
if (m) return m[1].toLowerCase();
return null;
}
async function gmailSearch(q, max = 50) {
const url = `${GEORGE}/api/search?account=info&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}: ${(await r.text()).slice(0,160)}`);
const j = await r.json();
return j.messages || [];
}
async function gmailGetMessage(id) {
const url = `${GEORGE}/api/messages/${id}?account=info`;
const r = await fetch(url, { headers: { Authorization: GEORGE_AUTH } });
if (!r.ok) throw new Error(`george get ${r.status}`);
return r.json();
}
async function upsertCandidate(email, display_name, dateMs, tag, subject, body) {
if (!email || !email.includes('@')) return null;
email = email.toLowerCase().trim();
const domain = email.split('@')[1];
if (SELF.has(domain)) return 'self';
if (DEAD_DOMAINS.has(domain)) return 'dead';
if (VENDOR_DOMAINS.has(domain)) {
// Still record but as vendor — useful audit even on client-side recon.
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), `george:${tag}:${subject ? subject.slice(0,60) : ''}`]);
return 'vendor';
}
// DW client — high signal.
const score = tag === 'shopify-order' ? 0.97 :
tag === 'trade-signup' ? 0.93 :
tag === 'sample-request' ? 0.85 :
tag === 'quote-request' ? 0.82 : 0.78;
const note = `george-info:${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, last_signature, notes)
VALUES ($1,$2,$3,$4,$4,1,1,$5,'is_designer',$6,$7::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),
last_signature = COALESCE(EXCLUDED.last_signature, sod_email_candidates.last_signature),
notes = CONCAT_WS(' || ', NULLIF(sod_email_candidates.notes,''), EXCLUDED.notes),
status = CASE WHEN sod_email_candidates.status='is_vendor' THEN sod_email_candidates.status ELSE 'is_designer' END,
updated_at = NOW()`,
[email, display_name, domain, new Date(dateMs), score,
body ? body.slice(0, 1500) : null,
note]);
return 'client';
}
async function main() {
const ir = await pool.query(
`INSERT INTO sod_ingest_runs (run_kind, source, status) VALUES ('george-info-recon','info@designerwallcoverings.com','running') RETURNING id`
);
const runId = ir.rows[0].id;
const tally = { messages_scanned: 0, clients: 0, vendors: 0, self: 0, dead: 0, no_email: 0 };
for (const { q, tag } of SEARCHES) {
console.log(`\n[${tag}] q="${q.slice(0,80)}…" limit=${LIMIT_PER_Q}`);
let msgs;
try { msgs = await gmailSearch(q, LIMIT_PER_Q); }
catch (e) { console.error(` search failed: ${e.message}`); continue; }
console.log(` ${msgs.length} messages`);
for (const m of msgs) {
tally.messages_scanned++;
let email = null, display_name = null;
// For Shopify-order notifications we need the BILLING email from the body,
// not the From: header (which is mailer@shopifyemail.com).
if (tag === 'shopify-order' || /shopifyemail|notifications\.shopify/.test(m.from || '')) {
try {
const full = await gmailGetMessage(m.id);
const body = (full.body || full.plaintext_body || full.payload?.snippet || '');
email = parseShopifyOrderBody(body);
} catch { /* skip */ }
if (!email) { tally.no_email++; continue; }
} else {
const parsed = parseFromHeader(m.from);
if (!parsed) { tally.no_email++; continue; }
email = parsed.email;
display_name = parsed.display_name;
}
const result = await upsertCandidate(
email, display_name,
m.date ? Date.parse(m.date) : Date.now(),
tag, m.subject, m.snippet || ''
);
if (result === 'client') tally.clients++;
else if (result === 'vendor') tally.vendors++;
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, JSON.stringify(tally), runId]
);
console.log(`\n[george-info-recon] done. tally=${JSON.stringify(tally)}`);
await pool.end();
}
main().catch(e => { console.error('fatal:', e); process.exit(1); });