`scripts/export-sfr-snapshot.js`

43 lines
// export-sfr-snapshot.js — dump the scraped single-family for-sale listings (public.sfr in the
// local `cre` DB) to a static JSON snapshot: data/sfr-redfin.json.
//
// WHY: prod (Kamatera) has no Postgres, so the live crcp.agentabrams.com serves data-backed pages
// from static snapshots with a DB->snapshot fallback (same pattern as data/condos-redfin.json /
// data/closed-sales.json). fetch-sfr-redfin.js only writes to the DB, so this is the companion
// exporter that produces the file /api/residential falls back to on prod.
//
// Run AFTER fetch-sfr-redfin.js:  node scripts/export-sfr-snapshot.js
'use strict';
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');

const ROOT = path.join(__dirname, '..');
const OUT = path.join(ROOT, 'data', 'sfr-redfin.json');
const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });

(async () => {
  // Tight, card-safe column projection. listing_text is the constant label "Single Family
  // Residential" for every gis-csv row, so it's dropped (the client tags type itself).
  const { rows } = await pool.query(`
    SELECT id, address, city, zip, price, beds, baths, sqft, year_built,
           firm_name, broker_name, source, lat, lng, created_at
      FROM sfr
     WHERE price > 0
     ORDER BY created_at DESC, price DESC`);

  const payload = {
    meta: {
      source: 'Redfin LA County single-family for-sale — feed-first (gis-csv), via Browserbase',
      note: 'Listing agent/firm only present where Redfin exposed it (fetch-sfr-agents.js); NULL otherwise (honest labeling).',
      fetched_at: new Date().toISOString(),
      count: rows.length,
    },
    sfr: rows,
  };
  fs.writeFileSync(OUT, JSON.stringify(payload, null, 2));
  const kb = (fs.statSync(OUT).size / 1024).toFixed(0);
  console.log(JSON.stringify({ count: rows.length, out: OUT, size_kb: +kb }));
  await pool.end();
})().catch(e => { console.error(e.message); process.exit(1); });