← back to Ventura Corridor
src/enrich/restaurant_owners.ts
303 lines
/**
* Restaurant owner finder — DuckDuckGo HTML search → LinkedIn first hit.
*
* Free, no key. Uses https://html.duckduckgo.com/html which returns plain HTML
* we can parse server-side. Polite throttle: 2.5s/req.
*
* Strategy per restaurant:
* 1. Query: "{name}" {city} owner site:linkedin.com
* 2. Pull the first organic result. If it's a /in/{slug}/ profile, extract
* person name + title from the snippet. If it's a /company/{slug} page,
* record the LinkedIn company URL (already useful) and try again with
* "owner OR founder OR chef" in the query.
* 3. UPSERT business_contacts row.
*
* Reality check: this is a coarse first pass. Some restaurants have no
* findable LinkedIn presence at all. Confidence stays moderate (0.55-0.75).
*
* Run:
* npm run enrich:restaurant-owners
* tsx src/enrich/restaurant_owners.ts -- --limit=10 # smoke test
*/
import 'dotenv/config';
import { pool, query } from '../../db/pool.ts';
const SLEEP_MS = 2500;
const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));
interface Row {
id: number;
name: string;
city: string | null;
category: string | null;
ownership_class: string | null;
}
function decodeEntities(s: string): string {
return s
.replace(/&/g, '&')
.replace(/"/g, '"')
.replace(/'/g, "'")
.replace(/'/g, "'")
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/ /g, ' ')
.replace(/'/g, "'")
.replace(///g, '/')
.replace(/&#?\w+;/g, '');
}
function stripTags(s: string): string {
return decodeEntities(s.replace(/<[^>]+>/g, '')).replace(/\s+/g, ' ').trim();
}
interface Hit {
url: string;
title: string;
snippet: string;
}
async function ddgSearch(q: string): Promise<Hit[]> {
const url = 'https://html.duckduckgo.com/html/?q=' + encodeURIComponent(q);
const r = await fetch(url, {
headers: {
'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 14_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36',
'Accept': 'text/html,application/xhtml+xml',
'Accept-Language': 'en-US,en;q=0.9',
},
});
if (!r.ok) throw new Error(`DDG ${r.status}`);
const html = await r.text();
// DDG HTML markup: each result is a div.result with
// <a class="result__a" href="...">title</a>
// <a class="result__url"...>display url</a>
// <a class="result__snippet" ...>snippet</a>
// The href is wrapped through a redirect (uddg=...) — extract the real URL.
const hits: Hit[] = [];
const re = /<a[^>]*class="result__a"[^>]*href="([^"]+)"[^>]*>([\s\S]*?)<\/a>[\s\S]*?<a[^>]*class="result__snippet"[^>]*>([\s\S]*?)<\/a>/g;
let m: RegExpExecArray | null;
while ((m = re.exec(html)) !== null) {
let href = m[1];
// DDG wraps real URLs as /l/?uddg=ENCODED
const u = href.match(/uddg=([^&]+)/);
if (u) {
try { href = decodeURIComponent(u[1]); } catch {}
}
hits.push({
url: href,
title: stripTags(m[2]),
snippet: stripTags(m[3]),
});
if (hits.length >= 10) break;
}
return hits;
}
function classifyLinkedinUrl(url: string): 'profile' | 'company' | 'school' | 'jobs' | 'other' {
if (!/linkedin\.com/i.test(url)) return 'other';
if (/linkedin\.com\/in\//i.test(url)) return 'profile';
if (/linkedin\.com\/company\//i.test(url)) return 'company';
if (/linkedin\.com\/school\//i.test(url)) return 'school';
if (/linkedin\.com\/jobs\//i.test(url)) return 'jobs';
return 'other';
}
// Pull a person name + role from a LinkedIn search-result title/snippet.
// LinkedIn format: "Person Name - Role at Company - LinkedIn"
function parsePersonHit(h: Hit): { name: string | null; title: string | null; role: string } {
const t = h.title;
// Common pattern: "Jane Doe - Owner at Pasta House - LinkedIn"
let name: string | null = null;
let title: string | null = null;
const m1 = t.match(/^([^-–|]+?)\s*[-–|]\s*([^-–|]+?)(?:\s*[-–|]\s*LinkedIn)?$/i);
if (m1) {
name = m1[1].trim();
title = m1[2].trim();
} else {
name = t.replace(/\s*[-–|]\s*LinkedIn\s*$/i, '').trim();
}
// Best-effort role classification.
const role = inferRole(title || h.snippet);
return { name, title, role };
}
function inferRole(s: string | null): string {
if (!s) return 'unknown';
const lc = s.toLowerCase();
if (/\bco-?owner\b/.test(lc)) return 'co-owner';
if (/\bowner\b/.test(lc)) return 'owner';
if (/\bfounder\b/.test(lc)) return 'founder';
if (/\bgeneral manager\b|\bgm\b/.test(lc)) return 'gm';
if (/\bexecutive chef\b|\bhead chef\b/.test(lc)) return 'chef';
if (/\bchef\b/.test(lc)) return 'chef';
if (/\bmanager\b/.test(lc)) return 'manager';
if (/\bfranchisee\b/.test(lc)) return 'franchisee';
if (/\bdirector\b/.test(lc)) return 'director';
if (/\bpresident\b|\bceo\b/.test(lc)) return 'ceo';
return 'unknown';
}
interface Found {
source: 'linkedin';
source_url: string;
source_snippet: string;
role: string;
person_name: string | null;
person_title: string | null;
person_linkedin: string;
confidence: number;
company_page?: string; // recorded separately if first hit was a company page
}
async function findOwnersFor(b: Row): Promise<Found[]> {
const queries = [
`"${b.name}" ${b.city || 'Los Angeles'} owner site:linkedin.com`,
`"${b.name}" ${b.city || 'Los Angeles'} chef OR founder site:linkedin.com`,
];
const found: Found[] = [];
let companyPage: string | null = null;
for (const q of queries) {
let hits: Hit[] = [];
try { hits = await ddgSearch(q); }
catch (e: any) { console.log(` [ddg] err on "${q.slice(0, 60)}": ${e.message}`); break; }
for (const h of hits.slice(0, 5)) {
const kind = classifyLinkedinUrl(h.url);
if (kind === 'company' && !companyPage) {
companyPage = h.url;
continue;
}
if (kind !== 'profile') continue;
const { name, title, role } = parsePersonHit(h);
if (!name) continue;
// Confidence: high if title clearly says owner/founder/chef, lower otherwise
const conf = (role === 'owner' || role === 'co-owner' || role === 'founder') ? 0.78
: (role === 'chef' || role === 'gm' || role === 'manager' || role === 'ceo') ? 0.62
: 0.45;
// Sanity check: snippet mentions the business name (case-insensitive).
const mentioned = (h.title + ' ' + h.snippet).toLowerCase().includes(b.name.toLowerCase().slice(0, Math.min(b.name.length, 18)));
if (!mentioned && conf < 0.6) continue;
found.push({
source: 'linkedin',
source_url: h.url,
source_snippet: (h.title + ' — ' + h.snippet).slice(0, 400),
role,
person_name: name,
person_title: title,
person_linkedin: h.url.split('?')[0],
confidence: mentioned ? conf : Math.max(0.3, conf - 0.15),
});
if (found.length >= 3) break;
}
if (found.length) break; // first query gave us hits, don't burn the second
await sleep(SLEEP_MS);
}
// If we found nothing personal but did spot a company page, record that
// separately (still useful — Steve can DM the company).
if (!found.length && companyPage) {
found.push({
source: 'linkedin',
source_url: companyPage,
source_snippet: `LinkedIn company page (no individual owner profile found in top results)`,
role: 'corporate_local_rep',
person_name: null,
person_title: null,
person_linkedin: companyPage,
confidence: 0.5,
company_page: companyPage,
});
}
return found;
}
async function main() {
const argLimit = process.argv.find((a) => a.startsWith('--limit='));
const limit = argLimit ? parseInt(argLimit.split('=')[1], 10) : 1000;
// Restaurants only per Steve's directive — but we'll cast wide on category
// ('amenity: restaurant', 'amenity: cafe', 'amenity: bar', 'amenity: fast_food').
const r = await query<Row>(`
SELECT b.id, b.name, b.city, b.category, e.ownership_class
FROM businesses b
LEFT JOIN business_enrichment e ON e.business_id = b.id
WHERE b.on_corridor
AND b.category ILIKE 'amenity:%'
AND (b.category ILIKE '%restaurant%' OR b.category ILIKE '%cafe%' OR b.category ILIKE '%bar%' OR b.category ILIKE '%fast_food%' OR b.category ILIKE '%pub%')
ORDER BY (e.ownership_class = 'independent') DESC NULLS LAST, b.id
LIMIT $1
`, [limit]);
console.log(`[owners] ${r.rowCount} restaurants to look up · throttle ${SLEEP_MS}ms/req`);
console.log(`[owners] estimated time: ${Math.ceil(r.rowCount * SLEEP_MS / 1000 / 60)} min`);
console.log('');
let tot = 0, withHit = 0, contactRows = 0, errs = 0;
for (const b of r.rows) {
tot++;
process.stdout.write(`[${tot}/${r.rowCount}] ${b.name.slice(0, 36).padEnd(36)} ${b.city || ''} → `);
try {
const found = await findOwnersFor(b);
if (found.length) {
withHit++;
for (const f of found) {
await query(`
INSERT INTO business_contacts (
business_id, role, person_name, person_title, person_linkedin,
source, source_url, source_snippet, confidence, last_verified_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW())
ON CONFLICT (business_id, source, dedupe_key) DO UPDATE SET
role = EXCLUDED.role,
person_title = EXCLUDED.person_title,
source_snippet = EXCLUDED.source_snippet,
confidence = GREATEST(business_contacts.confidence, EXCLUDED.confidence),
last_verified_at = NOW()
`, [
b.id, f.role, f.person_name, f.person_title, f.person_linkedin,
f.source, f.source_url, f.source_snippet, f.confidence,
]);
contactRows++;
}
const top = found[0];
console.log(`${top.role}: ${top.person_name || '(company page)'} conf=${top.confidence.toFixed(2)}`);
} else {
console.log('no LinkedIn hit');
}
} catch (e: any) {
errs++;
console.log(`ERR ${e.message.slice(0, 60)}`);
}
await sleep(SLEEP_MS);
}
console.log('');
console.log(`[owners] done · ${tot} searched · ${withHit} with LinkedIn hits · ${contactRows} contact rows inserted · ${errs} errors`);
// Sample summary
const sample = await query<{ name: string; person_name: string; role: string; person_linkedin: string }>(`
SELECT b.name, c.person_name, c.role, c.person_linkedin
FROM business_contacts c JOIN businesses b ON b.id = c.business_id
WHERE c.last_verified_at > NOW() - INTERVAL '5 minutes'
AND c.person_name IS NOT NULL
ORDER BY c.confidence DESC LIMIT 8
`);
console.log('');
console.log('[owners] top hits:');
for (const s of sample.rows) {
console.log(` ${s.name.slice(0, 30).padEnd(30)} ${(s.person_name || '').slice(0, 28).padEnd(28)} ${s.role.padEnd(10)} ${s.person_linkedin.slice(0, 60)}`);
}
await pool.end();
}
main().catch((e) => {
console.error('[owners]', e);
process.exit(1);
});