`db/002_deal_assets_view.sql`

23 lines
-- TK-10708  Read view CRCP/RENTV consume (per codex: consumers read a VIEW, not scraper tables).
-- Lives in the reflyers staging DB. On promotion, the same view is created in usre over the
-- promoted tables. Excludes GATED (tier 3) assets from the consumer surface by default.

CREATE OR REPLACE VIEW deal_assets_v AS
SELECT
  l.county_fips,
  l.doc_number,
  a.asset_type,
  a.tier,
  a.rights_basis,
  a.title,
  a.source_name,
  a.source_landing_url,
  a.local_path,
  l.match_confidence,
  a.last_verified_at
FROM asset_subject_link l
JOIN external_marketing_asset a ON a.id = l.asset_id
WHERE l.subject_type = 'deal'
  AND a.tier < 3            -- hide GATED marketplace assets from the consumer surface
ORDER BY l.doc_number, a.tier, l.match_confidence DESC NULLS LAST;