← back to Restaurant Directory

migrations/003_front_page_crawl.sql

26 lines

-- 003_front_page_crawl.sql
-- Front-page crawler audit table + ad-signals columns on facility_enrichment.
-- Mirrors ventura-corridor's pattern; adapted for facility_id (text PK).

CREATE TABLE IF NOT EXISTS public.front_page_audits (
  facility_id     TEXT REFERENCES facility(facility_id) ON DELETE CASCADE,
  url             TEXT NOT NULL,
  audited_at      TIMESTAMPTZ DEFAULT now(),
  raw_html_path   TEXT,
  http_status     INT,
  fetch_ms        INT,
  error_message   TEXT,
  PRIMARY KEY (facility_id, audited_at)
);

CREATE INDEX IF NOT EXISTS fpa_html_idx
  ON public.front_page_audits (raw_html_path)
  WHERE raw_html_path IS NOT NULL;

CREATE INDEX IF NOT EXISTS fpa_facility_audited_idx
  ON public.front_page_audits (facility_id, audited_at DESC);

ALTER TABLE public.facility_enrichment
  ADD COLUMN IF NOT EXISTS ad_signals JSONB,
  ADD COLUMN IF NOT EXISTS signals_updated_at TIMESTAMPTZ;