sori.studio/db/migrations/068_csp_violation_aggregates.sql

CREATE TABLE IF NOT EXISTS csp_violation_aggregates (
  policy_version TEXT NOT NULL,
  document_path TEXT NOT NULL,
  effective_directive TEXT NOT NULL,
  blocked_origin TEXT NOT NULL,
  user_agent_family TEXT NOT NULL,
  violation_count BIGINT NOT NULL DEFAULT 1,
  first_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (
    policy_version,
    document_path,
    effective_directive,
    blocked_origin,
    user_agent_family
  ),
  CONSTRAINT csp_violation_aggregates_count_check
    CHECK (violation_count > 0)
);

CREATE INDEX IF NOT EXISTS csp_violation_aggregates_last_seen_idx
  ON csp_violation_aggregates (last_seen_at DESC);