# Interactive Charts

Embed live charts and data tables in markdown documents using SQL queries or static data. Charts render inline in chat, feed, and file viewer.

# Interactive Charts in Documents

TeamDay documents support interactive chart and table blocks that render inline in markdown. Charts can display static data (baked in by the AI agent) or query a live database connection on every view.

TeamDay also renders full Mermaid diagrams from normal `mermaid` fenced blocks. Use Mermaid for process maps, architecture diagrams, timelines, ER diagrams, sequence diagrams, and other visual explanations that are not better represented as numeric charts.

~~~
```mermaid
flowchart LR
  Query[Query data] --> Report[Write Markdown]
  Report --> Chart[Render chart]
  Report --> Diagram[Render Mermaid]
```
~~~

## Chart Block Syntax

Chart blocks use fenced code blocks with a special language annotation:

~~~
```data
[{"category":"Hiking","revenue":45000},{"category":"Running","revenue":62000}]
```
~~~

The annotation format is: `source:blockType(param=value, param="value with spaces")`

### Sources

| Source | Description |
|--------|-------------|
| `data` | Static data embedded in the block. The AI bakes numbers in when writing the report. |
| `sql` | Live read-only PostgreSQL query against an org data connection. Runs server-side when someone views the document. |

### Block Types

| Type | Description |
|------|-------------|
| `chart` | Renders a Chart.js visualization (bar, line, pie, etc.) |
| `table` | Renders a formatted data table with headers and rows |

### Parameters

| Parameter | Required | Description |
|-----------|----------|-------------|
| `type` | chart only | Chart type: `bar`, `line`, `pie`, `doughnut`, `area` |
| `title` | no | Display title above the chart/table |
| `connection` | sql only | Name of the org data connection. `production-db` resolves to an org secret such as `TEAMDAY_DATA_CONNECTION_PRODUCTION_DB_URL`. |
| `cache` | no | Cache duration: `30s`, `5m`, `1h`, `none`. Default: `5m` |

## Static Charts (`data:chart`)

Static charts contain their data directly in the block body as JSON. The AI agent computes or fetches the data during the conversation, then writes it into the markdown file.

### Array of Objects

The simplest format — an array of objects where the first key becomes the x-axis label and remaining keys become data series:

~~~
```data
[
  {"region": "Europe", "revenue": 145000, "orders": 2340},
  {"region": "Americas", "revenue": 98000, "orders": 1560},
  {"region": "Asia", "revenue": 67000, "orders": 890}
]
```
~~~

This renders a bar chart with "Europe", "Americas", "Asia" as labels and two series: "revenue" and "orders".

### Labels + Datasets Format

For more control, use the structured format with explicit labels and named datasets:

~~~
```data
{
  "labels": ["Jan", "Feb", "Mar", "Apr", "May", "Jun"],
  "datasets": [
    {"label": "Revenue", "values": [45000, 48000, 52000, 51000, 58000, 63000]},
    {"label": "Costs", "values": [32000, 33000, 34000, 33500, 35000, 36000]}
  ]
}
```
~~~

## Live SQL Charts (`sql:chart`)

SQL charts query a database connection every time the document is viewed. The query runs server-side with caching.

~~~
```sql
SELECT DATE_TRUNC('month', created_at) as month,
       COUNT(*) as orders
FROM orders
WHERE created_at > NOW() - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1
```
~~~

### Column Mapping

The SQL result columns map to chart axes automatically:

- **First column** = labels (x-axis)
- **Remaining columns** = data series (y-axis)
- **Column names** become series labels in the legend

```sql
SELECT month,    revenue,   cost,    profit
--     ^ labels  ^ series1  ^ series2 ^ series3
FROM monthly_summary
ORDER BY month
```

### Connection Name

The `connection` parameter maps to an organization secret that stores a PostgreSQL URL. For example, `connection=production-db` looks for:

1. `TEAMDAY_DATA_CONNECTION_PRODUCTION_DB_URL`
2. `PRODUCTION_DB_DATABASE_URL`
3. `PRODUCTION_DB_URL`

The database user in that URL should be read-only. TeamDay also enforces a server-side read-only transaction, accepts only `SELECT` / `WITH` queries, applies a 5 second statement timeout, and caps results at 1,000 rows.

Plain `sql` fenced blocks render as SQL code examples. Executable SQL-backed report blocks must use `sql:chart(...)` or `sql:table(...)`.

### Caching

Live queries are cached server-side to avoid hitting the database on every page view:

| Value | Duration |
|-------|----------|
| `30s` | 30 seconds |
| `5m` | 5 minutes (default) |
| `1h` | 1 hour |
| `none` | No caching, always fresh |

Charts display a "cached" indicator when serving cached data.

## Data Tables (`sql:table` / `data:table`)

Tables work the same way as charts but render as formatted HTML tables instead of visualizations:

~~~
```sql
SELECT product_name,
       SUM(quantity) as units_sold,
       SUM(revenue) as revenue
FROM order_items
GROUP BY product_name
ORDER BY revenue DESC
LIMIT 10
```
~~~

Static data tables:

~~~
```data
[
  {"team": "Engineering", "headcount": 12, "budget": "$1.2M"},
  {"team": "Marketing", "headcount": 5, "budget": "$400K"},
  {"team": "Sales", "headcount": 8, "budget": "$600K"}
]
```
~~~

Tables automatically:
- Format numbers with commas (e.g., 45,000)
- Show null values as "—"
- Highlight rows on hover
- Indicate when results are truncated (>1,000 rows)

## Chart Types

### Bar Chart
Best for comparing categories. Use when you have discrete categories on the x-axis.
```
type=bar
```

### Line Chart
Best for trends over time. Use when the x-axis represents a time series.
```
type=line
```

### Area Chart
Like a line chart with the area filled. Good for showing volume or cumulative data.
```
type=area
```

### Pie / Doughnut Chart
Best for showing composition or parts of a whole. Use with a single data series.
```
type=pie
type=doughnut
```

## Where Charts Render

Chart blocks render in these surfaces:

| Surface | Static (`data:`) | Live (`sql:`) |
|---------|:-:|:-:|
| Chat messages | Yes | Yes |
| File viewer / editor preview | Yes | Yes |
| Feed preview | Yes | Yes |

The feed shows truncated text previews. Click through to the full document to see rendered charts.

## Teaching Your Agent

To have your AI agent write reports with charts, include the chart syntax in the agent's system prompt or as a skill. Example instruction:

```
When writing reports with data, embed charts using this syntax:

  ```data
  [{"label": "...", "value": ...}, ...]
  ```

For live dashboards, use SQL chart blocks:

  ```sql
  SELECT ...
  ```

Available chart types: bar, line, pie, doughnut, area
Available connections: production-db, analytics-db
```

## Limitations

- **Read-only queries** — INSERT, UPDATE, DELETE, DROP, and other mutations are blocked
- **5-second timeout** — queries that take longer are cancelled
- **1,000 row limit** — results exceeding this are truncated
- **PostgreSQL only** for live queries — MySQL, BigQuery, and others coming soon
- **No cross-database joins** — each chart block queries a single connection

## API Reference

The chart blocks use the [Connections Query API](https://docs.teamday.ai/api/connections) under the hood. You can call it directly for programmatic access.