← back to Lawyer Directory Builder
Add Wikipedia infobox enricher + firm-website contact crawler
fd5c25140111cd7ce3f398004ce9320eb1642bf7 · 2026-04-30 01:12:22 -0700 · Steve Abrams
- src/ingest/wikipedia_infobox.ts: pulls Category:Law_firms_based_in_Los_Angeles
+ Category:Law_firms_based_in_California (28 articles), parses .infobox table
for headquarters, founded year, lawyers count, revenue, website. Updates
attorney_count + firm_size_band where missing.
- src/enrich/firm_website_contacts.ts: for the 154 firms with websites,
fetches homepage + likely contact pages (/contact, /about, /our-team, etc.),
regex-extracts emails + phones, inserts into emails/phones tables.
Robots-respected via fetchCompliant. 0.5 rps default.
- docs/blocked_sources.md: documented adjacent-city ArcGIS Hub portals
(Beverly Hills, Santa Monica, Pasadena, Long Beach) — all client-rendered
SPAs with no DCAT feed, blocked without per-portal investigation or Playwright.
Files touched
M docs/blocked_sources.mdA src/enrich/firm_website_contacts.tsA src/ingest/wikipedia_infobox.ts
Diff
commit fd5c25140111cd7ce3f398004ce9320eb1642bf7
Author: Steve Abrams <steveabramsdesigns@gmail.com>
Date: Thu Apr 30 01:12:22 2026 -0700
Add Wikipedia infobox enricher + firm-website contact crawler
- src/ingest/wikipedia_infobox.ts: pulls Category:Law_firms_based_in_Los_Angeles
+ Category:Law_firms_based_in_California (28 articles), parses .infobox table
for headquarters, founded year, lawyers count, revenue, website. Updates
attorney_count + firm_size_band where missing.
- src/enrich/firm_website_contacts.ts: for the 154 firms with websites,
fetches homepage + likely contact pages (/contact, /about, /our-team, etc.),
regex-extracts emails + phones, inserts into emails/phones tables.
Robots-respected via fetchCompliant. 0.5 rps default.
- docs/blocked_sources.md: documented adjacent-city ArcGIS Hub portals
(Beverly Hills, Santa Monica, Pasadena, Long Beach) — all client-rendered
SPAs with no DCAT feed, blocked without per-portal investigation or Playwright.
---
docs/blocked_sources.md | 25 ++++
src/enrich/firm_website_contacts.ts | 191 ++++++++++++++++++++++++++++
src/ingest/wikipedia_infobox.ts | 242 ++++++++++++++++++++++++++++++++++++
3 files changed, 458 insertions(+)
diff --git a/docs/blocked_sources.md b/docs/blocked_sources.md
index bd77ea3..75cf24c 100644
--- a/docs/blocked_sources.md
+++ b/docs/blocked_sources.md
@@ -36,3 +36,28 @@ run sticks with the sources that work cleanly via curl-grade requests:
- Wikidata SPARQL (already wired)
When ready to do Playwright pass, see `src/ingest/calbar_playwright.ts` (TODO).
+
+## Adjacent-city open-data portals
+
+LA County firm coverage would benefit from the same kind of "Active Business Licenses"
+dataset for cities outside LA City proper:
+
+- Beverly Hills (~35K pop) — `beverlyhills-cityofbh.opendata.arcgis.com`
+- Santa Monica (~92K) — `data-smgov.opendata.arcgis.com`
+- Pasadena (~140K) — `data-cityofpasadena.opendata.arcgis.com`
+- Long Beach (~462K) — `data-longbeach.opendata.arcgis.com`
+
+All four serve their portal as a client-rendered SPA with no public Socrata/JSON API
+and no DCAT feed at the standard `/api/feed/dcat-us/1.1` path. The dataset listings
+load via JavaScript that calls Esri's internal Hub API with auth tokens generated
+client-side.
+
+Path forward (not tonight):
+- per-portal investigation to find each city's "businesses" or "active business licenses"
+ dataset, then call its FeatureServer query endpoint directly:
+ `https://services.arcgis.com/<orgId>/ArcGIS/rest/services/<dataset>/FeatureServer/0/query?where=...`
+- OR a Playwright pass that visits each portal, extracts dataset links from the rendered
+ catalog, and discovers the FeatureServer URLs.
+
+Beverly Hills + Santa Monica are likely low-yield (small cities, may not even publish
+business license data). Long Beach + Pasadena are higher-value targets.
diff --git a/src/enrich/firm_website_contacts.ts b/src/enrich/firm_website_contacts.ts
new file mode 100644
index 0000000..54a2372
--- /dev/null
+++ b/src/enrich/firm_website_contacts.ts
@@ -0,0 +1,191 @@
+/**
+ * Firm-website contact-page enricher.
+ *
+ * For each org with a non-empty website:
+ * 1. fetch homepage (robots-respected, rate-limited)
+ * 2. find candidate contact pages (/contact, /contact-us, /about-us, /our-team, /people)
+ * 3. fetch each
+ * 4. regex-extract emails + phones
+ * 5. upsert into emails / phones with email_type='office', phone_type='office'
+ * 6. update organizations.phone if NULL
+ *
+ * Stays scoped to firms WITH websites (≈154 today). For firms without a website,
+ * no free + ToS-clean enrichment path exists tonight; document and defer.
+ */
+import 'dotenv/config';
+import { load } from 'cheerio';
+import { fetchCompliant } from '../lib/compliance.ts';
+import { pool, query } from '../db/pool.ts';
+
+const SOURCE_NAME = 'Firm website contact pages';
+
+const CONTACT_PATHS = ['/contact', '/contact-us', '/contact_us', '/contactus', '/about', '/about-us', '/our-team', '/team', '/attorneys', '/people', '/our-attorneys'];
+
+const EMAIL_RX = /[\w.+-]+@[\w-]+(?:\.[\w-]+)+/g;
+// Be conservative — accept (xxx) xxx-xxxx, xxx-xxx-xxxx, xxx.xxx.xxxx, +1 xxx-xxx-xxxx
+const PHONE_RX = /(?:\+?1[\s.-]?)?\(?(\d{3})\)?[\s.-]?(\d{3})[\s.-]?(\d{4})\b/g;
+
+const BAD_EMAIL_DOMAINS = ['example.com', 'sentry.io', 'godaddy.com', 'wixsite.com', 'wordpress.com', 'gmail.com', 'yahoo.com'];
+
+function normPhone(area: string, prefix: string, line: string) {
+ return `(${area}) ${prefix}-${line}`;
+}
+
+async function ensureSource(): Promise<number> {
+ await query(`
+ INSERT INTO sources (source_name, source_type, base_url, terms_notes, allowed_method, rate_limit_rps)
+ VALUES ($1, 'directory', '(per-firm websites)',
+ 'Per-firm website contact pages. Robots.txt respected per host. Polite-use 0.5 rps.',
+ 'crawl', 0.5)
+ ON CONFLICT (source_name) DO NOTHING
+ `, [SOURCE_NAME]);
+ const r = await query<{ id: number }>(`SELECT id FROM sources WHERE source_name = $1`, [SOURCE_NAME]);
+ return r.rows[0].id;
+}
+
+async function startJob(sourceId: number, label: string) {
+ const r = await query<{ id: number }>(`
+ INSERT INTO scrape_jobs (source_id, job_label, status, started_at)
+ VALUES ($1, $2, 'running', NOW()) RETURNING id
+ `, [sourceId, label]);
+ return r.rows[0].id;
+}
+
+async function finishJob(jobId: number, fields: Record<string, unknown>) {
+ const sets: string[] = [];
+ const params: unknown[] = [];
+ let i = 1;
+ for (const [k, v] of Object.entries(fields)) { sets.push(`${k} = $${i++}`); params.push(v); }
+ sets.push(`finished_at = NOW()`);
+ params.push(jobId);
+ await query(`UPDATE scrape_jobs SET ${sets.join(', ')} WHERE id = $${i}`, params);
+}
+
+function extractEmails(html: string): string[] {
+ const found = new Set<string>();
+ const matches = html.match(EMAIL_RX) || [];
+ for (const m of matches) {
+ const lower = m.toLowerCase();
+ const dom = lower.split('@')[1];
+ if (BAD_EMAIL_DOMAINS.some(b => dom.endsWith(b))) continue;
+ if (lower.includes('@sentry-next.wixpress.com')) continue;
+ if (/\.(png|jpg|gif|svg|webp)$/.test(lower)) continue;
+ found.add(lower);
+ }
+ return [...found];
+}
+
+function extractPhones(text: string): string[] {
+ const found = new Set<string>();
+ let m: RegExpExecArray | null;
+ while ((m = PHONE_RX.exec(text)) !== null) {
+ const area = m[1]; const prefix = m[2]; const line = m[3];
+ if (area === '000' || prefix === '000') continue;
+ if (area === '111' || area === '999') continue;
+ if (area[0] === '0' || area[0] === '1') continue; // not a real area code
+ found.add(normPhone(area, prefix, line));
+ }
+ return [...found];
+}
+
+async function enrichOne(org: { id: number; name: string; website: string }, sourceId: number): Promise<{ phones: number; emails: number }> {
+ const baseUrl = (() => {
+ try { return new URL(org.website); } catch { return null; }
+ })();
+ if (!baseUrl) return { phones: 0, emails: 0 };
+
+ // Build candidate URLs: homepage + N contact pages.
+ const candidates = [baseUrl.href, ...CONTACT_PATHS.map(p => new URL(p, baseUrl).href)];
+ const seenHtml = new Set<string>();
+ const allEmails = new Set<string>();
+ const allPhones = new Set<string>();
+
+ for (const url of candidates.slice(0, 3)) { // hard cap 3 fetches/firm
+ try {
+ const res = await fetchCompliant(url, { timeoutMs: 15000 });
+ if (!res || res.status >= 400) continue;
+ const html = await res.text();
+ if (seenHtml.has(html.slice(0, 4000))) continue;
+ seenHtml.add(html.slice(0, 4000));
+
+ const $ = load(html);
+ $('script, style, noscript').remove();
+ const body = $('body').text();
+
+ for (const e of extractEmails(html)) allEmails.add(e);
+ for (const p of extractPhones(body)) allPhones.add(p);
+ } catch (e) {
+ const code = (e as { code?: string }).code;
+ if (code === 'ROBOTS_DISALLOWED' || code === 'CAPTCHA_DETECTED') return { phones: 0, emails: 0 };
+ // transient error: try next candidate
+ }
+ }
+
+ let phoneCount = 0;
+ let emailCount = 0;
+
+ for (const phone of allPhones) {
+ const exists = await query(`SELECT 1 FROM phones WHERE organization_id=$1 AND phone=$2 LIMIT 1`, [org.id, phone]);
+ if (exists.rowCount === 0) {
+ await query(`INSERT INTO phones (organization_id, phone, phone_type, source_url, last_verified_at)
+ VALUES ($1,$2,'office',$3,NOW())`, [org.id, phone, org.website]);
+ phoneCount++;
+ }
+ }
+ if (allPhones.size > 0) {
+ // Update primary phone on org if NULL.
+ await query(`UPDATE organizations SET phone = COALESCE(phone, $2) WHERE id = $1`, [org.id, [...allPhones][0]]);
+ }
+ for (const email of allEmails) {
+ const exists = await query(`SELECT 1 FROM emails WHERE organization_id=$1 AND LOWER(email)=LOWER($2) LIMIT 1`, [org.id, email]);
+ if (exists.rowCount === 0) {
+ await query(`INSERT INTO emails (organization_id, email, email_type, source_url, last_verified_at)
+ VALUES ($1,$2,'office',$3,NOW())`, [org.id, email, org.website]);
+ emailCount++;
+ }
+ }
+
+ return { phones: phoneCount, emails: emailCount };
+}
+
+async function main() {
+ console.log('[enrich] firm-website contact crawl…');
+ const sourceId = await ensureSource();
+ const jobId = await startJob(sourceId, 'enrich:firm-website-contacts');
+
+ const r = await query<{ id: number; name: string; website: string }>(`
+ SELECT id, name, website FROM organizations
+ WHERE type='law_firm' AND website ~ '^https?://'
+ ORDER BY id
+ `);
+ const firms = r.rows;
+ console.log(`[enrich] ${firms.length} firms with websites`);
+
+ let phoneTot = 0, emailTot = 0, processed = 0, errors = 0;
+ for (const f of firms) {
+ try {
+ const { phones, emails } = await enrichOne(f, sourceId);
+ phoneTot += phones; emailTot += emails;
+ processed++;
+ if (processed % 25 === 0) console.log(`[enrich] ${processed}/${firms.length}: phones+=${phoneTot} emails+=${emailTot}`);
+ } catch (e) {
+ errors++;
+ console.error(`[enrich] err ${f.name}: ${(e as Error).message}`);
+ }
+ }
+
+ await finishJob(jobId, {
+ status: 'completed',
+ records_found: firms.length,
+ records_inserted: phoneTot + emailTot,
+ records_skipped: errors,
+ });
+ console.log(`[enrich] done. firms=${firms.length} phones+=${phoneTot} emails+=${emailTot} errors=${errors}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[enrich] fatal:', err);
+ try { await pool.end(); } catch {}
+ process.exit(1);
+});
diff --git a/src/ingest/wikipedia_infobox.ts b/src/ingest/wikipedia_infobox.ts
new file mode 100644
index 0000000..5062526
--- /dev/null
+++ b/src/ingest/wikipedia_infobox.ts
@@ -0,0 +1,242 @@
+/**
+ * Wikipedia infobox enricher for LA-based law firms.
+ *
+ * Sources:
+ * - Category:Law_firms_based_in_Los_Angeles (~21 articles)
+ * - Category:Law_firms_based_in_California (~13 articles)
+ * For each article, fetches REST HTML, parses .infobox table for:
+ * - Headquarters / Address
+ * - Founded
+ * - Lawyers / No. of lawyers / Attorneys
+ * - Revenue
+ * - Website
+ * Upserts into organizations (matching by name first) — fills in attorney_count,
+ * founded_year, website, address from infobox where missing.
+ */
+import 'dotenv/config';
+import crypto from 'node:crypto';
+import { fetch } from 'undici';
+import { load } from 'cheerio';
+import { pool, query, withTx } from '../db/pool.ts';
+
+const SOURCE_NAME = 'Wikipedia (LA County law firm articles)';
+const USER_AGENT = process.env.USER_AGENT || 'LawyerDirectoryBuilder/0.1 (research; contact: steveabramsdesigns@gmail.com)';
+
+const CATEGORIES = [
+ 'Category:Law_firms_based_in_Los_Angeles',
+ 'Category:Law_firms_based_in_California',
+];
+
+async function ensureSource(): Promise<number> {
+ await query(`
+ INSERT INTO sources (source_name, source_type, base_url, terms_notes, allowed_method, rate_limit_rps)
+ VALUES ($1, 'api', 'https://en.wikipedia.org/api/rest_v1/page/html/',
+ 'Wikipedia REST API. CC-BY-SA. Polite-use 0.5 rps.', 'api', 0.5)
+ ON CONFLICT (source_name) DO NOTHING
+ `, [SOURCE_NAME]);
+ const r = await query<{ id: number }>(`SELECT id FROM sources WHERE source_name = $1`, [SOURCE_NAME]);
+ return r.rows[0].id;
+}
+
+async function startJob(sourceId: number, label: string) {
+ const r = await query<{ id: number }>(`
+ INSERT INTO scrape_jobs (source_id, job_label, status, started_at)
+ VALUES ($1, $2, 'running', NOW()) RETURNING id
+ `, [sourceId, label]);
+ return r.rows[0].id;
+}
+
+async function finishJob(jobId: number, fields: Record<string, unknown>) {
+ const sets: string[] = [];
+ const params: unknown[] = [];
+ let i = 1;
+ for (const [k, v] of Object.entries(fields)) { sets.push(`${k} = $${i++}`); params.push(v); }
+ sets.push(`finished_at = NOW()`);
+ params.push(jobId);
+ await query(`UPDATE scrape_jobs SET ${sets.join(', ')} WHERE id = $${i}`, params);
+}
+
+async function fetchJSON<T>(url: string): Promise<T> {
+ const r = await fetch(url, {
+ headers: { 'User-Agent': USER_AGENT, Accept: 'application/json' },
+ signal: AbortSignal.timeout(30000),
+ });
+ if (!r.ok) throw new Error(`${r.status} ${url}`);
+ return await r.json() as T;
+}
+
+async function fetchHTML(url: string): Promise<string> {
+ const r = await fetch(url, {
+ headers: { 'User-Agent': USER_AGENT, Accept: 'text/html' },
+ signal: AbortSignal.timeout(30000),
+ });
+ if (!r.ok) throw new Error(`${r.status} ${url}`);
+ return await r.text();
+}
+
+async function listCategoryMembers(cat: string): Promise<string[]> {
+ const url = `https://en.wikipedia.org/w/api.php?action=query&list=categorymembers&cmtitle=${encodeURIComponent(cat)}&cmlimit=500&format=json`;
+ const j = await fetchJSON<{ query: { categorymembers: { title: string; ns: number }[] } }>(url);
+ return j.query.categorymembers.filter(m => m.ns === 0).map(m => m.title);
+}
+
+interface Infobox {
+ headquarters?: string;
+ founded?: number;
+ lawyers?: number;
+ revenue?: string;
+ website?: string;
+ description?: string;
+}
+
+function parseYear(s: string): number | null {
+ const m = s.match(/(\d{4})/);
+ if (!m) return null;
+ const y = parseInt(m[1], 10);
+ return y >= 1700 && y <= new Date().getFullYear() ? y : null;
+}
+
+function parseInteger(s: string): number | null {
+ // "Lawyers 3,300+" → 3300
+ const m = s.replace(/,/g, '').match(/(\d{2,6})/);
+ return m ? parseInt(m[1], 10) : null;
+}
+
+function extractInfobox(html: string): Infobox {
+ const $ = load(html);
+ const ib: Infobox = {};
+
+ // First paragraph as description
+ const firstP = $('p').filter((_, p) => $(p).text().trim().length > 50).first().text().trim();
+ if (firstP) ib.description = firstP.slice(0, 600);
+
+ // Find the table.infobox; many Wikipedia article HTMLs have a nested vcard
+ const $box = $('table.infobox, .infobox.vcard').first();
+ if ($box.length === 0) return ib;
+
+ $box.find('tr').each((_, tr) => {
+ const $tr = $(tr);
+ const label = $tr.find('th').text().toLowerCase().trim();
+ const value = $tr.find('td').text().trim();
+ if (!label || !value) return;
+
+ if (/^head\s?quarters/.test(label) || label === 'address') ib.headquarters = value;
+ else if (/founded|established/.test(label)) {
+ const y = parseYear(value); if (y) ib.founded = y;
+ }
+ else if (/lawyers|attorneys|no\. of (lawyers|attorneys)/.test(label)) {
+ const n = parseInteger(value); if (n) ib.lawyers = n;
+ }
+ else if (/^revenue/.test(label)) ib.revenue = value;
+ else if (/^website/.test(label)) {
+ const a = $tr.find('td a').first().attr('href');
+ if (a) ib.website = a;
+ }
+ });
+
+ return ib;
+}
+
+function bandFromCount(n: number | undefined): string | null {
+ if (!n) return null;
+ if (n >= 500) return 'biglaw';
+ if (n >= 100) return 'large';
+ if (n >= 25) return 'medium';
+ if (n >= 5) return 'small';
+ return 'solo';
+}
+
+async function upsertFromArticle(title: string, sourceId: number) {
+ const articleUrl = `https://en.wikipedia.org/wiki/${encodeURIComponent(title.replace(/ /g, '_'))}`;
+ const restUrl = `https://en.wikipedia.org/api/rest_v1/page/html/${encodeURIComponent(title)}`;
+ let html: string;
+ try { html = await fetchHTML(restUrl); }
+ catch (e) { console.error(`[wiki] fetch err ${title}: ${(e as Error).message}`); return null; }
+
+ const ib = extractInfobox(html);
+ const name = title; // article title is the firm name
+
+ return await withTx(async (client) => {
+ let orgId: number | null = null;
+ if (ib.website) {
+ const r = await client.query<{ id: number }>(
+ `SELECT id FROM organizations WHERE LOWER(website) = LOWER($1) LIMIT 1`, [ib.website]);
+ if (r.rowCount) orgId = r.rows[0].id;
+ }
+ if (!orgId) {
+ const r = await client.query<{ id: number }>(
+ `SELECT id FROM organizations WHERE LOWER(name) = LOWER($1) AND county = 'Los Angeles' LIMIT 1`, [name]);
+ if (r.rowCount) orgId = r.rows[0].id;
+ }
+
+ if (orgId) {
+ await client.query(`
+ UPDATE organizations
+ SET website = COALESCE(website, $2),
+ attorney_count = GREATEST(attorney_count, COALESCE($3, 0)),
+ firm_size_band = COALESCE($4, firm_size_band),
+ source_url = COALESCE(source_url, $5),
+ updated_at = NOW()
+ WHERE id = $1
+ `, [orgId, ib.website, ib.lawyers, bandFromCount(ib.lawyers), articleUrl]);
+ } else {
+ const r = await client.query<{ id: number }>(`
+ INSERT INTO organizations (
+ name, type, address, city, county, website,
+ attorney_count, firm_size_band, source_url
+ ) VALUES (
+ $1, 'law_firm', $2, 'Los Angeles', 'Los Angeles', $3,
+ COALESCE($4::int, 0), $5, $6
+ ) RETURNING id
+ `, [name, ib.headquarters, ib.website, ib.lawyers, bandFromCount(ib.lawyers), articleUrl]);
+ orgId = r.rows[0].id;
+ }
+
+ const rawJson = JSON.stringify({ title, infobox: ib, articleUrl });
+ const hash = crypto.createHash('sha256').update(rawJson + '|wiki|' + title + '|' + orgId).digest('hex');
+ await client.query(`
+ INSERT INTO raw_records (source_id, source_url, entity_type, entity_id, raw_json, fetched_at, hash)
+ VALUES ($1,$2,'organization',$3,$4::jsonb,NOW(),$5)
+ ON CONFLICT (source_id, hash) DO NOTHING
+ `, [sourceId, articleUrl, orgId, rawJson, hash]);
+
+ return orgId;
+ });
+}
+
+async function main() {
+ console.log('[wiki] enriching from Wikipedia LA-firm category articles…');
+ const sourceId = await ensureSource();
+ const jobId = await startJob(sourceId, 'wiki:la-firm-articles');
+
+ const titles = new Set<string>();
+ for (const cat of CATEGORIES) {
+ const ts = await listCategoryMembers(cat);
+ ts.forEach(t => titles.add(t));
+ await new Promise(r => setTimeout(r, 1000));
+ }
+ console.log(`[wiki] ${titles.size} candidate articles`);
+
+ let processed = 0, kept = 0, skipped = 0;
+ for (const title of titles) {
+ try {
+ const id = await upsertFromArticle(title, sourceId);
+ if (id) kept++; else skipped++;
+ } catch (e) {
+ console.error(`[wiki] err ${title}: ${(e as Error).message}`);
+ skipped++;
+ }
+ processed++;
+ await new Promise(r => setTimeout(r, 800)); // ~0.8 rps polite
+ }
+
+ await finishJob(jobId, { status: 'completed', records_found: titles.size, records_inserted: kept, records_skipped: skipped });
+ console.log(`[wiki] done. articles=${titles.size} kept=${kept} skipped=${skipped}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[wiki] fatal:', err);
+ try { await pool.end(); } catch {}
+ process.exit(1);
+});
← c99aeb7 Add OSM name-match importer + ghost-address cleanup
·
back to Lawyer Directory Builder
·
Dashboard: add professionals + phones + emails counters; new 331776b →