`migrations/003_site_audits.sql`

46 lines
-- Site audits — captures a snapshot of each firm's marketing presence:
-- color palette, content signals, weight, and a 0-100 "marketing health" score.
--
-- This is NOT a real traffic ranking (those require paid APIs). It's a
-- composite health score from publicly observable on-page signals only.
-- "Better marketing" is a defensible inference; "more traffic" would be a lie.

CREATE TABLE IF NOT EXISTS site_audits (
  id                BIGSERIAL PRIMARY KEY,
  organization_id   BIGINT NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  audited_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  url               TEXT NOT NULL,
  final_url         TEXT,
  status_code       INTEGER,

  -- Visual
  screenshot_path   TEXT,
  primary_color     TEXT,
  palette           JSONB,         -- [{hex,r,g,b,fraction},...] top-N

  -- On-page signals (booleans)
  has_https         BOOLEAN,
  has_favicon       BOOLEAN,
  has_og_image      BOOLEAN,
  has_meta_viewport BOOLEAN,
  has_analytics     BOOLEAN,       -- GA4 / GTM / Plausible / Fathom
  has_h1            BOOLEAN,
  has_schema_org    BOOLEAN,
  has_phone_visible BOOLEAN,

  -- Page facts
  page_size_bytes   INTEGER,
  load_time_ms      INTEGER,
  font_count        INTEGER,
  image_count       INTEGER,
  link_count        INTEGER,

  -- Score & suggestions
  marketing_score   INTEGER,       -- 0..100
  suggestions       TEXT[]
);

CREATE INDEX IF NOT EXISTS idx_site_audits_org   ON site_audits(organization_id);
CREATE INDEX IF NOT EXISTS idx_site_audits_score ON site_audits(marketing_score DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_site_audits_when  ON site_audits(audited_at DESC);