`db/migrations/021_news_items.sql`

31 lines
-- Per-business scraped news/blog/press posts. The magazine surfaces these as
-- "what's new on Ventura Bl" change-feed entries. Body is raw extracted text;
-- summary is a qwen3:14b post-pass (filled by a separate worker so the
-- scraper stays fast + offline-replayable).

CREATE TABLE IF NOT EXISTS news_items (
  id              BIGSERIAL PRIMARY KEY,
  business_id     BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  source_url      TEXT NOT NULL,
  title           TEXT,
  body            TEXT,
  excerpt         TEXT,
  published_guess DATE,
  content_hash    TEXT NOT NULL,
  fetched_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  summary         TEXT,
  summary_model   TEXT,
  summarized_at   TIMESTAMPTZ
);

-- Dedup: same (business, content) shouldn't store twice. Hash covers title+body.
CREATE UNIQUE INDEX IF NOT EXISTS news_items_hash_idx
  ON news_items (business_id, content_hash);

-- For magazine reads ("latest news for biz X" / "what's new across the corridor").
CREATE INDEX IF NOT EXISTS news_items_business_idx
  ON news_items (business_id, fetched_at DESC);

CREATE INDEX IF NOT EXISTS news_items_fetched_idx
  ON news_items (fetched_at DESC);