← back to Commercialrealestate

migrations/021-broker-direct-listing.sql

17 lines

-- 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));