How to Build a Kpi Dashboard with Lovable

1Create Supabase Postgres functions for KPI aggregation. Assume these source tables exist:
2- orders: id, user_id, total, status ('paid'|'pending'|'refunded'), created_at
3- users: id, email, plan ('free'|'pro'|'enterprise'), created_at
4- events: id, user_id, name, created_at
5
6Create these SQL functions:
71. get_mrr(start_date date, end_date date) RETURNS numeric — sum of orders.total where status='paid' and created_at between start_date and end_date
82. get_new_users(start_date date, end_date date) RETURNS integer — count of users created between the dates
93. get_active_users(start_date date, end_date date) RETURNS integer — count distinct user_id from events between the dates
104. get_conversion_rate(start_date date, end_date date) RETURNS numeric — (count of users with plan != 'free') / (count of all users) * 100, rounded to 1 decimal
11
12Grant EXECUTE on all functions to the authenticated role.

1import { useQuery } from '@tanstack/react-query'
2import { supabase } from '@/integrations/supabase/client'
3import { subDays, format } from 'date-fns'
4
5type PeriodRange = { from: Date; to: Date }
6
7function previousPeriod(range: PeriodRange): PeriodRange {
8  const days = Math.round((range.to.getTime() - range.from.getTime()) / 86_400_000)
9  return {
10    from: subDays(range.from, days),
11    to: subDays(range.to, days),
12  }
13}
14
15export function useKpiMetric(
16  fnName: string,
17  range: PeriodRange
18) {
19  const prev = previousPeriod(range)
20
21  const current = useQuery({
22    queryKey: [fnName, 'current', range],
23    queryFn: async () => {
24      const { data, error } = await supabase.rpc(fnName, {
25        start_date: format(range.from, 'yyyy-MM-dd'),
26        end_date: format(range.to, 'yyyy-MM-dd'),
27      })
28      if (error) throw error
29      return data as number
30    },
31    staleTime: 5 * 60_000,
32  })
33
34  const previous = useQuery({
35    queryKey: [fnName, 'previous', prev],
36    queryFn: async () => {
37      const { data, error } = await supabase.rpc(fnName, {
38        start_date: format(prev.from, 'yyyy-MM-dd'),
39        end_date: format(prev.to, 'yyyy-MM-dd'),
40      })
41      if (error) throw error
42      return data as number
43    },
44    staleTime: 5 * 60_000,
45  })
46
47  const delta = current.data != null && previous.data
48    ? Math.round(((current.data - previous.data) / previous.data) * 100)
49    : null
50
51  return { current: current.data, previous: previous.data, delta, isLoading: current.isLoading }
52}

1Build a MetricWidget component at src/components/dashboard/MetricWidget.tsx.
2
3Requirements:
4- Props: title (string), fnName (string), range (PeriodRange), format ('currency'|'number'|'percent')
5- Use the useKpiMetric hook to fetch current, previous, and delta
6- Render a shadcn/ui Card with:
7  - CardHeader: title, a Skeleton placeholder while loading, and a MoreHorizontal Button for widget options
8  - Large formatted value (use Intl.NumberFormat for currency and percent)
9  - Delta Badge: positive delta = green with TrendingUp icon, negative = red with TrendingDown icon, zero = gray
10  - A small comparison BarChart (height=60) with two bars: current (indigo) and previous (gray). Suppress axes and tooltips for the mini chart.
11- Show a Skeleton for the entire card while isLoading is true
12- The MoreHorizontal menu should have options: Refresh, Change Period, Remove Widget

1Add drag-and-drop layout persistence to the dashboard.
2
3Requirements:
4- Create a dashboard_configs table in Supabase: id, user_id, widget_order (jsonb array of widget ids), created_at. RLS: users can read/write their own row only.
5- Install @dnd-kit/core and @dnd-kit/sortable
6- Wrap the widget grid in a DndContext + SortableContext from dnd-kit
7- Each MetricWidget is wrapped in a useSortable hook to get drag handle props
8- On drag end, update the local widget order state immediately (optimistic), then call supabase.from('dashboard_configs').upsert({ user_id: userId, widget_order: newOrder })
9- On mount, fetch the saved widget_order and apply it to the grid
10- Show a drag handle icon (GripVertical from lucide-react) on each widget card that users click to drag
11- Add a skeleton grid layout on first load while the config is fetching

1Add two finishing features to the dashboard:
2
31. Realtime freshness indicator:
4- Subscribe to the dashboard_events Realtime channel (reuse from other components if it exists)
5- When a new event fires, record the current timestamp in a Map keyed by widget type
6- Show a 'Live' green dot + 'Updated X seconds ago' text below each metric value
7- The timestamp updates every 10 seconds using a setInterval even without a new event
8
92. PDF export:
10- Add an Export PDF Button in the dashboard top bar (use Printer icon from lucide-react)
11- On click, call window.print()
12- Add a global CSS print stylesheet (@media print) that:
13  - Hides the top navigation, filter controls, and drag handles
14  - Sets each widget card to break-inside: avoid
15  - Sets the page to landscape orientation
16  - Adds a print timestamp footer using CSS content: 'Exported ' attr(data-date)
17- Set data-date on the dashboard container using a useEffect that formats the current date