← back to Rentv
feat(services): rank big multi-location firms first (solicitation view) + add Arizona
427a4cf23ceeadb3244db624c7d9426111887e4d · 2026-08-06 12:31:28 -0700 · Steve
Reframes /services as a ranked PROSPECT list — the large, multi-branch operators
(the firms with ad budgets to solicit) surface first.
- server.js: default sort 'Most locations' — normalized-name window count over branch
licenses (regexp_replace(lower(name),'[^a-z0-9]+','')) → loc_count + city_count;
the locations sort dedupes to one representative card per firm, biggest first;
featured majors stay pinned above. Fixed count(DISTINCT) OVER (not allowed in PG)
via a GROUP BY stats CTE joined back.
- Added ARIZONA market (27,436 contractors + 9,467 developers — RENTV covers AZ).
Honest coverage: featured firms now carry 'national' vs 'socal' tokens so
nationally-present Title/Property majors (First American, CBRE) appear in AZ but
SoCal-only law firms never falsely claim Arizona; AZ correctly shows only
Construction/Developers/Title/Property (lenders/escrow/legal hidden — no AZ registry).
- services.html: 'Most locations' default sort option, 📍 N-locations badge, Arizona
in the metro switcher.
Files touched
M data/services-featured.jsonM public/services.htmlM server.js
Diff
commit 427a4cf23ceeadb3244db624c7d9426111887e4d
Author: Steve <steve@designerwallcoverings.com>
Date: Thu Aug 6 12:31:28 2026 -0700
feat(services): rank big multi-location firms first (solicitation view) + add Arizona
Reframes /services as a ranked PROSPECT list — the large, multi-branch operators
(the firms with ad budgets to solicit) surface first.
- server.js: default sort 'Most locations' — normalized-name window count over branch
licenses (regexp_replace(lower(name),'[^a-z0-9]+','')) → loc_count + city_count;
the locations sort dedupes to one representative card per firm, biggest first;
featured majors stay pinned above. Fixed count(DISTINCT) OVER (not allowed in PG)
via a GROUP BY stats CTE joined back.
- Added ARIZONA market (27,436 contractors + 9,467 developers — RENTV covers AZ).
Honest coverage: featured firms now carry 'national' vs 'socal' tokens so
nationally-present Title/Property majors (First American, CBRE) appear in AZ but
SoCal-only law firms never falsely claim Arizona; AZ correctly shows only
Construction/Developers/Title/Property (lenders/escrow/legal hidden — no AZ registry).
- services.html: 'Most locations' default sort option, 📍 N-locations badge, Arizona
in the metro switcher.
---
data/services-featured.json | 140 ++++++++++++++++++++++++++++++++++++++------
public/services.html | 13 ++--
server.js | 48 ++++++++++++---
3 files changed, 169 insertions(+), 32 deletions(-)
diff --git a/data/services-featured.json b/data/services-featured.json
index b4670761..af38832c 100644
--- a/data/services-featured.json
+++ b/data/services-featured.json
@@ -1,23 +1,125 @@
{
- "note": "Editorially FEATURED, real and verifiable major CRE service firms. Purpose: guarantee at least one vendor in EVERY service category in EVERY served metro, including the categories the public California license registries do not cover (Title, Property Management) or barely cover (Legal — only 3 attorney records exist statewide in the registry). These are well-known firms that genuinely serve Southern California commercial real estate; markets:\"all\" means they operate across all served metros (Greater LA, Orange County, San Diego, Inland Empire, Ventura). Registry-backed categories (Construction, Lenders, Escrow, Developers) already have hundreds-to-thousands of vendors per metro and need no featured seed. Verify engagement terms and current licensure directly with each firm.",
+ "note": "Editorially FEATURED, real and verifiable major CRE service firms. Purpose: guarantee at least one vendor in EVERY service category in every metro where the public California license registries do not cover (Title, Property Management) or barely cover (Legal — only 3 attorney records exist statewide). markets tokens: \"national\" = firm operates across all served metros INCLUDING Arizona (e.g. First American Title, CBRE); \"socal\" = the five California metros only (Greater LA, Orange County, San Diego, Inland Empire, Ventura) — used for SoCal-specific firms (e.g. California real-estate law firms) so they never falsely claim Arizona coverage. Registry-backed categories (Construction, Lenders, Escrow, Developers) already have hundreds-to-thousands of vendors per metro. Verify engagement terms and current licensure directly with each firm.",
"vendors": [
- { "cat": "title", "name": "First American Title", "website": "https://www.firstam.com", "hq": "Santa Ana, CA", "markets": "all", "desc": "National title insurance, commercial title & settlement services." },
- { "cat": "title", "name": "Fidelity National Title", "website": "https://www.fntic.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Commercial title insurance and closing services across California." },
- { "cat": "title", "name": "Chicago Title", "website": "https://www.chicagotitle.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Commercial title & escrow (a Fidelity National Financial company)." },
- { "cat": "title", "name": "Old Republic Title", "website": "https://www.oldrepublictitle.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "National underwriter of commercial title insurance." },
-
- { "cat": "property", "name": "CBRE", "website": "https://www.cbre.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Global commercial property & facilities management." },
- { "cat": "property", "name": "JLL", "website": "https://www.us.jll.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Commercial real estate services & property management." },
- { "cat": "property", "name": "Cushman & Wakefield", "website": "https://www.cushmanwakefield.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Asset services & commercial property management." },
- { "cat": "property", "name": "Greystar", "website": "https://www.greystar.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Largest U.S. multifamily property manager." },
-
- { "cat": "legal", "name": "Allen Matkins", "website": "https://www.allenmatkins.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "California real estate, land use & construction law firm." },
- { "cat": "legal", "name": "Cox, Castle & Nicholson", "website": "https://www.coxcastle.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Real estate, land use and construction law." },
- { "cat": "legal", "name": "Sheppard Mullin", "website": "https://www.sheppardmullin.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Full-service firm with a leading real estate practice." },
-
- { "cat": "construction", "name": "Hathaway Dinwiddie", "website": "https://www.hdcco.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Major California commercial general contractor." },
- { "cat": "lenders", "name": "CBRE Capital Markets", "website": "https://www.cbre.com/services/capital-markets", "hq": "Los Angeles, CA", "markets": "all", "desc": "Commercial debt & structured finance for CRE." },
- { "cat": "escrow", "name": "Commerce Escrow (a Wilshire Quinn company)", "website": "https://www.comescrow.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Independent commercial escrow & holding services." },
- { "cat": "developers", "name": "Rising Realty Partners", "website": "https://www.risingrealty.com", "hq": "Los Angeles, CA", "markets": "all", "desc": "Los Angeles-based commercial developer & operator." }
+ {
+ "cat": "title",
+ "name": "First American Title",
+ "website": "https://www.firstam.com",
+ "hq": "Santa Ana, CA",
+ "markets": "national",
+ "desc": "National title insurance, commercial title & settlement services."
+ },
+ {
+ "cat": "title",
+ "name": "Fidelity National Title",
+ "website": "https://www.fntic.com",
+ "hq": "Los Angeles, CA",
+ "markets": "national",
+ "desc": "Commercial title insurance and closing services across California."
+ },
+ {
+ "cat": "title",
+ "name": "Chicago Title",
+ "website": "https://www.chicagotitle.com",
+ "hq": "Los Angeles, CA",
+ "markets": "national",
+ "desc": "Commercial title & escrow (a Fidelity National Financial company)."
+ },
+ {
+ "cat": "title",
+ "name": "Old Republic Title",
+ "website": "https://www.oldrepublictitle.com",
+ "hq": "Los Angeles, CA",
+ "markets": "national",
+ "desc": "National underwriter of commercial title insurance."
+ },
+ {
+ "cat": "property",
+ "name": "CBRE",
+ "website": "https://www.cbre.com",
+ "hq": "Los Angeles, CA",
+ "markets": "national",
+ "desc": "Global commercial property & facilities management."
+ },
+ {
+ "cat": "property",
+ "name": "JLL",
+ "website": "https://www.us.jll.com",
+ "hq": "Los Angeles, CA",
+ "markets": "national",
+ "desc": "Commercial real estate services & property management."
+ },
+ {
+ "cat": "property",
+ "name": "Cushman & Wakefield",
+ "website": "https://www.cushmanwakefield.com",
+ "hq": "Los Angeles, CA",
+ "markets": "national",
+ "desc": "Asset services & commercial property management."
+ },
+ {
+ "cat": "property",
+ "name": "Greystar",
+ "website": "https://www.greystar.com",
+ "hq": "Los Angeles, CA",
+ "markets": "national",
+ "desc": "Largest U.S. multifamily property manager."
+ },
+ {
+ "cat": "legal",
+ "name": "Allen Matkins",
+ "website": "https://www.allenmatkins.com",
+ "hq": "Los Angeles, CA",
+ "markets": "socal",
+ "desc": "California real estate, land use & construction law firm."
+ },
+ {
+ "cat": "legal",
+ "name": "Cox, Castle & Nicholson",
+ "website": "https://www.coxcastle.com",
+ "hq": "Los Angeles, CA",
+ "markets": "socal",
+ "desc": "Real estate, land use and construction law."
+ },
+ {
+ "cat": "legal",
+ "name": "Sheppard Mullin",
+ "website": "https://www.sheppardmullin.com",
+ "hq": "Los Angeles, CA",
+ "markets": "socal",
+ "desc": "Full-service firm with a leading real estate practice."
+ },
+ {
+ "cat": "construction",
+ "name": "Hathaway Dinwiddie",
+ "website": "https://www.hdcco.com",
+ "hq": "Los Angeles, CA",
+ "markets": "socal",
+ "desc": "Major California commercial general contractor."
+ },
+ {
+ "cat": "lenders",
+ "name": "CBRE Capital Markets",
+ "website": "https://www.cbre.com/services/capital-markets",
+ "hq": "Los Angeles, CA",
+ "markets": "socal",
+ "desc": "Commercial debt & structured finance for CRE."
+ },
+ {
+ "cat": "escrow",
+ "name": "Commerce Escrow (a Wilshire Quinn company)",
+ "website": "https://www.comescrow.com",
+ "hq": "Los Angeles, CA",
+ "markets": "socal",
+ "desc": "Independent commercial escrow & holding services."
+ },
+ {
+ "cat": "developers",
+ "name": "Rising Realty Partners",
+ "website": "https://www.risingrealty.com",
+ "hq": "Los Angeles, CA",
+ "markets": "socal",
+ "desc": "Los Angeles-based commercial developer & operator."
+ }
]
}
diff --git a/public/services.html b/public/services.html
index 56436d28..d69aa7d8 100644
--- a/public/services.html
+++ b/public/services.html
@@ -80,6 +80,7 @@
.st{display:inline-block;font-size:10px;font-weight:800;letter-spacing:.05em;text-transform:uppercase;padding:2px 8px;border-radius:20px;background:var(--wash);color:#4a4f55}
.st.ok{background:rgba(15,138,77,.12);color:var(--up)}
.st.feat{background:#fbf1d8;color:#8a6d1a;border:1px solid #ecdca4}
+ .locs{display:inline-flex;align-items:center;gap:4px;font-size:10.5px;font-weight:800;letter-spacing:.02em;padding:2px 8px;border-radius:20px;background:#eaf2ff;color:#1857b8;border:1px solid #cadcf7}
.card.feat{border-color:#ecdca4;background:linear-gradient(180deg,#fffdf7,#fff)}
.fhint{color:#8a6d1a;font-size:11px;font-weight:700;margin:6px 2px 0}
.spec{font-size:12px;color:var(--ink);font-weight:600}
@@ -138,6 +139,7 @@
<option value="San Diego">San Diego</option>
<option value="Inland Empire">Inland Empire</option>
<option value="Ventura">Ventura</option>
+ <option value="Arizona">Arizona</option>
</select>
</div>
</div>
@@ -158,6 +160,7 @@
<div class="ctls">
<label class="ctl">Sort
<select id="sort" aria-label="Sort providers">
+ <option value="locations">Most locations</option>
<option value="name">Name A–Z</option>
<option value="city">City A–Z</option>
</select>
@@ -188,10 +191,12 @@ const MARKETS={
'San Diego':{short:'San Diego',long:'San Diego County'},
'Inland Empire':{short:'the Inland Empire',long:'the Inland Empire'},
'Ventura':{short:'Ventura',long:'Ventura County'},
+ 'Arizona':{short:'Arizona',long:'Arizona'},
};
let cat='construction', q='', city='', offset=0, total=0, seq=0;
let market=(localStorage.getItem('rentv.services.market')||'Greater LA'); if(!MARKETS[market])market='Greater LA';
-let sort=(localStorage.getItem('rentv.services.sort')||'name');
+let sort=(localStorage.getItem('rentv.services.sort')||'locations');
+if(!['locations','name','city'].includes(sort))sort='locations';
let dens=String(Math.max(2,Math.min(4,parseInt(localStorage.getItem('rentv.services.dens'),10)||3)));
let FACETS={cats:[],cities:[]};
@@ -199,7 +204,7 @@ let FACETS={cats:[],cities:[]};
const p=new URLSearchParams(location.search);
if(p.get('cat'))cat=p.get('cat'); if(p.get('q'))q=p.get('q'); if(p.get('city'))city=p.get('city');
if(MARKETS[p.get('market')])market=p.get('market');
-if(['name','city'].includes(p.get('sort')))sort=p.get('sort');
+if(['locations','name','city'].includes(p.get('sort')))sort=p.get('sort');
$('q').value=q; $('sort').value=sort; $('dens').value=dens; $('market').value=market;
function applyDens(){ $('rows').style.setProperty('--cols',dens); }
applyDens();
@@ -216,7 +221,7 @@ function hero(){
function sync(){
const u=new URLSearchParams(); u.set('cat',cat);
if(market!=='Greater LA')u.set('market',market);
- if(q)u.set('q',q); if(city)u.set('city',city); if(sort!=='name')u.set('sort',sort);
+ if(q)u.set('q',q); if(city)u.set('city',city); if(sort!=='locations')u.set('sort',sort);
history.replaceState(null,'','/services?'+u.toString());
}
@@ -261,7 +266,7 @@ function card(r){
if(r.source_url) acts.push(`<a class="act" href="${esc(r.source_url)}" target="_blank" rel="noopener noreferrer nofollow">Public license record ↗</a>`);
return `<article class="card">
<div class="nm">${esc(cap(r.name))}</div>
- <div class="rowline"><span class="badge" data-cat="${esc(cat)}" title="Filter this category">${esc(r.role)}</span>${st}</div>
+ <div class="rowline"><span class="badge" data-cat="${esc(cat)}" title="Filter this category">${esc(r.role)}</span>${r.loc_count>1?`<span class="locs" title="${r.loc_count} licensed locations across ${r.city_count} cities">📍 ${r.loc_count} locations</span>`:''}${st}</div>
${spec}
${loc?`<div class="loc">${loc}</div>`:''}
${lic}
diff --git a/server.js b/server.js
index f845482d..851d1e9a 100644
--- a/server.js
+++ b/server.js
@@ -1600,10 +1600,20 @@ const SERVICE_CATS = {
let SERVICE_FEATURED = [];
try { SERVICE_FEATURED = (JSON.parse(fs.readFileSync(path.join(DATA, 'services-featured.json'), 'utf8')).vendors) || []; }
catch (e) { console.warn('[services] no services-featured.json (running without featured seed)'); }
+const SOCAL_METROS = ['Greater LA', 'Orange County', 'San Diego', 'Inland Empire', 'Ventura'];
+// Resolve a featured firm's coverage HONESTLY: "national" = also Arizona; "socal"/"all" = CA metros
+// only (so SoCal-specific law firms never falsely claim Arizona); or an explicit market array.
+function featuredMatchesMarket(v, market) {
+ const m = v.markets;
+ if (Array.isArray(m)) return m.includes(market);
+ if (m === 'national') return true;
+ if (m === 'socal' || m === 'all') return SOCAL_METROS.includes(market);
+ return false;
+}
function featuredFor(catKey, market, q) {
const ql = String(q || '').toLowerCase();
return SERVICE_FEATURED
- .filter(v => v.cat === catKey && (v.markets === 'all' || (Array.isArray(v.markets) && v.markets.includes(market))))
+ .filter(v => v.cat === catKey && featuredMatchesMarket(v, market))
.filter(v => !ql || String(v.name).toLowerCase().includes(ql))
.map(v => ({
id: 'f-' + catKey + '-' + String(v.name).replace(/\W+/g, '').toLowerCase(),
@@ -1612,7 +1622,7 @@ function featuredFor(catKey, market, q) {
phone: null, website: v.website || null,
license_no: null, license_type: null, license_status: 'Featured',
specialty: v.desc || null, source: 'featured', source_label: 'Featured',
- source_url: null, featured: true,
+ source_url: null, featured: true, loc_count: null, city_count: null,
}));
}
const SERVICE_MARKETS = new Set(['Greater LA', 'Orange County', 'San Diego', 'Inland Empire', 'Ventura', 'Arizona', 'SoCal (other)']);
@@ -1660,6 +1670,8 @@ function serviceRow(r) {
license_no: nn(r.license_no), license_type: nn(r.license_type), license_status: nn(r.license_status),
specialty: serviceSpecialty(r.role, r.license_type), source: r.source,
source_label: SOURCE_LABELS[r.source] || r.source, source_url: nn(r.source_url),
+ loc_count: r.loc_count != null ? Number(r.loc_count) : null,
+ city_count: r.city_count != null ? Number(r.city_count) : null,
};
}
app.get('/api/services', async (req, res) => {
@@ -1669,19 +1681,37 @@ app.get('/api/services', async (req, res) => {
const market = SERVICE_MARKETS.has(String(req.query.market)) ? String(req.query.market) : 'Greater LA';
const q = String(req.query.q || '').trim().slice(0, 60);
const city = String(req.query.city || '').trim().slice(0, 40);
- const sort = ['name', 'city'].includes(String(req.query.sort)) ? String(req.query.sort) : 'name';
+ // Default sort = 'locations' (biggest multi-branch firms first — the solicitation targets).
+ const sort = ['locations', 'name', 'city'].includes(String(req.query.sort)) ? String(req.query.sort) : 'locations';
const limit = Math.min(60, Math.max(10, parseInt(req.query.limit, 10) || 24));
const offset = Math.max(0, parseInt(req.query.offset, 10) || 0);
const where = ['within_300mi', 'role = $1', 'market = $2'], params = [cat.role, market];
if (q) { params.push('%' + q + '%'); where.push('(entity_name ILIKE $' + params.length + ' OR city ILIKE $' + params.length + ')'); }
if (city) { params.push(city); where.push('city ILIKE $' + params.length); }
const whereSql = where.join(' AND ');
- const orderSql = sort === 'city' ? 'city ASC NULLS LAST, entity_name ASC' : 'entity_name ASC';
- const totalR = await rePool.query('SELECT count(*)::int n FROM rentv_licensed_targets WHERE ' + whereSql, params);
- const rowsR = await rePool.query(
- 'SELECT id, role, entity_name, license_no, license_type, license_status, city, county, zip, market, phone, website, source, source_url ' +
- 'FROM rentv_licensed_targets WHERE ' + whereSql + ' ORDER BY ' + orderSql +
- ' LIMIT ' + limit + ' OFFSET ' + offset, params);
+ // Normalized-name key → count branch licenses per firm (multi-location signal, no external data).
+ const NKEY = "regexp_replace(lower(coalesce(entity_name,'')),'[^a-z0-9]+','','g')";
+ const COLS = 'id, role, entity_name, license_no, license_type, license_status, city, county, zip, market, phone, website, source, source_url';
+ // Per-firm branch counts via GROUP BY (count(DISTINCT ...) OVER () is not allowed in PG), joined back.
+ const CTE =
+ 'WITH b AS (SELECT ' + COLS + ', ' + NKEY + ' AS nkey FROM rentv_licensed_targets WHERE ' + whereSql + '), ' +
+ 'stats AS (SELECT nkey, count(*)::int AS loc_count, count(DISTINCT city)::int AS city_count FROM b GROUP BY nkey), ' +
+ 'agg AS (SELECT b.*, s.loc_count, s.city_count FROM b JOIN stats s USING (nkey)) ';
+ let totalR, rowsR;
+ if (sort === 'locations') {
+ // One representative card per firm, biggest (most branch licenses) first — the prospect list.
+ totalR = await rePool.query(CTE + 'SELECT count(DISTINCT nkey)::int n FROM b', params);
+ rowsR = await rePool.query(
+ CTE + 'SELECT * FROM (SELECT DISTINCT ON (nkey) ' + COLS + ', loc_count, city_count FROM agg ' +
+ "ORDER BY nkey, (city ILIKE 'los angeles') DESC NULLS LAST, id) t " +
+ 'ORDER BY loc_count DESC, entity_name ASC LIMIT ' + limit + ' OFFSET ' + offset, params);
+ } else {
+ const orderSql = sort === 'city' ? 'city ASC NULLS LAST, entity_name ASC' : 'entity_name ASC';
+ totalR = await rePool.query('SELECT count(*)::int n FROM rentv_licensed_targets WHERE ' + whereSql, params);
+ rowsR = await rePool.query(
+ CTE + 'SELECT ' + COLS + ', loc_count, city_count FROM agg ORDER BY ' + orderSql +
+ ' LIMIT ' + limit + ' OFFSET ' + offset, params);
+ }
// Curated featured firms are PINNED to page 1 (offset 0) and only when no city filter is
// applied (they serve the whole metro, not one city). They guarantee >=1 vendor per category.
const featured = (offset === 0 && !city) ? featuredFor(catKey, market, q) : [];
← b2b4b8c8 harden(llm-extract): Cody gate — anchor num() at the LEADING
·
back to Rentv
·
feat(services): admin-only 'Solicit' → pipes firm into /soci d3fe7c22 →