`scripts/ingest-santa-monica-permits.ts`

91 lines
/**
 * ingest-santa-monica-permits.ts
 *
 * Santa Monica Active Building & Safety Permits via CKAN datastore_search.
 * Source: data.santamonica.gov resource d6867c7d-89bc-4975-be35-4d2673a4764b
 * Verified 2026-04-30: 1,997 rows. ODC-BY 1.0 license.
 */
import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.PGHOST ?? '/tmp',
  database: process.env.PGDATABASE ?? 'stayclaim',
  user: process.env.PGUSER ?? process.env.USER,
  password: process.env.PGPASSWORD,
  port: parseInt(process.env.PGPORT ?? '5432', 10),
  max: 4,
});

const RESOURCE = 'd6867c7d-89bc-4975-be35-4d2673a4764b';
const URL = `https://data.santamonica.gov/api/3/action/datastore_search`;
const PAGE = 1000;

function canonicalize(addr: string): string {
  return addr.toLowerCase().replace(/[^\w\s-]/g, '').replace(/\s+/g, '-').replace(/-+/g, '-').replace(/^-|-$/g, '').slice(0, 90);
}

async function fetchPage(offset: number) {
  const u = `${URL}?resource_id=${RESOURCE}&limit=${PAGE}&offset=${offset}`;
  const res = await fetch(u);
  const j = await res.json() as { result: { records: any[]; total: number } };
  return j.result;
}

async function upsertOne(r: any) {
  const addr = (r.address ?? '').toString().trim();
  if (!addr) return;
  const permitNbr = (r.permit_number ?? r._id ?? '').toString().trim();
  if (!permitNbr) return;
  const canonical = canonicalize(addr);
  if (!canonical) return;
  const lat = r.latitude ? parseFloat(r.latitude) : null;
  const lon = r.longitude ? parseFloat(r.longitude) : null;

  const lid = (await pool.query<{ id: string }>(
    `INSERT INTO listing (slug, source, source_id, title, address_line1, city, state, country, latitude, longitude, is_public, tier)
     VALUES ($1,'santa_monica_permit',$2,$3,$4,'Santa Monica','CA','US',$5,$6,true,'free')
     ON CONFLICT (slug) DO UPDATE SET
       latitude = COALESCE(listing.latitude, EXCLUDED.latitude),
       longitude = COALESCE(listing.longitude, EXCLUDED.longitude),
       updated_at = now()
     RETURNING id`,
    [`${canonical}-sm`, `santamonica:${canonical}`, addr, addr, lat, lon]
  )).rows[0]!.id;

  const issueDate = r.date_issued ? r.date_issued.slice(0, 10) : null;
  await pool.query(
    `INSERT INTO permit (source, source_dataset, permit_number, listing_id, primary_address, apn,
       permit_type, permit_sub_type, status_desc, issue_date, valuation, work_desc,
       latitude, longitude, source_url, source_label)
     VALUES ('santa_monica','sm-active-permits',$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,'City of Santa Monica')
     ON CONFLICT (source_dataset, permit_number) DO UPDATE SET
       status_desc = COALESCE(EXCLUDED.status_desc, permit.status_desc),
       retrieved_at = now()`,
    [
      permitNbr, lid, addr, r.parcel_no ?? null,
      r.permit_type_desc ?? r.permit_type ?? null, r.permit_sub_type ?? null,
      r.status ?? null, issueDate, parseFloat(r.valuation ?? '0') || null,
      r.description ?? null, lat, lon,
      `https://data.santamonica.gov/dataset/active-building-and-safety-permits/resource/${RESOURCE}`,
    ]
  );
}

async function main() {
  let offset = 0;
  let total = 0;
  while (true) {
    const { records, total: tot } = await fetchPage(offset);
    if (!records.length) break;
    for (const r of records) await upsertOne(r);
    total += records.length;
    console.log(`  santa_monica: ${total}/${tot}`);
    if (records.length < PAGE) break;
    offset += PAGE;
  }
  console.log(`✓ Santa Monica permits: ${total} total`);
  await pool.end();
}

main().catch(e => { console.error('FATAL', e); process.exit(1); });