How to Build KPI dashboard with V0

1// Paste this prompt into V0's AI chat:
2// Build a KPI dashboard system. Create a Supabase schema with these tables:
3// 1. kpis: id (uuid PK), name (text), slug (text UNIQUE), unit (text like '$', '%', '#'), direction (text CHECK IN 'up','down'), current_value (numeric), target_value (numeric), updated_at (timestamptz)
4// 2. kpi_history: id (uuid PK), kpi_id (uuid FK to kpis), value (numeric), period_start (date), period_end (date)
5// 3. goals: id (uuid PK), kpi_id (uuid FK to kpis), target (numeric), deadline (date), status (text DEFAULT 'active')
6// 4. data_sources: id (uuid PK), name (text), query_template (text), connection_config (jsonb)
7// Add RLS policies for authenticated users. Generate SQL and TypeScript types.

1// Paste this prompt into V0's AI chat:
2// Create a KPI dashboard at app/kpis/page.tsx.
3// Requirements:
4// - Fetch all KPIs from Supabase with their latest 7 history values
5// - Display as a responsive grid of shadcn/ui Cards (3 columns on desktop, 1 on mobile)
6// - Each Card shows: KPI name, current_value in large text with unit, trend arrow (up green or down red based on direction), percentage change from previous period
7// - Add a tiny Recharts sparkline (AreaChart, 80px tall) inside each Card showing the last 7 data points
8// - Below the value, show a Progress bar toward the target_value
9// - Add Tabs at the top for time range: 7d, 30d, 90d
10// - Add a Tooltip on hover showing the exact value and date for each sparkline point
11// - Use Server Components for the page, wrap sparklines in a 'use client' component

1'use client'
2
3import { AreaChart, Area, XAxis, YAxis, CartesianGrid, Tooltip, ReferenceLine, ResponsiveContainer } from 'recharts'
4import { Card, CardContent, CardHeader, CardTitle } from '@/components/ui/card'
5import { Tabs, TabsContent, TabsList, TabsTrigger } from '@/components/ui/tabs'
6
7interface KpiChartProps {
8  data: { date: string; value: number }[]
9  target: number
10  unit: string
11  direction: 'up' | 'down'
12}
13
14export function KpiChart({ data, target, unit, direction }: KpiChartProps) {
15  const color = direction === 'up' ? '#22c55e' : '#ef4444'
16
17  return (
18    <Card>
19      <CardHeader>
20        <CardTitle>Historical Trend</CardTitle>
21      </CardHeader>
22      <CardContent>
23        <Tabs defaultValue="30d">
24          <TabsList>
25            <TabsTrigger value="7d">7 Days</TabsTrigger>
26            <TabsTrigger value="30d">30 Days</TabsTrigger>
27            <TabsTrigger value="90d">90 Days</TabsTrigger>
28          </TabsList>
29          <TabsContent value="30d">
30            <ResponsiveContainer width="100%" height={400}>
31              <AreaChart data={data}>
32                <CartesianGrid strokeDasharray="3 3" />
33                <XAxis dataKey="date" />
34                <YAxis tickFormatter={(v) => `${unit}${v}`} />
35                <Tooltip formatter={(v: number) => [`${unit}${v}`, 'Value']} />
36                <ReferenceLine y={target} stroke="#6366f1" strokeDasharray="5 5" label="Goal" />
37                <Area type="monotone" dataKey="value" stroke={color} fill={color} fillOpacity={0.15} />
38              </AreaChart>
39            </ResponsiveContainer>
40          </TabsContent>
41        </Tabs>
42      </CardContent>
43    </Card>
44  )
45}

1-- Run this in Supabase SQL Editor
2CREATE OR REPLACE FUNCTION get_kpi_trends(p_days int DEFAULT 30)
3RETURNS TABLE (
4  kpi_id uuid,
5  kpi_name text,
6  kpi_slug text,
7  unit text,
8  direction text,
9  current_value numeric,
10  previous_value numeric,
11  target_value numeric,
12  delta numeric,
13  delta_pct numeric,
14  trend text
15) AS $$
16BEGIN
17  RETURN QUERY
18  SELECT
19    k.id AS kpi_id,
20    k.name AS kpi_name,
21    k.slug AS kpi_slug,
22    k.unit,
23    k.direction,
24    k.current_value,
25    COALESCE(
26      (SELECT h.value FROM kpi_history h
27       WHERE h.kpi_id = k.id
28       ORDER BY h.period_end DESC OFFSET 1 LIMIT 1),
29      k.current_value
30    ) AS previous_value,
31    k.target_value,
32    k.current_value - COALESCE(
33      (SELECT h.value FROM kpi_history h
34       WHERE h.kpi_id = k.id
35       ORDER BY h.period_end DESC OFFSET 1 LIMIT 1),
36      k.current_value
37    ) AS delta,
38    CASE
39      WHEN COALESCE(
40        (SELECT h.value FROM kpi_history h
41         WHERE h.kpi_id = k.id
42         ORDER BY h.period_end DESC OFFSET 1 LIMIT 1),
43        0
44      ) = 0 THEN 0
45      ELSE ROUND(
46        ((k.current_value - (SELECT h.value FROM kpi_history h
47         WHERE h.kpi_id = k.id
48         ORDER BY h.period_end DESC OFFSET 1 LIMIT 1)) /
49        (SELECT h.value FROM kpi_history h
50         WHERE h.kpi_id = k.id
51         ORDER BY h.period_end DESC OFFSET 1 LIMIT 1)) * 100, 1
52      )
53    END AS delta_pct,
54    CASE
55      WHEN k.current_value > COALESCE(
56        (SELECT h.value FROM kpi_history h
57         WHERE h.kpi_id = k.id
58         ORDER BY h.period_end DESC OFFSET 1 LIMIT 1),
59        k.current_value
60      ) THEN 'up'
61      ELSE 'down'
62    END AS trend
63  FROM kpis k;
64END;
65$$ LANGUAGE plpgsql SECURITY DEFINER;

1// Paste this prompt into V0's AI chat:
2// Create a KPI settings page at app/kpis/settings/page.tsx.
3// Requirements:
4// - List all KPI definitions in a shadcn/ui Table with columns: Name, Slug, Unit, Direction, Current Value, Target, Actions
5// - Add a 'New KPI' Button that opens a Dialog with form fields:
6//   - name (Input), slug (Input, auto-generated from name), unit (Select: $, %, #, custom)
7//   - direction (RadioGroup: 'Higher is better' / 'Lower is better'), target_value (Input number)
8// - Edit button on each row opens the same Dialog pre-filled
9// - Delete button with AlertDialog confirmation
10// - Goals section below: Table of active goals with KPI name, target, deadline, Progress bar
11// - Add Goal button: Select KPI, target number Input, deadline Calendar date picker
12// - Use Server Actions for all CRUD operations (insert, update, delete on kpis and goals tables)

1import { NextRequest, NextResponse } from 'next/server'
2import { createClient } from '@supabase/supabase-js'
3
4const supabase = createClient(
5  process.env.SUPABASE_URL!,
6  process.env.SUPABASE_SERVICE_ROLE_KEY!
7)
8
9export async function GET(req: NextRequest) {
10  const authHeader = req.headers.get('Authorization')
11  if (authHeader !== `Bearer ${process.env.CRON_SECRET}`) {
12    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 })
13  }
14
15  const { data: sources } = await supabase
16    .from('data_sources')
17    .select('*, kpis(id, slug)')
18
19  const updates: { kpi_id: string; value: number }[] = []
20
21  for (const source of sources || []) {
22    try {
23      // Execute the query template to fetch latest value
24      const { data } = await supabase.rpc('execute_kpi_query', {
25        p_query: source.query_template,
26      })
27      if (data?.value !== undefined) {
28        updates.push({ kpi_id: source.kpis.id, value: data.value })
29      }
30    } catch (err) {
31      console.error(`Failed to refresh ${source.name}:`, err)
32    }
33  }
34
35  for (const update of updates) {
36    await supabase
37      .from('kpis')
38      .update({ current_value: update.value, updated_at: new Date().toISOString() })
39      .eq('id', update.kpi_id)
40
41    await supabase.from('kpi_history').insert({
42      kpi_id: update.kpi_id,
43      value: update.value,
44      period_start: new Date().toISOString().split('T')[0],
45      period_end: new Date().toISOString().split('T')[0],
46    })
47  }
48
49  return NextResponse.json({ refreshed: updates.length })
50}