`scripts/rent-roll-ingest.js`

120 lines
// rent-roll-ingest.js — turn a rent-roll PDF into structured underwriting numbers. LOCAL, $0.
//
// WHAT THIS IS: a loan originator gets rent rolls (the unit-by-unit lease schedule) as PDFs on the deals
// they work — from the OM, the listing broker's deal room, or the borrower. This tool parses ONE such PDF
// into a clean table (unit / tenant / rent / lease term / status) and computes Gross Scheduled Rent, vacancy,
// average rent/unit, and an NOI proxy — the inputs for DSCR / debt-sizing.
//
// WHAT THIS IS NOT: a scraper. Rent rolls are confidential, almost always behind a signed CONFIDENTIALITY
// AGREEMENT (CA) on the broker's deal room. There is no public corpus to "pull all rent rolls" from, and
// circumventing a CA to harvest them is off the table. So this operates ONLY on PDFs Steve legitimately has
// and drops into the folder. It reads files; it does not fetch them.
//
// USAGE:
//   1. Drop rent-roll PDFs into   data/rent-rolls/*.pdf
//   2. node scripts/rent-roll-ingest.js                 # process all new PDFs
//      node scripts/rent-roll-ingest.js path/to/one.pdf # process one
//   3. Each PDF gets a sidecar   data/rent-rolls/<name>.json   with units[] + underwriting{}.
//
// DEP: pdftotext (poppler) — already installed. Zero npm deps.
'use strict';
const fs = require('fs');
const path = require('path');
const { execFileSync } = require('child_process');

const ROOT = path.join(__dirname, '..');
const DIR = path.join(ROOT, 'data', 'rent-rolls');

const money = s => {
  if (s == null) return null;
  const n = parseFloat(String(s).replace(/[^0-9.]/g, ''));
  return isFinite(n) && n > 0 ? n : null;
};

// Pull the text out of a PDF with layout preserved (keeps columns roughly aligned).
function pdfText(file) {
  try { return execFileSync('pdftotext', ['-layout', file, '-'], { encoding: 'utf8', maxBuffer: 32 * 1024 * 1024 }); }
  catch (e) { return null; }
}

// Heuristic row parser. Rent rolls vary wildly, so we look for lines that carry a plausible rent figure
// and pull the unit id (first token) + the largest dollar figure on the line as the rent. Conservative:
// a line must have a $ amount in a sane monthly-rent band to count as a unit.
function parseRows(text) {
  const units = [];
  const lines = text.split('\n');
  const RENT_LO = 200, RENT_HI = 60000;          // sane monthly residential/small-commercial rent band
  for (const raw of lines) {
    const line = raw.replace(/\s+$/, '');
    if (!line.trim()) continue;
    if (/\b(total|subtotal|grand total|gross|scheduled|summary|average|vacancy)\b/i.test(line)) continue; // skip aggregate lines
    // Only count MONEY-FORMATTED figures ($-prefixed, thousands-comma, or .dd) as rent — a bare integer like a
    // street number ("1234 Demo Ave") or zip ("90042") in a title line is NOT a rent. Prevents phantom units.
    const dollars = (line.match(/\$\s*[\d,]+(?:\.\d{2})?|\b\d{1,3},\d{3}(?:\.\d{2})?\b|\b\d+\.\d{2}\b/g) || [])
      .map(money).filter(n => n && n >= RENT_LO && n <= RENT_HI);
    if (!dollars.length) continue;
    // unit id: a label word + identifier (Unit 101 / Apt A-12 / Suite 200 / #4) if present, else the leading token.
    const labeled = line.trim().match(/^(?:unit|apt|apartment|suite|ste|room|rm|#)\s*#?\s*([A-Za-z0-9\-]+)/i);
    const firstTok = labeled ? labeled[1] : ((line.trim().match(/^[A-Za-z0-9#\-]+/) || [])[0] || '');
    if (!firstTok) continue;
    const rent = Math.max(...dollars);            // largest in-band figure on the line ≈ the rent
    const vacant = /\bvacant\b|\bvac\b/i.test(line);
    const beds = (line.match(/\b(\d)\s*(?:bd|br|bed)\b/i) || [])[1];
    units.push({ unit: firstTok, rent: vacant ? 0 : rent, marketRent: rent, status: vacant ? 'vacant' : 'occupied', beds: beds ? +beds : null });
  }
  return units;
}

function underwrite(units) {
  const occ = units.filter(u => u.status === 'occupied');
  const vac = units.filter(u => u.status === 'vacant');
  const gsrMonthly = units.reduce((s, u) => s + (u.marketRent || 0), 0);   // gross SCHEDULED (market) rent
  const actualMonthly = occ.reduce((s, u) => s + (u.rent || 0), 0);         // in-place collected rent
  const annualGSR = gsrMonthly * 12, annualActual = actualMonthly * 12;
  const vacancyRate = units.length ? +(vac.length / units.length * 100).toFixed(1) : null;
  // NOI proxy: small-multifamily operating expenses run ~35-45% of EGI; use 40% as a transparent placeholder.
  const EXP_RATIO = 0.40;
  const noiProxy = +(annualActual * (1 - EXP_RATIO)).toFixed(0);
  return {
    unitCount: units.length, occupied: occ.length, vacant: vac.length, vacancyRate,
    grossScheduledRentMonthly: +gsrMonthly.toFixed(0), grossScheduledRentAnnual: +annualGSR.toFixed(0),
    inPlaceRentMonthly: +actualMonthly.toFixed(0), inPlaceRentAnnual: +annualActual.toFixed(0),
    avgRentPerUnit: occ.length ? +(actualMonthly / occ.length).toFixed(0) : null,
    noiProxyAnnual: noiProxy, noiBasis: `in-place rent × (1 − ${EXP_RATIO} expense ratio); replace with the OM's actual T-12 when available`,
    confidence: 'parsed from rent-roll PDF — verify unit count + totals against the document before underwriting',
  };
}

function processOne(file) {
  const text = pdfText(file);
  if (!text) return { file: path.basename(file), error: 'pdftotext failed (not a readable PDF?)' };
  const units = parseRows(text);
  const uw = underwrite(units);
  const out = {
    source: path.basename(file), parsedAt: new Date().toISOString(),
    units, underwriting: uw,
    note: 'Confidential rent-roll document provided by Steve. Parsed locally ($0). Not scraped, not redistributed.',
  };
  const sidecar = file.replace(/\.pdf$/i, '.json');
  fs.writeFileSync(sidecar, JSON.stringify(out, null, 2));
  return { file: path.basename(file), units: units.length, ...uw, sidecar: path.basename(sidecar) };
}

function main() {
  const arg = process.argv[2];
  if (arg) { console.log(JSON.stringify(processOne(path.resolve(arg)), null, 2)); return; }
  if (!fs.existsSync(DIR)) { fs.mkdirSync(DIR, { recursive: true }); }
  const pdfs = fs.readdirSync(DIR).filter(f => /\.pdf$/i.test(f));
  if (!pdfs.length) {
    console.log(`No rent-roll PDFs found. Drop them into ${DIR}/ and re-run.`);
    console.log('This tool parses rent rolls you legitimately have (deal OMs, broker deal rooms, borrower docs).');
    console.log('It does NOT scrape — rent rolls are CA-gated confidential documents with no public source.');
    return;
  }
  const results = pdfs.map(f => processOne(path.join(DIR, f)));
  console.log(JSON.stringify({ processed: results.length, results }, null, 2));
}

if (require.main === module) main();
module.exports = { processOne, parseRows, underwrite };