Arcstone prior-relationship flag (chip on grid card + list + firm roster + modal) + always show email/phone/website on broker modals

ad0fbf9a0314a0b59c74955f5f097555b0425706 · 2026-06-30 17:05:55 -0700 · Steve Abrams
Files touched

M public/broker-grid.html
M public/crcp.html
M scripts/serve.js
Diff

commit ad0fbf9a0314a0b59c74955f5f097555b0425706
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jun 30 17:05:55 2026 -0700

    Arcstone prior-relationship flag (chip on grid card + list + firm roster + modal) + always show email/phone/website on broker modals
---
 public/broker-grid.html | 10 ++++++----
 public/crcp.html        |  9 +++++----
 scripts/serve.js        | 23 ++++++++++++++++++++---
 3 files changed, 31 insertions(+), 11 deletions(-)

diff --git a/public/broker-grid.html b/public/broker-grid.html
index 384c74b..ff5f34f 100644
--- a/public/broker-grid.html
+++ b/public/broker-grid.html
@@ -30,6 +30,7 @@
   table.g a{color:var(--blue);text-decoration:none;} table.g a:hover{text-decoration:underline;}
   .atype{display:inline-block;font-size:10px;font-weight:700;text-transform:uppercase;padding:1px 6px;border-radius:8px;}
   .atype.res{background:rgba(88,166,255,.16);color:var(--blue);} .atype.com{background:rgba(200,162,75,.16);color:var(--gold);}
+  .atype.arc{background:rgba(127,174,106,.2);color:#8fd06f;border:1px solid rgba(127,174,106,.55);margin-left:5px;}
   .mut{color:var(--mut);} .miss{color:#5A6472;}
   #ov{display:none;position:fixed;inset:0;background:rgba(0,0,0,.6);z-index:60;align-items:flex-start;justify-content:center;padding:40px 14px;overflow-y:auto;}
   #ov.on{display:flex;}
@@ -92,6 +93,7 @@ const COLS=[
 let DATA=[], view='table', sortKey='listings', sortDir=-1, q='';
 function cell(r,c){
   const v=r[c.k];
+  if(c.k==='name') return esc(v==null?'':v)+(r.arcstone_flag?' <span class="atype arc" title="Prior Arcstone relationship">★ Arcstone</span>':'');
   if(c.t==='n') return num(v);
   if(c.t==='tel') return tel(v);
   if(c.t==='mail') return mail(v);
@@ -123,13 +125,13 @@ function render(){
   } else {
     $('#tableView').classList.add('hide'); $('#gridView').classList.remove('hide');
     $('#gridView').innerHTML=rows.map(r=>`<div class="gc brow" data-id="${r.id}" data-name="${esc(r.name)}" style="cursor:pointer">
-      <div class="nm">${esc(r.name)} ${r.agent_type==='residential'?'<span class="atype res">Res</span>':'<span class="atype com">Com</span>'}</div>
+      <div class="nm">${esc(r.name)} ${r.agent_type==='residential'?'<span class="atype res">Res</span>':'<span class="atype com">Com</span>'}${r.arcstone_flag?'<span class="atype arc" title="Prior Arcstone relationship">★ Arcstone</span>':''}</div>
       <div class="fm">${esc(r.firm||'—')}${r.title?' · '+esc(r.title):''}</div>
       <div class="row"><span class="k">Listings</span><span>${num(r.listings)}</span></div>
       <div class="row"><span class="k">Total assets</span><span>${num(r.total_assets)}</span></div>
       ${r.license?`<div class="row"><span class="k">DRE/MLS</span><span>${esc(r.license)}</span></div>`:''}
       ${r.specialties?`<div class="row"><span class="k">Specialties</span><span class="mut">${esc(r.specialties)}</span></div>`:''}
-      <div class="ci">${tel(r.phone)} ${mail(r.email)} ${r.website?url(r.website,'site↗'):''} ${r.linkedin?url(r.linkedin,'in↗'):''}</div>
+      <div class="ci">${tel(r.phone)} ${mail(r.email)} ${url(r.website,'site↗')} ${r.linkedin?url(r.linkedin,'in↗'):''}</div>
     </div>`).join('');
   }
 }
@@ -144,11 +146,11 @@ async function openContact(name,id){
   if(d.error){ mb.innerHTML='<div class="mut">'+esc(d.error)+'</div>'; return; }
   const b=d.broker, w=b.website?(/^https?:/.test(b.website)?b.website:'https://'+b.website):null;
   const li=b.linkedin?(/^https?:/.test(b.linkedin)?b.linkedin:'https://'+b.linkedin):null;
-  mb.innerHTML='<h3>'+esc(b.name)+atb(b.agent_type)+'</h3><div class="mfirm">'+esc(b.firm||'—')+(b.title?' · '+esc(b.title):'')+(b.license?' · DRE/MLS '+esc(b.license):'')+'</div>'
+  mb.innerHTML='<h3>'+esc(b.name)+atb(b.agent_type)+(b.arcstone_flag?'<span class="atype arc" title="Arcstone has a prior relationship with this agent/firm (pitched, co-listed, or Arcstone firm)">★ Prior Arcstone</span>':'')+'</h3><div class="mfirm">'+esc(b.firm||'—')+(b.title?' · '+esc(b.title):'')+(b.license?' · DRE/MLS '+esc(b.license):'')+'</div>'
     +'<div class="ci">'
     +(b.phone?'<a href="tel:'+esc(b.phone)+'">☎ '+esc(b.phone)+'</a>':'<span class="mut">☎ —</span>')
     +(b.email?'<a href="mailto:'+esc(b.email)+'">✉ '+esc(b.email)+'</a>':'<span class="mut">✉ —</span>')
-    +(w?'<a href="'+esc(w)+'" target="_blank" rel="noopener noreferrer">🌐 website</a>':'')
+    +(w?'<a href="'+esc(w)+'" target="_blank" rel="noopener noreferrer">🌐 '+esc(w.replace(/^https?:\/\/(www\.)?/,'').replace(/\/$/,''))+'</a>':'<span class="mut">🌐 —</span>')
     +(li?'<a href="'+esc(li)+'" target="_blank" rel="noopener noreferrer">in↗</a>':'')+'</div>'
     +'<div class="sec"><h4>Current listings ('+d.current.length+')</h4>'+(d.current.length?d.current.map(l=>'<div class="litem"><span>'+esc(l.address)+', '+esc(l.city)+(l.zip?' '+esc(l.zip):'')+'</span><span class="m">'+esc(l.type)+' · '+money(l.price)+(l.units?' · '+l.units+'u':'')+(l.cap_rate?' · '+l.cap_rate+'% cap':'')+(l.listed_at?' · listed '+fdate(l.listed_at):'')+'</span></div>').join(''):'<div class="mut">none in our set</div>')+'</div>'
     +'<div class="sec"><h4>Expired / off-market ('+((d.expired&&d.expired.length)||0)+')</h4>'+((d.expired&&d.expired.length)?d.expired.map(x=>'<div class="litem"><span>'+esc(x.address)+', '+esc(x.city)+'</span><span class="m">'+money(x.list_price)+' → sold '+money(x.sold_price)+' · '+fdate(x.sold_date)+(x.mls?' · MLS '+esc(x.mls):'')+(x.url?' · <a href="'+esc(x.url)+'" target="_blank" rel="noopener noreferrer">↗</a>':'')+'</span></div>').join(''):'<div class="mut">'+esc(d.expiredNote||'none')+'</div>')+'</div>'
diff --git a/public/crcp.html b/public/crcp.html
index 46b80ca..2a9dfed 100644
--- a/public/crcp.html
+++ b/public/crcp.html
@@ -54,6 +54,7 @@
   .atype{display:inline-block;font-size:10px;font-weight:700;letter-spacing:.4px;text-transform:uppercase;padding:1px 6px;border-radius:9px;margin-left:6px;vertical-align:middle;}
   .atype.res{background:rgba(91,155,213,.16);color:var(--draft);border:1px solid rgba(91,155,213,.4);}
   .atype.com{background:rgba(200,162,75,.14);color:var(--gold);border:1px solid rgba(200,162,75,.4);}
+  .atype.arc{background:rgba(127,174,106,.18);color:#8fd06f;border:1px solid rgba(127,174,106,.55);}
   .has{color:var(--active);} .miss{color:var(--archived);}
   .bar{height:8px;background:#0a0d13;border-radius:5px;overflow:hidden;margin-top:4px;}
   .bar i{display:block;height:100%;background:linear-gradient(90deg,var(--gold),var(--active));}
@@ -125,11 +126,11 @@ async function openContact(name, id, seg){
   let d; try{ d=await (await fetch('/api/broker?'+qs)).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}${atype(b.agent_type)}</h2><div class="mfirm">${b.firm||'—'}${b.title?' · '+b.title:''}${b.license?' · DRE/MLS '+b.license:''}</div>
+  mb.innerHTML=`<h2>${b.name}${atype(b.agent_type)}${b.arcstone_flag?'<span class="atype arc" title="Arcstone has a prior relationship with this agent/firm (pitched, co-listed, or Arcstone firm)">★ Prior Arcstone</span>':''}</h2><div class="mfirm">${b.firm||'—'}${b.title?' · '+b.title:''}${b.license?' · DRE/MLS '+b.license:''}</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>`:''}
+      ${w?`<a href="${w}" target="_blank" rel="noopener noreferrer">🌐 ${w.replace(/^https?:\/\/(www\.)?/,'').replace(/\/$/,'')}</a>`:'<span class="m">🌐 —</span>'}
       <span>📦 ${b.agent_type==='residential'?(d.current.length+' condo listing'+(d.current.length===1?'':'s')+' (Redfin)'):((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}${l.zip?' '+l.zip:''}</span><span class="m">${l.type} · ${money(l.price)}${l.units?' · '+l.units+'u':''}${l.cap_rate?' · '+l.cap_rate+'% cap':''}${l.listed_at?' · listed '+(l.listed_at||'').toString().slice(0,10):''}</span></div>`).join(''):'<div class="meta">none in our set</div>'}</div>
@@ -152,8 +153,8 @@ async function openFirm(name){
     <div class="cinfo"><span>👥 ${a.brokers||0} brokers/agents</span><span>🏠 ${a.residential||0} residential</span><span>☎ ${a.phone||0}</span><span>✉ ${a.email||0}</span></div>
     <div class="sec"><h4>Roster (${d.roster.length}) — click anyone for full contact + pitch</h4>
       ${d.roster.map(b=>`<div class="litem brow2" data-id="${b.id}" data-name="${(b.name||'').replace(/"/g,'&quot;')}" style="cursor:pointer">
-        <span class="a">${b.name} ${atype(b.agent_type)}</span>
-        <span class="m">${b.listings} lists · ${b.phone?'☎':'·'} ${b.email?'✉':'·'}</span></div>`).join('')}</div>`;
+        <span class="a">${b.name} ${atype(b.agent_type)}${b.arcstone_flag?'<span class="atype arc" title="Prior Arcstone relationship">★</span>':''}</span>
+        <span class="m">${b.listings} lists · ${b.phone?'☎':'·'} ${b.email?'✉':'·'} ${b.website?'🌐':'·'}</span></div>`).join('')}</div>`;
   document.querySelectorAll('.brow2').forEach(r=>r.onclick=()=>openContact(r.dataset.name, r.dataset.id));
 }
 // ---- Call Segments (Arcstone loan products) ----
diff --git a/scripts/serve.js b/scripts/serve.js
index 9bf8904..3254722 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -13,6 +13,19 @@ const PORT = process.env.PORT || 9911;
 const app = express();
 app.use(express.json({ limit: '256kb' }));
 
+// 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%')
+)`;
+
 // 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
 // deps resolve from the browserbase skill's node_modules.
@@ -133,7 +146,8 @@ app.get('/api/brokers/all', async (req, res) => {
       `SELECT b.id, b.name, f.name firm, b.agent_type, b.phone, b.email, b.website, b.linkedin,
               b.license, b.title, b.total_assets, b.specialties, b.office_addr, b.crexi_id, b.source, b.created_at,
               (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
+              + (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id) AS listings,
+              ${ARCSTONE_FLAG_SQL} AS arcstone_flag
          FROM broker b LEFT JOIN firm f ON f.id=b.firm_id
          ORDER BY listings DESC NULLS LAST, b.name`)).rows;
     res.json({ brokers: rows, total: rows.length });
@@ -372,7 +386,7 @@ app.get('/api/broker', async (req, res) => {
     const q = (s, a) => brokerdb.pool.query(s, a).then(r => r.rows);
     // Prefer id (unambiguous — names collide across commercial/residential). For a name lookup,
     // resolve deterministically: the row with the most listings (commercial edges + condos) wins.
-    const sel = `SELECT b.id, b.name, f.name firm, b.phone, b.email, b.title, b.total_assets, b.website, b.agent_type, b.license`;
+    const sel = `SELECT b.id, b.name, f.name firm, b.phone, b.email, b.title, b.total_assets, b.website, b.agent_type, b.license, ${ARCSTONE_FLAG_SQL} AS arcstone_flag`;
     const brokers = id
       ? await q(`${sel} FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.id=$1`, [id])
       : await q(`${sel},
@@ -430,11 +444,14 @@ 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
+              + (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id) AS listings,
+              ${ARCSTONE_FLAG_SQL} AS arcstone_flag
          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] }); }
 });
← 8d4f2eb Add .deploy.conf for Kamatera prod deploy (CRCP + FHA loans · back to Commercialrealestate · assessor: key-set pagination completion (AIN>cursor) to fini 1634e83 →