Building Recon: an AI analyst that shows its SQL

Introduction

Every store owner ends up asking the same four questions: how many orders are completed, what the profit and loss is, what total sales were, and which product sells the most. Answering them usually means a metrics layer, a dashboard, and a product manager translating one into the other.

Recon takes a different route. You ask in plain language, an agent investigates the database with read-only SQL, and it answers with the number and the query that produced it.

This is a case study of how it was built, the parts that were harder than expected, and the places where it is still held together with honest caveats.

The Context

The stack was fixed before any code: TypeScript on Node, Express, Supabase (Postgres plus Auth), Drizzle, and LangChain with LangGraph. Two constraints shaped almost every decision.

The agent reads, it does not write. It can query the business tables — customers, products, orders, order items, returns, expenses — and may only write to two tables it owns: reports and agent_runs. Destructive SQL is never allowed.

No tools until they were chosen. The project brief was explicit that no agent tools were pre-registered or invented "for later". The agent started with a model, a prompt, and nothing else.

That second rule sounds like a limitation. It turned out to be a useful forcing function: the first version of the agent had to say "I cannot query the database yet" rather than guess a number, which made the empty scaffolding visibly honest instead of plausible-looking.

The Approach

Tools are where the risk lives

Two tools, both small:

• read_schema lists every table and column. It is generated from the Drizzle definitions rather than a hand-written string, so it cannot drift from the migrations, and it works with no database connection at all.
• run_query executes one statement and returns rows as JSON.

The interesting part is the guard. A prefix check that only allows select and with is not enough, because a data-modifying CTE also starts with with. So the query runs inside a read-only transaction, with a statement timeout, and Postgres itself refuses the write:

1const rows = await getClient().begin(async (tx) => {
2  await tx.unsafe("set transaction read only");
3  await tx.unsafe("set local statement_timeout = '10s'");
4  return tx.unsafe(sql);
5});

Two layers, because each one alone is insufficient: the prefix check gives the model a fast, readable rejection, and the transaction is what actually holds.

Structured output, and one API rule nobody mentions

Free-form markdown was the first version, and it rendered as literal asterisks in the UI. The fix was to make the model return a schema instead: a headline, prose, metric tiles, one data table, and the tables it read.

That meant passing a zod schema as the agent's responseFormat. The first attempt failed against OpenAI with:

1400 Invalid schema for response_format 'extract': 'required' is required to be
2supplied and to be an array including every key in properties. Missing 'metrics'.

Strict structured outputs require every property to be listed in required. Optional and nullable fields are not the escape hatch; "nothing to show" has to be encoded as an empty array. Once the schema expressed absence as [] rather than null, it validated.

Memory that survives a follow-up, and nothing more

A second question in the same conversation should inherit context. The agent itself is stateless, so a session is a bounded transcript keyed by a conversation id the client sends.

Only the user's question and the final answer are replayed — three exchanges at most. Replaying prior tool calls would grow the prompt every turn and risk pairing a tool result with a missing call.

The component that made this verifiable was the control. Asking "and the month before that?" produces completely different answers depending on whether the conversation is shared:

Same words, different meaning, purely from carry-over. Without that control it is easy to believe a model is remembering when it is only being agreeable.

Note: Only this turn's messages count toward the token and SQL figures. With a transcript in play, summing every message would report a cumulative total rather than the cost of the answer you just read.

Reports are a background job

Saving a run as a report copies its headline, answer, and SQL into reports, marks the PDF pending, and hands the work to an Inngest function. That function builds a printable HTML document from the stored run, prints it with Chromium, writes the file, and flips the row to ready. A failed render lands in onFailure, so the row is marked failed with the error rather than sitting on pending forever.

Results and Trade-offs

The agent answers the four questions against a seeded database of 2,400 orders, 1,925 of them completed. Verified end to end: a live question returns a structured answer in roughly 7 seconds and about 3,000 tokens; a run is persisted with its duration and token count; a report becomes a 54 KB, one-page PDF that contains the answer, the metrics, and the data table; and a PDF renders in about 250 ms once Chromium is running.

The trade-offs are less tidy than the numbers:

• The model never does arithmetic. Every figure comes from SQL, which is why the answers hold up. It also means a badly formed question produces a badly formed query, and the user sees the failure rather than a plausible guess.
• Cost is per question, not per session. Roughly 3,000 tokens per run, and the transcript adds a little each turn.
• Memory is in-process. A restart forgets conversations while the tagged runs remain in the database.
• PDFs live on local disk. On a platform with an ephemeral filesystem they disappear on redeploy, leaving ready rows pointing at nothing. A volume or object storage is the real answer.
• PDF generation needs a browser. A Node buildpack image has no Chromium, so reports fail at the render step with a clear message. A container image that installs it is the honest requirement.

The deployment itself had the most surprising failure, and it had nothing to do with application code. The build died with Cannot find module .../pnpm/12.15.0/bin/pnpm.cjs. The cause was Corepack: versions up to 0.34.4 record pnpm 12 with a bin path pnpm no longer ships, and later versions reuse that stale cached record. The shim existed; the module behind it never did. Installing pnpm with npm inside the container sidesteps Corepack entirely.

Key Takeaways

• Put the safety in the database, not only in a string check. A read-only transaction survived a CTE that the prefix filter would have let through.
• Validate the model's output shape, and let a bad shape fail the run. A malformed answer recorded as failed is better than a half-formed one rendered as if it were true.
• Give the interface the data, not the presentation. When the model chose chart points, they did not fit; when it returns a table, the interface can draw whatever it likes.
• Prove memory with a control. The same ambiguous question in two contexts is the only convincing test.
• Read the platform's build errors literally. A missing module under a Corepack cache path described a bug in the toolchain, not in the application.

Have a question about this topic? Get in touch.