Automated data reports with an AI agent
A reporting agent runs on a schedule, reads numbers from a database, turns them into charts and a short written summary, and sends the result to the people who need it. The key safety choice is to let the model write words, never fetch or compute the numbers: the query is fixed and runs under a read-only database role, and a check compares every number in the summary with the query result before anything goes out. L2M gives you the pieces (Schedule Trigger, PostgreSQL and MySQL query nodes, a Chart node that renders Vega-Lite, PDF Output and SMTP), plus a sample that already does the chart-to-PDF half.
The job
| Trigger | A cron schedule, for example every Monday at 08:00 in your time zone |
| Input | Rows from a reporting view or a few aggregate queries |
| Work | Compute the figures, draw charts, write two or three sentences that explain them |
| Output | An email with the summary and the figures, and a PDF with the charts |
| Must never happen | A number in the email that is not in the data; a query that changes data |
Most of this job is a fixed sequence, and that is a good thing: the same query, charts and layout every week make reports comparable. The model only reads a handful of figures and says what changed, so the steps form a workflow DAGWorkflow DAG: A workflow drawn as a directed acyclic graph: steps joined by edges that never loop back, run in the order their inputs allow. with the model in one node (workflows vs agents). A tool-calling agent earns its place only for follow-up questions such as "why did refunds jump in week 46?", which need queries nobody wrote in advance.
The design
Read the data read-only, by construction
Give the report its own database user that can only SELECT from a reporting view, with its password stored as a secret (the node's secretRef). The database role is the access control.
PostgreSQL: Query (postgres_query) and MySQL: Query (mysql_query) send query and params exactly as written: no template is filled into either, so nothing from the run (not a webhook field, not a model's answer) can change the SQL. Put the date range in the SQL itself (WHERE created_at >= date_trunc('week', now()) - interval '1 week'). The node returns rows and rowCount.
Compute figures in code, not in the model
A JavaScript Code node turns the rows into a small object of figures (this week's orders, last week's, the refund count) and returns it as facts. Models are poor at arithmetic over many rows, and every row in a prompt costs tokens. Use JavaScript: Python code nodes are off by default without the container sandbox (code execution).
Charts and the PDF
Each Chart node renders a Vega-Lite spec to SVG on the server from the list of rows at its dataPath. A Code node outputs only what its code returns, so Charts below Figures do not see the query's rows: set dataPath to results.<query node id>.rows, or have Figures return the rows each chart needs. A Prompt Template places the SVGs in an HTML page and PDF Output prints it; the PDF report with charts recipe covers these nodes field by field.
Let the model write, then check its numbers
The LLM Call gets only the figures and an instruction such as "Write two sentences for the weekly report. Use only these numbers." Instructions reduce made-up numbers; they do not prevent them. So a second code node checks the answer before the send step:
const allowed = new Set(Object.values(input.facts).map(String));
const found = (String(input.answer).match(/\d[\d,]*(\.\d+)?/g) || []).map(n => n.replace(/,/g, ''));
const unknown = found.filter(n => !allowed.has(n));
return { summary: input.answer, unknownNumbers: unknown, numbersOk: unknown.length === 0 };Two wiring details. The LLM Call's output carries its answer, not the figures, so connect Figures to this check node too; that edge is where input.facts comes from. And the LLM Call reads its prompt from the prompt key, so put a Prompt Template (not drawn) between Figures and the LLM Call, or have Figures return the instruction and figures as prompt.
An If node with the condition {{numbersOk}} sends the email on true and holds the report on false. Keep the condition one template: compute the decision in code and branch on the result.
We ran this part on a local L2M with a scripted stand-in model (a local server returning fixed answers, not a real model) and the figures 1240, 1180, 31 and 29. "Orders rose to 1,240 this week from 1,180 last week. Refunds were 31." went to the send branch; the same sentence with 1,420 went to the hold branch with unknownNumbers: ["1420"]. The check is strict on purpose: "60 more orders" fails, because 60 is not a figure. Add derived figures (differences, percentages) to the facts in code, or tell the model not to compute.
Send it
SMTP: Send Email (smtp_send_email) renders to, subject, text and html from the run, so the email can carry the checked summary ({{summary}}, which the If node and the gate pass on) and a small HTML table of the figures, read by path, for example {{results.<figures node id>.facts.orders}}. The node cannot attach a file from the run, so the PDF stays on the run (PDF Output returns it as a data: URL in pdf.downloadUrl, and the studio's chat shows a Download PDF link): send the summary by email or Slack, and get the PDF from the run.
The schedule
The Schedule Trigger takes a cron expression (minute, hour, day of month, month, day of week), a time zone (empty means UTC) and an active switch. It registers only when active is on, the expression is valid and the trigger is an entry point with an outgoing edge. When the workflow has a published version, the schedule runs that version, not your draft.
One trap: day of month and day of week combine differently from POSIX cron. POSIX fires on a day matching either field, so 0 8 1 * 1 would run on the 1st and on every Monday. L2M's scheduler (the node-cron library, 4.6 in this release) requires both: in our check of its matcher, 0 8 1 * 1 matched Monday 1 June 2026 but not Tuesday 1 December 2026 or Monday 16 November 2026. Restrict only one of the two fields.
Follow-up questions: the agent part
For ad-hoc questions, attach a read-only SQL tool to an Agent Orchestrator through an MCP server that exposes your database (read-only SQL access over MCP covers the database role and the choice of server). Agent type sql adds instructions such as "Only execute SELECT queries", but that is a prompt, not enforcement. The guarantees come from:
- The MCP server connects as the same read-only database user.
- In the MCP tool policy, an admin marks the query tool read-only and marks or denies any tool that writes; the workflow's No write tools switch then blocks every write tool. The switch trusts a server's own
readOnlyHintunless an admin's mark overrides it, which is why the marks matter. - The run trace, which records every SQL statement the agent ran with its arguments.
Keep the agent out of the scheduled send path: its answers go to the person who asked.
Where approvals go
For the scheduled report, the right control is an Approval GateApproval gate: A step that pauses a run until a person approves or rejects what happens next, such as a payment, an email or a write tool call. on the hold branch: a person sees the summary next to the figures, can edit it and approve, and only then does the email go. Make the editable text field's key summary, the key the check node returns: an approval with no edits passes on the incoming summary, and a field under a new key would be missing. Give the approved output its own send step rather than joining the true branch's: a step with two parents takes shared keys from the parent whose edge was drawn last, so the If node's original summary could replace the edited one. A scheduled run has no known requester, so name the approver roles or users; with none listed, any operator, builder or admin may decide (approval gates). While you build trust in the summaries, you can put the gate on every report.
Ask before write tools does not fit: the design has no write tools. A follow-up agent with a tool that can change data is a design error to fix with least privilegeLeast privilege: The security principle that each user, program or agent gets only the access it needs for its task, and nothing more., not an approval.
How to test it
Save real runs as a golden runGolden run: A recorded run of a workflow or agent that you have checked and keep as the reference: its input, every step's result and every outside call, for replaying changes against. and group them in a golden set. In a replay, the query node is answered from the recording, so the test needs no database, and the email node never sends. Useful cases:
| Case | Input (recorded rows) | Pass looks like |
|---|---|---|
| Normal week | Orders and refunds both present | The new summary uses only figures from the facts; status success |
| Empty week | rowCount: 0 | No summary claims a trend |
| Big change | Orders doubled | Summary says so; the llm_judge scorer, if you enable it, agrees the summary states the direction correctly |
Code nodes are answered from the recording in a replay, so the Check numbers verdict and its branch are the recorded ones: a new prompt or model that invents a number still takes the recorded branch. Judge the new summary itself with a scorer (regex or llm_judge on the summary, which the run's output must include, for example via an Output node after the send step). A changed checker makes the replay inconclusive; test it with test runs and fresh golden runs (what a replay does).
What it costs to run
The scheduled report makes one model call per run; the query, charts and PDF use no tokens. The prompt is the instruction plus the figures: five numbers cost a fraction of five hundred rows. Each week costs about the same, so it is easy to cap with a workflow budgetBudget: A cap on model spend or tokens for one scope, such as a project, a user or a client, over a period, which warns or blocks new work when it is reached. using the block action (budgets). A scheduled run that a used-up budget refuses is recorded once per period as a failed run, not on every fire.
The follow-up agent is where cost varies: each iteration resends the conversation, and query results come back as tool messages. Keep Max iterations low (an Agent Orchestrator added in the editor starts at 6), make the SQL tool return few rows (a LIMIT in the server or the view), and keep the agent's tool message limit at its default of 6,000 characters or lower. See controlling LLM and agent costs. If the agent keeps rerunning the same query, see agent stuck in a tool loop.
Starting point, honestly
The closest sample is samples/workflows/report-with-charts-flow.json, the PDF report with charts recipe. It is seven nodes: a Set node with three small data sets written into the workflow, three Chart nodes, a Prompt Template that builds the HTML, PDF Output and Output. It has no trigger, no database, no model and no send step.
samples/workflows/key-inventory-report-flow.json draws the charts from live data instead: an MCP tool (list_keys, on a key-manager server you provide) feeds a Python code node (container sandbox needed).
What you add to the first sample:
- Replace Set with a Schedule Trigger and a PostgreSQL or MySQL query node, using a read-only user and a secret for its password.
- Add the Figures code node, and point each Chart's
dataPathat its rows (aresults.path or a list Figures returns). - Add an LLM Call with the figures, the Check numbers code node and an If node.
- Add SMTP: Send Email on the
truebranch, an Approval Gate on thefalsebranch with a second SMTP: Send Email on itsapprovedoutput, and a budget. - Save a few runs as golden runs before you change the prompt or the model.
Related use cases: scheduled monitoring agent (alert only on change), email to structured data (check before write, for extraction) and incident triage agent.
Further reading
- Vega-Lite documentation (vega.github.io): the JSON grammar the Chart node renders
- POSIX crontab (opengroup.org): the five time fields and how day of month and day of week combine
- PDF report with charts: the sample, step by step
- Chart node
- Approval gates
- Budgets