← back to Rentv
crm: full LinkedIn relationship layer from the whole export — connected/messaged/endorsed/invited (green, 'people you know') + firms-you-follow (distinct blue tint, 'whole brokerage')
f7b12994c5974376eb695403d81637fc01b2f988 · 2026-08-12 18:25:06 -0700 · Steve Abrams
- scripts/build-linkedin-graph.py parses Connections/messages/Invitations/Endorsements/Company-Follows -> data/linkedin-graph.json (gitignored PII)
- server: loadLinkedInGraph flags each contact by STRONGEST signal (Endorsed>Messaged>Connected>Invited>Follows-firm); li_rel + per-signal booleans on /api/crm
- crm.html: green person-badge vs blue firm-badge, split count line, relationship in detail panel
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M .gitignoreM public/crm.htmlA scripts/build-linkedin-graph.pyM server.js
Diff
commit f7b12994c5974376eb695403d81637fc01b2f988
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 12 18:25:06 2026 -0700
crm: full LinkedIn relationship layer from the whole export — connected/messaged/endorsed/invited (green, 'people you know') + firms-you-follow (distinct blue tint, 'whole brokerage')
- scripts/build-linkedin-graph.py parses Connections/messages/Invitations/Endorsements/Company-Follows -> data/linkedin-graph.json (gitignored PII)
- server: loadLinkedInGraph flags each contact by STRONGEST signal (Endorsed>Messaged>Connected>Invited>Follows-firm); li_rel + per-signal booleans on /api/crm
- crm.html: green person-badge vs blue firm-badge, split count line, relationship in detail panel
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
.gitignore | 2 ++
public/crm.html | 21 +++++++++----
scripts/build-linkedin-graph.py | 66 +++++++++++++++++++++++++++++++++++++++++
server.js | 39 ++++++++++++++++++++----
4 files changed, 117 insertions(+), 11 deletions(-)
diff --git a/.gitignore b/.gitignore
index 21527f54..289394a9 100644
--- a/.gitignore
+++ b/.gitignore
@@ -43,3 +43,5 @@ data/*.bak-*
data/crm-imported.jsonl.bak-*
data/linkedin-connections.csv
data/linkedin-follows.json
+data/linkedin-export/
+data/linkedin-graph.json
diff --git a/public/crm.html b/public/crm.html
index 0e308299..c949b847 100644
--- a/public/crm.html
+++ b/public/crm.html
@@ -83,6 +83,12 @@
.grid .row.lifollow{border-color:#2ecc71;box-shadow:0 0 0 1px #2ecc71 inset}
.tbl tbody tr.lifollow td{background:rgba(46,204,113,.11)}
.tbl tbody tr.lifollow td:first-child{box-shadow:inset 4px 0 0 #2ecc71}
+ /* firm-you-follow — distinct blue tint, weaker than the person-green */
+ .rows .row.lifirm{box-shadow:inset 4px 0 0 #4a90e2;background:rgba(74,144,226,.05)}
+ .grid .row.lifirm{border-color:#4a90e2}
+ .tbl tbody tr.lifirm td{background:rgba(74,144,226,.08)}
+ .tbl tbody tr.lifirm td:first-child{box-shadow:inset 4px 0 0 #4a90e2}
+ .li-badge.firm{background:rgba(74,144,226,.16);border-color:#4a90e2;color:#a9ccf7}
/* ── table mode: every scanned field a sortable column, full-width across the screen ── */
.content.tablemode .listwrap{flex:1;min-height:0;overflow:hidden;display:flex}
.content.tablemode #rows{flex:1;min-height:0}
@@ -331,7 +337,7 @@ function tableHTML(rows){
let v=c[k]; if(k==='rank'&&v!=null) v='#'+v;
return `<td title="${esc(v==null?'':v)}">${esc(v==null?'':v)}</td>`;
}).join('');
- return `<div class="tblwrap"><table class="tbl">${colg}<thead><tr>${head}</tr></thead><tbody>${rows.map(c=>`<tr data-id="${esc(c.id)}" class="${S.sel===c.id?'sel':''} ${c.li_follow?'lifollow':''}">${cell(c)}</tr>`).join('')}</tbody></table></div>`;
+ return `<div class="tblwrap"><table class="tbl">${colg}<thead><tr>${head}</tr></thead><tbody>${rows.map(c=>`<tr data-id="${esc(c.id)}" class="${S.sel===c.id?'sel':''} ${liCls(c)}">${cell(c)}</tr>`).join('')}</tbody></table></div>`;
}
function sortRows(rows){
let key=S.sort, dir=S.sortDir||'asc';
@@ -366,6 +372,10 @@ function drillFor(c){
return links.filter(Boolean).join('');
}
function typeLabel(t){ const m=(DATA.types||[]).concat(DATA.registries||[]).find(x=>x.key===t); return m?m.label:t; }
+// LinkedIn relationship helpers — a PERSON you know (green) vs someone at a FIRM you follow (distinct tint)
+function liPerson(c){ return c.li_connected||c.li_messaged||c.li_endorsed||c.li_invited; }
+function liCls(c){ return liPerson(c)?'lifollow':(c.li_firm?'lifirm':''); }
+function liBadge(c){ if(liPerson(c)) return `<span class="li-badge">🟢 ${esc(c.li_rel||'Connected')}</span>`; if(c.li_firm) return `<span class="li-badge firm">🏢 ${esc(c.firm||'Follows firm')}</span>`; return ''; }
function rowHTML(c){
const F=S.fields;
const bits=[];
@@ -374,10 +384,10 @@ function rowHTML(c){
if(F.email&&c.email) bits.push(esc(c.email));
if(F.phone&&c.phone) bits.push(esc(c.phone));
const av = c.photo ? `<img class="av" src="${esc(c.photo)}" loading="lazy" alt="" onerror="this.remove()">` : (c.type==='directory'?`<span class="av ph">${esc((c.name||'?').trim()[0]||'?')}</span>`:'');
- return `<div class="row ${S.sel===c.id?'sel':''} ${c.li_follow?'lifollow':''}" data-id="${esc(c.id)}">
+ return `<div class="row ${S.sel===c.id?'sel':''} ${liCls(c)}" data-id="${esc(c.id)}">
${av}
<div class="rmeta">
- <div class="nm">${esc(c.name)}${c.li_follow?'<span class="li-badge">🟢 in Following</span>':''}${F.type?`<span class="badge t-${esc(c.type)}">${esc(typeLabel(c.type))}</span>`:''}</div>
+ <div class="nm">${esc(c.name)}${liBadge(c)}${F.type?`<span class="badge t-${esc(c.type)}">${esc(typeLabel(c.type))}</span>`:''}</div>
${bits.length?`<div class="f">${bits.join(' · ')}</div>`:''}
${F.created?`<div class="when" title="${esc(c.created_at||'')}">🕓 ${esc(fmtWhen(c.created_at))}</div>`:''}
${F.drill?`<div class="drill">${drillFor(c)}</div>`:''}
@@ -397,8 +407,8 @@ async function render(){
$('countline').textContent = isRegistry(S.type)
? `${sorted.length.toLocaleString()} match${sorted.length===1?'':'es'} · ${typeLabel(S.type)} (registry search)`
: `${sorted.length.toLocaleString()} of ${Number(DATA.counts[S.type]??DATA.counts.all??0).toLocaleString()} · ${S.type==='all'?'all contacts':typeLabel(S.type)}`;
- const nfollow=sorted.filter(c=>c.li_follow).length;
- if(nfollow) $('countline').innerHTML = esc($('countline').textContent)+` · <span style="color:#8affc0;font-weight:800">🟢 ${nfollow.toLocaleString()} already following on LinkedIn</span>`;
+ const npeople=sorted.filter(liPerson).length, nfirm=sorted.filter(c=>!liPerson(c)&&c.li_firm).length;
+ if(npeople||nfirm){ let extra=''; if(npeople) extra+=` · <span style="color:#8affc0;font-weight:800">🟢 ${npeople.toLocaleString()} you know</span>`; if(nfirm) extra+=` · <span style="color:#6fb1ff;font-weight:800">🏢 ${nfirm.toLocaleString()} at firms you follow</span>`; $('countline').innerHTML = esc($('countline').textContent)+extra; }
if(S.mode==='table'){
$('rows').innerHTML = sorted.length ? tableHTML(sorted) : (hint?'':'<div class="empty">No contacts match.</div>');
$('rows').querySelectorAll('.tbl thead th[data-col]').forEach(th=>th.onclick=ev=>{
@@ -433,6 +443,7 @@ function openDetail(id, rows){
const add=(k,v)=>{ if(v!=null&&v!=='') cells.push(`<div class="cell"><div class="kk">${k}</div><div class="vv">${esc(v)}</div></div>`); };
add('Type',typeLabel(c.type)); add('Firm',c.firm); add('Email',c.email); add('Phone',c.phone);
add('City',[c.city,c.state].filter(Boolean).join(', ')); add('Title',c.title);
+ if(liPerson(c)) add('LinkedIn','🟢 '+(c.li_rel||'Connected')); else if(c.li_firm) add('LinkedIn','🏢 Follows '+(c.firm||'firm'));
if(c.type==='directory'){ add('List',c.list_category); if(c.rank!=null) add('Rank','#'+c.rank); add('Address',c.address); add('Website',c.website); add('Employees',c.employees); add('Founded',c.year_founded); add('Detail',c.hq); if(c.xref&&c.xref.count) add('Cross-Ref (RE DB)', c.xref.count+' match'+(c.xref.count>1?'es':'')+': '+c.xref.labels.join(' | ')); }
if(c.dre_license) add('DRE License',c.dre_license);
if(c.assessed_total) add('Assessed', '$'+Number(c.assessed_total).toLocaleString());
diff --git a/scripts/build-linkedin-graph.py b/scripts/build-linkedin-graph.py
new file mode 100644
index 00000000..872144be
--- /dev/null
+++ b/scripts/build-linkedin-graph.py
@@ -0,0 +1,66 @@
+#!/usr/bin/env python3
+"""Parse the LinkedIn data export (data/linkedin-export/*.csv) into one compact
+data/linkedin-graph.json of RAW names per relationship type. The rentv server normalizes
++ matches these against CRM contacts. INTERNAL/PII — gitignored."""
+import csv, json, os
+BASE = os.path.expanduser('~/Projects/rentv')
+EXP = os.path.join(BASE, 'data', 'linkedin-export')
+OUT = os.path.join(BASE, 'data', 'linkedin-graph.json')
+SELF = {'steve abrams', 'designer wallcoverings', ''}
+
+def clean(s): return (s or '').strip()
+def is_self(n): return n.strip().lower() in SELF
+
+def read_dict(fn):
+ p = os.path.join(EXP, fn)
+ if not os.path.exists(p): return []
+ with open(p, newline='', encoding='utf-8', errors='replace') as f:
+ return list(csv.DictReader(f))
+
+connected, firms, messaged, invited, endorsed = set(), set(), set(), set(), set()
+
+# Connections.csv — has a Notes preamble before the real header
+p = os.path.join(EXP, 'Connections.csv')
+if os.path.exists(p):
+ with open(p, newline='', encoding='utf-8', errors='replace') as f:
+ rows = list(csv.reader(f))
+ hi = next((i for i, r in enumerate(rows) if r and r[0].strip() == 'First Name'), -1)
+ if hi >= 0:
+ for r in rows[hi+1:]:
+ if len(r) >= 2:
+ nm = (clean(r[0]) + ' ' + clean(r[1])).strip()
+ if nm and not is_self(nm): connected.add(nm)
+
+# Company Follows.csv — Organization
+for r in read_dict('Company Follows.csv'):
+ o = clean(r.get('Organization'))
+ if o: firms.add(o)
+
+# messages.csv — FROM / TO (the other party in each thread)
+for r in read_dict('messages.csv'):
+ for k in ('FROM', 'TO'):
+ nm = clean(r.get(k))
+ if nm and not is_self(nm): messaged.add(nm)
+
+# Invitations.csv — From / To
+for r in read_dict('Invitations.csv'):
+ for k in ('From', 'To'):
+ nm = clean(r.get(k))
+ if nm and not is_self(nm): invited.add(nm)
+
+# Endorsements given + received
+for r in read_dict('Endorsement_Given_Info.csv'):
+ nm = (clean(r.get('Endorsee First Name')) + ' ' + clean(r.get('Endorsee Last Name'))).strip()
+ if nm and not is_self(nm): endorsed.add(nm)
+for r in read_dict('Endorsement_Received_Info.csv'):
+ nm = (clean(r.get('Endorser First Name')) + ' ' + clean(r.get('Endorser Last Name'))).strip()
+ if nm and not is_self(nm): endorsed.add(nm)
+
+graph = {
+ 'connected': sorted(connected), 'firms': sorted(firms), 'messaged': sorted(messaged),
+ 'invited': sorted(invited), 'endorsed': sorted(endorsed),
+}
+json.dump(graph, open(OUT, 'w'), ensure_ascii=False)
+print(f"wrote {OUT}")
+for k in ('connected', 'firms', 'messaged', 'invited', 'endorsed'):
+ print(f" {len(graph[k]):>6} {k}")
diff --git a/server.js b/server.js
index a0b321c2..312cb3fe 100644
--- a/server.js
+++ b/server.js
@@ -2396,6 +2396,31 @@ function loadLinkedInFollows() {
for (const n of names) { const nn = liNorm(n); if (nn) full.add(nn); const f = liFirstLast(n); if (f) fl.add(f); }
return { full, fl };
}
+// Full LinkedIn relationship graph from data/linkedin-graph.json (built from the export by
+// scripts/build-linkedin-graph.py): connections, people messaged, invited, endorsed, + firms followed.
+// Each CRM contact is tagged with its STRONGEST signal for the green highlight.
+function liBuildPersonSets(arr){ const full = new Set(), fl = new Set(); for (const n of (arr || [])) { const nn = liNorm(n); if (nn) full.add(nn); const f = liFirstLast(n); if (f) fl.add(f); } return { full, fl }; }
+function liFirmNorm(s){ return String(s || '').toLowerCase().replace(/\b(inc|llc|llp|lp|ltd|co|corp|corporation|company|group|the|usa|us|and|of|realty|real|estate|properties|partners|associates|brokerage|commercial|residential|holdings|international|worldwide)\b/g, ' ').replace(/[^a-z0-9]+/g, ' ').trim(); }
+function loadLinkedInGraph(){
+ let g = null;
+ try { g = JSON.parse(fs.readFileSync(path.join(DATA, 'linkedin-graph.json'), 'utf8')); } catch { /* none */ }
+ const csvConn = loadLinkedInFollows(); // connections.csv / follows.json (fallback + union)
+ const connected = liBuildPersonSets(g && g.connected);
+ for (const x of csvConn.full) connected.full.add(x); for (const x of csvConn.fl) connected.fl.add(x);
+ const messaged = liBuildPersonSets(g && g.messaged), invited = liBuildPersonSets(g && g.invited), endorsed = liBuildPersonSets(g && g.endorsed);
+ const firms = new Set(); if (g) for (const o of (g.firms || [])) { const n = liFirmNorm(o); if (n && n.length > 2) firms.add(n); }
+ const inSet = (sets, name) => { const nn = liNorm(name); if (nn && sets.full.has(nn)) return true; const f = liFirstLast(name); return !!(f && sets.fl.has(f)); };
+ return {
+ rel(name, firm){
+ const r = { connected: inSet(connected, name), messaged: inSet(messaged, name), invited: inSet(invited, name), endorsed: inSet(endorsed, name), firm: false };
+ if (firm) { const fn = liFirmNorm(firm); if (fn && fn.length > 2 && firms.has(fn)) r.firm = true; }
+ const label = r.endorsed ? 'Endorsed' : r.messaged ? 'Messaged' : r.connected ? 'Connected' : r.invited ? 'Invited' : r.firm ? 'Follows firm' : null;
+ r.any = !!(r.endorsed || r.messaged || r.connected || r.invited || r.firm);
+ r.label = label;
+ return r;
+ },
+ };
+}
app.get('/api/crm', adminOnly, async (req, res) => {
try {
let subs = [];
@@ -2411,15 +2436,17 @@ app.get('/api/crm', adminOnly, async (req, res) => {
const owners = crmNormalizeOwners(readJSON('la-commercial.json', { marquee: [] }));
const commercial = crmNormalizeCommercial(commercialRows);
const directory = crmLoadImported(); // manually-imported contacts (scanned lists, business cards, etc.)
- const liFollows = loadLinkedInFollows();
- const all = [...ownedContacts, ...commercial, ...owners, ...directory].map((c) => ({
+ const liGraph = loadLinkedInGraph();
+ const all = [...ownedContacts, ...commercial, ...owners, ...directory].map((c) => {
+ const lr = c.name || c.firm ? liGraph.rel(c.name, c.firm) : { any: false, label: null };
+ return {
...c,
- // green-highlight flag: is this person someone Steve already follows/connects with on LinkedIn?
- // exact normalized full-name match, OR first+last-token match (catches middle-initial/nickname variants).
- li_follow: !!(c.name && (liFollows.full.has(liNorm(c.name)) || (liFirstLast(c.name) && liFollows.fl.has(liFirstLast(c.name))))),
+ // LinkedIn relationship (strongest signal drives the green highlight + badge)
+ li_follow: lr.any, li_rel: lr.label,
+ li_connected: !!lr.connected, li_messaged: !!lr.messaged, li_endorsed: !!lr.endorsed, li_invited: !!lr.invited, li_firm: !!lr.firm,
// one precomputed lowercase search blob per contact (no per-keystroke JSON.stringify).
_s: [c.name, c.firm, c.email, c.phone, c.city, c.title, c.interest, c.source, c.dre_license, c.ain, c.list_category, c.address].filter(Boolean).join(' ').toLowerCase(),
- }));
+ }; });
const TYPES = [
{ key: 'newsletter', label: 'Subscribers', emoji: '📩' },
{ key: 'sublease', label: 'Sublease Brokers', emoji: '🔑' },
← c6e2c456 auto-data-snapshot: 2026-08-12T18:04:53 (7 data files) — dat
·
back to Rentv
·
crm: live LinkedIn refresh — incremental scrape of recent co c0a1f7cc →