Reports and views with SQL behind every figure

A report that stores its numbers starts to go stale the moment it is saved. The board pack says 92% occupancy, the property list says 89%, and nobody can say which is right without redoing the sums.

In InventDB SOAR, the database with the business engine, a report holds no figures of its own. It is an HTML template with SQL behind every figure, and each time it renders, on screen, on paper or in an email, the server runs those queries again against the live records. A saved view does the same for a data grid.

This article explains the template format, what the server does on each render, how charts survive an email client, how scheduled delivery works, and who sees which numbers. Reports and views are part of InventDB SOAR: authoring calls a language model from inside the server, and delivery runs as a workflow. InventDB Serverless, the managed database, has no AI inside.

A template with queries inside

A report template is a complete HTML document. Wherever a figure belongs, it holds a <script type="server"> block. The server runs these blocks in a sandboxed JavaScript interpreter built into the server, and the browser receives only the HTML they write. Inside a block, these globals are available:

Here is one section of an owner's portfolio report:

<section class="kpis">
<script type="server">
  const k = query("SELECT COUNT(*) AS leases, SUM(rent) AS rent FROM pms.leases " +
                  "WHERE owner_id = :owner_id AND status = 'Active'")[0];
  out.write(`<p>${k.leases} active leases, ${esc(fmt.currency(k.rent))} a month</p>`);

  const s = query("SELECT status, COUNT(*) AS n FROM pms.units " +
                  "WHERE owner_id = :owner_id GROUP BY status");
  out.write(chart.donut({ title: "Units by status",
    labels: s.map(r => r.status), values: s.map(r => r.n) }));
</script>
</section>

A placeholder such as :owner_id is filled from the call's parameters, falling back to the report's own, and a string value is quoted with any embedded quotes doubled. If a placeholder has no value, the render stops with an error that names it, because running the query without its filter would report the wrong rows without saying so.

What the server does on each render

1. A caller asks for a render: the Report Studio, the API, an AI agent through the MCP report tool, or a workflow step.
2. The server records the caller's identity and runs the render on a worker thread with that identity installed again, so every query in the template is planned with the caller's row rules.
3. A small lexer finds where each server block really ends. It tracks strings, nested template literals, comments and regular-expression literals, so HTML inside a string or a quote inside a regex cannot end a block early.
4. All blocks share one scope, and their top-level const and let declarations are treated as var, so one block can compute a total that a later block prints, and repeating a declaration is not an error.
5. Each block's output replaces the block, and the finished document is returned.

Three limits bound a render. Output is capped at 5 MB. The wall-clock budget is 15 minutes, set high on purpose: it exists to stop a runaway script, and a section that aggregates a few hundred thousand rows legitimately spends its time inside the query. There is no row cap per query by default, because a silent cap would let a report print COUNT(*) of the true total beside a table holding only the first rows. A template can set its own cap.

Charts that survive an inbox

The chart helpers take labels and values and return markup. In the app they return inline SVG whose text uses currentColor, so the same chart reads on a light or a dark page. Gmail and Outlook strip inline SVG from email, so when a render is bound for email the same calls return HTML and CSS: pie and donut charts become labelled share bars, a heatmap becomes a table of coloured cells, and line, area, histogram and scatter charts become a bar table. Email charts contain no SVG and no data URIs. The render API produces email-safe charts by default, and the in-app viewer asks for SVG.

Images take the opposite approach. recordImageBy finds an image attached to a record in a library type by a business key, and imageByFilename finds an attached file by name. Both return an <img> with the image inlined, so it displays without a sign-in. Identifiers passed to them are checked against letters, digits and underscores before they reach a query.

Printing on A4

The Report Studio previews a template as an A4 page, and the screen shows what will print. Templates the Studio's AI writes follow fixed print rules: they declare @page { size: A4; margin: 14mm 12mm; }, lay content out in a fixed column about 720 to 760 pixels wide, avoid viewport units and fixed positioning, and keep a card or a table row from splitting across two pages. The same rules work for a template you write by hand. Because the result is ordinary HTML and CSS, one document prints from a browser, saves as a PDF and renders on a phone.

Scheduled delivery is a workflow

A report has no scheduler of its own. Delivery is a workflow with a schedule trigger and three steps: a query that lists the recipients, a render step that renders the template once per recipient with that recipient's parameters, and an email step that sends each rendered page as the body of its own message.

{ "idx": 1, "kind": "sql_query", "save_as": "owners",
  "sql": "SELECT owner_id, email FROM pms.owners WHERE wants_statement = true" },
{ "idx": 2, "kind": "render_report", "save_as": "statements",
  "template_id": "tpl_owner_statement", "for_each": "owners",
  "params": { "owner_id": "${owners.owner_id}" } },
{ "idx": 3, "kind": "send_email", "for_each": "statements",
  "to": "${statements.email}", "subject": "Your monthly statement",
  "body": "${statements.html}" }

The rendered HTML is the email body; files are attached only when the plan asks for them. The server checks this shape when the workflow is saved, and refuses a render step whose result no later step uses, since that report would be produced and dropped. Workflow renders always use email-safe charts and run as the workflow's owner, so each recipient's scope comes from the parameter in the template's SQL.

Who sees which numbers

Every render runs as the person or workflow that asked for it. If Ann and Raj open the same template, each query is planned with their own row rules, and each sees totals over the rows they are allowed to read: one definition, and a correct answer for each reader. The template records are guarded separately. A person lists and opens their own templates, administrators see all of them, only the creator or an administrator can change or delete one, and creating one needs a write grant on the report templates type. A shared view can render a layout someone else wrote, and its queries still run as the reader.

Views: a saved query and its layout

A saved view starts in the console's data grid. When the grid shows what you want, whether you got there by browsing, filtering or asking the AI, you save it as a view. The view is one record that holds:

• the query, or for a browse view the live search term and sort, so the view is rebuilt against current data each time;
• the display mode, a table by default, and the columns to show;
• for a sheet display, its definition: columns, formulas, footer totals, frozen columns and chart bindings, and never the data;
• the Ask-AI conversation that produced the query, so a follow-up question continues from it;
• a version number, incremented on every update.

A layout other than a table, such as cards or tiles, is a report template linked from the view. Its script calls query(params.viewSql), and the console passes the grid's current SQL as that parameter, so the layout renders exactly the rows the grid would show, with its live filters and sort. Rendering and AI edits go through the report endpoints described above. The AI layout designer sends the schema the first time it drafts a layout, and on later edits sends only the current layout and your instruction, which keeps each edit small.

POST /api/saved-views
Content-Type: application/json

{
  "name": "Overdue leases",
  "namespace": "pms",
  "baseSql": "SELECT tenant, unit, balance FROM pms.leases WHERE balance > 0 ORDER BY balance DESC",
  "defaultMode": "table",
  "columns": ["tenant", "unit", "balance"]
}

The server stamps the creator and version 1 and returns the stored record. Listing returns up to 1,000 views, ordered by name.

Limits

• A report is as current as its last render. An emailed copy is a snapshot of the moment it rendered.
• Email charts are simpler than their SVG versions: a line or scatter chart becomes a bar table.
• Templates are code. Work done in JavaScript, such as looping over thousands of rows, counts against the render budget; aggregation belongs in the SQL.
• With no row cap, a template that prints a large table produces a large page, and output stops at 5 MB.
• A render that exceeds its 15-minute budget fails without partial output.
• Scheduled delivery runs on the workflow scheduler, which checks schedules every 30 seconds.

Using it

In InventDB SOAR, open Report, pick a layout from the gallery or start blank, point each block at your records and check the A4 preview. To render a saved template from code:

POST /api/report-templates/{id}/render
Authorization: Bearer <token>
Content-Type: application/json

{ "params": { "owner_id": "O-1006" }, "email_safe_charts": false }

The response carries the rendered HTML, built under the caller's row rules. AI agents connected over MCP render saved templates with the report tool, as the person they signed in as. To deliver a report on a schedule, describe the delivery on the Operate page, and the result is the three-step workflow shown above.