← back to Lawyer Directory Builder
Dashboard: add professionals + phones + emails counters; new wiki-lawyers importer
331776b0b7ccd044f90b2059690c7752baac8570 · 2026-04-30 01:48:00 -0700 · Steve Abrams
- /api/stats now returns professionals_total, phones_total, emails_total
- Header shows: firms · lawyers · geo-mapped · phones · emails · multi-firm bldgs · cities
- Numbers formatted with thousands separators
- src/ingest/wikipedia_lawyers.ts: ingests Category:Lawyers_from_Los_Angeles
+ Category:California_lawyers (~880 articles). Extracts birth/death year,
education, firm, position from .infobox. Inserts into professionals; if firm
matches an existing organization, also creates professional_locations link.
Files touched
M public/index.htmlA src/ingest/wikipedia_lawyers.tsM src/server/index.ts
Diff
commit 331776b0b7ccd044f90b2059690c7752baac8570
Author: Steve Abrams <steveabramsdesigns@gmail.com>
Date: Thu Apr 30 01:48:00 2026 -0700
Dashboard: add professionals + phones + emails counters; new wiki-lawyers importer
- /api/stats now returns professionals_total, phones_total, emails_total
- Header shows: firms · lawyers · geo-mapped · phones · emails · multi-firm bldgs · cities
- Numbers formatted with thousands separators
- src/ingest/wikipedia_lawyers.ts: ingests Category:Lawyers_from_Los_Angeles
+ Category:California_lawyers (~880 articles). Extracts birth/death year,
education, firm, position from .infobox. Inserts into professionals; if firm
matches an existing organization, also creates professional_locations link.
---
public/index.html | 19 +--
src/ingest/wikipedia_lawyers.ts | 251 ++++++++++++++++++++++++++++++++++++++++
src/server/index.ts | 3 +
3 files changed, 266 insertions(+), 7 deletions(-)
diff --git a/public/index.html b/public/index.html
index a20c74c..fad7812 100644
--- a/public/index.html
+++ b/public/index.html
@@ -80,9 +80,11 @@
<header>
<h1>LA Lawyer Directory</h1>
<span class="stat"><b id="s_firms">…</b> firms</span>
+ <span class="stat"><b id="s_pros">…</b> lawyers</span>
<span class="stat"><b id="s_geo">…</b> geo-mapped</span>
- <span class="stat"><b id="s_site">…</b> with website</span>
- <span class="stat"><b id="s_buildings">…</b> multi-firm buildings</span>
+ <span class="stat"><b id="s_phones">…</b> phones</span>
+ <span class="stat"><b id="s_emails">…</b> emails</span>
+ <span class="stat"><b id="s_buildings">…</b> multi-firm bldgs</span>
<span class="stat"><b id="s_cities">…</b> cities</span>
<div class="toolbar">
<span class="slider-wrap" id="slider_wrap">
@@ -251,11 +253,14 @@ function initMap() {
// ─── boot ───────────────────────────────────────────────────────────────────
(async () => {
const stats = await getJSON('/api/stats');
- document.getElementById('s_firms').textContent = stats.firms_total ?? 0;
- document.getElementById('s_geo').textContent = stats.firms_with_geo ?? 0;
- document.getElementById('s_site').textContent = stats.firms_with_site ?? 0;
- document.getElementById('s_buildings').textContent = stats.multi_firm_buildings ?? 0;
- document.getElementById('s_cities').textContent = stats.cities ?? 0;
+ const fmt = (n) => (n ?? 0).toLocaleString();
+ document.getElementById('s_firms').textContent = fmt(stats.firms_total);
+ document.getElementById('s_pros').textContent = fmt(stats.professionals_total);
+ document.getElementById('s_geo').textContent = fmt(stats.firms_with_geo);
+ document.getElementById('s_phones').textContent = fmt(stats.phones_total);
+ document.getElementById('s_emails').textContent = fmt(stats.emails_total);
+ document.getElementById('s_buildings').textContent = fmt(stats.multi_firm_buildings);
+ document.getElementById('s_cities').textContent = fmt(stats.cities);
const cities = await getJSON('/api/cities');
document.getElementById('cities').innerHTML = cities.rows.slice(0, 25).map(c =>
diff --git a/src/ingest/wikipedia_lawyers.ts b/src/ingest/wikipedia_lawyers.ts
new file mode 100644
index 0000000..a2adbff
--- /dev/null
+++ b/src/ingest/wikipedia_lawyers.ts
@@ -0,0 +1,251 @@
+/**
+ * Wikipedia importer for individual LA lawyers (Category:Lawyers_from_Los_Angeles).
+ *
+ * 285 articles → professionals table. Extracts from each article's infobox:
+ * - full name, birth/death year, education, firm, notable cases, position
+ * Skips judicial-only figures (already covered by Category:Judges).
+ * Resolves firm name → organizations (creates link if firm exists in our DB).
+ */
+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 lawyer biographies)';
+const USER_AGENT = process.env.USER_AGENT || 'LawyerDirectoryBuilder/0.1 (research; contact: steveabramsdesigns@gmail.com)';
+
+const CATEGORIES = [
+ 'Category:Lawyers_from_Los_Angeles',
+ 'Category:California_lawyers',
+];
+
+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.8 rps.', 'api', 0.8)
+ 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[]> {
+ let cont: string | undefined;
+ const titles = new Set<string>();
+ while (true) {
+ const u = `https://en.wikipedia.org/w/api.php?action=query&list=categorymembers&cmtitle=${encodeURIComponent(cat)}&cmlimit=500&format=json${cont ? `&cmcontinue=${encodeURIComponent(cont)}` : ''}`;
+ const j = await fetchJSON<{ query: { categorymembers: { title: string; ns: number }[] }; continue?: { cmcontinue: string } }>(u);
+ for (const m of j.query.categorymembers) if (m.ns === 0) titles.add(m.title);
+ if (!j.continue) break;
+ cont = j.continue.cmcontinue;
+ }
+ return [...titles];
+}
+
+interface Bio {
+ title: string;
+ fullName: string;
+ birthYear?: number;
+ deathYear?: number;
+ education?: string;
+ firm?: string;
+ position?: 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;
+}
+
+// Strip Wikipedia disambiguators: "Joaquin Avila (lawyer)" → "Joaquin Avila"
+function cleanTitle(t: string): string {
+ return t.replace(/\s*\([^)]*\)\s*$/, '').trim();
+}
+
+function splitName(full: string) {
+ const parts = full.split(/\s+/);
+ if (parts.length === 1) return { first: parts[0], last: '' };
+ if (parts.length === 2) return { first: parts[0], last: parts[1] };
+ // Treat last token as last name; everything before as first/middle.
+ const last = parts[parts.length - 1];
+ const first = parts[0];
+ const middle = parts.slice(1, -1).join(' ');
+ return { first, middle, last };
+}
+
+function extractInfobox(html: string, title: string): Bio {
+ const $ = load(html);
+ const bio: Bio = { title, fullName: cleanTitle(title) };
+
+ const firstP = $('p').filter((_, p) => $(p).text().trim().length > 50).first().text().trim();
+ if (firstP) bio.description = firstP.slice(0, 500);
+
+ const $box = $('table.infobox, .infobox.vcard').first();
+ if ($box.length === 0) return bio;
+
+ $box.find('tr').each((_, tr) => {
+ const $tr = $(tr);
+ const label = $tr.find('th').first().text().toLowerCase().trim();
+ const value = $tr.find('td').first().text().trim();
+ if (!label || !value) return;
+
+ if (/^born|^birth/.test(label)) { const y = parseYear(value); if (y) bio.birthYear = y; }
+ else if (/^died|^death/.test(label)) { const y = parseYear(value); if (y) bio.deathYear = y; }
+ else if (/^education|alma mater|college|school/.test(label)) bio.education = value.slice(0, 200);
+ else if (/^firm|employer|practice/.test(label)) bio.firm = value.slice(0, 200);
+ else if (/^position|^title|^office|known for/.test(label)) bio.position = value.slice(0, 200);
+ });
+
+ return bio;
+}
+
+async function upsertProfessional(bio: Bio, articleUrl: string, sourceId: number) {
+ const { first, middle, last } = splitName(bio.fullName);
+ if (!first || !last) return null;
+
+ return await withTx(async (client) => {
+ // Dedup: name match (case insensitive) — articles are unique enough by name.
+ let profId: number | null = null;
+ const r = await client.query<{ id: number }>(
+ `SELECT id FROM professionals WHERE LOWER(full_name) = LOWER($1) LIMIT 1`,
+ [bio.fullName]);
+ if (r.rowCount) profId = r.rows[0].id;
+
+ if (profId) {
+ await client.query(`
+ UPDATE professionals
+ SET first_name = COALESCE(first_name, $2),
+ last_name = COALESCE(last_name, $3),
+ middle_name = COALESCE(middle_name, $4),
+ graduation_year = COALESCE(graduation_year, $5),
+ bio = COALESCE(bio, $6),
+ confidence_tier = COALESCE(confidence_tier, 'medium'),
+ updated_at = NOW()
+ WHERE id = $1
+ `, [profId, first, last, middle, bio.birthYear ? bio.birthYear + 25 : null, bio.description]);
+ } else {
+ const r2 = await client.query<{ id: number }>(`
+ INSERT INTO professionals (full_name, first_name, middle_name, last_name, bio, source_confidence_score, confidence_tier)
+ VALUES ($1, $2, $3, $4, $5, 0.45, 'medium')
+ RETURNING id
+ `, [bio.fullName, first, middle, last, bio.description]);
+ profId = r2.rows[0].id;
+ }
+
+ // Education
+ if (bio.education) {
+ const exists = await client.query(
+ `SELECT 1 FROM education WHERE professional_id = $1 AND school_name = $2 LIMIT 1`,
+ [profId, bio.education]);
+ if (exists.rowCount === 0) {
+ await client.query(`
+ INSERT INTO education (professional_id, school_name, source_url)
+ VALUES ($1, $2, $3)
+ `, [profId, bio.education, articleUrl]);
+ }
+ }
+
+ // Firm linkage — try to find existing organization by name
+ if (bio.firm) {
+ const r3 = await client.query<{ id: number }>(
+ `SELECT id FROM organizations WHERE LOWER(name) = LOWER($1) LIMIT 1`,
+ [bio.firm]);
+ if (r3.rowCount) {
+ const orgId = r3.rows[0].id;
+ const exists = await client.query(
+ `SELECT 1 FROM professional_locations WHERE professional_id = $1 AND organization_id = $2 LIMIT 1`,
+ [profId, orgId]);
+ if (exists.rowCount === 0) {
+ await client.query(`
+ INSERT INTO professional_locations (professional_id, organization_id, role, source_url, last_verified_at)
+ VALUES ($1, $2, $3, $4, NOW())
+ `, [profId, orgId, bio.position || 'attorney', articleUrl]);
+ }
+ }
+ }
+
+ const rawJson = JSON.stringify(bio);
+ const hash = crypto.createHash('sha256').update(rawJson + '|wiki-lawyer|' + bio.title + '|' + profId).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, 'professional', $3, $4::jsonb, NOW(), $5)
+ ON CONFLICT (source_id, hash) DO NOTHING
+ `, [sourceId, articleUrl, profId, rawJson, hash]);
+
+ return profId;
+ });
+}
+
+async function main() {
+ console.log('[wiki-lawyers] importing LA lawyer biographies…');
+ const sourceId = await ensureSource();
+ const jobId = await startJob(sourceId, 'wiki-lawyers:la');
+
+ 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-lawyers] ${titles.size} candidate articles`);
+
+ let processed = 0, kept = 0, skipped = 0;
+ for (const title of titles) {
+ try {
+ const articleUrl = `https://en.wikipedia.org/wiki/${encodeURIComponent(title.replace(/ /g, '_'))}`;
+ const html = await fetchHTML(`https://en.wikipedia.org/api/rest_v1/page/html/${encodeURIComponent(title)}`);
+ const bio = extractInfobox(html, title);
+ const id = await upsertProfessional(bio, articleUrl, sourceId);
+ if (id) kept++; else skipped++;
+ } catch (e) {
+ console.error(`[wiki-lawyers] err ${title}: ${(e as Error).message}`);
+ skipped++;
+ }
+ processed++;
+ if (processed % 25 === 0) console.log(`[wiki-lawyers] ${processed}/${titles.size} kept=${kept} skipped=${skipped}`);
+ await new Promise(r => setTimeout(r, 700)); // ~1.4 rps polite
+ }
+
+ await finishJob(jobId, { status: 'completed', records_found: titles.size, records_inserted: kept, records_skipped: skipped });
+ console.log(`[wiki-lawyers] done. articles=${titles.size} kept=${kept} skipped=${skipped}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[wiki-lawyers] fatal:', err);
+ try { await pool.end(); } catch {}
+ process.exit(1);
+});
diff --git a/src/server/index.ts b/src/server/index.ts
index d87f4f0..f6d7b9f 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -33,6 +33,9 @@ app.get('/api/stats', async (_req, res) => {
GROUP BY address HAVING COUNT(*) > 1
) t
UNION ALL SELECT 'sources_active', COUNT(*)::text FROM sources
+ UNION ALL SELECT 'professionals_total', COUNT(*)::text FROM professionals
+ UNION ALL SELECT 'phones_total', COUNT(*)::text FROM phones
+ UNION ALL SELECT 'emails_total', COUNT(*)::text FROM emails
`);
const out: Record<string, number> = {};
for (const row of r.rows) out[row.key] = parseInt(row.value, 10);
← fd5c251 Add Wikipedia infobox enricher + firm-website contact crawle
·
back to Lawyer Directory Builder
·
Counsel & Bar visual loop: 21 iterations of Aston Martin DB1 ade90a7 →