← back to Restaurant Directory
scripts/ingest-pasadena.ts
257 lines
#!/usr/bin/env tsx
/**
* Ingest Pasadena food-establishment permits + inspections.
*
* Source: https://healthinspectionreports.cityofpasadena.net (PPHD Envision portal)
* No bulk download — JSON API endpoints, must iterate A-Z prefixes.
*
* Strategy:
* 1. Iterate keyword prefixes A-Z, 0-9 across SearchEnvisionPermits.
* 2. Dedupe by BlLicenseId.
* 3. For each facility, fetch inspection history via GetEnvisionInspections.
* 4. Insert into facility (source_dataset_id='pasadena_pphd_envision') and inspection.
* Pasadena uses numeric scores (0-100), no letter grade — store NULL grade.
* 5. No lat/lng in source — leave NULL; geocoding deferred to v2.
*
* Polite: 1.5s delay between facility detail calls.
*/
import { Pool } from 'pg';
const BASE = 'https://healthinspectionreports.cityofpasadena.net';
const DATASET_ID = 'pasadena_pphd_envision';
const DELAY_MS = 1500;
const pool = new Pool({
host: process.env.PGHOST ?? '/tmp',
database: process.env.PGDATABASE ?? 'restaurant_professional_directory',
user: process.env.PGUSER ?? process.env.USER,
max: 5,
});
const sleep = (ms: number) => new Promise(r => setTimeout(r, ms));
type SearchRow = {
PermitCategory: string;
CompanyName: string;
FullAddressLine1: string;
FullAddressLine2: string;
BlLicenseId: string;
};
type InspectionRow = {
RegistrationId: string;
Service?: string;
InspectionType?: string;
InspectionDate?: string;
InspectionScore?: number;
TotalViolationScore?: number;
Violations?: Array<{
ViolationCode?: string;
ViolationItem?: string;
ViolationText?: string;
Score?: number;
ViolationStatus?: string;
IsOpenViolation?: string;
InspectorComments?: string;
}>;
};
async function searchPrefix(keyword: string): Promise<SearchRow[]> {
const out: SearchRow[] = [];
let page = 1;
const PAGE_SIZE = 100;
for (;;) {
const res = await fetch(`${BASE}/Inspection/SearchEnvisionPermits`, {
method: 'POST',
headers: { 'Content-Type': 'application/json', 'Accept': 'application/json' },
body: JSON.stringify({ keyword, page, pageSize: PAGE_SIZE }),
});
if (!res.ok) {
console.error(`[pasadena] search "${keyword}" page ${page} failed: ${res.status}`);
break;
}
const data = await res.json() as { Success: boolean; Total: number; Rows: SearchRow[] };
if (!data.Success) break;
out.push(...(data.Rows || []));
if (page * PAGE_SIZE >= data.Total) break;
page++;
await sleep(500);
}
return out;
}
function slugify(name: string, address: string | null, blid: string): string {
const base = [name, address].filter(Boolean).join(' ');
return base
.toLowerCase()
.normalize('NFKD')
.replace(/[̀-ͯ]/g, '')
.replace(/[^a-z0-9]+/g, '-')
.replace(/^-|-$/g, '')
.slice(0, 110) + '-' + blid.toLowerCase();
}
function parseAddress(line1: string, line2: string): {
address: string | null; city: string | null; zip: string | null;
} {
// line1 = "175 N RAYMOND AVE" ; line2 = "PASADENA, CA, 91103" (typical)
const address = line1?.trim() || null;
let city: string | null = null, zip: string | null = null;
if (line2) {
const m = line2.match(/^([^,]+),\s*[A-Z]{2}[,\s]*(\d{5})/);
if (m) {
city = m[1].trim().toUpperCase();
zip = m[2];
} else {
city = line2.split(',')[0]?.trim().toUpperCase() ?? null;
}
}
return { address, city, zip };
}
function classifyType(category: string): {
facility_type: string | null; pe_description: string;
} {
const c = (category || '').toLowerCase();
if (c.includes('restaurant') || c.includes('food')) return { facility_type: 'restaurant', pe_description: category };
if (c.includes('market')) return { facility_type: 'market', pe_description: category };
if (c.includes('truck') || c.includes('mobile') || c.includes('cart')) return { facility_type: 'mobile', pe_description: category };
if (c.includes('bakery')) return { facility_type: 'bakery', pe_description: category };
return { facility_type: null, pe_description: category };
}
async function getInspections(blid: string): Promise<InspectionRow[]> {
try {
const res = await fetch(`${BASE}/Inspection/GetEnvisionInspections`, {
method: 'POST',
headers: { 'Content-Type': 'application/json', 'Accept': 'application/json' },
body: JSON.stringify({ entityId: blid, page: 1, pageSize: 50 }),
});
if (!res.ok) return [];
const data = await res.json() as { Inspections?: InspectionRow[] };
return data.Inspections || [];
} catch (e) {
console.error(`[pasadena] inspections ${blid} failed:`, (e as Error).message);
return [];
}
}
async function main() {
const startedAt = new Date();
console.log(`[pasadena] starting at ${startedAt.toISOString()}`);
// Phase 1: facility list via A-Z prefix walk + dedupe
const facilities = new Map<string, SearchRow>();
const prefixes = [..."abcdefghijklmnopqrstuvwxyz".split(""), ..."0123456789".split("")];
for (const p of prefixes) {
const rows = await searchPrefix(p);
for (const r of rows) {
if (r.BlLicenseId) facilities.set(r.BlLicenseId, r);
}
process.stdout.write(`\r[pasadena] prefix "${p}" → ${facilities.size} unique`);
await sleep(300);
}
console.log(`\n[pasadena] discovered ${facilities.size} unique food establishments`);
// Phase 2: write facility rows
let upsertOk = 0, upsertFail = 0;
for (const f of facilities.values()) {
const { address, city, zip } = parseAddress(f.FullAddressLine1, f.FullAddressLine2);
// Force city = PASADENA for Pasadena dataset (zip might disagree but the
// permit is a Pasadena permit by definition)
const finalCity = city || 'PASADENA';
const { facility_type, pe_description } = classifyType(f.PermitCategory);
const slug = slugify(f.CompanyName, address, f.BlLicenseId);
try {
await pool.query(
`INSERT INTO facility (
facility_id, slug, name, address, city, state, zip,
pe_description, facility_type,
source_dataset_id, source_pulled_at, last_updated_at
) VALUES ($1, $2, $3, $4, $5, 'CA', $6, $7, $8, $9, $10, $10)
ON CONFLICT (facility_id) DO UPDATE SET
name = EXCLUDED.name,
address = EXCLUDED.address,
pe_description = EXCLUDED.pe_description,
source_pulled_at = EXCLUDED.source_pulled_at`,
[f.BlLicenseId, slug, f.CompanyName, address, finalCity, zip,
pe_description, facility_type, DATASET_ID, startedAt]
);
upsertOk++;
} catch (e) {
upsertFail++;
if (upsertFail < 5) console.error(`[pasadena] facility ${f.BlLicenseId} failed:`, (e as Error).message);
}
}
console.log(`[pasadena] facilities: ok=${upsertOk} fail=${upsertFail}`);
// Phase 3: inspections (throttled, 1.5s/each)
let insOk = 0, insFail = 0;
let i = 0;
for (const blid of facilities.keys()) {
i++;
const insps = await getInspections(blid);
for (const ins of insps) {
const date = ins.InspectionDate ? new Date(ins.InspectionDate) : null;
if (!date || isNaN(date.getTime())) continue;
const serial = `${blid}-${date.toISOString().slice(0, 19).replace(/[^0-9]/g, '')}`;
try {
// Insert inspection row (no letter grade — Pasadena uses numeric only)
await pool.query(
`INSERT INTO inspection (
facility_id, serial_number, activity_date, service_code, service_description,
score, grade, program_status, source_pulled_at
) VALUES ($1, $2, $3, $4, $5, $6, NULL, 'ACTIVE', $7)
ON CONFLICT (serial_number) DO UPDATE SET
score = EXCLUDED.score,
service_description = EXCLUDED.service_description,
source_pulled_at = EXCLUDED.source_pulled_at`,
[blid, serial, date, ins.Service ?? null, ins.InspectionType ?? null,
ins.InspectionScore ?? null, startedAt]
);
// Insert violations
for (const v of (ins.Violations || [])) {
if (!v.ViolationCode && !v.ViolationText) continue;
await pool.query(
`INSERT INTO violation (serial_number, violation_status, violation_code, violation_description, points)
VALUES ($1, $2, $3, $4, $5)`,
[serial, v.ViolationStatus ?? null, v.ViolationCode ?? null,
[v.ViolationItem, v.ViolationText, v.InspectorComments].filter(Boolean).join(' — ') || null,
v.Score ?? null]
);
}
insOk++;
} catch (e) {
insFail++;
if (insFail < 5) console.error(`[pasadena] inspection ${serial} failed:`, (e as Error).message);
}
}
if (i % 100 === 0) {
const dt = (Date.now() - startedAt.getTime()) / 1000;
const rate = (i / dt).toFixed(2);
console.log(`[pasadena] ${i}/${facilities.size} · ins ok=${insOk} fail=${insFail} · ${rate}/s`);
}
await sleep(DELAY_MS);
}
console.log(`[pasadena] DONE — facilities=${facilities.size} inspections=${insOk} (failed=${insFail})`);
await pool.query(
`INSERT INTO ingest_run (dataset, source_url, started_at, finished_at,
rows_pulled, rows_inserted, rows_failed)
VALUES ('pasadena_inventory', $1, $2, now(), $3, $4, $5)`,
[BASE, startedAt, facilities.size, upsertOk, upsertFail]
);
await pool.end();
}
main().catch(async (e) => {
console.error('[pasadena] FATAL:', e);
await pool.end();
process.exit(1);
});