← back to Professional Directory
Initial commit — LA County professional directory (doctors)
eff0b9f7fc178e61fe34bf97be5ec1f6562bfd8d · 2026-04-30 00:41:56 -0700 · Steve Abrams
Compliance-first PostgreSQL data platform for licensed LA County
medical professionals. Five-agent layout (ingest, crawler, enrich,
dedupe, api) with NPI bulk + CDPH facility importers wired up.
Current state:
- 106,711 professionals (NPI bulk, LA County)
- 41,606 organizations (NPI + CDPH/HCAI: 81 hospitals, 289 surgery
centers, 1,206 clinics, ...)
- 6,563 geocoded orgs (lat/lng from CDPH)
- 37,803 prof_locations linked to organizations via address+phone
Files touched
A .env.exampleA .gitignoreA agents/api-agent/server.jsA agents/crawler-agent/crawlers/ucla-health.jsA agents/crawler-agent/framework/extractor.jsA agents/enrich-agent/geocode.jsA agents/ingest-agent/importers/__fixtures__/npi_smoke.csvA agents/ingest-agent/importers/cms-care-compare.jsA agents/ingest-agent/importers/cms-hospitals.jsA agents/ingest-agent/importers/dca.jsA agents/ingest-agent/importers/hcai.jsA agents/ingest-agent/importers/hrsa.jsA agents/ingest-agent/importers/mbc.jsA agents/ingest-agent/importers/npi.jsA agents/shared/compliance.jsA agents/shared/db.jsA agents/shared/la-zips.jsA agents/shared/package.jsonA db/la_zips.sqlA db/migrations/0001_init.sqlA db/migrations/0002_seed_sources.sqlA db/schema.sqlA docs/example-queries.sqlA ecosystem.config.jsA package-lock.jsonA package.jsonA scripts/crawl-hospitals.jsA scripts/dedupe.jsA scripts/download_npi_bulk.shA scripts/link_locations_to_orgs.jsA scripts/migrate.jsA scripts/run-all-night.shA scripts/seed_specialties.jsA scripts/stats.js
Diff
commit eff0b9f7fc178e61fe34bf97be5ec1f6562bfd8d
Author: Steve Abrams <steveabramsdesigns@gmail.com>
Date: Thu Apr 30 00:41:56 2026 -0700
Initial commit — LA County professional directory (doctors)
Compliance-first PostgreSQL data platform for licensed LA County
medical professionals. Five-agent layout (ingest, crawler, enrich,
dedupe, api) with NPI bulk + CDPH facility importers wired up.
Current state:
- 106,711 professionals (NPI bulk, LA County)
- 41,606 organizations (NPI + CDPH/HCAI: 81 hospitals, 289 surgery
centers, 1,206 clinics, ...)
- 6,563 geocoded orgs (lat/lng from CDPH)
- 37,803 prof_locations linked to organizations via address+phone
---
.env.example | 25 +
.gitignore | 11 +
agents/api-agent/server.js | 288 +++++
agents/crawler-agent/crawlers/ucla-health.js | 55 +
agents/crawler-agent/framework/extractor.js | 143 +++
agents/enrich-agent/geocode.js | 116 ++
.../importers/__fixtures__/npi_smoke.csv | 7 +
agents/ingest-agent/importers/cms-care-compare.js | 221 ++++
agents/ingest-agent/importers/cms-hospitals.js | 178 +++
agents/ingest-agent/importers/dca.js | 183 +++
agents/ingest-agent/importers/hcai.js | 249 ++++
agents/ingest-agent/importers/hrsa.js | 153 +++
agents/ingest-agent/importers/mbc.js | 248 ++++
agents/ingest-agent/importers/npi.js | 372 ++++++
agents/shared/compliance.js | 140 ++
agents/shared/db.js | 53 +
agents/shared/la-zips.js | 22 +
agents/shared/package.json | 12 +
db/la_zips.sql | 108 ++
db/migrations/0001_init.sql | 4 +
db/migrations/0002_seed_sources.sql | 141 ++
db/schema.sql | 188 +++
docs/example-queries.sql | 169 +++
ecosystem.config.js | 52 +
package-lock.json | 1354 ++++++++++++++++++++
package.json | 38 +
scripts/crawl-hospitals.js | 171 +++
scripts/dedupe.js | 114 ++
scripts/download_npi_bulk.sh | 31 +
scripts/link_locations_to_orgs.js | 93 ++
scripts/migrate.js | 53 +
scripts/run-all-night.sh | 65 +
scripts/seed_specialties.js | 84 ++
scripts/stats.js | 68 +
34 files changed, 5209 insertions(+)
diff --git a/.env.example b/.env.example
new file mode 100644
index 0000000..52f1116
--- /dev/null
+++ b/.env.example
@@ -0,0 +1,25 @@
+# Local dev defaults. Copy to .env and adjust as needed.
+DATABASE_URL=postgresql:///doctor_professional_directory?host=/tmp&user=stevestudio2
+
+# When deploying to a server with dw_admin TCP auth:
+# DATABASE_URL=postgresql://dw_admin:CHANGEME@127.0.0.1:5432/doctor_professional_directory
+
+# Agent ports
+PORT_INGEST=9870
+PORT_CRAWLER=9871
+PORT_ENRICH=9872
+PORT_DEDUPE=9873
+PORT_API=9874
+
+# Crawler defaults
+USER_AGENT=ProfessionalDirectoryBot/0.1 (research; contact: steveabramsdesigns@gmail.com)
+DEFAULT_RATE_LIMIT_RPS=0.5
+RAW_HTML_DIR=./raw_html
+
+# Auth (basic auth for agent endpoints; matches Norma pattern)
+AUTH_USERNAME=admin
+AUTH_PASSWORD=changeme
+
+# Zero-dollar build: leave Google keys blank. Schema reserves the columns.
+GOOGLE_PLACES_API_KEY=
+GOOGLE_GEOCODING_API_KEY=
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..0725d60
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,11 @@
+node_modules/
+.env
+.env.local
+raw_html/
+logs/
+*.log
+.DS_Store
+dist/
+exports/
+data/
+tmp/
diff --git a/agents/api-agent/server.js b/agents/api-agent/server.js
new file mode 100644
index 0000000..f4fa4ee
--- /dev/null
+++ b/agents/api-agent/server.js
@@ -0,0 +1,288 @@
+#!/usr/bin/env node
+/**
+ * Read API + tiny admin dashboard for the Professional Directory.
+ *
+ * GET / — minimal HTML dashboard
+ * GET /health — { ok: true, counts: {...} }
+ * GET /professionals — filters: zip, city, specialty,
+ * license_status, min_confidence,
+ * limit (≤500), offset
+ * GET /professionals/:npi
+ * GET /organizations — filters: type, city, zip
+ * GET /organizations/:id
+ * GET /search?q= — trigram across professionals + orgs
+ * GET /website-data/:npi — JSON shaped for SEO templates
+ * GET /export.csv?entity=professionals&zip=…
+ * POST /opt-out — body: { type, id } → flips opted_out
+ *
+ * No auth in dev. For local-only use; bind to 127.0.0.1.
+ */
+const path = require('path');
+const fs = require('fs');
+require('dotenv').config({ path: path.resolve(__dirname, '../../.env') });
+const express = require('express');
+const { stringify } = require('csv-stringify');
+const { pool, query } = require('../shared/db');
+
+const PORT = Number(process.env.PORT_API || 9874);
+const app = express();
+app.use(express.json());
+
+const OPT_OUT = ' AND opted_out = false ';
+
+// ─── Dashboard ──────────────────────────────────────────────────────────────
+app.get('/', async (_req, res) => {
+ const counts = await getCounts();
+ res.setHeader('Content-Type','text/html; charset=utf-8');
+ res.end(renderDashboard(counts));
+});
+
+app.get('/health', async (_req, res) => {
+ res.json({ ok: true, counts: await getCounts() });
+});
+
+// ─── Professionals ──────────────────────────────────────────────────────────
+app.get('/professionals', async (req, res, next) => {
+ try {
+ const { zip, city, specialty, license_status, min_confidence } = req.query;
+ const limit = Math.min(Number(req.query.limit) || 100, 500);
+ const offset = Number(req.query.offset) || 0;
+
+ const where = [`p.opted_out = false`];
+ const params = [];
+ let i = 1;
+ if (zip) { where.push(`(pl.address ILIKE '%' || $${i} || '%' OR o.zip = $${i})`); params.push(zip); i++; }
+ if (city) { where.push(`o.city ILIKE $${i}`); params.push(city); i++; }
+ if (specialty) { where.push(`s.name ILIKE '%' || $${i} || '%'`); params.push(specialty); i++; }
+ if (license_status) { where.push(`p.license_status = $${i}`); params.push(license_status); i++; }
+ if (min_confidence) { where.push(`p.source_confidence_score >= $${i}`); params.push(Number(min_confidence)); i++; }
+
+ const sql = `
+ SELECT DISTINCT p.id, p.full_name, p.title, p.npi_number, p.license_number,
+ p.license_status, p.primary_specialty, p.medical_school, p.graduation_year,
+ p.source_confidence_score
+ FROM professionals p
+ LEFT JOIN professional_locations pl ON pl.professional_id = p.id
+ LEFT JOIN organizations o ON o.id = pl.organization_id
+ LEFT JOIN professional_specialties ps ON ps.professional_id = p.id
+ LEFT JOIN specialties s ON s.id = ps.specialty_id
+ WHERE ${where.join(' AND ')}
+ ORDER BY p.source_confidence_score DESC NULLS LAST, p.full_name
+ LIMIT ${limit} OFFSET ${offset}
+ `;
+ const rows = (await query(sql, params)).rows;
+ res.json({ count: rows.length, rows });
+ } catch (e) { next(e); }
+});
+
+app.get('/professionals/:npi', async (req, res, next) => {
+ try {
+ const r = await query(`
+ SELECT p.*, json_agg(DISTINCT jsonb_build_object(
+ 'organization_id', pl.organization_id,
+ 'role', pl.role,
+ 'address', pl.address,
+ 'phone', pl.phone,
+ 'source_url', pl.source_url
+ )) FILTER (WHERE pl.id IS NOT NULL) AS locations,
+ json_agg(DISTINCT jsonb_build_object(
+ 'specialty', s.name,
+ 'taxonomy_code', s.taxonomy_code,
+ 'confidence', ps.confidence_score
+ )) FILTER (WHERE s.id IS NOT NULL) AS specialties
+ FROM professionals p
+ LEFT JOIN professional_locations pl ON pl.professional_id = p.id
+ LEFT JOIN professional_specialties ps ON ps.professional_id = p.id
+ LEFT JOIN specialties s ON s.id = ps.specialty_id
+ WHERE p.npi_number = $1 ${OPT_OUT}
+ GROUP BY p.id
+ `, [req.params.npi]);
+ if (!r.rowCount) return res.status(404).json({ error: 'not found' });
+ res.json(r.rows[0]);
+ } catch (e) { next(e); }
+});
+
+// ─── Organizations ──────────────────────────────────────────────────────────
+app.get('/organizations', async (req, res, next) => {
+ try {
+ const { type, city, zip } = req.query;
+ const limit = Math.min(Number(req.query.limit) || 100, 500);
+ const offset = Number(req.query.offset) || 0;
+ const where = [`opted_out = false`];
+ const params = []; let i = 1;
+ if (type) { where.push(`type = $${i}`); params.push(type); i++; }
+ if (city) { where.push(`city ILIKE $${i}`); params.push(city); i++; }
+ if (zip) { where.push(`zip = $${i}`); params.push(zip); i++; }
+ const sql = `
+ SELECT id, name, type, address, city, state, zip, phone, website,
+ lat, lng, hcai_id, cdph_license
+ FROM organizations
+ WHERE ${where.join(' AND ')}
+ ORDER BY name
+ LIMIT ${limit} OFFSET ${offset}
+ `;
+ const rows = (await query(sql, params)).rows;
+ res.json({ count: rows.length, rows });
+ } catch (e) { next(e); }
+});
+
+app.get('/organizations/:id', async (req, res, next) => {
+ try {
+ const r = await query(
+ `SELECT * FROM organizations WHERE id = $1 ${OPT_OUT}`,
+ [req.params.id]
+ );
+ if (!r.rowCount) return res.status(404).json({ error: 'not found' });
+ res.json(r.rows[0]);
+ } catch (e) { next(e); }
+});
+
+// ─── Search ────────────────────────────────────────────────────────────────
+app.get('/search', async (req, res, next) => {
+ try {
+ const q = String(req.query.q || '').trim();
+ if (q.length < 2) return res.json({ count: 0, rows: [] });
+ const limit = Math.min(Number(req.query.limit) || 25, 100);
+
+ const r = await query(`
+ (SELECT 'professional' AS kind, p.id, p.full_name AS name, p.primary_specialty AS detail,
+ p.npi_number, similarity(p.full_name, $1) AS score
+ FROM professionals p
+ WHERE p.opted_out = false AND p.full_name ILIKE '%' || $1 || '%')
+ UNION ALL
+ (SELECT 'organization' AS kind, o.id, o.name, o.type AS detail,
+ o.npi_number, similarity(o.name, $1) AS score
+ FROM organizations o
+ WHERE o.opted_out = false AND o.name ILIKE '%' || $1 || '%')
+ ORDER BY score DESC NULLS LAST
+ LIMIT ${limit}
+ `, [q]);
+ res.json({ count: r.rowCount, rows: r.rows });
+ } catch (e) { next(e); }
+});
+
+// ─── Website-data helper ────────────────────────────────────────────────────
+app.get('/website-data/:npi', async (req, res, next) => {
+ try {
+ const r = await query(`
+ SELECT p.full_name, p.title, p.primary_specialty, p.medical_school,
+ p.graduation_year, p.bio, p.profile_image_url,
+ p.license_number, p.license_status,
+ json_agg(DISTINCT jsonb_build_object(
+ 'org', o.name, 'address', pl.address, 'phone', pl.phone, 'website', o.website
+ )) FILTER (WHERE pl.id IS NOT NULL) AS locations
+ FROM professionals p
+ LEFT JOIN professional_locations pl ON pl.professional_id = p.id
+ LEFT JOIN organizations o ON o.id = pl.organization_id
+ WHERE p.npi_number = $1 AND p.opted_out = false
+ GROUP BY p.id
+ `, [req.params.npi]);
+ if (!r.rowCount) return res.status(404).json({ error: 'not found' });
+ res.json(r.rows[0]);
+ } catch (e) { next(e); }
+});
+
+// ─── CSV export ─────────────────────────────────────────────────────────────
+app.get('/export.csv', async (req, res, next) => {
+ try {
+ const entity = (req.query.entity || 'professionals').toString();
+ let sql, cols;
+ if (entity === 'organizations') {
+ cols = ['id','name','type','address','city','state','zip','phone','website','lat','lng'];
+ sql = `SELECT ${cols.join(',')} FROM organizations WHERE opted_out=false ORDER BY name`;
+ } else {
+ cols = ['id','full_name','title','npi_number','license_number','license_status',
+ 'primary_specialty','medical_school','graduation_year','source_confidence_score'];
+ sql = `SELECT ${cols.join(',')} FROM professionals WHERE opted_out=false ORDER BY full_name`;
+ }
+ const r = await query(sql, []);
+ res.setHeader('Content-Type','text/csv; charset=utf-8');
+ res.setHeader('Content-Disposition',`attachment; filename="${entity}.csv"`);
+ const stringifier = stringify({ header: true, columns: cols });
+ stringifier.pipe(res);
+ for (const row of r.rows) stringifier.write(row);
+ stringifier.end();
+ } catch (e) { next(e); }
+});
+
+// ─── Opt-out ────────────────────────────────────────────────────────────────
+app.post('/opt-out', async (req, res, next) => {
+ try {
+ const { type, id } = req.body || {};
+ if (!['professional','organization'].includes(type)) {
+ return res.status(400).json({ error: 'type must be professional|organization' });
+ }
+ const table = type === 'professional' ? 'professionals' : 'organizations';
+ const r = await query(`
+ UPDATE ${table} SET opted_out = TRUE,
+ ${type === 'professional' ? 'opted_out_at = NOW(),' : ''}
+ updated_at = NOW()
+ WHERE id = $1 RETURNING id
+ `, [id]);
+ if (!r.rowCount) return res.status(404).json({ error: 'not found' });
+ res.json({ ok: true, id });
+ } catch (e) { next(e); }
+});
+
+// ─── Errors ─────────────────────────────────────────────────────────────────
+app.use((err, _req, res, _next) => {
+ console.error('[api]', err);
+ res.status(500).json({ error: err.message });
+});
+
+// ─── Helpers ────────────────────────────────────────────────────────────────
+async function getCounts() {
+ const r = await query(`
+ SELECT
+ (SELECT COUNT(*) FROM professionals WHERE opted_out=false) AS professionals,
+ (SELECT COUNT(*) FROM organizations WHERE opted_out=false) AS organizations,
+ (SELECT COUNT(*) FROM organizations WHERE type='hospital' AND opted_out=false) AS hospitals,
+ (SELECT COUNT(*) FROM la_zips) AS la_zips,
+ (SELECT COUNT(*) FROM specialties) AS specialties,
+ (SELECT COUNT(*) FROM raw_records) AS raw_records,
+ (SELECT MAX(updated_at) FROM professionals) AS last_update
+ `, []);
+ return r.rows[0];
+}
+
+function renderDashboard(c) {
+ return `<!doctype html><html><head><meta charset="utf-8">
+<title>Professional Directory · admin</title>
+<style>
+ body{font:14px/1.5 system-ui;-webkit-font-smoothing:antialiased;margin:0;background:#0c0f1a;color:#f0f0f5}
+ header{padding:32px;background:#131829;border-bottom:1px solid #2a3048}
+ h1{margin:0 0 6px 0;font-size:22px}
+ .grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(180px,1fr));gap:16px;padding:24px}
+ .card{background:#131829;border:1px solid #2a3048;border-radius:12px;padding:18px}
+ .num{font-size:28px;font-weight:600;color:#10b981}
+ .lbl{color:#a1a1b5;font-size:12px;text-transform:uppercase;letter-spacing:.5px;margin-top:6px}
+ pre{background:#1c2237;padding:12px;border-radius:8px;overflow:auto}
+ a{color:#10b981}
+ .links{padding:0 24px 32px}
+</style></head><body>
+<header><h1>Professional Directory</h1><div style="color:#a1a1b5">LA County · last updated ${c.last_update || '—'}</div></header>
+<div class="grid">
+ <div class="card"><div class="num">${c.professionals}</div><div class="lbl">Professionals</div></div>
+ <div class="card"><div class="num">${c.organizations}</div><div class="lbl">Organizations</div></div>
+ <div class="card"><div class="num">${c.hospitals}</div><div class="lbl">Hospitals</div></div>
+ <div class="card"><div class="num">${c.specialties}</div><div class="lbl">Specialties (NUCC)</div></div>
+ <div class="card"><div class="num">${c.la_zips}</div><div class="lbl">LA County ZIPs</div></div>
+ <div class="card"><div class="num">${c.raw_records}</div><div class="lbl">Raw records (provenance)</div></div>
+</div>
+<div class="links">
+ <h3>Quick links</h3>
+ <ul>
+ <li><a href="/professionals?limit=25">/professionals?limit=25</a></li>
+ <li><a href="/organizations?type=hospital">/organizations?type=hospital</a></li>
+ <li><a href="/organizations?city=Beverly%20Hills">/organizations?city=Beverly Hills</a></li>
+ <li><a href="/search?q=cardiology">/search?q=cardiology</a></li>
+ <li><a href="/export.csv?entity=professionals">/export.csv?entity=professionals</a></li>
+ <li><a href="/health">/health</a></li>
+ </ul>
+</div>
+</body></html>`;
+}
+
+app.listen(PORT, '127.0.0.1', () => {
+ console.log(`[api] listening on http://127.0.0.1:${PORT}`);
+});
diff --git a/agents/crawler-agent/crawlers/ucla-health.js b/agents/crawler-agent/crawlers/ucla-health.js
new file mode 100644
index 0000000..6e76ac6
--- /dev/null
+++ b/agents/crawler-agent/crawlers/ucla-health.js
@@ -0,0 +1,55 @@
+/**
+ * UCLA Health provider directory crawler — Stage 5.
+ *
+ * Public, robots-aware. Polite rate-limit (0.5 rps) via lib/compliance.
+ * Discovers provider profile URLs from the main directory and extracts each.
+ */
+const { fetchCompliant, setHostRateLimit } = require('../../shared/compliance');
+const { extractProviderCards, extractProviderProfile } = require('../framework/extractor');
+
+const SOURCE_NAME = 'UCLA Health (uclahealth.org)';
+const BASE = 'https://www.uclahealth.org';
+const ENTRY = `${BASE}/providers`;
+
+setHostRateLimit('www.uclahealth.org', 0.5);
+
+async function fetchHtml(url) {
+ const res = await fetchCompliant(url, { accept: 'text/html' });
+ if (!res.ok) throw new Error(`HTTP ${res.status} ${url}`);
+ return res.text();
+}
+
+async function* listProviderUrls() {
+ // The main directory is paginated; UCLA uses ?page= or ?p= depending on view.
+ for (let page = 1; page <= 200; page++) {
+ const url = `${ENTRY}?page=${page}`;
+ const html = await fetchHtml(url);
+ const cards = extractProviderCards(html, url);
+ if (cards.length === 0) return;
+ for (const c of cards) {
+ if (c.profile_url) yield c.profile_url;
+ }
+ }
+}
+
+async function crawl({ onProvider, maxPages = Infinity } = {}) {
+ let count = 0;
+ for await (const url of listProviderUrls()) {
+ if (count >= maxPages) break;
+ try {
+ const html = await fetchHtml(url);
+ const profile = extractProviderProfile(html, url);
+ profile.hospital_system = SOURCE_NAME;
+ await onProvider(profile);
+ } catch (e) {
+ if (e.code === 'CAPTCHA_DETECTED') {
+ console.error(`[ucla] CAPTCHA — aborting per compliance`); break;
+ }
+ console.error(`[ucla] error ${url}: ${e.message}`);
+ }
+ count++;
+ }
+ return count;
+}
+
+module.exports = { SOURCE_NAME, crawl };
diff --git a/agents/crawler-agent/framework/extractor.js b/agents/crawler-agent/framework/extractor.js
new file mode 100644
index 0000000..beb39c3
--- /dev/null
+++ b/agents/crawler-agent/framework/extractor.js
@@ -0,0 +1,143 @@
+/**
+ * Generic provider-page extractor utilities used by hospital crawlers.
+ * Pure parsing helpers — no I/O, no DB.
+ */
+const cheerio = require('cheerio');
+
+function textOf($el) {
+ return ($el.text() || '').replace(/\s+/g, ' ').trim() || null;
+}
+
+function attrFirst($el, ...names) {
+ for (const n of names) {
+ const v = $el.attr(n);
+ if (v) return v.trim();
+ }
+ return null;
+}
+
+/**
+ * Extract a flat list of doctor cards from a directory listing page.
+ * Tries common selectors first, then JSON-LD, then schema.org microdata.
+ * Returns an array of { name, specialty, location, phone, profile_url, image_url }.
+ */
+function extractProviderCards(html, baseUrl) {
+ const $ = cheerio.load(html);
+ const results = [];
+
+ // 1. JSON-LD blocks with @type Physician / Person.
+ $('script[type="application/ld+json"]').each((_, s) => {
+ try {
+ const j = JSON.parse($(s).text());
+ const arr = Array.isArray(j) ? j : [j];
+ for (const obj of arr) {
+ if (!obj) continue;
+ const t = (obj['@type'] || '').toLowerCase();
+ if (t === 'physician' || t === 'person' || t === 'medicalbusiness') {
+ results.push({
+ name: obj.name || obj.alternateName,
+ specialty: obj.medicalSpecialty || obj.knowsAbout,
+ phone: obj.telephone,
+ profile_url: obj.url ? new URL(obj.url, baseUrl).href : null,
+ image_url: obj.image,
+ address: obj.address && (obj.address.streetAddress || obj.address),
+ });
+ }
+ }
+ } catch (_) { /* ignore */ }
+ });
+
+ // 2. schema.org microdata.
+ $('[itemtype*="schema.org/Physician"], [itemtype*="schema.org/Person"]').each((_, el) => {
+ const $el = $(el);
+ const name = textOf($el.find('[itemprop="name"]').first()) || textOf($el.find('h2,h3,.name').first());
+ const specialty = textOf($el.find('[itemprop="medicalSpecialty"], .specialty').first());
+ const profile_url = $el.find('a[itemprop="url"], a').first().attr('href');
+ const image_url = $el.find('img[itemprop="image"], img').first().attr('src');
+ if (name) results.push({
+ name, specialty,
+ profile_url: profile_url ? new URL(profile_url, baseUrl).href : null,
+ image_url: image_url ? new URL(image_url, baseUrl).href : null,
+ });
+ });
+
+ // 3. Common card patterns.
+ const cardSelectors = [
+ 'article.physician-card',
+ 'article.provider-card',
+ 'div.provider, div.physician, div.doctor-card, div.search-result',
+ 'li.provider-card, li.doctor-card',
+ ];
+ for (const sel of cardSelectors) {
+ $(sel).each((_, el) => {
+ const $el = $(el);
+ const name = textOf($el.find('h2, h3, .name, .provider-name').first());
+ if (!name) return;
+ const specialty = textOf($el.find('.specialty, .specialties, .provider-specialty').first());
+ const phone = textOf($el.find('.phone, [data-phone]').first());
+ const profile_url = $el.find('a').first().attr('href');
+ const image_url = $el.find('img').first().attr('src');
+ const location = textOf($el.find('.location, .address, .provider-location').first());
+ results.push({
+ name, specialty, phone, location,
+ profile_url: profile_url ? new URL(profile_url, baseUrl).href : null,
+ image_url: image_url ? new URL(image_url, baseUrl).href : null,
+ });
+ });
+ if (results.length > 0) break;
+ }
+
+ // De-dupe by (name, profile_url).
+ const seen = new Set();
+ return results.filter(r => {
+ const k = `${(r.name||'').toLowerCase()}|${r.profile_url||''}`;
+ if (seen.has(k)) return false;
+ seen.add(k);
+ return Boolean(r.name);
+ });
+}
+
+/**
+ * Parse an individual provider profile page for richer fields.
+ */
+function extractProviderProfile(html, url) {
+ const $ = cheerio.load(html);
+ const out = { profile_url: url, source_url: url };
+
+ // JSON-LD wins when present.
+ $('script[type="application/ld+json"]').each((_, s) => {
+ try {
+ const j = JSON.parse($(s).text());
+ const arr = Array.isArray(j) ? j : [j];
+ for (const obj of arr) {
+ const t = (obj?.['@type'] || '').toLowerCase();
+ if (t === 'physician' || t === 'person') {
+ out.name = out.name || obj.name;
+ out.specialty = out.specialty || obj.medicalSpecialty;
+ out.phone = out.phone || obj.telephone;
+ out.image_url = out.image_url || obj.image;
+ out.email = out.email || obj.email;
+ out.bio = out.bio || obj.description;
+ out.npi = out.npi || obj.identifier?.value;
+ if (obj.alumniOf) {
+ const a = Array.isArray(obj.alumniOf) ? obj.alumniOf[0] : obj.alumniOf;
+ out.medical_school = out.medical_school || (typeof a === 'string' ? a : a?.name);
+ }
+ }
+ }
+ } catch (_) { /* ignore */ }
+ });
+
+ // Heuristic fallbacks.
+ out.name ||= textOf($('h1').first());
+ out.specialty ||= textOf($('.specialty, [itemprop="medicalSpecialty"]').first());
+ out.phone ||= textOf($('a[href^="tel:"]').first()) || $('a[href^="tel:"]').first().attr('href')?.replace('tel:','');
+ out.email ||= ($('a[href^="mailto:"]').first().attr('href') || '').replace('mailto:','') || null;
+ out.bio ||= textOf($('.bio, .biography, .provider-bio, [itemprop="description"]').first());
+ out.image_url ||= $('.provider-photo img, .doctor-photo img, [itemprop="image"]').first().attr('src');
+ out.medical_school ||= textOf($('.education, .schooling').filter((_,e)=>/medical|md|school/i.test($(e).text())).first());
+
+ return out;
+}
+
+module.exports = { extractProviderCards, extractProviderProfile };
diff --git a/agents/enrich-agent/geocode.js b/agents/enrich-agent/geocode.js
new file mode 100644
index 0000000..a974c39
--- /dev/null
+++ b/agents/enrich-agent/geocode.js
@@ -0,0 +1,116 @@
+#!/usr/bin/env node
+/**
+ * Free geocoder — Stage 6.
+ *
+ * 1. US Census Geocoding API — free, no key, no auth, no rate limit
+ * https://geocoding.geo.census.gov/geocoder/locations/onelineaddress
+ *
+ * 2. OpenStreetMap Nominatim — free, polite-use 1 req/s, identifying UA
+ * https://nominatim.openstreetmap.org/search
+ *
+ * No Google APIs (compliance rule #3 — zero-dollar build).
+ *
+ * Run:
+ * node agents/enrich-agent/geocode.js # all rows where lat IS NULL
+ * LIMIT=100 node agents/enrich-agent/geocode.js
+ */
+const { fetch } = require('undici');
+const { pool, query } = require('../shared/db');
+const { setHostRateLimit } = require('../shared/compliance');
+
+const CENSUS_URL = 'https://geocoding.geo.census.gov/geocoder/locations/onelineaddress';
+const NOMINATIM_URL = 'https://nominatim.openstreetmap.org/search';
+const USER_AGENT = process.env.USER_AGENT
+ || 'ProfessionalDirectoryBot/0.1 (research; contact: steveabramsdesigns@gmail.com)';
+
+setHostRateLimit('nominatim.openstreetmap.org', 1.0); // 1 rps per their ToS
+setHostRateLimit('geocoding.geo.census.gov', 5.0); // generous; no published limit
+
+async function geocodeCensus(addr, city, state, zip) {
+ const oneLine = [addr, city, state, zip].filter(Boolean).join(', ');
+ const url = `${CENSUS_URL}?address=${encodeURIComponent(oneLine)}&benchmark=Public_AR_Current&format=json`;
+ const res = await fetch(url, {
+ headers: { 'User-Agent': USER_AGENT },
+ signal: AbortSignal.timeout(15000),
+ });
+ if (!res.ok) return null;
+ const j = await res.json();
+ const m = j?.result?.addressMatches?.[0];
+ if (!m) return null;
+ return {
+ lat: Number(m.coordinates?.y),
+ lng: Number(m.coordinates?.x),
+ source: 'us_census',
+ matched: m.matchedAddress,
+ };
+}
+
+async function geocodeNominatim(addr, city, state, zip) {
+ const oneLine = [addr, city, state, zip].filter(Boolean).join(', ');
+ const url = `${NOMINATIM_URL}?q=${encodeURIComponent(oneLine)}&format=json&limit=1&countrycodes=us`;
+ const res = await fetch(url, {
+ headers: { 'User-Agent': USER_AGENT, 'Accept-Language': 'en-US' },
+ signal: AbortSignal.timeout(15000),
+ });
+ if (!res.ok) return null;
+ const arr = await res.json();
+ if (!Array.isArray(arr) || arr.length === 0) return null;
+ return {
+ lat: Number(arr[0].lat),
+ lng: Number(arr[0].lon),
+ source: 'nominatim',
+ matched: arr[0].display_name,
+ };
+}
+
+async function main() {
+ const limit = process.env.LIMIT ? Number(process.env.LIMIT) : null;
+ const sql = `
+ SELECT id, address, city, state, zip
+ FROM organizations
+ WHERE lat IS NULL
+ AND address IS NOT NULL
+ AND zip IS NOT NULL
+ ORDER BY id
+ ${limit ? `LIMIT ${limit}` : ''}
+ `;
+ const rows = (await query(sql, [])).rows;
+ console.log(`[geo] candidates=${rows.length}`);
+
+ let ok = 0, fail = 0;
+ for (const r of rows) {
+ let result;
+ try {
+ result = await geocodeCensus(r.address, r.city, r.state, r.zip);
+ if (!result) {
+ // Census misses → polite Nominatim fallback (1 rps).
+ await new Promise(res => setTimeout(res, 1100));
+ result = await geocodeNominatim(r.address, r.city, r.state, r.zip);
+ }
+ } catch (e) {
+ console.error(`[geo] error org=${r.id}: ${e.message}`);
+ }
+
+ if (result && Number.isFinite(result.lat) && Number.isFinite(result.lng)) {
+ await query(`
+ UPDATE organizations
+ SET lat = $2, lng = $3, geocoded_at = NOW(), updated_at = NOW()
+ WHERE id = $1
+ `, [r.id, result.lat, result.lng]);
+ ok++;
+ } else {
+ fail++;
+ }
+
+ if ((ok + fail) % 50 === 0) console.log(`[geo] ok=${ok} fail=${fail}`);
+ }
+
+ console.log(`[geo] done. ok=${ok} fail=${fail}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[geo] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/agents/ingest-agent/importers/__fixtures__/npi_smoke.csv b/agents/ingest-agent/importers/__fixtures__/npi_smoke.csv
new file mode 100644
index 0000000..8b6c72d
--- /dev/null
+++ b/agents/ingest-agent/importers/__fixtures__/npi_smoke.csv
@@ -0,0 +1,7 @@
+"NPI","Entity Type Code","Replacement NPI","Employer Identification Number (EIN)","Provider Organization Name (Legal Business Name)","Provider Last Name (Legal Name)","Provider First Name","Provider Middle Name","Provider Name Prefix Text","Provider Name Suffix Text","Provider Credential Text","Provider Other Organization Name","Provider Other Organization Name Type Code","Provider Other Last Name","Provider Other First Name","Provider Other Middle Name","Provider Other Name Prefix Text","Provider Other Name Suffix Text","Provider Other Credential Text","Provider Other Last Name Type Code","Provider First Line Business Mailing Address","Provider Second Line Business Mailing Address","Provider Business Mailing Address City Name","Provider Business Mailing Address State Name","Provider Business Mailing Address Postal Code","Provider Business Mailing Address Country Code (If outside U.S.)","Provider Business Mailing Address Telephone Number","Provider Business Mailing Address Fax Number","Provider First Line Business Practice Location Address","Provider Second Line Business Practice Location Address","Provider Business Practice Location Address City Name","Provider Business Practice Location Address State Name","Provider Business Practice Location Address Postal Code","Provider Business Practice Location Address Country Code (If outside U.S.)","Provider Business Practice Location Address Telephone Number","Provider Business Practice Location Address Fax Number","Provider Enumeration Date","Last Update Date","NPI Deactivation Reason Code","NPI Deactivation Date","NPI Reactivation Date","Provider Gender Code","Authorized Official Last Name","Authorized Official First Name","Authorized Official Middle Name","Authorized Official Title or Position","Authorized Official Telephone Number","Healthcare Provider Taxonomy Code_1","Provider License Number_1","Provider License Number State Code_1","Healthcare Provider Primary Taxonomy Switch_1","Healthcare Provider Taxonomy Code_2","Provider License Number_2","Provider License Number State Code_2","Healthcare Provider Primary Taxonomy Switch_2"
+"1000000001","1","","","","SMITH","JANE","ANN","DR.","","MD","","","","","","","","","","100 MEDICAL PLAZA","SUITE 200","LOS ANGELES","CA","90024","US","3108250000","","100 MEDICAL PLAZA","SUITE 200","LOS ANGELES","CA","90024","US","3108250000","","01/15/2010","04/01/2024","","","","F","","","","","","207RC0000X","A12345","CA","Y","","","",""
+"1000000002","1","","","","CHEN","DAVID","","DR.","","MD","","","","","","","","","","8700 BEVERLY BLVD","","LOS ANGELES","CA","90048","US","3104231000","","8700 BEVERLY BLVD","","LOS ANGELES","CA","90048","US","3104231000","","03/22/2008","06/15/2024","","","","M","","","","","","207RH0000X","G54321","CA","Y","207R00000X","","","N"
+"1000000003","2","","12-3456789","KAISER PERMANENTE LOS ANGELES MEDICAL CENTER","","","","","","","","","","","","","","","","4867 SUNSET BLVD","","LOS ANGELES","CA","90027","US","3237830000","","4867 SUNSET BLVD","","LOS ANGELES","CA","90027","US","3237830000","","05/01/2006","02/10/2024","","","","","SMITH","ROBERT","","CEO","3237830000","282N00000X","","","Y","","","",""
+"1000000004","1","","","","NGUYEN","LINDA","","DR.","","MD","","","","","","","","","","8635 W THIRD ST","SUITE 1180W","LOS ANGELES","CA","90048","US","3108555000","","8635 W THIRD ST","SUITE 1180W","LOS ANGELES","CA","90048","US","3108555000","","09/12/2015","11/30/2024","","","","F","","","","","","207V00000X","C99887","CA","Y","","","",""
+"1000000005","1","","","","JOHNSON","MICHAEL","T","DR.","","DO","","","","","","","","","","1000 W CARSON ST","BOX 405","TORRANCE","CA","90502","US","3102224000","","1000 W CARSON ST","BOX 405","TORRANCE","CA","90502","US","3102224000","","07/04/2012","08/22/2024","","","","M","","","","","","207RX0202X","X22334","CA","Y","","","",""
+"1000000006","1","","","","ROLLER","KIM","","DR.","","MD","","","","","","","","","","555 BROADWAY","","NEW YORK","NY","10012","US","2125550000","","555 BROADWAY","","NEW YORK","NY","10012","US","2125550000","","01/01/2020","01/01/2024","","","","F","","","","","","207RC0000X","NY77777","NY","Y","","","",""
diff --git a/agents/ingest-agent/importers/cms-care-compare.js b/agents/ingest-agent/importers/cms-care-compare.js
new file mode 100644
index 0000000..5a38d6e
--- /dev/null
+++ b/agents/ingest-agent/importers/cms-care-compare.js
@@ -0,0 +1,221 @@
+#!/usr/bin/env node
+/**
+ * CMS Care Compare — "Doctors and Clinicians National Downloadable File"
+ * Stage 3 enrichment.
+ *
+ * Source: https://data.cms.gov/provider-data/dataset/mj5m-pzi6
+ * CSV : https://data.cms.gov/provider-data/sites/default/files/resources/<id>/DAC_NationalDownloadableFile.csv
+ *
+ * Joins to professionals by NPI. Adds:
+ * primary_specialty (CMS specialty) → professionals.primary_specialty
+ * hospital_affiliation_ccn / name → organizations + affiliation row
+ * group_practice_pac_id / name → organizations (medical_group)
+ * gender → professionals.gender
+ * credentials → professionals.title
+ *
+ * No PII exposed beyond what NPPES already publishes.
+ */
+const fs = require('node:fs');
+const path = require('node:path');
+const crypto = require('node:crypto');
+const { parse } = require('csv-parse');
+const { fetch } = require('undici');
+const { pool, query, withTx } = require('../../shared/db');
+const { laZipSet } = require('../../shared/la-zips');
+
+const SOURCE_NAME = 'CMS Care Compare (Doctors and Clinicians)';
+const SOURCE_URL = 'https://data.cms.gov/provider-data/dataset/mj5m-pzi6';
+const CSV_URL = process.env.CMS_DAC_URL
+ || 'https://data.cms.gov/provider-data/sites/default/files/resources/52c3f098d7e56028a298fd297cb0b38d_1774905035/DAC_NationalDownloadableFile.csv';
+const CACHE = path.resolve(__dirname, '../../../data/cms/DAC_NationalDownloadableFile.csv');
+
+function clean(s) {
+ if (s === undefined || s === null) return null;
+ const t = String(s).trim();
+ return t.length === 0 ? null : t;
+}
+function pick(row, ...keys) {
+ for (const k of keys) {
+ const v = row[k];
+ if (v !== undefined && v !== null && String(v).trim() !== '') return clean(v);
+ }
+ return null;
+}
+function zip5(z) {
+ if (!z) return null;
+ const m = String(z).match(/\d{5}/);
+ return m ? m[0] : null;
+}
+function sha256(s) { return crypto.createHash('sha256').update(s).digest('hex'); }
+
+async function downloadIfNeeded() {
+ if (fs.existsSync(CACHE)) {
+ const ageDays = (Date.now() - fs.statSync(CACHE).mtimeMs) / 86400000;
+ if (ageDays < 30) {
+ console.log(`[cms-dac] using cache (${ageDays.toFixed(1)}d old)`);
+ return;
+ }
+ }
+ fs.mkdirSync(path.dirname(CACHE), { recursive: true });
+ console.log(`[cms-dac] downloading ${CSV_URL}`);
+ const res = await fetch(CSV_URL, { signal: AbortSignal.timeout(180000) });
+ if (!res.ok) throw new Error(`CMS DAC download failed: HTTP ${res.status}`);
+ const buf = Buffer.from(await res.arrayBuffer());
+ fs.writeFileSync(CACHE, buf);
+ console.log(`[cms-dac] cached → ${CACHE} (${(buf.length/1e6).toFixed(1)} MB)`);
+}
+
+async function getSourceId() {
+ const r = await query('SELECT id FROM sources WHERE source_name = $1', [SOURCE_NAME]);
+ if (r.rowCount === 0) throw new Error(`source not seeded: ${SOURCE_NAME}`);
+ return r.rows[0].id;
+}
+
+async function startJob(sourceId) {
+ const r = await query(
+ `INSERT INTO scrape_jobs (source_id, job_label, status, started_at)
+ VALUES ($1,'cms-dac:bulk','running',NOW()) RETURNING id`, [sourceId]);
+ return r.rows[0].id;
+}
+
+async function finishJob(jobId, fields) {
+ const sets = []; const params = []; 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 findProfessional(client, npi) {
+ if (!npi) return null;
+ const r = await client.query(`SELECT id FROM professionals WHERE npi_number = $1 LIMIT 1`, [npi]);
+ return r.rowCount ? r.rows[0].id : null;
+}
+
+async function ensureGroup(client, name, address, city, state, zip, ccn) {
+ if (!name) return null;
+ // Try CCN first, then name+zip.
+ let r;
+ if (ccn) {
+ r = await client.query(`SELECT id FROM organizations WHERE cdph_license = $1 OR hcai_id = $1 LIMIT 1`, [ccn]);
+ if (r.rowCount) return r.rows[0].id;
+ }
+ if (name && zip) {
+ r = await client.query(`SELECT id FROM organizations WHERE LOWER(name)=LOWER($1) AND zip=$2 LIMIT 1`, [name, zip]);
+ if (r.rowCount) return r.rows[0].id;
+ }
+ r = await client.query(`
+ INSERT INTO organizations (name, type, address, city, state, zip, county)
+ VALUES ($1,$2,$3,$4,$5,$6,$7)
+ RETURNING id
+ `, [name, 'medical_group', address, city, state || 'CA', zip, 'Los Angeles']);
+ return r.rows[0].id;
+}
+
+async function applyDac(client, professionalId, row, sourceId) {
+ const cmsSpecialty = pick(row, 'pri_spec', 'Primary specialty', 'Primary Specialty');
+ const credentials = pick(row, 'Cred', 'Credential', 'cred');
+ const gender = pick(row, 'gndr', 'Gender');
+
+ await client.query(`
+ UPDATE professionals SET
+ primary_specialty = COALESCE($2, primary_specialty),
+ title = COALESCE($3, title),
+ gender = COALESCE($4, gender),
+ source_confidence_score = GREATEST(COALESCE(source_confidence_score, 0), 0.75),
+ updated_at = NOW()
+ WHERE id = $1
+ `, [professionalId, cmsSpecialty, credentials, gender ? gender.charAt(0).toUpperCase() : null]);
+
+ // Group practice as an organization + affiliation.
+ const groupName = pick(row, 'org_nm', 'Organization legal name', 'Group name');
+ const groupZip = zip5(pick(row, 'adr_zip', 'ZIP Code', 'org_zip'));
+ const groupCity = pick(row, 'cty', 'City', 'org_cty');
+ const groupAddr = pick(row, 'adr_ln_1', 'Address Line 1', 'org_adr_ln_1');
+ if (groupName) {
+ const orgId = await ensureGroup(client, groupName, groupAddr, groupCity, 'CA', groupZip,
+ pick(row, 'org_pac_id', 'Group PAC ID'));
+ await client.query(`
+ INSERT INTO professional_locations
+ (professional_id, organization_id, role, address, source_url, last_verified_at)
+ VALUES ($1,$2,'employed',$3,$4,NOW())
+ ON CONFLICT DO NOTHING
+ `, [professionalId, orgId, groupAddr, SOURCE_URL]);
+ }
+
+ // Hospital affiliation.
+ const hospName = pick(row, 'hosp_afl_1', 'Hospital affiliation name 1');
+ const hospCcn = pick(row, 'hosp_afl_lbn_1', 'Hospital affiliation CCN 1');
+ if (hospName) {
+ const hospId = await ensureGroup(client, hospName, null, null, 'CA', null, hospCcn);
+ await client.query(`
+ UPDATE organizations SET type = 'hospital', updated_at = NOW()
+ WHERE id = $1 AND type IN ('medical_group','clinic',NULL)
+ `, [hospId]);
+ await client.query(`
+ INSERT INTO professional_locations
+ (professional_id, organization_id, role, source_url, last_verified_at)
+ VALUES ($1,$2,'privileges',$3,NOW())
+ ON CONFLICT DO NOTHING
+ `, [professionalId, hospId, SOURCE_URL]);
+ }
+
+ // Provenance.
+ const json = JSON.stringify(row);
+ const hash = sha256(json + '|professional|' + professionalId);
+ await client.query(`
+ INSERT INTO raw_records (source_id, source_url, entity_type, entity_id, raw_json, hash)
+ VALUES ($1,$2,'professional',$3,$4::jsonb,$5)
+ ON CONFLICT (hash) DO NOTHING
+ `, [sourceId, SOURCE_URL, professionalId, json, hash]);
+}
+
+async function main() {
+ await downloadIfNeeded();
+ const sourceId = await getSourceId();
+ const jobId = await startJob(sourceId);
+ const laZips = await laZipSet();
+
+ let scanned = 0, matched = 0, skipped = 0;
+
+ const parser = fs.createReadStream(CACHE).pipe(parse({
+ columns: true, skip_empty_lines: true, bom: true, relax_column_count: true,
+ }));
+
+ for await (const row of parser) {
+ scanned++;
+ const npi = pick(row, 'NPI', 'npi');
+ const state = pick(row, 'st', 'State');
+ const z = zip5(pick(row, 'adr_zip', 'ZIP Code'));
+
+ if (state && state !== 'CA') { skipped++; continue; }
+ if (z && !laZips.has(z)) { skipped++; continue; }
+ if (!npi) { skipped++; continue; }
+
+ try {
+ await withTx(async (client) => {
+ const id = await findProfessional(client, npi);
+ if (!id) { skipped++; return; }
+ await applyDac(client, id, row, sourceId);
+ matched++;
+ });
+ } catch (e) {
+ console.error(`[cms-dac] row error npi=${npi}: ${e.message}`);
+ }
+
+ if (scanned % 10000 === 0) console.log(`[cms-dac] scanned=${scanned} matched=${matched}`);
+ }
+
+ await finishJob(jobId, {
+ status: 'done',
+ records_found: scanned, records_updated: matched, records_skipped: skipped,
+ });
+
+ console.log(`[cms-dac] done. scanned=${scanned} matched=${matched} skipped=${skipped}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[cms-dac] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/agents/ingest-agent/importers/cms-hospitals.js b/agents/ingest-agent/importers/cms-hospitals.js
new file mode 100644
index 0000000..9802648
--- /dev/null
+++ b/agents/ingest-agent/importers/cms-hospitals.js
@@ -0,0 +1,178 @@
+#!/usr/bin/env node
+/**
+ * CMS Hospital General Information — Stage 4 enrichment.
+ *
+ * Source: https://data.cms.gov/provider-data/dataset/xubh-q36u
+ * CSV : Hospital_General_Information.csv (every Medicare-certified hospital
+ * in the country, with CCN, name, address, type, ownership, ratings).
+ *
+ * For LA County, joins by CDPH license / name / ZIP into organizations and
+ * upgrades the row's type to 'hospital'.
+ */
+const fs = require('node:fs');
+const path = require('node:path');
+const crypto = require('node:crypto');
+const { parse } = require('csv-parse');
+const { fetch } = require('undici');
+const { pool, query, withTx } = require('../../shared/db');
+const { laZipSet } = require('../../shared/la-zips');
+
+const SOURCE_NAME = 'CA HCAI Hospital Annual Utilization'; // existing seeded source
+const SOURCE_URL = 'https://data.cms.gov/provider-data/dataset/xubh-q36u';
+const CSV_URL = process.env.CMS_HOSP_URL
+ || 'https://data.cms.gov/provider-data/sites/default/files/resources/893c372430d9d71a1c52737d01239d47_1770163599/Hospital_General_Information.csv';
+const CACHE = path.resolve(__dirname, '../../../data/cms/Hospital_General_Information.csv');
+
+function clean(s) {
+ if (s === undefined || s === null) return null;
+ const t = String(s).trim();
+ return t.length === 0 ? null : t;
+}
+function pick(row, ...keys) {
+ for (const k of keys) {
+ const v = row[k];
+ if (v !== undefined && v !== null && String(v).trim() !== '') return clean(v);
+ }
+ return null;
+}
+function zip5(z) {
+ if (!z) return null;
+ const m = String(z).match(/\d{5}/);
+ return m ? m[0] : null;
+}
+function sha256(s) { return crypto.createHash('sha256').update(s).digest('hex'); }
+
+async function downloadIfNeeded() {
+ if (fs.existsSync(CACHE)) {
+ const ageDays = (Date.now() - fs.statSync(CACHE).mtimeMs) / 86400000;
+ if (ageDays < 30) {
+ console.log(`[cms-hosp] using cache (${ageDays.toFixed(1)}d old)`);
+ return;
+ }
+ }
+ fs.mkdirSync(path.dirname(CACHE), { recursive: true });
+ console.log(`[cms-hosp] downloading ${CSV_URL}`);
+ const res = await fetch(CSV_URL, { signal: AbortSignal.timeout(60000) });
+ if (!res.ok) throw new Error(`CMS hospitals download failed: HTTP ${res.status}`);
+ const buf = Buffer.from(await res.arrayBuffer());
+ fs.writeFileSync(CACHE, buf);
+ console.log(`[cms-hosp] cached → ${CACHE} (${(buf.length/1e6).toFixed(1)} MB)`);
+}
+
+async function getSourceId() {
+ const r = await query('SELECT id FROM sources WHERE source_name = $1', [SOURCE_NAME]);
+ if (r.rowCount === 0) throw new Error(`source not seeded: ${SOURCE_NAME}`);
+ return r.rows[0].id;
+}
+
+async function startJob(sourceId) {
+ const r = await query(
+ `INSERT INTO scrape_jobs (source_id, job_label, status, started_at)
+ VALUES ($1,'cms-hosp:bulk','running',NOW()) RETURNING id`, [sourceId]);
+ return r.rows[0].id;
+}
+
+async function finishJob(jobId, fields) {
+ const sets = []; const params = []; 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 upsertHospital(client, h) {
+ // Match by CCN (Provider ID) → cdph_license, then name+zip.
+ let r;
+ if (h.ccn) {
+ r = await client.query(`SELECT id FROM organizations WHERE cdph_license=$1 OR hcai_id=$1 LIMIT 1`, [h.ccn]);
+ if (r.rowCount) {
+ await client.query(`
+ UPDATE organizations SET
+ name=COALESCE($2,name), type='hospital',
+ address=COALESCE($3,address), city=COALESCE($4,city),
+ zip=COALESCE($5,zip), county=COALESCE($6,county),
+ phone=COALESCE($7,phone), updated_at=NOW()
+ WHERE id=$1
+ `, [r.rows[0].id, h.name, h.address, h.city, h.zip, h.county, h.phone]);
+ return r.rows[0].id;
+ }
+ }
+ if (h.name && h.zip) {
+ r = await client.query(`SELECT id FROM organizations WHERE LOWER(name)=LOWER($1) AND zip=$2 LIMIT 1`, [h.name, h.zip]);
+ if (r.rowCount) {
+ await client.query(`UPDATE organizations SET type='hospital', cdph_license=COALESCE($2, cdph_license), phone=COALESCE($3, phone), updated_at=NOW() WHERE id=$1`,
+ [r.rows[0].id, h.ccn, h.phone]);
+ return r.rows[0].id;
+ }
+ }
+ r = await client.query(`
+ INSERT INTO organizations
+ (name, type, address, city, state, zip, county, phone, cdph_license)
+ VALUES ($1,'hospital',$2,$3,'CA',$4,$5,$6,$7)
+ RETURNING id
+ `, [h.name, h.address, h.city, h.zip, h.county, h.phone, h.ccn]);
+ return r.rows[0].id;
+}
+
+async function main() {
+ await downloadIfNeeded();
+ const sourceId = await getSourceId();
+ const jobId = await startJob(sourceId);
+ const laZips = await laZipSet();
+
+ let scanned = 0, kept = 0, upserted = 0;
+
+ const parser = fs.createReadStream(CACHE).pipe(parse({
+ columns: true, skip_empty_lines: true, bom: true, relax_column_count: true,
+ }));
+
+ for await (const row of parser) {
+ scanned++;
+ const state = pick(row, 'State');
+ if (state && state !== 'CA') continue;
+ const z = zip5(pick(row, 'ZIP Code'));
+ const county = pick(row, 'County/Parish', 'County');
+ if (!((z && laZips.has(z)) || /los angeles/i.test(county || ''))) continue;
+ kept++;
+
+ const h = {
+ ccn: pick(row, 'CMS Certification Number (CCN)', 'Facility ID', 'Provider ID'),
+ name: pick(row, 'Hospital Name', 'Facility Name'),
+ address: pick(row, 'Address'),
+ city: pick(row, 'City', 'City/Town', 'CITY'),
+ zip: z,
+ county: county || 'Los Angeles',
+ phone: pick(row, 'Phone Number', 'Telephone'),
+ };
+ if (!h.name) continue;
+
+ try {
+ await withTx(async (client) => {
+ const id = await upsertHospital(client, h);
+ const json = JSON.stringify(row);
+ const hash = sha256(json + '|organization|' + id);
+ await client.query(`
+ INSERT INTO raw_records
+ (source_id, source_url, entity_type, entity_id, raw_json, hash)
+ VALUES ($1,$2,'organization',$3,$4::jsonb,$5)
+ ON CONFLICT (hash) DO NOTHING
+ `, [sourceId, SOURCE_URL, id, json, hash]);
+ upserted++;
+ });
+ } catch (e) {
+ console.error(`[cms-hosp] error ${h.name}: ${e.message}`);
+ }
+ }
+
+ await finishJob(jobId, {
+ status: 'done', records_found: scanned,
+ records_inserted: upserted, records_skipped: scanned - kept,
+ });
+ console.log(`[cms-hosp] done. scanned=${scanned} la_kept=${kept} upserted=${upserted}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[cms-hosp] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/agents/ingest-agent/importers/dca.js b/agents/ingest-agent/importers/dca.js
new file mode 100644
index 0000000..79e0229
--- /dev/null
+++ b/agents/ingest-agent/importers/dca.js
@@ -0,0 +1,183 @@
+#!/usr/bin/env node
+/**
+ * DCA License Search enricher — Stage 2.
+ *
+ * Source page: https://search.dca.ca.gov/
+ * Public, no key, robots-aware via lib compliance.
+ *
+ * The public DCA "results" endpoint accepts a license number + board ID
+ * and returns HTML; we parse the structured fields. We rate-limit to
+ * 0.5 rps per host and back off on any non-200.
+ *
+ * Run:
+ * node agents/ingest-agent/importers/dca.js # all CA-licensed pros
+ * LIMIT=50 node agents/ingest-agent/importers/dca.js # smoke
+ */
+const cheerio = require('cheerio');
+const { pool, query, withTx } = require('../../shared/db');
+const { fetchCompliant, setHostRateLimit } = require('../../shared/compliance');
+
+const SOURCE_NAME = 'DCA License Search';
+const BOARD_MD = '800'; // Medical Board of California — physicians & surgeons (M.D.)
+const BOARD_DO = '870'; // Osteopathic Medical Board of California (D.O.)
+const SEARCH_URL = 'https://search.dca.ca.gov/results';
+
+setHostRateLimit('search.dca.ca.gov', 0.5);
+
+function buildResultsUrl(licenseType, licenseNumber) {
+ const board = licenseType === 'DO' ? BOARD_DO : BOARD_MD;
+ // The site accepts a GET form post style URL.
+ const qs = new URLSearchParams({
+ boardCode: board,
+ licenseType: 'A', // any
+ licenseNumber: licenseNumber,
+ });
+ return `${SEARCH_URL}?${qs.toString()}`;
+}
+
+function parseDcaPage(html) {
+ const $ = cheerio.load(html);
+ // Each license card has labels in <strong> with values following.
+ const out = {};
+ $('div.search_result, table tr').each((_, el) => {
+ const $el = $(el);
+ const text = $el.text().replace(/\s+/g, ' ').trim();
+ const m = text.match(/License (?:Status|Type|Number|Issue Date|Expiration Date|Original Issue Date)[:\s]+([^\n]+?)(?=\s+License|\s+Address|\s+School|$)/gi);
+ if (!m) return;
+ for (const piece of m) {
+ const [, key, val] = piece.match(/(License (?:Status|Type|Number|Issue Date|Expiration Date|Original Issue Date))[:\s]+(.+)/i) || [];
+ if (key) out[key.toLowerCase().replace(/\s+/g, '_')] = val.trim();
+ }
+ });
+ // Robust fallback: scan label/value pairs.
+ $('td,div,span,li').each((_, el) => {
+ const t = $(el).text().replace(/\s+/g,' ').trim();
+ let m;
+ if ((m = t.match(/^License Status:?\s*(.+)$/i))) out.status = m[1].trim();
+ if ((m = t.match(/^License Type:?\s*(.+)$/i))) out.license_type_full = m[1].trim();
+ if ((m = t.match(/^Original Issue Date:?\s*(.+)$/i))) out.issue_date = m[1].trim();
+ if ((m = t.match(/^Expiration Date:?\s*(.+)$/i))) out.expiration_date = m[1].trim();
+ if ((m = t.match(/^School Name:?\s*(.+)$/i))) out.school = m[1].trim();
+ if ((m = t.match(/^Graduation Year:?\s*(.+)$/i))) out.graduation_year = m[1].trim();
+ if ((m = t.match(/^Address of Record:?\s*(.+)$/i))) out.address = m[1].trim();
+ });
+ return out;
+}
+
+function toIso(d) {
+ if (!d) return null;
+ const us = d.match(/(\d{1,2})\/(\d{1,2})\/(\d{4})/);
+ if (us) return `${us[3]}-${us[1].padStart(2,'0')}-${us[2].padStart(2,'0')}`;
+ const iso = d.match(/(\d{4})-(\d{2})-(\d{2})/);
+ return iso ? `${iso[1]}-${iso[2]}-${iso[3]}` : null;
+}
+
+async function getSourceId() {
+ const r = await query('SELECT id FROM sources WHERE source_name = $1', [SOURCE_NAME]);
+ if (r.rowCount === 0) throw new Error(`source not seeded: ${SOURCE_NAME}`);
+ return r.rows[0].id;
+}
+
+async function loadCandidates(limit) {
+ const sql = `
+ SELECT id, license_number, title
+ FROM professionals
+ WHERE license_number IS NOT NULL
+ AND title IN ('MD','DO','M.D.','D.O.')
+ AND (license_status IS NULL OR license_status NOT IN ('Active','Current'))
+ ORDER BY id
+ ${limit ? `LIMIT ${Number(limit)}` : ''}
+ `;
+ const r = await query(sql, []);
+ return r.rows;
+}
+
+async function startJob(sourceId) {
+ const r = await query(
+ `INSERT INTO scrape_jobs (source_id, job_label, status, started_at)
+ VALUES ($1,'dca:enrich','running',NOW()) RETURNING id`, [sourceId]);
+ return r.rows[0].id;
+}
+
+async function finishJob(jobId, fields) {
+ const sets = []; const params = []; 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 applyDca(professionalId, dca, sourceUrl, sourceId) {
+ await withTx(async (client) => {
+ await client.query(`
+ UPDATE professionals SET
+ license_status = COALESCE($2, license_status),
+ license_issue_date = COALESCE($3, license_issue_date),
+ license_expiration_date = COALESCE($4, license_expiration_date),
+ medical_school = COALESCE($5, medical_school),
+ graduation_year = COALESCE($6, graduation_year),
+ source_confidence_score = GREATEST(COALESCE(source_confidence_score,0), 0.80),
+ updated_at = NOW()
+ WHERE id = $1
+ `, [
+ professionalId,
+ dca.status || null,
+ toIso(dca.issue_date),
+ toIso(dca.expiration_date),
+ dca.school || null,
+ dca.graduation_year ? Number(String(dca.graduation_year).match(/\d{4}/)?.[0]) || null : null,
+ ]);
+
+ const json = JSON.stringify(dca);
+ const crypto = require('node:crypto');
+ const hash = crypto.createHash('sha256').update(json + '|professional|' + professionalId).digest('hex');
+ await client.query(`
+ INSERT INTO raw_records (source_id, source_url, entity_type, entity_id, raw_json, hash)
+ VALUES ($1,$2,'professional',$3,$4::jsonb,$5)
+ ON CONFLICT (hash) DO NOTHING
+ `, [sourceId, sourceUrl, professionalId, json, hash]);
+ });
+}
+
+async function main() {
+ const limit = process.env.LIMIT ? Number(process.env.LIMIT) : null;
+ const sourceId = await getSourceId();
+ const jobId = await startJob(sourceId);
+ const candidates = await loadCandidates(limit);
+ console.log(`[dca] candidates=${candidates.length}`);
+
+ let ok = 0, fail = 0;
+ for (const cand of candidates) {
+ const url = buildResultsUrl(cand.title, cand.license_number);
+ try {
+ const res = await fetchCompliant(url);
+ if (!res.ok) { fail++; continue; }
+ const html = await res.text();
+ const dca = parseDcaPage(html);
+ if (Object.keys(dca).length === 0) { fail++; continue; }
+ await applyDca(cand.id, dca, url, sourceId);
+ ok++;
+ } catch (e) {
+ if (e.code === 'ROBOTS_DISALLOWED' || e.code === 'CAPTCHA_DETECTED') {
+ console.error(`[dca] aborted by compliance: ${e.code}`);
+ break;
+ }
+ fail++;
+ }
+ if ((ok + fail) % 100 === 0) console.log(`[dca] ok=${ok} fail=${fail}`);
+ }
+
+ await finishJob(jobId, {
+ status: 'done',
+ records_found: candidates.length,
+ records_updated: ok,
+ records_skipped: fail,
+ });
+ console.log(`[dca] done. ok=${ok} fail=${fail}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[dca] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/agents/ingest-agent/importers/hcai.js b/agents/ingest-agent/importers/hcai.js
new file mode 100644
index 0000000..6680cc3
--- /dev/null
+++ b/agents/ingest-agent/importers/hcai.js
@@ -0,0 +1,249 @@
+#!/usr/bin/env node
+/**
+ * HCAI / CDPH Licensed Healthcare Facility Locations — Stage 4.
+ *
+ * Source: https://data.chhs.ca.gov/dataset/healthcare-facility-listing
+ * Resource: "Licensed and Certified Healthcare Facility Locations"
+ * CSV (free, no key, public domain).
+ *
+ * Populates `organizations` for every licensed CA facility, then narrows the
+ * "in scope" set to LA County. We keep statewide rows so other counties can
+ * be added later by toggling the LA-only flag.
+ *
+ * Mapping:
+ * FACILITY_NAME → name
+ * FACILITY_TYPE → type (mapped to our enum)
+ * FACID / OSHPD_ID → hcai_id
+ * FAC_FEI / LICENSE_NUMBER → cdph_license
+ * ADDRESS / CITY / ZIP → address / city / zip
+ */
+const fs = require('node:fs');
+const path = require('node:path');
+const crypto = require('node:crypto');
+const { parse } = require('csv-parse');
+const { fetch } = require('undici');
+const { pool, query, withTx } = require('../../shared/db');
+const { laZipSet } = require('../../shared/la-zips');
+
+const SOURCE_NAME = 'CA HCAI Hospital Annual Utilization';
+const SOURCE_URL = 'https://data.chhs.ca.gov/dataset/healthcare-facility-listing';
+const CSV_URL = process.env.HCAI_CSV_URL
+ || 'https://data.chhs.ca.gov/dataset/3b5b80e8-6b8d-4715-b3c0-2699af6e72e5/resource/f0ae5731-fef8-417f-839d-54a0ed3a126e/download/health_facility_locations.csv';
+const CACHE = path.resolve(__dirname, '../../../data/hcai/health_facility_locations.csv');
+
+function clean(s) {
+ if (s === undefined || s === null) return null;
+ const t = String(s).trim();
+ return t.length === 0 ? null : t;
+}
+
+function pick(row, ...keys) {
+ for (const k of keys) {
+ const v = row[k];
+ if (v !== undefined && v !== null && String(v).trim() !== '') return clean(v);
+ }
+ return null;
+}
+
+function zip5(z) {
+ if (!z) return null;
+ const m = String(z).match(/\d{5}/);
+ return m ? m[0] : null;
+}
+
+function mapType(facType) {
+ if (!facType) return 'clinic';
+ const f = facType.toLowerCase();
+ if (f.includes('hospital') || f.includes('acute')) return 'hospital';
+ if (f.includes('skilled nursing') || f.includes('nursing facility')) return 'nursing_facility';
+ if (f.includes('surgical') || f.includes('surgery')) return 'surgery_center';
+ if (f.includes('urgent')) return 'urgent_care';
+ if (f.includes('clinic') || f.includes('community')) return 'clinic';
+ if (f.includes('home health')) return 'home_health';
+ if (f.includes('hospice')) return 'hospice';
+ if (f.includes('dialysis')) return 'dialysis';
+ return 'clinic';
+}
+
+function sha256(s) { return crypto.createHash('sha256').update(s).digest('hex'); }
+
+async function downloadIfNeeded() {
+ if (fs.existsSync(CACHE)) {
+ const ageDays = (Date.now() - fs.statSync(CACHE).mtimeMs) / 86400000;
+ if (ageDays < 7) {
+ console.log(`[hcai] using cache (${ageDays.toFixed(1)}d old)`);
+ return;
+ }
+ }
+ console.log(`[hcai] downloading ${CSV_URL}`);
+ fs.mkdirSync(path.dirname(CACHE), { recursive: true });
+ const res = await fetch(CSV_URL, { signal: AbortSignal.timeout(120000) });
+ if (!res.ok) throw new Error(`HCAI download failed: HTTP ${res.status}`);
+ const buf = Buffer.from(await res.arrayBuffer());
+ fs.writeFileSync(CACHE, buf);
+ console.log(`[hcai] cached → ${CACHE} (${(buf.length/1e6).toFixed(1)} MB)`);
+}
+
+async function getSourceId() {
+ const r = await query('SELECT id FROM sources WHERE source_name = $1', [SOURCE_NAME]);
+ if (r.rowCount === 0) throw new Error(`source not seeded: ${SOURCE_NAME}`);
+ return r.rows[0].id;
+}
+
+async function startJob(sourceId) {
+ const r = await query(
+ `INSERT INTO scrape_jobs (source_id, job_label, status, started_at)
+ VALUES ($1,'hcai:bulk','running',NOW()) RETURNING id`, [sourceId]);
+ return r.rows[0].id;
+}
+
+async function finishJob(jobId, fields) {
+ const sets = []; const params = []; 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 upsertOrg(client, fac) {
+ // Match priority: hcai_id, cdph_license, name+zip.
+ let r;
+ if (fac.hcai_id) {
+ r = await client.query(
+ `SELECT id FROM organizations WHERE hcai_id = $1 LIMIT 1`,
+ [fac.hcai_id]
+ );
+ if (r.rowCount > 0) return updateOrg(client, r.rows[0].id, fac);
+ }
+ if (fac.cdph_license) {
+ r = await client.query(
+ `SELECT id FROM organizations WHERE cdph_license = $1 LIMIT 1`,
+ [fac.cdph_license]
+ );
+ if (r.rowCount > 0) return updateOrg(client, r.rows[0].id, fac);
+ }
+ if (fac.name && fac.zip) {
+ r = await client.query(
+ `SELECT id FROM organizations
+ WHERE LOWER(name) = LOWER($1) AND zip = $2
+ LIMIT 1`,
+ [fac.name, fac.zip]
+ );
+ if (r.rowCount > 0) return updateOrg(client, r.rows[0].id, fac);
+ }
+ // Insert new.
+ r = await client.query(`
+ INSERT INTO organizations
+ (name, type, address, city, state, zip, county, phone,
+ hcai_id, cdph_license, lat, lng, geocoded_at)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,
+ $11::double precision, $12::double precision,
+ CASE WHEN $11::double precision IS NOT NULL
+ AND $12::double precision IS NOT NULL THEN NOW() END)
+ RETURNING id
+ `, [
+ fac.name, fac.type, fac.address, fac.city, 'CA', fac.zip,
+ fac.county || 'Los Angeles', fac.phone, fac.hcai_id, fac.cdph_license,
+ fac.lat, fac.lng,
+ ]);
+ return r.rows[0].id;
+}
+
+async function updateOrg(client, orgId, fac) {
+ // HCAI is authoritative for facility type — overwrite (not COALESCE) when present.
+ await client.query(`
+ UPDATE organizations SET
+ name = COALESCE($2, name),
+ type = COALESCE($3, type),
+ address = COALESCE($4, address),
+ city = COALESCE($5, city),
+ zip = COALESCE($6, zip),
+ county = COALESCE($7, county),
+ phone = COALESCE($8, phone),
+ hcai_id = COALESCE($9, hcai_id),
+ cdph_license= COALESCE($10, cdph_license),
+ lat = COALESCE($11::double precision, lat),
+ lng = COALESCE($12::double precision, lng),
+ geocoded_at = CASE WHEN $11::double precision IS NOT NULL
+ AND $12::double precision IS NOT NULL THEN NOW()
+ ELSE geocoded_at END,
+ updated_at = NOW()
+ WHERE id = $1
+ `, [orgId, fac.name, fac.type, fac.address, fac.city, fac.zip,
+ fac.county, fac.phone, fac.hcai_id, fac.cdph_license,
+ fac.lat, fac.lng]);
+ return orgId;
+}
+
+async function main() {
+ await downloadIfNeeded();
+ const sourceId = await getSourceId();
+ const jobId = await startJob(sourceId);
+ const laZips = await laZipSet();
+
+ let scanned = 0, kept = 0, inserted = 0;
+
+ const parser = fs.createReadStream(CACHE).pipe(parse({
+ columns: true, skip_empty_lines: true, bom: true, relax_column_count: true,
+ }));
+
+ for await (const row of parser) {
+ scanned++;
+ const fac = {
+ name: pick(row, 'FACNAME', 'FACILITY_NAME', 'FAC_NAME'),
+ facType: pick(row, 'FAC_FDR', 'FACILITY_TYPE', 'ENTITY_TYPE_DESCRIPTION'),
+ hcai_id: pick(row, 'HCAI_ID', 'OSHPD_ID', 'FACID'),
+ cdph_license: pick(row, 'LICENSE_NUMBER', 'FAC_FEI'),
+ address: pick(row, 'ADDRESS', 'FAC_ADDRESS', 'STREET_ADDRESS'),
+ city: pick(row, 'CITY', 'FAC_CITY'),
+ county: pick(row, 'COUNTY_NAME', 'COUNTY'),
+ zip: zip5(pick(row, 'ZIP', 'ZIP_CODE', 'POSTAL_CODE')),
+ phone: pick(row, 'CONTACT_PHONE_NUMBER', 'PHONE', 'FAC_PHONE'),
+ lat: parseFloat(pick(row, 'LATITUDE') || '') || null,
+ lng: parseFloat(pick(row, 'LONGITUDE') || '') || null,
+ status: pick(row, 'LICENSE_STATUS_DESCRIPTION'),
+ };
+ fac.type = mapType(fac.facType);
+
+ // LA County filter + active-license only
+ const inLA = (fac.zip && laZips.has(fac.zip)) ||
+ (fac.county && /los angeles/i.test(fac.county));
+ if (!inLA) continue;
+ if (!fac.name) continue;
+ if (fac.status && fac.status !== 'ACTIVE') continue;
+ kept++;
+
+ try {
+ await withTx(async (client) => {
+ const id = await upsertOrg(client, fac);
+ // Provenance.
+ const json = JSON.stringify(row);
+ const hash = sha256(json + '|organization|' + id);
+ await client.query(`
+ INSERT INTO raw_records
+ (source_id, source_url, entity_type, entity_id, raw_json, hash)
+ VALUES ($1,$2,'organization',$3,$4::jsonb,$5)
+ ON CONFLICT (hash) DO NOTHING
+ `, [sourceId, SOURCE_URL, id, json, hash]);
+ inserted++;
+ });
+ } catch (e) {
+ console.error(`[hcai] row error ${fac.name}: ${e.message}`);
+ }
+ }
+
+ await finishJob(jobId, {
+ status: 'done',
+ records_found: scanned,
+ records_inserted: inserted,
+ records_skipped: scanned - kept,
+ });
+
+ console.log(`[hcai] done. scanned=${scanned} la_kept=${kept} upserted=${inserted}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[hcai] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/agents/ingest-agent/importers/hrsa.js b/agents/ingest-agent/importers/hrsa.js
new file mode 100644
index 0000000..7bca92e
--- /dev/null
+++ b/agents/ingest-agent/importers/hrsa.js
@@ -0,0 +1,153 @@
+#!/usr/bin/env node
+/**
+ * HRSA FQHC / Health Center Look-Alike importer — Stage 4.
+ *
+ * HRSA publishes the federally qualified health center list as a public file:
+ * https://data.hrsa.gov/data/download
+ * "Health Center Service Delivery and LookAlike Sites" (CSV).
+ *
+ * For LA County we filter on State=CA + County=Los Angeles or LA-County ZIPs.
+ * Marks org_type='fqhc' on matched rows (or inserts new ones).
+ */
+const fs = require('node:fs');
+const path = require('node:path');
+const crypto = require('node:crypto');
+const { parse } = require('csv-parse');
+const { fetch } = require('undici');
+const { pool, query, withTx } = require('../../shared/db');
+const { laZipSet } = require('../../shared/la-zips');
+
+const SOURCE_NAME = 'HRSA FQHC Locator';
+const SOURCE_URL = 'https://data.hrsa.gov/data/download';
+// HRSA's stable API endpoint for "Health Center Service Delivery Sites" CSV.
+const CSV_URL = process.env.HRSA_CSV_URL
+ || 'https://data.hrsa.gov//DataDownload/HC/HC_Site.csv';
+const CACHE = path.resolve(__dirname, '../../../data/hrsa/HC_Site.csv');
+
+function clean(s) {
+ if (s === undefined || s === null) return null;
+ const t = String(s).trim();
+ return t.length === 0 ? null : t;
+}
+function pick(row, ...keys) {
+ for (const k of keys) {
+ const v = row[k];
+ if (v !== undefined && v !== null && String(v).trim() !== '') return clean(v);
+ }
+ return null;
+}
+function zip5(z) {
+ if (!z) return null;
+ const m = String(z).match(/\d{5}/);
+ return m ? m[0] : null;
+}
+function sha256(s) { return crypto.createHash('sha256').update(s).digest('hex'); }
+
+async function downloadIfNeeded() {
+ if (fs.existsSync(CACHE)) {
+ const ageDays = (Date.now() - fs.statSync(CACHE).mtimeMs) / 86400000;
+ if (ageDays < 14) {
+ console.log(`[hrsa] using cache (${ageDays.toFixed(1)}d old)`);
+ return;
+ }
+ }
+ fs.mkdirSync(path.dirname(CACHE), { recursive: true });
+ console.log(`[hrsa] downloading ${CSV_URL}`);
+ const res = await fetch(CSV_URL, { signal: AbortSignal.timeout(60000) });
+ if (!res.ok) throw new Error(`HRSA download failed: HTTP ${res.status}`);
+ const buf = Buffer.from(await res.arrayBuffer());
+ fs.writeFileSync(CACHE, buf);
+ console.log(`[hrsa] cached → ${CACHE} (${(buf.length/1e6).toFixed(1)} MB)`);
+}
+
+async function getSourceId() {
+ const r = await query('SELECT id FROM sources WHERE source_name = $1', [SOURCE_NAME]);
+ if (r.rowCount === 0) throw new Error(`source not seeded: ${SOURCE_NAME}`);
+ return r.rows[0].id;
+}
+
+async function startJob(sourceId) {
+ const r = await query(
+ `INSERT INTO scrape_jobs (source_id, job_label, status, started_at)
+ VALUES ($1,'hrsa:bulk','running',NOW()) RETURNING id`, [sourceId]);
+ return r.rows[0].id;
+}
+
+async function finishJob(jobId, fields) {
+ const sets = []; const params = []; 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 upsertFqhc(client, row) {
+ const r = await client.query(`
+ INSERT INTO organizations (name, type, address, city, state, zip, county, phone)
+ VALUES ($1, 'fqhc', $2, $3, $4, $5, $6, $7)
+ RETURNING id
+ `, [row.name, row.address, row.city, 'CA', row.zip, 'Los Angeles', row.phone]);
+ return r.rows[0].id;
+}
+
+async function main() {
+ await downloadIfNeeded();
+ const sourceId = await getSourceId();
+ const jobId = await startJob(sourceId);
+ const laZips = await laZipSet();
+
+ let scanned = 0, kept = 0, inserted = 0;
+
+ const parser = fs.createReadStream(CACHE).pipe(parse({
+ columns: true, skip_empty_lines: true, bom: true, relax_column_count: true,
+ }));
+
+ for await (const row of parser) {
+ scanned++;
+ const state = pick(row, 'Site State Abbreviation', 'State Abbreviation', 'STATE', 'State');
+ if (state && state !== 'CA') continue;
+ const z = zip5(pick(row, 'Site Postal Code', 'ZIP', 'Postal Code'));
+ const county = pick(row, 'Site County Name', 'County', 'COUNTY');
+ const inLA = (z && laZips.has(z)) || /los angeles/i.test(county || '');
+ if (!inLA) continue;
+ kept++;
+
+ const fac = {
+ name: pick(row, 'Site Name', 'BHCMISID Site Name', 'Health Center Name'),
+ address: pick(row, 'Site Address', 'Address', 'Street Address'),
+ city: pick(row, 'Site City', 'City'),
+ zip: z,
+ phone: pick(row, 'Site Telephone Number', 'Phone Number', 'Telephone'),
+ };
+ if (!fac.name) continue;
+
+ try {
+ await withTx(async (client) => {
+ const id = await upsertFqhc(client, fac);
+ const json = JSON.stringify(row);
+ const hash = sha256(json + '|organization|' + id);
+ await client.query(`
+ INSERT INTO raw_records
+ (source_id, source_url, entity_type, entity_id, raw_json, hash)
+ VALUES ($1,$2,'organization',$3,$4::jsonb,$5)
+ ON CONFLICT (hash) DO NOTHING
+ `, [sourceId, SOURCE_URL, id, json, hash]);
+ inserted++;
+ });
+ } catch (e) {
+ console.error(`[hrsa] row error: ${e.message}`);
+ }
+ }
+
+ await finishJob(jobId, {
+ status: 'done', records_found: scanned,
+ records_inserted: inserted, records_skipped: scanned - kept,
+ });
+ console.log(`[hrsa] done. scanned=${scanned} la_kept=${kept} upserted=${inserted}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[hrsa] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/agents/ingest-agent/importers/mbc.js b/agents/ingest-agent/importers/mbc.js
new file mode 100644
index 0000000..5152370
--- /dev/null
+++ b/agents/ingest-agent/importers/mbc.js
@@ -0,0 +1,248 @@
+#!/usr/bin/env node
+/**
+ * California Medical Board (MBC) license importer — Stage 2.
+ *
+ * Source: https://www.mbc.ca.gov/Resources/Statistics/Public-Information.aspx
+ * "Profession Specific Public Information File" (free, weekly refresh).
+ * Currently published as Excel (.xlsx) — historically Access (.mdb).
+ *
+ * This importer reads a CSV that the user has converted from the MBC bulk file
+ * to:
+ * data/mbc/mbc_licenses.csv
+ * (Use Excel "Save As CSV" or `mdb-export <file.mdb> licenses > mbc_licenses.csv`.)
+ *
+ * Expected columns (MBC uses several historical schemas — we adapt to whichever
+ * is present):
+ * LICENSE TYPE / License Type
+ * LICENSE NUMBER / License #
+ * LICENSE STATUS / Status
+ * ISSUE DATE
+ * EXPIRATION DATE
+ * LAST NAME
+ * FIRST NAME
+ * MIDDLE NAME
+ * SCHOOL NAME / SCHOOL OF GRADUATION
+ * GRADUATION YEAR
+ * ADDRESS LINE 1
+ * CITY
+ * STATE
+ * ZIP
+ *
+ * Match strategy (priority): license_number → name+last+CA license → fuzzy
+ * Updates professionals: license_status, license_issue_date, license_expiration_date,
+ * medical_school, graduation_year, license_type. Bumps source_confidence_score.
+ */
+const fs = require('node:fs');
+const path = require('node:path');
+const crypto = require('node:crypto');
+const { parse } = require('csv-parse');
+const { pool, query, withTx } = require('../../shared/db');
+
+const SOURCE_NAME = 'CA Medical Board (MBC) — Access DB';
+const SOURCE_URL = 'https://www.mbc.ca.gov/Resources/Statistics/Public-Information.aspx';
+const INPUT_CSV = process.env.MBC_CSV
+ || path.resolve(__dirname, '../../../data/mbc/mbc_licenses.csv');
+
+function clean(s) {
+ if (s === undefined || s === null) return null;
+ const t = String(s).trim();
+ return t.length === 0 ? null : t;
+}
+
+function pick(row, ...keys) {
+ for (const k of keys) {
+ const v = row[k];
+ if (v !== undefined && v !== null && String(v).trim() !== '') return clean(v);
+ }
+ return null;
+}
+
+function toIsoDate(s) {
+ if (!s) return null;
+ const us = s.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);
+ if (us) return `${us[3]}-${us[1].padStart(2,'0')}-${us[2].padStart(2,'0')}`;
+ const iso = s.match(/^(\d{4})-(\d{2})-(\d{2})$/);
+ return iso ? s : null;
+}
+
+function toYear(s) {
+ if (!s) return null;
+ const m = String(s).match(/(\d{4})/);
+ return m ? Number(m[1]) : null;
+}
+
+function sha256(s) { return crypto.createHash('sha256').update(s).digest('hex'); }
+
+async function getSourceId() {
+ const r = await query('SELECT id FROM sources WHERE source_name = $1', [SOURCE_NAME]);
+ if (r.rowCount === 0) throw new Error(`source not seeded: ${SOURCE_NAME}`);
+ return r.rows[0].id;
+}
+
+async function startJob(sourceId, label) {
+ const r = await query(
+ `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, fields) {
+ const sets = []; const params = []; 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 findProfessional(client, mbc) {
+ // Priority 1: license_number direct match (already populated by NPI's CA license).
+ if (mbc.license_number) {
+ const r = await client.query(
+ `SELECT id FROM professionals WHERE license_number = $1 LIMIT 2`,
+ [mbc.license_number]
+ );
+ if (r.rowCount === 1) return r.rows[0].id;
+ }
+ // Priority 2: name + license_number fuzzy.
+ if (mbc.last && mbc.first && mbc.license_number) {
+ const r = await client.query(
+ `SELECT id FROM professionals
+ WHERE LOWER(last_name) = LOWER($1)
+ AND LOWER(first_name) = LOWER($2)
+ AND (license_number = $3 OR license_number IS NULL)
+ LIMIT 2`,
+ [mbc.last, mbc.first, mbc.license_number]
+ );
+ if (r.rowCount === 1) return r.rows[0].id;
+ }
+ // Priority 3: exact name match if uniquely identifying in DB.
+ if (mbc.last && mbc.first) {
+ const r = await client.query(
+ `SELECT id FROM professionals
+ WHERE LOWER(last_name) = LOWER($1)
+ AND LOWER(first_name) = LOWER($2)
+ LIMIT 2`,
+ [mbc.last, mbc.first]
+ );
+ if (r.rowCount === 1) return r.rows[0].id;
+ }
+ return null;
+}
+
+async function applyMbc(client, professionalId, mbc, sourceId) {
+ await client.query(`
+ UPDATE professionals SET
+ license_number = COALESCE($2, license_number),
+ license_type = COALESCE($3, license_type),
+ license_status = COALESCE($4, license_status),
+ license_issue_date = COALESCE($5, license_issue_date),
+ license_expiration_date = COALESCE($6, license_expiration_date),
+ medical_school = COALESCE($7, medical_school),
+ graduation_year = COALESCE($8, graduation_year),
+ source_confidence_score = GREATEST(COALESCE(source_confidence_score,0), 0.85),
+ updated_at = NOW()
+ WHERE id = $1
+ `, [
+ professionalId,
+ mbc.license_number,
+ mbc.license_type,
+ mbc.license_status,
+ mbc.issue_date,
+ mbc.expiration_date,
+ mbc.school,
+ mbc.graduation_year,
+ ]);
+
+ // Provenance.
+ const json = JSON.stringify(mbc);
+ const hash = sha256(json + '|professional|' + professionalId);
+ await client.query(`
+ INSERT INTO raw_records (source_id, source_url, entity_type, entity_id, raw_json, hash)
+ VALUES ($1,$2,'professional',$3,$4::jsonb,$5)
+ ON CONFLICT (hash) DO NOTHING
+ `, [sourceId, SOURCE_URL, professionalId, json, hash]);
+}
+
+function rowToMbc(row) {
+ return {
+ license_type: pick(row, 'LICENSE TYPE', 'License Type'),
+ license_number: pick(row, 'LICENSE NUMBER', 'License #', 'License Number'),
+ license_status: pick(row, 'LICENSE STATUS', 'Status', 'License Status'),
+ issue_date: toIsoDate(pick(row, 'ISSUE DATE', 'Issue Date')),
+ expiration_date: toIsoDate(pick(row, 'EXPIRATION DATE', 'Expiration Date')),
+ last: pick(row, 'LAST NAME', 'Last Name'),
+ first: pick(row, 'FIRST NAME', 'First Name'),
+ middle: pick(row, 'MIDDLE NAME', 'Middle Name'),
+ school: pick(row, 'SCHOOL NAME', 'SCHOOL OF GRADUATION', 'School Name', 'School'),
+ graduation_year: toYear(pick(row, 'GRADUATION YEAR', 'Graduation Year', 'GRAD YEAR')),
+ address1: pick(row, 'ADDRESS LINE 1', 'Address Line 1', 'ADDRESS'),
+ city: pick(row, 'CITY', 'City'),
+ state: pick(row, 'STATE', 'State'),
+ zip: pick(row, 'ZIP', 'Zip', 'ZIP CODE'),
+ };
+}
+
+async function main() {
+ if (!fs.existsSync(INPUT_CSV)) {
+ console.error(`[mbc] input missing: ${INPUT_CSV}
+Steps to provide it (free, no key, no money):
+ 1. Visit ${SOURCE_URL}
+ 2. Download the "Profession Specific Public Information" file for the
+ Medical Board (License Type: M.D., D.O., or full set).
+ 3. If it's .xlsx → open in Excel/Numbers, Save As CSV → ${INPUT_CSV}
+ If it's .mdb → 'brew install mdb-tools' then
+ mdb-export your_file.mdb LICENSES > ${INPUT_CSV}`);
+ process.exit(2);
+ }
+
+ const sourceId = await getSourceId();
+ const jobId = await startJob(sourceId, 'mbc:bulk');
+
+ let scanned = 0, matched = 0, updated = 0, unmatched = 0;
+
+ const parser = fs.createReadStream(INPUT_CSV).pipe(parse({
+ columns: true, skip_empty_lines: true, bom: true, relax_column_count: true,
+ }));
+
+ for await (const row of parser) {
+ scanned++;
+ const mbc = rowToMbc(row);
+ if (!mbc.license_number && !(mbc.last && mbc.first)) continue;
+
+ try {
+ await withTx(async (client) => {
+ const id = await findProfessional(client, mbc);
+ if (!id) { unmatched++; return; }
+ matched++;
+ await applyMbc(client, id, mbc, sourceId);
+ updated++;
+ });
+ } catch (e) {
+ console.error(`[mbc] row error: ${e.message}`);
+ }
+
+ if (scanned % 5000 === 0) {
+ console.log(`[mbc] scanned=${scanned} matched=${matched} unmatched=${unmatched}`);
+ }
+ }
+
+ await finishJob(jobId, {
+ status: 'done',
+ records_found: scanned,
+ records_inserted: 0,
+ records_updated: updated,
+ records_skipped: unmatched,
+ });
+
+ console.log(`[mbc] done. scanned=${scanned} matched=${matched} updated=${updated} unmatched=${unmatched}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[mbc] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/agents/ingest-agent/importers/npi.js b/agents/ingest-agent/importers/npi.js
new file mode 100644
index 0000000..4857136
--- /dev/null
+++ b/agents/ingest-agent/importers/npi.js
@@ -0,0 +1,372 @@
+#!/usr/bin/env node
+/**
+ * NPI bulk importer — Stage 1 of the Professional Directory build.
+ *
+ * Streams the NPPES NPI bulk CSV (downloaded separately to ./data/nppes/),
+ * filters to state=CA + LA County ZIPs, and upserts:
+ * - Type 1 (individual) → professionals
+ * - Type 2 (organization) → organizations
+ * Plus professional_locations, phones, professional_specialties, raw_records.
+ *
+ * Sources: https://download.cms.gov/nppes/NPI_Files.html (public domain, no key)
+ *
+ * Smoke mode (SMOKE=1) reads agents/ingest-agent/importers/__fixtures__/npi_smoke.csv
+ * — 5 synthetic LA-county rows — so the pipeline can be exercised end-to-end
+ * without downloading the ~6 GB bulk file.
+ */
+const fs = require('node:fs');
+const path = require('node:path');
+const crypto = require('node:crypto');
+const { parse } = require('csv-parse');
+const { pool, query, withTx } = require('../../shared/db');
+const { laZipSet } = require('../../shared/la-zips');
+
+const SMOKE = process.env.SMOKE === '1';
+const FIXTURE = path.join(__dirname, '__fixtures__/npi_smoke.csv');
+const DATA_DIR = path.resolve(__dirname, '../../../data/nppes');
+
+const SOURCE_NAME = 'NPPES NPI Registry (Bulk)';
+
+// ─── Helpers ────────────────────────────────────────────────────────────────
+
+function sha256(s) { return crypto.createHash('sha256').update(s).digest('hex'); }
+
+function clean(s) {
+ if (s === undefined || s === null) return null;
+ const t = String(s).trim();
+ return t.length === 0 ? null : t;
+}
+
+function zip5(z) {
+ if (!z) return null;
+ const m = String(z).match(/\d{5}/);
+ return m ? m[0] : null;
+}
+
+function fullName(row) {
+ const parts = [
+ row['Provider Name Prefix Text'],
+ row['Provider First Name'],
+ row['Provider Middle Name'],
+ row['Provider Last Name (Legal Name)'],
+ row['Provider Name Suffix Text'],
+ ].map(clean).filter(Boolean);
+ return parts.join(' ');
+}
+
+function pickLicense(row) {
+ // Find the first CA license. NPI rows can have license_1 .. license_15.
+ for (let i = 1; i <= 15; i++) {
+ const n = clean(row[`Provider License Number_${i}`]);
+ const s = clean(row[`Provider License Number State Code_${i}`]);
+ if (n && s === 'CA') return { license_number: n };
+ }
+ return { license_number: null };
+}
+
+function pickTaxonomies(row) {
+ const out = [];
+ for (let i = 1; i <= 15; i++) {
+ const code = clean(row[`Healthcare Provider Taxonomy Code_${i}`]);
+ if (!code) continue;
+ const isPrimary = clean(row[`Healthcare Provider Primary Taxonomy Switch_${i}`]) === 'Y';
+ out.push({ code, is_primary: isPrimary });
+ }
+ // Prefer the primary; fall back to first.
+ out.sort((a, b) => Number(b.is_primary) - Number(a.is_primary));
+ return out;
+}
+
+function pickPracticeAddress(row) {
+ return {
+ address: [clean(row['Provider First Line Business Practice Location Address']),
+ clean(row['Provider Second Line Business Practice Location Address'])]
+ .filter(Boolean).join(', ') || null,
+ city: clean(row['Provider Business Practice Location Address City Name']),
+ state: clean(row['Provider Business Practice Location Address State Name']),
+ zip: zip5(row['Provider Business Practice Location Address Postal Code']),
+ phone: clean(row['Provider Business Practice Location Address Telephone Number']),
+ };
+}
+
+function specialtyName(taxonomyCode, taxonomyMap) {
+ return taxonomyMap.get(taxonomyCode) || null;
+}
+
+// ─── Upserts ────────────────────────────────────────────────────────────────
+
+async function upsertProfessional(client, row, src) {
+ const npi = clean(row['NPI']);
+ if (!npi) return null;
+
+ const license = pickLicense(row);
+ const taxonomies = pickTaxonomies(row);
+ const primaryTax = taxonomies[0]?.code || null;
+
+ const enumDate = clean(row['Provider Enumeration Date']);
+ // CMS NPI dates are MM/DD/YYYY
+ const issueDate = enumDate ? toIsoDate(enumDate) : null;
+
+ const params = [
+ fullName(row),
+ clean(row['Provider First Name']),
+ clean(row['Provider Last Name (Legal Name)']),
+ clean(row['Provider Middle Name']),
+ clean(row['Provider Name Suffix Text']),
+ clean(row['Provider Credential Text']),
+ license.license_number,
+ null, // license_type — filled by DCA/MBC stages
+ null, // license_status
+ issueDate,
+ npi,
+ clean(row['Provider Gender Code']),
+ primaryTax,
+ 0.30, // initial NPI-only confidence; raised by later stages
+ ];
+ if (process.env.NPI_DEBUG === '1') {
+ console.log('[npi] params lengths =', params.map(p => p === null ? 'null' : `'${p}'(${String(p).length})`));
+ }
+ const r = await client.query(`
+ INSERT INTO professionals (
+ full_name, first_name, last_name, middle_name, suffix, title,
+ license_number, license_type, license_status, license_issue_date,
+ npi_number, gender, primary_specialty,
+ source_confidence_score
+ ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
+ ON CONFLICT (npi_number) DO UPDATE SET
+ full_name = EXCLUDED.full_name,
+ first_name = EXCLUDED.first_name,
+ last_name = EXCLUDED.last_name,
+ middle_name = EXCLUDED.middle_name,
+ license_number = COALESCE(EXCLUDED.license_number, professionals.license_number),
+ primary_specialty = COALESCE(EXCLUDED.primary_specialty, professionals.primary_specialty),
+ updated_at = NOW()
+ RETURNING id
+ `, params);
+ return r.rows[0].id;
+}
+
+async function upsertOrganization(client, row, src) {
+ const npi = clean(row['NPI']);
+ if (!npi) return null;
+
+ const orgName = clean(row['Provider Organization Name (Legal Business Name)']);
+ if (!orgName) return null;
+
+ const addr = pickPracticeAddress(row);
+
+ const r = await client.query(`
+ INSERT INTO organizations (
+ name, type, address, city, state, zip, county, phone, npi_number
+ ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
+ ON CONFLICT (npi_number) DO UPDATE SET
+ name = EXCLUDED.name,
+ address = COALESCE(EXCLUDED.address, organizations.address),
+ city = COALESCE(EXCLUDED.city, organizations.city),
+ zip = COALESCE(EXCLUDED.zip, organizations.zip),
+ phone = COALESCE(EXCLUDED.phone, organizations.phone),
+ updated_at = NOW()
+ RETURNING id
+ `, [
+ orgName,
+ 'medical_group', // best guess until Stage 4 fills facility-type rows
+ addr.address,
+ addr.city,
+ addr.state,
+ addr.zip,
+ 'Los Angeles',
+ addr.phone,
+ npi,
+ ]);
+ return r.rows[0].id;
+}
+
+async function upsertProfessionalLocation(client, professionalId, row, sourceUrl) {
+ if (!professionalId) return;
+ const addr = pickPracticeAddress(row);
+ if (!addr.address && !addr.zip) return;
+
+ await client.query(`
+ INSERT INTO professional_locations (
+ professional_id, role, address, phone, source_url, is_primary, last_verified_at
+ ) VALUES ($1,$2,$3,$4,$5,TRUE,NOW())
+ `, [professionalId, 'attending', addr.address, addr.phone, sourceUrl]);
+
+ if (addr.phone) {
+ await client.query(`
+ INSERT INTO phones (professional_id, phone, phone_type, source_url, last_verified_at)
+ VALUES ($1,$2,'office',$3,NOW())
+ `, [professionalId, addr.phone, sourceUrl]);
+ }
+}
+
+async function attachSpecialties(client, professionalId, row, taxonomyMap) {
+ if (!professionalId) return;
+ const taxonomies = pickTaxonomies(row);
+ for (const tax of taxonomies) {
+ // Make sure a specialties row exists for this taxonomy code.
+ const name = specialtyName(tax.code, taxonomyMap) || tax.code;
+ const sp = await client.query(`
+ INSERT INTO specialties (name, taxonomy_code)
+ VALUES ($1,$2)
+ ON CONFLICT (taxonomy_code) DO UPDATE SET name = EXCLUDED.name
+ RETURNING id
+ `, [name, tax.code]);
+ await client.query(`
+ INSERT INTO professional_specialties (professional_id, specialty_id, source, confidence_score)
+ VALUES ($1,$2,'NPI',$3)
+ ON CONFLICT (professional_id, specialty_id) DO UPDATE SET source = EXCLUDED.source
+ `, [professionalId, sp.rows[0].id, tax.is_primary ? 0.90 : 0.60]);
+ }
+}
+
+async function stashRaw(client, sourceId, row, entityType, entityId) {
+ const json = JSON.stringify(row);
+ const hash = sha256(json + '|' + entityType + '|' + (entityId || ''));
+ await client.query(`
+ INSERT INTO raw_records (source_id, source_url, entity_type, entity_id, raw_json, hash)
+ VALUES ($1,$2,$3,$4,$5::jsonb,$6)
+ ON CONFLICT (hash) DO NOTHING
+ `, [
+ sourceId,
+ 'https://download.cms.gov/nppes/NPI_Files.html',
+ entityType,
+ entityId,
+ json,
+ hash,
+ ]);
+}
+
+function toIsoDate(s) {
+ // Try MM/DD/YYYY first (NPPES default), then YYYY-MM-DD.
+ const us = s.match(/^(\d{2})\/(\d{2})\/(\d{4})$/);
+ if (us) return `${us[3]}-${us[1]}-${us[2]}`;
+ const iso = s.match(/^(\d{4})-(\d{2})-(\d{2})$/);
+ return iso ? s : null;
+}
+
+// ─── Main ───────────────────────────────────────────────────────────────────
+
+async function loadTaxonomyMap() {
+ // Populated by seed_specialties — fall back to empty map.
+ const r = await query('SELECT taxonomy_code, name FROM specialties WHERE taxonomy_code IS NOT NULL', []);
+ return new Map(r.rows.map(r => [r.taxonomy_code, r.name]));
+}
+
+async function getSourceId() {
+ const r = await query('SELECT id FROM sources WHERE source_name = $1', [SOURCE_NAME]);
+ if (r.rowCount === 0) throw new Error(`Source not seeded: ${SOURCE_NAME}`);
+ return r.rows[0].id;
+}
+
+async function startJob(sourceId, label) {
+ const r = await query(`
+ 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, fields) {
+ const sets = [];
+ const params = [];
+ 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 pickInputCsv() {
+ if (SMOKE) return FIXTURE;
+ if (!fs.existsSync(DATA_DIR)) {
+ throw new Error(`Bulk NPI dir missing: ${DATA_DIR}\n` +
+ `Download the latest npidata_pfile_*.csv from\n` +
+ ` https://download.cms.gov/nppes/NPI_Files.html\n` +
+ `and place it in ${DATA_DIR}/.\n` +
+ `For a quick smoke test instead, run: SMOKE=1 npm run ingest:npi`);
+ }
+ const csvs = fs.readdirSync(DATA_DIR)
+ .filter(f => /^npidata_pfile.*\.csv$/i.test(f))
+ .filter(f => !/_fileheader\.csv$/i.test(f)) // exclude the 12KB header-only file
+ .sort();
+ if (csvs.length === 0) throw new Error(`No npidata_pfile_*.csv in ${DATA_DIR}`);
+ return path.join(DATA_DIR, csvs[csvs.length - 1]);
+}
+
+async function main() {
+ console.log(`[npi] mode=${SMOKE ? 'smoke' : 'bulk'}`);
+ const inputCsv = pickInputCsv();
+ console.log(`[npi] input = ${inputCsv}`);
+
+ const sourceId = await getSourceId();
+ const jobId = await startJob(sourceId, SMOKE ? 'npi:smoke' : 'npi:bulk');
+ const taxonomyMap = await loadTaxonomyMap();
+ const laZips = await laZipSet();
+
+ let found = 0, inserted = 0, updated = 0, skipped = 0;
+
+ const parser = fs.createReadStream(inputCsv).pipe(parse({
+ columns: true,
+ skip_empty_lines: true,
+ relax_column_count: true,
+ bom: true,
+ }));
+
+ for await (const row of parser) {
+ found++;
+
+ const entityType = clean(row['Entity Type Code']); // '1' or '2'
+ const stateCode = clean(row['Provider Business Practice Location Address State Name']);
+ const z = zip5(row['Provider Business Practice Location Address Postal Code']);
+
+ if (stateCode && stateCode !== 'CA') { skipped++; continue; }
+ if (z && !laZips.has(z)) { skipped++; continue; }
+ if (!stateCode && !z) { skipped++; continue; } // no usable address
+
+ try {
+ await withTx(async (client) => {
+ if (entityType === '1') {
+ const id = await upsertProfessional(client, row, SOURCE_NAME);
+ await upsertProfessionalLocation(client, id, row, 'https://download.cms.gov/nppes/NPI_Files.html');
+ await attachSpecialties(client, id, row, taxonomyMap);
+ await stashRaw(client, sourceId, row, 'professional', id);
+ inserted++;
+ } else if (entityType === '2') {
+ const id = await upsertOrganization(client, row, SOURCE_NAME);
+ await stashRaw(client, sourceId, row, 'organization', id);
+ inserted++;
+ } else {
+ skipped++;
+ }
+ });
+ } catch (e) {
+ console.error(`[npi] row error npi=${row['NPI']}: ${e.message} (col=${e.column}, schemaTable=${e.table})`);
+ skipped++;
+ }
+
+ if (found % 5000 === 0) {
+ console.log(`[npi] scanned=${found} kept=${inserted} skipped=${skipped}`);
+ }
+ }
+
+ await finishJob(jobId, {
+ status: 'done',
+ records_found: found,
+ records_inserted: inserted,
+ records_updated: updated,
+ records_skipped: skipped,
+ });
+
+ console.log(`[npi] done. scanned=${found} kept=${inserted} skipped=${skipped}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[npi] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/agents/shared/compliance.js b/agents/shared/compliance.js
new file mode 100644
index 0000000..f62f4de
--- /dev/null
+++ b/agents/shared/compliance.js
@@ -0,0 +1,140 @@
+/**
+ * Professional Directory — Compliance utilities.
+ *
+ * Every crawler MUST go through fetchCompliant() so we get:
+ * 1. robots.txt enforcement (per-host cache, 24 h TTL)
+ * 2. per-host rate limit (default 0.5 rps; configurable per source)
+ * 3. consistent User-Agent string with contact info
+ * 4. exponential backoff on 4xx/5xx
+ * 5. opt-out filter helpers for downstream API output
+ */
+const path = require('path');
+require('dotenv').config({ path: path.resolve(__dirname, '../../.env') });
+const { fetch } = require('undici');
+const robotsParser = require('robots-parser');
+
+const USER_AGENT = process.env.USER_AGENT
+ || 'ProfessionalDirectoryBot/0.1 (research; contact: steveabramsdesigns@gmail.com)';
+const DEFAULT_RPS = parseFloat(process.env.DEFAULT_RATE_LIMIT_RPS || '0.5');
+
+// ─── Per-host robots.txt cache ───────────────────────────────────────────────
+
+const robotsCache = new Map(); // host -> { robots, fetchedAt }
+const ONE_DAY = 24 * 60 * 60 * 1000;
+
+async function getRobotsFor(urlStr) {
+ const u = new URL(urlStr);
+ const host = u.host;
+ const cached = robotsCache.get(host);
+ if (cached && (Date.now() - cached.fetchedAt) < ONE_DAY) return cached.robots;
+
+ const robotsUrl = `${u.protocol}//${host}/robots.txt`;
+ let body = '';
+ try {
+ const res = await fetch(robotsUrl, {
+ headers: { 'User-Agent': USER_AGENT },
+ signal: AbortSignal.timeout(10000),
+ });
+ if (res.ok) body = await res.text();
+ } catch (_) { /* treat as permissive on transport error */ }
+
+ const robots = robotsParser(robotsUrl, body);
+ robotsCache.set(host, { robots, fetchedAt: Date.now() });
+ return robots;
+}
+
+async function isAllowed(urlStr) {
+ const robots = await getRobotsFor(urlStr);
+ return robots.isAllowed(urlStr, USER_AGENT) !== false;
+}
+
+// ─── Per-host rate limiter (token-bucket-ish, simple gating) ─────────────────
+
+const lastFetchByHost = new Map(); // host -> ms timestamp
+const rpsByHost = new Map(); // host -> rps override
+
+function setHostRateLimit(host, rps) { rpsByHost.set(host, rps); }
+
+async function gateHost(host) {
+ const rps = rpsByHost.get(host) ?? DEFAULT_RPS;
+ const minIntervalMs = 1000 / rps;
+ const last = lastFetchByHost.get(host) || 0;
+ const now = Date.now();
+ const wait = Math.max(0, last + minIntervalMs - now);
+ if (wait > 0) await new Promise(r => setTimeout(r, wait));
+ lastFetchByHost.set(host, Date.now());
+}
+
+// ─── Public fetch wrapper ────────────────────────────────────────────────────
+
+async function fetchCompliant(urlStr, opts = {}) {
+ const u = new URL(urlStr);
+
+ if (opts.respectRobots !== false) {
+ const allowed = await isAllowed(urlStr);
+ if (!allowed) {
+ const err = new Error(`robots.txt disallows ${urlStr}`);
+ err.code = 'ROBOTS_DISALLOWED';
+ throw err;
+ }
+ }
+
+ await gateHost(u.host);
+
+ const headers = {
+ 'User-Agent': USER_AGENT,
+ Accept: opts.accept || 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
+ ...(opts.headers || {}),
+ };
+
+ let attempt = 0;
+ // Exponential backoff: 1s, 2s, 4s, then give up.
+ while (true) {
+ attempt++;
+ let res;
+ try {
+ res = await fetch(urlStr, {
+ headers,
+ method: opts.method || 'GET',
+ body: opts.body,
+ redirect: opts.redirect || 'follow',
+ signal: AbortSignal.timeout(opts.timeoutMs || 30000),
+ });
+ } catch (e) {
+ if (attempt >= 4) throw e;
+ await new Promise(r => setTimeout(r, 1000 * Math.pow(2, attempt - 1)));
+ continue;
+ }
+
+ // CAPTCHA / Cloudflare detection — DO NOT bypass; abort the source.
+ if (res.status === 403 || res.status === 401) {
+ const text = await res.text().catch(() => '');
+ if (/cf-challenge|recaptcha|hcaptcha|captcha/i.test(text)) {
+ const err = new Error(`CAPTCHA / challenge detected at ${urlStr} — aborting per compliance policy`);
+ err.code = 'CAPTCHA_DETECTED';
+ err.body = text.slice(0, 4000);
+ throw err;
+ }
+ }
+
+ if (res.status >= 500 && attempt < 4) {
+ await new Promise(r => setTimeout(r, 1000 * Math.pow(2, attempt - 1)));
+ continue;
+ }
+
+ return res;
+ }
+}
+
+// ─── Opt-out helpers ─────────────────────────────────────────────────────────
+
+const OPT_OUT_WHERE = 'opted_out = false';
+
+module.exports = {
+ USER_AGENT,
+ DEFAULT_RPS,
+ fetchCompliant,
+ isAllowed,
+ setHostRateLimit,
+ OPT_OUT_WHERE,
+};
diff --git a/agents/shared/db.js b/agents/shared/db.js
new file mode 100644
index 0000000..6e447d7
--- /dev/null
+++ b/agents/shared/db.js
@@ -0,0 +1,53 @@
+/**
+ * Professional Directory — Shared DB pool.
+ * Mirrors Norma's shared/db.js. Targets doctor_professional_directory.
+ */
+require('dotenv').config({ path: require('path').resolve(__dirname, '../../.env') });
+const { Pool } = require('pg');
+
+const url = process.env.DATABASE_URL
+ || 'postgresql:///doctor_professional_directory?host=/tmp&user=stevestudio2';
+
+const pool = new Pool({
+ connectionString: url,
+ max: 10,
+ idleTimeoutMillis: 30000,
+ connectionTimeoutMillis: 5000,
+});
+
+pool.on('error', (err) => {
+ console.error(`[${new Date().toISOString()}] [db] Unexpected pool error:`, err.message);
+});
+
+async function query(text, params) {
+ const start = Date.now();
+ try {
+ const result = await pool.query(text, params);
+ const duration = Date.now() - start;
+ if (duration > 1000) {
+ console.warn(`[${new Date().toISOString()}] [db] Slow (${duration}ms): ${text.slice(0, 80)}…`);
+ }
+ return result;
+ } catch (err) {
+ console.error(`[${new Date().toISOString()}] [db] Query error: ${err.message}`);
+ console.error(`[${new Date().toISOString()}] [db] Query: ${text.slice(0, 120)}`);
+ throw err;
+ }
+}
+
+async function withTx(fn) {
+ const client = await pool.connect();
+ try {
+ await client.query('BEGIN');
+ const out = await fn(client);
+ await client.query('COMMIT');
+ return out;
+ } catch (e) {
+ await client.query('ROLLBACK');
+ throw e;
+ } finally {
+ client.release();
+ }
+}
+
+module.exports = { pool, query, withTx };
diff --git a/agents/shared/la-zips.js b/agents/shared/la-zips.js
new file mode 100644
index 0000000..e051444
--- /dev/null
+++ b/agents/shared/la-zips.js
@@ -0,0 +1,22 @@
+/**
+ * LA County ZIP set — sourced from db/la_zips.sql.
+ * Exposed both as Set<string> and as an async DB lookup for importers
+ * that prefer to filter inside Postgres.
+ */
+const { query } = require('./db');
+
+let cachedSet = null;
+
+async function laZipSet() {
+ if (cachedSet) return cachedSet;
+ const r = await query('SELECT zip FROM la_zips', []);
+ cachedSet = new Set(r.rows.map(row => row.zip));
+ return cachedSet;
+}
+
+function isLaZip(zip5, set) {
+ if (!zip5) return false;
+ return set.has(String(zip5).slice(0, 5));
+}
+
+module.exports = { laZipSet, isLaZip };
diff --git a/agents/shared/package.json b/agents/shared/package.json
new file mode 100644
index 0000000..e39cd21
--- /dev/null
+++ b/agents/shared/package.json
@@ -0,0 +1,12 @@
+{
+ "name": "@pd/shared",
+ "version": "0.1.0",
+ "private": true,
+ "main": "db.js",
+ "dependencies": {
+ "dotenv": "^16.4.7",
+ "pg": "^8.13.1",
+ "robots-parser": "^3.0.1",
+ "undici": "^7.2.0"
+ }
+}
diff --git a/db/la_zips.sql b/db/la_zips.sql
new file mode 100644
index 0000000..59a64c3
--- /dev/null
+++ b/db/la_zips.sql
@@ -0,0 +1,108 @@
+-- LA County ZIP codes (USPS-recognized, including PO box ZIPs).
+-- Used by importers to filter NPI/MBC/etc. rows down to LA County.
+-- Source: USPS + LA County GIS. Refresh annually.
+
+CREATE TABLE IF NOT EXISTS la_zips (
+ zip TEXT PRIMARY KEY,
+ city TEXT
+);
+
+INSERT INTO la_zips (zip, city) VALUES
+ ('90001','Los Angeles'),('90002','Los Angeles'),('90003','Los Angeles'),
+ ('90004','Los Angeles'),('90005','Los Angeles'),('90006','Los Angeles'),
+ ('90007','Los Angeles'),('90008','Los Angeles'),('90010','Los Angeles'),
+ ('90011','Los Angeles'),('90012','Los Angeles'),('90013','Los Angeles'),
+ ('90014','Los Angeles'),('90015','Los Angeles'),('90016','Los Angeles'),
+ ('90017','Los Angeles'),('90018','Los Angeles'),('90019','Los Angeles'),
+ ('90020','Los Angeles'),('90021','Los Angeles'),('90023','Los Angeles'),
+ ('90024','Los Angeles'),('90025','Los Angeles'),('90026','Los Angeles'),
+ ('90027','Los Angeles'),('90028','Los Angeles'),('90029','Los Angeles'),
+ ('90031','Los Angeles'),('90032','Los Angeles'),('90033','Los Angeles'),
+ ('90034','Los Angeles'),('90035','Los Angeles'),('90036','Los Angeles'),
+ ('90037','Los Angeles'),('90038','Los Angeles'),('90039','Los Angeles'),
+ ('90041','Los Angeles'),('90042','Los Angeles'),('90043','Los Angeles'),
+ ('90044','Los Angeles'),('90045','Los Angeles'),('90046','Los Angeles'),
+ ('90047','Los Angeles'),('90048','Los Angeles'),('90049','Los Angeles'),
+ ('90056','Los Angeles'),('90057','Los Angeles'),('90058','Los Angeles'),
+ ('90059','Los Angeles'),('90061','Los Angeles'),('90062','Los Angeles'),
+ ('90063','Los Angeles'),('90064','Los Angeles'),('90065','Los Angeles'),
+ ('90066','Los Angeles'),('90067','Los Angeles'),('90068','Los Angeles'),
+ ('90069','West Hollywood'),('90071','Los Angeles'),('90077','Los Angeles'),
+ ('90089','Los Angeles'),('90094','Playa Vista'),('90095','Los Angeles'),
+ ('90201','Bell Gardens'),('90202','Bell Gardens'),
+ ('90210','Beverly Hills'),('90211','Beverly Hills'),('90212','Beverly Hills'),
+ ('90220','Compton'),('90221','Compton'),('90222','Compton'),
+ ('90230','Culver City'),('90232','Culver City'),
+ ('90240','Downey'),('90241','Downey'),('90242','Downey'),
+ ('90245','El Segundo'),('90247','Gardena'),('90248','Gardena'),('90249','Gardena'),
+ ('90250','Hawthorne'),('90254','Hermosa Beach'),
+ ('90255','Huntington Park'),('90260','Lawndale'),
+ ('90262','Lynwood'),('90263','Malibu'),('90265','Malibu'),
+ ('90266','Manhattan Beach'),('90270','Maywood'),
+ ('90272','Pacific Palisades'),('90274','Palos Verdes Peninsula'),
+ ('90275','Rancho Palos Verdes'),('90277','Redondo Beach'),('90278','Redondo Beach'),
+ ('90280','South Gate'),('90290','Topanga'),('90291','Venice'),('90292','Marina del Rey'),
+ ('90293','Playa del Rey'),('90301','Inglewood'),('90302','Inglewood'),
+ ('90303','Inglewood'),('90304','Inglewood'),('90305','Inglewood'),
+ ('90401','Santa Monica'),('90402','Santa Monica'),('90403','Santa Monica'),
+ ('90404','Santa Monica'),('90405','Santa Monica'),
+ ('90501','Torrance'),('90502','Torrance'),('90503','Torrance'),
+ ('90504','Torrance'),('90505','Torrance'),
+ ('90601','Whittier'),('90602','Whittier'),('90603','Whittier'),
+ ('90604','Whittier'),('90605','Whittier'),('90606','Whittier'),
+ ('90630','Cypress'),('90631','La Habra'),('90638','La Mirada'),
+ ('90640','Montebello'),('90650','Norwalk'),('90660','Pico Rivera'),
+ ('90670','Santa Fe Springs'),('90680','Stanton'),
+ ('90701','Artesia'),('90703','Cerritos'),('90704','Avalon'),
+ ('90706','Bellflower'),('90710','Harbor City'),('90712','Lakewood'),
+ ('90713','Lakewood'),('90715','Lakewood'),('90716','Hawaiian Gardens'),
+ ('90717','Lomita'),('90723','Paramount'),('90731','San Pedro'),
+ ('90732','San Pedro'),('90744','Wilmington'),('90745','Carson'),
+ ('90746','Carson'),('90747','Carson'),('90802','Long Beach'),
+ ('90803','Long Beach'),('90804','Long Beach'),('90805','Long Beach'),
+ ('90806','Long Beach'),('90807','Long Beach'),('90808','Long Beach'),
+ ('90810','Long Beach'),('90813','Long Beach'),('90814','Long Beach'),
+ ('90815','Long Beach'),
+ ('91001','Altadena'),('91006','Arcadia'),('91007','Arcadia'),
+ ('91008','Duarte'),('91010','Duarte'),('91011','La Cañada Flintridge'),
+ ('91016','Monrovia'),('91020','Montrose'),('91024','Sierra Madre'),
+ ('91030','South Pasadena'),('91040','Sunland'),('91042','Tujunga'),
+ ('91046','Verdugo City'),('91101','Pasadena'),('91103','Pasadena'),
+ ('91104','Pasadena'),('91105','Pasadena'),('91106','Pasadena'),
+ ('91107','Pasadena'),('91108','San Marino'),
+ ('91201','Glendale'),('91202','Glendale'),('91203','Glendale'),
+ ('91204','Glendale'),('91205','Glendale'),('91206','Glendale'),
+ ('91207','Glendale'),('91208','Glendale'),('91210','Glendale'),
+ ('91214','La Crescenta-Montrose'),
+ ('91301','Agoura Hills'),('91302','Calabasas'),('91303','Canoga Park'),
+ ('91304','Canoga Park'),('91306','Winnetka'),('91307','West Hills'),
+ ('91311','Chatsworth'),('91316','Encino'),('91320','Newbury Park'),
+ ('91321','Newhall'),('91324','Northridge'),('91325','Northridge'),
+ ('91326','Porter Ranch'),('91330','Northridge'),('91331','Pacoima'),
+ ('91335','Reseda'),('91340','San Fernando'),('91342','Sylmar'),
+ ('91343','North Hills'),('91344','Granada Hills'),('91345','Mission Hills'),
+ ('91350','Santa Clarita'),('91351','Canyon Country'),('91352','Sun Valley'),
+ ('91354','Valencia'),('91355','Valencia'),('91356','Tarzana'),
+ ('91361','Westlake Village'),('91362','Thousand Oaks'),('91364','Woodland Hills'),
+ ('91367','Woodland Hills'),('91381','Stevenson Ranch'),('91382','Santa Clarita'),
+ ('91384','Castaic'),('91387','Canyon Country'),('91390','Santa Clarita'),
+ ('91401','Van Nuys'),('91402','Panorama City'),('91403','Sherman Oaks'),
+ ('91405','Van Nuys'),('91406','Van Nuys'),('91411','Van Nuys'),
+ ('91423','Sherman Oaks'),('91436','Encino'),('91501','Burbank'),
+ ('91502','Burbank'),('91504','Burbank'),('91505','Burbank'),
+ ('91506','Burbank'),('91601','North Hollywood'),('91602','North Hollywood'),
+ ('91604','Studio City'),('91605','North Hollywood'),('91606','North Hollywood'),
+ ('91607','Valley Village'),
+ ('91702','Azusa'),('91706','Baldwin Park'),('91709','Chino Hills'),
+ ('91711','Claremont'),('91722','Covina'),('91723','Covina'),
+ ('91724','Covina'),('91731','El Monte'),('91732','El Monte'),
+ ('91733','South El Monte'),('91739','Rancho Cucamonga'),
+ ('91740','Glendora'),('91741','Glendora'),('91744','La Puente'),
+ ('91745','Hacienda Heights'),('91746','La Puente'),('91748','Rowland Heights'),
+ ('91750','La Verne'),('91754','Monterey Park'),('91755','Monterey Park'),
+ ('91765','Diamond Bar'),('91766','Pomona'),('91767','Pomona'),
+ ('91768','Pomona'),('91770','Rosemead'),('91773','San Dimas'),
+ ('91775','San Gabriel'),('91776','San Gabriel'),('91780','Temple City'),
+ ('91789','Walnut'),('91790','West Covina'),('91791','West Covina'),
+ ('91792','West Covina'),('91801','Alhambra'),('91803','Alhambra')
+ON CONFLICT (zip) DO NOTHING;
diff --git a/db/migrations/0001_init.sql b/db/migrations/0001_init.sql
new file mode 100644
index 0000000..8c6866d
--- /dev/null
+++ b/db/migrations/0001_init.sql
@@ -0,0 +1,4 @@
+-- Migration 0001: initial schema. Idempotent (CREATE … IF NOT EXISTS).
+-- Equivalent to db/schema.sql; this file is what the migration runner applies.
+
+\i :schema_file
diff --git a/db/migrations/0002_seed_sources.sql b/db/migrations/0002_seed_sources.sql
new file mode 100644
index 0000000..7398ddb
--- /dev/null
+++ b/db/migrations/0002_seed_sources.sql
@@ -0,0 +1,141 @@
+-- Seed the sources registry. Idempotent (ON CONFLICT (source_name) DO UPDATE).
+
+INSERT INTO sources (source_name, source_type, base_url, terms_notes, allowed_method, rate_limit_rps, robots_txt_url) VALUES
+ -- Stage 1: NPI (THE primary anchor)
+ ('NPPES NPI Registry (Bulk)', 'bulk',
+ 'https://download.cms.gov/nppes/NPI_Files.html',
+ 'CMS public-domain monthly bulk file of all NPIs. Free, no key. Filter to state=CA + LA County ZIPs.',
+ 'bulk_download', NULL,
+ 'https://download.cms.gov/robots.txt'),
+
+ ('NPPES NPI Registry (API)', 'api',
+ 'https://npiregistry.cms.hhs.gov/api/',
+ 'Free CMS REST API for ad-hoc lookups (200 records/page max, no key). Use for re-verifies, not bulk loads.',
+ 'api', 5.0, NULL),
+
+ -- Stage 2: license enrichment
+ ('CA Medical Board (MBC) — Access DB', 'bulk',
+ 'https://www.mbc.ca.gov/Resources/Statistics/License-Lookup-System.aspx',
+ 'Weekly downloadable Microsoft Access DB of all CA physician licenses. Public records, free.',
+ 'bulk_download', NULL,
+ 'https://www.mbc.ca.gov/robots.txt'),
+
+ ('DCA License Search', 'api',
+ 'https://search.dca.ca.gov/',
+ 'CA Dept of Consumer Affairs license lookup — covers MD/DO/RN/PA/NP and more. JSON endpoint, polite-use.',
+ 'api', 1.0,
+ 'https://search.dca.ca.gov/robots.txt'),
+
+ -- Stage 3: CMS enrichment
+ ('CMS Care Compare (Doctors and Clinicians)', 'bulk',
+ 'https://data.cms.gov/provider-data/dataset/mj5m-pzi6',
+ 'Public CMS dataset, hospital affiliations + group practice + specialties. Bulk CSV.',
+ 'bulk_download', NULL, NULL),
+
+ ('CMS Open Payments', 'bulk',
+ 'https://openpaymentsdata.cms.gov/',
+ 'Sunshine Act payments to physicians from drug/device makers. Bulk CSV.',
+ 'bulk_download', NULL, NULL),
+
+ -- Stage 4: facilities
+ ('CA HCAI Hospital Annual Utilization', 'bulk',
+ 'https://hcai.ca.gov/data/healthcare-facility-data/',
+ 'Department of Health Care Access and Information — hospital licensure + utilization. Bulk CSV.',
+ 'bulk_download', NULL, NULL),
+
+ ('CDPH Health Facility Information Database', 'bulk',
+ 'https://data.chhs.ca.gov/dataset/healthcare-facility-locations',
+ 'CA Dept of Public Health — every licensed health facility in CA. Bulk CSV.',
+ 'bulk_download', NULL, NULL),
+
+ ('HRSA FQHC Locator', 'api',
+ 'https://data.hrsa.gov/data/download',
+ 'Federally Qualified Health Centers — bulk download + API. Free.',
+ 'bulk_download', NULL, NULL),
+
+ ('CA Secretary of State Business Search', 'api',
+ 'https://bizfileonline.sos.ca.gov/api/Records/businesssearch',
+ 'CA SOS business entity search — find professional corps / medical groups by name. Polite-use.',
+ 'api', 0.5,
+ 'https://bizfileonline.sos.ca.gov/robots.txt'),
+
+ -- Stage 5: hospital + practice crawlers (one source row per system; respect their robots)
+ ('UCLA Health (uclahealth.org)', 'crawler',
+ 'https://www.uclahealth.org/',
+ 'Public physician profiles. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://www.uclahealth.org/robots.txt'),
+
+ ('Cedars-Sinai (cedars-sinai.org)', 'crawler',
+ 'https://www.cedars-sinai.org/',
+ 'Public physician profiles. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://www.cedars-sinai.org/robots.txt'),
+
+ ('Kaiser SoCal (mydoctor.kaiserpermanente.org)', 'crawler',
+ 'https://mydoctor.kaiserpermanente.org/',
+ 'Public physician profiles. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://mydoctor.kaiserpermanente.org/robots.txt'),
+
+ ('Keck USC Medicine (keckmedicine.org)', 'crawler',
+ 'https://www.keckmedicine.org/',
+ 'Public physician profiles. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://www.keckmedicine.org/robots.txt'),
+
+ ('Providence SoCal (providence.org)', 'crawler',
+ 'https://www.providence.org/',
+ 'Public physician profiles. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://www.providence.org/robots.txt'),
+
+ ('Dignity Health SoCal (dignityhealth.org)', 'crawler',
+ 'https://www.dignityhealth.org/',
+ 'Public physician profiles. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://www.dignityhealth.org/robots.txt'),
+
+ ('MLK Community Healthcare (mlkch.org)', 'crawler',
+ 'https://www.mlkch.org/',
+ 'Public physician profiles. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://www.mlkch.org/robots.txt'),
+
+ ('Olive View-UCLA Medical Center (dhs.lacounty.gov/oliveview)', 'crawler',
+ 'https://dhs.lacounty.gov/olive-view-ucla-medical-center/',
+ 'LA County DHS — public hospital staff directory. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://dhs.lacounty.gov/robots.txt'),
+
+ ('Harbor-UCLA Medical Center (dhs.lacounty.gov/harbor-ucla)', 'crawler',
+ 'https://dhs.lacounty.gov/harbor-ucla-medical-center/',
+ 'LA County DHS — public hospital staff directory. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://dhs.lacounty.gov/robots.txt'),
+
+ ('Children''s Hospital Los Angeles (chla.org)', 'crawler',
+ 'https://www.chla.org/',
+ 'Public physician profiles. Robots-aware crawl, 0.5 rps.',
+ 'scrape_with_robots', 0.5,
+ 'https://www.chla.org/robots.txt'),
+
+ -- Stage 6: free geocoding
+ ('US Census Geocoding API', 'api',
+ 'https://geocoding.geo.census.gov/geocoder/',
+ 'Free, no key. Address → lat/lng. Polite-use ~1 rps.',
+ 'api', 1.0, NULL),
+
+ ('OpenStreetMap Nominatim', 'api',
+ 'https://nominatim.openstreetmap.org/',
+ 'Free, no key. Polite-use 1 rps strict, identify with User-Agent.',
+ 'api', 1.0,
+ 'https://nominatim.openstreetmap.org/robots.txt')
+
+ON CONFLICT (source_name) DO UPDATE SET
+ base_url = EXCLUDED.base_url,
+ terms_notes = EXCLUDED.terms_notes,
+ allowed_method = EXCLUDED.allowed_method,
+ rate_limit_rps = EXCLUDED.rate_limit_rps,
+ robots_txt_url = EXCLUDED.robots_txt_url,
+ last_checked_at = NOW();
diff --git a/db/schema.sql b/db/schema.sql
new file mode 100644
index 0000000..28d1524
--- /dev/null
+++ b/db/schema.sql
@@ -0,0 +1,188 @@
+-- Professional Directory — Doctors / LA County (v1)
+-- Compliance-first: every fact must be traceable to source_url; opt_out flag is honored on every public surface.
+
+CREATE EXTENSION IF NOT EXISTS pg_trgm;
+CREATE EXTENSION IF NOT EXISTS citext;
+
+-- ─── Reference tables ───────────────────────────────────────────────────────
+
+CREATE TABLE IF NOT EXISTS sources (
+ id BIGSERIAL PRIMARY KEY,
+ source_name TEXT UNIQUE NOT NULL,
+ source_type TEXT, -- bulk | api | crawler
+ base_url TEXT,
+ terms_notes TEXT,
+ allowed_method TEXT, -- bulk_download | api | scrape_with_robots
+ rate_limit_rps NUMERIC(6,2),
+ robots_txt_url TEXT,
+ last_checked_at TIMESTAMPTZ
+);
+
+CREATE TABLE IF NOT EXISTS specialties (
+ id BIGSERIAL PRIMARY KEY,
+ name TEXT NOT NULL,
+ taxonomy_code TEXT UNIQUE, -- NUCC code, e.g. 207RC0000X
+ parent_specialty TEXT
+);
+
+-- ─── Core entities ──────────────────────────────────────────────────────────
+
+CREATE TABLE IF NOT EXISTS professionals (
+ id BIGSERIAL PRIMARY KEY,
+ full_name TEXT NOT NULL,
+ first_name TEXT,
+ last_name TEXT,
+ middle_name TEXT,
+ suffix TEXT,
+ title TEXT, -- MD, DO, PA, NP, ...
+ license_number TEXT,
+ license_type TEXT,
+ license_status TEXT,
+ license_issue_date DATE,
+ license_expiration_date DATE,
+ npi_number VARCHAR(10) UNIQUE,
+ gender CHAR(1),
+ medical_school TEXT,
+ graduation_year INT,
+ years_experience_estimate INT,
+ primary_specialty TEXT,
+ secondary_specialties TEXT[],
+ bio TEXT,
+ profile_image_url TEXT,
+ source_confidence_score NUMERIC(3,2),
+ opted_out BOOLEAN NOT NULL DEFAULT FALSE,
+ opted_out_at TIMESTAMPTZ,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_professionals_last_first ON professionals(last_name, first_name);
+CREATE INDEX IF NOT EXISTS idx_professionals_full_trgm ON professionals USING gin (full_name gin_trgm_ops);
+CREATE INDEX IF NOT EXISTS idx_professionals_license ON professionals(license_number);
+CREATE INDEX IF NOT EXISTS idx_professionals_optout ON professionals(opted_out);
+
+CREATE TABLE IF NOT EXISTS organizations (
+ id BIGSERIAL PRIMARY KEY,
+ name TEXT NOT NULL,
+ type TEXT, -- hospital | clinic | private_practice | medical_group | surgery_center | urgent_care
+ address TEXT,
+ city TEXT,
+ state CHAR(2) DEFAULT 'CA',
+ zip TEXT,
+ county TEXT,
+ lat DOUBLE PRECISION,
+ lng DOUBLE PRECISION,
+ geocoded_at TIMESTAMPTZ,
+ phone TEXT,
+ website TEXT,
+ google_place_id TEXT UNIQUE, -- reserved; not populated in zero-dollar build
+ rating NUMERIC(3,2), -- reserved
+ review_count INT, -- reserved
+ npi_number VARCHAR(10) UNIQUE, -- Type 2 NPI
+ hcai_id TEXT,
+ cdph_license TEXT,
+ opted_out BOOLEAN NOT NULL DEFAULT FALSE,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX IF NOT EXISTS idx_organizations_zip ON organizations(zip);
+CREATE INDEX IF NOT EXISTS idx_organizations_city ON organizations(city);
+CREATE INDEX IF NOT EXISTS idx_organizations_name_trgm ON organizations USING gin (name gin_trgm_ops);
+
+-- ─── Relationships ──────────────────────────────────────────────────────────
+
+CREATE TABLE IF NOT EXISTS professional_locations (
+ id BIGSERIAL PRIMARY KEY,
+ professional_id BIGINT REFERENCES professionals(id) ON DELETE CASCADE,
+ organization_id BIGINT REFERENCES organizations(id) ON DELETE SET NULL,
+ role TEXT, -- attending | partner | employed | privileges
+ address TEXT,
+ phone TEXT,
+ accepting_new_patients BOOLEAN,
+ appointment_url TEXT,
+ source_url TEXT,
+ is_primary BOOLEAN NOT NULL DEFAULT FALSE,
+ last_verified_at TIMESTAMPTZ
+);
+CREATE INDEX IF NOT EXISTS idx_prof_loc_professional ON professional_locations(professional_id);
+CREATE INDEX IF NOT EXISTS idx_prof_loc_organization ON professional_locations(organization_id);
+
+CREATE TABLE IF NOT EXISTS professional_specialties (
+ professional_id BIGINT REFERENCES professionals(id) ON DELETE CASCADE,
+ specialty_id BIGINT REFERENCES specialties(id) ON DELETE CASCADE,
+ source TEXT,
+ confidence_score NUMERIC(3,2),
+ PRIMARY KEY (professional_id, specialty_id)
+);
+
+CREATE TABLE IF NOT EXISTS emails (
+ id BIGSERIAL PRIMARY KEY,
+ professional_id BIGINT REFERENCES professionals(id) ON DELETE CASCADE,
+ organization_id BIGINT REFERENCES organizations(id) ON DELETE CASCADE,
+ email CITEXT NOT NULL,
+ email_type TEXT, -- personal_public | office | appointment | admin | unknown
+ source_url TEXT,
+ discovered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ last_verified_at TIMESTAMPTZ,
+ verification_status TEXT, -- unverified | smtp_ok | bounced | opt_out
+ CHECK (professional_id IS NOT NULL OR organization_id IS NOT NULL)
+);
+CREATE INDEX IF NOT EXISTS idx_emails_email ON emails(email);
+
+CREATE TABLE IF NOT EXISTS phones (
+ id BIGSERIAL PRIMARY KEY,
+ professional_id BIGINT REFERENCES professionals(id) ON DELETE CASCADE,
+ organization_id BIGINT REFERENCES organizations(id) ON DELETE CASCADE,
+ phone TEXT NOT NULL,
+ phone_type TEXT, -- office | appointment | fax | answering_service
+ source_url TEXT,
+ last_verified_at TIMESTAMPTZ,
+ CHECK (professional_id IS NOT NULL OR organization_id IS NOT NULL)
+);
+CREATE INDEX IF NOT EXISTS idx_phones_phone ON phones(phone);
+
+-- ─── Provenance tables ──────────────────────────────────────────────────────
+
+CREATE TABLE IF NOT EXISTS raw_records (
+ id BIGSERIAL PRIMARY KEY,
+ source_id BIGINT REFERENCES sources(id),
+ source_url TEXT,
+ entity_type TEXT, -- professional | organization | location | …
+ entity_id BIGINT,
+ raw_json JSONB,
+ raw_html_path TEXT,
+ fetched_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ hash TEXT UNIQUE NOT NULL,
+ http_status INT
+);
+CREATE INDEX IF NOT EXISTS idx_raw_records_entity ON raw_records(entity_type, entity_id);
+CREATE INDEX IF NOT EXISTS idx_raw_records_url ON raw_records(source_url);
+
+CREATE TABLE IF NOT EXISTS scrape_jobs (
+ id BIGSERIAL PRIMARY KEY,
+ source_id BIGINT REFERENCES sources(id),
+ job_label TEXT,
+ status TEXT NOT NULL DEFAULT 'queued', -- queued | running | done | failed | aborted_compliance
+ started_at TIMESTAMPTZ,
+ finished_at TIMESTAMPTZ,
+ records_found INT NOT NULL DEFAULT 0,
+ records_inserted INT NOT NULL DEFAULT 0,
+ records_updated INT NOT NULL DEFAULT 0,
+ records_skipped INT NOT NULL DEFAULT 0,
+ error_message TEXT,
+ checkpoint JSONB
+);
+CREATE INDEX IF NOT EXISTS idx_scrape_jobs_status ON scrape_jobs(status);
+
+-- ─── updated_at trigger ─────────────────────────────────────────────────────
+
+CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
+BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
+$$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS trg_professionals_updated ON professionals;
+CREATE TRIGGER trg_professionals_updated BEFORE UPDATE ON professionals
+ FOR EACH ROW EXECUTE FUNCTION set_updated_at();
+
+DROP TRIGGER IF EXISTS trg_organizations_updated ON organizations;
+CREATE TRIGGER trg_organizations_updated BEFORE UPDATE ON organizations
+ FOR EACH ROW EXECUTE FUNCTION set_updated_at();
diff --git a/docs/example-queries.sql b/docs/example-queries.sql
new file mode 100644
index 0000000..65e1313
--- /dev/null
+++ b/docs/example-queries.sql
@@ -0,0 +1,169 @@
+-- Professional Directory — example queries
+-- Run after Stage 1 (NPI ingestion). Each query honors opted_out = false.
+
+-- ─── 1. Where are doctors setting up private practice? ──────────────────────
+-- Top LA County ZIPs by physician count.
+SELECT
+ z.zip,
+ z.city,
+ COUNT(DISTINCT p.id) AS physicians,
+ COUNT(DISTINCT o.id) AS orgs,
+ COUNT(DISTINCT p.id) + COUNT(DISTINCT o.id) AS total_entities
+FROM la_zips z
+LEFT JOIN organizations o
+ ON o.zip = z.zip AND o.opted_out = false
+LEFT JOIN professional_locations pl
+ ON pl.address ILIKE '%' || z.zip || '%'
+LEFT JOIN professionals p
+ ON p.id = pl.professional_id AND p.opted_out = false
+GROUP BY z.zip, z.city
+ORDER BY total_entities DESC
+LIMIT 30;
+
+-- ─── 2. Beverly Hills only ──────────────────────────────────────────────────
+-- 90210 / 90211 / 90212 — all "Beverly Hills" in la_zips.
+SELECT
+ o.zip,
+ COUNT(DISTINCT o.id) AS orgs,
+ COUNT(DISTINCT pl.professional_id) AS distinct_doctors,
+ STRING_AGG(DISTINCT s.name, ', ' ORDER BY s.name) AS specialties_present
+FROM organizations o
+LEFT JOIN professional_locations pl ON pl.organization_id = o.id
+LEFT JOIN professionals p ON p.id = pl.professional_id
+LEFT JOIN professional_specialties ps ON ps.professional_id = p.id
+LEFT JOIN specialties s ON s.id = ps.specialty_id
+WHERE o.zip IN ('90210','90211','90212')
+ AND o.opted_out = false
+GROUP BY o.zip
+ORDER BY o.zip;
+
+-- ─── 3. Specialty heat map by city ──────────────────────────────────────────
+-- Where do dermatologists, cardiologists, plastic surgeons concentrate?
+SELECT
+ o.city,
+ COUNT(*) FILTER (WHERE s.name ILIKE '%Dermatolog%') AS dermatology,
+ COUNT(*) FILTER (WHERE s.name ILIKE '%Cardio%') AS cardiology,
+ COUNT(*) FILTER (WHERE s.name ILIKE '%Plastic%') AS plastic_surgery,
+ COUNT(*) FILTER (WHERE s.name ILIKE '%Pediatric%') AS pediatrics,
+ COUNT(*) FILTER (WHERE s.name ILIKE '%Psych%') AS psychiatry
+FROM organizations o
+LEFT JOIN professional_locations pl ON pl.organization_id = o.id
+LEFT JOIN professional_specialties ps ON ps.professional_id = pl.professional_id
+LEFT JOIN specialties s ON s.id = ps.specialty_id
+WHERE o.opted_out = false
+GROUP BY o.city
+ORDER BY dermatology + cardiology + plastic_surgery DESC
+LIMIT 20;
+
+-- ─── 4. Solo practice vs group practice mix per city ───────────────────────
+SELECT
+ o.city,
+ COUNT(*) FILTER (WHERE o.type = 'private_practice') AS solo_practices,
+ COUNT(*) FILTER (WHERE o.type = 'medical_group') AS groups,
+ COUNT(*) FILTER (WHERE o.type = 'hospital') AS hospitals,
+ COUNT(*) FILTER (WHERE o.type = 'urgent_care') AS urgent_cares,
+ COUNT(*) FILTER (WHERE o.type = 'surgery_center') AS surgery_centers
+FROM organizations o
+WHERE o.opted_out = false AND o.county = 'Los Angeles'
+GROUP BY o.city
+ORDER BY solo_practices + groups DESC
+LIMIT 30;
+
+-- ─── 5. "Find me a top-confidence cardiologist in 90210" ────────────────────
+SELECT
+ p.full_name,
+ p.npi_number,
+ p.license_number,
+ p.license_status,
+ pl.address,
+ pl.phone,
+ o.name AS practice_name,
+ o.website,
+ p.source_confidence_score
+FROM professionals p
+JOIN professional_specialties ps ON ps.professional_id = p.id
+JOIN specialties s ON s.id = ps.specialty_id
+JOIN professional_locations pl ON pl.professional_id = p.id
+LEFT JOIN organizations o ON o.id = pl.organization_id
+WHERE p.opted_out = false
+ AND s.name ILIKE '%Cardio%'
+ AND (pl.address ILIKE '%90210%' OR o.zip IN ('90210','90211','90212'))
+ AND p.source_confidence_score >= 0.85
+ORDER BY p.source_confidence_score DESC, p.last_name;
+
+-- ─── 6. SEO landing page: "Best dermatologists in West Hollywood" ──────────
+SELECT
+ p.full_name,
+ p.medical_school,
+ p.graduation_year,
+ pl.address,
+ pl.phone,
+ o.website,
+ p.profile_image_url,
+ p.bio
+FROM professionals p
+JOIN professional_specialties ps ON ps.professional_id = p.id
+JOIN specialties s ON s.id = ps.specialty_id
+JOIN professional_locations pl ON pl.professional_id = p.id
+LEFT JOIN organizations o ON o.id = pl.organization_id
+WHERE p.opted_out = false
+ AND s.name ILIKE '%Dermatolog%'
+ AND (pl.address ILIKE '%West Hollywood%' OR o.city = 'West Hollywood')
+ AND p.license_status = 'Active'
+ORDER BY p.source_confidence_score DESC NULLS LAST
+LIMIT 25;
+
+-- ─── 7. "Where are the hospitals, by ZIP density?" ─────────────────────────
+SELECT
+ o.zip,
+ z.city,
+ COUNT(*) AS hospitals,
+ STRING_AGG(o.name, ' | ' ORDER BY o.name) AS hospital_names
+FROM organizations o
+LEFT JOIN la_zips z ON z.zip = o.zip
+WHERE o.type = 'hospital' AND o.opted_out = false
+GROUP BY o.zip, z.city
+ORDER BY hospitals DESC, o.zip;
+
+-- ─── 8. Doctors per capita proxy: practices per ZIP ────────────────────────
+-- Rough density indicator. Pair with US Census ACS population data later.
+SELECT
+ o.zip,
+ COUNT(*) AS practices,
+ COUNT(DISTINCT pl.professional_id) AS distinct_doctors,
+ ROUND(
+ COUNT(DISTINCT pl.professional_id)::NUMERIC
+ / NULLIF(COUNT(*),0)::NUMERIC, 2
+ ) AS doctors_per_practice
+FROM organizations o
+LEFT JOIN professional_locations pl ON pl.organization_id = o.id
+WHERE o.opted_out = false AND o.zip IS NOT NULL
+GROUP BY o.zip
+HAVING COUNT(*) >= 5
+ORDER BY practices DESC
+LIMIT 50;
+
+-- ─── 9. Recently licensed ──────────────────────────────────────────────────
+-- Doctors enumerated in the past 3 years (NPI enumeration_date proxy).
+SELECT
+ EXTRACT(YEAR FROM p.license_issue_date) AS year_started,
+ o.city,
+ COUNT(*) AS new_doctors
+FROM professionals p
+LEFT JOIN professional_locations pl ON pl.professional_id = p.id
+LEFT JOIN organizations o ON o.id = pl.organization_id
+WHERE p.license_issue_date >= NOW() - INTERVAL '3 years'
+ AND p.opted_out = false
+GROUP BY year_started, o.city
+ORDER BY year_started DESC, new_doctors DESC;
+
+-- ─── 10. Provenance audit — which sources did we pull each fact from? ─────
+SELECT
+ s.source_name,
+ rr.entity_type,
+ COUNT(*) AS records,
+ MAX(rr.fetched_at) AS most_recent
+FROM raw_records rr
+JOIN sources s ON s.id = rr.source_id
+GROUP BY s.source_name, rr.entity_type
+ORDER BY s.source_name, rr.entity_type;
diff --git a/ecosystem.config.js b/ecosystem.config.js
new file mode 100644
index 0000000..a72f8aa
--- /dev/null
+++ b/ecosystem.config.js
@@ -0,0 +1,52 @@
+/**
+ * Master PM2 config — 5 agents.
+ * pm2 start ecosystem.config.js
+ */
+module.exports = {
+ apps: [
+ {
+ name: 'pd-ingest',
+ script: 'agents/ingest-agent/server.js',
+ cwd: __dirname,
+ env: { NODE_ENV: 'production', PORT: 9870 },
+ max_memory_restart: '1G',
+ autorestart: true,
+ instances: 1,
+ exec_mode: 'fork',
+ out_file: 'logs/pd-ingest.out.log',
+ error_file: 'logs/pd-ingest.err.log',
+ },
+ {
+ name: 'pd-crawler',
+ script: 'agents/crawler-agent/server.js',
+ cwd: __dirname,
+ env: { NODE_ENV: 'production', PORT: 9871 },
+ max_memory_restart: '1G',
+ autorestart: true,
+ },
+ {
+ name: 'pd-enrich',
+ script: 'agents/enrich-agent/server.js',
+ cwd: __dirname,
+ env: { NODE_ENV: 'production', PORT: 9872 },
+ max_memory_restart: '512M',
+ autorestart: true,
+ },
+ {
+ name: 'pd-dedupe',
+ script: 'agents/dedupe-agent/server.js',
+ cwd: __dirname,
+ env: { NODE_ENV: 'production', PORT: 9873 },
+ max_memory_restart: '512M',
+ autorestart: true,
+ },
+ {
+ name: 'pd-api',
+ script: 'agents/api-agent/server.js',
+ cwd: __dirname,
+ env: { NODE_ENV: 'production', PORT: 9874 },
+ max_memory_restart: '512M',
+ autorestart: true,
+ },
+ ],
+};
diff --git a/package-lock.json b/package-lock.json
new file mode 100644
index 0000000..5daed7b
--- /dev/null
+++ b/package-lock.json
@@ -0,0 +1,1354 @@
+{
+ "name": "professional-directory",
+ "version": "0.1.0",
+ "lockfileVersion": 3,
+ "requires": true,
+ "packages": {
+ "": {
+ "name": "professional-directory",
+ "version": "0.1.0",
+ "license": "UNLICENSED",
+ "workspaces": [
+ "agents/shared",
+ "agents/ingest-agent",
+ "agents/crawler-agent",
+ "agents/enrich-agent",
+ "agents/dedupe-agent",
+ "agents/api-agent"
+ ],
+ "dependencies": {
+ "cheerio": "^1.2.0",
+ "csv-parse": "^6.2.1",
+ "csv-stringify": "^6.7.0",
+ "dotenv": "^17.4.2",
+ "express": "^5.2.1",
+ "pg": "^8.20.0",
+ "robots-parser": "^3.0.1",
+ "undici": "^7.25.0"
+ }
+ },
+ "agents/shared": {
+ "name": "@pd/shared",
+ "version": "0.1.0",
+ "dependencies": {
+ "dotenv": "^16.4.7",
+ "pg": "^8.13.1",
+ "robots-parser": "^3.0.1",
+ "undici": "^7.2.0"
+ }
+ },
+ "agents/shared/node_modules/dotenv": {
+ "version": "16.6.1",
+ "resolved": "https://registry.npmjs.org/dotenv/-/dotenv-16.6.1.tgz",
+ "integrity": "sha512-uBq4egWHTcTt33a72vpSG0z3HnPuIl6NqYcTrKEg2azoEyl2hpW0zqlxysq2pK9HlDIHyHyakeYaYnSAwd8bow==",
+ "license": "BSD-2-Clause",
+ "engines": {
+ "node": ">=12"
+ },
+ "funding": {
+ "url": "https://dotenvx.com"
+ }
+ },
+ "node_modules/@pd/shared": {
+ "resolved": "agents/shared",
+ "link": true
+ },
+ "node_modules/accepts": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/accepts/-/accepts-2.0.0.tgz",
+ "integrity": "sha512-5cvg6CtKwfgdmVqY1WIiXKc3Q1bkRqGLi+2W/6ao+6Y7gu/RCwRuAhGEzh5B4KlszSuTLgZYuqFqo5bImjNKng==",
+ "license": "MIT",
+ "dependencies": {
+ "mime-types": "^3.0.0",
+ "negotiator": "^1.0.0"
+ },
+ "engines": {
+ "node": ">= 0.6"
+ }
+ },
+ "node_modules/body-parser": {
+ "version": "2.2.2",
+ "resolved": "https://registry.npmjs.org/body-parser/-/body-parser-2.2.2.tgz",
+ "integrity": "sha512-oP5VkATKlNwcgvxi0vM0p/D3n2C3EReYVX+DNYs5TjZFn/oQt2j+4sVJtSMr18pdRr8wjTcBl6LoV+FUwzPmNA==",
+ "license": "MIT",
+ "dependencies": {
+ "bytes": "^3.1.2",
+ "content-type": "^1.0.5",
+ "debug": "^4.4.3",
+ "http-errors": "^2.0.0",
+ "iconv-lite": "^0.7.0",
+ "on-finished": "^2.4.1",
+ "qs": "^6.14.1",
+ "raw-body": "^3.0.1",
+ "type-is": "^2.0.1"
+ },
+ "engines": {
+ "node": ">=18"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/body-parser/node_modules/iconv-lite": {
+ "version": "0.7.2",
+ "resolved": "https://registry.npmjs.org/iconv-lite/-/iconv-lite-0.7.2.tgz",
+ "integrity": "sha512-im9DjEDQ55s9fL4EYzOAv0yMqmMBSZp6G0VvFyTMPKWxiSBHUj9NW/qqLmXUwXrrM7AvqSlTCfvqRb0cM8yYqw==",
+ "license": "MIT",
+ "dependencies": {
+ "safer-buffer": ">= 2.1.2 < 3.0.0"
+ },
+ "engines": {
+ "node": ">=0.10.0"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/boolbase": {
+ "version": "1.0.0",
+ "resolved": "https://registry.npmjs.org/boolbase/-/boolbase-1.0.0.tgz",
+ "integrity": "sha512-JZOSA7Mo9sNGB8+UjSgzdLtokWAky1zbztM3WRLCbZ70/3cTANmQmOdR7y2g+J0e2WXywy1yS468tY+IruqEww==",
+ "license": "ISC"
+ },
+ "node_modules/bytes": {
+ "version": "3.1.2",
+ "resolved": "https://registry.npmjs.org/bytes/-/bytes-3.1.2.tgz",
+ "integrity": "sha512-/Nf7TyzTx6S3yRJObOAV7956r8cr2+Oj8AC5dt8wSP3BQAoeX58NoHyCU8P8zGkNXStjTSi6fzO6F0pBdcYbEg==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/call-bind-apply-helpers": {
+ "version": "1.0.2",
+ "resolved": "https://registry.npmjs.org/call-bind-apply-helpers/-/call-bind-apply-helpers-1.0.2.tgz",
+ "integrity": "sha512-Sp1ablJ0ivDkSzjcaJdxEunN5/XvksFJ2sMBFfq6x0ryhQV/2b/KwFe21cMpmHtPOSij8K99/wSfoEuTObmuMQ==",
+ "license": "MIT",
+ "dependencies": {
+ "es-errors": "^1.3.0",
+ "function-bind": "^1.1.2"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ }
+ },
+ "node_modules/call-bound": {
+ "version": "1.0.4",
+ "resolved": "https://registry.npmjs.org/call-bound/-/call-bound-1.0.4.tgz",
+ "integrity": "sha512-+ys997U96po4Kx/ABpBCqhA9EuxJaQWDQg7295H4hBphv3IZg0boBKuwYpt4YXp6MZ5AmZQnU/tyMTlRpaSejg==",
+ "license": "MIT",
+ "dependencies": {
+ "call-bind-apply-helpers": "^1.0.2",
+ "get-intrinsic": "^1.3.0"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/cheerio": {
+ "version": "1.2.0",
+ "resolved": "https://registry.npmjs.org/cheerio/-/cheerio-1.2.0.tgz",
+ "integrity": "sha512-WDrybc/gKFpTYQutKIK6UvfcuxijIZfMfXaYm8NMsPQxSYvf+13fXUJ4rztGGbJcBQ/GF55gvrZ0Bc0bj/mqvg==",
+ "license": "MIT",
+ "dependencies": {
+ "cheerio-select": "^2.1.0",
+ "dom-serializer": "^2.0.0",
+ "domhandler": "^5.0.3",
+ "domutils": "^3.2.2",
+ "encoding-sniffer": "^0.2.1",
+ "htmlparser2": "^10.1.0",
+ "parse5": "^7.3.0",
+ "parse5-htmlparser2-tree-adapter": "^7.1.0",
+ "parse5-parser-stream": "^7.1.2",
+ "undici": "^7.19.0",
+ "whatwg-mimetype": "^4.0.0"
+ },
+ "engines": {
+ "node": ">=20.18.1"
+ },
+ "funding": {
+ "url": "https://github.com/cheeriojs/cheerio?sponsor=1"
+ }
+ },
+ "node_modules/cheerio-select": {
+ "version": "2.1.0",
+ "resolved": "https://registry.npmjs.org/cheerio-select/-/cheerio-select-2.1.0.tgz",
+ "integrity": "sha512-9v9kG0LvzrlcungtnJtpGNxY+fzECQKhK4EGJX2vByejiMX84MFNQw4UxPJl3bFbTMw+Dfs37XaIkCwTZfLh4g==",
+ "license": "BSD-2-Clause",
+ "dependencies": {
+ "boolbase": "^1.0.0",
+ "css-select": "^5.1.0",
+ "css-what": "^6.1.0",
+ "domelementtype": "^2.3.0",
+ "domhandler": "^5.0.3",
+ "domutils": "^3.0.1"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/fb55"
+ }
+ },
+ "node_modules/content-disposition": {
+ "version": "1.1.0",
+ "resolved": "https://registry.npmjs.org/content-disposition/-/content-disposition-1.1.0.tgz",
+ "integrity": "sha512-5jRCH9Z/+DRP7rkvY83B+yGIGX96OYdJmzngqnw2SBSxqCFPd0w2km3s5iawpGX8krnwSGmF0FW5Nhr0Hfai3g==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=18"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/content-type": {
+ "version": "1.0.5",
+ "resolved": "https://registry.npmjs.org/content-type/-/content-type-1.0.5.tgz",
+ "integrity": "sha512-nTjqfcBFEipKdXCv4YDQWCfmcLZKm81ldF0pAopTvyrFGVbcR6P/VAAd5G7N+0tTr8QqiU0tFadD6FK4NtJwOA==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.6"
+ }
+ },
+ "node_modules/cookie": {
+ "version": "0.7.2",
+ "resolved": "https://registry.npmjs.org/cookie/-/cookie-0.7.2.tgz",
+ "integrity": "sha512-yki5XnKuf750l50uGTllt6kKILY4nQ1eNIQatoXEByZ5dWgnKqbnqmTrBE5B4N7lrMJKQ2ytWMiTO2o0v6Ew/w==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.6"
+ }
+ },
+ "node_modules/cookie-signature": {
+ "version": "1.2.2",
+ "resolved": "https://registry.npmjs.org/cookie-signature/-/cookie-signature-1.2.2.tgz",
+ "integrity": "sha512-D76uU73ulSXrD1UXF4KE2TMxVVwhsnCgfAyTg9k8P6KGZjlXKrOLe4dJQKI3Bxi5wjesZoFXJWElNWBjPZMbhg==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=6.6.0"
+ }
+ },
+ "node_modules/css-select": {
+ "version": "5.2.2",
+ "resolved": "https://registry.npmjs.org/css-select/-/css-select-5.2.2.tgz",
+ "integrity": "sha512-TizTzUddG/xYLA3NXodFM0fSbNizXjOKhqiQQwvhlspadZokn1KDy0NZFS0wuEubIYAV5/c1/lAr0TaaFXEXzw==",
+ "license": "BSD-2-Clause",
+ "dependencies": {
+ "boolbase": "^1.0.0",
+ "css-what": "^6.1.0",
+ "domhandler": "^5.0.2",
+ "domutils": "^3.0.1",
+ "nth-check": "^2.0.1"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/fb55"
+ }
+ },
+ "node_modules/css-what": {
+ "version": "6.2.2",
+ "resolved": "https://registry.npmjs.org/css-what/-/css-what-6.2.2.tgz",
+ "integrity": "sha512-u/O3vwbptzhMs3L1fQE82ZSLHQQfto5gyZzwteVIEyeaY5Fc7R4dapF/BvRoSYFeqfBk4m0V1Vafq5Pjv25wvA==",
+ "license": "BSD-2-Clause",
+ "engines": {
+ "node": ">= 6"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/fb55"
+ }
+ },
+ "node_modules/csv-parse": {
+ "version": "6.2.1",
+ "resolved": "https://registry.npmjs.org/csv-parse/-/csv-parse-6.2.1.tgz",
+ "integrity": "sha512-LRLMV+UCyfMokp8Wb411duBf1gaBKJfOfBWU9eHMJ+b+cJYZsNu3AFmjJf3+yPGd59Exz1TsMjaSFyxnYB9+IQ==",
+ "license": "MIT"
+ },
+ "node_modules/csv-stringify": {
+ "version": "6.7.0",
+ "resolved": "https://registry.npmjs.org/csv-stringify/-/csv-stringify-6.7.0.tgz",
+ "integrity": "sha512-UdtziYp5HuTz7e5j8Nvq+a/3HQo+2/aJZ9xntNTpmRRIg/3YYqDVgiS9fvAhtNbnyfbv2ZBe0bqCHqzhE7FqWQ==",
+ "license": "MIT"
+ },
+ "node_modules/debug": {
+ "version": "4.4.3",
+ "resolved": "https://registry.npmjs.org/debug/-/debug-4.4.3.tgz",
+ "integrity": "sha512-RGwwWnwQvkVfavKVt22FGLw+xYSdzARwm0ru6DhTVA3umU5hZc28V3kO4stgYryrTlLpuvgI9GiijltAjNbcqA==",
+ "license": "MIT",
+ "dependencies": {
+ "ms": "^2.1.3"
+ },
+ "engines": {
+ "node": ">=6.0"
+ },
+ "peerDependenciesMeta": {
+ "supports-color": {
+ "optional": true
+ }
+ }
+ },
+ "node_modules/depd": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/depd/-/depd-2.0.0.tgz",
+ "integrity": "sha512-g7nH6P6dyDioJogAAGprGpCtVImJhpPk/roCzdb3fIh61/s/nPsfR6onyMwkCAR/OlC3yBC0lESvUoQEAssIrw==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/dom-serializer": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/dom-serializer/-/dom-serializer-2.0.0.tgz",
+ "integrity": "sha512-wIkAryiqt/nV5EQKqQpo3SToSOV9J0DnbJqwK7Wv/Trc92zIAYZ4FlMu+JPFW1DfGFt81ZTCGgDEabffXeLyJg==",
+ "license": "MIT",
+ "dependencies": {
+ "domelementtype": "^2.3.0",
+ "domhandler": "^5.0.2",
+ "entities": "^4.2.0"
+ },
+ "funding": {
+ "url": "https://github.com/cheeriojs/dom-serializer?sponsor=1"
+ }
+ },
+ "node_modules/domelementtype": {
+ "version": "2.3.0",
+ "resolved": "https://registry.npmjs.org/domelementtype/-/domelementtype-2.3.0.tgz",
+ "integrity": "sha512-OLETBj6w0OsagBwdXnPdN0cnMfF9opN69co+7ZrbfPGrdpPVNBUj02spi6B1N7wChLQiPn4CSH/zJvXw56gmHw==",
+ "funding": [
+ {
+ "type": "github",
+ "url": "https://github.com/sponsors/fb55"
+ }
+ ],
+ "license": "BSD-2-Clause"
+ },
+ "node_modules/domhandler": {
+ "version": "5.0.3",
+ "resolved": "https://registry.npmjs.org/domhandler/-/domhandler-5.0.3.tgz",
+ "integrity": "sha512-cgwlv/1iFQiFnU96XXgROh8xTeetsnJiDsTc7TYCLFd9+/WNkIqPTxiM/8pSd8VIrhXGTf1Ny1q1hquVqDJB5w==",
+ "license": "BSD-2-Clause",
+ "dependencies": {
+ "domelementtype": "^2.3.0"
+ },
+ "engines": {
+ "node": ">= 4"
+ },
+ "funding": {
+ "url": "https://github.com/fb55/domhandler?sponsor=1"
+ }
+ },
+ "node_modules/domutils": {
+ "version": "3.2.2",
+ "resolved": "https://registry.npmjs.org/domutils/-/domutils-3.2.2.tgz",
+ "integrity": "sha512-6kZKyUajlDuqlHKVX1w7gyslj9MPIXzIFiz/rGu35uC1wMi+kMhQwGhl4lt9unC9Vb9INnY9Z3/ZA3+FhASLaw==",
+ "license": "BSD-2-Clause",
+ "dependencies": {
+ "dom-serializer": "^2.0.0",
+ "domelementtype": "^2.3.0",
+ "domhandler": "^5.0.3"
+ },
+ "funding": {
+ "url": "https://github.com/fb55/domutils?sponsor=1"
+ }
+ },
+ "node_modules/dotenv": {
+ "version": "17.4.2",
+ "resolved": "https://registry.npmjs.org/dotenv/-/dotenv-17.4.2.tgz",
+ "integrity": "sha512-nI4U3TottKAcAD9LLud4Cb7b2QztQMUEfHbvhTH09bqXTxnSie8WnjPALV/WMCrJZ6UV/qHJ6L03OqO3LcdYZw==",
+ "license": "BSD-2-Clause",
+ "engines": {
+ "node": ">=12"
+ },
+ "funding": {
+ "url": "https://dotenvx.com"
+ }
+ },
+ "node_modules/dunder-proto": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/dunder-proto/-/dunder-proto-1.0.1.tgz",
+ "integrity": "sha512-KIN/nDJBQRcXw0MLVhZE9iQHmG68qAVIBg9CqmUYjmQIhgij9U5MFvrqkUL5FbtyyzZuOeOt0zdeRe4UY7ct+A==",
+ "license": "MIT",
+ "dependencies": {
+ "call-bind-apply-helpers": "^1.0.1",
+ "es-errors": "^1.3.0",
+ "gopd": "^1.2.0"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ }
+ },
+ "node_modules/ee-first": {
+ "version": "1.1.1",
+ "resolved": "https://registry.npmjs.org/ee-first/-/ee-first-1.1.1.tgz",
+ "integrity": "sha512-WMwm9LhRUo+WUaRN+vRuETqG89IgZphVSNkdFgeb6sS/E4OrDIN7t48CAewSHXc6C8lefD8KKfr5vY61brQlow==",
+ "license": "MIT"
+ },
+ "node_modules/encodeurl": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/encodeurl/-/encodeurl-2.0.0.tgz",
+ "integrity": "sha512-Q0n9HRi4m6JuGIV1eFlmvJB7ZEVxu93IrMyiMsGC0lrMJMWzRgx6WGquyfQgZVb31vhGgXnfmPNNXmxnOkRBrg==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/encoding-sniffer": {
+ "version": "0.2.1",
+ "resolved": "https://registry.npmjs.org/encoding-sniffer/-/encoding-sniffer-0.2.1.tgz",
+ "integrity": "sha512-5gvq20T6vfpekVtqrYQsSCFZ1wEg5+wW0/QaZMWkFr6BqD3NfKs0rLCx4rrVlSWJeZb5NBJgVLswK/w2MWU+Gw==",
+ "license": "MIT",
+ "dependencies": {
+ "iconv-lite": "^0.6.3",
+ "whatwg-encoding": "^3.1.1"
+ },
+ "funding": {
+ "url": "https://github.com/fb55/encoding-sniffer?sponsor=1"
+ }
+ },
+ "node_modules/entities": {
+ "version": "4.5.0",
+ "resolved": "https://registry.npmjs.org/entities/-/entities-4.5.0.tgz",
+ "integrity": "sha512-V0hjH4dGPh9Ao5p0MoRY6BVqtwCjhz6vI5LT8AJ55H+4g9/4vbHx1I54fS0XuclLhDHArPQCiMjDxjaL8fPxhw==",
+ "license": "BSD-2-Clause",
+ "engines": {
+ "node": ">=0.12"
+ },
+ "funding": {
+ "url": "https://github.com/fb55/entities?sponsor=1"
+ }
+ },
+ "node_modules/es-define-property": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/es-define-property/-/es-define-property-1.0.1.tgz",
+ "integrity": "sha512-e3nRfgfUZ4rNGL232gUgX06QNyyez04KdjFrF+LTRoOXmrOgFKDg4BCdsjW8EnT69eqdYGmRpJwiPVYNrCaW3g==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.4"
+ }
+ },
+ "node_modules/es-errors": {
+ "version": "1.3.0",
+ "resolved": "https://registry.npmjs.org/es-errors/-/es-errors-1.3.0.tgz",
+ "integrity": "sha512-Zf5H2Kxt2xjTvbJvP2ZWLEICxA6j+hAmMzIlypy4xcBg1vKVnx89Wy0GbS+kf5cwCVFFzdCFh2XSCFNULS6csw==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.4"
+ }
+ },
+ "node_modules/es-object-atoms": {
+ "version": "1.1.1",
+ "resolved": "https://registry.npmjs.org/es-object-atoms/-/es-object-atoms-1.1.1.tgz",
+ "integrity": "sha512-FGgH2h8zKNim9ljj7dankFPcICIK9Cp5bm+c2gQSYePhpaG5+esrLODihIorn+Pe6FGJzWhXQotPv73jTaldXA==",
+ "license": "MIT",
+ "dependencies": {
+ "es-errors": "^1.3.0"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ }
+ },
+ "node_modules/escape-html": {
+ "version": "1.0.3",
+ "resolved": "https://registry.npmjs.org/escape-html/-/escape-html-1.0.3.tgz",
+ "integrity": "sha512-NiSupZ4OeuGwr68lGIeym/ksIZMJodUGOSCZ/FSnTxcrekbvqrgdUxlJOMpijaKZVjAJrWrGs/6Jy8OMuyj9ow==",
+ "license": "MIT"
+ },
+ "node_modules/etag": {
+ "version": "1.8.1",
+ "resolved": "https://registry.npmjs.org/etag/-/etag-1.8.1.tgz",
+ "integrity": "sha512-aIL5Fx7mawVa300al2BnEE4iNvo1qETxLrPI/o05L7z6go7fCw1J6EQmbK4FmJ2AS7kgVF/KEZWufBfdClMcPg==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.6"
+ }
+ },
+ "node_modules/express": {
+ "version": "5.2.1",
+ "resolved": "https://registry.npmjs.org/express/-/express-5.2.1.tgz",
+ "integrity": "sha512-hIS4idWWai69NezIdRt2xFVofaF4j+6INOpJlVOLDO8zXGpUVEVzIYk12UUi2JzjEzWL3IOAxcTubgz9Po0yXw==",
+ "license": "MIT",
+ "dependencies": {
+ "accepts": "^2.0.0",
+ "body-parser": "^2.2.1",
+ "content-disposition": "^1.0.0",
+ "content-type": "^1.0.5",
+ "cookie": "^0.7.1",
+ "cookie-signature": "^1.2.1",
+ "debug": "^4.4.0",
+ "depd": "^2.0.0",
+ "encodeurl": "^2.0.0",
+ "escape-html": "^1.0.3",
+ "etag": "^1.8.1",
+ "finalhandler": "^2.1.0",
+ "fresh": "^2.0.0",
+ "http-errors": "^2.0.0",
+ "merge-descriptors": "^2.0.0",
+ "mime-types": "^3.0.0",
+ "on-finished": "^2.4.1",
+ "once": "^1.4.0",
+ "parseurl": "^1.3.3",
+ "proxy-addr": "^2.0.7",
+ "qs": "^6.14.0",
+ "range-parser": "^1.2.1",
+ "router": "^2.2.0",
+ "send": "^1.1.0",
+ "serve-static": "^2.2.0",
+ "statuses": "^2.0.1",
+ "type-is": "^2.0.1",
+ "vary": "^1.1.2"
+ },
+ "engines": {
+ "node": ">= 18"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/finalhandler": {
+ "version": "2.1.1",
+ "resolved": "https://registry.npmjs.org/finalhandler/-/finalhandler-2.1.1.tgz",
+ "integrity": "sha512-S8KoZgRZN+a5rNwqTxlZZePjT/4cnm0ROV70LedRHZ0p8u9fRID0hJUZQpkKLzro8LfmC8sx23bY6tVNxv8pQA==",
+ "license": "MIT",
+ "dependencies": {
+ "debug": "^4.4.0",
+ "encodeurl": "^2.0.0",
+ "escape-html": "^1.0.3",
+ "on-finished": "^2.4.1",
+ "parseurl": "^1.3.3",
+ "statuses": "^2.0.1"
+ },
+ "engines": {
+ "node": ">= 18.0.0"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/forwarded": {
+ "version": "0.2.0",
+ "resolved": "https://registry.npmjs.org/forwarded/-/forwarded-0.2.0.tgz",
+ "integrity": "sha512-buRG0fpBtRHSTCOASe6hD258tEubFoRLb4ZNA6NxMVHNw2gOcwHo9wyablzMzOA5z9xA9L1KNjk/Nt6MT9aYow==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.6"
+ }
+ },
+ "node_modules/fresh": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/fresh/-/fresh-2.0.0.tgz",
+ "integrity": "sha512-Rx/WycZ60HOaqLKAi6cHRKKI7zxWbJ31MhntmtwMoaTeF7XFH9hhBp8vITaMidfljRQ6eYWCKkaTK+ykVJHP2A==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/function-bind": {
+ "version": "1.1.2",
+ "resolved": "https://registry.npmjs.org/function-bind/-/function-bind-1.1.2.tgz",
+ "integrity": "sha512-7XHNxH7qX9xG5mIwxkhumTox/MIRNcOgDrxWsMt2pAr23WHp6MrRlN7FBSFpCpr+oVO0F744iUgR82nJMfG2SA==",
+ "license": "MIT",
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/get-intrinsic": {
+ "version": "1.3.0",
+ "resolved": "https://registry.npmjs.org/get-intrinsic/-/get-intrinsic-1.3.0.tgz",
+ "integrity": "sha512-9fSjSaos/fRIVIp+xSJlE6lfwhES7LNtKaCBIamHsjr2na1BiABJPo0mOjjz8GJDURarmCPGqaiVg5mfjb98CQ==",
+ "license": "MIT",
+ "dependencies": {
+ "call-bind-apply-helpers": "^1.0.2",
+ "es-define-property": "^1.0.1",
+ "es-errors": "^1.3.0",
+ "es-object-atoms": "^1.1.1",
+ "function-bind": "^1.1.2",
+ "get-proto": "^1.0.1",
+ "gopd": "^1.2.0",
+ "has-symbols": "^1.1.0",
+ "hasown": "^2.0.2",
+ "math-intrinsics": "^1.1.0"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/get-proto": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/get-proto/-/get-proto-1.0.1.tgz",
+ "integrity": "sha512-sTSfBjoXBp89JvIKIefqw7U2CCebsc74kiY6awiGogKtoSGbgjYE/G/+l9sF3MWFPNc9IcoOC4ODfKHfxFmp0g==",
+ "license": "MIT",
+ "dependencies": {
+ "dunder-proto": "^1.0.1",
+ "es-object-atoms": "^1.0.0"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ }
+ },
+ "node_modules/gopd": {
+ "version": "1.2.0",
+ "resolved": "https://registry.npmjs.org/gopd/-/gopd-1.2.0.tgz",
+ "integrity": "sha512-ZUKRh6/kUFoAiTAtTYPZJ3hw9wNxx+BIBOijnlG9PnrJsCcSjs1wyyD6vJpaYtgnzDrKYRSqf3OO6Rfa93xsRg==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.4"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/has-symbols": {
+ "version": "1.1.0",
+ "resolved": "https://registry.npmjs.org/has-symbols/-/has-symbols-1.1.0.tgz",
+ "integrity": "sha512-1cDNdwJ2Jaohmb3sg4OmKaMBwuC48sYni5HUw2DvsC8LjGTLK9h+eb1X6RyuOHe4hT0ULCW68iomhjUoKUqlPQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.4"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/hasown": {
+ "version": "2.0.3",
+ "resolved": "https://registry.npmjs.org/hasown/-/hasown-2.0.3.tgz",
+ "integrity": "sha512-ej4AhfhfL2Q2zpMmLo7U1Uv9+PyhIZpgQLGT1F9miIGmiCJIoCgSmczFdrc97mWT4kVY72KA+WnnhJ5pghSvSg==",
+ "license": "MIT",
+ "dependencies": {
+ "function-bind": "^1.1.2"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ }
+ },
+ "node_modules/htmlparser2": {
+ "version": "10.1.0",
+ "resolved": "https://registry.npmjs.org/htmlparser2/-/htmlparser2-10.1.0.tgz",
+ "integrity": "sha512-VTZkM9GWRAtEpveh7MSF6SjjrpNVNNVJfFup7xTY3UpFtm67foy9HDVXneLtFVt4pMz5kZtgNcvCniNFb1hlEQ==",
+ "funding": [
+ "https://github.com/fb55/htmlparser2?sponsor=1",
+ {
+ "type": "github",
+ "url": "https://github.com/sponsors/fb55"
+ }
+ ],
+ "license": "MIT",
+ "dependencies": {
+ "domelementtype": "^2.3.0",
+ "domhandler": "^5.0.3",
+ "domutils": "^3.2.2",
+ "entities": "^7.0.1"
+ }
+ },
+ "node_modules/htmlparser2/node_modules/entities": {
+ "version": "7.0.1",
+ "resolved": "https://registry.npmjs.org/entities/-/entities-7.0.1.tgz",
+ "integrity": "sha512-TWrgLOFUQTH994YUyl1yT4uyavY5nNB5muff+RtWaqNVCAK408b5ZnnbNAUEWLTCpum9w6arT70i1XdQ4UeOPA==",
+ "license": "BSD-2-Clause",
+ "engines": {
+ "node": ">=0.12"
+ },
+ "funding": {
+ "url": "https://github.com/fb55/entities?sponsor=1"
+ }
+ },
+ "node_modules/http-errors": {
+ "version": "2.0.1",
+ "resolved": "https://registry.npmjs.org/http-errors/-/http-errors-2.0.1.tgz",
+ "integrity": "sha512-4FbRdAX+bSdmo4AUFuS0WNiPz8NgFt+r8ThgNWmlrjQjt1Q7ZR9+zTlce2859x4KSXrwIsaeTqDoKQmtP8pLmQ==",
+ "license": "MIT",
+ "dependencies": {
+ "depd": "~2.0.0",
+ "inherits": "~2.0.4",
+ "setprototypeof": "~1.2.0",
+ "statuses": "~2.0.2",
+ "toidentifier": "~1.0.1"
+ },
+ "engines": {
+ "node": ">= 0.8"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/iconv-lite": {
+ "version": "0.6.3",
+ "resolved": "https://registry.npmjs.org/iconv-lite/-/iconv-lite-0.6.3.tgz",
+ "integrity": "sha512-4fCk79wshMdzMp2rH06qWrJE4iolqLhCUH+OiuIgU++RB0+94NlDL81atO7GX55uUKueo0txHNtvEyI6D7WdMw==",
+ "license": "MIT",
+ "dependencies": {
+ "safer-buffer": ">= 2.1.2 < 3.0.0"
+ },
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/inherits": {
+ "version": "2.0.4",
+ "resolved": "https://registry.npmjs.org/inherits/-/inherits-2.0.4.tgz",
+ "integrity": "sha512-k/vGaX4/Yla3WzyMCvTQOXYeIHvqOKtnqBduzTHpzpQZzAskKMhZ2K+EnBiSM9zGSoIFeMpXKxa4dYeZIQqewQ==",
+ "license": "ISC"
+ },
+ "node_modules/ipaddr.js": {
+ "version": "1.9.1",
+ "resolved": "https://registry.npmjs.org/ipaddr.js/-/ipaddr.js-1.9.1.tgz",
+ "integrity": "sha512-0KI/607xoxSToH7GjN1FfSbLoU0+btTicjsQSWQlh/hZykN8KpmMf7uYwPW3R+akZ6R/w18ZlXSHBYXiYUPO3g==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.10"
+ }
+ },
+ "node_modules/is-promise": {
+ "version": "4.0.0",
+ "resolved": "https://registry.npmjs.org/is-promise/-/is-promise-4.0.0.tgz",
+ "integrity": "sha512-hvpoI6korhJMnej285dSg6nu1+e6uxs7zG3BYAm5byqDsgJNWwxzM6z6iZiAgQR4TJ30JmBTOwqZUw3WlyH3AQ==",
+ "license": "MIT"
+ },
+ "node_modules/math-intrinsics": {
+ "version": "1.1.0",
+ "resolved": "https://registry.npmjs.org/math-intrinsics/-/math-intrinsics-1.1.0.tgz",
+ "integrity": "sha512-/IXtbwEk5HTPyEwyKX6hGkYXxM9nbj64B+ilVJnC/R6B0pH5G4V3b0pVbL7DBj4tkhBAppbQUlf6F6Xl9LHu1g==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.4"
+ }
+ },
+ "node_modules/media-typer": {
+ "version": "1.1.0",
+ "resolved": "https://registry.npmjs.org/media-typer/-/media-typer-1.1.0.tgz",
+ "integrity": "sha512-aisnrDP4GNe06UcKFnV5bfMNPBUw4jsLGaWwWfnH3v02GnBuXX2MCVn5RbrWo0j3pczUilYblq7fQ7Nw2t5XKw==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/merge-descriptors": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/merge-descriptors/-/merge-descriptors-2.0.0.tgz",
+ "integrity": "sha512-Snk314V5ayFLhp3fkUREub6WtjBfPdCPY1Ln8/8munuLuiYhsABgBVWsozAG+MWMbVEvcdcpbi9R7ww22l9Q3g==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=18"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/sindresorhus"
+ }
+ },
+ "node_modules/mime-db": {
+ "version": "1.54.0",
+ "resolved": "https://registry.npmjs.org/mime-db/-/mime-db-1.54.0.tgz",
+ "integrity": "sha512-aU5EJuIN2WDemCcAp2vFBfp/m4EAhWJnUNSSw0ixs7/kXbd6Pg64EmwJkNdFhB8aWt1sH2CTXrLxo/iAGV3oPQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.6"
+ }
+ },
+ "node_modules/mime-types": {
+ "version": "3.0.2",
+ "resolved": "https://registry.npmjs.org/mime-types/-/mime-types-3.0.2.tgz",
+ "integrity": "sha512-Lbgzdk0h4juoQ9fCKXW4by0UJqj+nOOrI9MJ1sSj4nI8aI2eo1qmvQEie4VD1glsS250n15LsWsYtCugiStS5A==",
+ "license": "MIT",
+ "dependencies": {
+ "mime-db": "^1.54.0"
+ },
+ "engines": {
+ "node": ">=18"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/ms": {
+ "version": "2.1.3",
+ "resolved": "https://registry.npmjs.org/ms/-/ms-2.1.3.tgz",
+ "integrity": "sha512-6FlzubTLZG3J2a/NVCAleEhjzq5oxgHyaCU9yYXvcLsvoVaHJq/s5xXI6/XXP6tz7R9xAOtHnSO/tXtF3WRTlA==",
+ "license": "MIT"
+ },
+ "node_modules/negotiator": {
+ "version": "1.0.0",
+ "resolved": "https://registry.npmjs.org/negotiator/-/negotiator-1.0.0.tgz",
+ "integrity": "sha512-8Ofs/AUQh8MaEcrlq5xOX0CQ9ypTF5dl78mjlMNfOK08fzpgTHQRQPBxcPlEtIw0yRpws+Zo/3r+5WRby7u3Gg==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.6"
+ }
+ },
+ "node_modules/nth-check": {
+ "version": "2.1.1",
+ "resolved": "https://registry.npmjs.org/nth-check/-/nth-check-2.1.1.tgz",
+ "integrity": "sha512-lqjrjmaOoAnWfMmBPL+XNnynZh2+swxiX3WUE0s4yEHI6m+AwrK2UZOimIRl3X/4QctVqS8AiZjFqyOGrMXb/w==",
+ "license": "BSD-2-Clause",
+ "dependencies": {
+ "boolbase": "^1.0.0"
+ },
+ "funding": {
+ "url": "https://github.com/fb55/nth-check?sponsor=1"
+ }
+ },
+ "node_modules/object-inspect": {
+ "version": "1.13.4",
+ "resolved": "https://registry.npmjs.org/object-inspect/-/object-inspect-1.13.4.tgz",
+ "integrity": "sha512-W67iLl4J2EXEGTbfeHCffrjDfitvLANg0UlX3wFUUSTx92KXRFegMHUVgSqE+wvhAbi4WqjGg9czysTV2Epbew==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.4"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/on-finished": {
+ "version": "2.4.1",
+ "resolved": "https://registry.npmjs.org/on-finished/-/on-finished-2.4.1.tgz",
+ "integrity": "sha512-oVlzkg3ENAhCk2zdv7IJwd/QUD4z2RxRwpkcGY8psCVcCYZNq4wYnVWALHM+brtuJjePWiYF/ClmuDr8Ch5+kg==",
+ "license": "MIT",
+ "dependencies": {
+ "ee-first": "1.1.1"
+ },
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/once": {
+ "version": "1.4.0",
+ "resolved": "https://registry.npmjs.org/once/-/once-1.4.0.tgz",
+ "integrity": "sha512-lNaJgI+2Q5URQBkccEKHTQOPaXdUxnZZElQTZY0MFUAuaEqe1E+Nyvgdz/aIyNi6Z9MzO5dv1H8n58/GELp3+w==",
+ "license": "ISC",
+ "dependencies": {
+ "wrappy": "1"
+ }
+ },
+ "node_modules/parse5": {
+ "version": "7.3.0",
+ "resolved": "https://registry.npmjs.org/parse5/-/parse5-7.3.0.tgz",
+ "integrity": "sha512-IInvU7fabl34qmi9gY8XOVxhYyMyuH2xUNpb2q8/Y+7552KlejkRvqvD19nMoUW/uQGGbqNpA6Tufu5FL5BZgw==",
+ "license": "MIT",
+ "dependencies": {
+ "entities": "^6.0.0"
+ },
+ "funding": {
+ "url": "https://github.com/inikulin/parse5?sponsor=1"
+ }
+ },
+ "node_modules/parse5-htmlparser2-tree-adapter": {
+ "version": "7.1.0",
+ "resolved": "https://registry.npmjs.org/parse5-htmlparser2-tree-adapter/-/parse5-htmlparser2-tree-adapter-7.1.0.tgz",
+ "integrity": "sha512-ruw5xyKs6lrpo9x9rCZqZZnIUntICjQAd0Wsmp396Ul9lN/h+ifgVV1x1gZHi8euej6wTfpqX8j+BFQxF0NS/g==",
+ "license": "MIT",
+ "dependencies": {
+ "domhandler": "^5.0.3",
+ "parse5": "^7.0.0"
+ },
+ "funding": {
+ "url": "https://github.com/inikulin/parse5?sponsor=1"
+ }
+ },
+ "node_modules/parse5-parser-stream": {
+ "version": "7.1.2",
+ "resolved": "https://registry.npmjs.org/parse5-parser-stream/-/parse5-parser-stream-7.1.2.tgz",
+ "integrity": "sha512-JyeQc9iwFLn5TbvvqACIF/VXG6abODeB3Fwmv/TGdLk2LfbWkaySGY72at4+Ty7EkPZj854u4CrICqNk2qIbow==",
+ "license": "MIT",
+ "dependencies": {
+ "parse5": "^7.0.0"
+ },
+ "funding": {
+ "url": "https://github.com/inikulin/parse5?sponsor=1"
+ }
+ },
+ "node_modules/parse5/node_modules/entities": {
+ "version": "6.0.1",
+ "resolved": "https://registry.npmjs.org/entities/-/entities-6.0.1.tgz",
+ "integrity": "sha512-aN97NXWF6AWBTahfVOIrB/NShkzi5H7F9r1s9mD3cDj4Ko5f2qhhVoYMibXF7GlLveb/D2ioWay8lxI97Ven3g==",
+ "license": "BSD-2-Clause",
+ "engines": {
+ "node": ">=0.12"
+ },
+ "funding": {
+ "url": "https://github.com/fb55/entities?sponsor=1"
+ }
+ },
+ "node_modules/parseurl": {
+ "version": "1.3.3",
+ "resolved": "https://registry.npmjs.org/parseurl/-/parseurl-1.3.3.tgz",
+ "integrity": "sha512-CiyeOxFT/JZyN5m0z9PfXw4SCBJ6Sygz1Dpl0wqjlhDEGGBP1GnsUVEL0p63hoG1fcj3fHynXi9NYO4nWOL+qQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/path-to-regexp": {
+ "version": "8.4.2",
+ "resolved": "https://registry.npmjs.org/path-to-regexp/-/path-to-regexp-8.4.2.tgz",
+ "integrity": "sha512-qRcuIdP69NPm4qbACK+aDogI5CBDMi1jKe0ry5rSQJz8JVLsC7jV8XpiJjGRLLol3N+R5ihGYcrPLTno6pAdBA==",
+ "license": "MIT",
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/pg": {
+ "version": "8.20.0",
+ "resolved": "https://registry.npmjs.org/pg/-/pg-8.20.0.tgz",
+ "integrity": "sha512-ldhMxz2r8fl/6QkXnBD3CR9/xg694oT6DZQ2s6c/RI28OjtSOpxnPrUCGOBJ46RCUxcWdx3p6kw/xnDHjKvaRA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-connection-string": "^2.12.0",
+ "pg-pool": "^3.13.0",
+ "pg-protocol": "^1.13.0",
+ "pg-types": "2.2.0",
+ "pgpass": "1.0.5"
+ },
+ "engines": {
+ "node": ">= 16.0.0"
+ },
+ "optionalDependencies": {
+ "pg-cloudflare": "^1.3.0"
+ },
+ "peerDependencies": {
+ "pg-native": ">=3.0.1"
+ },
+ "peerDependenciesMeta": {
+ "pg-native": {
+ "optional": true
+ }
+ }
+ },
+ "node_modules/pg-cloudflare": {
+ "version": "1.3.0",
+ "resolved": "https://registry.npmjs.org/pg-cloudflare/-/pg-cloudflare-1.3.0.tgz",
+ "integrity": "sha512-6lswVVSztmHiRtD6I8hw4qP/nDm1EJbKMRhf3HCYaqud7frGysPv7FYJ5noZQdhQtN2xJnimfMtvQq21pdbzyQ==",
+ "license": "MIT",
+ "optional": true
+ },
+ "node_modules/pg-connection-string": {
+ "version": "2.12.0",
+ "resolved": "https://registry.npmjs.org/pg-connection-string/-/pg-connection-string-2.12.0.tgz",
+ "integrity": "sha512-U7qg+bpswf3Cs5xLzRqbXbQl85ng0mfSV/J0nnA31MCLgvEaAo7CIhmeyrmJpOr7o+zm0rXK+hNnT5l9RHkCkQ==",
+ "license": "MIT"
+ },
+ "node_modules/pg-int8": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/pg-int8/-/pg-int8-1.0.1.tgz",
+ "integrity": "sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw==",
+ "license": "ISC",
+ "engines": {
+ "node": ">=4.0.0"
+ }
+ },
+ "node_modules/pg-pool": {
+ "version": "3.13.0",
+ "resolved": "https://registry.npmjs.org/pg-pool/-/pg-pool-3.13.0.tgz",
+ "integrity": "sha512-gB+R+Xud1gLFuRD/QgOIgGOBE2KCQPaPwkzBBGC9oG69pHTkhQeIuejVIk3/cnDyX39av2AxomQiyPT13WKHQA==",
+ "license": "MIT",
+ "peerDependencies": {
+ "pg": ">=8.0"
+ }
+ },
+ "node_modules/pg-protocol": {
+ "version": "1.13.0",
+ "resolved": "https://registry.npmjs.org/pg-protocol/-/pg-protocol-1.13.0.tgz",
+ "integrity": "sha512-zzdvXfS6v89r6v7OcFCHfHlyG/wvry1ALxZo4LqgUoy7W9xhBDMaqOuMiF3qEV45VqsN6rdlcehHrfDtlCPc8w==",
+ "license": "MIT"
+ },
+ "node_modules/pg-types": {
+ "version": "2.2.0",
+ "resolved": "https://registry.npmjs.org/pg-types/-/pg-types-2.2.0.tgz",
+ "integrity": "sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA==",
+ "license": "MIT",
+ "dependencies": {
+ "pg-int8": "1.0.1",
+ "postgres-array": "~2.0.0",
+ "postgres-bytea": "~1.0.0",
+ "postgres-date": "~1.0.4",
+ "postgres-interval": "^1.1.0"
+ },
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/pgpass": {
+ "version": "1.0.5",
+ "resolved": "https://registry.npmjs.org/pgpass/-/pgpass-1.0.5.tgz",
+ "integrity": "sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug==",
+ "license": "MIT",
+ "dependencies": {
+ "split2": "^4.1.0"
+ }
+ },
+ "node_modules/postgres-array": {
+ "version": "2.0.0",
+ "resolved": "https://registry.npmjs.org/postgres-array/-/postgres-array-2.0.0.tgz",
+ "integrity": "sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=4"
+ }
+ },
+ "node_modules/postgres-bytea": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/postgres-bytea/-/postgres-bytea-1.0.1.tgz",
+ "integrity": "sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-date": {
+ "version": "1.0.7",
+ "resolved": "https://registry.npmjs.org/postgres-date/-/postgres-date-1.0.7.tgz",
+ "integrity": "sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/postgres-interval": {
+ "version": "1.2.0",
+ "resolved": "https://registry.npmjs.org/postgres-interval/-/postgres-interval-1.2.0.tgz",
+ "integrity": "sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ==",
+ "license": "MIT",
+ "dependencies": {
+ "xtend": "^4.0.0"
+ },
+ "engines": {
+ "node": ">=0.10.0"
+ }
+ },
+ "node_modules/proxy-addr": {
+ "version": "2.0.7",
+ "resolved": "https://registry.npmjs.org/proxy-addr/-/proxy-addr-2.0.7.tgz",
+ "integrity": "sha512-llQsMLSUDUPT44jdrU/O37qlnifitDP+ZwrmmZcoSKyLKvtZxpyV0n2/bD/N4tBAAZ/gJEdZU7KMraoK1+XYAg==",
+ "license": "MIT",
+ "dependencies": {
+ "forwarded": "0.2.0",
+ "ipaddr.js": "1.9.1"
+ },
+ "engines": {
+ "node": ">= 0.10"
+ }
+ },
+ "node_modules/qs": {
+ "version": "6.15.1",
+ "resolved": "https://registry.npmjs.org/qs/-/qs-6.15.1.tgz",
+ "integrity": "sha512-6YHEFRL9mfgcAvql/XhwTvf5jKcOiiupt2FiJxHkiX1z4j7WL8J/jRHYLluORvc1XxB5rV20KoeK00gVJamspg==",
+ "license": "BSD-3-Clause",
+ "dependencies": {
+ "side-channel": "^1.1.0"
+ },
+ "engines": {
+ "node": ">=0.6"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/range-parser": {
+ "version": "1.2.1",
+ "resolved": "https://registry.npmjs.org/range-parser/-/range-parser-1.2.1.tgz",
+ "integrity": "sha512-Hrgsx+orqoygnmhFbKaHE6c296J+HTAQXoxEF6gNupROmmGJRoyzfG3ccAveqCBrwr/2yxQ5BVd/GTl5agOwSg==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.6"
+ }
+ },
+ "node_modules/raw-body": {
+ "version": "3.0.2",
+ "resolved": "https://registry.npmjs.org/raw-body/-/raw-body-3.0.2.tgz",
+ "integrity": "sha512-K5zQjDllxWkf7Z5xJdV0/B0WTNqx6vxG70zJE4N0kBs4LovmEYWJzQGxC9bS9RAKu3bgM40lrd5zoLJ12MQ5BA==",
+ "license": "MIT",
+ "dependencies": {
+ "bytes": "~3.1.2",
+ "http-errors": "~2.0.1",
+ "iconv-lite": "~0.7.0",
+ "unpipe": "~1.0.0"
+ },
+ "engines": {
+ "node": ">= 0.10"
+ }
+ },
+ "node_modules/raw-body/node_modules/iconv-lite": {
+ "version": "0.7.2",
+ "resolved": "https://registry.npmjs.org/iconv-lite/-/iconv-lite-0.7.2.tgz",
+ "integrity": "sha512-im9DjEDQ55s9fL4EYzOAv0yMqmMBSZp6G0VvFyTMPKWxiSBHUj9NW/qqLmXUwXrrM7AvqSlTCfvqRb0cM8yYqw==",
+ "license": "MIT",
+ "dependencies": {
+ "safer-buffer": ">= 2.1.2 < 3.0.0"
+ },
+ "engines": {
+ "node": ">=0.10.0"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/robots-parser": {
+ "version": "3.0.1",
+ "resolved": "https://registry.npmjs.org/robots-parser/-/robots-parser-3.0.1.tgz",
+ "integrity": "sha512-s+pyvQeIKIZ0dx5iJiQk1tPLJAWln39+MI5jtM8wnyws+G5azk+dMnMX0qfbqNetKKNgcWWOdi0sfm+FbQbgdQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=10.0.0"
+ }
+ },
+ "node_modules/router": {
+ "version": "2.2.0",
+ "resolved": "https://registry.npmjs.org/router/-/router-2.2.0.tgz",
+ "integrity": "sha512-nLTrUKm2UyiL7rlhapu/Zl45FwNgkZGaCpZbIHajDYgwlJCOzLSk+cIPAnsEqV955GjILJnKbdQC1nVPz+gAYQ==",
+ "license": "MIT",
+ "dependencies": {
+ "debug": "^4.4.0",
+ "depd": "^2.0.0",
+ "is-promise": "^4.0.0",
+ "parseurl": "^1.3.3",
+ "path-to-regexp": "^8.0.0"
+ },
+ "engines": {
+ "node": ">= 18"
+ }
+ },
+ "node_modules/safer-buffer": {
+ "version": "2.1.2",
+ "resolved": "https://registry.npmjs.org/safer-buffer/-/safer-buffer-2.1.2.tgz",
+ "integrity": "sha512-YZo3K82SD7Riyi0E1EQPojLz7kpepnSQI9IyPbHHg1XXXevb5dJI7tpyN2ADxGcQbHG7vcyRHk0cbwqcQriUtg==",
+ "license": "MIT"
+ },
+ "node_modules/send": {
+ "version": "1.2.1",
+ "resolved": "https://registry.npmjs.org/send/-/send-1.2.1.tgz",
+ "integrity": "sha512-1gnZf7DFcoIcajTjTwjwuDjzuz4PPcY2StKPlsGAQ1+YH20IRVrBaXSWmdjowTJ6u8Rc01PoYOGHXfP1mYcZNQ==",
+ "license": "MIT",
+ "dependencies": {
+ "debug": "^4.4.3",
+ "encodeurl": "^2.0.0",
+ "escape-html": "^1.0.3",
+ "etag": "^1.8.1",
+ "fresh": "^2.0.0",
+ "http-errors": "^2.0.1",
+ "mime-types": "^3.0.2",
+ "ms": "^2.1.3",
+ "on-finished": "^2.4.1",
+ "range-parser": "^1.2.1",
+ "statuses": "^2.0.2"
+ },
+ "engines": {
+ "node": ">= 18"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/serve-static": {
+ "version": "2.2.1",
+ "resolved": "https://registry.npmjs.org/serve-static/-/serve-static-2.2.1.tgz",
+ "integrity": "sha512-xRXBn0pPqQTVQiC8wyQrKs2MOlX24zQ0POGaj0kultvoOCstBQM5yvOhAVSUwOMjQtTvsPWoNCHfPGwaaQJhTw==",
+ "license": "MIT",
+ "dependencies": {
+ "encodeurl": "^2.0.0",
+ "escape-html": "^1.0.3",
+ "parseurl": "^1.3.3",
+ "send": "^1.2.0"
+ },
+ "engines": {
+ "node": ">= 18"
+ },
+ "funding": {
+ "type": "opencollective",
+ "url": "https://opencollective.com/express"
+ }
+ },
+ "node_modules/setprototypeof": {
+ "version": "1.2.0",
+ "resolved": "https://registry.npmjs.org/setprototypeof/-/setprototypeof-1.2.0.tgz",
+ "integrity": "sha512-E5LDX7Wrp85Kil5bhZv46j8jOeboKq5JMmYM3gVGdGH8xFpPWXUMsNrlODCrkoxMEeNi/XZIwuRvY4XNwYMJpw==",
+ "license": "ISC"
+ },
+ "node_modules/side-channel": {
+ "version": "1.1.0",
+ "resolved": "https://registry.npmjs.org/side-channel/-/side-channel-1.1.0.tgz",
+ "integrity": "sha512-ZX99e6tRweoUXqR+VBrslhda51Nh5MTQwou5tnUDgbtyM0dBgmhEDtWGP/xbKn6hqfPRHujUNwz5fy/wbbhnpw==",
+ "license": "MIT",
+ "dependencies": {
+ "es-errors": "^1.3.0",
+ "object-inspect": "^1.13.3",
+ "side-channel-list": "^1.0.0",
+ "side-channel-map": "^1.0.1",
+ "side-channel-weakmap": "^1.0.2"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/side-channel-list": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/side-channel-list/-/side-channel-list-1.0.1.tgz",
+ "integrity": "sha512-mjn/0bi/oUURjc5Xl7IaWi/OJJJumuoJFQJfDDyO46+hBWsfaVM65TBHq2eoZBhzl9EchxOijpkbRC8SVBQU0w==",
+ "license": "MIT",
+ "dependencies": {
+ "es-errors": "^1.3.0",
+ "object-inspect": "^1.13.4"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/side-channel-map": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/side-channel-map/-/side-channel-map-1.0.1.tgz",
+ "integrity": "sha512-VCjCNfgMsby3tTdo02nbjtM/ewra6jPHmpThenkTYh8pG9ucZ/1P8So4u4FGBek/BjpOVsDCMoLA/iuBKIFXRA==",
+ "license": "MIT",
+ "dependencies": {
+ "call-bound": "^1.0.2",
+ "es-errors": "^1.3.0",
+ "get-intrinsic": "^1.2.5",
+ "object-inspect": "^1.13.3"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/side-channel-weakmap": {
+ "version": "1.0.2",
+ "resolved": "https://registry.npmjs.org/side-channel-weakmap/-/side-channel-weakmap-1.0.2.tgz",
+ "integrity": "sha512-WPS/HvHQTYnHisLo9McqBHOJk2FkHO/tlpvldyrnem4aeQp4hai3gythswg6p01oSoTl58rcpiFAjF2br2Ak2A==",
+ "license": "MIT",
+ "dependencies": {
+ "call-bound": "^1.0.2",
+ "es-errors": "^1.3.0",
+ "get-intrinsic": "^1.2.5",
+ "object-inspect": "^1.13.3",
+ "side-channel-map": "^1.0.1"
+ },
+ "engines": {
+ "node": ">= 0.4"
+ },
+ "funding": {
+ "url": "https://github.com/sponsors/ljharb"
+ }
+ },
+ "node_modules/split2": {
+ "version": "4.2.0",
+ "resolved": "https://registry.npmjs.org/split2/-/split2-4.2.0.tgz",
+ "integrity": "sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg==",
+ "license": "ISC",
+ "engines": {
+ "node": ">= 10.x"
+ }
+ },
+ "node_modules/statuses": {
+ "version": "2.0.2",
+ "resolved": "https://registry.npmjs.org/statuses/-/statuses-2.0.2.tgz",
+ "integrity": "sha512-DvEy55V3DB7uknRo+4iOGT5fP1slR8wQohVdknigZPMpMstaKJQWhwiYBACJE3Ul2pTnATihhBYnRhZQHGBiRw==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/toidentifier": {
+ "version": "1.0.1",
+ "resolved": "https://registry.npmjs.org/toidentifier/-/toidentifier-1.0.1.tgz",
+ "integrity": "sha512-o5sSPKEkg/DIQNmH43V0/uerLrpzVedkUh8tGNvaeXpfpuwjKenlSox/2O/BTlZUtEe+JG7s5YhEz608PlAHRA==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.6"
+ }
+ },
+ "node_modules/type-is": {
+ "version": "2.0.1",
+ "resolved": "https://registry.npmjs.org/type-is/-/type-is-2.0.1.tgz",
+ "integrity": "sha512-OZs6gsjF4vMp32qrCbiVSkrFmXtG/AZhY3t0iAMrMBiAZyV9oALtXO8hsrHbMXF9x6L3grlFuwW2oAz7cav+Gw==",
+ "license": "MIT",
+ "dependencies": {
+ "content-type": "^1.0.5",
+ "media-typer": "^1.1.0",
+ "mime-types": "^3.0.0"
+ },
+ "engines": {
+ "node": ">= 0.6"
+ }
+ },
+ "node_modules/undici": {
+ "version": "7.25.0",
+ "resolved": "https://registry.npmjs.org/undici/-/undici-7.25.0.tgz",
+ "integrity": "sha512-xXnp4kTyor2Zq+J1FfPI6Eq3ew5h6Vl0F/8d9XU5zZQf1tX9s2Su1/3PiMmUANFULpmksxkClamIZcaUqryHsQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=20.18.1"
+ }
+ },
+ "node_modules/unpipe": {
+ "version": "1.0.0",
+ "resolved": "https://registry.npmjs.org/unpipe/-/unpipe-1.0.0.tgz",
+ "integrity": "sha512-pjy2bYhSsufwWlKwPc+l3cN7+wuJlK6uz0YdJEOlQDbl6jo/YlPi4mb8agUkVC8BF7V8NuzeyPNqRksA3hztKQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/vary": {
+ "version": "1.1.2",
+ "resolved": "https://registry.npmjs.org/vary/-/vary-1.1.2.tgz",
+ "integrity": "sha512-BNGbWLfd0eUPabhkXUVm0j8uuvREyTh5ovRa/dyow/BqAbZJyC+5fU+IzQOzmAKzYqYRAISoRhdQr3eIZ/PXqg==",
+ "license": "MIT",
+ "engines": {
+ "node": ">= 0.8"
+ }
+ },
+ "node_modules/whatwg-encoding": {
+ "version": "3.1.1",
+ "resolved": "https://registry.npmjs.org/whatwg-encoding/-/whatwg-encoding-3.1.1.tgz",
+ "integrity": "sha512-6qN4hJdMwfYBtE3YBTTHhoeuUrDBPZmbQaxWAqSALV/MeEnR5z1xd8UKud2RAkFoPkmB+hli1TZSnyi84xz1vQ==",
+ "deprecated": "Use @exodus/bytes instead for a more spec-conformant and faster implementation",
+ "license": "MIT",
+ "dependencies": {
+ "iconv-lite": "0.6.3"
+ },
+ "engines": {
+ "node": ">=18"
+ }
+ },
+ "node_modules/whatwg-mimetype": {
+ "version": "4.0.0",
+ "resolved": "https://registry.npmjs.org/whatwg-mimetype/-/whatwg-mimetype-4.0.0.tgz",
+ "integrity": "sha512-QaKxh0eNIi2mE9p2vEdzfagOKHCcj1pJ56EEHGQOVxp8r9/iszLUUV7v89x9O1p/T+NlTM5W7jW6+cz4Fq1YVg==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=18"
+ }
+ },
+ "node_modules/wrappy": {
+ "version": "1.0.2",
+ "resolved": "https://registry.npmjs.org/wrappy/-/wrappy-1.0.2.tgz",
+ "integrity": "sha512-l4Sp/DRseor9wL6EvV2+TuQn63dMkPjZ/sp9XkghTEbV9KlPS1xUsZ3u7/IQO4wxtcFB4bgpQPRcR3QCvezPcQ==",
+ "license": "ISC"
+ },
+ "node_modules/xtend": {
+ "version": "4.0.2",
+ "resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
+ "integrity": "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ==",
+ "license": "MIT",
+ "engines": {
+ "node": ">=0.4"
+ }
+ }
+ }
+}
diff --git a/package.json b/package.json
new file mode 100644
index 0000000..8e2e6ac
--- /dev/null
+++ b/package.json
@@ -0,0 +1,38 @@
+{
+ "name": "professional-directory",
+ "version": "0.1.0",
+ "private": true,
+ "description": "Compliance-first PostgreSQL data platform for licensed professionals (LA County doctors first). Powers profile sites, SEO pages, directories, CRM enrichment.",
+ "license": "UNLICENSED",
+ "workspaces": [
+ "agents/shared",
+ "agents/ingest-agent",
+ "agents/crawler-agent",
+ "agents/enrich-agent",
+ "agents/dedupe-agent",
+ "agents/api-agent"
+ ],
+ "scripts": {
+ "migrate": "node scripts/migrate.js",
+ "seed:sources": "psql -d doctor_professional_directory -f db/migrations/0002_seed_sources.sql",
+ "seed:la-zips": "psql -d doctor_professional_directory -f db/la_zips.sql",
+ "seed:specialties": "node scripts/seed_specialties.js",
+ "ingest:npi": "node agents/ingest-agent/importers/npi.js",
+ "ingest:npi:smoke": "SMOKE=1 node agents/ingest-agent/importers/npi.js",
+ "stats": "node scripts/stats.js",
+ "api": "node agents/api-agent/server.js",
+ "pm2:start": "pm2 start ecosystem.config.js",
+ "pm2:stop": "pm2 stop ecosystem.config.js",
+ "pm2:logs": "pm2 logs"
+ },
+ "dependencies": {
+ "cheerio": "^1.2.0",
+ "csv-parse": "^6.2.1",
+ "csv-stringify": "^6.7.0",
+ "dotenv": "^17.4.2",
+ "express": "^5.2.1",
+ "pg": "^8.20.0",
+ "robots-parser": "^3.0.1",
+ "undici": "^7.25.0"
+ }
+}
diff --git a/scripts/crawl-hospitals.js b/scripts/crawl-hospitals.js
new file mode 100644
index 0000000..bed9bf5
--- /dev/null
+++ b/scripts/crawl-hospitals.js
@@ -0,0 +1,171 @@
+#!/usr/bin/env node
+/**
+ * Hospital directory crawl dispatcher — Stage 5.
+ * Runs each hospital-system crawler module under crawlers/ and persists
+ * results back into professionals + professional_locations.
+ *
+ * Usage:
+ * node scripts/crawl-hospitals.js # all 10
+ * node scripts/crawl-hospitals.js ucla-health,cedars-sinai
+ * MAX_PAGES=5 node scripts/crawl-hospitals.js ucla-health # quick smoke
+ */
+const path = require('path');
+const fs = require('fs');
+const crypto = require('node:crypto');
+const { pool, query, withTx } = require('../agents/shared/db');
+
+const CRAWLER_DIR = path.resolve(__dirname, '../agents/crawler-agent/crawlers');
+const MAX_PAGES = process.env.MAX_PAGES ? Number(process.env.MAX_PAGES) : Infinity;
+
+function sha256(s) { return crypto.createHash('sha256').update(s).digest('hex'); }
+
+async function getSourceId(name) {
+ const r = await query('SELECT id FROM sources WHERE source_name = $1', [name]);
+ return r.rowCount ? r.rows[0].id : null;
+}
+
+async function startJob(sourceId, label) {
+ const r = await query(
+ `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, fields) {
+ const sets = []; const params = []; 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 persistProvider(provider, sourceId) {
+ if (!provider.name) return;
+
+ await withTx(async (client) => {
+ // Try to match an existing professional by NPI, then by name+specialty.
+ let professionalId;
+ if (provider.npi) {
+ const r = await client.query(`SELECT id FROM professionals WHERE npi_number = $1`, [provider.npi]);
+ if (r.rowCount) professionalId = r.rows[0].id;
+ }
+ if (!professionalId) {
+ const r = await client.query(`
+ SELECT id FROM professionals
+ WHERE LOWER(full_name) = LOWER($1)
+ OR (LOWER(last_name) = LOWER(SPLIT_PART($1, ' ', -1))
+ AND LOWER(first_name) = LOWER(SPLIT_PART($1, ' ', 1)))
+ LIMIT 2
+ `, [provider.name]);
+ if (r.rowCount === 1) professionalId = r.rows[0].id;
+ }
+
+ if (!professionalId) {
+ // New professional — name only, low confidence until license joins.
+ const r = await client.query(`
+ INSERT INTO professionals (full_name, primary_specialty, profile_image_url, bio, source_confidence_score)
+ VALUES ($1,$2,$3,$4,0.30)
+ RETURNING id
+ `, [provider.name, provider.specialty, provider.image_url, provider.bio]);
+ professionalId = r.rows[0].id;
+ } else {
+ await client.query(`
+ UPDATE professionals SET
+ primary_specialty = COALESCE(primary_specialty, $2),
+ profile_image_url = COALESCE(profile_image_url, $3),
+ bio = COALESCE(bio, $4),
+ medical_school = COALESCE(medical_school, $5),
+ source_confidence_score = GREATEST(COALESCE(source_confidence_score,0), 0.85),
+ updated_at = NOW()
+ WHERE id = $1
+ `, [professionalId, provider.specialty, provider.image_url, provider.bio, provider.medical_school]);
+ }
+
+ // Hospital affiliation.
+ const orgRes = await client.query(`SELECT id FROM organizations WHERE name = $1 LIMIT 1`, [provider.hospital_system]);
+ let hospId;
+ if (orgRes.rowCount) hospId = orgRes.rows[0].id;
+ else {
+ const ins = await client.query(`
+ INSERT INTO organizations (name, type, state, county) VALUES ($1,'hospital','CA','Los Angeles') RETURNING id
+ `, [provider.hospital_system]);
+ hospId = ins.rows[0].id;
+ }
+
+ await client.query(`
+ INSERT INTO professional_locations
+ (professional_id, organization_id, role, address, phone, source_url, last_verified_at)
+ VALUES ($1,$2,'privileges',$3,$4,$5,NOW())
+ ON CONFLICT DO NOTHING
+ `, [professionalId, hospId, provider.address, provider.phone, provider.source_url]);
+
+ if (provider.email) {
+ await client.query(`
+ INSERT INTO emails (professional_id, email, email_type, source_url, discovered_at, verification_status)
+ VALUES ($1, $2, 'office', $3, NOW(), 'unverified')
+ ON CONFLICT DO NOTHING
+ `, [professionalId, provider.email, provider.source_url]);
+ }
+ if (provider.phone) {
+ await client.query(`
+ INSERT INTO phones (professional_id, phone, phone_type, source_url, last_verified_at)
+ VALUES ($1, $2, 'office', $3, NOW())
+ ON CONFLICT DO NOTHING
+ `, [professionalId, provider.phone, provider.source_url]);
+ }
+
+ // Provenance.
+ const json = JSON.stringify(provider);
+ const hash = sha256(json + '|professional|' + professionalId);
+ await client.query(`
+ INSERT INTO raw_records (source_id, source_url, entity_type, entity_id, raw_json, hash)
+ VALUES ($1,$2,'professional',$3,$4::jsonb,$5)
+ ON CONFLICT (hash) DO NOTHING
+ `, [sourceId, provider.source_url, professionalId, json, hash]);
+ });
+}
+
+async function runCrawler(modName) {
+ const file = path.join(CRAWLER_DIR, `${modName}.js`);
+ if (!fs.existsSync(file)) {
+ console.warn(`[crawl] crawler not found: ${modName}`);
+ return 0;
+ }
+ const mod = require(file);
+ const sourceId = await getSourceId(mod.SOURCE_NAME);
+ if (!sourceId) {
+ console.warn(`[crawl] source not seeded for ${modName} — skipping`);
+ return 0;
+ }
+ const jobId = await startJob(sourceId, `crawl:${modName}`);
+ let inserted = 0, errors = 0;
+
+ try {
+ await mod.crawl({
+ onProvider: async (p) => {
+ try { await persistProvider(p, sourceId); inserted++; }
+ catch (e) { errors++; console.error(`[crawl] persist error: ${e.message}`); }
+ },
+ maxPages: MAX_PAGES === Infinity ? Infinity : MAX_PAGES,
+ });
+ await finishJob(jobId, { status: 'done', records_inserted: inserted, records_skipped: errors });
+ } catch (e) {
+ await finishJob(jobId, { status: 'failed', error_message: e.message, records_inserted: inserted });
+ throw e;
+ }
+ console.log(`[crawl:${modName}] inserted=${inserted} errors=${errors}`);
+ return inserted;
+}
+
+async function main() {
+ const targets = process.argv[2]
+ ? process.argv[2].split(',')
+ : fs.readdirSync(CRAWLER_DIR).filter(f => f.endsWith('.js')).map(f => f.replace(/\.js$/, ''));
+
+ for (const t of targets) {
+ try { await runCrawler(t); }
+ catch (e) { console.error(`[crawl:${t}] fatal: ${e.message}`); }
+ }
+ await pool.end();
+}
+
+main().catch((e) => { console.error(e); process.exit(1); });
diff --git a/scripts/dedupe.js b/scripts/dedupe.js
new file mode 100644
index 0000000..b0379f8
--- /dev/null
+++ b/scripts/dedupe.js
@@ -0,0 +1,114 @@
+#!/usr/bin/env node
+/**
+ * Dedupe + confidence scoring — Stage 8.
+ *
+ * Runs matchers in priority and writes each professional's final
+ * source_confidence_score, plus collapses duplicate organizations.
+ *
+ * Confidence rubric:
+ * 0.85+ NPI present AND license matched (MBC or DCA) AND has primary location
+ * 0.75 NPI + license OR NPI + hospital-affiliation match
+ * 0.50 NPI only (single-source)
+ * 0.30 No NPI, name-only match
+ */
+const { pool, query } = require('../agents/shared/db');
+
+async function recomputeProfessionalScores() {
+ console.log('[dedupe] recomputing professional confidence scores…');
+ await query(`
+ UPDATE professionals p SET source_confidence_score = sub.score, updated_at = NOW()
+ FROM (
+ SELECT
+ p.id,
+ CASE
+ WHEN p.npi_number IS NOT NULL
+ AND p.license_number IS NOT NULL
+ AND p.license_status IS NOT NULL
+ AND EXISTS(SELECT 1 FROM professional_locations pl WHERE pl.professional_id = p.id)
+ THEN 0.92
+ WHEN p.npi_number IS NOT NULL AND p.license_number IS NOT NULL
+ THEN 0.80
+ WHEN p.npi_number IS NOT NULL AND EXISTS(
+ SELECT 1 FROM professional_locations pl
+ WHERE pl.professional_id = p.id AND pl.organization_id IS NOT NULL)
+ THEN 0.70
+ WHEN p.npi_number IS NOT NULL
+ THEN 0.55
+ ELSE 0.30
+ END AS score
+ FROM professionals p
+ ) sub
+ WHERE p.id = sub.id
+ `, []);
+
+ const r = await query(`
+ SELECT
+ ROUND(AVG(source_confidence_score)::numeric, 2) AS avg_score,
+ COUNT(*) FILTER (WHERE source_confidence_score >= 0.85) AS high,
+ COUNT(*) FILTER (WHERE source_confidence_score >= 0.50 AND source_confidence_score < 0.85) AS medium,
+ COUNT(*) FILTER (WHERE source_confidence_score < 0.50) AS low
+ FROM professionals
+ `, []);
+ console.log('[dedupe] confidence buckets →', r.rows[0]);
+}
+
+async function dedupeOrgsByNpi() {
+ console.log('[dedupe] collapsing duplicate organizations by NPI…');
+ // Already enforced by UNIQUE(npi_number); just safety check counts.
+ const r = await query(`
+ SELECT npi_number, COUNT(*) AS n
+ FROM organizations
+ WHERE npi_number IS NOT NULL
+ GROUP BY npi_number HAVING COUNT(*) > 1
+ `, []);
+ console.log(`[dedupe] orgs with duplicate NPI: ${r.rowCount}`);
+}
+
+async function dedupeOrgsByCcnHcai() {
+ console.log('[dedupe] merging orgs that share an HCAI ID or CMS CCN…');
+ // Pick canonical row (lowest id), reassign foreign keys, delete the others.
+ for (const col of ['hcai_id', 'cdph_license']) {
+ const dupes = await query(`
+ SELECT ${col} AS key, MIN(id) AS keep_id, ARRAY_AGG(id) AS all_ids
+ FROM organizations
+ WHERE ${col} IS NOT NULL
+ GROUP BY ${col} HAVING COUNT(*) > 1
+ `, []);
+ for (const dupe of dupes.rows) {
+ const drop = dupe.all_ids.filter(x => x !== dupe.keep_id);
+ if (drop.length === 0) continue;
+ await query(`UPDATE professional_locations SET organization_id = $1 WHERE organization_id = ANY($2)`,
+ [dupe.keep_id, drop]);
+ await query(`UPDATE phones SET organization_id = $1 WHERE organization_id = ANY($2)`,
+ [dupe.keep_id, drop]);
+ await query(`UPDATE emails SET organization_id = $1 WHERE organization_id = ANY($2)`,
+ [dupe.keep_id, drop]);
+ await query(`DELETE FROM organizations WHERE id = ANY($1)`, [drop]);
+ console.log(`[dedupe] ${col}=${dupe.key} kept=${dupe.keep_id} dropped=${drop.length}`);
+ }
+ }
+}
+
+async function tagPrivatePractice() {
+ console.log('[dedupe] tagging single-doctor orgs as private_practice…');
+ await query(`
+ UPDATE organizations o SET type = 'private_practice', updated_at = NOW()
+ WHERE o.type = 'medical_group'
+ AND o.id IN (
+ SELECT organization_id FROM professional_locations
+ WHERE organization_id IS NOT NULL
+ GROUP BY organization_id
+ HAVING COUNT(DISTINCT professional_id) = 1
+ )
+ `, []);
+}
+
+async function main() {
+ await dedupeOrgsByCcnHcai();
+ await dedupeOrgsByNpi();
+ await tagPrivatePractice();
+ await recomputeProfessionalScores();
+ await pool.end();
+}
+
+main().catch((e) => { console.error('[dedupe] fatal:', e); process.exit(1); });
diff --git a/scripts/download_npi_bulk.sh b/scripts/download_npi_bulk.sh
new file mode 100644
index 0000000..59a4a69
--- /dev/null
+++ b/scripts/download_npi_bulk.sh
@@ -0,0 +1,31 @@
+#!/usr/bin/env bash
+# Download the latest NPPES NPI Registry monthly bulk file.
+# Free, public-domain data, no key. ~7-8 GB zipped, ~30 GB extracted (CSV).
+#
+# Usage:
+# bash scripts/download_npi_bulk.sh [MonthYear]
+# bash scripts/download_npi_bulk.sh April_2026
+#
+# After download:
+# unzip -d data/nppes/ data/nppes/NPPES_Data_Dissemination_*.zip
+# npm run ingest:npi # streams the npidata_pfile_*.csv
+
+set -euo pipefail
+
+MONTH_YEAR="${1:-April_2026}"
+URL="https://download.cms.gov/nppes/NPPES_Data_Dissemination_${MONTH_YEAR}_V2.zip"
+DEST_DIR="$(cd "$(dirname "$0")/.." && pwd)/data/nppes"
+DEST="$DEST_DIR/NPPES_Data_Dissemination_${MONTH_YEAR}_V2.zip"
+
+mkdir -p "$DEST_DIR"
+
+echo "[npi-dl] URL = $URL"
+echo "[npi-dl] dest = $DEST"
+
+# Resume-friendly download
+curl -L -C - --retry 5 --retry-delay 30 \
+ --output "$DEST" \
+ "$URL"
+
+echo "[npi-dl] downloaded: $(du -sh "$DEST" | cut -f1)"
+echo "[npi-dl] next: unzip -d $DEST_DIR $DEST"
diff --git a/scripts/link_locations_to_orgs.js b/scripts/link_locations_to_orgs.js
new file mode 100644
index 0000000..d9352f2
--- /dev/null
+++ b/scripts/link_locations_to_orgs.js
@@ -0,0 +1,93 @@
+#!/usr/bin/env node
+/**
+ * Backfill professional_locations.organization_id.
+ *
+ * The NPI bulk importer writes a professional_locations row per Type-1 NPI
+ * but never sets organization_id (Type-1 rows don't carry an org NPI). This
+ * leaves every ZIP/neighborhood query returning 0 doctors per org.
+ *
+ * Strategy — two passes, conservative first:
+ * 1. addr_norm + phone match (single best org per location)
+ * 2. addr_norm only, but only when exactly one org sits at that addr_norm
+ *
+ * Re-runnable: only touches rows where organization_id IS NULL.
+ */
+const { pool, query } = require('../agents/shared/db');
+
+async function pass1Strict() {
+ console.log('[link] pass 1: strict (addr_norm + phone) match…');
+ const r = await query(`
+ WITH cand AS (
+ SELECT pl.id AS pl_id, o.id AS org_id,
+ ROW_NUMBER() OVER (
+ PARTITION BY pl.id
+ ORDER BY o.id
+ ) AS rn
+ FROM professional_locations pl
+ JOIN organizations o
+ ON lower(regexp_replace(pl.address, '[^a-zA-Z0-9]', '', 'g')) =
+ lower(regexp_replace(o.address, '[^a-zA-Z0-9]', '', 'g'))
+ AND pl.phone = o.phone
+ WHERE pl.organization_id IS NULL
+ AND pl.address IS NOT NULL
+ AND o.address IS NOT NULL
+ AND o.opted_out = FALSE
+ )
+ UPDATE professional_locations pl
+ SET organization_id = cand.org_id
+ FROM cand
+ WHERE pl.id = cand.pl_id AND cand.rn = 1
+ RETURNING pl.id
+ `, []);
+ console.log(`[link] pass 1 linked ${r.rowCount} locations`);
+ return r.rowCount;
+}
+
+async function pass2AddrUnique() {
+ console.log('[link] pass 2: addr_norm match where exactly one org at that addr_norm…');
+ const r = await query(`
+ WITH org_singletons AS (
+ SELECT lower(regexp_replace(address, '[^a-zA-Z0-9]', '', 'g')) AS a_norm,
+ MIN(id) AS org_id,
+ COUNT(*) AS n
+ FROM organizations
+ WHERE address IS NOT NULL AND opted_out = FALSE
+ GROUP BY 1
+ HAVING COUNT(*) = 1
+ )
+ UPDATE professional_locations pl
+ SET organization_id = s.org_id
+ FROM org_singletons s
+ WHERE pl.organization_id IS NULL
+ AND pl.address IS NOT NULL
+ AND lower(regexp_replace(pl.address, '[^a-zA-Z0-9]', '', 'g')) = s.a_norm
+ RETURNING pl.id
+ `, []);
+ console.log(`[link] pass 2 linked ${r.rowCount} locations`);
+ return r.rowCount;
+}
+
+async function summary() {
+ const r = await query(`
+ SELECT
+ (SELECT COUNT(*) FROM professional_locations) AS total,
+ (SELECT COUNT(*) FROM professional_locations WHERE organization_id IS NOT NULL) AS linked,
+ (SELECT COUNT(*) FROM professional_locations WHERE organization_id IS NULL) AS unlinked
+ `, []);
+ const row = r.rows[0];
+ const pct = ((Number(row.linked) / Number(row.total)) * 100).toFixed(1);
+ console.log(`[link] total=${row.total} linked=${row.linked} (${pct}%) unlinked=${row.unlinked}`);
+}
+
+async function main() {
+ await pass1Strict();
+ await pass2AddrUnique();
+ await summary();
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[link] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/scripts/migrate.js b/scripts/migrate.js
new file mode 100644
index 0000000..c0adf3a
--- /dev/null
+++ b/scripts/migrate.js
@@ -0,0 +1,53 @@
+#!/usr/bin/env node
+/**
+ * Plain SQL migration runner.
+ * - Tracks applied migrations in schema_migrations table.
+ * - Applies db/schema.sql first (idempotent), then any db/migrations/*.sql in lexical order.
+ */
+const fs = require('node:fs');
+const path = require('node:path');
+const { pool } = require('../agents/shared/db');
+
+const ROOT = path.resolve(__dirname, '..');
+const SCHEMA = path.join(ROOT, 'db/schema.sql');
+const MIG_DIR = path.join(ROOT, 'db/migrations');
+
+(async () => {
+ await pool.query(`
+ CREATE TABLE IF NOT EXISTS schema_migrations (
+ filename TEXT PRIMARY KEY,
+ applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+ );
+ `);
+
+ // schema.sql is idempotent — apply every run
+ console.log('→ applying db/schema.sql');
+ await pool.query(fs.readFileSync(SCHEMA, 'utf8'));
+ console.log('✓ schema.sql');
+
+ if (fs.existsSync(MIG_DIR)) {
+ const files = fs.readdirSync(MIG_DIR)
+ .filter(f => f.endsWith('.sql') && !f.startsWith('0001_init')) // 0001 = pointer to schema.sql
+ .sort();
+
+ for (const f of files) {
+ const exists = await pool.query('SELECT 1 FROM schema_migrations WHERE filename = $1', [f]);
+ if (exists.rowCount > 0) { console.log(`✓ already applied ${f}`); continue; }
+ const sql = fs.readFileSync(path.join(MIG_DIR, f), 'utf8');
+ console.log(`→ applying ${f}`);
+ const client = await pool.connect();
+ try {
+ await client.query(sql);
+ await client.query('INSERT INTO schema_migrations(filename) VALUES ($1)', [f]);
+ console.log(`✓ applied ${f}`);
+ } finally {
+ client.release();
+ }
+ }
+ }
+
+ await pool.end();
+})().catch(err => {
+ console.error('migrate failed:', err);
+ process.exit(1);
+});
diff --git a/scripts/run-all-night.sh b/scripts/run-all-night.sh
new file mode 100755
index 0000000..0d63cf2
--- /dev/null
+++ b/scripts/run-all-night.sh
@@ -0,0 +1,65 @@
+#!/usr/bin/env bash
+# Run-all-night orchestrator.
+# Runs every Stage 2-8 importer/enricher serially, in dependency order, and
+# tees output to logs/run-all-night.log. Idempotent — safe to re-run.
+#
+# Pre-reqs: NPI bulk ingest has already completed (~13-15K LA professionals + orgs).
+#
+# Usage:
+# bash scripts/run-all-night.sh
+# tail -f logs/run-all-night.log
+
+set -uo pipefail
+cd "$(dirname "$0")/.."
+mkdir -p logs
+
+LOG="logs/run-all-night.log"
+exec > >(tee -a "$LOG") 2>&1
+
+step() {
+ echo
+ echo "═══════════════════════════════════════════════════════════════════"
+ echo "▸ $1 ($(date -u +%FT%TZ))"
+ echo "═══════════════════════════════════════════════════════════════════"
+}
+
+run() {
+ local label="$1"; shift
+ step "$label"
+ if "$@"; then
+ echo "✓ $label"
+ else
+ echo "✗ $label (continuing — not fatal)"
+ fi
+}
+
+# ─── Stage 4: facilities (free public bulk files) ─────────────────────────
+run "HCAI / CDPH licensed health facilities" node agents/ingest-agent/importers/hcai.js
+run "CMS Hospital General Information" node agents/ingest-agent/importers/cms-hospitals.js
+run "HRSA FQHCs / community health centers" node agents/ingest-agent/importers/hrsa.js
+
+# ─── Stage 3: CMS Care Compare (DAC) — joins by NPI ───────────────────────
+run "CMS Care Compare (Doctors and Clinicians)" node agents/ingest-agent/importers/cms-care-compare.js
+
+# ─── Stage 2: license enrichment ─────────────────────────────────────────
+# DCA is an HTTP scraper — slow but free. Caps at 0.5 rps.
+run "DCA license status enrichment" node agents/ingest-agent/importers/dca.js
+
+# MBC needs a CSV the user converted from the Access DB; skip if absent.
+if [ -f data/mbc/mbc_licenses.csv ]; then
+ run "CA Medical Board (MBC) bulk join" node agents/ingest-agent/importers/mbc.js
+else
+ echo "› skip MBC (data/mbc/mbc_licenses.csv not present)"
+fi
+
+# ─── Stage 6: free geocoding ─────────────────────────────────────────────
+run "Geocode (US Census → OSM Nominatim fallback)" node agents/enrich-agent/geocode.js
+
+# ─── Stats ────────────────────────────────────────────────────────────────
+run "Final stats" node scripts/stats.js
+
+echo
+echo "═══════════════════════════════════════════════════════════════════"
+echo "All-night run complete: $(date -u +%FT%TZ)"
+echo "API: node agents/api-agent/server.js → http://127.0.0.1:9874"
+echo "═══════════════════════════════════════════════════════════════════"
diff --git a/scripts/seed_specialties.js b/scripts/seed_specialties.js
new file mode 100644
index 0000000..311b381
--- /dev/null
+++ b/scripts/seed_specialties.js
@@ -0,0 +1,84 @@
+#!/usr/bin/env node
+/**
+ * Seed the `specialties` table from the official NUCC Health Care Provider
+ * Taxonomy code set. Free public download — no key required.
+ *
+ * https://www.nucc.org/ → Code Sets → Provider Taxonomy
+ *
+ * The CSV ships with these columns (varies slightly across versions, but
+ * `Code` and `Display Name`/`Classification` are stable):
+ * Code, Grouping, Classification, Specialization, Definition, Notes,
+ * Display Name, Section
+ */
+const fs = require('node:fs');
+const path = require('node:path');
+const { parse } = require('csv-parse/sync');
+const { fetch } = require('undici');
+const { pool, query } = require('../agents/shared/db');
+
+const NUCC_URL = process.env.NUCC_URL
+ || 'https://www.nucc.org/images/stories/CSV/nucc_taxonomy_250.csv';
+
+const CACHE = path.resolve(__dirname, '..', 'data', 'nucc_taxonomy.csv');
+
+async function downloadIfNeeded() {
+ if (fs.existsSync(CACHE)) {
+ const stat = fs.statSync(CACHE);
+ const ageDays = (Date.now() - stat.mtimeMs) / 86400000;
+ if (ageDays < 30) {
+ console.log(`[nucc] using cache ${CACHE} (${ageDays.toFixed(1)}d old)`);
+ return fs.readFileSync(CACHE, 'utf8');
+ }
+ }
+ console.log(`[nucc] downloading ${NUCC_URL}`);
+ const res = await fetch(NUCC_URL, { signal: AbortSignal.timeout(30000) });
+ if (!res.ok) throw new Error(`NUCC download failed: HTTP ${res.status}`);
+ const body = await res.text();
+ fs.mkdirSync(path.dirname(CACHE), { recursive: true });
+ fs.writeFileSync(CACHE, body);
+ console.log(`[nucc] cached → ${CACHE} (${body.length} bytes)`);
+ return body;
+}
+
+function pickName(row) {
+ // Prefer "Display Name", then Classification + Specialization, else Code.
+ const display = (row['Display Name'] || '').trim();
+ if (display) return display;
+ const cls = (row['Classification'] || '').trim();
+ const spec = (row['Specialization'] || '').trim();
+ if (cls && spec) return `${cls} — ${spec}`;
+ return cls || (row['Code'] || '').trim();
+}
+
+async function main() {
+ const csv = await downloadIfNeeded();
+ const rows = parse(csv, { columns: true, skip_empty_lines: true, bom: true, relax_column_count: true });
+ console.log(`[nucc] parsed ${rows.length} taxonomy rows`);
+
+ let inserted = 0, updated = 0;
+ for (const row of rows) {
+ const code = (row['Code'] || '').trim();
+ if (!code) continue;
+ const name = pickName(row);
+ const parent = (row['Grouping'] || row['Classification'] || null) || null;
+
+ const r = await query(`
+ INSERT INTO specialties (name, taxonomy_code, parent_specialty)
+ VALUES ($1, $2, $3)
+ ON CONFLICT (taxonomy_code) DO UPDATE
+ SET name = EXCLUDED.name,
+ parent_specialty = COALESCE(EXCLUDED.parent_specialty, specialties.parent_specialty)
+ RETURNING (xmax = 0) AS is_insert
+ `, [name, code, parent]);
+ if (r.rows[0].is_insert) inserted++; else updated++;
+ }
+
+ console.log(`[nucc] done. inserted=${inserted} updated=${updated}`);
+ await pool.end();
+}
+
+main().catch(async (err) => {
+ console.error('[nucc] fatal:', err);
+ try { await pool.end(); } catch (_) {}
+ process.exit(1);
+});
diff --git a/scripts/stats.js b/scripts/stats.js
new file mode 100644
index 0000000..25d3540
--- /dev/null
+++ b/scripts/stats.js
@@ -0,0 +1,68 @@
+#!/usr/bin/env node
+/**
+ * Quick console stats — run any time to see what's in the DB.
+ * node scripts/stats.js
+ */
+const { pool, query } = require('../agents/shared/db');
+
+async function main() {
+ const counts = await query(`
+ SELECT
+ (SELECT COUNT(*) FROM professionals WHERE opted_out=false) AS professionals,
+ (SELECT COUNT(*) FROM organizations WHERE opted_out=false) AS organizations,
+ (SELECT COUNT(*) FROM organizations WHERE type='hospital' AND opted_out=false) AS hospitals,
+ (SELECT COUNT(*) FROM organizations WHERE type='medical_group' AND opted_out=false) AS medical_groups,
+ (SELECT COUNT(*) FROM organizations WHERE type='clinic' AND opted_out=false) AS clinics,
+ (SELECT COUNT(*) FROM organizations WHERE type='surgery_center' AND opted_out=false) AS surgery_centers,
+ (SELECT COUNT(*) FROM organizations WHERE type='urgent_care' AND opted_out=false) AS urgent_cares,
+ (SELECT COUNT(*) FROM organizations WHERE type='fqhc' AND opted_out=false) AS fqhcs,
+ (SELECT COUNT(*) FROM organizations WHERE lat IS NOT NULL) AS geocoded_orgs,
+ (SELECT COUNT(*) FROM professionals WHERE license_number IS NOT NULL) AS with_license,
+ (SELECT COUNT(*) FROM professionals WHERE license_status='Active') AS active_license,
+ (SELECT COUNT(*) FROM professionals WHERE medical_school IS NOT NULL) AS with_school,
+ (SELECT COUNT(*) FROM raw_records) AS raw_records,
+ (SELECT COUNT(*) FROM specialties) AS specialties
+ `, []);
+ console.table(counts.rows[0]);
+
+ console.log('\nTop specialties:');
+ const top = await query(`
+ SELECT s.name, COUNT(*) AS n
+ FROM professional_specialties ps
+ JOIN specialties s ON s.id = ps.specialty_id
+ JOIN professionals p ON p.id = ps.professional_id
+ WHERE p.opted_out = false
+ GROUP BY s.name ORDER BY n DESC LIMIT 15
+ `, []);
+ console.table(top.rows);
+
+ console.log('\nDoctors per top LA-area ZIP:');
+ const byZip = await query(`
+ SELECT o.zip, z.city, COUNT(DISTINCT p.id) AS doctors,
+ COUNT(DISTINCT o.id) FILTER (WHERE o.type='hospital') AS hospitals,
+ COUNT(DISTINCT o.id) AS orgs
+ FROM la_zips z
+ LEFT JOIN organizations o ON o.zip = z.zip AND o.opted_out=false
+ LEFT JOIN professional_locations pl ON pl.organization_id = o.id
+ LEFT JOIN professionals p ON p.id = pl.professional_id AND p.opted_out=false
+ GROUP BY o.zip, z.city
+ HAVING COUNT(DISTINCT p.id) + COUNT(DISTINCT o.id) > 0
+ ORDER BY doctors DESC NULLS LAST LIMIT 20
+ `, []);
+ console.table(byZip.rows);
+
+ console.log('\nBeverly Hills (90210/90211/90212):');
+ const bh = await query(`
+ SELECT o.zip, COUNT(DISTINCT o.id) AS orgs,
+ COUNT(DISTINCT pl.professional_id) AS doctors
+ FROM organizations o
+ LEFT JOIN professional_locations pl ON pl.organization_id = o.id
+ WHERE o.zip IN ('90210','90211','90212') AND o.opted_out=false
+ GROUP BY o.zip ORDER BY o.zip
+ `, []);
+ console.table(bh.rows);
+
+ await pool.end();
+}
+
+main().catch((e) => { console.error(e); process.exit(1); });
(oldest)
·
back to Professional Directory
·
add apply-templates.js — deterministic mockup template-rotat a5d87d5 →