`server.js`

133 lines
#!/usr/bin/env node
'use strict';
/*
 * socrata-cre-viewer
 * Searchable / sortable grid over realestate.socrata_cre_prospects — LA Socrata
 * CRE advertiser-prospect firms (Active Businesses + LADBS permits), net-new vs
 * the curated LABJ set. 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);
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 });
pool.on('error', (err) => console.error('pg pool error', err));
const TABLE = 'socrata_cre_prospects';

const SORTABLE = new Set(['firm_name', 'role', 'contact_name', 'city', 'naics_desc',
  'permit_sub_type', 'valuation', 'issue_date', 'source', 'first_seen', 'zip']);
const FILTERS = ['role', 'source', 'city', 'naics_desc', 'council_district'];
const SEARCH_COLS = ['firm_name', 'contact_name', 'address', 'naics_desc', 'work_description'];

function safeEq(a, b) {
  const ab = Buffer.from(a), bb = Buffer.from(b);
  return ab.length === bb.length && 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 safeEq(u || '', USER) && safeEq(p || '', PASS);
}

function buildWhere(qs) {
  const clauses = [], 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 i = params.length;
    clauses.push('(' + SEARCH_COLS.map((c) => `${c} ILIKE $${i}`).join(' OR ') + ')');
  }
  return { where: clauses.length ? 'WHERE ' + clauses.join(' AND ') : '', params };
}

async function apiRows(qs) {
  const { where, params } = buildWhere(qs);
  let sort = qs.get('sort') || 'firm_name';
  if (!SORTABLE.has(sort)) sort = 'firm_name';
  const dir = (qs.get('dir') || 'asc').toLowerCase() === 'desc' ? 'DESC' : 'ASC';
  // valuation is stored as text; sort it numerically when chosen.
  const sortExpr = sort === 'valuation' ? "NULLIF(valuation,'')::bigint" : sort;
  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 total = Number((await pool.query(`SELECT count(*)::bigint n FROM ${TABLE} ${where}`, params)).rows[0].n);
  const rowsSql =
    `SELECT firm_name, role, contact_name, address, city, state, zip, naics, naics_desc,
            permit_type, permit_sub_type, valuation, issue_date, source, work_description,
            council_district, first_seen
       FROM ${TABLE} ${where}
      ORDER BY ${sortExpr} ${dir} NULLS LAST, firm_name ASC
      LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
  const rows = (await pool.query(rowsSql, [...params, limit, offset])).rows;
  return { total, page, limit, sort, dir, rows };
}

let fc = null, fa = 0;
async function apiFacets() {
  if (fc && Date.now() - fa < 3 * 60 * 1000) return fc;
  const facet = async (col, lim) => (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 LIMIT ${lim}`)).rows;
  const [role, source, city, naics_desc] = await Promise.all([
    facet('role', 20), facet('source', 10), facet('city', 40), facet('naics_desc', 40)]);
  const total = Number((await pool.query(`SELECT count(*)::bigint n FROM ${TABLE}`)).rows[0].n);
  fc = { total, role, source, city, naics_desc }; fa = Date.now();
  return fc;
}

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));
}

// Static assets from public/ (e.g. the nav-agent drop-in). Additive: only serves
// files that resolve INSIDE public/ — path-traversal attempts fall through to 404.
const PUBLIC_DIR = path.join(__dirname, 'public');
const MIME = {
  '.js': 'application/javascript; charset=utf-8', '.css': 'text/css; charset=utf-8',
  '.html': 'text/html; charset=utf-8', '.json': 'application/json; charset=utf-8',
  '.svg': 'image/svg+xml', '.png': 'image/png', '.jpg': 'image/jpeg', '.jpeg': 'image/jpeg',
  '.gif': 'image/gif', '.webp': 'image/webp', '.ico': 'image/x-icon', '.woff2': 'font/woff2',
  '.woff': 'font/woff', '.ttf': 'font/ttf', '.map': 'application/json; charset=utf-8',
};
function serveStatic(res, pathname) {
  const rel = decodeURIComponent(pathname).replace(/^\/+/, '');
  const abs = path.resolve(PUBLIC_DIR, rel);
  if (abs !== PUBLIC_DIR && !abs.startsWith(PUBLIC_DIR + path.sep)) return false; // traversal guard
  if (!fs.existsSync(abs) || !fs.statSync(abs).isFile()) return false;
  const type = MIME[path.extname(abs).toLowerCase()] || 'application/octet-stream';
  res.writeHead(200, { 'Content-Type': type });
  res.end(fs.readFileSync(abs));
  return true;
}

const server = http.createServer(async (req, res) => {
  if (!authed(req)) { res.writeHead(401, { 'WWW-Authenticate': 'Basic realm="socrata-cre"' }); 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/rows') return send(res, 200, await apiRows(url.searchParams));
    if (url.pathname === '/healthz') return send(res, 200, { ok: true });
    if (serveStatic(res, url.pathname)) return;
    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, () => console.log(`socrata-cre-viewer live: http://${HOST}:${server.address().port}  (login ${USER})`));