Skip to main content
Every guarded request writes one row to the security_events table (input and output are separate rows). Self-hosted deployments own that Postgres database directly, so you can point psql, a BI tool, or your warehouse’s foreign-data wrapper at it and run these recipes as-is. On PromptGuard Cloud, pull the same data through the Interactions API and load it into your own store.
There is no pre-aggregated roll-up table today — these recipes run directly against the raw security_events (and, for configuration/auth activity, audit_events) event tables. The Daily and hourly roll-ups section shows how to build your own materialized view if you want cheaper dashboards.

​
The tables you’ll query

​
security_events — the request log

One row per policy evaluation. The columns that matter for analytics:
ColumnTypeNotes
created_attimestamptzEvent time. Index exists on (user_id, created_at).
decisionvarchar(20)allow | block | redact
threat_typevarchar(50)injection, pii, toxicity, data_exfiltration, … (NULL when allowed)
confidencefloat0.0–1.0
processing_time_msfloatEngine-only latency. Excludes the downstream LLM call on proxied requests.
modelvarchar(100)Upstream model, e.g. gpt-4o-mini (NULL on pure /guard scans)
providervarchar(50)openai, anthropic, …
surfacevarchar(20)proxy | sdk | guard | browser | desktop. Separates Shadow AI traffic from the developer path. NULL on historical rows.
end_user_idvarchar(255)Per-end-user attribution. Populated when the caller sends the X-End-User header.
country_code / regionchar(2) / varchar(100)GeoIP enrichment, filled lazily.
tokens_input / tokens_outputintegerFrom the upstream response. NULL on /guard scans that never proxy.
cost_usd_estimatenumeric(12,6)USD cost = model pricing × tokens. NULL on scans. Sub-cent precision preserved.
directionvarchar(10)input | output
event_metadatajsonFree-form. Holds detector (regex | ml | agentic | a detector name), route_preset, content_preview, and per-detector latency_ms.
triage_statusvarchar(20)open | in_review | resolved
security_events is retention-bounded. Rows are purged on a per-plan schedule (Free 24h, Pro 7d, Scale 30d, Enterprise 90d / custom). A query over a 90-day window on a Pro plan returns at most 7 days of rows. For durable analytics, raise your retention (Enterprise custom_retention_days) or stream events into your own warehouse. audit_events is never purged.

​
audit_events — the compliance trail

Configuration changes, authentication, and data access. Hash-chained and never purged. Columns: created_at, organization_id, user_id, event_type, category (security | authentication | data_access | configuration | billing), action, outcome (success | failure | denied | error), resource_type, resource_id, ip_address, details (jsonb).

​
Latency: p50 / p95 / p99

Engine processing time by percentile over the last 24 hours. Use percentile_cont for interpolated percentiles. 
SELECT
  percentile_cont(0.50) WITHIN GROUP (ORDER BY processing_time_ms) AS p50_ms,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY processing_time_ms) AS p95_ms,
  percentile_cont(0.99) WITHIN GROUP (ORDER BY processing_time_ms) AS p99_ms,
  count(*) AS events
FROM security_events
WHERE created_at > now() - interval '24 hours';
Break the same percentiles down by surface to compare the developer proxy against Shadow AI scans: 
SELECT
  surface,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY processing_time_ms) AS p95_ms,
  percentile_cont(0.99) WITHIN GROUP (ORDER BY processing_time_ms) AS p99_ms,
  count(*) AS events
FROM security_events
WHERE created_at > now() - interval '24 hours'
GROUP BY surface
ORDER BY p99_ms DESC NULLS LAST;
processing_time_ms is the time PromptGuard’s engine spent, not end-to-end request time. On the proxy path it excludes the upstream provider call. See Latency Budgets for what each detector contributes.

​
Cost by model

Spend and token volume grouped by upstream model. cost_usd_estimate is NULL on /guard-only scans (they never call a provider), so filter them out. 
SELECT
  provider,
  model,
  count(*)                       AS requests,
  sum(tokens_input)              AS tokens_in,
  sum(tokens_output)             AS tokens_out,
  round(sum(cost_usd_estimate), 4) AS cost_usd
FROM security_events
WHERE created_at > now() - interval '30 days'
  AND cost_usd_estimate IS NOT NULL
GROUP BY provider, model
ORDER BY cost_usd DESC;
Cost attributed to blocked requests (spend you avoided by blocking, or spend on requests that were redacted then still forwarded): 
SELECT
  decision,
  count(*)                        AS requests,
  round(sum(cost_usd_estimate), 4) AS cost_usd
FROM security_events
WHERE created_at > now() - interval '30 days'
  AND cost_usd_estimate IS NOT NULL
GROUP BY decision
ORDER BY cost_usd DESC;

​
High-risk activity by user

Ranks callers by blocked-request volume. Uses end_user_id (populated when you send the X-End-User header); fall back to user_id for account-level attribution. 
SELECT
  coalesce(end_user_id, user_id::text) AS actor,
  count(*) FILTER (WHERE decision = 'block')  AS blocks,
  count(*) FILTER (WHERE decision = 'redact') AS redactions,
  count(*)                                     AS total_events,
  round(
    100.0 * count(*) FILTER (WHERE decision = 'block') / nullif(count(*), 0),
    1
  ) AS block_pct
FROM security_events
WHERE created_at > now() - interval '7 days'
GROUP BY actor
HAVING count(*) FILTER (WHERE decision = 'block') > 0
ORDER BY blocks DESC
LIMIT 50;
Drill into a single high-risk user’s most recent blocks, including the threat type and detector that fired: 
SELECT
  created_at,
  threat_type,
  event_metadata->>'detector' AS detector,
  confidence,
  event_metadata->>'content_preview' AS preview
FROM security_events
WHERE end_user_id = '<end-user-id>'
  AND decision = 'block'
ORDER BY created_at DESC
LIMIT 100;

​
Blocked over time

Hourly blocked-vs-total counts for a time-series chart: 
SELECT
  date_trunc('hour', created_at)              AS bucket,
  count(*)                                     AS total,
  count(*) FILTER (WHERE decision = 'block')   AS blocked,
  count(*) FILTER (WHERE decision = 'redact')  AS redacted,
  round(
    100.0 * count(*) FILTER (WHERE decision = 'block') / nullif(count(*), 0),
    2
  ) AS block_rate_pct
FROM security_events
WHERE created_at > now() - interval '48 hours'
GROUP BY bucket
ORDER BY bucket;
Swap date_trunc('hour', …) for date_trunc('day', …) and widen the interval for a daily trend.

​
Threats by detector

decision/threat_type tell you what was caught; the detector that caught it lives in event_metadata->>'detector' (values include regex, ml, agentic, and specific detector names). This recipe attributes blocks to the detection layer that fired — useful for tuning which layers earn their latency. 
SELECT
  threat_type,
  coalesce(event_metadata->>'detector', 'unknown') AS detector,
  count(*)                     AS hits,
  round(avg(confidence), 3)    AS avg_confidence,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY processing_time_ms) AS p95_ms
FROM security_events
WHERE created_at > now() - interval '7 days'
  AND decision IN ('block', 'redact')
GROUP BY threat_type, detector
ORDER BY hits DESC;
Threat mix by surface (are Shadow AI pastes producing different threats than the developer proxy?): 
SELECT
  surface,
  threat_type,
  count(*) AS hits
FROM security_events
WHERE created_at > now() - interval '7 days'
  AND threat_type IS NOT NULL
GROUP BY surface, threat_type
ORDER BY surface, hits DESC;

​
Daily and hourly roll-ups

There is no roll-up table shipped, so long-window dashboards scan raw events every load. If that gets expensive, materialize a daily summary yourself. This view is safe to REFRESH on a schedule (nightly, or hourly with CONCURRENTLY): 
CREATE MATERIALIZED VIEW IF NOT EXISTS security_events_daily AS
SELECT
  date_trunc('day', created_at)                AS day,
  surface,
  provider,
  model,
  count(*)                                      AS events,
  count(*) FILTER (WHERE decision = 'block')    AS blocked,
  count(*) FILTER (WHERE decision = 'redact')   AS redacted,
  sum(coalesce(cost_usd_estimate, 0))           AS cost_usd,
  sum(coalesce(tokens_input, 0))                AS tokens_in,
  sum(coalesce(tokens_output, 0))               AS tokens_out,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY processing_time_ms) AS p95_ms
FROM security_events
GROUP BY 1, 2, 3, 4;

-- A unique index lets you REFRESH ... CONCURRENTLY without locking readers.
CREATE UNIQUE INDEX IF NOT EXISTS idx_security_events_daily_key
  ON security_events_daily (day, surface, provider, model);
Refresh it: 
REFRESH MATERIALIZED VIEW CONCURRENTLY security_events_daily;
Because security_events is retention-bounded, a materialized view built from it inherits the same horizon — refreshing after a purge drops the aged-out days. To keep history beyond your plan’s retention, INSERT each day’s roll-up into a plain table you own (self-host) or into your warehouse (Cloud) before the source rows are purged, rather than relying on the view alone.
An INSERT-based accumulator that survives purges: 
CREATE TABLE IF NOT EXISTS security_events_daily_history (
  day        date PRIMARY KEY,
  events     bigint,
  blocked    bigint,
  redacted   bigint,
  cost_usd   numeric(14,6),
  p95_ms     double precision
);

INSERT INTO security_events_daily_history
SELECT
  date_trunc('day', created_at)::date,
  count(*),
  count(*) FILTER (WHERE decision = 'block'),
  count(*) FILTER (WHERE decision = 'redact'),
  sum(coalesce(cost_usd_estimate, 0)),
  percentile_cont(0.95) WITHIN GROUP (ORDER BY processing_time_ms)
FROM security_events
WHERE created_at >= current_date - interval '1 day'
  AND created_at <  current_date
GROUP BY 1
ON CONFLICT (day) DO UPDATE SET
  events   = excluded.events,
  blocked  = excluded.blocked,
  redacted = excluded.redacted,
  cost_usd = excluded.cost_usd,
  p95_ms   = excluded.p95_ms;
Schedule that with pg_cron (self-host / Supabase) or any external scheduler: 
SELECT cron.schedule(
  'rollup-security-events-daily',
  '10 0 * * *',                       -- 00:10 UTC daily
  $$INSERT INTO security_events_daily_history
    SELECT date_trunc('day', created_at)::date, count(*),
           count(*) FILTER (WHERE decision = 'block'),
           count(*) FILTER (WHERE decision = 'redact'),
           sum(coalesce(cost_usd_estimate, 0)),
           percentile_cont(0.95) WITHIN GROUP (ORDER BY processing_time_ms)
    FROM security_events
    WHERE created_at >= current_date - interval '1 day'
      AND created_at < current_date
    GROUP BY 1
    ON CONFLICT (day) DO UPDATE SET
      events = excluded.events, blocked = excluded.blocked,
      redacted = excluded.redacted, cost_usd = excluded.cost_usd,
      p95_ms = excluded.p95_ms;$$
);

​
Audit trail (configuration & auth)

Config changes and authentication live in audit_events, not security_events. Recent denied or failed sensitive actions: 
SELECT
  created_at,
  category,
  action,
  outcome,
  resource_type,
  resource_id,
  ip_address
FROM audit_events
WHERE created_at > now() - interval '7 days'
  AND outcome IN ('failure', 'denied', 'error')
ORDER BY created_at DESC;

​
Next steps

Latency Budgets

Per-detector p99 budgets behind processing_time_ms

Audit Logs

Export the same data through the Interactions API

Usage Tracking

Plan quotas, request counts, and spend

Dashboard

Prebuilt analytics in the app