Anyone can wire an LLM to a database in an afternoon. Making it correct enough to put in front of real users is the whole job, and almost none of it is prompting.

You have seen the demo. Someone types "how many orders last quarter?" into a chat box, an LLM writes some SQL, a number comes back, the room claps. It looks solved.

Then you ship it, and the trouble shows up in three shapes:

• It answers a slightly awkward question by inventing a number, confidently.

• It returns the right total for the wrong customer.

• The same question takes 2 seconds one time and 90 the next.

An analytics assistant that is occasionally wrong is worse than no assistant, because people trust it. I spent months turning that clapping-room demo into something dependable. The lesson that surprised me: the hard part is not the LLM. It is everything you build around the LLM to keep it in line.

Below is the model I ended up with, the six bugs that taught it to me — three from building it and three from real users breaking it — and what I would tell you if you are starting now.

Draw a hard line between understanding and doing

An LLM is very good at reading vague human intent and quite bad at producing exact, repeatable output. So the first decision, the one the rest of the design depends on, is to give it only the first job.

The LLM understands the question. Deterministic code does everything after.

The model reads "top performers by earnings last month" and produces a small, validated plan: this metric, this filter, this time range, aggregate not list. That is all. From there plain code takes over. It composes the SQL from templates, runs it, checks it, and renders the table. Same plan in, byte-identical SQL out, every time.

Non-determinism is fine in the part that reads intent. It is not fine in the part that produces answers. Put the line there and most of your problems get easier.

The pipeline

Not every question needs the same machinery, so questions fall through a cascade, fastest and most-trusted first:

Four things in that diagram carry most of the weight.

Auth is the first gate, and it fails closed. A request with no authorization context does not get a helpful "here is everything." In production it gets declined. The scariest bug in a multi-tenant system is not a wrong number. It is the right number for the wrong tenant.

Row security is enforced at one chokepoint, right before execution, not scattered through the code that builds queries. Every path, including the freeform one, funnels through the same rewrite that injects the tenant's org filter into the SQL. The LLM never writes the tenant filter, so a prompt-injected "show me all orgs" cannot remove it. And the caller's role never overrides the org: an admin from a customer org is an admin inside their org, not a skeleton key. We learned that one the hard way, when a role header briefly beat the org header and an admin from one tenant could read everyone's rows.

The rendering step is deterministic code, not the model. The LLM never draws the table. I will get to why in a minute.

Every dead end is an honest decline, never a fabrication. Real SQL, or "I cannot answer that, here is why." There is no third door where it guesses.

That is the theory. It came out of specific bugs — the first three while building, the next three at the hands of real users.

Three bugs that taught me the architecture

1. The table that turned into a paragraph

A user reported that answers showed up as a clean table while streaming, then collapsed into a run-on paragraph the moment the response finished. I looked at it for a while before I checked the raw model output.

The LLM was emitting markdown tables without the |---| separator row, but only sometimes. A valid table mid-stream, a broken one on completion, and the renderer dutifully showed the broken one as prose.

My first fix was a frontend patch that detected the broken pipes and repaired them. It worked. It was also a band-aid, because now I was maintaining a parser for my own model's mistakes. The real fix was the same line from earlier: stop letting the LLM produce the table at all. The model returns a one-line headline. Deterministic code builds a valid table from the structured rows. Broken tables became impossible, and I deleted the frontend hack.

The lesson: the moment you are writing code to clean up your LLM's output shape, that is the LLM doing a job it should not have.

2. The 90-second hang

A query that used to be quick suddenly took about 110 seconds. The logs blamed a single stage that sat there for 106 of them. The database was not the problem. It answered in milliseconds. A cold LLM call was hanging against a 90-second timeout, then retrying into another one.

The deeper issue was an incoherent timeout stack. A per-attempt timeout, a retry count, and an outer deadline had drifted out of sync, so one slow call could swallow the whole request. I rebuilt them into one hierarchy, with the outer deadline larger than the per-attempt timeout and bounded retries. I also found a streaming path with no deadline guard at all and added one.

The lesson: timeouts are a system, not a decoration you sprinkle per call. If your inner timeout can exceed your outer deadline, it is not really a timeout.

3. The latency floor you cannot optimize away

With the timeout fixed, the freeform-SQL path still swung between 10 and 30 seconds for the same question. I instrumented everything. The prompt was fully cached and the database was instant. The variance was the reasoning model thinking: 268 reasoning tokens one run, 1,553 the next, and I paid for every one.

There is no config flag for that. It is how reasoning models work. I shaved off a reliable chunk by lowering reasoning effort on simple queries and dropping a redundant verification step, then I wrote the floor down honestly instead of pretending I had beaten it. The proper fix, caching generated SQL per question-shape or routing trivial SELECTs to a non-reasoning model, is future work I chose not to do yet.

The lesson: measure before you optimize, and be honest about the floors you cannot move. "90s down to 30s, and here is why it will not easily go lower" is a better story than a fake win.

Then real users arrived

The three bugs above shaped the architecture. The next three showed up only after real people started asking real questions, and they were a different kind of lesson: not "the system is broken" but "the system is technically right and the user still cannot trust it."

4. The guard that gave dead-end advice

We have a telemetry table where every machine reports thousands of historical snapshots. A user asked how many machines run a certain software. The generated SQL scanned every historical snapshot, which would have blown a 60-second timeout, so a cost guard stepped in and declined: "please narrow to a single org." The user narrowed to a single org. Same decline. They narrowed again, word for word. Same decline.

Two real defects were hiding under that one conversation. The query shape was wrong: a "does this machine have X right now" question should read only each machine's latest snapshot, which turns a 60-second timeout into a 2-second query. And the guard's advice was canned: it suggested narrowing by org to a user who already had. The fix for the second one generalizes to every guard you will ever write: before you advise, check what the query already binds, and only suggest moves the user has not made. Advice that repeats what the user just did is its own kind of fabrication. An honest decline must leave the user a move.

5. The wrong zero

A user asked how many machines have "Manage Engine" installed. The data spells it "ManageEngine". Substring match, zero rows, and the system confidently reported that no machines have it, when in truth 2,062 of the org's 2,063 machines did. Another user asked for tickets "from projects other than TCL RF." The classifier had no way to represent "other than," so it silently bound the value positively and counted only the excluded project. Which also matched nothing, because the stored name is "TCL_RF_Support" with underscores.

Zero is the deadliest wrong answer, because it looks exactly like a real one. Nobody double-checks an empty result the way they squint at a suspicious total. Three fixes came out of this, all class-level, none of them per-phrase patches: every place a user-typed name meets a stored value now matches with separators collapsed, so spacing and underscores cannot zero out a real match; exclusion became a first-class flag the planner can set instead of a nuance it silently drops; and a structural verifier now checks that a negation the user asked for actually appears in the SQL, because a plan intending to exclude something is not evidence that the SQL does. Intent is never evidence. Verify the output, not the plan.

6. Right answers that looked fabricated

This one hurt the most, because nothing was wrong. A customer admin called the API with their org's identity headers and asked for their open tickets. The answer was 275, which was exactly, provably, their org's number. They filed it as a bug: "random value."

Why? The response included the SQL for transparency, and they did the diligent thing: ran it by hand. It returned 106,483. The security wall injects the tenant filter at the execution chokepoint, after the display SQL was captured, so the response showed the pre-wall query. Correct answer, unverifiable receipt. To a careful user that is indistinguishable from a made-up number, and careful users are exactly the ones you want to keep.

The fix was small and the lesson was not. Responses now carry the SQL that actually executed, wall included, plus a marker saying what the result was scoped to. Re-run it by hand and you get the same number the assistant gave you. Correctness the user cannot verify reads as fabrication. Trust needs receipts.

And one classic from the same week, as a bonus: a stray local variable named _err shadowed a module-level helper of the same name inside a closure, which turned every error-recovery path into a crash. It hid for three days behind what looked like random LLM timeouts, and we found it only by refusing to write off a "flaky" test. When a failure looks random, it is usually two bugs stacked, and the boring one is load-bearing.

What I would tell you if you are starting now

Treat LLM non-determinism as an architecture problem, not a testing one. Every full test run failed a different single case, each of which passed on an isolated re-run. That is the model flipping a coin, not a regression. The wrong move is to weaken tests until they go green, because then you are blind to real breakage. The right move is to shrink the surface exposed to the coin flip and push as much as you can onto deterministic paths. You will never make an LLM deterministic. You can make most of your system not depend on it.

Ban band-aids and root-cause everything. Every quick patch I added under pressure I later had to remove, and each one had been hiding the real bug. The rule that kept paying off: when you find a band-aid you wrote earlier, delete it and fix the cause.

Ship behind flags. Every behavioral change went out default-off, got validated with the flag on, then became the default, with the old behavior one env var away. That turns a scary change into a reversible one, which is the only kind worth making to something people rely on.

Treat deployment as a config audit. Before go-live, sweep for hardcoded hosts, stray absolute paths, and fail-open security defaults. A hardcoded dev path is embarrassing. An auth flag that defaults to "see everything" in production is a breach. Know which one you are looking at.

And the boring one that beats all the others: protect your secrets. Every key and password lives in a git-ignored env file and gets pasted nowhere public. If one ever leaks, in a screenshot or a log or a chat, rotate it immediately and treat it as burned. The best architecture in the world dies to one leaked credential.

The takeaway

A trustworthy natural-language data assistant is not a cleverer prompt. It is a system that treats the LLM as one component, a strong but unreliable interpreter, and wraps it in deterministic generation, honest failure, tenant-safe access, verifiable answers, and steady root-causing.

The demo is the easy part. The trust is the job.

Draw the line between understanding and doing early. Refuse to fabricate. Show the SQL you actually ran, not the one you meant to run. And be honest, in your product and in your write-ups, about what you have not solved yet.