`server.js`

145 lines
#!/usr/bin/env node
'use strict';
/*
 * rentv-licensed-targets viewer
 * Searchable / sortable grid over the 102k rentv_licensed_targets table
 * (RENTV commercial advertiser-prospect universe: CA CSLB contractors,
 *  AZ ROC, DFPI lenders/escrow, DRE PM/subdivision, state bar, DOI title).
 *
 * Zero framework: Node built-in http + pg (parameterized queries only).
 * Basic Auth admin / DW2024!  (override via VIEWER_USER / VIEWER_PASS).
 */
const http = require('http');
const fs = require('fs');
const path = require('path');
const crypto = require('crypto');
const { Pool } = require('pg');

const PORT = Number(process.env.PORT || 0);           // 0 = OS picks a free port
const HOST = process.env.HOST || '127.0.0.1';
const USER = process.env.VIEWER_USER || 'admin';
const PASS = process.env.VIEWER_PASS || 'DW2024!';

const pool = new Pool({
  host: process.env.PGHOST || '/tmp',
  database: process.env.PGDATABASE || 'realestate',
  max: 6,
});
// pg emits 'error' on idle-client failure; without a listener Node crashes the process.
pool.on('error', (err) => console.error('pg pool error', err));
const TABLE = 'rentv_licensed_targets';

// Columns the client is allowed to sort by (whitelist → no injection surface).
const SORTABLE = new Set([
  'entity_name', 'contact_name', 'role', 'license_no', 'license_type',
  'license_status', 'city', 'county', 'market', 'phone', 'website',
  'source', 'scraped_at', 'id',
]);
// Exact-match filter columns (value bound as a parameter).
const FILTERS = ['role', 'market', 'license_status', 'source', 'county'];
// Free-text search columns (ILIKE against one bound %q% param).
const SEARCH_COLS = ['entity_name', 'contact_name', 'license_no', 'city', 'address', 'phone'];

function timingSafeEqual(a, b) {
  const ab = Buffer.from(a), bb = Buffer.from(b);
  if (ab.length !== bb.length) return false;
  return crypto.timingSafeEqual(ab, bb);
}
function authed(req) {
  const h = req.headers.authorization || '';
  if (!h.startsWith('Basic ')) return false;
  const [u, p] = Buffer.from(h.slice(6), 'base64').toString('utf8').split(':');
  return timingSafeEqual(u || '', USER) && timingSafeEqual(p || '', PASS);
}

// Build the shared WHERE clause + bound params from the query string.
function buildWhere(qs) {
  const clauses = [];
  const params = [];
  for (const col of FILTERS) {
    const v = qs.get(col);
    if (v) { params.push(v); clauses.push(`${col} = $${params.length}`); }
  }
  const q = (qs.get('q') || '').trim();
  if (q) {
    params.push(`%${q}%`);
    const idx = params.length;
    clauses.push('(' + SEARCH_COLS.map((c) => `${c} ILIKE $${idx}`).join(' OR ') + ')');
  }
  return { where: clauses.length ? 'WHERE ' + clauses.join(' AND ') : '', params };
}

async function apiTargets(qs) {
  const { where, params } = buildWhere(qs);

  let sort = qs.get('sort') || 'entity_name';
  if (!SORTABLE.has(sort)) sort = 'entity_name';
  const dir = (qs.get('dir') || 'asc').toLowerCase() === 'desc' ? 'DESC' : 'ASC';

  const limit = Math.min(Math.max(parseInt(qs.get('limit'), 10) || 50, 1), 200);
  const page = Math.max(parseInt(qs.get('page'), 10) || 1, 1);
  const offset = (page - 1) * limit;

  const countSql = `SELECT count(*)::bigint AS n FROM ${TABLE} ${where}`;
  const countRes = await pool.query(countSql, params);
  const total = Number(countRes.rows[0].n);

  // NULLS LAST keeps blank contact/website at the bottom regardless of dir.
  const rowsSql =
    `SELECT id, source, role, entity_name, contact_name, license_no, license_type,
            license_status, address, city, county, state, zip, phone, website,
            market, scraped_at
       FROM ${TABLE} ${where}
      ORDER BY ${sort} ${dir} NULLS LAST, id ASC
      LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
  const rowsRes = await pool.query(rowsSql, [...params, limit, offset]);

  return { total, page, limit, sort, dir, rows: rowsRes.rows };
}

let facetCache = null, facetAt = 0;
async function apiFacets() {
  if (facetCache && Date.now() - facetAt < 5 * 60 * 1000) return facetCache;
  const facet = async (col) => (await pool.query(
    `SELECT ${col} AS v, count(*)::int AS n FROM ${TABLE}
      WHERE ${col} IS NOT NULL AND ${col} <> '' GROUP BY ${col} ORDER BY n DESC`
  )).rows;
  const [role, market, license_status, source, county] = await Promise.all(
    ['role', 'market', 'license_status', 'source', 'county'].map(facet)
  );
  const total = Number((await pool.query(`SELECT count(*)::bigint n FROM ${TABLE}`)).rows[0].n);
  facetCache = { total, role, market, license_status, source, county };
  facetAt = Date.now();
  return facetCache;
}

function send(res, code, body, type = 'application/json') {
  res.writeHead(code, { 'Content-Type': type, 'Cache-Control': 'no-store' });
  res.end(typeof body === 'string' || Buffer.isBuffer(body) ? body : JSON.stringify(body));
}

const server = http.createServer(async (req, res) => {
  if (!authed(req)) {
    res.writeHead(401, { 'WWW-Authenticate': 'Basic realm="rentv-targets"' });
    return res.end('Auth required');
  }
  const url = new URL(req.url, 'http://x');
  try {
    if (url.pathname === '/' || url.pathname === '/index.html') {
      return send(res, 200, fs.readFileSync(path.join(__dirname, 'public', 'index.html')), 'text/html; charset=utf-8');
    }
    if (url.pathname === '/api/facets') return send(res, 200, await apiFacets());
    if (url.pathname === '/api/targets') return send(res, 200, await apiTargets(url.searchParams));
    if (url.pathname === '/healthz') return send(res, 200, { ok: true });
    return send(res, 404, { error: 'not found' });
  } catch (e) {
    console.error(e);
    return send(res, 500, { error: String(e.message || e) });
  }
});

server.listen(PORT, HOST, () => {
  const p = server.address().port;
  console.log(`rentv-licensed-targets viewer live: http://${HOST}:${p}  (login ${USER})`);
});