← back to Stars of Design
scrapers/extract-from-signatures.js
339 lines
#!/usr/bin/env node
'use strict';
// Tick 3 — Fill empty display_name + firm/role/city/LinkedIn for sod_email_candidates
// by parsing each contact's most-recent George info@ message body. Why this
// tick — promote-candidates.js can't materialize sod_designers/sod_firms rows
// without a name, and Gmail MCP returned bare-email From: headers for many
// threads. Their actual identity lives in the message body's signature block.
//
// Flow per candidate:
// 1. george /api/search?account=info&q=from:<email> OR to:<email> → message ids
// 2. george /api/messages/<id>?account=info → full body
// 3. Slice body to plausible signature region (last ~1.5 KB)
// 4. gemma3:12b in JSON mode → { full_name, firm_name, firm_city, firm_role,
// linkedin_personal, linkedin_company, website, phone }
// 5. UPDATE sod_email_candidates(display_name, last_signature, notes)
// 6. INSERT linkedin/site URLs into sod_links via designer_id / firm_id
// 7. Promote via promote-candidates.js when the LLM gives us at least a
// person OR firm name.
//
// Usage:
// node scrapers/extract-from-signatures.js --limit=25
// node scrapers/extract-from-signatures.js --email=jane@studio.com # one row
// node scrapers/extract-from-signatures.js --dry # no writes
require('dotenv').config();
const { Pool } = require('pg');
const path = require('path');
const args = process.argv.slice(2).reduce((m, a) => {
const [k, v] = a.replace(/^--/, '').split('=');
m[k] = v ?? true; return m;
}, {});
const LIMIT = parseInt(args.limit || '25', 10);
const ONLY_EMAIL = args.email || null;
const DRY = !!args.dry;
const MODEL = args.model || 'gemma3:12b';
const GEORGE = process.env.GEORGE_URL || 'http://127.0.0.1:9850';
const GEORGE_AUTH = 'Basic ' + Buffer.from('admin:DWSecure2024!').toString('base64');
const OLLAMA = process.env.OLLAMA_HOST || 'http://127.0.0.1:11434';
const pool = new Pool({
connectionString: process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp&user=stevestudio2',
max: 4,
});
const SYSTEM = `You extract the actual person and firm identity from an email signature
block. The body is the most recent email from / to a designer or firm DW corresponds
with. Look for the signature region (after a -- delimiter, or the final
4-12 lines containing name + title + firm + phone + url patterns).
Output STRICT JSON, no preamble:
{
"full_name": "<person's first+last> or null",
"firm_name": "<firm/studio/agency> or null",
"firm_city": "<city only — no street, no zip> or null",
"firm_state": "<state/province two-letter or full name> or null",
"firm_country": "<country if non-US> or null",
"firm_role": "<the person's title> or null",
"areas_served": ["<region>", ...] ,
"linkedin_personal": "<linkedin.com/in/... or null>",
"linkedin_company": "<linkedin.com/company/... or null>",
"website": "<firm primary URL or null>",
"instagram": "<instagram URL or @handle or null>"
}
Rules:
- Don't invent. If a field isn't clearly in the body, return null.
- **NEVER extract street address. NEVER extract zip / postal code.** City and
state only — Steve's directory PII policy. If the signature contains a
street address like "123 Main Street, Suite 4B" — IGNORE the street part,
keep only the city/state if present after it.
- **NEVER extract phone numbers.** Phone is opt-in via the designer's claim
flow, not via signature scraping.
- For areas_served, look for "Serving:", "Areas:", "We cover", or a
parenthetical region phrase. Otherwise infer SAFELY from city/state — a
Manhattan firm typically serves NYC + Hamptons; an LA firm typically
serves LA + Palm Springs. Return [] if you can't infer.
- Skip the email-quoting (lines starting with >).
- Skip footer disclaimers and unsubscribe links.
- If the body is a Shopify order notification with no signature, return all
nulls (and areas_served=[]).
- Strip emoji and decorative characters from names.
`;
async function ask(systemPrompt, userPrompt) {
const r = await fetch(`${OLLAMA}/api/chat`, {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify({
model: MODEL,
stream: false,
format: 'json',
messages: [
{ role: 'system', content: systemPrompt },
{ role: 'user', content: userPrompt.slice(0, 6000) },
],
options: { temperature: 0.1, num_predict: 280 },
}),
});
if (!r.ok) throw new Error(`ollama ${r.status}`);
const j = await r.json();
const raw = (j.message && j.message.content) || '';
const stripped = raw.replace(/<think>[\s\S]*?<\/think>/gi, '').trim();
const m = stripped.match(/\{[\s\S]*\}/);
if (!m) throw new Error('no JSON in response');
return JSON.parse(m[0]);
}
// Try each George account in order until one returns a hit. info@ is
// where most DW client traffic lands; steve-office is the secondary fallback
// for designers who emailed steve@dw directly.
const ACCOUNTS = ['info', 'steve-office'];
async function georgeSearch(q, max = 1) {
for (const acct of ACCOUNTS) {
const url = `${GEORGE}/api/search?account=${acct}&q=${encodeURIComponent(q)}&maxResults=${max}`;
const r = await fetch(url, { headers: { Authorization: GEORGE_AUTH } });
if (!r.ok) continue;
const j = await r.json();
const m = (j.messages || [])[0];
if (m) return { ...m, _account: acct };
}
return null;
}
async function georgeGet(id, account = 'info') {
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();
}
function trimToSignature(body) {
if (!body) return '';
// Drop quoted-reply lines (start with > or "On <date> ... wrote:")
const lines = body.split(/\r?\n/);
const cutIdx = lines.findIndex(l => /^On\s+.+\s+wrote:\s*$/.test(l));
const sliced = cutIdx > 0 ? lines.slice(0, cutIdx) : lines;
const noQuotes = sliced.filter(l => !/^[>]/.test(l));
// Find -- delimiter
let sigStart = -1;
for (let i = noQuotes.length - 1; i >= 0 && i >= noQuotes.length - 40; i--) {
if (/^--\s*$/.test(noQuotes[i])) { sigStart = i; break; }
}
if (sigStart >= 0) return noQuotes.slice(sigStart, sigStart + 15).join('\n');
// No -- delimiter: take last 14 non-empty lines
const nonEmpty = noQuotes.map(l => l.trim()).filter(Boolean);
return nonEmpty.slice(-14).join('\n');
}
function slugify(s) {
return String(s || '').toLowerCase().replace(/[^a-z0-9]+/g,'-').replace(/^-+|-+$/g,'').slice(0,100);
}
async function processCandidate(c) {
// Search George for the most recent message exchanged with this email.
// Try `from:` first (incoming), then `to:` (outgoing).
let m = await georgeSearch(`from:${c.email}`, 1);
if (!m) m = await georgeSearch(`to:${c.email}`, 1);
if (!m) { console.log(` ${c.email.padEnd(36)} no-message`); return { skipped: true }; }
const full = await georgeGet(m.id, m._account || 'info');
if (!full) { console.log(` ${c.email.padEnd(36)} get-failed`); return { skipped: true }; }
// Body candidates: plaintext_body OR snippet OR payload.body
const body = full.plaintext_body || full.body || full.snippet || '';
if (!body || body.length < 60) { console.log(` ${c.email.padEnd(36)} body-too-short`); return { skipped: true }; }
const sig = trimToSignature(body);
if (!sig || sig.length < 30) { console.log(` ${c.email.padEnd(36)} no-sig-region`); return { skipped: true }; }
let parsed;
try {
parsed = await ask(SYSTEM, `Email: ${c.email}\nDomain: ${c.domain}\n\nSignature region:\n${sig}`);
} catch (e) {
console.log(` ${c.email.padEnd(36)} llm-fail: ${e.message}`);
return { skipped: true };
}
// Refuse obviously-bad LLM output
let name = parsed.full_name && parsed.full_name.length > 2 && parsed.full_name.length < 80 ? parsed.full_name.trim() : null;
let firm = parsed.firm_name && parsed.firm_name.length > 2 && parsed.firm_name.length < 120 ? parsed.firm_name.trim() : null;
// GUARD: the LLM occasionally grabs DW's outgoing signature ("Designer
// Wallcoverings and Fabrics") and reports it as the contact's firm. The
// contact is NOT working for DW — that's our own signature inside an
// outgoing message the contact replied to. Refuse.
if (firm && /^Designer\s+Wallcoverings/i.test(firm)) firm = null;
// GUARD: gemma3:12b occasionally pattern-matches a generic first name
// ("Samantha", "Steve", "Sarah", "Jenny", "Vicky") when it can't find a
// real one — usually grabbing DW's outgoing signature embedded in a
// reply chain. Refuse a bare first-name unless it actually appears in
// the local-part of the email address.
if (name) {
const isJustFirstName = /^[A-Z][a-z]+$/.test(name);
const localPart = c.email.split('@')[0].toLowerCase();
const nameLow = name.toLowerCase();
if (isJustFirstName && !localPart.includes(nameLow.slice(0, 4))) name = null;
}
// GUARD: refuse firm names that are just the LLM's noise output.
if (firm && /^(N\/?A|Unknown|None|-)$/i.test(firm)) firm = null;
// GUARD: refuse firm names that are obviously just a website (the LLM
// sometimes echoes the domain as the firm name).
if (firm && /^[a-z0-9-]+\.(com|net|org|co|io|studio)$/i.test(firm)) firm = null;
// GUARD: per directory PII policy [[feedback_designer_directory_pii_policy]]
// — strip any street-address-shaped fragment that snuck into firm_city.
let firmCity = parsed.firm_city || null;
if (firmCity) {
firmCity = firmCity
.replace(/\b\d+\s+[A-Z][A-Za-z'.\-]+\s+(?:Street|St|Avenue|Ave|Road|Rd|Boulevard|Blvd|Lane|Ln|Drive|Dr|Court|Ct|Place|Pl|Way|Pkwy|Pike|Hwy|Highway|Suite|Ste|Unit|Apt)\b\.?,?\s*/gi, '')
.replace(/\b\d{5}(?:-\d{4})?\b/g, '') // zip
.trim().replace(/^,|,$/, '').trim();
if (firmCity.length < 2) firmCity = null;
}
const firmState = (parsed.firm_state || null);
const firmCountry = parsed.firm_country || null;
// areas_served — accept array of strings, dedupe, strip street/zip noise
let areas = Array.isArray(parsed.areas_served)
? parsed.areas_served.filter(x => typeof x === 'string').map(s => s.trim())
.filter(s => s.length >= 2 && s.length < 60)
.filter(s => !/^\d/.test(s)) // no leading digits (addresses)
.filter(s => !/\b\d{5}\b/.test(s)) // no zip
: [];
areas = [...new Set(areas)].slice(0, 12);
console.log(` ${c.email.padEnd(36)} → name=${name||'-'} firm=${firm||'-'} city=${firmCity||'-'} state=${firmState||'-'} areas=${areas.length}`);
if (DRY) return { dry: true, parsed };
// 1. Update candidate row
await pool.query(`
UPDATE sod_email_candidates
SET display_name = COALESCE($1, display_name),
last_signature = $2,
notes = CONCAT_WS(' || ', NULLIF(notes,''), 'sig-extract:' || COALESCE($1,'null') || '|' || COALESCE($3,'null') || '|' || COALESCE($4,'null')),
updated_at = NOW()
WHERE id = $5`,
[name, sig, firm, parsed.firm_role, c.id]);
// 2. If we got a person name OR firm name, materialize in sod_designers/sod_firms
let designerId = null, firmId = null;
if (firm) {
const slug = slugify(firm);
const f = await pool.query(`
INSERT INTO sod_firms (slug, name, city, state_or_region, country, areas_served, primary_email)
VALUES ($1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (slug) DO UPDATE SET
city = COALESCE(sod_firms.city, EXCLUDED.city),
state_or_region = COALESCE(sod_firms.state_or_region, EXCLUDED.state_or_region),
country = COALESCE(sod_firms.country, EXCLUDED.country),
areas_served = COALESCE(sod_firms.areas_served, EXCLUDED.areas_served),
primary_email = COALESCE(sod_firms.primary_email, EXCLUDED.primary_email),
updated_at = NOW()
RETURNING id`,
[slug, firm, firmCity, firmState, firmCountry, areas.length ? areas : null, c.email]);
firmId = f.rows[0].id;
}
if (name) {
const slug = slugify(name);
const d = await pool.query(`
INSERT INTO sod_designers (slug, full_name, primary_email, city, state_or_region, country, areas_served, role, headshot_source)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 'made-with-ai')
ON CONFLICT (slug) DO UPDATE SET
primary_email = COALESCE(sod_designers.primary_email, EXCLUDED.primary_email),
city = COALESCE(sod_designers.city, EXCLUDED.city),
state_or_region = COALESCE(sod_designers.state_or_region, EXCLUDED.state_or_region),
country = COALESCE(sod_designers.country, EXCLUDED.country),
areas_served = COALESCE(sod_designers.areas_served, EXCLUDED.areas_served),
role = COALESCE(sod_designers.role, EXCLUDED.role),
updated_at = NOW()
RETURNING id`,
[slug, name, c.email, firmCity, firmState, firmCountry, areas.length ? areas : null, parsed.firm_role]);
designerId = d.rows[0].id;
if (firmId) {
await pool.query(`
INSERT INTO sod_designer_firm (designer_id, firm_id, title, is_current, source)
VALUES ($1, $2, $3, true, 'sig-extract')
ON CONFLICT DO NOTHING`,
[designerId, firmId, parsed.firm_role || 'Designer']);
}
}
// 3. Stash any LinkedIn / website / instagram URLs. Phone deliberately NOT
// extracted — per directory PII policy phone is opt-in via claim flow.
let igUrl = parsed.instagram || null;
if (igUrl && igUrl.startsWith('@')) igUrl = `https://instagram.com/${igUrl.slice(1)}`;
for (const [url, kind] of [
[parsed.linkedin_personal, 'linkedin'],
[parsed.linkedin_company, 'linkedin'],
[parsed.website, 'firm-site'],
[igUrl, 'instagram'],
]) {
if (!url || !/^https?:\/\//.test(url)) continue;
if (!designerId && !firmId) continue;
await pool.query(`
INSERT INTO sod_links (designer_id, firm_id, url, kind, added_by)
VALUES ($1, $2, $3, $4, 'sig-extract')
ON CONFLICT DO NOTHING`,
[designerId, firmId, url, kind]);
}
return { ok: true, name, firm, city: firmCity, state: firmState, areas_count: areas.length };
}
async function main() {
let sql, params;
if (ONLY_EMAIL) {
sql = `SELECT id, email, domain, signal_score
FROM sod_email_candidates
WHERE email = $1
LIMIT 1`;
params = [ONLY_EMAIL];
} else {
// Two-phase select. The candidates we want most are:
// a) status=is_designer with empty display_name (highest yield — name is
// the prerequisite to materializing sod_designers)
// b) status=is_designer WITH a name but no sod_designers row yet (firm
// / role / LinkedIn enrichment — gives the 418 named candidates real
// profiles instead of bare-name placeholders)
// We prioritize (a) over (b) by ordering empty-name rows first.
sql = `SELECT c.id, c.email, c.domain, c.signal_score
FROM sod_email_candidates c
LEFT JOIN sod_designers d ON d.primary_email = c.email
WHERE c.status = 'is_designer'
AND d.id IS NULL
ORDER BY (CASE WHEN c.display_name IS NULL OR c.display_name='' THEN 0 ELSE 1 END),
c.signal_score DESC NULLS LAST
LIMIT $1`;
params = [LIMIT];
}
const r = await pool.query(sql, params);
console.log(`[sig-extract] ${r.rows.length} candidate(s) model=${MODEL} dry=${DRY}`);
const tally = { ok:0, withName:0, withFirm:0, skipped:0 };
for (const c of r.rows) {
const res = await processCandidate(c);
if (res.ok || res.dry) {
tally.ok++;
if (res.name) tally.withName++;
if (res.firm) tally.withFirm++;
} else tally.skipped++;
}
console.log(`[sig-extract] done. ${JSON.stringify(tally)}`);
await pool.end();
}
main().catch(e => { console.error('fatal:', e); process.exit(1); });