`scripts/ingest-pasadena-permits.ts`

164 lines
/**
 * ingest-pasadena-permits.ts
 *
 * Pasadena Active Building Permits via ArcGIS FeatureServer.
 * Source: data.cityofpasadena.net item 24525f546e5e4346957bb55695afc448
 * Verified 2026-04-30: 607 rows, daily refresh.
 *
 * Tier: A (Pasadena city government primary record).
 */
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 FEATURE_URL = 'https://services2.arcgis.com/zNjnZafDYCAJAbN0/ArcGIS/rest/services/Permit_Activity/FeatureServer/0/query';
const PAGE = 2000;

type Feature = {
  attributes: Record<string, any>;
  geometry?: { x: number; y: number };
};

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): Promise<Feature[]> {
  const params = new URLSearchParams({
    where: '1=1',
    outFields: '*',
    returnGeometry: 'true',
    outSR: '4326',
    resultOffset: String(offset),
    resultRecordCount: String(PAGE),
    f: 'json',
  });
  const res = await fetch(`${FEATURE_URL}?${params}`);
  const json = await res.json() as { features?: Feature[] };
  return json.features ?? [];
}

async function ensureSchema() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS permit (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      source TEXT NOT NULL DEFAULT 'ladbs',
      source_dataset TEXT NOT NULL,
      permit_number TEXT NOT NULL,
      listing_id UUID REFERENCES listing(id),
      primary_address TEXT,
      zip_code TEXT,
      council_district TEXT,
      apn TEXT,
      zone TEXT,
      permit_group TEXT,
      permit_type TEXT,
      permit_sub_type TEXT,
      use_code TEXT,
      use_desc TEXT,
      issue_date DATE,
      status_date DATE,
      submitted_date DATE,
      status_desc TEXT,
      valuation NUMERIC(15,2),
      square_footage INT,
      work_desc TEXT,
      neighborhood_council TEXT,
      community_plan_area TEXT,
      latitude NUMERIC(9,6),
      longitude NUMERIC(9,6),
      source_url TEXT,
      source_tier CHAR(1) NOT NULL DEFAULT 'A',
      source_label TEXT NOT NULL DEFAULT 'LA City Department of Building & Safety',
      retrieved_at TIMESTAMPTZ DEFAULT now(),
      UNIQUE (source_dataset, permit_number)
    );
  `);
}

async function upsertOne(f: Feature): Promise<void> {
  // Pasadena fields (verified 2026-04-30): ADDRESS, CASE_NUMBER, DESCRIPTION,
  // LAND_PARCEL_NO, PARCEL_NO, LATEST_ACTIVITY (epoch ms), TOTAL_SQFT
  const a = f.attributes ?? {};
  const addr = (a.ADDRESS ?? '').toString().trim();
  if (!addr) return;
  const permitNbr = (a.CASE_NUMBER ?? '').toString().trim();
  if (!permitNbr) return;
  const canonical = canonicalize(addr);
  if (!canonical) return;

  const lat = f.geometry?.y ?? null;
  const lon = f.geometry?.x ?? 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,'pasadena_permit',$2,$3,$4,'Pasadena','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}-pas`, `pasadena:${canonical}`, addr, addr, lat, lon]
  )).rows[0]!.id;

  let issueDate: string | null = null;
  if (a.LATEST_ACTIVITY) {
    const ms = parseInt(String(a.LATEST_ACTIVITY), 10);
    if (isFinite(ms) && ms > 0) {
      const d = new Date(ms);
      if (d.getFullYear() >= 1900 && d.getFullYear() <= 2030) {
        issueDate = d.toISOString().slice(0, 10);
      }
    }
  }
  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,
       square_footage, latitude, longitude, source_url, source_label)
     VALUES ('pasadena','pasadena-permit-activity',$1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,'City of Pasadena')
     ON CONFLICT (source_dataset, permit_number) DO UPDATE SET
       status_desc = COALESCE(EXCLUDED.status_desc, permit.status_desc),
       retrieved_at = now()`,
    [
      permitNbr, lid, addr, (a.LAND_PARCEL_NO ?? a.PARCEL_NO ?? null),
      null, null, null,
      issueDate, null, a.DESCRIPTION ?? null,
      parseInt(String(a.TOTAL_SQFT ?? '0'), 10) || null,
      lat, lon,
      `https://data.cityofpasadena.net/datasets/24525f546e5e4346957bb55695afc448_0/explore?showTable=true`,
    ]
  );
}

async function main() {
  await ensureSchema();
  let offset = 0;
  let total = 0;
  while (true) {
    const features = await fetchPage(offset);
    if (!features.length) break;
    for (const f of features) await upsertOne(f);
    total += features.length;
    console.log(`  pasadena: ${total} processed`);
    if (features.length < PAGE) break;
    offset += PAGE;
  }
  console.log(`✓ Pasadena permits: ${total} total`);
  await pool.end();
}

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