agent-authored-dashboards.md24.9 KBView on GitHub # Agent-Authored Statistical Dashboards
## 1) Introduction — goal, present state, future state
Today agents write plain markdown into conversations, and statistics (response times, follow-up rates, win rates, activity timelines) are static. We want agents to author interactive dashboards — composite layouts of cards, charts, tables, and stat tiles that users can customize in-place without re-running the agent. The agent outputs a dashboard spec as a special markdown block; the frontend renders it from a whitelisted block vocabulary, executes any backing queries on-demand, caches results for 5 minutes, and lets users switch chart types, show/hide columns, and apply filters and aggregation changes — all client-side until they want the updated data.
The spec separates three concerns so agents can compose visuals freely without emitting markup:
1. **Data sources** — *named* result sets. Each is either `query`-backed (SQL executed by the backend) or `mock`-backed (inline rows shipped in the spec). A dashboard can declare several.
2. **Layout** — a recursive container tree (`row` / `column` / `grid`) with sizing, whose leaves are blocks.
3. **Blocks** — typed leaf widgets that bind to a data source: `stat` (a single value, optionally a radial gauge — the "35% win rate circle"), `chart`, `table`, `entityCard`, `profile`, `text`, and `repeater` (renders one templated card per row of a source).
The agent emits a validated JSON tree, never HTML — so rendering stays on the design system, secure, and interactive. **Mock-backed sources** let an agent (or a human) author a fully-populated dashboard with no database access, which is what powers demos and component-test fixtures.
We deliberately do **not** support agent-authored raw HTML. It looks flexible but breaks the three things this design rests on: the interactivity contract (raw HTML can't re-run SQL or rebind to controls), security (agent output is downstream of untrusted email/CRM content — a prompt-injection → XSS path), and design consistency (hand-authored markup won't match Shadcn/Tailwind/Recharts). If a true escape hatch is ever needed, the right form is a sandboxed iframe with a postMessage data bridge — a later, last-resort addition, not the primary mechanism.
## 2) Present state
### 2.1 Architecture diagram
```text
Agent execution Conversation markdown Frontend component
┌──────────────────┐ ┌──────────────────┐ ┌──────────────────┐
│ Agent writes │──plain──► │ Markdown with │──render──► │ Markdown Editor │
│ conversation │ markdown │ code blocks, │ │ + components │
│ (plain text) │ │ lists, tables │ │ (read-only) │
└──────────────────┘ └──────────────────┘ └──────────────────┘
Backend (tRPC) Database
┌──────────────────┐ ┌──────────────────┐
│ statistics.* │◄──data──► │ crm_events, │
│ routes (ad-hoc) │ │ crm_conversations
└──────────────────┘ └──────────────────┘
```
### 2.2 Step-by-step walkthrough
1. **Agent writes conversation** — Mastra agent workflow at [apps/server/src/mastra/workflows](apps/server/src/mastra/workflows). Agent calls tools like `updateConversationTool` or `createAndLinkEventsTool` which write markdown strings to `crm_conversations.context`. The markdown is plain text (no embedded interactivity).
2. **Conversation fetched by frontend** — tRPC route `conversations.getByIdWithHydration` at [apps/server/src/trpc/routes/conversations.ts](apps/server/src/trpc/routes/conversations.ts). Returns `{ context: string, ... }` where context is the plain markdown.
3. **Markdown rendered in UI** — React component in [apps/mail/modules/conversations/components/ConversationDetail.tsx](apps/mail/modules/conversations/components/ConversationDetail.tsx) or similar. Uses markdown library to render `context` as HTML. No interactivity beyond links and basic formatting.
4. **Statistics fetched separately** — If user navigates to `/statistics` page at [apps/mail/app/(routes)/statistics/page.tsx](apps/mail/app/(routes)/statistics/page.tsx), calls tRPC `statistics.getOverview` at [apps/server/src/trpc/routes/statistics.ts](apps/server/src/trpc/routes/statistics.ts). Returns pre-computed aggregates (response_time, follow_up_time, win_rate, etc.). Static for the session, no user customization.
5. **User interaction is limited** — Markdown editor at [apps/mail/components/markdown-editor.tsx](apps/mail/components/markdown-editor.tsx) is read-only when displaying conversation context. No way for agents to embed dynamic queries.
## 3) Designed state
### 3.1 Architecture diagram
```text
Agent writes dashboard spec Markdown editor recognizes block
┌──────────────────────────┐ ┌──────────────────────────┐
│ Agent tool writes │ │ ```dashboard fence: │
│ dashboard JSON spec: │──────────►│ parse + validate spec │
│ dataSources{} (query │ │ (zod) → render tree │
│ | mock), layout tree, │ └────────────┬─────────────┘
│ blocks │ │
└──────────────────────────┘ ┌────────────▼─────────────┐
│ <DashboardViewer spec> │
│ resolves each source, │
│ recursively renders │
│ layout via <LayoutNode> │
└────────────┬─────────────┘
per source, branch on kind │
┌───────────────────────────────────┴───────────────────┐
│ │
┌────────────▼────────────┐ ┌───────────────▼────────────┐
│ mock source │ │ query source │
│ inline rows from spec │ │ trpc dashboard.execute │
│ (no network) │ │ (SELECT-only, param-bound, │
│ → feeds blocks directly│ │ user-scoped, 5-min cache) │
└────────────┬────────────┘ └───────────────┬─────────────┘
│ │
└──────────────────────────┬──────────────────────────────┘
│ resolved rows per source
┌────────────────▼────────────────┐
│ Block renderers (whitelist): │
│ stat · chart · table · │
│ entityCard · profile · text · │
│ repeater(template per row) │
└──────────────────────────────────┘
User interaction (client-side only until refresh)
┌──────────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ Switch chart: │ │ Show/hide │ │ Filter rows: │ │ Change agg: │
│ bar ↔ line ↔ pie │ │ columns, │ │ stage=closed │ │ AVG → SUM │
│ (instant) │ │ reorder │ │ (refetch │ │ (refetch │
│ │ │ (instant) │ │ query srcs) │ │ query srcs) │
└──────────────────┘ └──────────────┘ └──────────────┘ └──────────────┘
```
### 3.2 Spec format
A dashboard is `{ spec_version, id, title, dataSources, layout }`. `dataSources` is a map of name → source; `layout` is a tree of container and block nodes that reference sources by name.
**Data source** (discriminated union):
```jsonc
// query-backed — executed by the backend
"winrate": {
"query": "SELECT AVG(won::int)::float AS rate FROM crm_conversations WHERE user_id = $1 AND occurred_at BETWEEN $2 AND $3",
"queryParams": [
{ "name": "userId", "type": "string", "sourceField": "userId" },
{ "name": "dateFrom", "type": "date", "sourceField": "dateFrom" },
{ "name": "dateTo", "type": "date", "sourceField": "dateTo" }
],
"computations": [
{ "field": "rate", "transforms": [{ "type": "ratio_to_percent", "label": "rate" }] }
]
}
// mock-backed — inline rows, never hits the backend (demos + test fixtures)
"conversion": {
"mock": [
{ "stage": "lead", "rate": 0.62 },
{ "stage": "qualified", "rate": 0.41 },
{ "stage": "proposal", "rate": 0.28 },
{ "stage": "closed", "rate": 0.18 }
]
}
```
A source is `query`-backed **or** `mock`-backed, never both. The validator rejects a source that declares neither or both. `computations` (server-side for query sources, applied client-side for mock sources so the two paths render identically) stay per-source.
**Layout tree** — container nodes (`row`, `column`, `grid`) hold `children`; block nodes are leaves that bind to a source by name.
Example 1 — `[ win-rate circle ] [ conversion by stage ]`:
```markdown
```dashboard
{
"spec_version": "2.0",
"id": "dashboard_winrate_overview_abc123",
"title": "Win Rate Overview",
"dataSources": {
"winrate": { "query": "SELECT ...", "queryParams": [ ... ], "computations": [ ... ] },
"conversion": { "query": "SELECT stage, rate FROM ...", "queryParams": [ ... ] }
},
"layout": {
"type": "row",
"gap": "md",
"children": [
{
"type": "stat",
"source": "winrate",
"field": "rate",
"style": "radial",
"label": "Win rate",
"format": "percent"
},
{
"type": "chart",
"source": "conversion",
"chart": "bar",
"xAxis": "stage",
"yAxis": "rate",
"title": "Conversion by stage"
}
]
}
}
```
```
Example 2 — a card per user, each `[ profile ] [ conversion by stage ]`, driven by a `repeater`:
```jsonc
{
"dataSources": {
"users": { "query": "SELECT id, name, avatar_url FROM crm_contacts WHERE owner_id = $1", "queryParams": [ ... ] },
"conversionByUser": { "query": "SELECT stage, rate FROM ... WHERE contact_id = $1", "queryParams": [ { "name": "userId", "type": "string", "sourceField": "row.id" } ] }
},
"layout": {
"type": "repeater",
"source": "users",
"as": "row",
"template": {
"type": "entityCard",
"children": [
{ "type": "profile", "name": "{{row.name}}", "avatar": "{{row.avatar_url}}" },
{
"type": "chart",
"source": "conversionByUser",
"params": { "userId": "{{row.id}}" },
"chart": "bar", "xAxis": "stage", "yAxis": "rate"
}
]
}
}
}
```
The `repeater` resolves its `source`, then for each row instantiates `template`, interpolating `{{row.*}}` into child fields and per-block `params`. A block whose `source` takes `params` (like `conversionByUser`) executes once per row with the row-scoped params — these are normal query executes, each independently cached.
### 3.3 Step-by-step walkthrough
1. **Agent writes dashboard spec** — agent tool `createDashboardTool` at [apps/server/src/mastra/tools/dashboard/createDashboardTool.ts](apps/server/src/mastra/tools/dashboard/createDashboardTool.ts) takes data sources (query or mock), a layout tree, and block definitions; validates against the spec schema; and writes a ```` ```dashboard ```` fence into `crm_conversations.context`. For demos it can be handed mock sources and emit a fully-populated spec with no SQL.
2. **Markdown editor parses dashboard block** — enhanced [apps/mail/components/markdown-editor.tsx](apps/mail/components/markdown-editor.tsx) recognizes the `dashboard` fence, parses the JSON, and validates it with a zod schema before rendering. Invalid specs render as an error card, not a crash.
3. **`<DashboardViewer>` resolves sources + renders the tree** — new component at [apps/mail/components/DashboardViewer.tsx](apps/mail/components/DashboardViewer.tsx). For each entry in `dataSources`: a `mock` source yields its inline rows immediately; a `query` source is fetched via `trpc.dashboard.execute.useQuery(...)`. It then walks `layout` with a recursive `<LayoutNode>` that dispatches on `type` — containers render their `children`, blocks render their bound source's rows.
```typescript
// source resolution — mock short-circuits, query hits the backend
function useSource(name: string, source: DashboardSource, params: QueryParams) {
const isMock = 'mock' in source;
const query = trpc.dashboard.execute.useQuery(
{ specId, source: name, queryParams: params },
{ enabled: !isMock, staleTime: 5 * 60 * 1000 },
);
if (isMock) return { rows: applyComputations(source.mock, source.computations), isLoading: false };
return { rows: query.data?.rows ?? [], isLoading: query.isLoading, error: query.error };
}
```
4. **Backend executes query sources** — `dashboard.execute` tRPC proc at [apps/server/src/trpc/routes/dashboard.ts](apps/server/src/trpc/routes/dashboard.ts). Takes `specId`, a `source` name, query params, and optional filter/agg overrides. Validates the source's query (SELECT-only via regex + SQL parser), binds params (user-scoped), executes, applies the source's `computations`, and returns `{ rows, metadata }`. Mock sources never reach this proc.
```typescript
if (!/^\s*SELECT\b/i.test(query)) throw new Error('Only SELECT queries allowed');
const rows = await db.execute(bindParams(query, { userId, dateFrom, dateTo }));
const computed = applyComputations(rows, source.computations);
return { rows: computed, metadata };
```
5. **User customizes** — switch chart type / show-hide / reorder columns are instant and client-side; filter and aggregation changes re-execute only the affected **query** sources (mock sources re-filter client-side). All customization lives in component state, not persisted. A "Refresh" button clears the per-source cache.
## 4) Implementation phases
### Phase 1 — Spec format + recursive renderer + core blocks (stat, chart, table) + mock sources
**Goal:** Define the v2 spec (dataSources union, layout tree, blocks), render it with a recursive `<LayoutNode>`, support `stat` / `chart` / `table` blocks and `row` / `column` / `grid` containers, resolve mock sources inline and query sources via tRPC with 5-min caching.
- [ ] Define spec TypeScript types + zod schema at [apps/mail/types/dashboard.ts](apps/mail/types/dashboard.ts): `spec_version`, `id`, `title`, `dataSources` (discriminated union of `{ query, queryParams, computations }` and `{ mock, computations }`), and `layout` (recursive container + block node union). Reject sources declaring neither/both query and mock.
- [ ] Create `<DashboardViewer spec={spec} />` at [apps/mail/components/DashboardViewer.tsx](apps/mail/components/DashboardViewer.tsx): resolve every source (mock → inline rows, query → `dashboard.execute`), then render `layout` via a recursive `<LayoutNode>`. Per-source loading/error states. "Refresh" button clears cache.
- [ ] Implement core block renderers: `stat` (plain number + `style: "radial"` gauge, with `format` percent/duration/number), `chart` (bar + table fallback), `table`; and containers `row` / `column` / `grid` (with `gap`/sizing). All styled with the design system (Shadcn/Tailwind, Recharts).
- [ ] Create tRPC route `dashboard.execute` at [apps/server/src/trpc/routes/dashboard.ts](apps/server/src/trpc/routes/dashboard.ts): takes `specId` + `source` name + query params (+ later filters/aggs). Validates SELECT-only, binds params, scopes to user, applies the source's computations, returns `{ rows, metadata }`.
- [ ] Recognize the `dashboard` fence in [apps/mail/components/markdown-editor.tsx](apps/mail/components/markdown-editor.tsx): parse + zod-validate; render `<DashboardViewer />` on success, an error card on failure.
- [ ] Shared `applyComputations(rows, computations)` helper used by both the mock (client) and query (server) paths so they render identically.
**Tests:**
- [ ] Spec/zod validation at [apps/mail/__tests__/types/dashboard.test.ts](apps/mail/__tests__/types/dashboard.test.ts): valid query source, valid mock source, source with both → reject, source with neither → reject, malformed layout tree → reject.
- [ ] `<DashboardViewer />` at [apps/mail/__tests__/components/DashboardViewer.test.tsx](apps/mail/__tests__/components/DashboardViewer.test.tsx): **a fully mock-backed spec renders with no tRPC calls** (the demo path), a query-backed spec calls `dashboard.execute`, nested row/column layout renders children in order, `stat` radial renders the value, refresh clears cache.
- [ ] `dashboard.execute` at [apps/server/src/trpc/routes/__tests__/dashboard.test.ts](apps/server/src/trpc/routes/__tests__/dashboard.test.ts): valid SELECT scoped to user, computations applied, non-SELECT rejected, injection attempt (DROP/`;`) blocked.
- [ ] Run `pnpm --filter @cedar/mail test src/components/DashboardViewer`.
- [ ] Run `pnpm --filter @cedar/server test src/trpc/routes/dashboard`.
### Phase 2 — Block vocabulary: entityCard, profile, text, repeater
**Goal:** Add the composite-card blocks and the `repeater` so agents can build "a card per user, each with a profile + mini-chart". Per-row param interpolation drives per-row query executes.
- [ ] Add `entityCard` (a styled card container), `profile` (avatar + name + optional subtitle/fields), and `text` (markdown) block renderers in [apps/mail/components/DashboardViewer.tsx](apps/mail/components/DashboardViewer.tsx).
- [ ] Implement `repeater`: resolve its `source`, instantiate `template` per row, interpolate `{{row.*}}` into child fields and per-block `params`. Render as a list/grid of cards.
- [ ] Support per-block `params` with `sourceField: "row.<col>"` so a block's source executes once per repeater row (each execute independently cached).
- [ ] Guardrails: cap repeater row count (e.g. 50) with a "show more"; surface per-card loading/error without failing the whole dashboard.
**Tests:**
- [ ] `repeater` over a mock `users` source renders one `entityCard` per row, `{{row.name}}` interpolated into each `profile`.
- [ ] Per-row params: a `chart` block inside the template requests its source with the row's id (assert N executes for N rows, or N client-side filters for a mock source).
- [ ] `entityCard` + `profile` snapshot/structure test against the design system.
- [ ] Run `pnpm --filter @cedar/mail test src/components/DashboardViewer`.
### Phase 3 — Chart type switching (line, pie), column visibility + reordering
**Goal:** Expand `chart` to line + pie, add per-`table`/`chart` column visibility toggles and drag-to-reorder. Pure client-side customization, no refetch.
- [ ] Add `<LineChart>` and `<PieChart>` to the `chart` block; chart-type selector UI.
- [ ] Column visibility panel (checkbox per column) and drag-to-reorder (dnd-kit). Reorders/visibility live in block state.
- [ ] Ensure all chart types share one data shape; only rendering changes. Table respects visibility + order.
**Tests:**
- [ ] [apps/mail/__tests__/components/DashboardViewer.test.tsx](apps/mail/__tests__/components/DashboardViewer.test.tsx): bar → line → pie → table switching keeps data, changes rendering.
- [ ] Column visibility: uncheck a column → gone from table + chart legend. Drag-reorder changes table order.
- [ ] Run `pnpm --filter @cedar/mail test src/components/DashboardViewer`.
### Phase 4 — Filtering + aggregation controls
**Goal:** Filter rows and switch aggregation functions per query source. Changes re-execute the affected query sources (mock sources filter client-side).
- [ ] Filter panel from `filterableColumns` (select for enum, input + operator for string/number). On change, update state and re-execute the source.
- [ ] Aggregation picker from `aggregationOptions` (AVG/SUM/COUNT/MIN/MAX). On change, rebuild the source query and re-execute.
- [ ] Backend `dashboard.execute`: accept `filters` (validate operators: `=`, `!=`, `>`, `<`, `contains`) → WHERE injection; accept `aggregations` → validated function replacement.
- [ ] Mock sources apply filters/aggregations client-side so the controls behave identically in demos.
- [ ] Active-filter chips with clear-one / clear-all and a "Reset to defaults" button.
**Tests:**
- [ ] Component: filter `stage="closed_won"` → query source re-executes with filters; same filter on a mock source filters client-side, no tRPC call.
- [ ] Component: AVG → SUM rebuilds the query and re-executes.
- [ ] Backend [apps/server/src/trpc/routes/__tests__/dashboard.test.ts](apps/server/src/trpc/routes/__tests__/dashboard.test.ts): WHERE applied + scoped; invalid operator rejected; AVG → SUM replacement correct.
- [ ] Run `pnpm --filter @cedar/mail test src/components/DashboardViewer` and `pnpm --filter @cedar/server test src/trpc/routes/dashboard`.
### Phase 5 — Agent tool + knowledge-base templates (incl. mock-data demo specs)
**Goal:** Give agents a tool to author specs, plus a template library. Templates ship in two flavors: query-backed (real data) and mock-backed (instant demos).
- [ ] Create `createDashboardTool` at [apps/server/src/mastra/tools/dashboard/createDashboardTool.ts](apps/server/src/mastra/tools/dashboard/createDashboardTool.ts): takes data sources (query or mock), layout, blocks; validates against the spec schema; returns the ```` ```dashboard ```` block. SELECT-only enforced for query sources.
- [ ] Template library at [apps/server/src/services/knowledge-base/dashboards.ts](apps/server/src/services/knowledge-base/dashboards.ts): Win Rate Overview (stat + chart), Response Times by Stage, Activity Timeline, Deal Status Distribution, Per-Rep cards (repeater). Each with a **mock-backed variant** for demos.
- [ ] Integrate into agent instructions: prefer dashboards for statistical asks; use mock variants when asked for a demo / example.
**Tests:**
- [ ] [apps/server/src/mastra/tools/dashboard/__tests__/createDashboardTool.test.ts](apps/server/src/mastra/tools/dashboard/__tests__/createDashboardTool.test.ts): valid call returns a schema-valid spec; non-SELECT query source rejected; mock-only spec accepted.
- [ ] Each template parses + zod-validates; mock variants render with zero tRPC calls.
- [ ] Integration [apps/server/src/mastra/__tests__/integration/dashboard-generation.test.ts](apps/server/src/mastra/__tests__/integration/dashboard-generation.test.ts): agent calls tool → spec written to conversation → frontend parses + renders.
- [ ] Run `pnpm --filter @cedar/server test src/mastra/tools/dashboard`.
---
**Saved to**: `/Users/jesse/Desktop/Apps/cedar-mail-repos/cedar-mail-2/apps/mail/docs/agent-authored-dashboards.md`
**Summary**: 5 phases. Phase 1 establishes the restructured spec (named `dataSources` as a query/mock union, a recursive `layout` tree, and `stat`/`chart`/`table` blocks) with mock sources rendering with no backend — the demo path. Phase 2 adds `entityCard`/`profile`/`text`/`repeater` for composite cards. Phase 3 adds chart-type switching and column customization. Phase 4 adds filtering + aggregation (re-executes query sources, client-side for mock). Phase 5 adds the agent tool and a template library with query- and mock-backed variants.
When ready, run:
```bash
/implement-design /Users/jesse/Desktop/Apps/cedar-mail-repos/cedar-mail-2/apps/mail/docs/agent-authored-dashboards.md
```
Or use the [[implement-design]] skill to execute phase-by-phase and auto-commit after each phase.