Overview#

A natural language report generator that turns a plain-English description into a printable Apache Velocity PDF. The AI plans the sections, writes the SQL behind each one, generates the XHTML template, validates it for well-formedness, and renders it through Apache Velocity to HTML and on to PDF via WeasyPrint. A vision-aware feedback chat lets users refine the layout by attaching a screenshot and saying “move the totals to the right” or “make the header logo bigger.” Reports are saved, organized into categories, and reloadable in seconds with fresh data from the live database.

This is Part 3 of the series. Part 1 and Part 2 covered the dashboard renderings, the same AI pipeline emitting six-card visual analytics. Same database. Same schema retrieval. Same Claude planner. Different front-of-house: a page, not a panel. A document, not a dashboard.

The Gap Dashboards Don’t Close#

Dashboards answer questions. Documents are how those answers get distributed to the rest of the business. The accounts-receivable team does not want a profit-margin scatter plot. They want an invoice. Operations does not want a heatmap of order volume. They want a packing summary. Finance does not want a donut of revenue by region. They want a quarterly statement they can email to a CFO and print on letterhead.

Every organization that ships a BI tool eventually rebuilds the same kind of report templates by hand. Crystal Reports. SSRS. Jasper. Servoy’s own VelocityReport plugin. The shape of the work has not changed in twenty years: an analyst writes the SQL, a developer hand-codes the layout, somebody else owns the printer-safe CSS, and three weeks later the customer gets their first PDF. Every change request adds another iteration.

How a Description Becomes a Document#

Figure 2 - End-to-End Pipeline: A natural-language description enters the AI pipeline. Schema retrieval pulls the relevant database views from a vector index. The report planner decides on a section list, header through footer, with a layout hint per section. Each data section gets its own validated SQL query. The Velocity template generator writes a single XHTML document that knits sections together with Velocity bindings. A two-stage validator checks XHTML well-formedness and applies safe rewrites before either Apache Velocity (via airspeed) renders the HTML preview or WeasyPrint converts it to PDF.

The pipeline reuses every reliability layer from the dashboard project: the same view-first schema retrieval, the same MS SQL Server prompt rules, the same automatic post-processing fixes, the same Redis caching tiers, and the same retry safety net. The novel pieces sit at the start and at the end. At the start, a report planner replaces the dashboard planner: instead of choosing six visualization types, it chooses up to eight section types. At the end, an XHTML template generator replaces the chart-mapping layer.

The section-planning step is where the personality of a report comes from. The AI selects from a palette of layout hints: header, address block, metadata grid, line-item table, summary, detail block, and footer. An invoice gets a header, an address block for the customer, a metadata grid for invoice number and dates, a line-item table, a summary block, and a footer. A customer statement gets a different mix. A summary report gets fewer sections, broader queries, and bigger text. The structure follows the question.

The Input Panel: Three Fields and an Honest Spinner#

Figure 3 - Report Input: The whole entry point. A multiline description (“Top 10 customers in revenue for the last 3 months, include a customer detail breakdown for sales team follow-up”), a Report type dropdown that defaults to “Auto-detect from description,” a Page size dropdown that defaults to Letter, and a single Generate Report button. The five report types (Invoice, Statement, Summary, Detail, Custom) bias the planner toward shapes the user already has a mental model for. Leaving it on Auto-detect lets the AI choose. Page size offers seven options (Letter, Letter Landscape, Legal, Legal Landscape, Tabloid Landscape, A4, A4 Landscape) — landscape for wide tables, portrait for narrow ones.

The input panel is small on purpose. Three controls. No advanced settings, no template picker, no layout chooser. The whole point of the natural-language layer is that the user does not know which Velocity primitives produce a clean invoice. The AI does. The user describes the document they want to hand to a customer.

The Report Type dropdown is a hint, not a constraint. “Invoice” tells the planner to expect a header, address block, line items, and totals. “Statement” tells it to expect a date-bounded list of transactions and a running balance. “Summary” tells it to keep section count low and column count tight. “Detail” tells it to do the opposite. “Custom” gives the planner full latitude. Most users leave it on Auto-Detect because the description usually carries the intent.

The Generate Report button doubles as an honest spinner during the run. It cycles through four named stages on a timer: “Analyzing schema…” (Qdrant retrieval), “Planning sections…” (the report planner), “Generating template…” (the Velocity generator), and “Validating XHTML…” (lxml well-formedness). Each stage matches a real backend step, so the user always knows where the pipeline is and which step is taking the time on the rare slow run. A 60-second wait without feedback feels broken; a 60-second wait with four named stages feels like progress. We treat the loading state as part of the product, not an afterthought.

The Preview: Two Tabs, One Source of Truth#

Figure 4 - HTML Preview: The HTML tab renders the Velocity template through airspeed inside a sandboxed iframe at exactly 680 pixels of height. What you see is what airspeed produced from the live SQL results, rendered with the template’s own CSS. The Print button at the top-right calls the iframe’s contentWindow.print() so the browser uses the document’s @page rules instead of the surrounding chrome. The page size and orientation come from the user’s selection in the input panel — Letter portrait by default, with Letter Landscape, Legal, Tabloid, and A4 (portrait or landscape) on offer for wider data sets. 20mm margins, no surprises.

The HTML preview is the fast path. Velocity rendering completes in under a second. The iframe is sandboxed: same-origin disabled, no script execution, no network. The template gets to lay itself out with its own CSS, the user gets to see it the way Velocity produced it, and the host application stays insulated from any markup the AI happened to generate.

Figure 5 - PDF Preview: The PDF tab lazily renders the same template through WeasyPrint and shows the result in an embed element. A “PDF is stale” banner appears in amber when the user has applied a feedback change but has not yet asked for a new PDF render. The Refresh button rebuilds the PDF from the current template. The Download PDF button hands the user a real file with a sanitized filename derived from the report title. Spaces become underscores; non-alphanumeric characters get stripped. “Quarterly Sales Statement” downloads as “Quarterly_Sales_Statement.pdf.”

We split HTML and PDF previews because they have different cost profiles. HTML through airspeed is effectively free. PDF through WeasyPrint is the slow step: font loading, CSS layout, page break calculation, glyph embedding. Rendering the PDF eagerly on every iteration would punish the user for every chat refinement. Lazy-on-tab-switch is the right default. The stale banner makes the freshness model explicit.

KEY INSIGHT: Two preview tabs are not just convenience. They separate the cheap iteration path (HTML) from the expensive certification path (PDF), and they let the user feel the cost difference. The user iterates in HTML, certifies in PDF, downloads when they’re ready.

SQL Disclosure: Show Your Work#

Figure 6 - The Generated SQL: The accordion shows one entry per data-bound section. Each entry’s title is the humanized section ID with a one-line purpose underneath. Expanding a section reveals the exact SQL the AI wrote, syntax-styled in a monospace block, with a Copy button that copies the query to the clipboard. Users can read the query, run it in their own SSMS, or paste it into a ticket if a number looks wrong.

A report that prints the wrong total is worse than no report at all. The SQL disclosure exists because users will see numbers they want to verify, and “trust the AI” is not a verification strategy. Every data-bound section in the report has a corresponding SQL query. Every query is visible. The Copy button is one click. A skeptical user can take the query, run it themselves, and confirm the report.

This pattern is identical to the SQL disclosure in the dashboard projects. The principle is the same: an AI tool that cannot show its work is a black box. Showing the SQL turns “the AI said so” into “here is the query that produced this number, run it yourself if you want.”

Feedback Chat: Tell It What’s Wrong, or Show It#

Figure 7 - Feedback Chat with Vision: A user attached a screenshot of the current Summary report (visible as a thumbnail above their message), asked for a layout adjustment in plain English, and sent it with Ctrl+Enter. The assistant’s response streams in token by token with a pulsing cursor, ending in an Apply Changes button at the top-right of the panel. Clicking Apply re-renders the HTML preview with the new template. The PDF preview marks itself stale. The user iterates again.

The feedback chat is where natural-language reporting earns its name. Users do not describe layout the way developers describe layout. They say “move that thing over there.” They circle a region in a screenshot. They say “make this look more like the example.” Three things make this loop work in practice.

Image attachments are first-class. The Attach button takes any image up to 5 MB. The thumbnail appears inline in the user’s message bubble. The image is base64-encoded and shipped to Claude’s vision API alongside the text. The model can see what the user sees. “The header logo is too small, the customer address is overlapping the totals” is a perfectly solvable problem when both you and the model are looking at the same screenshot.

Streaming means the user sees thinking. The assistant’s response streams over server-sent events. A pulsing cursor signals live output. A Stop button cancels the stream and marks the partial response with a “[cancelled]” tag, leaving the message intact in the history. There is no spinner anxiety. There is no wondering whether the request hung.

Apply is explicit. The model returns an entire revised template. The user gets a chance to read the chat response, scroll back, decide whether the suggested change matches their intent, and then click Apply Changes. The HTML preview re-renders. The PDF marks stale. The previous version is still in the chat history, three messages back. Reverting is two clicks.

KEY INSIGHT: Vision in the feedback loop is what makes plain-English layout edits possible. “Move the totals” becomes a tractable instruction when the model can see the totals. Without the screenshot, every layout request is a guessing game. With it, the model has the same context the user has.

Validation: Two Hard Layers, One Soft#

A document that fails to render is worse than a document that renders ugly. The pipeline runs every generated template through three checks before the user sees a preview.

The first layer is post-processing. Apache Velocity templates have to be well-formed XHTML, but Claude generates HTML that mostly works in browsers, which is not the same thing. We rewrite known offenders silently: bare <br> becomes <br />, <img> without a self-closing slash gets one, display: flex and display: grid become display: block (because WeasyPrint’s box model is not browser-compatible there), and transform, animation, and transition properties get stripped because none of them exist for a print-bound layout.

The second layer is XHTML well-formedness. The template runs through lxml.etree.fromstring(). Any parse error is hard. The error gets fed back into the next generation attempt, the model gets a chance to fix it, and the system tries again. The retry is bounded at three attempts. If the third attempt still does not parse, the user sees an error message that surfaces the parse failure, with the exact details. In current testing across the report types we ship, the retry safety net rarely fires.

The third layer is soft. A CSS property scanner walks the template’s <style> blocks and inline style= attributes for properties that WeasyPrint will silently ignore. A variable binding check confirms that every $sections.<id> reference in the template matches a section in the report spec. Both checks log warnings to the UI as a collapsible “Generated with N warnings” panel below the input. The report still renders. The user knows what was wobbly.

KEY INSIGHT: Reliable AI-generated XHTML is the same shape as reliable AI-generated SQL. Three layers stacked. Post-processing catches the model’s repeat mistakes silently. A hard validator triggers a bounded retry. A soft validator warns without blocking. The expensive layer (the retry loop) is the one you hope never fires.

Save, Reload, Regenerate#

Figure 8 - The Report Library: A right-side drawer with three tabs: Save, Load, Categories. The Load tab groups saved reports by category, sorted by most-recently-modified within each group. Every entry shows the report name, an optional description, a type badge (Invoice, Statement, Summary, Detail, Custom), and the modified-on timestamp. Four actions per entry: Load (restore the full template and SQL state), Regenerate (re-run the entire AI pipeline against fresh data), Edit (rename, recategorize, or update the description), and Delete (with a confirmation dialog).

A report that took a minute to generate the first time should take a second to reload. Saving stores the template, the report spec, the per-section SQL, and the section data alongside a name, optional description, category, and the original natural-language prompt. Loading restores all of it. The HTML preview re-renders immediately from the stored template and section data, no AI calls required.

Regenerate is the interesting button. Reports save the AI’s output, not the database state. Loading a six-month-old saved invoice replays its template against six-month-old data. Regenerate clears the relevant cache entries, re-runs the entire pipeline against the current database, and produces a new template with new numbers. The same prompt, today’s data. If the prompts or post-processing rules have improved since the report was first saved, regenerate picks those up automatically.

Figure 9 - Categories with Cascade Safety: The Categories tab manages the folder structure. Add creates a new category. Rename cascades: every report tagged with the old category gets updated to the new name. Delete cascades too: every report in a deleted category moves to “Uncategorized.” Both destructive operations show a confirmation dialog with the affected report count. “Uncategorized” is protected and cannot be renamed or deleted, so a report always has a valid home.

The cascade behavior is what stops a category rename from leaving orphans. Without it, a user who renames “Invoices” to “Customer Invoices” would discover later that all their saved reports still reference the old name and have vanished from the new category’s view. With it, the rename quietly walks every record, rewrites the category field, and the user never has to think about the consistency.

Servoy Without a Plugin#

The original promise of this project was a natural-language report generator that integrated with Servoy. The historical answer to that requirement is Servoy’s plugins.VelocityReport, a plugin that takes a Velocity template, a data context, and renders to PDF inside the Servoy runtime.

We chose not to depend on it.

The plugin is not bundled with current Servoy distributions. Installing it requires admin access to the Servoy server, a JAR drop into the plugins folder, and a service restart. Every customer environment becomes a deployment ticket. For a hosted natural-language tool, the plugin is friction the user did not sign up for.

Instead, the web app is the complete rendering stack. The same Velocity template format is preserved in the output, byte-for-byte compatible with plugins.VelocityReport if a customer ever decides to wire it in. Until then, Servoy consumes the rendered output two ways. A bgcolor-styled Web Browser component embeds the React app, and users generate and download PDFs from inside their existing Servoy form. Or, server-side, a Servoy script calls plugins.http.getPageData against /api/report/download/{id} and pulls the PDF bytes for storage, email attachment, or chained workflow.

KEY INSIGHT: The right integration is sometimes no integration. The Velocity template format gives Servoy customers an upgrade path back to the plugin if they ever want it. Until then, an iframe embed and an HTTP endpoint cover both delivery paths without a plugin install.

Same Pipeline, Different Output#

Figure 10 - One Pipeline, Three Outputs: The schema retrieval, prompt rules, post-processing, security validation, retry loop, and Redis caching are identical across all three project front-ends. What changes is the planner (six cards versus up to eight sections), the per-card or per-section generator (chart spec versus Velocity XHTML), and the final renderer (Metabase, ECharts, or WeasyPrint). The reusable middle is what makes adding a new output mode a project-week, not a project-month.

The dashboard projects taught us where the leverage was. Three quarters of the work in a natural-language data product is in the middle: identifying which views to query, getting the SQL right against a real database, building a retry loop you trust, and caching the expensive parts. We had that middle. Pointing it at a new output type meant writing a new planner, a new generator, and a new renderer. The schema retrieval, the SQL agent, the security validation, the prompt rule set, and the Redis cache layout came over unchanged.

The economics of the third project changed shape because the first two existed. The same is true forward. A fourth output type, a Word document, an Excel workbook, an email-ready HTML newsletter, all sit on the same middle. The natural-language layer is in place. The schema retrieval is in place. The SQL is reliable. What changes per output type is the planner’s section vocabulary and the generator’s target syntax.

Key Achievements#

The Full Stack#

Frontend:

• React 18.3 + TypeScript 5.9 + Vite 5.4
• Tailwind CSS v4 + shadcn/ui (Radix-based primitives)
• TanStack React Query 5.9 (server state, save/load mutations)
• Lucide icons, theme selector, two-column responsive layout

Backend:

• FastAPI + Python 3.11
• Apache Velocity rendering via airspeed (pure-Python Velocity engine)
• PDF rendering via WeasyPrint with presentational_hints=True
• lxml for XHTML well-formedness validation
• pyodbc for direct MS SQL Server execution
• Redis for cache and saved-report persistence

AI:

• Claude Sonnet 4.6 via Anthropic API for report planning, SQL generation, Velocity template generation, and the vision-aware feedback loop
• Qdrant for semantic schema matching against precomputed view embeddings
• Sentence Transformers: all-mpnet-base-v2, 768-dimension embeddings

Database:

• MS SQL Server (WideWorldImporters), the same dataset that powers the dashboard projects

What This Changes#

The traditional path from report request to printable PDF is a multi-week round trip through analysts, developers, and CSS-print debuggers. The natural-language path takes under a minute. The user describes the document. The AI plans the sections, writes the SQL, generates the XHTML template, validates it, and renders. If the layout is wrong, the user takes a screenshot, says what is wrong, and the next iteration is in front of them ten seconds later.

The architecture in this article is reproducible. The judgment that shaped it came from the two dashboard projects that preceded it. Every prompt rule we wrote for the SQL agent is still in force. Every post-processing fix for T-SQL still runs. The XHTML post-processing rules came out of watching real Claude outputs fail real WeasyPrint renders. The cascade behavior in the categories tab came out of watching the first user accidentally orphan their saved reports. None of this is in a textbook. It is the residue of two projects that ran first.

KEY INSIGHT: Natural-language layers compound across output types. The hardest part of the dashboard project, getting reliable SQL out of a real database with a real schema, was the hardest part of the report project too. Solve it once, and every subsequent output mode is a planner and a renderer, not a research program.

Closing#

Sixty seconds, not three weeks. Up to eight sections per report, all bound to live SQL. A vision-aware feedback chat that closes the layout loop in plain English. A save-and-reload library with cascade-safe categories. Two preview tabs that separate cheap iteration from expensive certification. Servoy integration with zero plugin install.

The pipeline is the same one that produced the dashboards. The output is a printable document. The throughline across all three projects is unchanged: the user types a question, the system answers it in the format the business needs, and every layer between is built so the slow expensive layer rarely has to run.

Some clients want a BI platform with AI on top. Some want native React dashboards inside an existing product. Some want printable PDFs for the customer-facing side of the same business. The middle of the pipeline is the same in all three cases. That is the principle the series leaves with.

The Series#

This is Part 3 of a 3-part series on natural-language data products built on a shared AI pipeline:

1. Ask Your Database Anything: The Metabase Version: The full-BI-platform version, built on Metabase, for clients who want saved dashboards, drill-through, and scheduled reports alongside the natural language layer.
2. Ask Your Database Anything: Native React Dashboards: The embedded-in-a-product version, native React rendering with ECharts, AG Grid, and Leaflet, no BI platform underneath.
3. Ask Your Database Anything: Printable PDF Reports (this article): The document-shaped version, Apache Velocity templates rendered to PDF via WeasyPrint, with a vision-aware feedback chat for plain-English layout edits.