← back to Nationalrealestate
src/server/contractors.ts
247 lines
/**
* SHARED CA CSLB licensed-contractor API — the coordination layer the four RE
* builds (usre engine, CRCP/Frank, HomesOnSpec, RENTV) all read. TK-10488.
*
* Reads migration 018 tables:
* ca_contractors (registry, one row per CSLB license)
* ca_contractor_class_ref (code -> title/kind, 48 CSLB codes seeded)
* ca_contractor_classification (normalized license -> many trades)
*
* READ-ONLY. All endpoints work on an EMPTY table (data is loaded by a separate
* process). Sourcing is CSLB public-record; any customer-facing display is
* Steve-gated — this whole server sits behind Basic Auth, so these are internal.
*
* GET /api/contractors -> search (name/county/city/zip/class/status)
* GET /api/contractors/match -> coordination endpoint (mode=home | mode=deal)
* GET /api/contractors/:license_no -> one record (same shape)
*
* NOTE: :license_no route is registered AFTER /match so 'match' is never
* swallowed as a license number.
*/
import type { Express, Request, Response } from 'express';
import { query } from '../../db/pool.ts';
const clamp = (v: unknown, def: number, max: number) => {
const n = Math.floor(Number(v));
return Number.isFinite(n) && n > 0 ? Math.min(n, max) : def;
};
const like = (v: unknown, cap = 80) => '%' + String(v).slice(0, cap).replace(/[%_\\]/g, '\\$&') + '%';
// CSLB classification codes: 'A', 'B', 'B-2', 'C-10', 'ASB', 'HAZ', 'D-49' …
const CODE_RE = /^[A-Z]{1,3}(-[A-Z0-9]{1,3})?$/;
const cleanCode = (v: unknown) => {
const t = String(v ?? '').trim().toUpperCase();
return CODE_RE.test(t) ? t : null;
};
const cleanCodes = (v: unknown) =>
String(v ?? '').split(',').map((s) => cleanCode(s)).filter((x): x is string => !!x);
/** Default residential trade set for mode=home (GC + the common subs). */
const HOME_TRADES = ['B', 'C-8', 'C-10', 'C-36', 'C-20', 'C-39', 'C-33', 'C-35', 'C-15', 'C-54', 'C-5', 'C-6'];
/** GC classes framed for a commercial loan officer (mode=deal). */
const GC_CLASSES = ['A', 'B'];
/** Key trades around a CRE asset for mode=deal. */
const DEAL_TRADES = ['C-8', 'C-10', 'C-20', 'C-36', 'C-39', 'C-16', 'C-35', 'C-12'];
const PER_GROUP_CAP = 25;
// Column whitelist for ?sort=<col>&order=asc|desc on /api/contractors (SQL-injection-safe:
// raw input never reaches SQL — only these fixed fragments do). business_name is the stable
// tiebreaker so paging stays deterministic. Keys mirror the shaped JSON field names.
const SORT_COLS: Record<string, string> = {
business_name: 'c.business_name',
license_no: 'c.license_no',
city: 'c.city',
county: 'c.county',
state: 'c.state_code',
zip: 'c.zip',
license_status: 'c.license_status',
issue_date: 'c.issue_date',
expire_date: 'c.expire_date',
primary_class: 'c.primary_class',
phone: 'c.phone',
};
/** Resolve ORDER BY from ?sort=&order= (whitelist); defaults to business_name ASC. */
function resolveContractorOrderBy(q: Request['query']): string {
const col = SORT_COLS[String(q.sort || '')];
if (col) {
const desc = String(q.order || 'asc').toLowerCase() === 'desc';
return `${col} ${desc ? 'DESC' : 'ASC'} NULLS LAST, c.business_name ASC`;
}
return 'c.business_name ASC';
}
// Columns selected for the public contractor shape (+ optional distance alias).
const CONTRACTOR_COLS = `
c.id, c.license_no, c.business_name, c.address, c.city, c.county,
c.state_code, c.zip, c.phone, c.license_status,
to_char(c.issue_date,'YYYY-MM-DD') AS issue_date,
to_char(c.expire_date,'YYYY-MM-DD') AS expire_date,
c.classifications, c.primary_class,
c.cb_bond_company, c.cb_bond_amount,
c.wc_status, c.wc_insurance_co, c.lat, c.lng`;
/** Build the JSON shape one contractor row returns (shared by all endpoints). */
function shapeContractor(r: any, titles: Map<string, string>) {
const codes: string[] = Array.isArray(r.classifications) ? r.classifications : [];
return {
license_no: r.license_no,
business_name: r.business_name,
address: r.address ?? null,
city: r.city ?? null,
county: r.county ?? null,
state: r.state_code ?? 'CA',
zip: r.zip ?? null,
phone: r.phone ?? null,
license_status: r.license_status ?? null,
issue_date: r.issue_date ?? null,
expire_date: r.expire_date ?? null,
classifications: codes,
primary_class: r.primary_class ?? null,
classification_titles: codes.map((code) => ({ code, title: titles.get(code) ?? null })),
bond: {
company: r.cb_bond_company ?? null,
amount: r.cb_bond_amount != null ? Number(r.cb_bond_amount) : null,
},
workers_comp: {
status: r.wc_status ?? null,
carrier: r.wc_insurance_co ?? null,
},
...(r.distance_km != null ? { distance_km: Number(Number(r.distance_km).toFixed(1)) } : {}),
};
}
/** Load titles for every distinct code appearing in a batch of rows (one query). */
async function titleMapFor(rows: any[]): Promise<Map<string, string>> {
const codes = new Set<string>();
for (const r of rows) for (const c of (r.classifications || [])) codes.add(c);
if (r_empty(codes)) return new Map();
const ref = await query<{ code: string; title: string }>(
`SELECT code, title FROM ca_contractor_class_ref WHERE code = ANY($1)`,
[[...codes]]);
return new Map(ref.rows.map((x) => [x.code, x.title]));
}
const r_empty = (s: Set<string>) => s.size === 0;
/** WHERE builder shared by /api/contractors and the match modes. */
function buildFilter(q: Request['query'], opts: { defaultStatus?: string } = {}) {
const params: unknown[] = [];
const parts: string[] = [];
// status: default 'CLEAR' (CSLB "good standing"; CSLB has no 'Active' value); status=all (or empty) drops the filter
const rawStatus = q.status === undefined ? (opts.defaultStatus ?? 'CLEAR') : String(q.status);
if (rawStatus && rawStatus.toLowerCase() !== 'all') {
params.push(rawStatus);
parts.push(`c.license_status = $${params.length}`);
}
if (q.q) { params.push(like(q.q)); parts.push(`c.normalized_name ILIKE $${params.length} ESCAPE '\\'`); }
if (q.county) { params.push(String(q.county)); parts.push(`upper(c.county) = upper($${params.length})`); }
if (q.city) { params.push(String(q.city)); parts.push(`upper(c.city) = upper($${params.length})`); }
if (q.zip) { params.push(String(q.zip).slice(0, 10)); parts.push(`c.zip = $${params.length}`); }
const code = cleanCode(q.class);
if (code) { params.push([code]); parts.push(`c.classifications && $${params.length}::text[]`); }
return { where: parts.length ? parts.join(' AND ') : 'TRUE', params };
}
export function mountContractors(app: Express) {
// ── search ────────────────────────────────────────────────────────────────
app.get('/api/contractors', async (req: Request, res: Response) => {
try {
const { where, params } = buildFilter(req.query);
const limit = clamp(req.query.limit, 50, 200);
const offset = Math.max(0, Math.floor(Number(req.query.offset) || 0));
const orderBy = resolveContractorOrderBy(req.query);
const rows = await query<any>(
`SELECT ${CONTRACTOR_COLS} FROM ca_contractors c
WHERE ${where}
ORDER BY ${orderBy}
LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
[...params, limit, offset]);
const cnt = await query<{ n: number }>(
`SELECT count(*)::int n FROM ca_contractors c WHERE ${where}`, params);
const titles = await titleMapFor(rows.rows);
res.json({
count: cnt.rows[0]?.n ?? 0,
limit, offset,
results: rows.rows.map((r) => shapeContractor(r, titles)),
});
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
// ── coordination endpoint (registered BEFORE :license_no) ───────────────────
app.get('/api/contractors/match', async (req: Request, res: Response) => {
try {
const mode = String(req.query.mode || 'home').toLowerCase();
if (mode !== 'home' && mode !== 'deal') return res.status(400).json({ error: "mode must be 'home' or 'deal'" });
// geo scope: zip > city > county (at least one recommended, but not required)
const zip = req.query.zip ? String(req.query.zip).slice(0, 10) : null;
const city = req.query.city ? String(req.query.city) : null;
const county = req.query.county ? String(req.query.county) : null;
const ctype = req.query.ctype ? String(req.query.ctype) : null;
// trade groups to fill, per mode
let groups: string[];
if (mode === 'home') {
const req_trades = cleanCodes(req.query.trades);
groups = req_trades.length ? req_trades : HOME_TRADES;
} else {
// deal: GCs (as one 'A/B' group) + key trades
groups = [...GC_CLASSES, ...DEAL_TRADES];
}
// dedupe, preserve order
groups = [...new Set(groups)];
// one query per group, nearest-first, capped
const matches: Record<string, any[]> = {};
for (const code of groups) {
// $1 = status, $2 = [code] (used by the && classifications filter)
const params: unknown[] = ['CLEAR', [code]];
// nearest-first ranking: exact zip, then city, then county (each a 0/1 DESC key)
const rank: string[] = [];
if (zip) { params.push(zip); rank.push(`(c.zip = $${params.length})::int DESC`); }
if (city) { params.push(city); rank.push(`(upper(c.city) = upper($${params.length}))::int DESC`); }
if (county) { params.push(county); rank.push(`(upper(c.county) = upper($${params.length}))::int DESC`); }
rank.push('c.business_name ASC');
// geo scope filter: keep to the market (county OR city OR zip) when any is given
const scope: string[] = [];
if (zip) { params.push(zip); scope.push(`c.zip = $${params.length}`); }
if (city) { params.push(city); scope.push(`upper(c.city) = upper($${params.length})`); }
if (county) { params.push(county); scope.push(`upper(c.county) = upper($${params.length})`); }
const scopeSql = scope.length ? `AND (${scope.join(' OR ')})` : '';
params.push(PER_GROUP_CAP);
const rows = await query<any>(
`SELECT ${CONTRACTOR_COLS}
FROM ca_contractors c
WHERE c.license_status = $1
AND c.classifications && $2::text[]
${scopeSql}
ORDER BY ${rank.join(', ')}
LIMIT $${params.length}`,
params);
const titles = await titleMapFor(rows.rows);
matches[code] = rows.rows.map((r) => shapeContractor(r, titles));
}
res.json({
mode,
criteria: { zip, city, county, ...(mode === 'deal' ? { ctype } : {}), trades: groups },
cap_per_group: PER_GROUP_CAP,
matches,
});
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
// ── one record ──────────────────────────────────────────────────────────────
app.get('/api/contractors/:license_no', async (req: Request, res: Response) => {
try {
const lic = String(req.params.license_no).trim().slice(0, 40);
if (!lic) return res.status(400).json({ error: 'bad license_no' });
const rows = await query<any>(
`SELECT ${CONTRACTOR_COLS} FROM ca_contractors c WHERE c.license_no = $1 LIMIT 1`, [lic]);
if (!rows.rows.length) return res.status(404).json({ error: 'contractor not found' });
const titles = await titleMapFor(rows.rows);
res.json(shapeContractor(rows.rows[0], titles));
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
}