Evidence — Code-Driven BI Dashboards

Overview

Evidence, the open-source BI framework that generates beautiful, interactive dashboards from SQL queries and Markdown. Helps developers build data reports as code, deploy them as static sites, and create self-service analytics without heavy BI tools.

Instructions

Project Setup

# Create a new Evidence project
npx degit evidence-dev/template my-dashboard
cd my-dashboard
npm install
npm run dev    # Dashboard at localhost:3000

Writing Reports

Evidence reports are Markdown files with embedded SQL:

<!-- pages/sales-overview.md — Sales dashboard page -->
# Sales Overview

```sql monthly_revenue
SELECT
  date_trunc('month', created_at) AS month,
  SUM(amount) AS revenue,
  COUNT(*) AS orders,
  SUM(amount) / COUNT(*) AS avg_order_value
FROM orders
WHERE created_at >= '2025-01-01'
GROUP BY 1
ORDER BY 1

Revenue has grown {fmt(monthlyrevenue[monthlyrevenue.length - 1].revenue, 'usd')} this month, a {pct_change} change from last month.

<LineChart data={monthly_revenue} x=month y=revenue yFmt=usd title="Monthly Revenue" />

<BarChart data={monthly_revenue} x=month y=orders title="Orders per Month" />

Revenue by Product

```sql productbreakdown SELECT p.name AS product, SUM(oi.quantity * oi.unitprice) AS revenue, SUM(oi.quantity) AS unitssold FROM orderitems oi JOIN products p ON p.id = oi.productid JOIN orders o ON o.id = oi.orderid WHERE o.createdat >= currentdate - interval '30 days' GROUP BY 1 ORDER BY 2 DESC LIMIT 10

<DataTable data={product_breakdown} rows=10>
  <Column id=product title="Product" />
  <Column id=revenue title="Revenue" fmt=usd />
  <Column id=units_sold title="Units Sold" fmt=num0 />
</DataTable>

<BarChart
  data={product_breakdown}
  x=product
  y=revenue
  yFmt=usd
  swapXY=true
  title="Top 10 Products by Revenue"
/>

Data Source Configuration

Connect to your databases:

# sources/mydb/connection.yaml — PostgreSQL connection
name: mydb
type: postgres
host: localhost
port: 5432
database: analytics
user: evidence_reader
password: ${POSTGRES_PASSWORD}    # From environment variable
ssl: true

# sources/duckdb/connection.yaml — DuckDB for file-based analytics
name: local
type: duckdb
filename: data/analytics.duckdb

# sources/bigquery/connection.yaml — Google BigQuery
name: warehouse
type: bigquery
project_id: my-gcp-project
dataset: analytics
credentials_path: ./gcp-key.json

# sources/csv/connection.yaml — CSV files
name: csvdata
type: csv
path: data/        # All CSV files in this directory become tables

Interactive Components

<!-- pages/cohort-analysis.md — Interactive filters -->
# Cohort Analysis

<!-- Dropdown filter -->
<Dropdown name=time_period title="Time Period">
  <DropdownOption value="7 days" valueLabel="Last 7 Days" />
  <DropdownOption value="30 days" valueLabel="Last 30 Days" />
  <DropdownOption value="90 days" valueLabel="Last 90 Days" />
</Dropdown>

<Dropdown name=segment title="Customer Segment" data={segments} value=id label=name />

```sql cohort_data
SELECT
  date_trunc('week', first_purchase) AS cohort_week,
  weeks_since_first AS week_number,
  COUNT(DISTINCT customer_id) AS customers,
  SUM(revenue) AS revenue
FROM customer_cohorts
WHERE first_purchase >= current_date - interval '${inputs.time_period}'
  AND segment = '${inputs.segment.value}'
GROUP BY 1, 2
ORDER BY 1, 2

<Heatmap data={cohortdata} x=weeknumber y=cohort_week value=customers title="Customer Retention by Cohort" />

<!-- Big number KPIs --> <BigValue data={cohort_data} value=customers title="Total Customers" fmt=num0 comparison=revenue comparisonTitle="Total Revenue" comparisonFmt=usd />

### Templated Pages

Generate pages dynamically from data:

<!-- pages/products/[product_name].md — One page per product -->

{params.product_name}

```sql productdetail SELECT * FROM products WHERE slug = '${params.productname}'

```sql product_sales
SELECT
  date_trunc('day', o.created_at) AS date,
  SUM(oi.quantity) AS units,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE p.slug = '${params.product_name}'
  AND o.created_at >= current_date - interval '90 days'
GROUP BY 1
ORDER BY 1

<LineChart data={productsales} x=date y=revenue yFmt=usd /> <LineChart data={productsales} x=date y=units />

### Deployment

Build static site

npm run build

Deploy to any static hosting

Vercel

npx vercel

Netlify

npx netlify deploy --prod --dir=build

Evidence Cloud (managed hosting)

npx evidence deploy

Schedule refreshes with cron

Evidence rebuilds queries at build time

Set up a cron job to rebuild periodically:

0 /4 cd /app/dashboard && npm run build && cp -r build /var/www/dashboard

## Examples


### Example 1: Creating a weekly SaaS metrics report

**User request:**

Build an Evidence dashboard that shows our weekly SaaS metrics — MRR, churn rate, new trials, and conversion rate — from our PostgreSQL database.

The agent scaffolds an Evidence project, configures the PostgreSQL connection in `evidence.plugins.yaml`, creates SQL queries for each metric (`select date_trunc('week', created_at) as week, sum(amount) as mrr from subscriptions...`), builds a Markdown page with `<LineChart>`, `<BigValue>`, and `<DataTable>` components, and adds date range inputs with `<DateRange>` for filtering.

### Example 2: Building a templated customer health page

**User request:**

I need a per-customer detail page in Evidence that shows usage trends, support tickets, and renewal date for each customer.

The agent creates a templated page at `pages/customers/[customer_id].md`, writes SQL queries that filter by `${params.customer_id}`, adds a customer index page with `<DataTable>` linking to each detail page, and includes `<BarChart>` for usage and `<Alert>` components for upcoming renewals.


## Guidelines

1. **SQL is the source of truth** — Write queries directly in Markdown; no abstraction layer between you and the data
2. **Use parameterized queries carefully** — Evidence supports `${inputs.x}` but sanitize inputs; prefer dropdown constraints over free text
3. **One page per topic** — Keep reports focused; use navigation for different areas (sales, product, customer)
4. **Templated pages for catalogs** — Use `[param].md` for product pages, customer profiles, or any entity-level reports
5. **Read-only database users** — Connect Evidence with a read-only database user; it should never write data
6. **Version control reports** — Evidence projects are code; store in Git, review in PRs, deploy via CI
7. **Build-time queries** — Queries run at build time, not on page load; schedule rebuilds based on data freshness needs
8. **DuckDB for local analysis** — Use DuckDB as a data source for CSV/Parquet files; no database server needed