← back to Ventura Corridor
src/enrich/scrape_contacts.ts
323 lines
/**
* Mine emails + LinkedIn URLs from already-crawled raw HTML files
* (data/raw/firm-NNN.html) and insert into business_contacts.
*
* No HTTP calls. No LLM. Pure regex + INSERT.
*
* Roles inserted:
* - email_general (generic info@/contact@/sales@/hello@)
* - email_personal (firstname.lastname@... or single-name@)
* - linkedin_company (linkedin.com/company/<slug>)
* - linkedin_person (linkedin.com/in/<slug>)
*/
import { readFileSync } from 'node:fs';
import { join } from 'node:path';
import { query, pool } from '../../db/pool.ts';
const RAW_DIR = join(process.cwd(), 'data/raw');
// Strict email pattern. Must be a real-looking address, not a CDN URL fragment.
const EMAIL_RE = /\b([a-zA-Z0-9._+-]+)@([a-zA-Z0-9.-]+\.[a-zA-Z]{2,})\b/g;
// LinkedIn URL patterns
const LI_COMPANY_RE = /linkedin\.com\/company\/([a-zA-Z0-9_-]+)/gi;
const LI_PERSON_RE = /linkedin\.com\/in\/([a-zA-Z0-9_-]+)/gi;
// Social profile patterns
const IG_RE = /instagram\.com\/([a-zA-Z0-9_.]{1,30})(?:\/|\?|"|$)/gi;
const FB_RE = /facebook\.com\/([a-zA-Z0-9._-]{2,80})(?:\/|\?|"|$)/gi;
const X_RE = /(?:twitter|x)\.com\/([a-zA-Z0-9_]{1,15})(?:\/|\?|"|$)/gi;
const BSKY_RE = /bsky\.app\/profile\/([a-zA-Z0-9.-]+)(?:\/|\?|"|$)/gi;
// IG/FB/X path-segments that aren't profiles (filter out)
const IG_NOT_HANDLE = new Set(['p', 'reel', 'reels', 'explore', 'tv', 'stories', 'accounts', 'about', 'directory', 'embed']);
const FB_NOT_HANDLE = new Set(['sharer', 'sharer.php', 'dialog', 'plugins', 'tr', 'login', 'help', 'business', 'pages', 'events', 'groups', 'permalink.php']);
const X_NOT_HANDLE = new Set(['intent', 'share', 'home', 'search', 'i', 'compose', 'hashtag', 'explore', 'login']);
// Filter junk: emails from image CDNs, sentry, analytics, common boilerplate.
const EMAIL_BLOCK_DOMAINS = new Set([
'sentry.io', 'sentry-cdn.com', 'wixpress.com', 'wix.com',
'wordpress.com', 'wp.com', 'godaddy.com', 'squarespace.com',
'shopify.com', 'wix.com', 'gravatar.com', 'gstatic.com',
'googleusercontent.com', 'googleapis.com', 'cloudflare.com',
'cloudfront.net', 'amazonaws.com', 'akamai.net', 'doubleclick.net',
'jsdelivr.net', 'unpkg.com', 'bootstrapcdn.com', 'jquery.com',
'fontawesome.com', 'typekit.net', 'fonts.googleapis.com',
'fonts.gstatic.com', 'www.w3.org', 'schema.org', 'example.com',
'sentry-io', 'newrelic.com', 'mixpanel.com', 'segment.io',
'hubspot.com', 'mailchimp.com', 'mc.us10.list-manage.com',
]);
// Personal-name local-part heuristic: contains a dot or is a single word ≥3 chars
// and NOT in the generic set.
const GENERIC_LOCAL_PARTS = new Set([
'info', 'contact', 'sales', 'hello', 'support', 'help',
'admin', 'office', 'team', 'mail', 'email', 'inquiries',
'customerservice', 'service', 'reception', 'frontdesk',
'press', 'media', 'pr', 'marketing', 'hr', 'jobs', 'careers',
'webmaster', 'postmaster', 'no-reply', 'noreply', 'donotreply',
'billing', 'accounts', 'accounting', 'legal', 'compliance',
'general', 'enquiries', 'enquiry', 'feedback', 'newsletter',
'subscribe', 'unsubscribe', 'orders', 'rsvp', 'events',
]);
interface AuditRow {
business_id: number;
business_name: string;
raw_html_path: string | null;
fetched_url: string | null;
}
async function pickQueue(): Promise<AuditRow[]> {
// Latest audit per business that has a raw HTML file.
const r = await query<AuditRow>(`
SELECT DISTINCT ON (a.business_id)
a.business_id, b.name AS business_name, a.raw_html_path, a.fetched_url
FROM front_page_audits a
JOIN businesses b ON b.id = a.business_id
WHERE a.raw_html_path IS NOT NULL
ORDER BY a.business_id, a.audited_at DESC
`);
return r.rows;
}
function classifyEmail(email: string): 'email_general' | 'email_personal' | null {
const [local, domain] = email.split('@');
if (!domain) return null;
if (EMAIL_BLOCK_DOMAINS.has(domain.toLowerCase())) return null;
// Reject image/font filenames masquerading as emails (e.g. shadow@2x.png)
if (/\.(png|jpe?g|gif|svg|webp|ico|bmp|tiff?|woff2?|ttf|eot|css|js|json|xml)$/i.test(domain)) return null;
// Block image/asset filename collisions like 2x@cdn.example.com (single digit local)
if (/^\d+x?$/.test(local)) return null;
// Block obvious non-emails
if (local.length < 2 || local.length > 64) return null;
if (domain.length < 4 || domain.length > 100) return null;
// Reject sentry/analytics-style email-shaped fragments
for (const blocked of EMAIL_BLOCK_DOMAINS) if (domain.endsWith('.' + blocked)) return null;
const localLower = local.toLowerCase();
if (GENERIC_LOCAL_PARTS.has(localLower)) return 'email_general';
// Looks like a person if dot-separated or all-letters and not generic
if (localLower.includes('.') || /^[a-zA-Z]+$/.test(localLower)) return 'email_personal';
return 'email_general';
}
interface Found {
emails: { email: string; role: 'email_general' | 'email_personal' }[];
linkedinCompany: string[];
linkedinPerson: string[];
instagram: { handle: string; url: string }[];
facebook: { handle: string; url: string }[];
twitter: { handle: string; url: string }[];
bluesky: { handle: string; url: string }[];
}
function extractFromHtml(html: string): Found {
const seenEmail = new Set<string>();
const emails: Found['emails'] = [];
let m: RegExpExecArray | null;
EMAIL_RE.lastIndex = 0;
while ((m = EMAIL_RE.exec(html)) !== null) {
const e = (m[1] + '@' + m[2]).toLowerCase();
if (seenEmail.has(e)) continue;
seenEmail.add(e);
const role = classifyEmail(e);
if (role) emails.push({ email: e, role });
}
const seenLiC = new Set<string>();
const liCompany: string[] = [];
LI_COMPANY_RE.lastIndex = 0;
while ((m = LI_COMPANY_RE.exec(html)) !== null) {
const slug = m[1].toLowerCase();
if (slug === 'company' || slug === 'home' || slug === 'login') continue;
if (seenLiC.has(slug)) continue;
seenLiC.add(slug);
liCompany.push('https://www.linkedin.com/company/' + slug);
}
const seenLiP = new Set<string>();
const liPerson: string[] = [];
LI_PERSON_RE.lastIndex = 0;
while ((m = LI_PERSON_RE.exec(html)) !== null) {
const slug = m[1].toLowerCase();
if (slug === 'me' || slug === 'login' || slug === 'unsubscribe') continue;
if (seenLiP.has(slug)) continue;
seenLiP.add(slug);
liPerson.push('https://www.linkedin.com/in/' + slug);
}
// Instagram
const seenIG = new Set<string>();
const ig: Found['instagram'] = [];
IG_RE.lastIndex = 0;
while ((m = IG_RE.exec(html)) !== null) {
const h = m[1].toLowerCase();
if (IG_NOT_HANDLE.has(h) || h.length < 2 || seenIG.has(h)) continue;
seenIG.add(h);
ig.push({ handle: '@' + h, url: 'https://www.instagram.com/' + h + '/' });
}
// Facebook
const seenFB = new Set<string>();
const fb: Found['facebook'] = [];
FB_RE.lastIndex = 0;
while ((m = FB_RE.exec(html)) !== null) {
const h = m[1];
const hl = h.toLowerCase();
if (FB_NOT_HANDLE.has(hl) || h.length < 2 || seenFB.has(hl) || h.endsWith('.php')) continue;
seenFB.add(hl);
fb.push({ handle: h, url: 'https://www.facebook.com/' + h + '/' });
}
// Twitter / X
const seenX = new Set<string>();
const tw: Found['twitter'] = [];
X_RE.lastIndex = 0;
while ((m = X_RE.exec(html)) !== null) {
const h = m[1];
const hl = h.toLowerCase();
if (X_NOT_HANDLE.has(hl) || h.length < 1 || seenX.has(hl)) continue;
seenX.add(hl);
tw.push({ handle: '@' + h, url: 'https://x.com/' + h });
}
// BlueSky
const seenBS = new Set<string>();
const bs: Found['bluesky'] = [];
BSKY_RE.lastIndex = 0;
while ((m = BSKY_RE.exec(html)) !== null) {
const h = m[1].toLowerCase();
if (seenBS.has(h)) continue;
seenBS.add(h);
bs.push({ handle: '@' + h, url: 'https://bsky.app/profile/' + h });
}
return { emails, linkedinCompany: liCompany, linkedinPerson: liPerson, instagram: ig, facebook: fb, twitter: tw, bluesky: bs };
}
async function insertContact(opts: {
businessId: number;
role: string;
email?: string;
linkedin?: string;
sourceUrl: string | null;
notes: string;
confidence: number;
}) {
await query(
`
INSERT INTO business_contacts (
business_id, role, person_name, person_email, person_linkedin,
source, source_url, confidence, notes
) VALUES ($1, $2, $3, $4, $5, 'site_scrape', $6, $7, $8)
ON CONFLICT DO NOTHING
`,
[
opts.businessId,
opts.role,
// person_name: use email local-part for personal emails (lets the side-panel show "John Smith" instead of just an email)
opts.role === 'email_personal' && opts.email
? opts.email.split('@')[0].replace(/[._]/g, ' ')
: null,
opts.email ?? null,
opts.linkedin ?? null,
opts.sourceUrl,
opts.confidence,
opts.notes,
]
);
}
async function main() {
const queue = await pickQueue();
console.log(`[scrape-contacts] mining ${queue.length} HTML files…`);
let stats = { emails: 0, li_company: 0, li_person: 0, businesses_with_finds: 0 };
for (const row of queue) {
if (!row.raw_html_path) continue;
// raw_html_path is stored as 'raw/firm-NNN.html' but actual location is data/raw/.
const rel = row.raw_html_path.startsWith('data/') ? row.raw_html_path : 'data/' + row.raw_html_path;
const path = join(process.cwd(), rel);
let html: string;
try {
html = readFileSync(path, 'utf8');
} catch {
continue;
}
const found = extractFromHtml(html);
if (!found.emails.length && !found.linkedinCompany.length && !found.linkedinPerson.length
&& !found.instagram.length && !found.facebook.length && !found.twitter.length && !found.bluesky.length) continue;
stats.businesses_with_finds++;
for (const e of found.emails) {
await insertContact({
businessId: row.business_id,
role: e.role,
email: e.email,
sourceUrl: row.fetched_url,
notes: 'extracted from front-page HTML',
confidence: e.role === 'email_general' ? 0.9 : 0.75,
});
stats.emails++;
}
for (const url of found.linkedinCompany) {
await insertContact({
businessId: row.business_id,
role: 'linkedin_company',
linkedin: url,
sourceUrl: row.fetched_url,
notes: 'linkedin company page linked from front-page',
confidence: 0.9,
});
stats.li_company++;
}
for (const url of found.linkedinPerson) {
await insertContact({
businessId: row.business_id,
role: 'linkedin_person',
linkedin: url,
sourceUrl: row.fetched_url,
notes: 'linkedin profile linked from front-page',
confidence: 0.7,
});
stats.li_person++;
}
for (const ig of found.instagram) {
await query(
`INSERT INTO business_contacts (business_id, role, person_name, source, source_url, confidence, notes)
VALUES ($1, 'social_handle', $2, 'site_scrape', $3, 0.85, 'instagram')
ON CONFLICT DO NOTHING`,
[row.business_id, ig.handle, ig.url]
);
(stats as any).instagram = ((stats as any).instagram || 0) + 1;
}
for (const f of found.facebook) {
await query(
`INSERT INTO business_contacts (business_id, role, person_name, source, source_url, confidence, notes)
VALUES ($1, 'social_handle', $2, 'site_scrape', $3, 0.85, 'facebook')
ON CONFLICT DO NOTHING`,
[row.business_id, f.handle, f.url]
);
(stats as any).facebook = ((stats as any).facebook || 0) + 1;
}
for (const t of found.twitter) {
await query(
`INSERT INTO business_contacts (business_id, role, person_name, source, source_url, confidence, notes)
VALUES ($1, 'social_handle', $2, 'site_scrape', $3, 0.85, 'twitter')
ON CONFLICT DO NOTHING`,
[row.business_id, t.handle, t.url]
);
(stats as any).twitter = ((stats as any).twitter || 0) + 1;
}
for (const b of found.bluesky) {
await query(
`INSERT INTO business_contacts (business_id, role, person_name, source, source_url, confidence, notes)
VALUES ($1, 'social_handle', $2, 'site_scrape', $3, 0.85, 'bluesky')
ON CONFLICT DO NOTHING`,
[row.business_id, b.handle, b.url]
);
(stats as any).bluesky = ((stats as any).bluesky || 0) + 1;
}
}
console.log(`[scrape-contacts] done · ${JSON.stringify(stats)}`);
await pool.end();
}
main().catch((e) => {
console.error('[scrape-contacts]', e);
process.exit(1);
});