feat(enterprise-analytics): Add governed ad-hoc observability query workbench with saved queries and exports

[matdev83]

Assessment: 9.4/10

Expected Enterprise/observability value: exceptional
Proxy-level fit: very high
Implementation leverage vs complexity: high if queries target a governed analytical schema and are isolated from operational authorities

Summary

Add an Enterprise ad-hoc observability analytics workbench that lets authorized engineers, SREs, FinOps and AI platform teams ask arbitrary read-only questions over AIProxer request/session/trace/quality/economic data instead of being limited to dashboards anticipated in advance.

Helicone provides HQL (Helicone Query Language), a SQL analytics surface over its observability data with schema introspection, a dashboard editor, saved queries, REST execution, CSV downloads and explicit query/time/rate limits.

Reference:

• https://github.com/Helicone/helicone/blob/main/docs/features/hql.mdx

AIProxer should borrow the flexibility while enforcing stricter workload isolation and multi-tenant authorization from the start.

Why this is distinct from existing work

#509 — request log / observability persistence

Owns how request/session metadata and optional large content are retained.

#411 — Enterprise dashboards/reports

Owns curated product views such as spend by project/user, executive trends and forecasts.

#456 — one-request explanation

Answers why a particular request was routed/transformed/failed.

#518 — external export

Streams normalized events to customer-owned Kafka/data platforms.

This issue

Answers unforeseen analytical questions such as:

Which model has the best tests_passed rate per dollar for PR review?

Did TTFT regress after config generation 1847?

Which agent versions generate the most tool retries?

How much wall time in successful sessions is LLM vs shell/test work?

Which projects are still using a deprecated route?

Did evaluator code_quality decline only for one provider region?

A mature observability product cannot require a new dashboard widget for every investigation.

Architecture principle

The query workbench is a read-only analytical projection over observability evidence. It must never expose unrestricted access to AIProxer's operational/configuration databases or permit query execution to impair inference.

1. Governed analytical schema

Expose a stable versioned logical schema rather than raw internal database tables.

Candidate views/tables:

requests             # #509 metadata projection
attempts             # B-leg attempt summaries
sessions             # #511/session aggregates
spans                 # #502 internal + #522 external spans
usage_cost            # authoritative economic projection
quality_scores        # #519
properties            # #520
 evaluator_runs        # #521 metadata/status
alerts                # #516 safe state/history where appropriate
batch_async_jobs      # #500/#512 safe lifecycle views

Exact schema should follow implemented authorities. Avoid exposing persistence implementation details such as Bun table names or internal migration columns.

2. Read-only query language

SQL is attractive because enterprise analysts already know it, but the service must support only a constrained read-only subset over registered views.

At minimum reject:

INSERT / UPDATE / DELETE / MERGE
DDL
COPY / INTO OUTFILE
ATTACH / extension loading
filesystem/network/table functions
arbitrary system/catalog tables
stored procedure/function creation
SET statements that weaken limits
multi-statement query chains

The SDD should choose between:

• SQL parser + validated AST compiled to the analytics backend; or
• a structured query DSL with optional SQL frontend.

Do not implement security with regex keyword filtering.

3. RBAC/DAC must be injected independently of user SQL

#506 authorization is mandatory.

A team-scoped user running:

SELECT * FROM requests

must still receive only rows authorized for that principal.

Enforce scope in the query service/planner/data-source layer so it cannot be removed by the user's WHERE, subquery, join or alias tricks.

Requirements:

• organization/workspace/team/project scope applied before result limits;
• direct request/session IDs cannot bypass DAC;
• aggregates/counts use the same authorized row set;
• saved queries do not capture/bypass the creator's former broader permissions;
• execution re-evaluates current caller permissions each time.

4. Metadata-only default views

Ordinary analytical views contain no raw prompt/completion/tool content.

Content access is a separate privileged surface tied to #509/#474/#506.

Possible explicit view/function later:

request_content_authorized

with stronger permission, retention checks and #508 audit.

Do not make SELECT * casually dump source code or credentials.

Reversible-redaction mappings from #513/#474 are never exposed through general analytical SQL.

5. Quality and custom dimensions as first-class analytics

#519 and #520 are particularly valuable when queryable.

Examples:

SELECT route,
       avg(score_value) AS code_quality,
       sum(provider_cost) AS cost
FROM ...
WHERE score_metric = 'code_quality'
  AND property('workflow') = 'pr-review'
GROUP BY route;

The physical schema may use normalized maps/joins rather than SQL functions; the example illustrates intended analytical power.

Do not dynamically create one database column per score/property.

6. Session/agent-flow analytics

Combine #502/#522/#511 evidence to support questions such as:

session wall time
LLM/provider time
AIProxer overhead
tool/shell/test/retrieval time
number of model turns
tool-call count/failure rate
session cost
session quality/outcome

Materialize common session aggregates where that prevents expensive repeated span-tree scans.

The workbench can still query individual bounded spans when authorized.

7. Query resource governance

Hard server-side limits must apply independent of SQL text:

max execution time
max result rows
max result bytes
max scanned rows/bytes where backend supports it
max memory per query
max concurrent queries per principal/org
query rate limit
max joins/subquery depth/AST nodes
max grouping cardinality
max time range by permission/profile where needed

Helicone documents row, timeout and rate limits for HQL; AIProxer should make these a formal query-workload policy.

On limit exhaustion, cancel the query and return a stable reason rather than allowing runaway analytics work.

8. Workload isolation from inference

This is non-negotiable.

Potential deployment modes:

Small Enterprise deployment

Read from bounded indexed PostgreSQL projections using a separate connection pool with low priority/strict timeouts.

Larger deployment

Replicate/export observability events into a columnar analytical store (ClickHouse or another adapter) and query that store.

The query service should consume an AnalyticsStore abstraction rather than making ClickHouse mandatory in V1.

Hard requirements:

• never share the last DB connections needed by inference/accounting/control plane;
• no table lock/DDL generated by user queries;
• analytics saturation cannot exhaust request-path worker pools;
• analytics backend outage does not fail inference.

9. Schema introspection

Expose a protected machine-readable schema:

views/tables
columns
types
description
sensitivity/content class
join relationships where supported

UI can use this for completion/browser help.

Only expose objects the caller may know exist; tenant-specific secret infrastructure table names are not useful schema metadata.

10. Saved queries

Allow authorized users to save named query definitions:

query id/name
description
SQL/query text
owner/scope
created/updated revision
optional tags

Support private/team/org sharing according to #506.

Saved-query mutation is audited through #508.

Execution always uses the caller's current permissions; a saved query created by an admin is not a capability token.

11. Parameterized saved queries

Useful for reusable diagnostics:

project_id
start_time
end_time
route
workflow

Define typed parameters rather than string substitution to avoid SQL injection and improve caching/planning.

12. Result export

Allow bounded JSON/CSV export.

Small results can stream directly.

Large permitted exports should become an async job producing a short-lived protected download object/reference.

Requirements:

• export row/byte caps;
• feat(enterprise-rbac): Add control-plane RBAC and row-level data access scopes #506 explicit download permission;
• feat(enterprise-audit): Add tamper-evident administrative audit trail with durable archival #508 audit for privileged/large/content exports;
• expiring signed/internal download token;
• feat(policy-routing): Add trusted tag/constraint-based deployment routing #481/feat(enterprise-logs): Add metadata-first request log store with object-storage payload offload #509 residency policy;
• no permanent public object URLs.

Do not use browser memory to assemble multi-GB CSVs.

13. Query history and diagnostics

Persist bounded safe execution metadata:

query id/hash
actor
started/completed
outcome
elapsed
rows returned
bytes returned
scan/cost estimate where available
limit reason

Do not put full sensitive SQL literal values into broad logs by default.

Query history itself obeys #506.

14. Query templates / observability cookbook

Ship useful saved-query templates rather than only a blank SQL box:

quality / dollar by route
p95 TTFT by provider
fallback rate by model
AIProxer overhead by phase
agent wall-time breakdown
negative-feedback examples
project spend anomalies
evaluator score regression
MCP/tool failure hotspots

This gives non-SQL experts immediate value while keeping the underlying surface general.

15. Integration with dashboards

A saved query can later become a chart/report data source for #411's Enterprise UI.

But this issue does not need to build a complete BI visualization designer.

Keep architecture:

analytics query authority
   -> tabular/time-series result
   -> workbench table
   -> optional #411 visualization/report consumer

16. Scheduled queries/reports

Helicone also provides recurring email/Slack reports. AIProxer should avoid a duplicate scheduling/delivery system:

• feat(enterprise): Company-wide project catalog, session-project correlation #411 owns Enterprise reporting/summary UX;
• feat(enterprise-alerting): Add policy-aware alerts, incident channels and delivery history #516 owns delivery channels/alerting infrastructure;
• saved feat(enterprise-analytics): Add governed ad-hoc observability query workbench with saved queries and exports #524 queries can be future report inputs.

Do not put report scheduling into V1 unless composition requires a tiny shared contract.

17. Multi-instance behavior

Query execution itself can be stateless behind the load balancer if analytical storage is shared.

Long exports/jobs use durable coordination and #514/#500-style ownership where needed.

Saved query definitions/config converge through the Enterprise control plane/#494 as appropriate.

18. Open-Core boundary

This is a strong closed Enterprise observability feature.

OSS should retain normal structured diagnostics, Prometheus/OTel and local developer views.

Enterprise owns:

cross-organization analytical schema
ad-hoc governed query service
saved/shared queries
large exports
analytics workload isolation
Enterprise query UI

Do not intentionally cripple open telemetry standards in OSS to create this value.

Suggested V1

1. Versioned metadata-only analytical views over feat(enterprise-logs): Add metadata-first request log store with object-storage payload offload #509 request/attempt/usage data.
2. Strict read-only SQL AST validator/compiler.
3. feat(enterprise-rbac): Add control-plane RBAC and row-level data access scopes #506 DAC injection independent of query text.
4. hard timeout/row/result/concurrency limits.
5. separate PostgreSQL analytics pool.
6. schema browser + tabular results.
7. saved/private/team queries.
8. JSON/CSV bounded exports.
9. add feat(enterprise-quality): Add production feedback, quality scores and annotations for requests and sessions #519/feat(enterprise-observability): Add bounded custom request dimensions for filtering, grouping and segmentation #520/feat(tracing): Add semantic GenAI spans for routing, attempts, sidecars and tool lifecycles #502/feat(enterprise-agent-observability): Ingest external tool, retrieval and agent-step spans into AIProxer traces #522 views as their features land.
10. introduce columnar AnalyticsStore adapter when scale benchmarks justify it.

Acceptance criteria

• An authorized Enterprise user can execute an ad-hoc read-only analytical query over request metadata without receiving raw internal DB-table access.
• DML/DDL/system/network/filesystem query capabilities are rejected by parsed/validated query structure, not regex filtering.
• feat(enterprise-rbac): Add control-plane RBAC and row-level data access scopes #506 row scope is enforced even when a user query omits or attempts to subvert tenant/team predicates.
• Query timeout, rows, bytes and concurrency are bounded server-side.
• Analytics queries use an isolated resource pool/store and cannot consume all operational DB connections needed by inference/control-plane work.
• feat(enterprise-quality): Add production feedback, quality scores and annotations for requests and sessions #519 quality scores and feat(enterprise-observability): Add bounded custom request dimensions for filtering, grouping and segmentation #520 custom dimensions can be grouped/filtered once implemented.
• Saved query execution always uses the current caller's permissions.
• Large CSV/JSON exports are permissioned, bounded and audited without blocking inference.
• Analytics-backend outage leaves inference operational.

Non-goals

• unrestricted SQL shell against AIProxer databases;
• DML/DDL through the workbench;
• raw prompt/source-code access by default;
• making ClickHouse mandatory before scale data justifies it;
• full Tableau/Looker-style BI product in V1;
• using saved queries as authorization capabilities;
• executing analytics in inference worker pools.

Why 9.4/10

Prebuilt dashboards cover known questions; production incidents and optimization work are dominated by questions nobody predicted. Helicone's HQL demonstrates the product value of giving engineers direct analytical access. A governed AIProxer workbench becomes even more powerful when it can join routing attempts, cost, quality scores and external agent spans while preserving strict Enterprise RBAC and workload isolation.

[matdev83]

Cross-feature Enterprise observability rationale

#524 is the investigation surface for the broader Enterprise observability stack:

telemetry            #502 / #509 / #522
observational dims   #520
quality/outcomes     #519 / #521
        ↓
#524 ad-hoc investigation
        ↓
#523 regression corpus / replay
#516 alerts
#411 dashboards/reports

Prebuilt dashboards answer known questions. Production incidents and optimization work are dominated by questions nobody predicted when the dashboard was designed.

The intended Enterprise value is to let SRE/FinOps/AI-platform engineers ask questions such as:

Which model gives the highest tests_passed/$ for PR review?
Did latency regress only for agent_version 1.8 after config generation N?
How much successful-session wall time is provider inference vs tests/tools?
Which provider/model rollout caused a quality-score distribution shift?
Which projects still depend on a deprecated route?

This is why #524 should not be reduced to 'SQL over the request table'. It must join the semantic evidence produced by routing/attempt tracing, external agent spans, quality scores and custom dimensions.

At the same time, the analytical surface must remain isolated from operational correctness: read-only governed views, current #506 DAC enforcement independent of user SQL, hard scan/time/result limits, and separate analytics resources/store so an expensive investigation can never starve inference/accounting/control-plane database work.