`db/migrations/016_magazine.sql`

29 lines
-- Migration 016 — magazine of corridor businesses with AI-generated editorial
-- Pivot from outreach pipeline → corridor shopping guide / "magazine of who's here"
-- Each business gets a glowing AI-written feature; ad-signal-detected businesses prioritized
-- (they're proven ad-buyers, natural targets for a paid feature placement).

CREATE TABLE IF NOT EXISTS magazine_features (
  id            BIGSERIAL PRIMARY KEY,
  business_id   BIGINT       NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  headline      TEXT,                          -- 4-8 word punchy headline
  subhead       TEXT,                          -- 10-20 word descriptor
  editorial     TEXT,                          -- 80-150 word feature paragraph
  pull_quote    TEXT,                          -- optional callout (≤20 words)
  category_tag  TEXT,                          -- 'restaurant' | 'professional' | 'beauty' | 'shop' | etc
  photo_url     TEXT,                          -- placeholder until photos sourced
  status        TEXT NOT NULL DEFAULT 'draft', -- 'draft' | 'reviewed' | 'published' | 'spiked'
  model         TEXT,                          -- 'qwen3:14b' | 'claude' | etc
  prompt_hash   TEXT,                          -- so we can re-gen if prompt changes
  generated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  reviewed_at   TIMESTAMPTZ,
  published_at  TIMESTAMPTZ,
  views         INTEGER NOT NULL DEFAULT 0,
  notes         TEXT,
  UNIQUE (business_id)                          -- one feature per business at a time
);

CREATE INDEX IF NOT EXISTS idx_magfeat_status         ON magazine_features (status);
CREATE INDEX IF NOT EXISTS idx_magfeat_published_at   ON magazine_features (published_at DESC) WHERE status='published';
CREATE INDEX IF NOT EXISTS idx_magfeat_category       ON magazine_features (category_tag);