`scripts/db/broker-schema.sql`

66 lines
-- broker-schema.sql — the broker/agent relationship graph for the CRE explorer (local Postgres "cre").
-- Nodes: firm, broker, listing.  Edges: broker_listing (who lists what).  Built for mind-mapping
-- connections — "which brokers co-list", "which firm dominates a submarket", "this agent's whole book".

CREATE TABLE IF NOT EXISTS firm (
  id          serial PRIMARY KEY,
  name        text UNIQUE NOT NULL,
  created_at  timestamptz DEFAULT now()
);

CREATE TABLE IF NOT EXISTS broker (
  id          serial PRIMARY KEY,
  name        text NOT NULL,
  firm_id     integer REFERENCES firm(id),
  title       text,
  phone       text,
  email       text,
  crexi_id    text,                      -- vendor broker id when available
  source      text,                      -- where we learned this broker (crexi, sheet, etc.)
  created_at  timestamptz DEFAULT now(),
  UNIQUE (name, firm_id)                 -- a broker is unique within a firm
);

CREATE TABLE IF NOT EXISTS listing (
  id          text PRIMARY KEY,          -- the CRE listing id (e.g. crx2560155)
  address     text,
  city        text,
  zip         text,
  type        text,
  price       bigint,
  cap_rate    numeric,
  units       integer,
  firm_name   text,                      -- listing brokerage (denormalized for convenience)
  source      text,
  created_at  timestamptz DEFAULT now()
);

-- The edge table: a broker is on a listing (lead/co/team). This is the graph that powers the mind-map.
CREATE TABLE IF NOT EXISTS broker_listing (
  broker_id   integer NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
  listing_id  text    NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
  role        text DEFAULT 'broker',     -- lead | co | team
  PRIMARY KEY (broker_id, listing_id)
);

CREATE INDEX IF NOT EXISTS idx_broker_firm     ON broker(firm_id);
CREATE INDEX IF NOT EXISTS idx_listing_city    ON listing(city);
CREATE INDEX IF NOT EXISTS idx_listing_firm    ON listing(firm_name);
CREATE INDEX IF NOT EXISTS idx_bl_listing      ON broker_listing(listing_id);

-- A convenience view: broker with firm name + listing count (node weight for the mind-map).
CREATE OR REPLACE VIEW broker_node AS
  SELECT b.id, b.name, f.name AS firm, b.phone, b.email, b.title,
         count(bl.listing_id) AS listings
    FROM broker b
    LEFT JOIN firm f ON f.id = b.firm_id
    LEFT JOIN broker_listing bl ON bl.broker_id = b.id
   GROUP BY b.id, f.name;

-- Co-listing edges: pairs of brokers who appear on the same listing (the connection graph).
CREATE OR REPLACE VIEW broker_cobroker AS
  SELECT a.broker_id AS a, b.broker_id AS b, count(*) AS shared_listings
    FROM broker_listing a
    JOIN broker_listing b ON a.listing_id = b.listing_id AND a.broker_id < b.broker_id
   GROUP BY a.broker_id, b.broker_id;