Shared reports

A report is a Markdown file with charts in it. The prose is analysis; each chart is a widget whose rows are either frozen in the file or produced by a live SQL query the server re-runs on a schedule. An agent writes the file and publishes it with the ah CLI; everyone in the organization reads it at /reports/<slug> in the dashboard, where live widgets stay current and every revision is kept.

The loop

Five commands, in this order. preview is the one that keeps mistakes out of the org’s view: it validates the file, runs every live widget once, prints the resolved report with its hints, and saves nothing.

ah reports read last-week           # learn the house format from an existing report
# write weekly.md (below)
ah reports preview --file weekly.md  # validate + run every widget; fix what the errors name
ah reports create weekly-growth --file weekly.md   # publish — visible to the whole org now
ah reports read weekly-growth        # verify from the published copy; post the URL

Next week: ah reports pull weekly-growth --out weekly.md, revise the prose, then ah reports update weekly-growth --file weekly.md. Widgets whose spec did not change keep their cache. list, read, preview, pull, history, diff, show, refresh and widgets work with a read token; create, update and delete need write scope.

The authoring file

Ordinary GitHub-flavored Markdown — headings, lists, tables, task lists, footnotes and link references all render (raw HTML is dropped) — with an optional front matter block and any number of top-level ```widget fences. Each fence holds one JSON object: a widget kind plus either rows (frozen) or sql (live).

---
title: Weekly growth
description: Where the funnel stands this week. Kept current by the growth agent.
project: ah_xxxxxxxx          # default project for widgets that name none
since: 7d                     # default window for live widgets (default 7d)
---

## Where we are

Signups are up 12% week over week (as of Aug 30), carried by the pricing page rewrite.
The live numbers below recompute on their own — the line under each one says when.

```widget
{ "widget": "stat", "title": "This week", "ttl": "1h",
  "sql": "SELECT count(DISTINCT s.anon_id) AS visitors, count(*) FILTER (WHERE e.name = 'signup') AS signups FROM events e JOIN sessions s ON s.id = e.session_id WHERE e.ts >= :since AND s.classification NOT IN ('crawler','suspected_bot','test')" }
```

```widget
{ "widget": "line", "title": "Signups per day", "ttl": "1h", "since": "30d",
  "sql": "SELECT date_trunc('day', e.ts)::date AS day, count(*) AS signups FROM events e JOIN sessions s ON s.id = e.session_id WHERE e.name = 'signup' AND e.ts >= :since AND s.classification NOT IN ('crawler','suspected_bot','test') GROUP BY 1 ORDER BY 1" }
```

```widget
{ "widget": "bar", "title": "Sessions by source", "width": "half", "ttl": "6h",
  "sql": "SELECT coalesce(utm_source, '(direct)') AS source, count(*) AS sessions FROM sessions s WHERE started_at >= :since AND s.classification NOT IN ('crawler','suspected_bot','test') GROUP BY 1 ORDER BY 2 DESC LIMIT 12" }
```

```widget
{ "widget": "table", "title": "Top events", "width": "half", "ttl": "1h",
  "sql": "SELECT e.name, count(*) AS n FROM events e JOIN sessions s ON s.id = e.session_id WHERE e.ts >= :since AND s.classification NOT IN ('crawler','suspected_bot','test') GROUP BY 1 ORDER BY 2 DESC LIMIT 20" }
```

## Against plan

```widget
{ "widget": "stat", "title": "Q3 targets",
  "rows": [ { "signups": 600, "conversion": 0.042 }, { "signups": 480, "conversion": 0.038 } ],
  "format": { "conversion": "percent" }, "labels": { "signups": "Q3 signups" } }
```

Next: turn on the retention email once the [experiment](/flags) reads.

Slugs are lowercase letters, digits and hyphens, up to 64 characters; create derives one from the title when you omit it. Reserved: preview, widgets, new, history, latest.

The widget library

Five kinds. Each has a column contract: the shape the rows — inline or from the query — must have. The parser checks inline rows when you publish; live rows are checked on every compute, and a query whose shape drifts fails that widget with the contract named, never the page. ah reports widgets prints this table.

```widget
{ "widget": "stat", "title": "Last 7 days", "ttl": "1h",
  "sql": "SELECT count(*) AS sessions, count(DISTINCT anon_id) AS visitors, round(avg(EXTRACT(EPOCH FROM (last_at - started_at)))) AS avg_duration FROM sessions s WHERE started_at >= :since AND s.classification NOT IN ('crawler','suspected_bot','test')",
  "format": { "avg_duration": "duration" }, "labels": { "avg_duration": "Avg session" } }
```

```widget
{ "widget": "line", "title": "Sessions per day", "since": "30d", "ttl": "1h",
  "sql": "SELECT date_trunc('day', started_at)::date AS day, count(*) AS sessions, count(*) FILTER (WHERE converted) AS converted FROM sessions s WHERE started_at >= :since AND s.classification NOT IN ('crawler','suspected_bot','test') GROUP BY 1 ORDER BY 1" }
```

```widget
{ "widget": "area", "title": "Sessions by device", "stack": true, "ttl": "6h",
  "sql": "SELECT date_trunc('day', started_at)::date AS day, count(*) FILTER (WHERE device_type = 'mobile') AS mobile, count(*) FILTER (WHERE device_type = 'desktop') AS desktop FROM sessions s WHERE started_at >= :since AND s.classification NOT IN ('crawler','suspected_bot','test') GROUP BY 1 ORDER BY 1" }
```

```widget
{ "widget": "bar", "title": "Sessions by source", "width": "half", "ttl": "6h",
  "sql": "SELECT coalesce(utm_source, '(direct)') AS source, count(*) AS sessions FROM sessions s WHERE started_at >= :since AND s.classification NOT IN ('crawler','suspected_bot','test') GROUP BY 1 ORDER BY 2 DESC LIMIT 12" }
```

```widget
{ "widget": "table", "title": "Top events", "width": "half", "ttl": "1h",
  "sql": "SELECT e.name, count(*) AS n, count(DISTINCT s.anon_id) AS visitors FROM events e JOIN sessions s ON s.id = e.session_id WHERE e.ts >= :since AND s.classification NOT IN ('crawler','suspected_bot','test') GROUP BY 1 ORDER BY 2 DESC LIMIT 20" }
```

```widget
{ "widget": "stat", "title": "Q3 targets",
  "rows": [ { "signups": 600, "conversion": 0.042 }, { "signups": 480, "conversion": 0.038 } ],
  "format": { "conversion": "percent" }, "labels": { "signups": "Q3 signups" } }
```

Live queries

A live widget’s sql runs under the same guard as ah sql: one read-only SELECT (or WITH), scoped to the report’s projects, 10-second timeout, the tables ah schema lists. :since and :until are bound to the widget’s window; because a relative since is resolved at compute time, "7d" always means the last seven days — that is what makes the widget live. A literal date inside the SQL is not.

Exclude bots and test traffic yourself. Every other ah verb applies the default filter for you; a live query sees raw rows. Unless the report is about bots, join sessions and filter on its classification — from events:

SELECT count(*) AS signups
FROM events e
JOIN sessions s ON s.id = e.session_id
WHERE e.name = 'signup' AND e.ts >= :since
  AND s.classification NOT IN ('crawler','suspected_bot','test')

From sessions, the same WHERE on classification without the join. preview prints a hint whenever a query reads events or sessions without mentioning classification — a hint, never a rejection, because some reports are about bots.

Results are capped at 2,000 rows (the card says how many there were) and 256 KB; aggregate or LIMIT rather than shipping raw rows. A failed compute — a timeout, a column that disappeared, a project moved to another organization — leaves the last good rows on the card, dated by their compute, with the error text underneath; ah reports read prints the same text under the widget for the agent, and ah reports show lists every widget’s cache state.

Freshness

Pick the TTL by how the reader watches, not by how fresh the data could be: a weekly review 24h; something people glance at during the working day 1h (the default); liveops during a launch 5m–15m. Floor 1m, ceiling 30d.

Opening a report serves whatever is cached, immediately. A widget past its TTL is shown as it was — marked stale — refreshing — while one recompute runs in the background, and the page picks up the result on its own. A widget with no cache yet computes inline (create warms every widget so the first click is instant). The refresh button on a card, ah reports refresh, or POST /api/v1/reports/:slug/refresh recompute now, bounded to once per widget per 60 seconds. A report holds at most 24 live widgets; freeze the rest.

Revisions

create makes revision 1; every update appends one. The page renders the latest; the history link lists them all and ?rev=N shows an older one behind a banner (its live widgets show current data — history keeps the prose and the specs, not the numbers). From the CLI:

ah reports history weekly-growth              # rev, when, by, blocks ±
ah reports diff weekly-growth --from 3 --to 4  # unified diff of the two sources
ah reports read weekly-growth --rev 3          # an older revision, resolved
ah reports pull weekly-growth --rev 3 --out weekly-r3.md   # its file, byte-identical

ah reports delete <slug> --yes removes the report with every revision; the flag is mandatory because agents are non-interactive.

Reading a report

In the dashboard, Reports sits in the rail’s account section: reports belong to the organization, not to one project, so the project switcher does not filter them. Every member sees every report from the moment it is created — there is no draft state and no per-report permission. Sharing outside the organization is not available yet; Print on the page gives a clean sheet with each widget’s as-of line, which is the export for now.

From the terminal, ah reports read <slug> prints the same resolved report — prose, every widget’s current rows, and the as-of/TTL footer under each — which is how an agent verifies what it published. Long tables are cut to 40 rows (--full for all); --json returns the resolved blocks with rows for scripting.

# Weekly growth
Where the funnel stands this week. Kept current by the growth agent.
(revision 4 · 2026-08-31 10:09 by growth-agent · rendered 2026-08-31 10:28)

## Where we are
…

▸ This week  [stat · live]
  visitors 1,204 · signups 37
  as of 2026-08-31 10:09 (19m ago) · refreshes every 1h · window 7d (2026-08-24 → now) · 12 ms

Limits

For agents and scripts

The skill is the fastest way in: it teaches an agent the loop above, the classification idiom, TTL choice and how to verify — install it from the skills page or take the raw file:

mkdir -p ~/.claude/skills/agenthog-report && \
  curl -fsSL https://agenthog.io/skill/report/SKILL.md \
    -o ~/.claude/skills/agenthog-report/SKILL.md

Every verb has an HTTP twin under /api/v1/reports with the token auth described in Server to server: GET /api/v1/reports, POST /api/v1/reports { slug?, source, title?, warm? }, GET /api/v1/reports/:slug/render?rev=N&wait=1 (the resolved blocks with rows), PATCH and DELETE /api/v1/reports/:slug, GET …/revisions, POST …/refresh, POST /api/v1/reports/preview and GET /api/v1/reports/widgets. The file is the only write payload — no blocks JSON — so pull is byte-identical for free.

See also the ah CLI for ah sql and ah schema, which a live query is built on.