← back to Animals

migrations/012_site_audit_html.sql

21 lines

-- 012_site_audit_html.sql
-- Persist raw HTML snapshots from site_audit and add ad_signals JSONB columns
-- so the ad-social-tracker Pass 1 can mine pixel fingerprints.
--
-- Animals has no separate business_enrichment table — store on site_audits.
-- ad_signals is keyed per-audit (latest audit per business is canonical via
-- DISTINCT ON queries downstream).

ALTER TABLE public.site_audits
  ADD COLUMN IF NOT EXISTS raw_html_path      TEXT,
  ADD COLUMN IF NOT EXISTS ad_signals         JSONB,
  ADD COLUMN IF NOT EXISTS signals_updated_at TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS site_audits_raw_html_path_idx
  ON public.site_audits(raw_html_path)
  WHERE raw_html_path IS NOT NULL;

CREATE INDEX IF NOT EXISTS site_audits_ad_signals_gin_idx
  ON public.site_audits USING GIN (ad_signals)
  WHERE ad_signals IS NOT NULL;