`schema.sql`

42 lines
-- RENTV licensed-professional targeting dataset — SoCal + Arizona, commercial-relevant.
-- Source-agnostic staging table. Feeds the /social Growth console's role facets
-- (title/escrow/contractors/lenders/attorneys), the same way the CA-DRE registry
-- already backs Brokers/Owners.
--
-- HONESTY: every row is a PUBLIC government-license record — targeting data, NOT a
-- send list. No email/phone unless the source itself provides it. Never imply contact
-- data we don't have. Mirrors the /summit-leads provenance caveat.
--
-- APPLYING THIS to canonical Kamatera dw_unified is a gated action — see
-- ~/.claude/yolo-queue/pending-approval/rentv-licensed-targets-scrape.md

CREATE TABLE IF NOT EXISTS rentv_licensed_targets (
  id              bigserial PRIMARY KEY,
  source          text NOT NULL,        -- cslb | az_roc | dfpi_escrow | dfpi_cfl | doi_title | nmls | ca_state_bar | az_state_bar
  role            text NOT NULL,        -- Contractor | Title | Escrow | Lender | Attorney
  entity_name     text NOT NULL,        -- business or person name (as licensed)
  contact_name    text,                 -- person, when the license is personal
  license_no      text,
  license_type    text,                 -- classification / license class (e.g. CSLB "B")
  license_status  text,
  address         text,
  city            text,
  county          text,
  state           text DEFAULT 'CA',
  zip             text,
  phone           text,                 -- only if the source provides it
  website         text,                 -- only if the source provides it
  market          text,                 -- resolved RENTV market: Greater LA | Orange County | Inland Empire | San Diego | Ventura | Arizona
  commercial_flag boolean,              -- passed the commercial-relevant filter
  within_300mi    boolean,              -- passed the geo radius (LA core + Scottsdale)
  source_url      text,
  raw             jsonb,                -- full original source record (provenance)
  scraped_at      timestamptz DEFAULT now(),
  UNIQUE (source, license_no)
);

CREATE INDEX IF NOT EXISTS idx_rlt_role   ON rentv_licensed_targets (role);
CREATE INDEX IF NOT EXISTS idx_rlt_market ON rentv_licensed_targets (market);
CREATE INDEX IF NOT EXISTS idx_rlt_city   ON rentv_licensed_targets (city);
CREATE INDEX IF NOT EXISTS idx_rlt_comm   ON rentv_licensed_targets (commercial_flag, within_300mi);