← back to Commercialrealestate
auto-save: 2026-07-23T14:52:02 (1 files) — scripts/serve.js
6ad26c3cd045e93e19907acac8287bce5dc46976 · 2026-07-23 14:52:11 -0700 · Steve Abrams
Files touched
Diff
commit 6ad26c3cd045e93e19907acac8287bce5dc46976
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jul 23 14:52:11 2026 -0700
auto-save: 2026-07-23T14:52:02 (1 files) — scripts/serve.js
---
scripts/serve.js | 100 ++++++++-----------------------------------------------
1 file changed, 13 insertions(+), 87 deletions(-)
diff --git a/scripts/serve.js b/scripts/serve.js
index 12412d2..fe29915 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -169,18 +169,10 @@ app.post('/api/contacts/:key/send-draft', async (req, res) => {
} catch (e) { res.status(502).json({ error: 'george_unreachable', detail: String(e.message).split('\n')[0] }); }
});
-// Arcstone-relationship flag (SQL). A broker/agent is flagged if Arcstone has a
-// prior relationship: (1) already pitched/contacted (broker_pitch), (2) co-listed
-// a property with an Arcstone broker (Frank id 5356) via broker_listing, (3) is a
-// member of the Arcstone firm, or (4) is the synthetic Arcstone demo (is_test).
-// `b` must be the broker table alias in the surrounding query.
-const ARCSTONE_FLAG_SQL = `(
- b.is_test
- OR EXISTS(SELECT 1 FROM broker_pitch bp WHERE bp.broker_id = b.id)
- OR EXISTS(SELECT 1 FROM broker_listing x JOIN broker_listing y ON x.listing_id = y.listing_id
- WHERE x.broker_id = b.id AND y.broker_id = 5356)
- OR EXISTS(SELECT 1 FROM firm af WHERE af.id = b.firm_id AND af.name ILIKE 'Arcstone%')
-)`;
+// Relationship flag (SQL) — retired. The lender-relationship feature was removed,
+// so this always evaluates false. Kept as a stable column so downstream queries and
+// the front end that read `arcstone_flag` don't need to change shape.
+const ARCSTONE_FLAG_SQL = `false`;
// GATED, cost-surfaced live comps via Browserbase (cloud browser -> LoopNet city/type search).
// ~$0.03/call (a Browserbase session). Shells to scripts/live-comps-bb.js so the cloud-browser
@@ -301,7 +293,7 @@ app.get('/api/brokers/linkedin', async (req, res) => {
}));
res.json({
people, total: people.length, withProfile: people.filter(p => p.linkedin).length,
- note: 'Manual follow list for Frank — open each profile and follow in LinkedIn. No automation (LinkedIn TOS).'
+ note: 'Manual follow list — open each profile and follow in LinkedIn. No automation (LinkedIn TOS).'
});
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0], people: [] }); }
});
@@ -408,12 +400,10 @@ app.get('/api/gov-agents', async (req, res) => {
return govSnapMetro(res, metro, q, limit);
});
-// Best leads for Frank — rank brokers by how financeable their CURRENT listings are for Arcstone's
-// non-QM / portfolio products (mixed-use, non-warrantable condo, DSCR multifamily — the deals
-// conventional lenders pass on). Returns ranked brokers WITH their current listings (MLS-like fields).
-// Business listing data only; excludes Frank himself (the loan officer, not a lead). No owner names.
-const FRANK_BROKER_ID = 5356;
-app.get('/api/leads/frank', async (req, res) => {
+// Top brokers — rank brokers by the quality/mix of their CURRENT listings (mixed-use + multifamily +
+// commercial weighted highest). Returns ranked brokers WITH their current listings (MLS-like fields).
+// Business listing data only. No owner names.
+app.get('/api/leads/top', async (req, res) => {
if (!brokerdb) return res.json({ leads: [], unavailable: true });
try {
const limit = Math.min(+(req.query.limit) || 40, 200);
@@ -428,8 +418,7 @@ app.get('/api/leads/frank', async (req, res) => {
ELSE 1 END AS fit
FROM broker b JOIN firm f ON f.id=b.firm_id
JOIN broker_listing bl ON bl.broker_id=b.id
- JOIN listing l ON l.id=bl.listing_id
- WHERE b.id <> ${FRANK_BROKER_ID})
+ JOIN listing l ON l.id=bl.listing_id)
SELECT broker_id, name, firm, agent_type,
count(*)::int listings, sum(fit)::int fit_score, coalesce(sum(price),0)::bigint total_value,
json_agg(json_build_object('id',lid,'address',address,'city',city,'zip',zip,'type',type,
@@ -437,7 +426,7 @@ app.get('/api/leads/frank', async (req, res) => {
ORDER BY price DESC NULLS LAST) listings_json
FROM ll GROUP BY broker_id, name, firm, agent_type
ORDER BY fit_score DESC, total_value DESC LIMIT $1`, [limit])).rows;
- res.json({ leads, scoredBy: 'Arcstone-financeability of current listings (mixed-use/condo×3, multifamily/commercial×2). Business data; no owner names.' });
+ res.json({ leads, scoredBy: 'Listing mix quality (mixed-use/condo×3, multifamily/commercial×2). Business data; no owner names.' });
} catch (e) {
// Graceful degrade (prod has no `cre` Postgres → pool.query throws): the front-end iterates
// d.leads, so an empty-array 200 renders "no leads" cleanly instead of a 502 console error.
@@ -818,7 +807,6 @@ app.get('/api/broker', async (req, res) => {
AND lower(cs.address) LIKE '%'||lower(coalesce((regexp_match(l.address,'[A-Za-z]{3,}'))[1],'~'))||'%'
WHERE bl.broker_id=$1 AND cs.sold_price>0
ORDER BY cs.sold_date DESC NULLS LAST LIMIT 12`, [broker.id]).catch(() => []);
- const pitches = await q(`SELECT id, channel, subject, body, status, created_at FROM broker_pitch WHERE broker_id=$1 ORDER BY created_at DESC`, [broker.id]).catch(() => []);
// Sales: recent closed_sale (Redfin sold) in the cities this broker lists in — with sold DATE + MLS# + url.
// Honest area context — NOT attributed to this broker (the sold feed carries no agent).
const cities = [...new Set(current.map(c => c.city).filter(Boolean))].slice(0, 6);
@@ -826,7 +814,7 @@ app.get('/api/broker', async (req, res) => {
if (cities.length) comps = await q(
`SELECT address, city, zip, sold_price, sold_date, beds, baths, sqft, mls, url FROM closed_sale
WHERE city = ANY($1) AND sold_price>0 ORDER BY sold_date DESC NULLS LAST LIMIT 12`, [cities]).catch(() => []);
- res.json({ broker, current, closed, expired, pitches, comps,
+ res.json({ broker, current, closed, expired, comps,
compsNote: comps.length ? 'Recent sold comps in this broker’s areas (Redfin) with sold dates + MLS# — area context, not this broker’s own closings.' : null,
expiredNote: expired.length ? 'Listings of this broker that now appear in the sold feed (best-effort address match).' : 'No off-market/expired feed yet — none of this broker’s listings matched a sold record.',
closedNote: closed.length ? null : 'Per-broker closed history needs a gated detail-scrape; area sales shown instead.' });
@@ -851,77 +839,15 @@ app.get('/api/firm', async (req, res) => {
const roster = await q(
`SELECT b.id, b.name, b.agent_type, b.phone, b.email, b.website,
(SELECT count(*) FROM broker_listing bl WHERE bl.broker_id=b.id)
- + (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id) AS listings,
- ${ARCSTONE_FLAG_SQL} AS arcstone_flag
+ + (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id) AS listings
FROM broker b WHERE b.firm_id=$1 ORDER BY listings DESC, b.name LIMIT 200`, [firm.id]);
- const firmArcstone = /^arcstone/i.test(firm.name);
const agg = (await q(`SELECT count(*)::int brokers,
count(*) FILTER (WHERE agent_type='residential')::int residential,
count(phone)::int phone, count(email)::int email FROM broker WHERE firm_id=$1`, [firm.id]))[0];
- firm.arcstone_flag = firmArcstone || roster.some(b => b.arcstone_flag);
res.json({ firm, roster, agg });
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
});
-// Generate a pitch DRAFT to a broker introducing Arcstone non-warrantable/non-conforming condo
-// financing. Template-based (no fabrication), stored as status='draft'. SENDING is NOT done here —
-// outreach is George-gated + Steve-approved (deliberately).
-app.post('/api/broker/pitch', async (req, res) => {
- if (!brokerdb) return res.status(503).json({ error: 'broker DB unavailable' });
- const bid = (req.body || {}).id ? +(req.body || {}).id : null;
- const name = String((req.body || {}).name || '').trim();
- const channel = (req.body || {}).channel === 'call_script' ? 'call_script' : 'email';
- try {
- const q = (s, a) => brokerdb.pool.query(s, a).then(r => r.rows);
- // Resolve the SAME row /api/broker shows (id wins; else most-listings row for a colliding name).
- const broker = bid
- ? (await q(`SELECT id, name, firm_id, agent_type FROM broker WHERE id=$1`, [bid]))[0]
- : (await q(`SELECT id, name, firm_id, agent_type FROM broker b WHERE name ILIKE $1
- ORDER BY ((SELECT count(*) FROM broker_listing bl WHERE bl.broker_id=b.id)
- +(SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id)) DESC, b.id LIMIT 1`, [name]))[0];
- if (!broker) return res.status(404).json({ error: 'broker not found' });
- const firm = (await q(`SELECT name FROM firm WHERE id=$1`, [broker.firm_id]))[0]?.name || 'your brokerage';
- // refs pull from BOTH commercial listings + residential condos so the pitch references real work.
- // The Arcstone non-warrantable-condo pitch is MOST relevant to residential condo agents.
- const listings = await q(`SELECT address, city, type FROM (
- SELECT l.address, l.city, l.type, l.price FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id WHERE bl.broker_id=$1
- UNION ALL
- SELECT c.address, c.city, 'condo' type, c.price FROM broker_condo bc JOIN condo c ON c.id=bc.condo_id WHERE bc.broker_id=$1
- ) x ORDER BY price DESC NULLS LAST LIMIT 3`, [broker.id]);
- const progs = await q(`SELECT title, detail FROM arcstone_resource WHERE kind='program' ORDER BY id`);
- const contact = (await q(`SELECT detail FROM arcstone_resource WHERE kind='contact' LIMIT 1`))[0]?.detail || '';
- const first = broker.name.split(/\s+/)[0];
- const refs = listings.length ? `I’ve seen your work around LA — including ${listings.map(l => `${l.address} (${l.city}, ${l.type})`).slice(0, 2).join(' and ')}. ` : '';
- const progLines = progs.map(p => `• ${p.title} — ${p.detail}`).join('\n');
- // Segment-aware pitch: subject + hook + ask match the loan product Steve is offering.
- const seg = SEGMENTS[(req.body || {}).segment] || SEGMENTS['nonwarrantable-condo'];
- const SUBJ = {
- 'mixed-use': 'Financing for mixed-use deals conventional lenders pass on',
- 'nonwarrantable-condo': 'Financing for non-warrantable condos your buyers can’t close conventionally',
- 'standard-condo': 'I’ll check your condo complex in Fannie Mae’s CPM — and finance the ones that don’t qualify',
- 'dscr-1-4': 'DSCR financing for your 1-4 unit investor buyers (no income docs)',
- 'dscr-5-9': 'DSCR financing for 5-9 unit small multifamily',
- 'sfr-bankstmt': 'Bank-statement / P&L financing for your self-employed buyers',
- 'nonqm': 'Non-QM financing for the buyers conventional lenders decline'
- };
- const ASK = {
- 'standard-condo': 'Send me any condo project you’re working and I’ll run it through Fannie Mae’s CPM for prior approvals/rejections — free, in a day.',
- 'dscr-1-4': 'Got an investor buyer on a 1-4 unit who’s tight on income docs? Send it — DSCR qualifies on the property’s cash flow.',
- 'dscr-5-9': 'Have a 5-9 unit deal? DSCR can qualify it on cash flow — send it over.',
- 'sfr-bankstmt': 'Have a self-employed buyer the banks turned down? Bank-statement / P&L income can get them qualified.',
- 'mixed-use': 'Got a mixed-use deal stuck conventionally? Send it and I’ll tell you in a day if we can fund it.'
- };
- const subject = SUBJ[(req.body || {}).segment] || SUBJ['nonwarrantable-condo'];
- const ask = ASK[(req.body || {}).segment] || 'If you’ve got a buyer stuck on financing right now, send it over and I’ll tell you in a day whether we can fund it.';
- const body = `Hi ${first},\n\n${refs}I work with Arcstone Financial, a national non-QM/portfolio lender, and I reach out to active ${firm} agents because we close the deals conventional lenders walk away from — ${seg.hook}.\n\nWhere we keep your deal alive (${seg.product}):\n${progLines}\n\n${ask}\n\nArcstone Financial · ${contact}\n\n— (drafted via the CRE control panel; NMLS-licensed; this is not an offer to lend)`;
- const callScript = `CALL SCRIPT — ${broker.name} (${firm}) · ${seg.label}\n\nOpener: "Hi ${first}, this is [you] with Arcstone Financial — do you have 30 seconds?"\nHook: "${refs}We do ${seg.hook}."\nProduct: ${seg.product} — ${progs.map(p => p.title).join(', ')}.\nAsk: "${ask}"\nClose: Arcstone Financial · ${contact}`;
- const r = (await q(`INSERT INTO broker_pitch(broker_id, author_user_id, channel, subject, body, status)
- VALUES($1,(SELECT id FROM app_user WHERE username='frank'),$2,$3,$4,'draft') RETURNING id, channel, subject, body, status, created_at`,
- [broker.id, channel, channel === 'email' ? subject : null, channel === 'email' ? body : callScript]))[0];
- res.json({ pitch: r, note: 'DRAFT only — sending to a broker is George-gated + Steve-approved, not done here.' });
- } catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
-});
-
// On-demand email hunt for ONE broker — backs the "✉ find email" button on the CRCP Top-brokers
// table + broker grid + contact modal. Visits the broker's own website (homepage + up to 3
// contact-ish pages), extracts mailto:/text emails, and picks with the SAME guards as the
← 2dd4383 afternoon CRE update 2026-07-23
·
back to Commercialrealestate
·
Remove Frank + Arcstone from CRCP entirely 9ce5a12 →