The report file

A report is a folder of app/src/reports/, and the folder's name is its address: app/src/reports/sales/ opens at /reports/sales. The folder holds:

A report folder holds only report.tsx and .sql files. Any other file fails the check. Adding the folder adds the report to the list on /reports.

Anatomy

monthly.sql:

-- Sales and orders per month
select to_char(sold_on, 'YYYY-MM') as month,
       sum(amount) as sales, count(*) as orders
from sales
where sold_on between $from and $to
group by 1

report.tsx:

import { defineReport, query } from "~/components/report-kit/define"
import { Info, Rule, Rules, Sql, Technical } from "~/components/report-kit/info"

import monthly from "./monthly.sql?raw"

type Month = { month: string; sales: number; orders: number }

export default defineReport({
  meta: {
    title: "Sales",
    description: "Sales and orders per month.",
    connector: "postgres",
  },
  page: {
    info: () => (
      <>
        <Info>
          <Rules>
            <Rule id="sales" title="Sales">
              the order amount, including tax, by the day it was sold.
            </Rule>
          </Rules>
        </Info>
        <Technical>
          <Sql query="monthly" />
        </Technical>
      </>
    ),
  },
  queries: { monthly: query<Month>(monthly, { grain: "month" }) },
  blocks: (k) => [
    <k.Line
      key="sales"
      series="monthly"
      x="month"
      xFormat="month"
      y={{ sales: "Sales" }}
      title="Sales per month"
      info={{ what: "Sales per month.", rules: ["sales"], sql: ["monthly"] }}
    />,
  ],
})

The parts: meta (the report's name), page (its options and the dialog), queries (each .sql file by the name blocks read it under) and blocks (the page, top to bottom). The page itself holds no text: explanations go in the dialog.

meta

page

Queries

Each query is a .sql file in the folder, imported with ?raw and named in queries: queries: { monthly: query<Month>(monthly) }. The name is how blocks read the result: series="monthly". Month is the row the query returns, one entry per column: blocks may name only those columns.

• Start the file with a -- comment line: it is the query's title in the dialog. A file without one fails the check.
• Queries are read-only: they must start with select or with.
• Results arrive in one shape on every connector: numbers as numbers, dates as YYYY-MM-DD text, timestamps as YYYY-MM-DD HH:MM:SS text.

Reshaping rows in code

When the SQL cannot say it, a query can reshape its rows with reconstruct. It runs on the server after the rows are cut to the date filter's range, and the blocks then read what it returns:

regions: query(regions, {
  reconstruct: (rows: Region[]) =>
    rows.map((r) => ({ region: r.region, perOrder: r.sales / r.orders })),
}),

Here the blocks may name region and perOrder, and no longer sales. Prefer SQL when it can do the job: the dialog shows the SQL, not the code.

$from, $to and the date filter

A query may name $from and $to, plus the param of any input the page declares. They are sent to the database as parameters, never pasted into the text.

How the date filter works:

1. Every query of a report runs once over a wide window: $from is the first day of the month 23 months back, and $to is today. The result is cached for ten minutes.
2. The rows are then cut to the range the date filter shows, by the query's grain, query<Month>(monthly, { grain: "month" }): with "month", each row needs a month column holding YYYY-MM; with "week", a week column holding the ISO week, YYYY-Www. With "all" (the default), nothing is cut.
3. So changing the date range runs no query. A report opens on the first of the month a year ago through today.

With every query on "all" the date filter changes nothing on the page, so a report whose results have no dates sets filterBar: false.

Dialects

The SQL is your database's own. What differs most:

$from::date runs only on Postgres and Redshift. SQLite has no date type: a date is its YYYY-MM-DD text, so date($from) compares as text, which sorts right. For a yes/no column a block reads (strong, ring, footer), return 1 and 0: they work on all nine.

Reading the data in a prop

A few props may read a value from the results instead of a fixed text: title, description, y, target, tooltip, reference, columns, groups, lines and segmentParams. Give them a function of the data: title={(d) => String(d.totals[0].label)} is the label column of the first row of totals.

The checks

Claude runs npm run lint after every change to a report. It checks three things:

A query that fails on the database shows its error on the page. To fix one, ask Claude: "The Sales report fails the check. Fix it."