Analytics Cookbook

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:

Column	Type	Notes
`created_at`	`timestamptz`	Event time. Index exists on `(user_id, created_at)`.
`decision`	`varchar(20)`	`allow` \| `block` \| `redact`
`threat_type`	`varchar(50)`	`injection`, `pii`, `toxicity`, `data_exfiltration`, … (NULL when allowed)
`confidence`	`float`	0.0–1.0
`processing_time_ms`	`float`	Engine-only latency. Excludes the downstream LLM call on proxied requests.
`model`	`varchar(100)`	Upstream model, e.g. `gpt-4o-mini` (NULL on pure `/guard` scans)
`provider`	`varchar(50)`	`openai`, `anthropic`, …
`surface`	`varchar(20)`	`proxy` \| `sdk` \| `guard` \| `browser` \| `desktop`. Separates Shadow AI traffic from the developer path. NULL on historical rows.
`end_user_id`	`varchar(255)`	Per-end-user attribution. Populated when the caller sends the `X-End-User` header.
`country_code` / `region`	`char(2)` / `varchar(100)`	GeoIP enrichment, filled lazily.
`tokens_input` / `tokens_output`	`integer`	From the upstream response. NULL on `/guard` scans that never proxy.
`cost_usd_estimate`	`numeric(12,6)`	Deprecated 2026-08-10 — NULL on every event written since. Historical rows keep their values and the column is not going away, so existing queries still run. It held tokens × a per-model price table PromptGuard maintained by hand, and that table could not be kept correct; use `tokens_input` / `tokens_output` and your provider’s own billing, which is authoritative.
`direction`	`varchar(10)`	`input` \| `output`
`event_metadata`	`json`	Free-form. Holds `detector` (`regex` \| `ml` \| `agentic` \| a detector name), `route_preset`, `content_preview`, and per-detector `latency_ms`.
`triage_status`	`varchar(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.

Token volume by model

Grouped by upstream model. Tokens are 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,
  sum(tokens_input + tokens_output)       AS tokens_total
FROM security_events
WHERE created_at > now() - interval '30 days'
  AND tokens_input IS NOT NULL
GROUP BY provider, model
ORDER BY tokens_total DESC;

These are token counts, not dollars. PromptGuard used to publish a cost_usd_estimate here. It was tokens multiplied by a price table we maintained by hand for every model on every provider, and it drifted — one model carried a price 7.5× its real rate for months, and nothing detected it, because a wrong price looks exactly like a right one.Token counts come from the provider’s own usage block, so they cannot go stale. To convert them to spend, join against your provider’s current published rates, or read the figure straight from their billing console — which is authoritative in a way a copy of their price list never is.

Token volume attributed to blocked requests (the calls you avoided sending, plus those redacted and still forwarded):

SELECT
  decision,
  count(*)                                          AS requests,
  coalesce(sum(tokens_input + tokens_output), 0)    AS tokens_total
FROM security_events
WHERE created_at > now() - interval '30 days'
GROUP BY decision
ORDER BY tokens_total 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(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,
  tokens_in  bigint,
  tokens_out bigint,
  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(tokens_input, 0)),
  sum(coalesce(tokens_output, 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,
  tokens_in = excluded.tokens_in,
  tokens_out = excluded.tokens_out,
  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(tokens_input, 0)), sum(coalesce(tokens_output, 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, tokens_in = excluded.tokens_in,
      tokens_out = excluded.tokens_out,
      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

Get Started

Core Concepts

Gateway

Shadow AI

Guides

Cookbooks

Platform

Going to Production

Resources

Roadmap / Preview

The tables you’ll query

`security_events` — the request log

`audit_events` — the compliance trail

Latency: p50 / p95 / p99

Token volume by model

High-risk activity by user

Blocked over time

Threats by detector

Daily and hourly roll-ups

Audit trail (configuration & auth)

Next steps

Latency Budgets

Audit Logs

Usage Tracking

Dashboard

​The tables you’ll query

​security_events — the request log

​audit_events — the compliance trail

​Latency: p50 / p95 / p99

​Token volume by model

​High-risk activity by user

​Blocked over time

​Threats by detector

​Daily and hourly roll-ups

​Audit trail (configuration & auth)

​Next steps

Latency Budgets

Audit Logs

Usage Tracking

Dashboard

The tables you’ll query

`security_events` — the request log

`audit_events` — the compliance trail

Latency: p50 / p95 / p99

Token volume by model

High-risk activity by user

Blocked over time

Threats by detector

Daily and hourly roll-ups

Audit trail (configuration & auth)

Next steps