`scripts/schema.sql`

77 lines
-- CRUnifiedDB — unified commercial-real-estate database for sublease.agentabrams.com
-- Mirrors the dw_unified pattern: per-firm crawler agents write here; the app reads here.

CREATE TABLE IF NOT EXISTS sponsors (
  id            SERIAL PRIMARY KEY,
  slug          TEXT UNIQUE NOT NULL,          -- loopnet, cbre, naicapital ...
  name          TEXT NOT NULL,
  kind          TEXT NOT NULL,                 -- marketplace | brokerage | operator | lender | data
  role          TEXT NOT NULL DEFAULT 'broker',-- broker | financing_partner
  tagline       TEXT,
  website       TEXT,
  listings_url  TEXT,                          -- where the crawler starts
  logo_path     TEXT,                          -- /assets/logos/<slug>.jpg
  crawl_enabled BOOLEAN NOT NULL DEFAULT TRUE,
  tos_risk      TEXT NOT NULL DEFAULT 'low',   -- low | medium | high (CoStar/LoopNet = high)
  notes         TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS listings (
  id            SERIAL PRIMARY KEY,
  sponsor_id    INTEGER NOT NULL REFERENCES sponsors(id) ON DELETE CASCADE,
  external_id   TEXT,                          -- the firm's own listing id
  title         TEXT,
  address       TEXT,
  city          TEXT,
  state         TEXT DEFAULT 'CA',
  zip           TEXT,
  lat           DOUBLE PRECISION,
  lng           DOUBLE PRECISION,
  space_type    TEXT,                          -- office | retail | industrial | flex | medical | land
  is_sublease   BOOLEAN NOT NULL DEFAULT TRUE,
  size_sf       INTEGER,                       -- rentable square feet
  price_amount  NUMERIC,                       -- rate value
  price_unit    TEXT,                          -- $/SF/yr, $/SF/mo, $/mo, negotiable
  term          TEXT,                          -- remaining sublease term
  available_on  TEXT,
  source_url    TEXT,
  image_url     TEXT,
  description   TEXT,
  status        TEXT NOT NULL DEFAULT 'active',-- active | pending | off_market
  raw           JSONB,
  first_seen    TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen     TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (sponsor_id, external_id)
);
CREATE INDEX IF NOT EXISTS idx_listings_sponsor ON listings(sponsor_id);
CREATE INDEX IF NOT EXISTS idx_listings_geo ON listings(lat, lng);
CREATE INDEX IF NOT EXISTS idx_listings_type ON listings(space_type);

-- Individual agents/reps within a firm (populated by crawlers where available)
CREATE TABLE IF NOT EXISTS agents (
  id          SERIAL PRIMARY KEY,
  sponsor_id  INTEGER NOT NULL REFERENCES sponsors(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  title       TEXT,
  email       TEXT,
  phone       TEXT,
  profile_url TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (sponsor_id, name)
);

-- One row per crawler execution — the audit/cost trail
CREATE TABLE IF NOT EXISTS crawl_runs (
  id             SERIAL PRIMARY KEY,
  sponsor_id     INTEGER REFERENCES sponsors(id) ON DELETE CASCADE,
  started_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  finished_at    TIMESTAMPTZ,
  status         TEXT NOT NULL DEFAULT 'running', -- running | ok | error | blocked
  listings_found INTEGER DEFAULT 0,
  listings_new   INTEGER DEFAULT 0,
  cost_usd       NUMERIC DEFAULT 0,
  notes          TEXT
);