Building a Reporting Agent: Structured Analysis Tools or LLM-Generated SQL?

Building an internal reporting Agent for operations teams sounds like magic — turning natural language questions into charts on demand. But when you actually sit down to implement one, the architectural trade-offs you make will directly determine whether the system produces correct numbers, not just queries that run without errors.

A developer shared their tech stack and challenges on Reddit: Python, LangGraph, FastAPI, and React/ECharts, with all data sourced from the company's HTTP APIs. After a user asks a question, the Agent fetches data, analyzes it, and returns a chart. A typical scenario: pull attendance records and student rosters from different APIs, join them on person_id, filter for late arrivals, then count tardiness by class. This seemingly simple requirement hides the core challenges of AI Agent data analysis architecture.

The Pain Point with the Current Approach: Data Flooding the Conversation History

The developer's current approach breaks functionality into a set of fine-grained tools — data fetching, grouping, aggregation (count, max, etc.), and charting are all separate. Tools pass datasets between each other using dataset IDs rather than having the model repeat raw records back — this is the right instinct, since having the LLM copy data row by row wastes tokens and is error-prone.

The problem is where the data lives. Although complete datasets are stored in tool-message artifacts, the message content still returns too much raw data. As conversation turns accumulate, the context window gets crowded with raw records, eventually triggering history truncation — and that truncation causes the model to lose critical schema and business definition information.

The developer's ideal state: large datasets always stay outside the conversation history, and the model only sees IDs, schemas, business descriptions, and small-scale result previews. This thinking is sound. The real decision is how to let the model express the analytical operations it wants to perform.

LangGraph's persistent state mechanism is the key architectural infrastructure here. LangGraph lets developers split Agent runtime state into two parts — "message history" and "custom state fields" — each managed with independent truncation and persistence strategies. Message history triggers truncation as the conversation grows, but custom state fields can be configured to always persist and never participate in truncation logic. Storing dataset ID mappings, schema summaries, and business metric definitions in custom state fields means that even after dozens of conversation turns, this metadata remains fully intact and won't be lost when the history window shrinks. This is fundamentally different from writing metadata into regular messages — once the latter exceeds the context window, it's permanently invisible, whereas the former can be re-fetched at any time via tool calls. When designing a LangGraph state schema, explicitly distinguishing between "conversation content" and "session metadata" is an important engineering decision for maintaining stability in long conversations.

The Trade-offs Between Two Approaches

The developer proposed two alternatives to the current fine-grained tools, each with clear risks.

Option 1: Structured Analysis Tools

The model provides dataset IDs, predefined join relationships, filter conditions, grouping fields, and aggregation operations, and the backend validates and executes them with Pandas or DuckDB. The advantage is that operations are strictly constrained to predefined semantics — the model can't generate requests that can't be parsed.

But the developer astutely identified the hidden danger: you'll gradually invent your own query language. As business requirements grow, you'll need to support more complex filters, nested aggregations, and multi-table joins. Every expansion adds another brick to this private DSL. You may end up re-implementing a broken version of SQL, and then having to maintain its own parser, validator, and documentation.

Option 2: LLM-Generated SQL

API responses are converted into tables in DuckDB, the model receives schema and business metadata and generates SQL directly, which is validated and executed, with results stored and passed by ID to the charting tool. SQL is expressive enough, is a mature query language validated over decades, and models have been trained on extensive SQL data.

Its risks are more subtle and more dangerous: syntactically correct queries that execute successfully but produce wrong numbers due to incorrect joins or misunderstood metric definitions. For example, a one-to-many join causing duplicate counting, or "number of tardiness incidents" being misinterpreted as "number of distinct people who were late." These errors won't throw exceptions — they'll quietly produce a chart that looks professional but misleads decision-making.

DuckDB's role in this architecture deserves a separate note. It's an embedded columnar analytics database that runs directly inside a Python process with no separate service to deploy. Its core strength is being able to register Pandas DataFrames, JSON, or even Parquet files as virtual tables, making API-returned data immediately queryable with SQL — this makes the "convert API responses to tables, then analyze with SQL" workflow low-friction. DuckDB has fairly complete standard SQL support, including window functions and CTEs (Common Table Expressions), which are common requirements in reporting scenarios. Compared to pushing data into traditional databases like PostgreSQL, DuckDB eliminates the overhead of network transfer and connection management, making it well-suited for medium-scale ad hoc analytical datasets. However, its embedded nature means that multi-process concurrent writes require additional design consideration — if the Agent handles high concurrent request volumes, you'll need to evaluate whether a standalone query engine is necessary.

The Core Challenge: Correctness Is Much Harder Than Running Without Errors

Comparing the two approaches, the real point of divergence isn't Pandas vs. SQL — it's how to guarantee the correctness of analysis results. This is also the most worth exploring part of the community discussion.

For how to expose schemas and business definitions without blowing up the context window, a viable approach is layered delivery: by default, give the model only a condensed table structure and field business-meaning summary; when the model explicitly needs details about a specific table, let it pull the complete definition on demand through a dedicated "query schema" tool. This preserves information accessibility while avoiding full metadata being carried in every conversation turn.

For recovering this information after history truncation, critical metadata needs to be decoupled from conversation history. Schemas, business definitions, and dataset ID mappings should be stored in independent state storage (such as LangGraph's persistent state), not dependent on conversation messages. This way, even if message history gets trimmed, the Agent can still re-acquire context through tools.

How to Catch Incorrect Joins and Duplicate Counting

This is the hardest and most easily overlooked part of the entire system. Syntax checking is completely unable to catch these semantic errors — additional protection layers are needed:

• Row count conservation checks: Changes in record counts before and after a join often expose unexpected Cartesian products or one-to-many expansion. If row count after the join far exceeds expectations, a warning should be triggered.
• Join key uniqueness validation: Before executing a join, verify that the join key is truly unique on the "one" side of the relationship, preventing duplicate counting caused by duplicate keys.
• Result reasonableness preview: Return a small-scale result preview to the model, giving it an opportunity to do a sanity check on the order of magnitude of the numbers, or perform a self-check before returning results to the user.
• Predefined metrics instead of ad hoc calculations: For critical business metrics like "number of tardiness incidents," rather than having the model improvise aggregation logic each time, solidify the precise definition in the metadata layer to reduce ambiguity.

Cartesian Products are the most typical class of join errors, and understanding them helps in designing protective rules. When two tables are JOINed on a key and that key has duplicate values on either side, the result row count multiplies — for example, if the attendance table has 3 records for the same person_id, and the student roster also has that person_id appearing twice due to data issues, the join produces 6 records for that student, causing tardiness counts to be double-counted. This inflation won't trigger any SQL error — the result looks completely normal. The essence of a "row count conservation check" is numerical validation of this phenomenon: if the row count after the join exceeds the row count of the "many" side table before the join, it usually means duplicate keys exist on the "one" side. In practice, before executing a JOIN, you can detect duplicate keys using the difference between SELECT COUNT(*) vs COUNT(DISTINCT key), and inject the detection result as a hint to the model, letting it decide whether to proceed, adjust the SQL, or ask the user for clarification.

Practical Recommendations

From this discussion, a pragmatic direction emerges: the two approaches are not mutually exclusive. The SQL approach has stronger expressive power and a more mature ecosystem — it's the long-term path worth investing in, but it must be paired with a correctness protection layer. The structured tool approach is better suited as a fallback for scenarios where business query patterns are relatively fixed and high determinism is required.

Regardless of which path you choose, these principles apply universally: keep large datasets out of conversation history, interact with the model through IDs and schemas, and consolidate business definitions into reusable metadata. The real engineering challenge was never about getting the Agent to render a chart — it's about making operations teams confident enough to trust the numbers on that chart.

This article is based on technical discussions from the Reddit developer community and represents a single-source experience-sharing and architecture exploration. Specific approaches still need to be validated against your own business context.