← back to Professional Directory

db/migrations/006_front_page_crawl.sql

26 lines

-- 006_front_page_crawl.sql
-- Front-page HTML capture + ad/social-tracking enrichment.
-- Mirrors lawyer-directory's front_page_audits + business_enrichment, adapted
-- to organizations (organization_id, not business_id).

CREATE TABLE IF NOT EXISTS public.front_page_audits (
  organization_id BIGINT REFERENCES organizations(id),
  url             TEXT NOT NULL,
  audited_at      TIMESTAMPTZ DEFAULT now(),
  raw_html_path   TEXT,
  http_status     INT,
  fetch_ms        INT,
  PRIMARY KEY (organization_id, audited_at)
);
CREATE INDEX IF NOT EXISTS fpa_org_id_idx ON front_page_audits(organization_id);
CREATE INDEX IF NOT EXISTS fpa_html_idx
  ON front_page_audits(raw_html_path)
  WHERE raw_html_path IS NOT NULL;

CREATE TABLE IF NOT EXISTS public.org_enrichment (
  organization_id    BIGINT PRIMARY KEY REFERENCES organizations(id),
  ad_signals         JSONB,
  signals_updated_at TIMESTAMPTZ,
  enriched_at        TIMESTAMPTZ DEFAULT now()
);