← back to Commercialrealestate
CRCP: broker contact columns (phone/email/web) + click-through contact modal (full business contact + current/closed listings) + Frank user (admin) + Arcstone Financial resources + non-warrantable-condo pitch generator (email/call-script DRAFTS, send stays George-gated)
d7ca6495664bf697cf2971530225a53c65e760ee · 2026-06-28 07:41:07 -0700 · Steve
Files touched
M public/crcp.htmlA scripts/db/pitch-schema.sqlM scripts/serve.js
Diff
commit d7ca6495664bf697cf2971530225a53c65e760ee
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Jun 28 07:41:07 2026 -0700
CRCP: broker contact columns (phone/email/web) + click-through contact modal (full business contact + current/closed listings) + Frank user (admin) + Arcstone Financial resources + non-warrantable-condo pitch generator (email/call-script DRAFTS, send stays George-gated)
---
public/crcp.html | 57 +++++++++++++++++++++++++++++++--
scripts/db/pitch-schema.sql | 52 ++++++++++++++++++++++++++++++
scripts/serve.js | 77 +++++++++++++++++++++++++++++++++++++++++----
3 files changed, 177 insertions(+), 9 deletions(-)
diff --git a/public/crcp.html b/public/crcp.html
index 3368d34..a34401a 100644
--- a/public/crcp.html
+++ b/public/crcp.html
@@ -39,6 +39,21 @@
.prog{display:flex;justify-content:space-between;font-size:12px;color:var(--muted);margin-top:10px;}
.meta{font-size:11px;color:var(--muted);margin-top:6px;}
.flash{animation:fl .8s;} @keyframes fl{0%{background:rgba(200,162,75,.18)}100%{background:transparent}}
+ /* contact modal */
+ #ov{position:fixed;inset:0;background:rgba(0,0,0,.6);display:none;z-index:50;}
+ #ov.on{display:flex;align-items:center;justify-content:center;}
+ #modal{width:min(760px,94vw);max-height:90vh;overflow-y:auto;background:var(--panel);border:1px solid var(--line);border-radius:15px;padding:20px 22px;}
+ #modal h2{margin:0 0 2px;font-size:20px;} #modal .mfirm{color:var(--gold);font-size:13px;}
+ #modal .x{float:right;background:none;border:0;color:var(--muted);font-size:20px;cursor:pointer;}
+ .cinfo{display:flex;flex-wrap:wrap;gap:8px;margin:10px 0;}
+ .cinfo a,.cinfo span{font-size:13px;background:var(--panel2);border:1px solid var(--line);border-radius:18px;padding:5px 11px;color:var(--fg);text-decoration:none;}
+ .sec{margin-top:14px;} .sec h4{font-size:11px;text-transform:uppercase;letter-spacing:.5px;color:var(--muted);margin:0 0 7px;border-top:1px solid var(--line);padding-top:10px;}
+ .litem{display:flex;justify-content:space-between;font-size:13px;padding:4px 0;} .litem .a{color:var(--fg);} .litem .m{color:var(--muted);}
+ .btn{background:var(--gold);color:#1a1206;border:0;border-radius:9px;padding:9px 14px;font-size:13px;font-weight:700;cursor:pointer;margin-right:8px;}
+ .btn.alt{background:transparent;color:var(--draft);border:1px solid var(--draft);}
+ .pitch{background:#0a0d13;border:1px solid var(--line);border-radius:10px;padding:12px;font-size:13px;white-space:pre-wrap;margin-top:10px;}
+ .pitch .subj{color:var(--gold);font-weight:700;margin-bottom:6px;}
+ .gate{font-size:11px;color:var(--staged);margin-top:6px;}
</style>
</head>
<body>
@@ -58,7 +73,7 @@
<div class="meta" id="enrMeta"></div>
</div>
<div class="panel"><h3>Top brokers <span class="c" id="brC"></span></h3>
- <table id="brTbl"><thead><tr><th>Broker</th><th>Firm</th><th class="n">Lists</th><th>Contact</th></tr></thead><tbody></tbody></table>
+ <table id="brTbl"><thead><tr><th>Broker</th><th class="n">Lists</th><th>Phone</th><th>Email</th><th>Web</th></tr></thead><tbody></tbody></table>
</div>
</div>
<div>
@@ -72,8 +87,37 @@
</div>
</div>
</div>
+<div id="ov"><div id="modal"><button class="x" onclick="document.getElementById('ov').classList.remove('on')">✕</button><div id="mbody"></div></div></div>
<script>
const $=s=>document.querySelector(s);
+const money=n=>n?'$'+Number(n).toLocaleString():'—';
+async function openContact(name){
+ const ov=$('#ov'), mb=$('#mbody'); ov.classList.add('on'); mb.innerHTML='<div class="meta">loading '+name+'…</div>';
+ let d; try{ d=await (await fetch('/api/broker?name='+encodeURIComponent(name))).json(); }catch(e){ mb.innerHTML='error'; return; }
+ if(d.error){ mb.innerHTML='<div class="meta">'+d.error+'</div>'; return; }
+ const b=d.broker, w=b.website?(/^https?:/.test(b.website)?b.website:'https://'+b.website):null;
+ mb.innerHTML=`<h2>${b.name}</h2><div class="mfirm">${b.firm||'—'}${b.title?' · '+b.title:''}</div>
+ <div class="cinfo">
+ ${b.phone?`<a href="tel:${b.phone}">☎ ${b.phone}</a>`:'<span class="m">☎ —</span>'}
+ ${b.email?`<a href="mailto:${b.email}">✉ ${b.email}</a>`:'<span class="m">✉ —</span>'}
+ ${w?`<a href="${w}" target="_blank" rel="noopener noreferrer">🌐 website</a>`:''}
+ <span>📦 ${b.total_assets||'?'} total listings (Crexi)</span>
+ </div>
+ <div class="sec"><h4>Current listings (${d.current.length})</h4>${d.current.length?d.current.map(l=>`<div class="litem"><span class="a">${l.address}, ${l.city}</span><span class="m">${l.type} · ${money(l.price)}${l.cap_rate?' · '+l.cap_rate+'% cap':''}</span></div>`).join(''):'<div class="meta">none in our set</div>'}</div>
+ <div class="sec"><h4>Closed listings — past 10 yrs (${d.closed.length})</h4>${d.closed.length?d.closed.map(l=>`<div class="litem"><span class="a">${l.address}, ${l.city}</span><span class="m">${money(l.sold_price)} · ${l.sold_date||''}</span></div>`).join(''):'<div class="meta">'+(d.closedNote||'none')+'</div>'}</div>
+ <div class="sec"><h4>Arcstone pitch (non-warrantable condo financing)</h4>
+ <button class="btn" onclick="genPitch('${name.replace(/'/g,"\\'")}','email')">✉ Generate email pitch</button>
+ <button class="btn alt" onclick="genPitch('${name.replace(/'/g,"\\'")}','call_script')">☎ Generate call script</button>
+ <div id="pitchout">${d.pitches&&d.pitches.length?renderPitch(d.pitches[0]):''}</div>
+ </div>`;
+}
+function renderPitch(p){ return `<div class="pitch">${p.subject?`<div class="subj">${p.subject}</div>`:''}${(p.body||'').replace(/&/g,'&').replace(/</g,'<')}</div><div class="gate">⚑ DRAFT — sending is George-gated + Steve-approved; not sent from here.</div>`; }
+async function genPitch(name,channel){
+ const out=$('#pitchout'); out.innerHTML='<div class="meta">drafting…</div>';
+ try{ const r=await (await fetch('/api/broker/pitch',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({name,channel})})).json();
+ out.innerHTML = r.error?('<div class="meta">'+r.error+'</div>'):renderPitch(r.pitch);
+ }catch(e){ out.innerHTML='<div class="meta">'+e.message+'</div>'; }
+}
const fmt=n=>n==null?'—':Number(n).toLocaleString();
let prev={};
function tile(k,key,v,sub,cls){ const d=(prev[key]!=null&&v>prev[key])?('+'+(v-prev[key])):'';
@@ -102,8 +146,15 @@ async function poll(){
const penr=(prev.brokers_phone||0)+(prev.brokers_email||0);
$('#enrMeta').innerHTML = enr>penr ? `<span class="has">▲ +${enr-penr} contacts since last refresh — Job B populating…</span>` : (enr===0?'Job B (broker enrichment) queued — contacts will populate here live.':'enrichment idle / complete');
// top brokers
- $('#brC').textContent=(s.topBrokers||[]).length+' shown';
- $('#brTbl tbody').innerHTML=(s.topBrokers||[]).map(b=>`<tr><td>${b.name}</td><td class="firm">${b.firm||'—'}</td><td class="n">${b.listings}</td><td>${b.phone?'<span class="has">☎</span>':'<span class="miss">☎</span>'} ${b.email?'<span class="has">✉</span>':'<span class="miss">✉</span>'}</td></tr>`).join('');
+ $('#brC').textContent=(s.topBrokers||[]).length+' shown · click a broker';
+ const web=u=>{ if(!u) return '<span class="miss">—</span>'; const h=/^https?:/.test(u)?u:'https://'+u; return `<a href="${h}" target="_blank" rel="noopener noreferrer" style="color:var(--gold)">site↗</a>`; };
+ $('#brTbl tbody').innerHTML=(s.topBrokers||[]).map(b=>`<tr class="brow" data-name="${(b.name||'').replace(/"/g,'"')}" style="cursor:pointer">
+ <td>${b.name}<div class="firm">${b.firm||'—'}</div></td>
+ <td class="n">${b.listings}</td>
+ <td>${b.phone?`<a href="tel:${b.phone}" class="has" style="color:var(--active)">${b.phone}</a>`:'<span class="miss">—</span>'}</td>
+ <td>${b.email?`<a href="mailto:${b.email}" class="has" style="color:var(--draft)">${b.email}</a>`:'<span class="miss">—</span>'}</td>
+ <td>${web(b.website)}</td></tr>`).join('');
+ document.querySelectorAll('.brow').forEach(r=>r.onclick=e=>{ if(e.target.tagName==='A') return; openContact(r.dataset.name); });
// warrantability
$('#wa').innerHTML = bar('FHA-approved', s.fhaApproved||0, (s.fhaApproved||0)+(s.fhaExpired||0), 'var(--active)')
+ bar('FHA-expired (flag/review)', s.fhaExpired||0, (s.fhaApproved||0)+(s.fhaExpired||0), 'var(--staged)');
diff --git a/scripts/db/pitch-schema.sql b/scripts/db/pitch-schema.sql
new file mode 100644
index 0000000..5a8edb8
--- /dev/null
+++ b/scripts/db/pitch-schema.sql
@@ -0,0 +1,52 @@
+-- pitch-schema.sql — users, Arcstone resources, broker pitches, public closed-listings.
+-- Scope: LEGITIMATE B2B outreach prep only. Broker = business contact. NO personal/family data,
+-- NO background-dossier services. Closed listings = publicly-recorded sales only, sourced.
+
+-- App users (start with Frank, full access). Arcstone resources are owned by a user.
+CREATE TABLE IF NOT EXISTS app_user (
+ id serial PRIMARY KEY,
+ username text UNIQUE NOT NULL,
+ display_name text,
+ role text DEFAULT 'member', -- 'admin' = full access
+ created_at timestamptz DEFAULT now()
+);
+
+-- Arcstone Financial resources/programs (the lender whose services the pitches introduce).
+-- owner_user_id ties the resource set to a user (Frank), per "resources on arcstone stay with frank".
+CREATE TABLE IF NOT EXISTS arcstone_resource (
+ id serial PRIMARY KEY,
+ owner_user_id integer REFERENCES app_user(id),
+ kind text, -- program | contact | value_prop | disclaimer
+ title text NOT NULL,
+ detail text,
+ url text,
+ created_at timestamptz DEFAULT now()
+);
+
+-- Generated pitch DRAFTS to a broker (introducing Arcstone non-warrantable/non-conforming condo
+-- financing). status stays 'draft' — SENDING is a separate, George-gated, Steve-approved action.
+CREATE TABLE IF NOT EXISTS broker_pitch (
+ id serial PRIMARY KEY,
+ broker_id integer REFERENCES broker(id) ON DELETE CASCADE,
+ author_user_id integer REFERENCES app_user(id),
+ channel text DEFAULT 'email', -- email | call_script
+ subject text,
+ body text,
+ status text DEFAULT 'draft', -- draft | approved | sent (sent only via George + approval)
+ created_at timestamptz DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS idx_pitch_broker ON broker_pitch(broker_id);
+
+-- Publicly-recorded CLOSED/sold listings tied to a broker (legit public sources only, sourced).
+CREATE TABLE IF NOT EXISTS broker_closed_listing (
+ id serial PRIMARY KEY,
+ broker_id integer REFERENCES broker(id) ON DELETE CASCADE,
+ address text,
+ city text,
+ sold_price bigint,
+ sold_date date,
+ type text,
+ source text, -- provenance URL (public record)
+ created_at timestamptz DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS idx_closed_broker ON broker_closed_listing(broker_id);
diff --git a/scripts/serve.js b/scripts/serve.js
index 8e0c5b1..9161e01 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -76,6 +76,24 @@ app.get('/api/brokers/top', async (req, res) => {
catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
});
+// Full enriched contact card for one broker (phone/email/website/linkedin/office + per-field
+// provenance + listing book). Business-contact research data only (Job B enrichment).
+app.get('/api/brokers/contact/:id', async (req, res) => {
+ if (!brokerdb) return res.status(503).json({ error: 'broker DB unavailable' });
+ try {
+ const b = await brokerdb.brokerContact(+req.params.id);
+ if (!b) return res.status(404).json({ error: 'broker not found' });
+ res.json(b);
+ } catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
+});
+
+// Enrichment coverage rollup (how many brokers now have phone/email/website/linkedin).
+app.get('/api/brokers/enrich-stats', async (req, res) => {
+ if (!brokerdb) return res.json({ unavailable: true });
+ try { res.json(await brokerdb.enrichStats()); }
+ catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
+});
+
// Firm market-share: listings per firm (the broker graph's listing.firm_name). Powers the share chart.
app.get('/api/firms/share', async (req, res) => {
if (!brokerdb) return res.json([]);
@@ -204,19 +222,66 @@ app.get('/api/crcp/stats', async (req, res) => {
try { out.brokers_web = (await q(`SELECT count(*)::int n FROM broker WHERE website IS NOT NULL`))[0].n; } catch (_) { out.brokers_web = null; }
try { out.condos = (await q(`SELECT count(*)::int n FROM condo`))[0].n;
out.condosByStatus = {}; (await q(`SELECT warrantable_status s,count(*)::int n FROM condo GROUP BY 1`)).forEach(r => out.condosByStatus[r.s] = r.n); } catch (_) { out.condos = 0; }
- out.topBrokers = await q(`SELECT b.name, f.name firm, count(DISTINCT bl.listing_id)::int listings, b.total_assets, b.phone, b.email,
- CASE WHEN to_regclass('public.broker') IS NOT NULL THEN NULL END AS _x, b.website
+ const brokerCols = 'b.name, f.name firm, count(DISTINCT bl.listing_id)::int listings, b.total_assets, b.phone, b.email' +
+ (out.brokers_web != null ? ', b.website' : '');
+ out.topBrokers = await q(`SELECT ${brokerCols}
FROM broker b LEFT JOIN firm f ON f.id=b.firm_id LEFT JOIN broker_listing bl ON bl.broker_id=b.id
- GROUP BY b.id, f.name ORDER BY listings DESC NULLS LAST LIMIT 20`).catch(async()=>
- q(`SELECT b.name, f.name firm, count(DISTINCT bl.listing_id)::int listings, b.total_assets, b.phone, b.email
- FROM broker b LEFT JOIN firm f ON f.id=b.firm_id LEFT JOIN broker_listing bl ON bl.broker_id=b.id
- GROUP BY b.id, f.name ORDER BY listings DESC NULLS LAST LIMIT 20`));
+ GROUP BY b.id, f.name ORDER BY listings DESC NULLS LAST LIMIT 20`);
out.topFirms = await q(`SELECT f.name firm, count(DISTINCT bl.listing_id)::int listings, count(DISTINCT b.id)::int brokers FROM firm f JOIN broker b ON b.firm_id=f.id JOIN broker_listing bl ON bl.broker_id=b.id GROUP BY 1 ORDER BY 2 DESC LIMIT 12`);
}
res.json(out);
} catch (e) { res.status(502).json({ ...out, error: String(e.message).split('\n')[0] }); }
});
+// Broker contact detail — full BUSINESS contact + current listings + public closed listings + pitches.
+// Legitimate B2B prep only (no personal/family data). Click-through target for the CRCP + mind-map.
+app.get('/api/broker', async (req, res) => {
+ if (!brokerdb) return res.status(503).json({ error: 'broker DB unavailable' });
+ const name = String(req.query.name || '').trim();
+ if (!name) return res.status(400).json({ error: 'name required' });
+ try {
+ const q = (s, a) => brokerdb.pool.query(s, a).then(r => r.rows);
+ const brokers = await q(`SELECT b.id, b.name, f.name firm, b.phone, b.email, b.title, b.total_assets, b.website
+ FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.name ILIKE $1 ORDER BY b.id LIMIT 1`, [name]).catch(() =>
+ q(`SELECT b.id, b.name, f.name firm, b.phone, b.email, b.title, b.total_assets FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.name ILIKE $1 LIMIT 1`, [name]));
+ const broker = brokers[0];
+ if (!broker) return res.status(404).json({ error: 'broker not found' });
+ const current = await q(`SELECT l.id, l.address, l.city, l.type, l.price, l.cap_rate, bl.role
+ FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id WHERE bl.broker_id=$1 ORDER BY l.price DESC NULLS LAST`, [broker.id]);
+ const closed = await q(`SELECT address, city, sold_price, sold_date, type, source FROM broker_closed_listing WHERE broker_id=$1 ORDER BY sold_date DESC NULLS LAST`, [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(() => []);
+ res.json({ broker, current, closed, pitches, closedNote: closed.length ? null : 'Public 10-yr closed-sales enrichment not yet run (gated public-records pull).' });
+ } 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 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);
+ const broker = (await q(`SELECT id, name, firm_id FROM broker WHERE name ILIKE $1 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';
+ const listings = await q(`SELECT l.address, l.city, l.type FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id WHERE bl.broker_id=$1 ORDER BY l.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');
+ const subject = `Financing for non-warrantable condos your buyers can’t close conventionally`;
+ 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 — specifically non-warrantable and non-conforming condos (projects that fail Fannie/Freddie/FHA: high investor concentration, off the FHA-approved list, HOA litigation, high commercial ratio).\n\nWhen your buyer loves a condo but the project won’t qualify conventionally, that’s where we keep your deal alive:\n${progLines}\n\nIf you’ve got a condo buyer stuck on warrantability right now, send it over and I’ll tell you in a day whether we can finance it.\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})\n\nOpener: "Hi ${first}, this is [you] with Arcstone Financial — do you have 30 seconds?"\nHook: "${refs}We finance the condos conventional lenders decline — non-warrantable and non-conforming projects."\nValue: ${progs.map(p => p.title).join(', ')}.\nAsk: "Do you have any condo buyers stuck on warrantability right now? Send me the project and I’ll tell you in a day if we can fund it."\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] }); }
+});
+
app.use('/data', express.static(path.join(ROOT, 'data')));
app.use('/', express.static(path.join(ROOT, 'public')));
← c5507a7 CRE Job B Tier 3: filter data-aggregator emails (signalhire/
·
back to Commercialrealestate
·
auto-save: 2026-06-28T07:43:27 (2 files) — public/brokers.ht 44b6973 →