broker listings in their own dbase: new broker_direct_listing table (migration 021); /api/agent-profile reads direct listings (own + firm) from it, main graph stays crexi-originals only (Steve)

cff722f0bb872009827987d58163d2eeb8832cf4 · 2026-08-19 10:53:52 -0700 · Steve Abrams

Files touched

A migrations/021-broker-direct-listing.sql
M scripts/serve.js

Diff

commit cff722f0bb872009827987d58163d2eeb8832cf4
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 19 10:53:52 2026 -0700

    broker listings in their own dbase: new broker_direct_listing table (migration 021); /api/agent-profile reads direct listings (own + firm) from it, main graph stays crexi-originals only (Steve)
---
 migrations/021-broker-direct-listing.sql | 16 ++++++++++++++++
 scripts/serve.js                         |  9 +++++++--
 2 files changed, 23 insertions(+), 2 deletions(-)

diff --git a/migrations/021-broker-direct-listing.sql b/migrations/021-broker-direct-listing.sql
new file mode 100644
index 0000000..c8b56a2
--- /dev/null
+++ b/migrations/021-broker-direct-listing.sql
@@ -0,0 +1,16 @@
+-- broker_direct_listing — the dedicated store for OUR-OWN directly-scraped broker listings
+-- (Steve 2026-08-19: "keep broker listings we find directly in its own dbase"). Kept separate from
+-- the crexi-originated `listing` graph so it's always clear which listings are ours-direct.
+CREATE TABLE IF NOT EXISTS broker_direct_listing (
+  id bigserial PRIMARY KEY,
+  broker_id text, agent_name text, firm_name text,
+  address text NOT NULL, city text, zip text,
+  price bigint, type text, cap_rate numeric, units int,
+  role text,          -- agent | firm-listing
+  source text,        -- broker-site | broker-site-oc | crexi | loopnet
+  detail_url text,
+  scraped_at timestamptz DEFAULT now()
+);
+CREATE UNIQUE INDEX IF NOT EXISTS bdl_addr_broker ON broker_direct_listing (lower(address), coalesce(broker_id,firm_name));
+CREATE INDEX IF NOT EXISTS bdl_broker ON broker_direct_listing (broker_id);
+CREATE INDEX IF NOT EXISTS bdl_firm ON broker_direct_listing (lower(firm_name));
diff --git a/scripts/serve.js b/scripts/serve.js
index 6334ff0..dfdd03e 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -428,7 +428,12 @@ app.get('/api/agent-profile', async (req, res) => {
          FROM broker_condo bc JOIN condo c ON c.id = bc.condo_id
         WHERE bc.broker_id = $1 ORDER BY c.price DESC NULLS LAST`, [b.id]).catch(() => ({ rows: [] }))).rows;
     const overlayCondos = await fetchCondoCards(agentOverlayCondos(b.name).ids);
-    const listings = mergeListings(commercial, condoEdge, overlayCondos);
+    // this agent's OWN directly-scraped listings from the dedicated store (broker_direct_listing)
+    const directOwn = (await brokerdb.pool.query(
+      `SELECT address, city, zip, type, price, units, cap_rate
+         FROM broker_direct_listing WHERE broker_id = $1 AND (role='agent' OR source='crexi')
+        ORDER BY price DESC NULLS LAST`, [b.id]).catch(() => ({ rows: [] }))).rows;
+    const listings = mergeListings(commercial, condoEdge, overlayCondos, directOwn);
 
     // Past/closed (sold) track record + firm-site inventory (labeled "Firm listings", NOT this agent's book).
     const closed = (await brokerdb.pool.query(
@@ -436,7 +441,7 @@ app.get('/api/agent-profile', async (req, res) => {
          FROM broker_closed_listing WHERE broker_id = $1 ORDER BY sold_date DESC NULLS LAST`, [b.id]).catch(() => ({ rows: [] }))).rows;
     const firmListings = b.firm ? (await brokerdb.pool.query(
       `SELECT address, city, zip, type, price, units, cap_rate
-         FROM listing WHERE source='broker-site-oc' AND lower(firm_name)=lower($1) ORDER BY price DESC NULLS LAST`, [b.firm]).catch(() => ({ rows: [] }))).rows : [];
+         FROM broker_direct_listing WHERE source='broker-site-oc' AND lower(firm_name)=lower($1) ORDER BY price DESC NULLS LAST`, [b.firm]).catch(() => ({ rows: [] }))).rows : [];
 
     res.json({
       found: true, id: b.id, name: b.name, firm: b.firm, phone: b.phone, email: b.email,

← d523da0 auto-data-snapshot: 2026-08-19T10:40:44 (2 data files) — dat · back to Commercialrealestate · repoint all 3 direct scrapers (crexi/firm-site/personal-site 1768107 →