> ## Documentation Index
> Fetch the complete documentation index at: https://docs.promptguard.co/llms.txt
> Use this file to discover all available pages before exploring further.

# Analytics Cookbook

> Copy-pasteable SQL for querying your PromptGuard telemetry — p95 latency, cost by model, high-risk users, blocked-over-time, threats by detector, and daily roll-ups.

<Info>
  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](/platform/audit-logs#exporting-data) and load it into your own store.
</Info>

<Note>
  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](#daily-and-hourly-roll-ups) section shows how to build your own materialized view if you want cheaper dashboards.
</Note>

## 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)`            | USD cost = model pricing × tokens. NULL on scans. Sub-cent precision preserved.                                                                  |
| `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`                                                                                                              |

<Warning>
  `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.
</Warning>

### `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.

```sql theme={"system"}
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:

```sql theme={"system"}
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;
```

<Note>
  `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](/production/latency-budgets) for what each detector contributes.
</Note>

## 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.

```sql theme={"system"}
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):

```sql theme={"system"}
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.

```sql theme={"system"}
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:

```sql theme={"system"}
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:

```sql theme={"system"}
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.

```sql theme={"system"}
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?):

```sql theme={"system"}
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`):

```sql theme={"system"}
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:

```sql theme={"system"}
REFRESH MATERIALIZED VIEW CONCURRENTLY security_events_daily;
```

<Warning>
  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.
</Warning>

An `INSERT`-based accumulator that survives purges:

```sql theme={"system"}
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:

```sql theme={"system"}
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:

```sql theme={"system"}
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

<CardGroup cols={2}>
  <Card title="Latency Budgets" icon="gauge-high" href="/production/latency-budgets">
    Per-detector p99 budgets behind `processing_time_ms`
  </Card>

  <Card title="Audit Logs" icon="clipboard-list" href="/platform/audit-logs">
    Export the same data through the Interactions API
  </Card>

  <Card title="Usage Tracking" icon="chart-bar" href="/platform/usage-tracking">
    Plan quotas, request counts, and spend
  </Card>

  <Card title="Dashboard" icon="chart-line" href="/platform/dashboard">
    Prebuilt analytics in the app
  </Card>
</CardGroup>