`db/migrations/002_sponsored_placement.sql`

30 lines
-- Sponsored placement intake — paid module rendered above the timeline on
-- an address page. NEVER touches event / entity_place_association /
-- listing_override (those are editorial truth).
CREATE TABLE IF NOT EXISTS sponsored_placement (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  listing_id      uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
  source          text NOT NULL,                       -- 'zillow' | 'redfin' | 'airbnb' | 'other'
  external_url    text NOT NULL,
  headline        text NOT NULL,
  price_label     text,
  contact_email   text NOT NULL,
  affiliation     text NOT NULL,                       -- "listing agent for…", "owner", etc.
  permit_number   text,                                -- self-attested STR permit if jurisdiction requires
  status          text NOT NULL DEFAULT 'pending',     -- pending | approved | rejected | active | expired
  monthly_price   numeric(8,2) NOT NULL DEFAULT 29.00,
  stripe_subscription_id text,
  submitted_at    timestamptz NOT NULL DEFAULT now(),
  reviewed_at     timestamptz,
  active_through  timestamptz
);
CREATE INDEX IF NOT EXISTS idx_sponsored_listing ON sponsored_placement (listing_id);
CREATE INDEX IF NOT EXISTS idx_sponsored_status  ON sponsored_placement (status);

-- Public read view: only currently-active placements
CREATE OR REPLACE VIEW v_sponsored_active AS
SELECT id, listing_id, source, external_url, headline, price_label
FROM sponsored_placement
WHERE status = 'active'
  AND (active_through IS NULL OR active_through > now());