← back to Commercialrealestate

scripts/db/pitch-schema.sql

18 lines

-- broker-closed-listings schema — publicly-recorded closed/sold listings tied to a broker.
-- Scope: LEGITIMATE B2B contact prep only. Broker = business contact. NO personal/family data,
-- NO background-dossier services. Closed listings = publicly-recorded sales only, sourced.

-- Publicly-recorded CLOSED/sold listings tied to a broker (legit public sources only, sourced).
CREATE TABLE IF NOT EXISTS broker_closed_listing (
  id          serial PRIMARY KEY,
  broker_id   integer REFERENCES broker(id) ON DELETE CASCADE,
  address     text,
  city        text,
  sold_price  bigint,
  sold_date   date,
  type        text,
  source      text,                            -- provenance URL (public record)
  created_at  timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_closed_broker ON broker_closed_listing(broker_id);