Module schema 

Analytics store schema (DW-043).

The analytics database is a SEPARATE SQLite file from the state store (DW-018): different lifecycle (retention deletes vs identity upserts), different write pattern (high-churn batched appends vs request-path point lookups), and an independent bounded-disk story (incremental vacuum reclaims retention deletes here without ever compacting the identity store). It owns its own PRAGMA user_version namespace — a state-store database and an analytics database can never be confused for one another, and the state store’s forward-only migration contract stays untouched by analytics churn.

Schema v1 (this file is migration 001; v2 adds the DW-120 export_runs ledger — see SCHEMA_V2):

• meta — rollup cursors (see super::rollup): the exclusive upper bound each granularity has durably rolled through, written in the SAME transaction as the rollup rows it covers, so a crash between rollups never double-counts a window.
• raw — one row per completed request, exactly the redacted AccessRecord field set plus a JSON object of custom dimensions. Raw retention is SHORT (default 24 h): raw exists to seed rollups and answer custom-dimension ad hoc queries; the durable history is the rollup tables.
• rollup_fixed — pre-aggregated counters per (granularity, window, fixed dimension tuple). Latency lives as 13 fixed per-bucket counts (MS_BUCKETS bounds) so any set of windows merges by summation and percentiles are estimable without storing per-request samples.
• rollup_dim — the custom-dimension twin: one row per (granularity, window, dimension name, value), aggregated in Rust (the source is a JSON column; keeping the aggregation out of SQL avoids a JSON1 feature dependency in the bundled SQLite).

Constants

BUCKET_COLS

Total stored bucket counts per rollup row: 12 bounded + overflow.

LATEST_SCHEMA_VERSION

Latest analytics schema version this build knows.

MS_BUCKETS

Latency bucket UPPER bounds in milliseconds (inclusive). A request lands in exactly one bucket: the first bound it does not exceed, else the overflow bucket (index 12). The bounds are the observability histogram’s shape at millisecond granularity — sub-millisecond precision is noise at gateway scale, 10 s covers every classified timeout. Counts are stored NON-cumulative (per-bucket); cumulative form is computed at read time.

SCHEMA_V1

The full v1 DDL. Idempotent (IF NOT EXISTS) so a fresh database and a re-run both land at version 1. Connection-level pragmas (WAL, synchronous, auto_vacuum) are applied at open time — several of them return rows and cannot live in an execute_batch.

SCHEMA_V2

Schema v2 (DW-120): the export_runs ledger. One row per scheduled-or-manual usage-statement export attempt per window (primary key; a re-export REPLACES the row — the file output is equally idempotent). status is ok or failed; partial flags a window older than the query granularity’s retention (undercounts are possible) so billing consumers can reject flagged statements instead of trusting them; windows_nonempty counts the range’s rollup windows that carry any data (informational — quiet windows have no rows and are not loss).

SCHEMA_V3

Schema v3 (DW-079): the ai_spend table. One row per completed AI request carrying the provider-reported token usage and the priced cost (micro-USD), attributed to the consumer/team/model/provider that served it. The spend query surface (super::query::spend_summary) aggregates these rows per consumer/team/model for billing reconciliation; the export statement (DW-120) folds the per-consumer totals into its rows.

SCHEMA_V4

Schema v4 (DW-084): the ai_governance_events table. One row per governance check outcome (allow or deny) when the audit switch is on, plus every DENIAL even when the audit switch is off (the done-when requirement: a blocked attempt appears in the audit log). The admin /analytics/governance-audit endpoint reads these rows for shadow review.

SCHEMA_V5

Schema v5 (DW-081): the ai_prompt_logs table. One row per captured AI request carrying the REDACTED prompt and response as JSON, attributed to the consumer/route/provider/model that served it. Capture is opt-in (privacy-first); the redaction pass scrubs PII/secrets before storage. The admin /analytics/prompt-logs endpoint reads these rows.

SCHEMA_V6

Schema v6 (DW-086): three tables for prompt experimentation.

SCHEMA_V7

Schema v7 (DW-087): the mcp_tool_calls table. One row per MCP tool call proxied through the gateway, carrying the session correlation id, the authenticated consumer, the tool name, the authorization outcome (allowed), the call duration, an optional error code, and a status string (success, error, or denied). The admin /mcp/calls endpoint reads these rows; the session and consumer indexes support per-session and per-consumer drill-down.

SCHEMA_V8

Schema v8 (DW-113): add the consumer_type column to ai_spend and mcp_tool_calls for typed agent/user attribution. The column defaults to 'user' so pre-DW-113 rows and records that omit the field land as user traffic (the pre-existing default). Uses ALTER TABLE (additive) so existing databases migrate in place.

SCHEMA_V9

Schema v9 (DW-093): add request_id and correlation_id columns to the raw table for business-metrics journey/funnel analytics. Both default to '' so pre-DW-093 rows and records that omit the fields land as empty (the journey query simply returns no rows for an empty correlation id). The correlation_id index supports the journey query (all raw rows for one correlation id, ordered by time). Uses ALTER TABLE (additive) so existing databases migrate in place.

SCHEMA_V10

Schema v10 (DW-102): add optional replay-capture columns to the raw table for time-travel debugging. request_headers_redacted holds the redacted request headers (opt-in, scrubbed via the existing PII redaction patterns) and auth_identity holds the authenticated consumer name (opt-in). Both default to NULL so pre-DW-102 rows and records that omit the fields (capture is opt-in) land as NULL — the replay reader treats NULL as “not captured”. Uses ALTER TABLE (additive) so existing databases migrate in place.

SCHEMA_V11

Schema v11 (PERF-12, #209): add bytes_in and bytes_out columns to the raw table for per-route traffic accounting. bytes_in is the declared Content-Length (0 for chunked request bodies); bytes_out is the frame-counted response body size (counted as frames stream to the client, no buffering). Both default to 0 so pre-v11 rows land as zero. Uses ALTER TABLE (additive) so existing databases migrate in place.

Functions

bucket_of

Bucket index for one duration (the overflow bucket for anything past the last bound).

migrate

Apply migrations to a fresh-or-existing analytics connection. A database at a NEWER version than this build is a hard error (the state store’s forward-only rule, same rationale).

percentile

Percentile estimate from non-cumulative bucket counts: the first bound whose cumulative count reaches p * total (else the last bound — everything past it is reported as 10 s, the honest ceiling).