← back to Nationalrealestate
src/server/property.ts
558 lines
/**
* M-P1 property-level detail layer. Parcel data is read straight from each county's
* registered source (parcel_source table) — LA County (06037) is a read-only SQLite
* file built by CRCP (2.43M rows, opened lazily, CRCP scripts/serve.js pattern) —
* never copied into PG. County-level context (NRI climate, HUD FMR, ACS, Zillow)
* joins in from metric_series.
*
* The LA roll carries NO owner names, mailing addresses, zoning codes, or sale
* prices — those sections return nulls ("coverage pending"), never fabricated.
*/
import type { Express, Request, Response } from 'express';
import { existsSync } from 'node:fs';
import { join } from 'node:path';
import { homedir } from 'node:os';
import { createRequire } from 'node:module';
import { query } from '../../db/pool.ts';
const require = createRequire(import.meta.url); // better-sqlite3 is native CJS, loaded lazily
const LA_FIPS = '06037';
export const LA_SQLITE_PATH =
process.env.ASSESSOR_SQLITE || join(homedir(), 'Projects', 'commercialrealestate', 'data', 'assessor.sqlite');
interface SqliteStmt { all(...params: unknown[]): unknown[]; get(...params: unknown[]): unknown; }
interface SqliteDb { prepare(sql: string): SqliteStmt; }
const dbCache = new Map<string, SqliteDb | null>();
function openParcelDb(path: string): SqliteDb | null {
if (dbCache.has(path)) return dbCache.get(path)!;
let db: SqliteDb | null = null;
try {
if (existsSync(path)) {
const Database = require('better-sqlite3');
db = new Database(path, { readonly: true, fileMustExist: true });
}
} catch (e) {
console.error('[property] sqlite open failed:', e);
db = null;
}
dbCache.set(path, db);
return db;
}
interface ParcelRow {
ain: string;
situs_house_no: number | null;
situs_street: string | null;
property_location: string | null;
year_built: number | null;
effective_year_built: number | null;
sqft_main: number | null;
bedrooms: number | null;
bathrooms: number | null;
units: number | null;
use_desc1: string | null;
use_desc2: string | null;
roll_total_value: number | null;
roll_land_value: number | null;
roll_imp_value: number | null;
roll_year: string | null;
recording_date: string | null;
}
async function parcelSourceFor(countyFips: string): Promise<{ source_kind: string; path_or_url: string } | null> {
const r = await query<{ source_kind: string; path_or_url: string }>(
`SELECT source_kind, path_or_url FROM parcel_source WHERE county_fips = $1`, [countyFips],
);
return r.rows[0] ?? null;
}
async function getCountyDb(countyFips: string): Promise<SqliteDb | null> {
const src = await parcelSourceFor(countyFips);
if (!src || src.source_kind !== 'sqlite') return null;
return openParcelDb(src.path_or_url);
}
function parseAddressQuery(q: string): { houseNo: number | null; street: string } {
const m = q.trim().toUpperCase().match(/^(\d+)\s+(.+)$/);
if (m) return { houseNo: Number(m[1]), street: m[2].trim() };
return { houseNo: null, street: q.trim().toUpperCase() };
}
const likeEscape = (s: string) => s.replace(/[%_]/g, '\\$&');
const PENDING = 'coverage pending for this county';
// ── shared county-level context (source-agnostic: keyed on county FIPS) ──
async function countyContext(county: string) {
const rg = await query<any>(
`SELECT id, canonical_key, name, state_code, fips, cbsa_code, lat, lng, population
FROM region WHERE region_type = 'county' AND fips = $1`, [county],
);
const region = rg.rows[0] ?? null;
const latest: Record<string, number> = {};
let metroName: string | null = null;
let nri: { risk_rating: string | null; top_hazards: any } | null = null;
if (region) {
const mr = await query<{ metric: string; value: string }>(
`SELECT DISTINCT ON (metric) metric, value::text AS value
FROM metric_series WHERE region_id = $1 ORDER BY metric, period DESC`, [region.id],
);
for (const row of mr.rows) latest[row.metric] = Number(row.value);
if (region.cbsa_code) {
const met = await query<{ name: string }>(
`SELECT name FROM region WHERE region_type = 'metro' AND cbsa_code = $1`, [region.cbsa_code],
);
metroName = met.rows[0]?.name ?? null;
}
const nr = await query<{ risk_rating: string | null; top_hazards: any }>(
`SELECT risk_rating, top_hazards FROM nri_ratings WHERE county_fips = $1`, [county],
);
nri = nr.rows[0] ?? null;
}
return { region, latest, metroName, nri };
}
function buildContextSections(county: string, ctx: Awaited<ReturnType<typeof countyContext>>) {
const { region, latest, metroName, nri } = ctx;
const fmr = ['fmr_0br', 'fmr_1br', 'fmr_2br', 'fmr_3br', 'fmr_4br'].some(k => latest[k] != null)
? { fmr_0br: latest.fmr_0br ?? null, fmr_1br: latest.fmr_1br ?? null, fmr_2br: latest.fmr_2br ?? null,
fmr_3br: latest.fmr_3br ?? null, fmr_4br: latest.fmr_4br ?? null } : null;
return {
climate: region ? {
risk_score: latest.nri_risk_score ?? null, eal_score: latest.nri_eal_score ?? null,
social_vulnerability: latest.nri_sovi_score ?? null, community_resilience: latest.nri_resl_score ?? null,
risk_rating: nri?.risk_rating ?? null, top_hazards: nri?.top_hazards ?? [],
source: 'FEMA National Risk Index v1.20.0 (Dec 2025), county level',
} : null,
rental: region ? {
fmr, fmr_year: fmr ? 'FY2026' : null, zori: latest.zori ?? null, rent_yield: latest.rent_yield ?? null,
median_gross_rent: latest.median_gross_rent ?? null,
source: 'HUD FMR FY2026 + Zillow ZORI + derived, county level',
} : null,
demographics: region ? {
population: latest.population ?? region.population ?? null, median_hh_income: latest.median_hh_income ?? null,
median_gross_rent: latest.median_gross_rent ?? null, vacancy_rate: latest.vacancy_rate ?? null,
renter_share: latest.renter_share ?? null, source: 'Census ACS 2023 5-yr, county level',
} : null,
location: region ? {
county_name: region.name, state: region.state_code, fips: region.fips,
metro: metroName, cbsa_code: region.cbsa_code, lat: region.lat, lng: region.lng,
zhvi: latest.zhvi ?? null, zhvi_yoy: latest.zhvi_yoy ?? null,
links: { market: '/market.html?key=' + encodeURIComponent(region.canonical_key) },
} : null,
};
}
// ── PG-backed parcels (NYC PLUTO etc: owner + zoning + assessed) ──
interface PgParcel {
source_id: string; address: string; norm_address: string; city: string; zip: string;
lat: number | null; lng: number | null; year_built: number | null; sqft: number | null;
beds: number | null; baths: number | null;
units: number | null; use_desc: string | null; land_value: number | null;
improvement_value: number | null; total_value: number | null; tax_year: string | null;
owner_name: string | null; zoning: string | null;
last_sale_date: string | null; last_sale_price: number | null; extra: any;
}
/** "350 5th Ave." -> "350 5 AVE" — PLUTO/King store numeric streets without
* ordinals; abbreviations then match as LIKE prefixes (AVE% hits AVENUE). */
function normAddressQuery(q: string): string {
return q.trim().toUpperCase()
.replace(/[.,#]/g, ' ')
.replace(/\b(\d+)(?:ST|ND|RD|TH)\b/g, '$1')
.replace(/\s+/g, ' ')
.trim();
}
const SEARCH_COLS = `source_id, address, city, zip, year_built, sqft, beds, baths, units, use_desc, total_value, owner_name, zoning`;
const asMatch = (p: any) => ({
ain: p.source_id, address: p.address, year_built: p.year_built, sqft: p.sqft,
bedrooms: p.beds, bathrooms: p.baths, units: p.units, use: p.use_desc,
assessed_value: p.total_value, owner: p.owner_name, zoning: p.zoning,
});
interface PgCountyMeta {
assessor: { name: string; phone: string; website: string };
detailsNote: string; historyNote: string; taxNote: string; ownerNote: string; zoningNote: string;
}
const NYC_META: PgCountyMeta = {
assessor: { name: 'NYC Department of Finance', phone: '311',
website: 'https://www.nyc.gov/site/finance/property/property.page' },
detailsNote: 'NYC MapPLUTO is lot-level — building sqft shown; unit-level beds/baths not in PLUTO.',
historyNote: 'Recorded sales (price + date) from NYC ACRIS priced transfer documents — deed family + RPTT transfer-tax returns (2018–present) — where available; condo-unit deeds and buyer/seller names pending. PLUTO supplies current attributes.',
taxNote: 'NYC assessed values from MapPLUTO (current version).',
ownerNote: 'Owner of record from NYC MapPLUTO.',
zoningNote: 'Primary zoning district (zonedist1) from NYC MapPLUTO.',
};
const PG_COUNTY_META: Record<string, PgCountyMeta> = {
'36005': NYC_META, '36047': NYC_META, '36061': NYC_META, '36081': NYC_META, '36085': NYC_META,
'53033': {
assessor: { name: 'King County Department of Assessments', phone: '(206) 296-7300',
website: 'https://kingcounty.gov/en/dept/assessments' },
detailsNote: 'King County assessor EXTR extracts (residential building record).',
historyNote: 'Recorded deed history with prices from King County EXTR_RPSale (10 most recent priced sales).',
taxNote: 'Appraised land + improvement values from King County real property accounts (latest bill year).',
ownerNote: 'Owner from King County records.',
zoningNote: 'Current zoning from the King County parcel record.',
},
'17031': {
assessor: { name: 'Cook County Assessor', phone: '(312) 443-7550',
website: 'https://www.cookcountyassessor.com' },
detailsNote: 'Cook County Assessor open data (parcel universe + parcel addresses).',
historyNote: 'Deed/sale history pending (Cook County Recorder feed); assessed values shown under Tax History.',
taxNote: 'Cook County certified/mailed assessed values (latest tax year on the open-data portal).',
ownerNote: 'Owner/taxpayer of record from Cook County parcel addresses dataset.',
zoningNote: 'Zoning from the Cook County parcel record.',
},
'06073': {
assessor: { name: 'San Diego County Assessor/Recorder/County Clerk (ARCC)', phone: '(619) 236-3771',
website: 'https://www.sdarcc.gov' },
detailsNote: 'San Diego County parcels from SanGIS/SANDAG (situs, structure, assessed values).',
historyNote: 'Last recorded document (deed) date + reference from SanGIS. Sale PRICE and full deed chain are via ARCC Official Records / ParcelQuest — AB 1785 removed online APN recorder search (Dec 2024); see Public Records links.',
taxNote: 'Assessed land + improvement + total from the SanGIS assessor roll.',
ownerNote: 'Owner name is on the ARCC secured roll (not in the SanGIS public layer); see Public Records links.',
zoningNote: 'Assessor zoning (asr_zone) from the SanGIS parcel record.',
},
default: {
assessor: { name: 'County Assessor', phone: '', website: '' },
detailsNote: 'County parcel record.', historyNote: 'Sale history pending for this county.',
taxNote: 'Latest assessed values on file.', ownerNote: 'Owner of record from the county roll.',
zoningNote: 'Zoning from the county parcel record.',
},
};
async function pgSearchRows(county: string, q: string): Promise<any[]> {
const norm = normAddressQuery(q);
const exact = await query<PgParcel>(
`SELECT ${SEARCH_COLS} FROM parcel WHERE county_fips = $1 AND norm_address LIKE $2 ESCAPE '\\'
ORDER BY norm_address LIMIT 25`, [county, likeEscape(norm) + '%'],
);
if (exact.rows.length) return exact.rows;
// Fallback: house number not on file (e.g. Empire State is "338 5 AVENUE" for
// the 338-350 range) — same street, nearest house number first.
const m = norm.match(/^(\d+)\s+(.+)$/);
if (!m) return [];
const houseNo = Number(m[1]);
const near = await query<PgParcel>(
`SELECT ${SEARCH_COLS} FROM parcel
WHERE county_fips = $1 AND norm_address LIKE $2 ESCAPE '\\'
ORDER BY ABS(COALESCE((regexp_match(norm_address, '^(\\d+)'))[1]::bigint, 0) - $3) LIMIT 25`,
[county, '%' + likeEscape(m[2]) + '%', houseNo],
);
return near.rows;
}
async function pgSearch(county: string, q: string) {
return (await pgSearchRows(county, q)).map(asMatch);
}
async function pgProperty(county: string, loc: string) {
let r = await query<PgParcel>(`SELECT * FROM parcel WHERE county_fips=$1 AND source_id=$2`, [county, loc]);
if (!r.rows.length) {
r = await query<PgParcel>(
`SELECT * FROM parcel WHERE county_fips=$1 AND norm_address LIKE $2 ESCAPE '\\' LIMIT 1`,
[county, likeEscape(normAddressQuery(loc)) + '%'],
);
}
const p = r.rows[0];
if (!p) return null;
// comps: same county, same use, sqft ±30%
let comps: any[] = [];
if (p.sqft && p.use_desc) {
const lo = Math.round(+p.sqft * 0.7), hi = Math.round(+p.sqft * 1.3);
const c = await query<any>(
`SELECT source_id, address, year_built, sqft, units, total_value, owner_name
FROM parcel WHERE county_fips=$1 AND use_desc=$2 AND source_id != $3
AND sqft BETWEEN $4 AND $5 ORDER BY total_value DESC NULLS LAST LIMIT 8`,
[county, p.use_desc, p.source_id, lo, hi],
);
comps = c.rows.map((x: any) => ({ ain: x.source_id, address: x.address, year_built: x.year_built,
sqft: x.sqft, units: x.units, assessed_value: x.total_value, owner: x.owner_name }));
}
return { p, comps };
}
export function mountProperty(app: Express) {
// address search within a parcel-covered county (LA-only for now)
app.get('/api/property/search', async (req: Request, res: Response) => {
try {
const county = String(req.query.county || LA_FIPS);
const q = String(req.query.q || '').trim();
if (!q) return res.status(400).json({ error: 'q required' });
const src = await parcelSourceFor(county);
if (!src) return res.status(404).json({ error: `no parcel source loaded for county ${county}` });
if (src.source_kind === 'pg') {
const matches = await pgSearch(county, q);
return res.json({ county, q, count: matches.length, matches });
}
const db = await getCountyDb(county);
if (!db) return res.status(404).json({ error: `no parcel source loaded for county ${county}` });
const { houseNo, street } = parseAddressQuery(q);
let rows: ParcelRow[];
if (houseNo != null) {
rows = db.prepare(
`SELECT * FROM assessor_parcel
WHERE situs_house_no = ? AND situs_street LIKE ? ESCAPE '\\'
ORDER BY situs_street LIMIT 25`,
).all(houseNo, likeEscape(street) + '%') as ParcelRow[];
} else {
rows = db.prepare(
`SELECT * FROM assessor_parcel
WHERE situs_street LIKE ? ESCAPE '\\'
ORDER BY situs_street, situs_house_no LIMIT 25`,
).all(likeEscape(street) + '%') as ParcelRow[];
}
res.json({
county, q, count: rows.length,
matches: rows.map(r => ({
ain: r.ain, address: r.property_location,
year_built: r.year_built, sqft: r.sqft_main,
bedrooms: r.bedrooms, bathrooms: r.bathrooms, units: r.units,
use: r.use_desc2 || r.use_desc1, assessed_value: r.roll_total_value,
})),
});
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
}
});
// full 13-section property payload
app.get('/api/property', async (req: Request, res: Response) => {
try {
const county = String(req.query.county || LA_FIPS);
const loc = String(req.query.loc || '').trim();
if (!loc) return res.status(400).json({ error: 'loc required (ain or property_location)' });
const src0 = await parcelSourceFor(county);
if (!src0) return res.status(404).json({ error: `no parcel source loaded for county ${county}` });
// ── PG-backed counties (NYC PLUTO, King WA, Cook IL: owner/zoning/values/sales where present) ──
if (src0.source_kind === 'pg') {
const found = await pgProperty(county, loc);
if (!found) return res.status(404).json({ error: 'parcel not found: ' + loc });
const { p, comps } = found;
const ctx = await countyContext(county);
const meta = PG_COUNTY_META[county] ?? PG_COUNTY_META.default;
const total = p.total_value != null ? +p.total_value : null;
const extra = p.extra ?? {};
const sales: any[] = Array.isArray(extra.sales) ? extra.sales : [];
// Historical events + public-record deep links (populated for counties with a parcel_event/parcel_links feed, e.g. San Diego).
const deedEvents = (await query<{ event_type: string; date: string; amount: string | null; doc_type: string | null; doc_number: string | null; source_url: string | null }>(
`SELECT event_type, to_char(event_date,'YYYY-MM-DD') AS date, amount::text, doc_type, doc_number, source_url
FROM parcel_event WHERE county_fips=$1 AND source_id=$2 ORDER BY event_date DESC NULLS LAST LIMIT 100`,
[county, p.source_id])).rows;
const storedLinks = (await query<{ kind: string; url: string; label: string | null }>(
`SELECT kind, url, label FROM parcel_links WHERE county_fips=$1 AND source_id=$2 ORDER BY kind`,
[county, p.source_id])).rows.map(r => ({ ...r, deep_link: r.kind === 'gis' }));
// Honest search portals (NOT deep links — the county gated per-APN URLs) built with a dashed APN.
const dashApn = (apn: string) => county === '06073' && /^\d{10}$/.test(apn)
? `${apn.slice(0, 3)}-${apn.slice(3, 6)}-${apn.slice(6, 8)}-${apn.slice(8, 10)}` : apn;
const searchPortals = county === '06073' ? [
{ kind: 'assessor', deep_link: false, url: 'https://www.sdarcc.gov', label: `Search SD Assessor (ARCC) — APN ${dashApn(p.source_id)}` },
{ kind: 'recorder', deep_link: false, url: 'https://arcc-acclaim.sdcounty.ca.gov/', label: `Search ARCC Official Records — APN ${dashApn(p.source_id)}` },
{ kind: 'tax', deep_link: false, url: 'https://iwr.sdttc.com/', label: `Search SD Treasurer-Tax Collector — APN ${dashApn(p.source_id)}` },
] : [];
return res.json({
public_records: [...storedLinks, ...searchPortals],
county, region_key: ctx.region?.canonical_key ?? null,
property_details: {
ain: p.source_id, address: p.address, use: p.use_desc, use_category: p.use_desc,
year_built: p.year_built || null, effective_year_built: null,
sqft: p.sqft || null, bedrooms: p.beds, bathrooms: p.baths, units: p.units,
note: meta.detailsNote,
},
property_history: {
last_recording_date: p.last_sale_date, sale_price: p.last_sale_price != null ? +p.last_sale_price : null,
sales: sales.map(s => ({ date: s.date, price: s.price, buyer: s.buyer || null, seller: s.seller || null })),
events: deedEvents,
recorded_documents: deedEvents.length,
// honest completeness signal: SanGIS gives only the LAST recorded doc, not the full chain
full_chain_available: deedEvents.length > 1 || sales.length > 1,
note: meta.historyNote,
},
tax_history: {
rolls: [{ roll_year: p.tax_year, total_value: total,
land_value: p.land_value != null ? +p.land_value : null,
improvement_value: p.improvement_value != null ? +p.improvement_value : null }],
note: meta.taxNote,
},
contact_information: { phone: null, email: null,
note: 'Owner phone/email are not in public rolls; owner name/mailing under Ownership.' },
ownership_information: {
owner_name: p.owner_name, mailing_address: extra.taxpayer_mailing ?? extra.mailing_address ?? null,
owner_occupied: null,
note: p.owner_name ? (extra.owner_source ? `Owner: ${extra.owner_source}.` : meta.ownerNote) : PENDING,
},
zoning: { code: p.zoning, note: p.zoning ? meta.zoningNote : PENDING },
contacts: {
county_assessor: meta.assessor,
brokers_link: '/brokers.html?state=' + (ctx.region?.state_code ?? ''),
note: 'Local brokers from the state licensing registry; per-parcel listing agent pending.',
},
map: {
lat: p.lat ?? ctx.region?.lat ?? null, lng: p.lng ?? ctx.region?.lng ?? null,
precision: p.lat ? 'parcel' : 'county_centroid',
note: p.lat ? 'Parcel coordinates from the county source.' : 'County centroid (no parcel coordinates in this extract).',
},
comps,
...buildContextSections(county, ctx),
});
}
const db = await getCountyDb(county);
if (!db) return res.status(404).json({ error: `no parcel source loaded for county ${county}` });
let parcel = db.prepare(`SELECT * FROM assessor_parcel WHERE ain = ?`).get(loc) as ParcelRow | undefined;
if (!parcel) {
parcel = db.prepare(`SELECT * FROM assessor_parcel WHERE property_location = ?`).get(loc.toUpperCase()) as ParcelRow | undefined;
}
if (!parcel) {
const { houseNo, street } = parseAddressQuery(loc);
if (houseNo != null) {
parcel = db.prepare(
`SELECT * FROM assessor_parcel WHERE situs_house_no = ? AND situs_street LIKE ? ESCAPE '\\' LIMIT 1`,
).get(houseNo, likeEscape(street) + '%') as ParcelRow | undefined;
}
}
if (!parcel) return res.status(404).json({ error: 'parcel not found: ' + loc });
// county region + county-level metrics (NRI / FMR / ACS / Zillow / derived)
const rg = await query(
`SELECT id, canonical_key, name, state_code, fips, cbsa_code, lat, lng, population
FROM region WHERE region_type = 'county' AND fips = $1`, [county],
);
const region = rg.rows[0] ?? null;
let latest: Record<string, number> = {};
let metroName: string | null = null;
let nri: { risk_rating: string | null; top_hazards: any } | null = null;
if (region) {
const mr = await query<{ metric: string; value: string }>(
`SELECT DISTINCT ON (metric) metric, value::text AS value
FROM metric_series WHERE region_id = $1
ORDER BY metric, period DESC`, [region.id],
);
for (const row of mr.rows) latest[row.metric] = Number(row.value);
if (region.cbsa_code) {
const met = await query<{ name: string }>(
`SELECT name FROM region WHERE region_type = 'metro' AND cbsa_code = $1`, [region.cbsa_code],
);
metroName = met.rows[0]?.name ?? null;
}
const nr = await query<{ risk_rating: string | null; top_hazards: any }>(
`SELECT risk_rating, top_hazards FROM nri_ratings WHERE county_fips = $1`, [county],
);
nri = nr.rows[0] ?? null;
}
// comps: same use, sqft ±30%, recent recording; same street floats up
let comps: any[] = [];
if (parcel.sqft_main && parcel.use_desc2) {
const lo = Math.round(parcel.sqft_main * 0.7), hi = Math.round(parcel.sqft_main * 1.3);
comps = (db.prepare(
`SELECT ain, property_location, year_built, sqft_main, bedrooms, bathrooms,
roll_total_value, roll_year, recording_date
FROM assessor_parcel
WHERE ain != ? AND use_desc2 = ? AND sqft_main BETWEEN ? AND ?
AND recording_date IS NOT NULL
ORDER BY (situs_street = ?) DESC, recording_date DESC
LIMIT 8`,
).all(parcel.ain, parcel.use_desc2, lo, hi, parcel.situs_street) as any[]).map(c => ({
ain: c.ain, address: c.property_location, year_built: c.year_built,
sqft: c.sqft_main, bedrooms: c.bedrooms, bathrooms: c.bathrooms,
assessed_value: c.roll_total_value, roll_year: c.roll_year, recording_date: c.recording_date,
}));
}
const fmr = ['fmr_0br', 'fmr_1br', 'fmr_2br', 'fmr_3br', 'fmr_4br']
.some(k => latest[k] != null)
? { fmr_0br: latest.fmr_0br ?? null, fmr_1br: latest.fmr_1br ?? null, fmr_2br: latest.fmr_2br ?? null,
fmr_3br: latest.fmr_3br ?? null, fmr_4br: latest.fmr_4br ?? null }
: null;
res.json({
county, region_key: region?.canonical_key ?? null,
property_details: {
ain: parcel.ain, address: parcel.property_location,
use: parcel.use_desc2 || parcel.use_desc1, use_category: parcel.use_desc1,
year_built: parcel.year_built || null, effective_year_built: parcel.effective_year_built || null,
sqft: parcel.sqft_main || null, bedrooms: parcel.bedrooms, bathrooms: parcel.bathrooms,
units: parcel.units,
},
property_history: {
last_recording_date: parcel.recording_date,
sale_price: null, // not in the LA assessor roll extract
note: 'Recording date from the LA County assessor roll; sale prices and full deed chain pending (county recorder feed, M-P2).',
},
tax_history: {
rolls: [{
roll_year: parcel.roll_year, total_value: parcel.roll_total_value,
land_value: parcel.roll_land_value, improvement_value: parcel.roll_imp_value,
}],
note: 'Single roll year in the current extract (2025 secured roll); multi-year history pending.',
},
contact_information: {
phone: null, email: null,
note: 'Owner contact details are not in the public assessor roll; ' + PENDING + '.',
},
ownership_information: {
owner_name: null, mailing_address: null, owner_occupied: null,
note: 'Owner fields are not in the CRCP roll extract; ' + PENDING + ' (full AIN roll or recorder grantee feed, M-P2).',
},
zoning: {
code: null,
note: 'Zoning is not in the assessor roll extract; ' + PENDING + ' (ZIMAS / city zoning layers, M-P2).',
},
contacts: {
county_assessor: {
name: 'Los Angeles County Office of the Assessor',
phone: '(213) 974-3211', website: 'https://assessor.lacounty.gov',
},
brokers_link: '/brokers.html?state=CA',
note: 'Local brokers from the 926k-broker registry; per-parcel listing agent pending.',
},
map: {
lat: region?.lat ?? null, lng: region?.lng ?? null,
precision: 'county_centroid',
note: 'Parcel lat/lng not in the roll extract — showing the county centroid (parcel geocoding, M-P2).',
},
comps,
climate: region ? {
risk_score: latest.nri_risk_score ?? null, eal_score: latest.nri_eal_score ?? null,
social_vulnerability: latest.nri_sovi_score ?? null, community_resilience: latest.nri_resl_score ?? null,
risk_rating: nri?.risk_rating ?? null, top_hazards: nri?.top_hazards ?? [],
source: 'FEMA National Risk Index v1.20.0 (Dec 2025), county level',
} : null,
rental: region ? {
fmr, fmr_year: fmr ? 'FY2026' : null,
zori: latest.zori ?? null, rent_yield: latest.rent_yield ?? null,
median_gross_rent: latest.median_gross_rent ?? null,
source: 'HUD FMR FY2026 + Zillow ZORI + derived, county level',
} : null,
demographics: region ? {
population: latest.population ?? region.population ?? null,
median_hh_income: latest.median_hh_income ?? null,
median_gross_rent: latest.median_gross_rent ?? null,
vacancy_rate: latest.vacancy_rate ?? null, renter_share: latest.renter_share ?? null,
source: 'Census ACS 2023 5-yr, county level',
} : null,
location: region ? {
county_name: region.name, state: region.state_code, fips: region.fips,
metro: metroName, cbsa_code: region.cbsa_code,
lat: region.lat, lng: region.lng,
zhvi: latest.zhvi ?? null, zhvi_yoy: latest.zhvi_yoy ?? null,
links: { market: '/market.html?key=' + encodeURIComponent(region.canonical_key) },
} : null,
});
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
}
});
// which counties have parcel coverage (drives the market.html entry point)
app.get('/api/property/coverage', async (_req: Request, res: Response) => {
try {
const r = await query(`SELECT county_fips, source_kind, parcel_count, loaded_at FROM parcel_source ORDER BY county_fips`);
res.json({ counties: r.rows });
} catch (e: any) {
res.status(500).json({ error: String(e.message || e) });
}
});
}