How to Build a Reporting Tool with Lovable

1Create a reporting tool database schema in Supabase.
2
3Create this table:
4
5reports:
6  id uuid primary key default gen_random_uuid()
7  name text not null
8  description text
9  category text (e.g. 'Sales', 'Operations', 'Finance')
10  query_config jsonb not null — stores: { sql: string, params: [{name, label, type: 'date'|'text'|'select', options?: string[]}], columns: [{key, label, type: 'text'|'number'|'date'|'badge'}] }
11  is_active boolean default true
12  created_by uuid references auth.users
13  last_run_at timestamptz
14  run_count integer default 0
15  created_at timestamptz default now()
16
17RLS:
18  Enable RLS on reports.
19  SELECT: authenticated users can read all active reports.
20  INSERT/UPDATE/DELETE: only service role (admins manage reports via Supabase dashboard).
21
22Insert two sample report rows:
231. name='Monthly Sales Summary', category='Sales', query_config with a sql template that selects from orders grouped by month, with a date range param (start_date, end_date)
242. name='User Signups by Day', category='Users', query_config with sql selecting from auth.users grouped by created_at date, with a date range param

1Create a Supabase Edge Function at supabase/functions/run-report/index.ts.
2
3The function accepts POST requests with body: { report_id: string, params: Record<string, string>, page: number, page_size: number }.
4
5Logic:
61. Authenticate the caller using the Authorization header (check for a valid Supabase JWT using the anon key — call createClient with the anon key and pass the user's JWT)
72. Fetch the report row from the reports table by report_id. If not found, return 404.
83. Extract query_config.sql from the report. The SQL contains named placeholders like :start_date, :end_date.
94. Replace each :param_name placeholder with a positional $1, $2, etc. and build a values array in matching order.
105. Append LIMIT $N OFFSET $M to the SQL for pagination.
116. Run the query using supabase.rpc or a raw postgres connection via the service role client.
127. Also run a COUNT(*) variant of the same query (wrap in SELECT COUNT(*) FROM (...) as t) for total row count.
138. Update reports.last_run_at = now() and increment run_count.
149. Return JSON: { rows: any[], total: number, columns: query_config.columns, page, page_size }.
15
16Return CORS headers on all responses including OPTIONS preflight.

1Build the report viewer page at src/pages/Reports.tsx.
2
3Layout:
4- Left Sidebar (shadcn/ui Sidebar or a simple resizable panel): lists all reports from the reports table grouped by category. Each item shows report name and last_run_at relative time. Clicking sets the active report ID in URL search params (?report=uuid).
5- Right main area: shows filter bar, DataTable, and export buttons.
6
7Filter bar:
8- Reads the active report's query_config.params array and renders the appropriate control per param type:
9  - type='date': shadcn/ui DatePickerWithRange (two date inputs)
10  - type='text': Input
11  - type='select': Select with query_config.params[n].options as SelectItems
12- A 'Run Report' Button triggers the Edge Function call
13- A 'Columns' Popover Button with a Checkbox list for toggling column visibility (TanStack Table columnVisibility state)
14
15DataTable:
16- Columns are built dynamically from the response's columns array
17- Server-side pagination: Previous/Next buttons update page state and re-call the Edge Function
18- Show total row count: '1,234 rows'
19- Loading state: show Skeleton rows during fetch
20
21Export bar (below table):
22- 'Export CSV' Button: serializes current rows to CSV and triggers download
23- 'Export PDF' Button: calls window.print() — add a print CSS class that hides everything except the table

1// src/lib/export-utils.ts
2import { format } from 'date-fns'
3
4export type ReportColumn = {
5  key: string
6  label: string
7}
8
9export function exportToCSV(rows: Record<string, unknown>[], columns: ReportColumn[], reportName: string): void {
10  const header = columns.map((c) => `"${c.label}"`).join(',')
11  const body = rows
12    .map((row) =>
13      columns
14        .map((c) => {
15          const val = row[c.key]
16          if (val == null) return '""'
17          const str = String(val).replace(/"/g, '""')
18          return `"${str}"`
19        })
20        .join(',')
21    )
22    .join('\n')
23
24  const csv = `${header}\n${body}`
25  const blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' })
26  const url = URL.createObjectURL(blob)
27  const link = document.createElement('a')
28  link.href = url
29  link.download = `${reportName.replace(/\s+/g, '-').toLowerCase()}-${format(new Date(), 'yyyy-MM-dd')}.csv`
30  link.click()
31  URL.revokeObjectURL(url)
32}
33
34export function exportToPDF(reportName: string): void {
35  const originalTitle = document.title
36  document.title = reportName
37  window.print()
38  document.title = originalTitle
39}

1Add scheduled report delivery to the reporting tool.
2
3New table: report_schedules
4  id uuid primary key default gen_random_uuid()
5  report_id uuid references reports(id)
6  user_id uuid references auth.users(id)
7  frequency text — 'daily' | 'weekly' | 'monthly'
8  params jsonb — the filter params to use for the scheduled run
9  email_to text
10  is_active boolean default true
11  next_run_at timestamptz
12  last_run_at timestamptz
13
14RLS: users can manage their own schedules (user_id = auth.uid()).
15
16Create an Edge Function at supabase/functions/deliver-scheduled-reports/index.ts:
171. Accept a POST request (will be called by pg_cron)
182. Query report_schedules WHERE is_active = true AND next_run_at <= now()
193. For each schedule, call the existing run-report Edge Function internally
204. Format the rows as an HTML table
215. Send via Resend API (use RESEND_API_KEY from Deno.env.get)
226. Update last_run_at and calculate next_run_at based on frequency
23
24In the UI, add a 'Schedule' Button in the report viewer that opens a Dialog to create/edit a schedule for the current report.