Interactive · Supervised · Shipped
A confident wrong number is the only failure that matters
Building a natural-language analytics agent that a stranger can trust — and the seven subsystems it took to get there.
Analytics teams get interrupted constantly with questions that are individually cheap and collectively enormous. What's DAU on that platform this week. Did the checkout change move conversion. Why did retention dip on Tuesday. Each takes an analyst ten minutes and destroys an hour of focus.
The obvious fix — point an LLM at the warehouse — fails in a specific and instructive way.
The model writes plausible SQL against tables it does not understand. It joins at the wrong grain. It sums a snapshot table that should be read point-in-time. And it returns a confident, beautifully formatted, wrong number.
A wrong answer delivered fluently is worse than no answer, because it gets pasted into a deck. Three weeks later someone defends a decision in a meeting using a figure that was never real, and nobody in the room has any way to trace it.
So the real problem is not "can a model query a warehouse." It is: how do you make a model's analytical output trustworthy enough that a stranger can act on it — when the model is non-deterministic, the schema carries ten years of accumulated weirdness, and the person asking has no way to check the work.
Everything distinctive about this architecture follows from that one question.
The constraints that shaped it
Answers must be sourced or explicitly caveated. Every number traces to a tool result. The system prefers "I couldn't verify this" over a plausible guess. This single requirement drives the evidence contract, the retry logic, and half the post-processing pipeline.
Institutional knowledge doesn't live in the schema. The rules that make an answer correct — this metric is weekly not daily, that platform's spend data lags three days, this column mixes two entity types — exist in analysts' heads. They must be captured in a form the agent reads selectively, because there is far more of it than fits in a prompt.
Runs are long and bursty. A real investigation is thirty seconds to several minutes of tool calls. Chat platforms want an HTTP acknowledgment in about three. Those two facts are irreconcilable in a single synchronous request, which forces an async architecture with out-of-band delivery.
The interface is a chat thread, not a dashboard. Threads give you conversational follow-up for free, but only if the system carries state forward. They also mean the output is structured chat blocks and uploaded images, not HTML.
The team is small. Every operational choice trades sophistication for "one person can debug this on a Friday." One host and rsync rather than a cluster and a registry. Knowing why that tradeoff was correct at this scale is more interesting than the tradeoff itself.
Request lifecycle
The path from mention to answer, end to end.
1 · Front door. The chat platform posts an event to a lightweight serverless function. It verifies the request signature, drops platform retries, and filters to mentions only.
The dedupe matters more than it sounds. Chat platforms redeliver aggressively when your response is slow — and your response is always slow, because you are about to do minutes of work. Without dedupe you answer the same question three times.
2 · Cheap-intent fast path. A small set of high-frequency, low-complexity questions is pattern-matched and answered right there with a direct query and a small model. Sub-three-second replies, no agent involved.
This is a large share of traffic and it never touches the expensive path. It is the same insight as caching, applied to intent — and sub-three-second replies on common questions do more for adoption than better answers on hard ones.
3 · Async handoff. For everything else the function acknowledges immediately, then re-invokes itself asynchronously. The second invocation reads recent thread history for context, posts a visible "working on it" placeholder, and fires a request at the agent host with a deliberately short timeout — a read timeout is expected and treated as success.
Nothing comes back this way. The acknowledgment and the answer travel entirely different paths. Serverless self-invocation is the clean way around a platform gateway timeout: cheaper and simpler than introducing a queue when the only requirement is escaping a time limit.
4 · Admission. The agent host accepts the request behind a shared-secret header, immediately returns "accepted," and runs the work as a background task under a small concurrency semaphore. There is no queue; the semaphore is the backpressure. An endpoint exposing in-flight count is what makes safe deploys possible.
5 · Context assembly. Before the model sees anything, the system builds its working context: topic-matched rules and table routes from the knowledge layer, prior findings from this thread's memory, the current date resolved into explicit ranges, and the answer contract. A tier classifier decides fast-model-with-short-timeout versus deep-model-with-long-timeout based on the question's shape.
6 · The agent loop. A standard tool-use loop — the model proposes a tool call, the system validates it against routing rules before execution, runs it, clamps the result size, feeds it back. Guarded by a wall-clock timeout, a turn cap, and a running cost budget that hard-stops mid-loop.
7 · Validation. The structured answer goes through a deterministic post-processing pipeline — no second model call. Structure gets repaired, sources get reconstructed from the tool log, and an evidence contract decides whether the claims are actually supported.
8 · Render and deliver. The validated result becomes chat blocks, a chart image, and — when the data is too big for the chat client — a spreadsheet attachment. Posted directly to the original thread by the agent host, out-of-band from step 3.
9 · Record. A trace of the run (tools used, queries issued, cost, latency, matched topics) is written to a log store, non-blockingly. Findings are persisted to thread memory for follow-ups. Feedback buttons ride along on the message.
Subsystem: the agent host
One long-lived process, because agent runs need persistent tool connections and outlive any serverless timeout.
Model tiering. Questions are classified into a fast tier and a deep tier by keyword and shape, with an explicit override token for humans who know better. Deep-model runs cost an order of magnitude more and take ten times longer, and most questions do not need one.
Getting the classifier wrong in the conservative direction — escalating too often — is much cheaper than the reverse. Build the asymmetry in deliberately.
Concurrency. A small semaphore, single digits, around the whole run. Agent runs are memory-hungry and tool-connection-bound. Unbounded concurrency degrades everything at once rather than queueing gracefully.
Timeout hierarchy. Four nested layers, and you need all of them:
- Per-tool-call
- Inter-message gap
- Whole-run wall clock
- Framework-level watchdog
The inter-message gap is the one people miss and the one that saves you. Hangs don't error. A stream that stalls without raising an exception will otherwise hang until the outermost bound, holding a concurrency permit the entire time.
Progress signaling. A background task edits the placeholder message on a decaying cadence with the current tool name. Purely cosmetic, disproportionately valuable. Three minutes of silence reads as broken; the same three minutes with visible progress reads as thorough. Perceived latency is a product problem, not a vanity concern.
Session continuity. The agent framework's session id is persisted per thread, so a follow-up resumes rather than restarts. Paired with — not replaced by — thread memory, because sessions expire or get invalidated on retry, and the memory survives that.
Subsystem: the knowledge layer
This is the part that makes the difference between a demo and a system, and it is the piece most reimplementations skip.
The naive approach is to stuff every rule, table description, and caveat into the system prompt. It does not survive contact with reality. The corpus outgrows the context window — and worse, dumping everything makes the model actively worse, because it pattern-matches on irrelevant rules and drags unrelated tables into the answer.
More context makes the model worse. This is counterintuitive and it is the most important practical finding in the whole system. An irrelevant rule in context is not neutral. Select aggressively, then trim to a budget. Fewer, more relevant lines beat comprehensive dumps, and the gap widens as the corpus grows.
Here is the pattern that works.
Annotated plain-text playbooks
Knowledge lives in human-editable text files organized by domain: analysis methodology, per-product mechanics, tool-specific gotchas, durable business facts. Analysts write prose. Inline machine-readable annotations mark the parts the system indexes:
RULE [...]— a correctness constraint ("weekly retention means W1, not D7")ROUTE [...]— which table or source answers which question classTOPIC [...]— what this section is about, for matching
Prose for humans, annotations for the index, one file.
The critical property: the annotation lives next to the prose explaining it. Analysts maintain it without learning a schema, and the machine-readable layer stays honest because it cannot drift from its own explanation. Keep them in separate systems and they diverge within a quarter.
Startup indexing and per-query selection
On boot, everything is parsed into a topic-keyed index, merged with metric definitions and model metadata pulled from the transformation layer's manifest.
Per query, two independent matchers run and their results are unioned: a small fast model classifying the question into topics, and deterministic keyword/stem matching.
The union matters because they fail differently. The classifier handles paraphrase and intent — "why are people churning" resolves to retention. Keyword and stem matching catches internal jargon and product names the model has never seen. Run both. Cheap, and materially better than either alone.
Selected knowledge is then budget-trimmed to a line count that scales with tier.
Always-load minimum
A tiny set of facts — domain quirks, project identifiers — is injected verbatim on every query regardless of topic match, because a miss on those is catastrophic rather than merely unhelpful. Reserve this for things where being wrong is unrecoverable, and keep it genuinely tiny.
A read gate
A pre-tool-execution hook blocks every data-tool call until the agent has actually read a playbook in that session.
Without it, models skip the reading and improvise — exactly like people. This is crude and it works. Sometimes the right mechanism for "the model doesn't reliably do X" is making X a precondition rather than an instruction.
Generated indexes
Some knowledge is too volatile to hand-maintain: the inventory of dashboards, the catalog of analyst-authored notebooks and the tables they touch. These are crawled from source systems on a schedule and written into the same playbook format. Generated and handwritten knowledge are treated identically at read time.
There is a failure mode here that is worth naming precisely. Generated knowledge goes stale silently, and the silence is the danger. When the credential expires, the build fails quietly, the agent keeps answering from an index frozen weeks ago, and nothing surfaces the problem — the answers just slowly stop matching reality.
Rebuild on a schedule, and alert on staleness rather than only on failure. "Last successful build was N days ago" catches this. "Did the last run error" does not.
Curated event context
A timeline of things that happened — launches, experiments, incidents — extracted from team channels and human-reviewed before entry. This is what lets the agent connect "conversion dropped on the 12th" to "the checkout change rolled out on the 11th," which is the difference between a metric report and an explanation.
Two disciplines make it safe:
- Proposals are PR-gated, never auto-written. The failure mode of auto-ingestion is not a bad entry — it is a bad entry that becomes context for every future answer, invisibly, for months.
- The agent cites entry ids, and the system resolves ids to actual links. A model that never handles a URL can never invent one.
Thread memory
Per-thread JSON: tables used, queries that worked, columns discovered, key numbers, methodology, open decisions. Injected into follow-ups as prior findings.
This is what makes "now break that out by platform" work without re-deriving everything — and it doubles as evidence when a follow-up legitimately answers from established context rather than a fresh query.
Subsystem: answer validation
A deterministic pipeline. No second model call, because a validator that hallucinates is worse than no validator, and because this path has to be debuggable at 9am when someone says the bot lied.
Roughly two dozen ordered steps, in three families.
Repair
Strip leaked reasoning ("let me check..."). Remove unsolicited follow-up offers. Extract markdown tables out of prose into structured fields — and when the model narrated results without tabulating them, rebuild the table from raw tool output. Normalize headlines to one line. Fix self-contradictory metadata.
Reconstruct
Rebuild the source list from the tool log rather than trusting the model's claims. Query results become table citations; analytics platform calls become chart links. Prepend the actual data window parsed from the executed SQL — "data window: A to B" from the query, not from the model's summary. Auto-generate a chart spec from the table when the model omitted one.
Never let the model self-report its provenance. Models are directionally honest and specifically unreliable, and provenance is exactly where specifics matter.
Gate
The evidence contract: cross-reference every data claim against tools that actually succeeded. Claims with no successful tool and no source get flagged, confidence dropped, and an explicit caveat appended.
The critical design decision: the gate appends, it does not delete.
This is the single most important reversal in the system's history. An earlier, stricter version replaced unsupported-looking answers outright with "I could not verify this" — and it repeatedly destroyed correct answers, because detecting "is this claim supported" has imperfect recall.
Degrading confidence and attaching a caveat is recoverable: the user still gets the analysis and knows to check it. Deleting a correct answer is not: the user gets nothing, and loses trust in the tool.
When your detector is imperfect, prefer the mechanism whose false positives are survivable. Asymmetric costs should produce asymmetric mechanisms.
Confidence and retry
A confidence score composes the signals — unresolved errors, conflicting results across sources, repeated identical calls indicating thrash, single-call give-up risk — into one number, with a caveat attached below a threshold.
And a set of retry triggers sits upstream of all this, inside the loop: an answer that is pure narration with no numbers, or a data question answered without data, triggers a retry with prior findings re-injected rather than being shipped.
Encode known-bad query shapes as executable guards
Some errors are structural and recur forever. The canonical example: a table holding daily snapshots of a cumulative metric, where summing a week of rows multiplies the true value several-fold. Every new analyst makes this mistake. So does every model.
A playbook rule helps. A guard in the query path that rejects the shape outright is what actually stops it.
Institutional knowledge that can be expressed as code should be code. Prose scales with attention; code does not need any.
Subsystem: operations
Rendering. Chart specs render through a hosted chart-image service rather than a headless browser — dramatically less operational surface for a system whose job is analysis, not image processing. Tables exceeding chat-client limits trigger a spreadsheet export path. A small model writes chart titles.
Scheduling. Scheduled work — daily alert digests, weekly scorecards, automated alert triage — dispatches prompts through the same query endpoint as human questions. One pipeline, one set of guarantees, one place to debug. Scheduled jobs are declarative config files, so adding one is a file rather than a deploy.
There is a further benefit: scheduled jobs exercise the user path continuously, so a regression surfaces on a schedule instead of on a user.
A caution from experience. Four scheduling mechanisms accumulated — in-process cron, host timers, CI cron, cloud events — because each was locally the easiest choice at the time. Collectively they are a maintenance problem: no single place shows what runs when, and jobs whose schedulers live outside version control vanish on a host rebuild. The specific trap is that a job configured on a host but not in the repo appears to work indefinitely and then disappears without a trace. Pick one mechanism early and pay the cost of conforming to it.
Evaluation as a deploy gate. A suite of real questions runs against a live staging instance with real tool connections, asserting a shared quality contract — no leaked reasoning, sources present, data window stated when filtered, fail-closed when unsupported — plus per-case assertions and latency budgets.
Unit tests cannot see the failure you care about. A test suite verifies the post-processor's steps. It cannot tell you the agent quietly stopped citing sources, that answers got 40% slower, or that a prompt change made it chatty. Live evaluation is slow, occasionally flaky, and the only thing that catches quality regressions.
Staging that's actually reachable. A second full instance runs alongside production. A prefix on a chat message routes that one question to staging, proxied internally. Real interface, real data, real tool connections, zero production risk — and anyone on the team can do it without a local environment. This is the highest-leverage testing affordance in the system.
Deploy safety. The pipeline checks the in-flight query count before restarting and refuses to kill live work. Trivial to build, and it converts "deploying might kill someone's three-minute investigation" into a non-issue.
Feedback. Thumbs up/down on every answer, with a reason picker and a free-text correction on negative.
Corrections are the highest-value signal the system produces — a domain expert telling you exactly what the agent got wrong is worth more than a thousand thumbs-ups. The pattern that follows: turn a correction into a proposal bundle — the feedback, a new eval case built from it, and a suggested knowledge patch — for human review, rather than auto-patching.
The trap is building capture and generation but never wiring the last mile. Corrections then accumulate in a directory nobody opens. A half-connected learning loop is not a learning loop. Decide up front who reviews the proposals and when, or do not build it.
If you're building this
Build in this order. It front-loads the parts that decide whether the thing works at all.
Stage 1 — Prove the loop. One data source, one hardcoded question, a CLI. No chat, no async, no knowledge layer. You are answering one thing: can the model plan and execute a real query and return a structured result? Ship nothing until the answer is yes.
Stage 2 — Make it wrong loudly. Before adding features, add the evidence ledger: record which tools ran and succeeded, and cross-reference claims against them. You want the system's failure mode to be visible before you have users. This inverts the usual order deliberately.
Stage 3 — The knowledge layer. Three or four playbooks with annotations, startup indexing, keyword matching. Skip the model-based classifier initially; keywords get you most of the way. This is the stage where answer quality jumps.
Stage 4 — Chat, async. Front door with signature verification and retry dedupe, async handoff, background execution, out-of-band reply. Add the placeholder-and-progress pattern immediately.
Stage 5 — Post-processing. Repair and reconstruct steps, then the confidence score. Resist the urge to make the gate destructive.
Stage 6 — Evals, then everything else. A dozen real questions with quality-contract assertions, wired as a deploy gate against a staging instance. Only now add more data sources, scheduling, charts, feedback. Each of those is straightforward once stages 1–6 hold; none of them saves you if they do not.
What I'd do differently
Consolidate scheduling from day one. The sprawl was death by locally-reasonable decisions.
Secrets in a managed store, not environment files. A hand-managed env file on a host is fine at one instance and a liability the moment there are two.
Terminate TLS in front of the agent host. A shared-secret header over plain HTTP is not authentication; it is a speed bump. A reverse proxy with a certificate costs an afternoon.
Treat docs as code that rots. Architecture docs drifted from reality in ways that actively misled — including a system-prompt instruction pointing the agent at a tool that had been disabled by configuration. The agent's own system prompt is the worst place for this, because the model follows it faithfully off a cliff. If a document names a tool, a flag, or a host, add a test that asserts it exists. Documentation that cannot fail will eventually lie.
Choose one agent execution path. Maintaining both a framework-based loop and a hand-rolled one meant two code paths with subtly different tool availability, and a config flag deciding which was live — with docs asserting one answer and configuration asserting the other. Migrations should end in a deletion. Until they do, they are not finished.
Design the correction loop end to end. Building feedback capture and the proposal generator but never wiring the last mile meant the highest-value signal in the system accumulated where nobody read it.
The experiment agent that preceded this one could run without oversight because the model was never allowed to make a call — every decision was computed in SQL first. This system cannot make that guarantee, and the entire architecture above is what replaces it.