`scripts/export-assessor-sqlite.js`

117 lines
// export-assessor-sqlite.js — dump the LA County assessor parcel roll (public.assessor_parcel in the
// local `cre` Postgres, ~2.43M rows) to a read-only SQLite file: data/assessor.sqlite.
//
// WHY: prod (Kamatera) has no Postgres server, so the live crcp.agentabrams.com serves data-backed
// pages from static artifacts with a DB->fallback pattern (data/condos-redfin.json, data/sfr-redfin.json).
// Those are JSON because condos/sfr are only a few thousand rows. The assessor roll is 2.43M rows —
// a JSON snapshot would be ~1GB and can't be loaded into node memory per request. So the "static
// artifact" for this dataset is a SQLite file: it ships in the deploy exactly like the JSON snapshots,
// but better-sqlite3 queries it per-address (situs_house_no + situs_street) with zero DB server.
// (DTD verdict A, 2026-07-07 — 3/3: SQLite is the only option that delivers the data AND keeps the
//  "ship a file, no server" contract.)
//
// Run AFTER the assessor ingest (ingest-assessor.js / fetch-assessor*.js):
//   node scripts/export-assessor-sqlite.js
//
// The runtime fallback in scripts/serve.js (/api/address-history) reads the file this produces.
'use strict';
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
const Database = require('better-sqlite3');

const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'data', 'assessor.sqlite');
const TMP = OUT + '.tmp';                                  // build to .tmp, atomic-rename at the end
const BATCH = 50000;                                      // keyset page size — keeps node memory flat

const pool = new Pool({
  host: process.env.CRE_PGHOST || '/tmp', port: +(process.env.CRE_PGPORT || 5432),
  database: process.env.CRE_PGDATABASE || 'cre', user: process.env.CRE_PGUSER || process.env.USER || 'stevestudio2',
});

// The exact projection the /api/address-history endpoint reads back, PLUS the two lookup keys
// (situs_house_no, situs_street) it filters on. Keeping the SQLite column set aligned with the
// endpoint's SELECT is what makes the fallback a drop-in for the Postgres row.
const SELECT = `
  SELECT ain, situs_house_no, situs_street, property_location,
         year_built, effective_year_built, sqft_main, bedrooms, bathrooms, units,
         use_desc1, use_desc2, roll_total_value, roll_land_value, roll_imp_value,
         roll_year, recording_date
    FROM assessor_parcel
   WHERE ain > $1
   ORDER BY ain
   LIMIT ${BATCH}`;

(async () => {
  fs.rmSync(TMP, { force: true });
  const db = new Database(TMP);
  // Bulk-load PRAGMAs: no journal / no fsync while building a throwaway file. Reset to safe WAL after.
  db.pragma('journal_mode = OFF');
  db.pragma('synchronous = OFF');
  db.exec(`
    CREATE TABLE assessor_parcel (
      ain                  TEXT PRIMARY KEY,
      situs_house_no       INTEGER,
      situs_street         TEXT,
      property_location    TEXT,
      year_built           INTEGER,
      effective_year_built INTEGER,
      sqft_main            INTEGER,
      bedrooms             INTEGER,
      bathrooms            INTEGER,
      units                INTEGER,
      use_desc1            TEXT,
      use_desc2            TEXT,
      roll_total_value     INTEGER,
      roll_land_value      INTEGER,
      roll_imp_value       INTEGER,
      roll_year            TEXT,
      recording_date       TEXT
    );`);

  const cols = ['ain','situs_house_no','situs_street','property_location','year_built',
    'effective_year_built','sqft_main','bedrooms','bathrooms','units','use_desc1','use_desc2',
    'roll_total_value','roll_land_value','roll_imp_value','roll_year','recording_date'];
  const insert = db.prepare(
    `INSERT OR IGNORE INTO assessor_parcel (${cols.join(',')}) VALUES (${cols.map(() => '?').join(',')})`);
  const insertMany = db.transaction((rows) => {
    for (const r of rows) {
      insert.run(cols.map(c => {
        const v = r[c];
        if (v == null) return null;
        if (c === 'recording_date') return v instanceof Date ? v.toISOString().slice(0, 10) : String(v);
        return v;                                          // pg already returns numbers as JS numbers
      }));
    }
  });

  let cursor = '';                                         // ain is TEXT; '' sorts before any real AIN
  let total = 0;
  for (;;) {
    const { rows } = await pool.query(SELECT, [cursor]);
    if (!rows.length) break;
    insertMany(rows);
    total += rows.length;
    cursor = rows[rows.length - 1].ain;
    if (total % (BATCH * 5) === 0) process.stderr.write(`  ...${total.toLocaleString()} rows\n`);
    if (rows.length < BATCH) break;                        // last (short) page
  }

  // Address-lookup index: mirrors the endpoint filter (situs_house_no = ? AND situs_street LIKE ?).
  process.stderr.write('  building index...\n');
  db.exec('CREATE INDEX idx_assessor_addr ON assessor_parcel (situs_house_no, situs_street)');
  // DELETE (rollback-journal) mode, NOT WAL: the shipped file is read-only on prod, and WAL would
  // require writable -wal/-shm sidecars alongside it. DELETE mode leaves ONE self-contained file that
  // opens cleanly read-only anywhere. VACUUM after to compact.
  db.pragma('journal_mode = DELETE');
  db.exec('VACUUM');
  db.close();

  fs.rmSync(OUT, { force: true });
  fs.renameSync(TMP, OUT);
  const mb = (fs.statSync(OUT).size / 1048576).toFixed(1);
  console.log(JSON.stringify({ rows: total, out: OUT, size_mb: +mb }));
  await pool.end();
})().catch(e => { console.error(e.message); try { fs.rmSync(TMP, { force: true }); } catch (_) {} process.exit(1); });