`db/001_external_marketing_asset.sql`

59 lines
-- TK-10708  external_marketing_asset + asset_subject_link
-- DRAFT staging schema for the re-flyer-aggregator. NOT applied to prod.
-- (codex-revised) Assets are PROPERTY/LISTING-scoped and may link to 0..n deals,
-- so an asset table + a subject-link table replaces the too-narrow deal_attachment.
-- Only VALIDATED rows are later PROMOTED into usre as the canonical cross-build
-- dataset; CRCP/RENTV consume a view/API, never these scraper/staging tables.
--
-- Link-only posture: source_url + document_url + provenance, NO content_hash and
-- NO local_path unless a Steve-GATED tier-3 download actually happened.

CREATE TABLE IF NOT EXISTS external_marketing_asset (
  id             bigserial PRIMARY KEY,
  asset_type     text NOT NULL
     CHECK (asset_type IN ('offering_memorandum','marketing_flyer','property_spotlight','property_brief','market_report','deal_recap')),
  tier           smallint NOT NULL CHECK (tier IN (1,2,3)),   -- 1 first-party/broker-landing, 2 self-generated recap, 3 marketplace/rehost (GATED)
  title          text,
  description     text,

  -- WHERE it was found (link-only friendly; no download implied)
  source_name    text,                    -- 'CBRE', 'RENTV', 'self-generated', ...
  source_landing_url text,                -- the human-viewable listing/landing page
  document_url   text,                    -- the OM/flyer document URL if the page exposes one
  discovery_method text,                  -- 'broker_domain_join' | 'press_room' | 'sitemap' | 'manual' | 'generated'

  -- RIGHTS / COMPLIANCE (codex): store the basis, don't assume 'public = ok'
  rights_basis   text,                    -- 'first_party' | 'public_record' | 'self_generated' | 'GATED_needs_review'
  access_status  text,                    -- 'public' | 'robots_disallowed' | 'login_required' | 'gated'
  robots_ok      boolean,                 -- did robots.txt permit automated discovery of this path?

  -- Only populated for TIER 2 generated or a GATED tier-3 rehost
  local_path     text,
  file_format    text CHECK (file_format IN ('pdf','html','markdown')),

  source_firm_id bigint,                  -- optional: resolved broker/firm
  last_verified_at timestamptz,           -- link freshness (replaces content_hash for link-only)
  created_at     timestamptz NOT NULL DEFAULT now()
);

-- An asset can be the subject of a PROPERTY (parcel) and/or 0..n DEALS.
CREATE TABLE IF NOT EXISTS asset_subject_link (
  id            bigserial PRIMARY KEY,
  asset_id      bigint NOT NULL REFERENCES external_marketing_asset(id) ON DELETE CASCADE,
  subject_type  text NOT NULL CHECK (subject_type IN ('parcel','deal')),
  -- parcel subject: (county_fips, ain) ; deal subject: (county_fips, doc_number). Deal key nullable.
  county_fips   text NOT NULL,
  ain           text,
  doc_number    text,
  match_confidence numeric,               -- 0..1 from the provenance probe
  created_at    timestamptz NOT NULL DEFAULT now(),
  CHECK (subject_type <> 'parcel' OR ain IS NOT NULL),
  CHECK (subject_type <> 'deal'   OR doc_number IS NOT NULL)
);

CREATE INDEX IF NOT EXISTS idx_ema_type ON external_marketing_asset(asset_type);
CREATE INDEX IF NOT EXISTS idx_ema_tier ON external_marketing_asset(tier);
CREATE INDEX IF NOT EXISTS idx_asl_asset ON asset_subject_link(asset_id);
CREATE INDEX IF NOT EXISTS idx_asl_deal ON asset_subject_link(county_fips, doc_number);
CREATE INDEX IF NOT EXISTS idx_asl_parcel ON asset_subject_link(county_fips, ain);