Skip to content
say hello

// projects · case study

Trusted answers from product data

02 / client name private

Back to all projects
case-02.analytics-assistant
02

Product analyticsProduct analytics assistant

Client name private

// what we built

We built an analytics assistant that lets teams ask questions about product usage in everyday language and get answers they can trace back to the underlying data.

Product
Product analytics assistant
Field
Product analytics
Technical layers
04
Safeguards
06

The challenge

What the team needed to solve

Teams often need an analyst or SQL knowledge to answer everyday questions about product usage. A self-serve assistant also has to protect each customer’s data and show exactly how every answer was calculated.

What we built

How the product works

The assistant translates supported questions into safe, read-only queries, asks for clarification when needed, and explains the result in plain language. Built-in checks protect customer boundaries, verify metric definitions, and keep the source query available for review.

architecture-inspection.map
01

Architecture inspection

The model is one participant in a typed workflow. These notes mark where responsibility, authority, and recovery live.

// system mapGuarded analytics queryTrusted answers from product data
sourceQuestionteam + tenant context
agentQuery plannermetrics + dimensions
guarded query core
  1. logicPolicy checkrole + tenant scope
  2. logicRead-only tenant SQLallowlist + limits
  • logicTinybirdreal-time metrics
  • logicClickHousetenant facts
four-lens verification
  1. logicSchemavalid fields
  2. logicPolicytenant-safe
  3. logicEvidenceclaims match rows
  4. logicSanitytotals + trends
outputAnswerplain-language result
outputProvenancequery + sources + checks
  1. 01

    Authenticated tenant context

    Responsibility
    Resolve organization, workspace, user, and analytics-read permissions before a versioned API route runs.
    Boundary
    Mixed credentials, inactive workspace membership, and missing analytics permission fail before data access.
    Failure path
    Reject the request with a sanitized authentication or authorization error and do not create an analysis run.
  2. 02

    Deterministic query plan

    Responsibility
    Map supported analytics intents to bounded ClickHouse SQL for metrics, funnels, cohorts, revenue, churn, paths, and breakdowns.
    Boundary
    Only SELECT queries over allowlisted analytics sources with tenant, time, bot, and row-limit rules may execute.
    Failure path
    Reject unsafe SQL before Tinybird; an invalid query does not consume the external SQL-call budget.
  3. 03

    Tenant-scoped execution

    Responsibility
    Execute the validated query and normalize bounded result rows for answer generation.
    Boundary
    Every allowed table is rewritten as a tenant-scoped subquery and capped with an outer result limit.
    Failure path
    Abort on client disconnect, record provider failure, and prevent a changed retry from exceeding the SQL budget.
  4. 04

    Answer and provenance gate

    Responsibility
    Write an explanation from authoritative rows and attach server-built SQL provenance, usage, and trace data.
    Boundary
    Four deterministic lenses must accept tenant safety, SQL semantics, metric definitions, and answer consistency.
    Failure path
    Retry within the run budget, then fail the checkpointed run instead of returning an unverified answer.
decision-authority.ledger
02

Decisions and authority

The important choices are explicit: what the system optimizes, what it refuses to improvise, and where people retain control.

  1. 01

    Constraint

    Letting a language model invent arbitrary SQL makes metric meaning and access policy difficult to prove.

    Decision

    Use a deterministic planner for supported analytics questions and reserve the model for routing and explanation.

    Known metric definitions and SQL shapes can be tested independently from the wording of the answer.

    Trade-off

    Unsupported rolling metrics map to the closest supported analysis and must disclose that limitation.

  2. 02

    Constraint

    A correct tenant predicate in generated SQL is not enough protection against accidental cross-workspace access.

    Decision

    Validate tenant predicates and also rewrite every allowed source into a tenant-scoped subquery.

    The second enforcement layer does not rely on a single planner or validator decision.

    Trade-off

    The restricted SQL subset excludes CTEs, set operations, outer joins, and external-source functions.

  3. 03

    Constraint

    A fluent answer can misstate rows or attach provenance invented by the answer provider.

    Decision

    Build provenance on the server, remove provider-supplied SQL, and run four deterministic verification lenses.

    The final evidence trail comes from executed policy-approved SQL rather than model narration.

    Trade-off

    A useful-looking answer is withheld when any critical verification lens fails.

capability-stack.inventory
03

Capability and stack

Capabilities describe the work the system can perform. The stack notes explain why each technical layer is present.

Enabled capabilities

  • Metrics, funnels, retention, LTV, churn, segmentation, attribution, and journey analysis
  • Deterministic read-only SQL planning with tenant-scoped source rewriting
  • Streaming runs, clarification interrupts, follow-ups, and workspace-scoped chat history
  • Canonical server-built SQL provenance and auditable agent traces
  • Four deterministic verification lenses for safety and answer quality
  • Durable run and checkpoint support with model usage and cost accounting

Stack rationale

  • Runtime

    Provide typed authenticated endpoints, streaming contracts, and explicit request validation.

    TypeScript · Bun · Hono · Zod/OpenAPI

  • Analytics data

    Query tenant-scoped event facts and persist run, conversation, trace, and checkpoint state.

    Tinybird · ClickHouse · PostgreSQL

  • AI orchestration

    Coordinate resumable analysis while limiting the model to classification, summarization, and answer writing.

    LangGraph · Cloudflare AI Gateway

  • Operations

    Stream run state, access durable Postgres storage, and record structured operational events.

    SSE · Bun SQL · Pino

safeguards-recovery.runbook
04

Safeguards and recovery

Reliable behavior includes the unhappy path: how faults are detected, contained, retried, or handed back to an operator.

Failure runbook

  1. 01
    Condition
    A segmentation request omits the dimension to break down.
    Detection
    The planner identifies a required breakdown with no valid selected field.
    Response
    Checkpoint the run, interrupt with bounded choices and validated free text, then resume after human input.
  2. 02
    Condition
    A planned query violates read-only, tenant, source, time, or raw-field policy.
    Detection
    Semantic SQL validation runs before the Tinybird provider is called.
    Response
    Block execution, record the policy reason in the trace, and leave the external SQL budget untouched.
  3. 03
    Condition
    The answer provider times out or returns an invalid structured response.
    Detection
    The provider validates response shape and classifies retryable transport failures.
    Response
    Retry within the provider limit, then use the deterministic local answer path; authentication failures fail closed.
  4. 04
    Condition
    A process restarts while an analysis run is still marked running.
    Detection
    Startup recovery inspects persisted run state after storage initialization.
    Response
    Mark stale running work failed while retaining its trace, conversation, and durable checkpoint records.

Principles in practice

  • Start with the workflow

    A checkpointed graph separates route classification, deterministic planning, tool execution, answer writing, and verification.

  • Show the sources

    Answers are grounded in returned rows and receive canonical SQL provenance assembled by the server.

  • Put rules in code

    Closed tools, SQL allowlists, time and row bounds, exact metric definitions, and four verification lenses stay outside generation.

  • Keep people in charge

    Ambiguous breakdowns pause as resumable clarification interrupts instead of silently choosing a dimension.

  • Limit access by default

    Authentication scopes every route and query to one organization, workspace, and analytics-read permission.

  • Plan for things to go wrong

    Persisted runs, Postgres checkpoints, streamed trace events, abort propagation, and startup recovery expose long-running state.

validation-evidence.report

// validation signal

What was checked—and what was not.

20/20 curated integration-evaluation scenarios passed on seeded analytics data, including ground-truth factual checks, six robustness cases, and a five-turn conversation.

Internal integration evaluation on seeded data; this is not a production KPI.

Method

A gated internal integration evaluation ran curated questions against seeded analytics data with the live model path and independently computed ground truth.

Checks performed

  • 20/20 curated scenarios passed across supported analytics intents and workflow controls
  • All 14 factually scored scenarios matched their ground-truth checks
  • Six robustness scenarios passed without prohibited fallback behavior
  • A five-turn conversation retained its thread and answered each supported follow-up

Outside this claim

  • The internal evaluation does not establish production accuracy, latency, scale, customer adoption, or business impact.
  • Production deployment and dependency-specific release proofs were outside the recorded evaluation.
next-system.request

// from difficult workflow to working product

Have a workflow like this?

We can help define the product, decide where AI adds value, and take it through a tested implementation.