[object Object]

← back to Nationalrealestate

usre: resolved broker phone + firm-fallback tier + email/address on the desk (TK-10687)

7d880947ff0420fa5d750cb1b9744168eeb88d90 · 2026-08-18 12:55:52 -0700 · Steve Abrams

Every broker must be callable (Steve). /api/brokers now returns a single resolved
phone = broker.phone || firm.phone || firm_contacts.phone, plus phone_tier
(direct|firm) so the UI can badge the firm fallback; also resolved email + address.
broker-desk.js renders a contact row (tel:/mailto: + tier badge + 'phone pending'
when none). Migration 022 adds firm contact-enrichment columns (phone_source, email,
email_source, contact_enriched_at, contact_attempts, phone_status) + a missing-phone
work-queue index. Verified live: a direct-line-less broker now shows the firm phone
tagged 'firm'. Local only; Kamatera migration + deploy stay gated.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

Diff

commit 7d880947ff0420fa5d750cb1b9744168eeb88d90
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Aug 18 12:55:52 2026 -0700

    usre: resolved broker phone + firm-fallback tier + email/address on the desk (TK-10687)
    
    Every broker must be callable (Steve). /api/brokers now returns a single resolved
    phone = broker.phone || firm.phone || firm_contacts.phone, plus phone_tier
    (direct|firm) so the UI can badge the firm fallback; also resolved email + address.
    broker-desk.js renders a contact row (tel:/mailto: + tier badge + 'phone pending'
    when none). Migration 022 adds firm contact-enrichment columns (phone_source, email,
    email_source, contact_enriched_at, contact_attempts, phone_status) + a missing-phone
    work-queue index. Verified live: a direct-line-less broker now shows the firm phone
    tagged 'firm'. Local only; Kamatera migration + deploy stay gated.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 db/migrations/022_firm_contact_enrich.sql | 22 ++++++++++++++++++++++
 public/broker-desk.css                    |  6 ++++++
 public/broker-desk.js                     |  5 ++++-
 src/server/index.ts                       | 10 ++++++++++
 4 files changed, 42 insertions(+), 1 deletion(-)

diff --git a/db/migrations/022_firm_contact_enrich.sql b/db/migrations/022_firm_contact_enrich.sql
new file mode 100644
index 0000000..1c580d7
--- /dev/null
+++ b/db/migrations/022_firm_contact_enrich.sql
@@ -0,0 +1,22 @@
+-- TK-10687: firm contact enrichment — every firm/business must have a phone (Steve 2026-08-18),
+-- and every active broker inherits a callable number via the firm fallback. The DRE registry ships
+-- name + license + mailing CITY only (no street, no phone, no email), so phone/email/address are
+-- sourced externally at $0 (firm-site scrape + openclaw + local models + free web). This migration
+-- adds the canonical firm-level contact columns + enrichment provenance/status so the enrichment
+-- driver is idempotent and resumable, and so a "firms still missing a phone" work-queue is queryable.
+-- firm.phone / firm.website / firm.street_address / firm.address_source already exist (migrations
+-- 001 + 021); firm_contacts(kind email/phone/contact_url) is the multi-value backing store (002).
+-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
+
+ALTER TABLE firm
+  ADD COLUMN IF NOT EXISTS phone_source        TEXT,          -- 'firm_site' | 'openclaw' | 'web_search' | 'dre_backfill' | 'redfin' | 'google_places_resolve'
+  ADD COLUMN IF NOT EXISTS email               TEXT,          -- canonical firm email (promoted from firm_contacts for fast API)
+  ADD COLUMN IF NOT EXISTS email_source        TEXT,
+  ADD COLUMN IF NOT EXISTS contact_enriched_at TIMESTAMPTZ,   -- last time the enrichment driver touched this firm
+  ADD COLUMN IF NOT EXISTS contact_attempts    INTEGER NOT NULL DEFAULT 0,  -- attempts so far (backoff / triage)
+  ADD COLUMN IF NOT EXISTS phone_status        TEXT;          -- 'found' | 'no_site' | 'unreachable' | 'pending' (NULL = never attempted)
+
+-- Work-queue index: "CA firms with an active broker still missing a phone" is the driver's target set.
+CREATE INDEX IF NOT EXISTS idx_firm_phone_missing
+  ON firm (license_state)
+  WHERE coalesce(nullif(phone,''), '') = '';
diff --git a/public/broker-desk.css b/public/broker-desk.css
index 3cf5344..5a3bce3 100644
--- a/public/broker-desk.css
+++ b/public/broker-desk.css
@@ -21,6 +21,12 @@ input,select{background:var(--panel);color:var(--fg);border:1px solid var(--line
 .card.firm{cursor:pointer;transition:border-color .12s;} .card.firm:hover{border-color:var(--gold);}
 .card .n{font-size:15px;font-weight:600;line-height:1.25;}
 .card .m{font-size:12px;color:var(--muted);margin-top:6px;display:flex;flex-wrap:wrap;gap:10px;}
+/* TK-10687 contact row — every broker is callable (direct line or firm fallback). */
+.card .ct{font-size:12px;margin-top:7px;display:flex;flex-wrap:wrap;gap:10px;align-items:center;}
+.card .ct a{color:var(--gold);text-decoration:none;} .card .ct a:hover{text-decoration:underline;}
+.card .ct .tier{font-size:9px;letter-spacing:.05em;text-transform:uppercase;color:var(--muted);border:1px solid var(--line);border-radius:4px;padding:0 4px;}
+.card .ct .pend{color:#8a93a3;font-style:italic;}
+.card .ct .addr{color:var(--muted);}
 .agents{color:var(--gold);font-weight:700;}
 .lt{font-size:10px;letter-spacing:.05em;text-transform:uppercase;color:var(--gold);border:1px solid var(--line);border-radius:5px;padding:1px 6px;}
 .roster{margin-top:9px;padding-top:9px;border-top:1px solid var(--line);font-size:12px;color:#c3ccd8;display:none;}
diff --git a/public/broker-desk.js b/public/broker-desk.js
index 2edd4fb..84a56b5 100644
--- a/public/broker-desk.js
+++ b/public/broker-desk.js
@@ -97,7 +97,10 @@
     setCount(rows.length, d.total||0);
     $('#grid').innerHTML = rows.map(b=>`<div class="card">
       <div class="n">${esc(b.name)||'—'}</div>
-      <div class="m"><span class="lt">${esc(b.license_type)}</span><span>${esc(b.firm_name)||'—'}</span><span>${esc(b.city)}</span>${b.license_no?`<span>DRE ${esc(b.license_no)}</span>`:''}</div></div>`).join('') || '<div style="color:var(--muted);padding:20px">no '+NOUN+' match</div>';
+      <div class="m"><span class="lt">${esc(b.license_type)}</span><span>${esc(b.firm_name)||'—'}</span><span>${esc(b.city)}</span>${b.license_no?`<span>DRE ${esc(b.license_no)}</span>`:''}</div>
+      <div class="ct">${b.phone
+          ? `<a href="tel:${esc(b.phone)}">📞 ${esc(b.phone)}</a>${b.phone_tier==='firm'?`<span class="tier" title="firm main line (broker direct line unknown)">firm</span>`:''}`
+          : `<span class="pend" title="phone enrichment pending">☎ phone pending</span>`}${b.email?` <a href="mailto:${esc(b.email)}">✉ ${esc(b.email)}</a>`:''}${b.address?` <span class="addr">📍 ${esc(b.address)}</span>`:''}</div></div>`).join('') || '<div style="color:var(--muted);padding:20px">no '+NOUN+' match</div>';
   }
   async function load(){ $('#loading').style.display='block'; S.tab==='firms'?await loadFirms():await loadBrokers(); $('#loading').style.display='none'; }
 
diff --git a/src/server/index.ts b/src/server/index.ts
index 0b450dc..05ab68b 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -401,6 +401,16 @@ app.get('/api/brokers', async (req, res) => {
               -- fall back to the firm's crawled contact number.
               COALESCE(NULLIF(f.phone,''), (SELECT value FROM firm_contacts WHERE firm_id = f.id AND kind = 'phone' ORDER BY id LIMIT 1)) AS firm_phone,
               (SELECT value FROM firm_contacts WHERE firm_id = f.id AND kind = 'email' ORDER BY id LIMIT 1) AS firm_email,
+              -- TK-10687 "every broker must have a phone": single resolved contact for the desk card.
+              -- Broker's direct line first, then the firm's registry phone, then its crawled contact
+              -- number. phone_tier tells the UI whether it's the broker's own line or the firm fallback.
+              COALESCE(NULLIF(b.phone,''), NULLIF(f.phone,''), (SELECT value FROM firm_contacts WHERE firm_id = f.id AND kind = 'phone' ORDER BY id LIMIT 1)) AS phone,
+              CASE WHEN NULLIF(b.phone,'') IS NOT NULL THEN 'direct'
+                   WHEN NULLIF(f.phone,'') IS NOT NULL THEN 'firm'
+                   WHEN (SELECT 1 FROM firm_contacts WHERE firm_id = f.id AND kind = 'phone' LIMIT 1) IS NOT NULL THEN 'firm'
+                   ELSE NULL END AS phone_tier,
+              COALESCE(NULLIF(b.email,''), NULLIF(f.email,''), (SELECT value FROM firm_contacts WHERE firm_id = f.id AND kind = 'email' ORDER BY id LIMIT 1)) AS email,
+              f.street_address AS address,
               COUNT(*) OVER()::int AS total
          FROM broker b
          LEFT JOIN firm f ON f.id = b.firm_id

← 2492689 chore: v0.20.0 (session close — DRE firm contact card shippe  ·  back to Nationalrealestate  ·  usre: firm-phone-first contact enrichment driver (agents+loc 4cd353f →