`db/migrations/007_sublease.sql`

34 lines
-- Sublease inventory — manual-entry CRE sublease/direct listings.
-- Data source is manual admin entry (all live sublease sources are Cloudflare/ToS bot-walled,
-- so a clean robots-honored crawl is impossible). Ships empty; RENTV's team logs real deals.
CREATE TABLE IF NOT EXISTS sublease (
  id             serial PRIMARY KEY,
  address        text NOT NULL,
  city           text,
  submarket      text,
  floor_suite    text,
  sqft           numeric,
  asking_rate    numeric,                 -- $ per SF
  rate_period    text DEFAULT 'yr',       -- 'yr' | 'mo'
  lease_type     text DEFAULT 'sublease', -- 'sublease' | 'direct'
  term_months    integer,
  available_date date,
  sublandlord    text,                    -- the sitting tenant offering the space
  firm_name      text,                    -- listing brokerage
  firm_id        integer,
  broker_name    text,
  broker_phone   text,
  broker_email   text,
  broker_id      integer,
  listing_url    text,
  source         text DEFAULT 'manual',
  lat            double precision,
  lng            double precision,
  status         text DEFAULT 'active',   -- 'active' | 'leased' | 'withdrawn'
  notes          text,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_sublease_status ON sublease(status);
CREATE INDEX IF NOT EXISTS idx_sublease_city   ON sublease(lower(city));