[object Object]

← back to Commercialrealestate

repoint all 3 direct scrapers (crexi/firm-site/personal-site) to write to broker_direct_listing (its own dbase) instead of the main graph

1768107895e4da82cba113e898a3f87757c1d040 · 2026-08-19 10:54:47 -0700 · Steve Abrams

Files touched

Diff

commit 1768107895e4da82cba113e898a3f87757c1d040
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 19 10:54:47 2026 -0700

    repoint all 3 direct scrapers (crexi/firm-site/personal-site) to write to broker_direct_listing (its own dbase) instead of the main graph
---
 scripts/backfill-broker-listings.js | 11 +++++------
 scripts/backfill-openclaw-llm.js    |  8 ++++----
 scripts/scrape-crexi-loopnet.js     | 14 ++++++--------
 3 files changed, 15 insertions(+), 18 deletions(-)

diff --git a/scripts/backfill-broker-listings.js b/scripts/backfill-broker-listings.js
index 2d32600..23d09fd 100644
--- a/scripts/backfill-broker-listings.js
+++ b/scripts/backfill-broker-listings.js
@@ -80,13 +80,12 @@ function extractListings(html) {
     listings.slice(0, 4).forEach(l => console.log(`       $${l.price.toLocaleString()}  ${l.address}`));
     if (APPLY && listings.length && !shared) {  // only attribute UNIQUE-domain (personal-site) listings to the agent
       for (const l of listings) {
-        // insert listing (tagged reversible) + link to broker; dedup by address+broker
+        // personal-site listing -> dedicated direct store, attributed to this agent (unique index dedups)
         const ins = await db.pool.query(
-          `INSERT INTO listing (id, address, price, type, source, created_at)
-           VALUES (gen_random_uuid()::text, $1, $2, 'Commercial', 'broker-site', now())
-           ON CONFLICT DO NOTHING RETURNING id`, [l.address, l.price]).catch(() => ({ rows: [] }));
-        const lid = ins.rows[0]?.id;
-        if (lid) { await db.pool.query(`INSERT INTO broker_listing (broker_id, listing_id, role) VALUES ($1,$2,'agent') ON CONFLICT DO NOTHING`, [b.id, lid]).catch(() => {}); wrote++; }
+          `INSERT INTO broker_direct_listing (broker_id, agent_name, address, price, type, role, source)
+           VALUES ($1, $2, $3, $4, 'Commercial', 'agent', 'broker-site') ON CONFLICT DO NOTHING RETURNING id`,
+          [b.id, b.name, l.address, l.price]).catch(() => ({ rows: [] }));
+        if (ins.rows[0]?.id) wrote++;
       }
     }
   }
diff --git a/scripts/backfill-openclaw-llm.js b/scripts/backfill-openclaw-llm.js
index 7ba8881..88dee7a 100644
--- a/scripts/backfill-openclaw-llm.js
+++ b/scripts/backfill-openclaw-llm.js
@@ -83,12 +83,12 @@ async function scrapeDomain(url) {
     res.listings.slice(0, 3).forEach(l => console.log(`       $${l.price.toLocaleString()}  ${l.address}`));
     if (APPLY && res.listings.length) {
       for (const l of res.listings) {
-        // Attribute at the FIRM level via listing.firm_name (no per-broker link explosion) — the
+        // FIRM-level rows in the dedicated direct-listing store (firm_name, no broker_id) — the
         // agent-profile endpoint joins these by the agent's firm and shows them labeled "Firm listings".
         const ins = await db.pool.query(
-          `INSERT INTO listing (id,address,price,type,firm_name,source,created_at)
-           VALUES (gen_random_uuid()::text,$1,$2,$3,$4,'broker-site-oc',now()) RETURNING id`,
-          [l.address, l.price, l.type, r.firm]).catch(() => ({ rows: [] }));
+          `INSERT INTO broker_direct_listing (firm_name,address,price,type,role,source)
+           VALUES ($1,$2,$3,$4,'firm-listing','broker-site-oc') ON CONFLICT DO NOTHING RETURNING id`,
+          [r.firm, l.address, l.price, l.type]).catch(() => ({ rows: [] }));
         if (ins.rows[0]?.id) wrote++;
       }
     }
diff --git a/scripts/scrape-crexi-loopnet.js b/scripts/scrape-crexi-loopnet.js
index e31e62a..50cf8a3 100644
--- a/scripts/scrape-crexi-loopnet.js
+++ b/scripts/scrape-crexi-loopnet.js
@@ -134,14 +134,12 @@ async function resolveBroker(agent, firm) {
     console.log(`  ✓ $${price.toLocaleString()}  ${addr}  — ${agent} @ ${firm || '?'} ${tag}`);
     if (bid === 'NEW') created++; else if (bid) matched++;
     if (APPLY && bid && bid !== 'NEW') {
-      // dedup by exact address across ALL sources (Crexi returns a stable address string)
-      const dup = (await db.pool.query(`SELECT 1 FROM listing WHERE lower(address)=lower($1) LIMIT 1`, [addr])).rows[0];
-      if (dup) { continue; }
-      const lid = (await db.pool.query(
-        `INSERT INTO listing (id,address,price,type,cap_rate,firm_name,source,created_at)
-         VALUES (gen_random_uuid()::text,$1,$2,$3,$4,$5,'crexi',now()) RETURNING id`,
-        [addr, price, TYPE, c.cap, firm]).catch(() => ({ rows: [] }))).rows[0];
-      if (lid) { await db.pool.query(`INSERT INTO broker_listing (broker_id,listing_id,role) VALUES ($1,$2,'agent') ON CONFLICT DO NOTHING`, [bid, lid.id]).catch(() => {}); wrote++; }
+      // write to the dedicated direct-listing store (unique index dedups by address+broker)
+      const ins = (await db.pool.query(
+        `INSERT INTO broker_direct_listing (broker_id,agent_name,firm_name,address,price,type,cap_rate,role,source,detail_url)
+         VALUES ($1,$2,$3,$4,$5,$6,$7,'agent','crexi',$8) ON CONFLICT DO NOTHING RETURNING id`,
+        [bid, agent, firm, addr, price, TYPE, c.cap, c.url]).catch(() => ({ rows: [] }))).rows[0];
+      if (ins) wrote++;
     }
   }
   console.log(`\n== ${i} detail pages · ${matched} matched existing brokers · ${created} new brokers${APPLY ? ' created' : ' (would create)'} · ${APPLY ? wrote + ' listings written (source=crexi, role=agent, reversible)' : 'DRY-RUN'} ==`);

← cff722f broker listings in their own dbase: new broker_direct_listin  ·  back to Commercialrealestate  ·  CRCP: harden per-broker grind — auto-dismiss JS dialogs + al c189e73 →