← back to Nationalrealestate
firm directory: capture + surface street address via Places formattedAddress (lever #3, TK-10669)
771e7f828421a2c6e8e44204e5b51926264ba9cd · 2026-08-18 09:03:36 -0700 · Steve Abrams
Files touched
A db/migrations/021_firm_street_address.sqlM public/brokers.htmlM src/enrich/firm_website_resolve.tsM src/server/index.ts
Diff
commit 771e7f828421a2c6e8e44204e5b51926264ba9cd
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Aug 18 09:03:36 2026 -0700
firm directory: capture + surface street address via Places formattedAddress (lever #3, TK-10669)
---
db/migrations/021_firm_street_address.sql | 9 +++++++++
public/brokers.html | 3 ++-
src/enrich/firm_website_resolve.ts | 23 +++++++++++++----------
src/server/index.ts | 2 +-
4 files changed, 25 insertions(+), 12 deletions(-)
diff --git a/db/migrations/021_firm_street_address.sql b/db/migrations/021_firm_street_address.sql
new file mode 100644
index 0000000..1899f31
--- /dev/null
+++ b/db/migrations/021_firm_street_address.sql
@@ -0,0 +1,9 @@
+-- TK-10669: firm street address for a full DRE-style contact card.
+-- The DRE registry ships name + license + mailing CITY only (no street, no phone).
+-- Google Places returns formattedAddress in the SAME searchText call that yields
+-- website + phone, so we capture the street address at $0 extra on confident matches.
+-- No BEGIN/COMMIT here — migrate.ts wraps each file in a transaction.
+
+ALTER TABLE firm
+ ADD COLUMN IF NOT EXISTS street_address TEXT, -- Places formattedAddress (confident matches only)
+ ADD COLUMN IF NOT EXISTS address_source TEXT; -- provenance: 'google_places_resolve' | (future) 'dre_lookup'
diff --git a/public/brokers.html b/public/brokers.html
index 30aaae2..d61c90a 100644
--- a/public/brokers.html
+++ b/public/brokers.html
@@ -234,7 +234,8 @@ function firmDetailHtml(d) {
// TK-10669 primary-contact line: the firm's own registry-grade phone + website
// (Google Places resolve). This is the DRE-style contact the row was missing —
// distinct from the crawl-derived firm_contacts shown below.
- const loc = [f.hq_city, f.hq_state].filter(Boolean).join(', ');
+ // Prefer the full Places street address; fall back to the registry city/state.
+ const loc = f.street_address || [f.hq_city, f.hq_state].filter(Boolean).join(', ');
const primaryBits = [];
if (f.phone) primaryBits.push(`<a href="tel:${esc(f.phone)}">📞 ${esc(f.phone)}</a>`);
if (f.website) primaryBits.push(`<a href="${esc(f.website)}" target="_blank" rel="noopener noreferrer">🏢 ${esc(String(f.website).replace(/^https?:\/\//, ''))} ↗</a>`);
diff --git a/src/enrich/firm_website_resolve.ts b/src/enrich/firm_website_resolve.ts
index 786c42e..8ec3d0b 100644
--- a/src/enrich/firm_website_resolve.ts
+++ b/src/enrich/firm_website_resolve.ts
@@ -34,7 +34,7 @@ const BATCH = Number(process.env.USRE_RESOLVE_BATCH || 100);
const RATE_PER_CALL = 0.032; // Text-Search Pro list price, ledger-only (free-tier = $0)
// TK-10669: phone added to the mask — one call now yields website + phone, closing
// the firm.phone gap (0/215K populated) for the DRE-style contact card at $0 extra.
-const FIELD_MASK = 'places.id,places.displayName,places.websiteUri,places.nationalPhoneNumber';
+const FIELD_MASK = 'places.id,places.displayName,places.websiteUri,places.nationalPhoneNumber,places.formattedAddress';
// Optional asset-class scope (--asset=commercial|residential or USRE_RESOLVE_ASSET).
// RENTV is CRE-only, so the first sweep targets the ~4.1K commercial firms.
const ASSET_RAW = (process.argv.find(a => a.startsWith('--asset='))?.split('=')[1] || process.env.USRE_RESOLVE_ASSET || '').toLowerCase();
@@ -87,7 +87,7 @@ async function bumpQuota(n: number): Promise<void> {
[ym(), n]);
}
-async function searchText(q: string): Promise<Array<{ name: string; website?: string; phone?: string }>> {
+async function searchText(q: string): Promise<Array<{ name: string; website?: string; phone?: string; address?: string }>> {
const res = await fetch(API, {
method: 'POST',
headers: { 'Content-Type': 'application/json', 'X-Goog-Api-Key': KEY, 'X-Goog-FieldMask': FIELD_MASK },
@@ -95,7 +95,7 @@ async function searchText(q: string): Promise<Array<{ name: string; website?: st
});
if (!res.ok) throw new Error(`places searchText ${res.status}: ${(await res.text()).slice(0, 160)}`);
const j: any = await res.json();
- return (j.places || []).map((p: any) => ({ name: p.displayName?.text || '', website: p.websiteUri, phone: p.nationalPhoneNumber }));
+ return (j.places || []).map((p: any) => ({ name: p.displayName?.text || '', website: p.websiteUri, phone: p.nationalPhoneNumber, address: p.formattedAddress }));
}
/**
@@ -105,7 +105,7 @@ async function searchText(q: string): Promise<Array<{ name: string; website?: st
* firm, so a non-echoing host is recorded as a LEAD (firm_site low_confidence)
* WITHOUT overwriting firm.website. ~30% of hits are loose (audited 2026-07-30).
*/
-function pickWebsite(hits: Array<{ name: string; website?: string; phone?: string }>, firmName: string): { url: string; confident: boolean; phone?: string } | null {
+function pickWebsite(hits: Array<{ name: string; website?: string; phone?: string; address?: string }>, firmName: string): { url: string; confident: boolean; phone?: string; address?: string } | null {
const cands = hits
.filter(h => !!h.website)
.filter(h => { const host = hostOf(h.website!); return host && !BLOCK.has(host); });
@@ -114,9 +114,9 @@ function pickWebsite(hits: Array<{ name: string; website?: string; phone?: strin
const winner = named || cands[0];
const h = hostOf(winner.website!);
if (!h) return null;
- // Phone only rides a name-echoing (confident) hit — never staple a loose match's
- // phone onto the firm, same rule as firm.website.
- return { url: 'https://' + h, confident: !!named, phone: named ? winner.phone : undefined };
+ // Phone + address only ride a name-echoing (confident) hit — never staple a loose
+ // match's contact details onto the firm, same rule as firm.website.
+ return { url: 'https://' + h, confident: !!named, phone: named ? winner.phone : undefined, address: named ? winner.address : undefined };
}
interface FirmRow { id: number; name: string; hq_city: string | null; license_state: string | null }
@@ -172,9 +172,12 @@ async function main() {
// COALESCE(NULLIF(...)) so we fill only empty fields — never clobber an
// already-known website/phone with a fresh Places guess.
await query(
- `UPDATE firm SET website = COALESCE(NULLIF(website,''), $2),
- phone = COALESCE(NULLIF(phone,''), $3)
- WHERE id = $1`, [f.id, pick.url, pick.phone || null]);
+ `UPDATE firm SET website = COALESCE(NULLIF(website,''), $2),
+ phone = COALESCE(NULLIF(phone,''), $3),
+ street_address = COALESCE(NULLIF(street_address,''), $4),
+ address_source = CASE WHEN NULLIF(street_address,'') IS NULL AND $4 IS NOT NULL
+ THEN 'google_places_resolve' ELSE address_source END
+ WHERE id = $1`, [f.id, pick.url, pick.phone || null, pick.address || null]);
if (pick.phone) phones++;
await query(
`INSERT INTO firm_site (firm_id, url, discovery_method) VALUES ($1,$2,'google_places_resolve')
diff --git a/src/server/index.ts b/src/server/index.ts
index d23169b..910a782 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -489,7 +489,7 @@ app.get('/api/firm/:id', async (req, res) => {
const id = Number(req.params.id);
if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'bad firm id' });
const [firm, site, contacts, brokers] = await Promise.all([
- query(`SELECT id, name, website, phone, hq_city, hq_state, license_no, license_state,
+ query(`SELECT id, name, website, phone, street_address, hq_city, hq_state, license_no, license_state,
source, agent_count, asset_class, created_at
FROM firm WHERE id = $1`, [id]),
query(`SELECT url, discovery_method, discovered_at, http_status, title, has_idx_listings,
← d116917 firm directory: capture phone via Places resolve (commercial
·
back to Nationalrealestate
·
firm API: surface phone/website/street_address in /api/broke d1ab982 →