`migrations/019_site_audit_html.sql`

38 lines
-- 019_site_audit_html.sql
-- Persist raw HTML snapshots from site_audit Playwright pass so a downstream
-- regex-only ad-signal detector can mine known tracking-pixel fingerprints.
--
-- Why a separate firm_ad_signals table (and not a column on organizations)?
--   - This is INTERNAL sales-targeting data only. Per
--     feedback_lawyer_directory_compliance.md, internal CRM signals stay isolated
--     from the public profile schema so they can never accidentally render
--     into a public-facing artifact.
--   - Cal §6155 / Rule 7.x: directory-only, no fabricated marketing claims —
--     these signals are inputs for our outreach, not facts about the firm.
--
-- Schema:
--   site_audits.raw_html_path  — relpath under raw_html/ to the persisted HTML
--   firm_ad_signals            — one row per firm, JSONB blob of pixel-detect booleans

ALTER TABLE public.site_audits
  ADD COLUMN IF NOT EXISTS raw_html_path TEXT;

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 TABLE IF NOT EXISTS public.firm_ad_signals (
  firm_id            BIGINT PRIMARY KEY REFERENCES public.organizations(id) ON DELETE CASCADE,
  ad_signals         JSONB NOT NULL DEFAULT '{}'::jsonb,
  signals_updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS firm_ad_signals_paid_count_idx
  ON public.firm_ad_signals (((ad_signals->>'paid_ads_count')::int) DESC NULLS LAST);

CREATE INDEX IF NOT EXISTS firm_ad_signals_updated_idx
  ON public.firm_ad_signals (signals_updated_at DESC);

COMMENT ON TABLE public.firm_ad_signals IS
  'INTERNAL sales-targeting only. Tracking-pixel detect output mined from raw_html_path. Admin-gated. Never expose publicly.';