artifacts/dashboard_story_example.py
import json
import sys
from pathlib import Path
import duckdb
sys.path.append(str(Path(__file__).resolve().parents[3]))
from scripts._lib.motherduck_artifact_utils import artifact_session
def one(conn: duckdb.DuckDBPyConnection, sql: str) -> dict:
cursor = conn.execute(sql)
columns = [col[0] for col in cursor.description]
row = cursor.fetchone()
return dict(zip(columns, row))
def many(conn: duckdb.DuckDBPyConnection, sql: str) -> list[dict]:
cursor = conn.execute(sql)
columns = [col[0] for col in cursor.description]
return [dict(zip(columns, row)) for row in cursor.fetchall()]
def main() -> None:
with artifact_session(slug="motherduck-build-dashboard", database_keys=["analytics"]) as session:
conn = session.conn
orders_table = session.table("analytics", "main", "orders")
conn.execute(
f"""
CREATE TABLE {orders_table} (
order_id INTEGER,
order_date DATE,
category VARCHAR,
customer_id INTEGER,
revenue DOUBLE
)
"""
)
conn.executemany(
f"INSERT INTO {orders_table} VALUES (?, ?, ?, ?, ?)",
[
(1, "2026-01-03", "Database", 101, 1200.0),
(2, "2026-01-07", "Compute", 102, 800.0),
(3, "2026-02-11", "Database", 101, 1600.0),
(4, "2026-02-21", "Sharing", 103, 400.0),
(5, "2026-03-03", "Compute", 104, 2200.0),
(6, "2026-03-18", "Database", 105, 900.0),
],
)
result = {
"backend": session.describe(),
"story": "Revenue and product mix",
"kpis": one(
conn,
f"""
SELECT
SUM(revenue) AS total_revenue,
COUNT(DISTINCT order_id) AS order_count,
COUNT(DISTINCT customer_id) AS customer_count
FROM {orders_table}
""",
),
"trend": many(
conn,
f"""
SELECT strftime(date_trunc('month', order_date), '%Y-%m') AS month,
SUM(revenue) AS revenue
FROM {orders_table}
GROUP BY 1
ORDER BY 1
""",
),
"breakdown": many(
conn,
f"""
SELECT category, SUM(revenue) AS revenue
FROM {orders_table}
GROUP BY 1
ORDER BY revenue DESC
""",
),
"detail": many(
conn,
f"""
SELECT strftime(order_date, '%Y-%m-%d') AS order_date,
category,
revenue
FROM {orders_table}
ORDER BY order_date DESC
LIMIT 5
""",
),
}
print(json.dumps(result, indent=2))
if __name__ == "__main__":
main()
artifacts/dashboard_story_example.ts
export {};
declare const process: { env: Record<string, string | undefined> };
type OrderRow = {
order_id: number;
order_date: string;
category: string;
customer_id: number;
revenue: number;
};
function normalizeMetadataValue(value: string | undefined, fallback: string): string {
const raw = (value ?? "").trim();
if (!raw) return fallback;
const normalized = raw.replace(/[^A-Za-z0-9._-]+/g, "-").replace(/^[-._]+|[-._]+$/g, "");
return normalized || fallback;
}
function buildUseCaseUserAgent(): string {
const harness = normalizeMetadataValue(process.env.MOTHERDUCK_AGENT_HARNESS, "unknown");
const llm = normalizeMetadataValue(process.env.MOTHERDUCK_AGENT_LLM, "unknown");
return `agent-skills/3.0.0(harness-${harness};llm-${llm})`;
}
const orders: OrderRow[] = [
{ order_id: 1, order_date: "2026-01-03", category: "Database", customer_id: 101, revenue: 1200.0 },
{ order_id: 2, order_date: "2026-01-07", category: "Compute", customer_id: 102, revenue: 800.0 },
{ order_id: 3, order_date: "2026-02-11", category: "Database", customer_id: 101, revenue: 1600.0 },
{ order_id: 4, order_date: "2026-02-21", category: "Sharing", customer_id: 103, revenue: 400.0 },
{ order_id: 5, order_date: "2026-03-03", category: "Compute", customer_id: 104, revenue: 2200.0 },
{ order_id: 6, order_date: "2026-03-18", category: "Database", customer_id: 105, revenue: 900.0 },
];
const totalRevenue = orders.reduce((sum, row) => sum + row.revenue, 0);
const orderCount = new Set(orders.map((row) => row.order_id)).size;
const customerCount = new Set(orders.map((row) => row.customer_id)).size;
const trendMap = new Map<string, number>();
for (const row of orders) {
const month = row.order_date.slice(0, 7);
trendMap.set(month, (trendMap.get(month) ?? 0) + row.revenue);
}
const trend = Array.from(trendMap.entries())
.map(([month, revenue]) => ({ month, revenue }))
.sort((a, b) => a.month.localeCompare(b.month));
const breakdownMap = new Map<string, number>();
for (const row of orders) {
breakdownMap.set(row.category, (breakdownMap.get(row.category) ?? 0) + row.revenue);
}
const breakdown = Array.from(breakdownMap.entries())
.map(([category, revenue]) => ({ category, revenue }))
.sort((a, b) => b.revenue - a.revenue);
const detail = [...orders]
.sort((a, b) => b.order_date.localeCompare(a.order_date))
.slice(0, 5)
.map(({ order_date, category, revenue }) => ({ order_date, category, revenue }));
const result = {
backend: {
mode: "typescript-companion",
databases: { analytics: "analytics" },
user_agent: buildUseCaseUserAgent(),
},
story: "Revenue and product mix",
kpis: {
total_revenue: totalRevenue,
order_count: orderCount,
customer_count: customerCount,
},
trend,
breakdown,
detail,
};
console.log(JSON.stringify(result, null, 2));
references/DASHBOARD_IMPLEMENTATION_GUIDE.md
<!-- Preserved detailed implementation guidance moved from SKILL.md so the main skill can stay concise. -->
# Build an Analytics Dashboard
Use this skill when creating a multi-chart, multi-KPI interactive dashboard with live MotherDuck data. This is a use-case skill -- it ties together `motherduck-explore`, `motherduck-query`, `motherduck-create-dive`, and `motherduck-duckdb-sql` into a single end-to-end workflow.
## Contents
- [Source Of Truth](#source-of-truth)
- [Verified Delivery Defaults](#verified-delivery-defaults)
- [Validation Signals](#validation-signals)
- [Language Focus: TypeScript/Javascript and Python](#language-focus-typescriptjavascript-and-python)
- [TypeScript/TSX Starter](#typescripttsx-starter)
- [Python Validation Starter](#python-validation-starter)
- [When to Use](#when-to-use)
- [Prerequisites](#prerequisites)
- [Dashboard Workflow](#dashboard-workflow)
- [Dashboard Design Principles](#dashboard-design-principles)
- [Key Rules](#key-rules)
- [Common Mistakes](#common-mistakes)
- [Related Skills](#related-skills)
## Source Of Truth
- Prefer the current MotherDuck Dive guide and public Dives docs first.
- If MotherDuck MCP is available, call `get_dive_guide` before saving or updating a dashboard Dive.
- Apply the design defaults in `motherduck-create-dive` before copying presentation classes from this reference. Treat the examples here as query and composition patterns; the responsive shell, theme tokens, filter surface, and viewport QA come from the design skill.
- Keep the dashboard guidance aligned with the documented product posture:
- Dives are for live workspace analytics and the long tail of questions
- heavy shaping belongs in SQL, not in React
- small previews are for iteration; saved dashboards should query live data
- for full customer-facing analytics with per-customer isolation, see `motherduck-build-cfa-app`
## Verified Delivery Defaults
The repeated repo runs point to a stable dashboard posture:
- keep one dashboard story per Dive instead of mixing several unrelated narratives
- shape metrics and breakdowns in SQL first, then render the result in TSX
- use small previews for iteration, but keep saved dashboards live against MotherDuck data
- escalate to `motherduck-build-cfa-app` when the request becomes a customer-facing product surface rather than a workspace dashboard
## Validation Signals
Use these signals for testing, review, and regression checks. They are not an instruction to include a separate "Validation Signals" section in normal user-facing replies.
- run `artifacts/dashboard_story_example.py` against a temporary MotherDuck database
- verify the output contains the expected sections: `kpis`, `trend`, `breakdown`, and `detail`
- verify the dashboard still tells one coherent story instead of several unrelated narratives
- treat dashboard plans without explicit section-to-SQL mapping as incomplete
## Language Focus: TypeScript/Javascript and Python
- Prefer **TypeScript/TSX** for dashboard UI examples because Dives are React components.
- Prefer **Python** for:
- preparing the source dataset
- validating aggregations before visualization
- automating dashboard refresh or publication workflows outside the Dive code
- The normal split is:
- SQL for metrics and aggregation
- TypeScript/TSX for rendering
- Python only when data prep or validation is part of the task
## TypeScript/TSX Starter
```tsx
import { useSQLQuery } from "@motherduck/react-sql-query";
import { LineChart, Line, XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer } from "recharts";
const N = (v: unknown): number => (v != null ? Number(v) : 0);
export default function MonthlyRevenueDashboard() {
const kpis = useSQLQuery(`
SELECT SUM(revenue) AS total_revenue,
COUNT(DISTINCT order_id) AS order_count,
ROUND(AVG(revenue), 2) AS avg_order_value
FROM "analytics"."main"."orders"
`);
const trend = useSQLQuery(`
SELECT strftime(date_trunc('month', order_date), '%Y-%m') AS month,
SUM(revenue) AS revenue
FROM "analytics"."main"."orders"
GROUP BY 1 ORDER BY 1
`);
const kpiRows = Array.isArray(kpis.data) ? kpis.data : [];
const trendData = (Array.isArray(trend.data) ? trend.data : []).map(r => ({
month: r.month as string,
revenue: N(r.revenue),
}));
return (
<div className="min-h-screen px-4 py-6 sm:p-6 lg:p-8" style={{ background: "#f8f8f8" }}>
<h1 className="text-2xl font-semibold" style={{ color: "#231f20" }}>Revenue</h1>
<p className="text-sm mb-6" style={{ color: "#6a6a6a" }}>Monthly overview</p>
<div className="mb-8 grid grid-cols-1 gap-4 sm:grid-cols-2 lg:grid-cols-3 lg:gap-6">
{[
{ label: "Total Revenue", value: kpiRows[0]?.total_revenue, fmt: (v: number) => `$${(v / 1000).toFixed(0)}K` },
{ label: "Orders", value: kpiRows[0]?.order_count, fmt: (v: number) => v.toLocaleString() },
{ label: "Avg Order", value: kpiRows[0]?.avg_order_value, fmt: (v: number) => `$${v.toFixed(2)}` },
].map(({ label, value, fmt }) => (
<div key={label}>
{kpis.isLoading ? (
<div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
) : (
<p className="text-3xl font-bold tabular-nums sm:text-4xl" style={{ color: "#231f20" }}>{fmt(N(value))}</p>
)}
<p className="text-sm mt-2" style={{ color: "#6a6a6a" }}>{label}</p>
</div>
))}
</div>
{trend.isLoading ? (
<div className="bg-gray-100 animate-pulse rounded" style={{ height: 250 }} />
) : (
<ResponsiveContainer width="100%" height={250}>
<LineChart data={trendData}>
<CartesianGrid strokeDasharray="3 3" stroke="#eee" />
<XAxis dataKey="month" fontSize={11} />
<YAxis tickFormatter={(v) => `$${(v / 1000).toFixed(0)}K`} fontSize={11} />
<Tooltip formatter={(v: number) => `$${v.toLocaleString()}`} />
<Line type="linear" dataKey="revenue" stroke="#0777b3" strokeWidth={2} dot={false} />
</LineChart>
</ResponsiveContainer>
)}
</div>
);
}
```
## Python Validation Starter
```python
import duckdb
USE_CASE_USER_AGENT = "agent-skills/3.0.0(harness-<harness>;llm-<llm>)"
conn = duckdb.connect(f"md:analytics?custom_user_agent={USE_CASE_USER_AGENT}")
rows = conn.sql("""
SELECT strftime(date_trunc('month', order_date), '%Y-%m') AS month,
SUM(revenue) AS revenue
FROM "analytics"."main"."orders"
GROUP BY 1
ORDER BY 1
""").fetchall()
conn.close()
```
## When to Use
- The user asks for a dashboard, report, or multi-section data app.
- The output requires more than a single chart -- typically KPIs, trend charts, breakdowns, and detail tables combined.
- The data lives in MotherDuck and the result should be a saved, shareable Dive.
- The request is a workspace analytics surface. For full customer-facing apps with per-customer isolation, see `motherduck-build-cfa-app`.
## Prerequisites
- Data must already exist in MotherDuck. Use `motherduck-explore` to discover databases and tables before starting.
- Familiarity with `motherduck-create-dive` skill for Dive mechanics (useSQLQuery, N() helper, Recharts, Tailwind, loading states).
---
## Dashboard Workflow
Follow these six steps in order. Do not skip steps -- each one depends on the output of the previous step.
For implementation:
- prefer **TypeScript/TSX** for the dashboard UI because Dives are React components
- prefer **Python** for validating the source metrics before the UI is written
- do not move grouping, filtering, or date formatting out of SQL just because the UI is in TypeScript
### Step 1: Explore Available Data
Use the `motherduck-explore` skill to discover what data is available and understand its shape.
1. List databases with `MD_ALL_DATABASES()`.
2. List tables in the target database with `duckdb_tables()`.
3. Inspect columns with `duckdb_columns()` to understand types and nullability.
4. Run `SUMMARIZE` on each key table to understand distributions, ranges, null rates, and cardinality.
5. **Check date ranges** on every time column -- the data may not cover the period you expect, which changes the dashboard story entirely.
6. Sample rows with `LIMIT 10` to see actual values.
A quick date range check prevents building a dashboard on stale or misaligned data:
```sql
SELECT min(order_date) AS earliest,
max(order_date) AS latest,
count(*) AS total_rows
FROM "my_db"."main"."orders";
```
Identify the following before proceeding:
- **Key metrics** -- the numeric columns that will become KPIs and chart values (e.g., revenue, order count, session duration).
- **Key dimensions** -- the categorical or temporal columns used for grouping, filtering, and axis labels (e.g., category, region, date).
- **Date/time columns** -- the timestamps used for time-series trends.
- **Relationships** -- how tables join together (shared keys like customer_id, product_id).
Do not proceed to Step 2 until you can name the exact columns you will query.
---
### Step 2: Define the Dashboard Story
Every dashboard tells ONE story. Pick a single narrative focus before writing any code.
**Common dashboard stories:**
- Revenue and sales performance
- Product usage and engagement
- Operational efficiency and reliability
- Customer behavior and retention
**Example starting composition:** Adapt this to the decision and available data; the counts below are defaults, not limits.
1. **KPIs (3-5 numbers).** These are the most important metrics at a glance. Pick the numbers the user would check first every morning. Examples: Total Revenue, Order Count, Average Order Value, Customer Count.
2. **Primary chart.** Show the central comparison or trend; use a time-series only when change over time is the question. Examples: Monthly Revenue (LineChart), Daily Active Users (AreaChart), Weekly Request Volume (AreaChart).
3. **Secondary chart (0-1 optional).** This shows a breakdown or comparison. Examples: Revenue by Category (BarChart), Error Rate by Endpoint (BarChart), Feature Usage (BarChart).
4. **Detail table (0-1 optional).** Use a table when the user needs exact values or when there are more than 8 categories. Examples: Top 10 Products by Revenue, Slowest Endpoints, Top Pages by Views.
Keep sections that help the audience decide or investigate. Split unrelated narratives into separate dashboards; do not split a coherent requested analysis merely to satisfy a chart quota.
---
### Step 3: Write the SQL Queries
Write one `useSQLQuery` call per dashboard section. Separate queries ensure independent loading states and keep each query simple and debuggable.
**Query design rules:**
1. **One query per section.** KPIs get one query. Each chart gets its own query. The table gets its own query.
2. **Pre-aggregate in SQL, not JavaScript.** Compute sums, averages, counts, and ratios in SQL. The React component should only render values, never compute them.
3. **Format dates in SQL.** Use `strftime(date_trunc('month', ts), '%Y-%m')` or `strftime(date_trunc('day', ts), '%Y-%m-%d')`. Never parse or format dates in JavaScript.
4. **Use fully qualified table names.** Always reference tables as `"database"."schema"."table"`.
5. **Order and limit in SQL.** Sort time-series data with `ORDER BY 1`. Limit detail tables with `LIMIT 10` or `LIMIT 20`.
6. **Use CTEs for complex logic.** Break multi-step calculations into CTEs for readability.
7. **Preview cheaply, save live.** Use small subsets or aggregates while iterating, then keep the final saved Dive wired to live `useSQLQuery` calls.
**Example queries for a sales dashboard:**
```sql
-- KPI query: returns one row with all KPI values
SELECT SUM(revenue) AS total_revenue,
COUNT(DISTINCT order_id) AS order_count,
ROUND(AVG(revenue), 2) AS avg_order_value,
COUNT(DISTINCT customer_id) AS customer_count
FROM "my_db"."main"."orders"
-- Trend query: monthly revenue for a line chart
SELECT strftime(date_trunc('month', order_date), '%Y-%m') AS month,
SUM(revenue) AS revenue
FROM "my_db"."main"."orders"
GROUP BY 1 ORDER BY 1
-- Breakdown query: revenue by category for a bar chart
SELECT category, SUM(revenue) AS revenue
FROM "my_db"."main"."orders"
GROUP BY 1 ORDER BY 2 DESC LIMIT 8
-- Detail query: top products for a table
SELECT product_name, category,
SUM(revenue) AS revenue, COUNT(*) AS orders
FROM "my_db"."main"."order_items"
GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 10
```
Use the `motherduck-query` skill to test each query against real data before embedding it in the Dive.
---
### Step 4: Design the Layout
Follow these layout conventions for a consistent, professional dashboard.
**Structure (top to bottom):**
1. **Title** -- `text-2xl font-bold mb-8` with `color: "#231f20"`.
2. **KPI group** -- start with `grid grid-cols-1 gap-4 sm:grid-cols-2 lg:grid-cols-4 lg:gap-6`; preserve DOM reading order as the grid expands.
3. **Primary chart** -- full width, 200-280px height, `mb-10`.
4. **Secondary chart** -- full width, 200-280px height, `mb-10` (optional).
5. **Detail table** -- full width with `overflow-x-auto` (optional).
**Styling rules:**
- Outermost container: `className="min-h-screen px-4 py-6 sm:p-6 lg:p-8"` with semantic theme tokens and a centered, fluid inner canvas.
- No card borders, no card shadows. Content floats on the background.
- KPI labels: `text-sm` with `color: "#6a6a6a"`.
- KPI values: `text-3xl sm:text-4xl font-bold tabular-nums` with the semantic text token; add a bounded sparkline or progress visual when trend data exists.
- Section headings: `text-lg font-semibold mb-4` with `color: "#231f20"`.
- Use inline `style` for brand colors. Never use Tailwind bracket syntax (`w-[200px]`).
**Color palette for charts:**
```tsx
const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];
```
This palette is an example. Use consistent semantic colors from the selected theme across charts, preserving the existing visual system during scoped edits.
---
### Step 5: Build the Dive
Assemble the React component using `motherduck-create-dive` skill patterns.
**Component structure:**
```tsx
import { useSQLQuery } from "@motherduck/react-sql-query";
import {
LineChart, Line, BarChart, Bar, AreaChart, Area,
XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer
} from "recharts";
import { Loader2 } from "lucide-react";
const N = (v: unknown): number => (v != null ? Number(v) : 0);
const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];
export default function MyDashboard() {
// Separate queries for each dashboard section
const { data: kpiData, isLoading: kpiLoading } = useSQLQuery(`SELECT ... -- KPIs`);
const kpiRows = Array.isArray(kpiData) ? kpiData : [];
const { data: trendData, isLoading: trendLoading } = useSQLQuery(`SELECT ... -- Time series`);
const trendRows = Array.isArray(trendData) ? trendData : [];
const { data: breakdownData, isLoading: breakdownLoading } = useSQLQuery(`SELECT ... -- Breakdown`);
const breakdownRows = Array.isArray(breakdownData) ? breakdownData : [];
const { data: detailData, isLoading: detailLoading } = useSQLQuery(`SELECT ... -- Detail table`);
const detailRows = Array.isArray(detailData) ? detailData : [];
return (
<div className="min-h-screen px-4 py-6 sm:p-6 lg:p-8" style={{ backgroundColor: "#f8f8f8" }}>
{/* Title */}
{/* KPIs with kpiLoading skeleton */}
{/* Primary chart with trendLoading spinner */}
{/* Secondary chart with breakdownLoading spinner */}
{/* Detail table with detailLoading skeleton */}
</div>
);
}
```
Dive component mechanics -- `export default function`, the `N()` helper, `Array.isArray` guards, per-section loading skeletons and spinners, `ResponsiveContainer` -- are owned by `motherduck-create-dive`. Follow that skill's rules; `references/DASHBOARD_PATTERNS.md` shows them applied in complete dashboard templates.
The dashboard-specific rule: each section renders its own loading state independently. Never use a single full-page spinner.
Create the Dive via `MD_CREATE_DIVE` (SQL) or `save_dive` (MCP). When MCP is available, call `get_dive_guide` first.
---
### Step 6: Iterate
After the initial Dive is created:
1. Open the Dive at the returned URL.
2. Verify that all sections load with real data.
3. Check that KPI values are reasonable and formatted correctly.
4. Confirm charts display the expected trends and categories.
5. Update via `MD_UPDATE_DIVE_CONTENT` (SQL) or `update_dive` (MCP) to fix issues.
Common iteration fixes:
- Adjust date truncation granularity (day vs. week vs. month).
- Change chart type (LineChart to AreaChart, or BarChart to table).
- Tune LIMIT values for breakdown charts and detail tables.
- Add or remove KPIs based on user feedback.
---
## Dashboard Design Principles
1. **Start with KPIs.** The most important numbers appear at the top. A user should understand the current state of the business in the first 2 seconds.
2. **One chart shows the primary trend.** This is almost always a time-series (LineChart or AreaChart). It answers "how is the main metric changing over time?"
3. **Second chart shows a breakdown or comparison.** This is usually a BarChart. It answers "where is the main metric coming from?" or "how do segments compare?"
4. **Tables for detail.** Use a table when there are more than 8 categories or when the user needs exact values. Tables are clearer than bar charts with many bars.
5. **One dashboard, one narrative.** Do not mix unrelated stories (e.g., sales performance and server health) in one dashboard. Build separate dashboards instead.
6. **Consistent colors across all charts.** Use shared theme tokens and stable series colors in both light and dark modes.
7. **Compute business metrics in SQL.** React handles presentation, formatting, and UI state; keep expensive shaping and metric definitions in SQL.
---
## Key Rules
- **One dashboard = one story.** Do not mix unrelated metrics.
- **Max 5 KPIs, 2 charts, 1 table.** More than this and the dashboard becomes noisy.
- **Every section has independent loading.** Each `useSQLQuery` manages its own `isLoading` state.
- **Pre-aggregate in SQL, not JavaScript.** The component renders values; it does not compute them.
- **Format dates in SQL with `strftime()`.** Never use `new Date()` or date parsing in JavaScript.
- **Use fully qualified table names.** Always `"database"."schema"."table"`.
- **Background `#f8f8f8`, no card borders, no card shadows.**
- **Follow `motherduck-create-dive` component rules.** `export default function`, `N()` for all numeric query values, `Array.isArray` guards, no Tailwind bracket syntax.
---
## Common Mistakes
1. **Too many charts.** The dashboard becomes noisy and loses focus. Limit to 2 charts maximum. If you need more, build a second dashboard.
2. **One giant query instead of separate queries per section.** Each section should have its own `useSQLQuery` call. One query for everything means one loading state for everything -- the dashboard feels slow and errors cascade.
3. **Formatting and computing in JavaScript instead of SQL.** Compute sums, averages, ratios, and date formatting in SQL. The React component only renders the pre-computed values.
4. **Inconsistent colors across charts.** Define `COLORS` once and use the same array for every chart. Do not pick ad-hoc colors.
5. **Missing loading states.** Every section needs its own loading skeleton or spinner. A blank section while data loads looks broken.
6. **Forgetting the `N()` helper.** Query values are `unknown`. Without `N()`, numeric operations return `NaN` and charts render blank.
7. **Parsing dates in JavaScript.** Use `strftime()` in SQL. JavaScript `new Date()` parsing is unreliable and causes timezone bugs.
8. **Not guarding data with `Array.isArray`.** Calling `.map()` on undefined during the loading phase crashes the entire Dive.
9. **Using Tailwind bracket syntax.** `w-[200px]` and `text-[#333]` do not work in Dives. Use inline `style` instead.
10. **Card borders and shadows.** The design system uses a flat `#f8f8f8` background with no containers. Do not wrap sections in bordered cards.
---
## Related Skills
- `motherduck-explore` -- Discover databases, tables, columns, and data shares.
- `motherduck-query` -- Execute and optimize analytical SQL queries against MotherDuck.
- `motherduck-create-dive` -- Visualization mechanics: useSQLQuery, Recharts, Tailwind, loading states.
- `motherduck-duckdb-sql` -- DuckDB SQL syntax reference and function lookup.
references/DASHBOARD_PATTERNS.md
# Dashboard Patterns
Query and composition templates for common dashboard stories. Each shows complete Dive data wiring with proper imports, independent loading states, and the `N()` helper. Before using one, apply the design defaults in `motherduck-create-dive`: replace the presentation shell and literal palette with the responsive grid, semantic light/dark tokens, filter surface, and viewport QA from its responsive design-system reference. Replace placeholder table names with actual fully qualified names.
## Contents
- [1. Sales Dashboard](#1-sales-dashboard)
- [2. Product Analytics Dashboard](#2-product-analytics-dashboard)
- [3. Operational Metrics Dashboard](#3-operational-metrics-dashboard)
- [Adapting Templates](#adapting-templates)
---
## 1. Sales Dashboard
KPIs: Total Revenue, Order Count, Avg Order Value, Customer Count. Charts: Monthly Revenue (Line), Revenue by Category (Bar). Table: Top 10 Products.
```tsx
import { useSQLQuery } from "@motherduck/react-sql-query";
import {
LineChart, Line, BarChart, Bar,
XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer
} from "recharts";
import { Loader2 } from "lucide-react";
const N = (v: unknown): number => (v != null ? Number(v) : 0);
const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];
export default function SalesDashboard() {
const { data: kpiData, isLoading: kpiLoading, isError: kpiError, error: kpiMsg } = useSQLQuery(`
SELECT SUM(revenue) AS total_revenue, COUNT(DISTINCT order_id) AS order_count,
ROUND(AVG(revenue), 2) AS avg_order_value, COUNT(DISTINCT customer_id) AS customer_count
FROM "my_db"."main"."orders"
`);
const kpiRows = Array.isArray(kpiData) ? kpiData : [];
const { data: trendData, isLoading: trendLoading } = useSQLQuery(`
SELECT strftime(date_trunc('month', order_date), '%Y-%m') AS month, SUM(revenue) AS revenue
FROM "my_db"."main"."orders" GROUP BY 1 ORDER BY 1
`);
const trendRows = Array.isArray(trendData) ? trendData : [];
const { data: catData, isLoading: catLoading } = useSQLQuery(`
SELECT category, SUM(revenue) AS revenue
FROM "my_db"."main"."order_items" GROUP BY 1 ORDER BY 2 DESC LIMIT 8
`);
const catRows = Array.isArray(catData) ? catData : [];
const { data: detailData, isLoading: detailLoading } = useSQLQuery(`
SELECT product_name, category, SUM(revenue) AS revenue, COUNT(*) AS orders
FROM "my_db"."main"."order_items" GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 10
`);
const detailRows = Array.isArray(detailData) ? detailData : [];
const KPI = ({ label, value, prefix = "" }: {
label: string; value: string; prefix?: string;
}) => (
<div>
<p className="text-sm mb-1" style={{ color: "#6a6a6a" }}>{label}</p>
{kpiLoading ? (
<div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
) : (
<p className="text-3xl font-bold tabular-nums sm:text-4xl" style={{ color: "#231f20" }}>{prefix}{value}</p>
)}
</div>
);
return (
<div className="min-h-screen px-4 py-6 sm:p-6 lg:p-8" style={{ backgroundColor: "#f8f8f8" }}>
<h1 className="text-2xl font-bold mb-8" style={{ color: "#231f20" }}>Sales Dashboard</h1>
{/* KPIs */}
<div className="mb-10 grid grid-cols-1 gap-4 sm:grid-cols-2 lg:grid-cols-4 lg:gap-6">
<KPI label="Total Revenue" prefix="$" value={`${(N(kpiRows[0]?.total_revenue) / 1000).toFixed(0)}K`} />
<KPI label="Order Count" value={N(kpiRows[0]?.order_count).toLocaleString()} />
<KPI label="Avg Order Value" prefix="$" value={N(kpiRows[0]?.avg_order_value).toFixed(2)} />
<KPI label="Customers" value={N(kpiRows[0]?.customer_count).toLocaleString()} />
</div>
{kpiError && (
<p className="text-sm mb-4" style={{ color: "#bd4e35" }}>
Failed to load KPIs: {kpiMsg?.message || "Unknown error"}
</p>
)}
{/* Chart 1: Monthly Revenue Trend */}
<div className="mb-10">
<h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Monthly Revenue</h2>
{trendLoading ? (
<div className="flex items-center justify-center h-64">
<Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
</div>
) : (
<ResponsiveContainer width="100%" height={260}>
<LineChart data={trendRows}>
<CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
<XAxis dataKey="month" tick={{ fontSize: 12 }} />
<YAxis tick={{ fontSize: 12 }} tickFormatter={(v) => `$${(v / 1000).toFixed(0)}K`} />
<Tooltip formatter={(value: number) => `$${value.toLocaleString()}`} />
<Line type="monotone" dataKey="revenue" stroke={COLORS[0]} strokeWidth={2} dot={false} />
</LineChart>
</ResponsiveContainer>
)}
</div>
{/* Chart 2: Revenue by Category */}
<div className="mb-10">
<h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Revenue by Category</h2>
{catLoading ? (
<div className="flex items-center justify-center h-64">
<Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
</div>
) : (
<ResponsiveContainer width="100%" height={260}>
<BarChart data={catRows}>
<CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
<XAxis dataKey="category" tick={{ fontSize: 12 }} />
<YAxis tick={{ fontSize: 12 }} tickFormatter={(v) => `$${(v / 1000).toFixed(0)}K`} />
<Tooltip formatter={(value: number) => `$${value.toLocaleString()}`} />
<Bar dataKey="revenue" fill={COLORS[0]} radius={[4, 4, 0, 0]} />
</BarChart>
</ResponsiveContainer>
)}
</div>
{/* Table: Top 10 Products by Revenue */}
<div>
<h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Top Products by Revenue</h2>
{detailLoading ? (
<div className="space-y-3">
{[...Array(5)].map((_, i) => (
<div key={i} className="h-8 bg-gray-200 animate-pulse rounded" />
))}
</div>
) : (
<div className="overflow-x-auto">
<table className="w-full text-sm">
<thead>
<tr className="border-b border-gray-200">
<th className="text-left py-3 font-semibold" style={{ color: "#231f20" }}>Product</th>
<th className="text-left py-3 font-semibold" style={{ color: "#231f20" }}>Category</th>
<th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Revenue</th>
<th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Orders</th>
</tr>
</thead>
<tbody>
{detailRows.map((row, i) => (
<tr key={i} className="border-b border-gray-200"
style={{ backgroundColor: i % 2 === 0 ? "transparent" : "#f0f0f0" }}>
<td className="py-3" style={{ color: "#231f20" }}>{row.product_name}</td>
<td className="py-3" style={{ color: "#6a6a6a" }}>{row.category}</td>
<td className="text-right py-3" style={{ color: "#231f20" }}>
${N(row.revenue).toLocaleString()}
</td>
<td className="text-right py-3" style={{ color: "#6a6a6a" }}>
{N(row.orders).toLocaleString()}
</td>
</tr>
))}
</tbody>
</table>
</div>
)}
</div>
</div>
);
}
```
## 2. Product Analytics Dashboard
KPIs: Active Users, Sessions, Avg Session Duration, Conversion Rate. Charts: Daily Active Users (Area), Feature Usage (Bar). Table: Top Pages.
```tsx
import { useSQLQuery } from "@motherduck/react-sql-query";
import {
AreaChart, Area, BarChart, Bar,
XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer
} from "recharts";
import { Loader2 } from "lucide-react";
const N = (v: unknown): number => (v != null ? Number(v) : 0);
const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];
export default function ProductAnalyticsDashboard() {
const { data: kpiData, isLoading: kpiLoading, isError: kpiError, error: kpiMsg } = useSQLQuery(`
SELECT COUNT(DISTINCT user_id) AS active_users, COUNT(DISTINCT session_id) AS total_sessions,
ROUND(AVG(session_duration_sec) / 60.0, 1) AS avg_session_min,
ROUND(100.0 * SUM(CASE WHEN converted = true THEN 1 ELSE 0 END) / COUNT(*), 1) AS conversion_rate
FROM "product_db"."main"."sessions"
WHERE session_start >= CURRENT_DATE - INTERVAL 30 DAY
`);
const kpiRows = Array.isArray(kpiData) ? kpiData : [];
const { data: dauData, isLoading: dauLoading } = useSQLQuery(`
SELECT strftime(date_trunc('day', session_start), '%Y-%m-%d') AS day,
COUNT(DISTINCT user_id) AS active_users
FROM "product_db"."main"."sessions"
WHERE session_start >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY 1 ORDER BY 1
`);
const dauRows = Array.isArray(dauData) ? dauData : [];
const { data: featureData, isLoading: featureLoading } = useSQLQuery(`
SELECT feature_name, COUNT(*) AS usage_count
FROM "product_db"."main"."feature_events"
WHERE event_time >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY 1 ORDER BY 2 DESC LIMIT 8
`);
const featureRows = Array.isArray(featureData) ? featureData : [];
const { data: pageData, isLoading: pageLoading } = useSQLQuery(`
SELECT page_path, COUNT(*) AS views, COUNT(DISTINCT user_id) AS unique_visitors
FROM "product_db"."main"."page_views"
WHERE view_time >= CURRENT_DATE - INTERVAL 30 DAY
GROUP BY 1 ORDER BY 2 DESC LIMIT 10
`);
const pageRows = Array.isArray(pageData) ? pageData : [];
const KPI = ({ label, value, suffix = "" }: {
label: string; value: string; suffix?: string;
}) => (
<div>
<p className="text-sm mb-1" style={{ color: "#6a6a6a" }}>{label}</p>
{kpiLoading ? (
<div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
) : (
<p className="text-3xl font-bold tabular-nums sm:text-4xl" style={{ color: "#231f20" }}>{value}{suffix}</p>
)}
</div>
);
return (
<div className="min-h-screen px-4 py-6 sm:p-6 lg:p-8" style={{ backgroundColor: "#f8f8f8" }}>
<h1 className="text-2xl font-bold mb-8" style={{ color: "#231f20" }}>Product Analytics</h1>
{/* KPIs */}
<div className="mb-10 grid grid-cols-1 gap-4 sm:grid-cols-2 lg:grid-cols-4 lg:gap-6">
<KPI label="Active Users (30d)" value={N(kpiRows[0]?.active_users).toLocaleString()} />
<KPI label="Sessions" value={N(kpiRows[0]?.total_sessions).toLocaleString()} />
<KPI label="Avg Session Duration" value={N(kpiRows[0]?.avg_session_min).toFixed(1)} suffix=" min" />
<KPI label="Conversion Rate" value={N(kpiRows[0]?.conversion_rate).toFixed(1)} suffix="%" />
</div>
{kpiError && (
<p className="text-sm mb-4" style={{ color: "#bd4e35" }}>
Failed to load KPIs: {kpiMsg?.message || "Unknown error"}
</p>
)}
{/* Chart 1: Daily Active Users Trend */}
<div className="mb-10">
<h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Daily Active Users</h2>
{dauLoading ? (
<div className="flex items-center justify-center h-64">
<Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
</div>
) : (
<ResponsiveContainer width="100%" height={260}>
<AreaChart data={dauRows}>
<CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
<XAxis dataKey="day" tick={{ fontSize: 12 }} />
<YAxis tick={{ fontSize: 12 }} />
<Tooltip />
<Area
type="monotone"
dataKey="active_users"
stroke={COLORS[0]}
fill={COLORS[0]}
fillOpacity={0.15}
/>
</AreaChart>
</ResponsiveContainer>
)}
</div>
{/* Chart 2: Feature Usage Breakdown */}
<div className="mb-10">
<h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Feature Usage</h2>
{featureLoading ? (
<div className="flex items-center justify-center h-64">
<Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
</div>
) : (
<ResponsiveContainer width="100%" height={260}>
<BarChart data={featureRows}>
<CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
<XAxis dataKey="feature_name" tick={{ fontSize: 12 }} />
<YAxis tick={{ fontSize: 12 }} />
<Tooltip />
<Bar dataKey="usage_count" fill={COLORS[0]} radius={[4, 4, 0, 0]} />
</BarChart>
</ResponsiveContainer>
)}
</div>
{/* Table: Top Pages by Views */}
<div>
<h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Top Pages</h2>
{pageLoading ? (
<div className="space-y-3">
{[...Array(5)].map((_, i) => (
<div key={i} className="h-8 bg-gray-200 animate-pulse rounded" />
))}
</div>
) : (
<div className="overflow-x-auto">
<table className="w-full text-sm">
<thead>
<tr className="border-b border-gray-200">
<th className="text-left py-3 font-semibold" style={{ color: "#231f20" }}>Page</th>
<th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Views</th>
<th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Unique Visitors</th>
</tr>
</thead>
<tbody>
{pageRows.map((row, i) => (
<tr key={i} className="border-b border-gray-200"
style={{ backgroundColor: i % 2 === 0 ? "transparent" : "#f0f0f0" }}>
<td className="py-3" style={{ color: "#231f20" }}>{row.page_path}</td>
<td className="text-right py-3" style={{ color: "#231f20" }}>
{N(row.views).toLocaleString()}
</td>
<td className="text-right py-3" style={{ color: "#6a6a6a" }}>
{N(row.unique_visitors).toLocaleString()}
</td>
</tr>
))}
</tbody>
</table>
</div>
)}
</div>
</div>
);
}
```
## 3. Operational Metrics Dashboard
KPIs: Total Requests, Error Rate, P95 Latency, Uptime. Charts: Request Volume (Area), Error Rate by Endpoint (Bar). Table: Slowest Endpoints.
```tsx
import { useSQLQuery } from "@motherduck/react-sql-query";
import {
AreaChart, Area, BarChart, Bar,
XAxis, YAxis, CartesianGrid, Tooltip, ResponsiveContainer
} from "recharts";
import { Loader2 } from "lucide-react";
const N = (v: unknown): number => (v != null ? Number(v) : 0);
const COLORS = ["#0777b3", "#bd4e35", "#2d7a00", "#e18727", "#638CAD", "#adadad"];
export default function OperationalMetricsDashboard() {
const { data: kpiData, isLoading: kpiLoading, isError: kpiError, error: kpiMsg } = useSQLQuery(`
SELECT COUNT(*) AS total_requests,
ROUND(100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) / COUNT(*), 2) AS error_rate,
ROUND(quantile_cont(latency_ms, 0.95), 0) AS p95_latency_ms,
ROUND(100.0 * SUM(CASE WHEN status_code < 500 THEN 1 ELSE 0 END) / COUNT(*), 2) AS uptime_pct
FROM "ops_db"."main"."requests" WHERE request_time >= CURRENT_DATE - INTERVAL 24 HOUR
`);
const kpiRows = Array.isArray(kpiData) ? kpiData : [];
const { data: volumeData, isLoading: volumeLoading } = useSQLQuery(`
SELECT strftime(date_trunc('hour', request_time), '%Y-%m-%d %H:00') AS hour, COUNT(*) AS requests
FROM "ops_db"."main"."requests"
WHERE request_time >= CURRENT_DATE - INTERVAL 24 HOUR
GROUP BY 1 ORDER BY 1
`);
const volumeRows = Array.isArray(volumeData) ? volumeData : [];
const { data: errorData, isLoading: errorLoading } = useSQLQuery(`
SELECT endpoint,
ROUND(100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) / COUNT(*), 2) AS error_rate
FROM "ops_db"."main"."requests"
WHERE request_time >= CURRENT_DATE - INTERVAL 24 HOUR
GROUP BY 1 HAVING COUNT(*) >= 10 ORDER BY 2 DESC LIMIT 8
`);
const errorRows = Array.isArray(errorData) ? errorData : [];
const { data: slowData, isLoading: slowLoading } = useSQLQuery(`
SELECT endpoint, COUNT(*) AS requests, ROUND(AVG(latency_ms), 0) AS avg_latency_ms,
ROUND(quantile_cont(latency_ms, 0.95), 0) AS p95_latency_ms,
ROUND(quantile_cont(latency_ms, 0.99), 0) AS p99_latency_ms
FROM "ops_db"."main"."requests"
WHERE request_time >= CURRENT_DATE - INTERVAL 24 HOUR
GROUP BY 1 HAVING COUNT(*) >= 10 ORDER BY 4 DESC LIMIT 10
`);
const slowRows = Array.isArray(slowData) ? slowData : [];
const KPI = ({ label, value, suffix = "" }: {
label: string; value: string; suffix?: string;
}) => (
<div>
<p className="text-sm mb-1" style={{ color: "#6a6a6a" }}>{label}</p>
{kpiLoading ? (
<div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
) : (
<p className="text-3xl font-bold tabular-nums sm:text-4xl" style={{ color: "#231f20" }}>{value}{suffix}</p>
)}
</div>
);
const errorRateColor = (rate: number): string => {
if (rate >= 5) return "#bd4e35";
if (rate >= 1) return "#e18727";
return "#2d7a00";
};
return (
<div className="min-h-screen px-4 py-6 sm:p-6 lg:p-8" style={{ backgroundColor: "#f8f8f8" }}>
<h1 className="text-2xl font-bold mb-8" style={{ color: "#231f20" }}>Operational Metrics</h1>
{/* KPIs */}
<div className="mb-10 grid grid-cols-1 gap-4 sm:grid-cols-2 lg:grid-cols-4 lg:gap-6">
<KPI label="Total Requests (24h)" value={N(kpiRows[0]?.total_requests).toLocaleString()} />
<div>
<p className="text-sm mb-1" style={{ color: "#6a6a6a" }}>Error Rate</p>
{kpiLoading ? (
<div className="h-12 w-24 bg-gray-200 animate-pulse rounded" />
) : (
<p className="text-3xl font-bold tabular-nums sm:text-4xl"
style={{ color: errorRateColor(N(kpiRows[0]?.error_rate)) }}>
{N(kpiRows[0]?.error_rate).toFixed(2)}%
</p>
)}
</div>
<KPI label="P95 Latency" value={N(kpiRows[0]?.p95_latency_ms).toLocaleString()} suffix=" ms" />
<KPI label="Uptime" value={N(kpiRows[0]?.uptime_pct).toFixed(2)} suffix="%" />
</div>
{kpiError && (
<p className="text-sm mb-4" style={{ color: "#bd4e35" }}>
Failed to load KPIs: {kpiMsg?.message || "Unknown error"}
</p>
)}
{/* Chart 1: Request Volume Over Time */}
<div className="mb-10">
<h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Request Volume (Hourly)</h2>
{volumeLoading ? (
<div className="flex items-center justify-center h-64">
<Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
</div>
) : (
<ResponsiveContainer width="100%" height={260}>
<AreaChart data={volumeRows}>
<CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
<XAxis dataKey="hour" tick={{ fontSize: 12 }} />
<YAxis tick={{ fontSize: 12 }} />
<Tooltip />
<Area
type="monotone"
dataKey="requests"
stroke={COLORS[0]}
fill={COLORS[0]}
fillOpacity={0.15}
/>
</AreaChart>
</ResponsiveContainer>
)}
</div>
{/* Chart 2: Error Rate by Endpoint */}
<div className="mb-10">
<h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Error Rate by Endpoint</h2>
{errorLoading ? (
<div className="flex items-center justify-center h-64">
<Loader2 className="animate-spin" size={32} style={{ color: "#0777b3" }} />
</div>
) : (
<ResponsiveContainer width="100%" height={260}>
<BarChart data={errorRows}>
<CartesianGrid strokeDasharray="3 3" stroke="#e0e0e0" />
<XAxis dataKey="endpoint" tick={{ fontSize: 12 }} />
<YAxis tick={{ fontSize: 12 }} tickFormatter={(v) => `${v}%`} />
<Tooltip formatter={(value: number) => `${value}%`} />
<Bar dataKey="error_rate" fill={COLORS[1]} radius={[4, 4, 0, 0]} />
</BarChart>
</ResponsiveContainer>
)}
</div>
{/* Table: Slowest Endpoints */}
<div>
<h2 className="text-lg font-semibold mb-4" style={{ color: "#231f20" }}>Slowest Endpoints</h2>
{slowLoading ? (
<div className="space-y-3">
{[...Array(5)].map((_, i) => (
<div key={i} className="h-8 bg-gray-200 animate-pulse rounded" />
))}
</div>
) : (
<div className="overflow-x-auto">
<table className="w-full text-sm">
<thead>
<tr className="border-b border-gray-200">
<th className="text-left py-3 font-semibold" style={{ color: "#231f20" }}>Endpoint</th>
<th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Requests</th>
<th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>Avg Latency</th>
<th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>P95 Latency</th>
<th className="text-right py-3 font-semibold" style={{ color: "#231f20" }}>P99 Latency</th>
</tr>
</thead>
<tbody>
{slowRows.map((row, i) => (
<tr key={i} className="border-b border-gray-200"
style={{ backgroundColor: i % 2 === 0 ? "transparent" : "#f0f0f0" }}>
<td className="py-3" style={{ color: "#231f20" }}>{row.endpoint}</td>
<td className="text-right py-3" style={{ color: "#6a6a6a" }}>
{N(row.requests).toLocaleString()}
</td>
<td className="text-right py-3" style={{ color: "#231f20" }}>
{N(row.avg_latency_ms).toLocaleString()} ms
</td>
<td className="text-right py-3" style={{ color: "#231f20" }}>
{N(row.p95_latency_ms).toLocaleString()} ms
</td>
<td className="text-right py-3"
style={{ color: N(row.p99_latency_ms) > 1000 ? "#bd4e35" : "#231f20" }}>
{N(row.p99_latency_ms).toLocaleString()} ms
</td>
</tr>
))}
</tbody>
</table>
</div>
)}
</div>
</div>
);
}
```
---
## Adapting Templates
- **Replace table names** with your actual fully qualified names (`"db"."schema"."table"`).
- **Replace column names** to match your schema. Use `motherduck-explore` to discover columns.
- **Adjust aggregations** to match your data (e.g., `SUM(amount)` vs. `SUM(quantity * unit_price)`).
- **Adjust date granularity**: change `date_trunc('month', ...)` to `'day'`, `'week'`, `'hour'`, or `'quarter'`.
- **Adjust time windows**: change `INTERVAL 30 DAY` to match your reporting period.
All templates share: one `useSQLQuery` per section, `N()` and `COLORS` at file top, `Array.isArray` guards, per-section loading states, `export default function`, `#f8f8f8` background, no card borders.
| Scenario | Template |
|---|---|
| E-commerce, revenue, order analytics | Sales Dashboard |
| SaaS product, user engagement, features | Product Analytics Dashboard |
| API monitoring, infrastructure, SRE | Operational Metrics Dashboard |
references/EXECUTION_REFERENCE.md
# Execution Reference
Read this for example execution or an explicit structured-output request. These fixtures illustrate the pattern; they are not the user’s dataset or a prerequisite for ordinary work.
## Structured Output
If the caller explicitly asks for structured JSON, return raw JSON only with no Markdown fences or prose before/after it.
This is mainly for automated tests, regression checks, or downstream tooling that needs a stable machine-readable shape. Normal human-facing use of the skill can stay in prose unless JSON is explicitly requested.
Use this exact top-level shape when JSON is requested:
```json
{
"summary": {},
"assumptions": [],
"implementation_plan": [],
"validation_plan": [],
"risks": []
}
```
## Runnable Artifact
- `artifacts/dashboard_story_example.py` -- MotherDuck-backed Python example that produces KPI, trend, breakdown, and detail outputs for one dashboard story
- `artifacts/dashboard_story_example.ts` -- TypeScript companion artifact with the same dashboard output contract
From the repository root, run it with (for an installed skill, substitute its absolute artifact path):
```bash
uv run --with duckdb python skills/motherduck-build-dashboard/artifacts/dashboard_story_example.py
```
Run the same artifact against a temporary MotherDuck database:
```bash
MOTHERDUCK_ARTIFACT_USE_MOTHERDUCK=1 \
uv run --with duckdb python skills/motherduck-build-dashboard/artifacts/dashboard_story_example.py
```
From a checkout of this repository, validate the TypeScript companion artifacts:
```bash
uv run scripts/test_typescript_artifacts.py
```
SKILL.md
---
name: motherduck-build-dashboard
description: Build a MotherDuck dashboard as a Dive, choosing the analytical story, metrics, and section queries.
license: MIT
---
# Build an Analytics Dashboard
## Start Here: Is a MotherDuck Server Active?
Use an active remote MotherDuck MCP server or local MotherDuck server to inspect the in-scope database, schema, grain, keys, and relevant metrics. Reuse known context and narrow discovery to the requested work; do not scan the whole workspace by default. Let the actual data model shape the result.
Resolve the target from the request or active context. Ask only if ambiguity materially affects the result. Without a server, use supplied schema and explicit assumptions for planning; do not imply live validation.
## Dashboard Defaults
- One story per dashboard.
- Start with a responsive KPI group, a primary chart, and supporting detail where it helps the decision. Add sections only when the question or data warrants them; these are defaults, not fixed chart quotas.
- Heavy shaping in SQL, not React.
- When the dashboard should trigger an action (approve, refresh, submit), add a write-back button that runs a Flight; see `motherduck-create-dive`.
- Viewers can ask follow-up questions through the Dive's AI Chat, so name sections and metrics in business terms.
## Workflow
1. Inspect the available MotherDuck server or supplied schema context.
2. Read relevant root/domain Guides, then explore the real schema and validate the governed metrics.
3. Pick the dashboard story.
4. Write one query per section.
5. For a new dashboard or layout change, use the design defaults and responsive references in `motherduck-create-dive`; preserve the existing design for a scoped SQL or text edit.
6. Compose the dashboard in a Dive. When MotherDuck MCP is available, call `get_dive_guide` before writing Dive code and before every `save_dive`, `update_dive`, or `edit_dive_content`.
7. When the request includes creating or updating the Dive, save only after responsive, theme, query-state, and data validation. Before `save_dive` or `update_dive`, follow the review step that `get_dive_guide` sets for your client: when it asks you to show the final source or preview and get the user's go-ahead, do that once, unless the user already said to save without review.
8. Read the saved Dive back. Leave work-in-progress as Draft; promote it to Ready only after the requested delivery is validated. Reuse Endorsed Dives before rebuilding an existing trusted answer.
Match execution to the request: answer, review, or planning work returns the requested dashboard artifacts; build or change work creates or updates the requested in-scope Dive and validates it. Ask before destructive replacement, unrelated external writes, or a material expansion of scope.
When this skill produces a native DuckDB (`md:`) connection, watermark it with `custom_user_agent=agent-skills/3.0.0(harness-<harness>;llm-<llm>)`. If metadata is missing, fall back to `harness-unknown` and `llm-unknown`.
## Output
For a full engagement, cover the following as relevant to the request:
- the dashboard story
- the section list
- the validated SQL for each section
- the Dive implementation plan
- the save/update path
For explicit structured JSON requests, read [the output contract](references/EXECUTION_REFERENCE.md#structured-output). Otherwise use the format that fits the requested deliverable.
## References
Read only the sections relevant to the task; these are guidance, not a mandatory itinerary.
- `references/DASHBOARD_IMPLEMENTATION_GUIDE.md` -- section-to-SQL mapping, TSX composition, and validation examples
- `references/DASHBOARD_PATTERNS.md` -- example dashboard compositions and reusable sections
## Examples
Read [the execution reference](references/EXECUTION_REFERENCE.md) only to run the bundled examples or reproduce their validation.
- [dashboard_story_example.py](artifacts/dashboard_story_example.py)
- [dashboard_story_example.ts](artifacts/dashboard_story_example.ts)
## Related Skills
Load related skills only for missing capabilities; reuse established context.
- `motherduck-explore` -- inspect the actual database before deciding the dashboard sections
- `motherduck-query` -- validate each dashboard query
- `motherduck-create-dive` -- useSQLQuery, design defaults, responsive layout, theming, preview/save, and visual QA
- `motherduck-duckdb-sql` -- resolve syntax and function questions