`scripts/db/broker-enrich-schema.sql`

38 lines
-- broker-enrich-schema.sql — additive columns + provenance + book tables for Job B (broker enrichment).
-- CCPA posture: business-contact fields only (name/firm/business phone/business email/public website/
-- LinkedIn). Source URL recorded per filled field for provenance. NO consumer data. NO send.

ALTER TABLE broker ADD COLUMN IF NOT EXISTS website     text;
ALTER TABLE broker ADD COLUMN IF NOT EXISTS linkedin    text;
ALTER TABLE broker ADD COLUMN IF NOT EXISTS specialties text;
ALTER TABLE broker ADD COLUMN IF NOT EXISTS office_addr text;   -- brokerage office address (business)
ALTER TABLE broker ADD COLUMN IF NOT EXISTS enriched_at timestamptz;

-- Per-field provenance: which source URL gave us each enriched field (audit trail for the CCPA posture).
CREATE TABLE IF NOT EXISTS broker_field_source (
  broker_id  integer NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
  field      text    NOT NULL,            -- phone | email | website | linkedin | specialties | office_addr
  value      text,
  source_url text,                        -- where we read it
  tier       text,                        -- tier1-listing | tier2-firmsite | tier3-search | tier4-browserbase
  found_at   timestamptz DEFAULT now(),
  PRIMARY KEY (broker_id, field)
);

-- Each broker's FULL listing book "anywhere" (beyond our LA set) when a public profile lists it.
CREATE TABLE IF NOT EXISTS broker_other_listing (
  broker_id   integer NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
  ext_id      text    NOT NULL,           -- external listing id / slug
  title       text,
  address     text,
  city        text,
  state       text,
  price       bigint,
  asset_type  text,
  url         text,
  source      text,                       -- crexi | firm-site | ...
  found_at    timestamptz DEFAULT now(),
  PRIMARY KEY (broker_id, ext_id)
);
CREATE INDEX IF NOT EXISTS idx_other_broker ON broker_other_listing(broker_id);