Agentic Report Generator

The idea was to give users a ChatGPT-style interface connected to the same operational data they already worked with. Instead of choosing tables, writing SQL, exporting results, and assembling a report by hand, someone could ask a question in plain English and let the system work through those steps.

Users could choose from the local models installed on the workstation. Simple questions could return a short answer, while an explicit report request started a longer workflow that planned the research, gathered evidence, created visualizations, and composed a document.

I split the work into stages because asking one model call to understand the question, research the database, calculate results, and write a polished report was too much. The planning stage produced a title, research questions, and section outline. A research loop then chose tools and kept working until it had enough findings. Finally, the writing stage composed each section from the saved evidence rather than relying on the model's memory of earlier tool calls.

The model could inspect relevant schema descriptions, sample rows, check distinct values, and run its own SELECT queries. I added embedding-based schema retrieval so it received the tables related to the question instead of the entire database definition. This helped preserve the limited context window for the actual research and writing.

Every successful query became a saved finding with its purpose, SQL, and result. The final report was grounded in that finding log, and large query results could be handed directly to visualization tools on the server without filling the model's context with thousands of values.

I created chart tools for bar, line, pie, scatter, grouped, stacked, and histogram views. The model supplied a constrained data specification, and the server rendered the image with Matplotlib. The model never generated or executed plotting code, which made the feature more predictable and much safer.

For geographic questions, the model could run a read-only query and turn the result into GeoJSON for an interactive map. The tool supported heat maps, points, clusters, and categories, automatically connected signal identifiers with coordinates when needed, and created a static snapshot that could be embedded in a report.

Report generation could take several minutes, so I made each run an independent background task rather than tying it to one browser request. Progress, model output, tool calls, and errors streamed through Server-Sent Events. If the browser disconnected, the work continued and the interface could reconnect and replay what it missed.

Conversations, reports, charts, maps, and research findings were saved to disk. I also experimented with conversation compaction so longer chats could be summarized before they overflowed the model's context window.

The research agent was allowed to write flexible SQL, but it was not allowed to write to the database. Queries were checked to allow only a single SELECT or WITH statement, capped to a reasonable number of rows, and executed through PostgreSQL sessions configured as read-only with statement timeouts. The database itself was the final enforcement layer.

Chart and map tools reused the same protected query path. Their inputs were validated specifications rather than arbitrary code, so adding a visual did not create a second route around the database or execution safeguards.

The limiting factor was the local model. Smaller models fit the available hardware and responded quickly, but they often stopped their research too early, wrote shallow analysis, or mishandled a complicated sequence of tool calls. Larger models were noticeably better, but took too long for an interactive application on the workstation I had.

I added routing, retries, context controls, SQL repair, automated tests, and evaluation scripts, but those systems could not create reasoning ability that the model did not have. The final prototype demonstrated the architecture and produced working reports, charts, and maps, but it never reached the consistency I would require before giving it to users.

This was useful precisely because it did not turn into a polished production feature. I learned how to break an open-ended task into agents and tools, preserve state across a long-running workflow, ground writing in recorded evidence, and design execution boundaries around generated SQL. I also learned to recognize when better orchestration could help and when the underlying model was simply the bottleneck.