# How to Build a Reporting Tool with Lovable

- Tool: How to Build with Lovable
- Difficulty: Intermediate
- Compatibility: Lovable Pro or higher
- Last updated: April 2026

## TL;DR

Build a dynamic reporting tool in Lovable where report definitions live in a query_config JSONB column. A parameterized Supabase Edge Function runs each report's SQL safely, returns paginated rows to a DataTable with column toggles, and lets users export results as CSV or PDF — all without touching code for each new report.

## Before you start

- Lovable Pro account for Edge Function generation
- Supabase project with SUPABASE_SERVICE_ROLE_KEY saved in Cloud tab → Secrets
- At least one data table in your Supabase project to report on
- Basic understanding of SQL SELECT statements and WHERE clauses
- Node 18+ for running any local type generation (optional)

## Step-by-step guide

### 1. Create the reports registry schema

Prompt Lovable to create the reports table that acts as the registry for all report definitions. Each row defines a complete report: its SQL template, the parameter slots, and display metadata.

```
Create a reporting tool database schema in Supabase.

Create this table:

reports:
  id uuid primary key default gen_random_uuid()
  name text not null
  description text
  category text (e.g. 'Sales', 'Operations', 'Finance')
  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'}] }
  is_active boolean default true
  created_by uuid references auth.users
  last_run_at timestamptz
  run_count integer default 0
  created_at timestamptz default now()

RLS:
  Enable RLS on reports.
  SELECT: authenticated users can read all active reports.
  INSERT/UPDATE/DELETE: only service role (admins manage reports via Supabase dashboard).

Insert two sample report rows:
1. 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)
2. 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
```

> Pro tip: Ask Lovable to also create a report_runs table (id, report_id, user_id, params_used jsonb, row_count int, duration_ms int, ran_at timestamptz) so you can show users their run history and cache repeated identical queries.

**Expected result:** The reports table is created with RLS. Two sample report rows exist. TypeScript types are generated. The app shell renders in the preview.

### 2. Build the parameterized report-runner Edge Function

Create the Edge Function that takes a report ID and parameter values, fetches the query_config, builds a safe parameterized query, and returns rows plus column definitions.

```
Create a Supabase Edge Function at supabase/functions/run-report/index.ts.

The function accepts POST requests with body: { report_id: string, params: Record<string, string>, page: number, page_size: number }.

Logic:
1. 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)
2. Fetch the report row from the reports table by report_id. If not found, return 404.
3. Extract query_config.sql from the report. The SQL contains named placeholders like :start_date, :end_date.
4. Replace each :param_name placeholder with a positional $1, $2, etc. and build a values array in matching order.
5. Append LIMIT $N OFFSET $M to the SQL for pagination.
6. Run the query using supabase.rpc or a raw postgres connection via the service role client.
7. Also run a COUNT(*) variant of the same query (wrap in SELECT COUNT(*) FROM (...) as t) for total row count.
8. Update reports.last_run_at = now() and increment run_count.
9. Return JSON: { rows: any[], total: number, columns: query_config.columns, page, page_size }.

Return CORS headers on all responses including OPTIONS preflight.
```

> Pro tip: For the actual SQL execution, use Supabase's pg library via the service role. Never use string concatenation to inject user values into SQL — always use positional parameters ($1, $2) to prevent SQL injection.

**Expected result:** The Edge Function deploys successfully. Posting a report_id with valid params returns rows and column definitions as JSON.

### 3. Build the report viewer with DataTable and column toggles

Ask Lovable to build the main report viewer page. A Sidebar on the left lists all reports. Selecting a report loads its filters and renders the results in a DataTable with column visibility toggles.

```
Build the report viewer page at src/pages/Reports.tsx.

Layout:
- 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).
- Right main area: shows filter bar, DataTable, and export buttons.

Filter bar:
- Reads the active report's query_config.params array and renders the appropriate control per param type:
  - type='date': shadcn/ui DatePickerWithRange (two date inputs)
  - type='text': Input
  - type='select': Select with query_config.params[n].options as SelectItems
- A 'Run Report' Button triggers the Edge Function call
- A 'Columns' Popover Button with a Checkbox list for toggling column visibility (TanStack Table columnVisibility state)

DataTable:
- Columns are built dynamically from the response's columns array
- Server-side pagination: Previous/Next buttons update page state and re-call the Edge Function
- Show total row count: '1,234 rows'
- Loading state: show Skeleton rows during fetch

Export bar (below table):
- 'Export CSV' Button: serializes current rows to CSV and triggers download
- 'Export PDF' Button: calls window.print() — add a print CSS class that hides everything except the table
```

> Pro tip: Store the column visibility state in localStorage keyed by report ID so users' column preferences persist across page refreshes.

**Expected result:** The report viewer renders with a sidebar of reports. Selecting a report and clicking Run Report populates the DataTable. Column toggles show and hide columns. Pagination works.

### 4. Add CSV and PDF export

Implement the export utilities as standalone TypeScript helper functions that the DataTable calls directly. Keep them decoupled from the UI components so they can be reused.

```
// src/lib/export-utils.ts
import { format } from 'date-fns'

export type ReportColumn = {
  key: string
  label: string
}

export function exportToCSV(rows: Record<string, unknown>[], columns: ReportColumn[], reportName: string): void {
  const header = columns.map((c) => `"${c.label}"`).join(',')
  const body = rows
    .map((row) =>
      columns
        .map((c) => {
          const val = row[c.key]
          if (val == null) return '""'
          const str = String(val).replace(/"/g, '""')
          return `"${str}"`
        })
        .join(',')
    )
    .join('\n')

  const csv = `${header}\n${body}`
  const blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' })
  const url = URL.createObjectURL(blob)
  const link = document.createElement('a')
  link.href = url
  link.download = `${reportName.replace(/\s+/g, '-').toLowerCase()}-${format(new Date(), 'yyyy-MM-dd')}.csv`
  link.click()
  URL.revokeObjectURL(url)
}

export function exportToPDF(reportName: string): void {
  const originalTitle = document.title
  document.title = reportName
  window.print()
  document.title = originalTitle
}
```

> Pro tip: For PDF, add a <style> tag in your report page that sets @media print { body > *:not(#report-table-container) { display: none !important; } }. This ensures only the table prints.

**Expected result:** Clicking Export CSV downloads a properly formatted CSV file. Clicking Export PDF opens the browser print dialog showing only the report table.

### 5. Add report scheduling and email delivery (optional enhancement)

Extend the tool by letting users schedule reports to run automatically and receive results by email. This uses pg_cron and a second Edge Function for delivery.

```
Add scheduled report delivery to the reporting tool.

New table: report_schedules
  id uuid primary key default gen_random_uuid()
  report_id uuid references reports(id)
  user_id uuid references auth.users(id)
  frequency text — 'daily' | 'weekly' | 'monthly'
  params jsonb — the filter params to use for the scheduled run
  email_to text
  is_active boolean default true
  next_run_at timestamptz
  last_run_at timestamptz

RLS: users can manage their own schedules (user_id = auth.uid()).

Create an Edge Function at supabase/functions/deliver-scheduled-reports/index.ts:
1. Accept a POST request (will be called by pg_cron)
2. Query report_schedules WHERE is_active = true AND next_run_at <= now()
3. For each schedule, call the existing run-report Edge Function internally
4. Format the rows as an HTML table
5. Send via Resend API (use RESEND_API_KEY from Deno.env.get)
6. Update last_run_at and calculate next_run_at based on frequency

In the UI, add a 'Schedule' Button in the report viewer that opens a Dialog to create/edit a schedule for the current report.
```

> Pro tip: Set up the pg_cron job in Supabase Dashboard → Database → Cron Jobs: SELECT cron.schedule('deliver-reports', '0 * * * *', $$SELECT net.http_post(url:='https://YOUR_PROJECT.supabase.co/functions/v1/deliver-scheduled-reports', headers:='{}', body:='{}')$$).

**Expected result:** A Schedule button appears in the report viewer. Creating a schedule saves to report_schedules. The Edge Function sends email reports on schedule.

## Complete code example

File: `src/lib/export-utils.ts`

```typescript
import { format } from 'date-fns'

export type ReportColumn = {
  key: string
  label: string
  type: 'text' | 'number' | 'date' | 'badge'
}

export function exportToCSV(
  rows: Record<string, unknown>[],
  columns: ReportColumn[],
  reportName: string
): void {
  if (rows.length === 0) {
    console.warn('No rows to export')
    return
  }

  const header = columns.map((c) => `"${c.label}"`).join(',')

  const body = rows
    .map((row) =>
      columns
        .map((c) => {
          const val = row[c.key]
          if (val == null) return '""'
          const str = String(val).replace(/"/g, '""')
          return `"${str}"`
        })
        .join(',')
    )
    .join('\n')

  const csv = `${header}\n${body}`
  const blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' })
  const url = URL.createObjectURL(blob)
  const a = document.createElement('a')
  a.href = url
  a.download = `${reportName.replace(/\s+/g, '-').toLowerCase()}-${format(new Date(), 'yyyy-MM-dd')}.csv`
  document.body.appendChild(a)
  a.click()
  document.body.removeChild(a)
  URL.revokeObjectURL(url)
}

export function exportToPDF(reportName: string): void {
  const original = document.title
  document.title = reportName
  window.print()
  document.title = original
}

export function formatCellValue(value: unknown, type: ReportColumn['type']): string {
  if (value == null) return '—'
  if (type === 'date' && typeof value === 'string') {
    return format(new Date(value), 'MMM d, yyyy')
  }
  if (type === 'number' && typeof value === 'number') {
    return value.toLocaleString()
  }
  return String(value)
}
```

## Common mistakes

- **Building SQL queries by concatenating user-supplied filter values as strings** — String concatenation in SQL is the classic SQL injection vector. A user could supply '; DROP TABLE orders; --' as a date value. Fix: Always use parameterized queries with positional placeholders ($1, $2). The Edge Function should replace :param_name tokens with positional indices and pass values in a separate array.
- **Fetching all report rows before applying pagination** — Reports on large tables can return millions of rows. Fetching all of them crashes the Edge Function and saturates the frontend. Fix: Always apply LIMIT and OFFSET in the SQL itself, inside the Edge Function. Pass page and page_size from the frontend and calculate OFFSET = (page - 1) * page_size.
- **Using the VITE_ prefix for service role key secrets** — VITE_ prefixed variables are injected at build time into the frontend bundle. The service role key would be exposed to all visitors. Fix: Store SUPABASE_SERVICE_ROLE_KEY (no VITE_ prefix) in Cloud tab → Secrets. It is only available inside Edge Functions via Deno.env.get('SUPABASE_SERVICE_ROLE_KEY').
- **Not adding CORS headers to Edge Function responses** — The browser will block all responses from the Edge Function with a CORS error when called from the Lovable preview or your custom domain. Fix: Return 'Access-Control-Allow-Origin': '*' on all responses and handle OPTIONS preflight requests with a 200 response containing the same CORS headers.
- **Enabling RLS on the reports table but forgetting to add SELECT policy** — RLS blocks all access by default. Without a SELECT policy, authenticated users get an empty array from every query on the reports table. Fix: Add an explicit RLS policy: CREATE POLICY 'authenticated read' ON reports FOR SELECT TO authenticated USING (is_active = true).

## Best practices

- Store SQL templates in the database, not in Edge Function source code — this lets you add new reports without a code deployment
- Validate all param types before substituting them into the SQL template — reject non-date strings for date params, reject unknown values for enum params
- Cap page_size at a hard maximum (e.g. 500) in the Edge Function regardless of what the frontend requests
- Use count-only queries (SELECT COUNT(*) FROM (...)) for pagination totals — never load all rows just to count them
- Log every report run to a report_runs table including params used, duration, and row count for debugging and audit purposes
- Scope report access with RLS or a separate report_access table — not every user should see every report
- Cache identical report runs in a report_cache table keyed by (report_id, params_hash) with a TTL — identical requests within 5 minutes serve the cached result
- Provide column type metadata in query_config.columns so the DataTable can format numbers, dates, and badges consistently without frontend code changes

## Frequently asked questions

### Can I run reports against tables I didn't create in this project?

Yes. The Edge Function runs with the service role key which has access to all tables in your Supabase project. You can write SQL in query_config that queries any table, including tables created by other parts of your app. Just make sure those tables exist before running the report.

### How do I prevent users from seeing each other's data in reports?

Add a user_id filter to every report's SQL template — for example, WHERE user_id = :current_user_id. In the Edge Function, inject the authenticated user's ID as a required parameter regardless of what the frontend sends. Never trust the frontend to supply the user ID for security-sensitive filters.

### What happens if a report query takes more than 30 seconds?

Supabase Edge Functions have a 30-second timeout on the free tier and 150 seconds on paid plans. For long-running reports, add a query timeout parameter to the SQL and consider running heavy reports asynchronously: the Edge Function kicks off the query, stores the result in a report_results table, and the frontend polls for completion.

### Can I add charts to my reports?

Yes. The column definitions in query_config can include a visualize field. When the DataTable receives columns with visualize: 'bar' or visualize: 'line', render a Recharts chart above the table using the rows data. The DataTable and chart share the same data array — no second fetch needed.

### Why does the CSV download include extra quote characters?

This is correct CSV escaping. Any field that contains a double quote is escaped by doubling it (" becomes ""). Most spreadsheet applications, including Excel and Google Sheets, parse this correctly. If you see doubled quotes in your editor, the file itself is correct — open it in a spreadsheet app to verify.

### How do I add a new report without touching code?

Insert a new row into the reports table via Supabase Dashboard → Table Editor. Populate query_config with the SQL template and param definitions. The next time a user opens the reporting tool, the new report appears in the sidebar automatically — no Lovable prompt or code change needed.

### Can I get expert help setting this up for my specific data model?

Yes. The RapidDev team helps founders configure reporting tools against their existing Supabase schemas, including writing the query_config templates and setting up scheduled delivery. Reach out at rapidevelopers.com.

### How do I handle reports that need joins across multiple tables?

Write the full JOIN SQL in query_config.sql. For example: SELECT o.id, u.email, o.total FROM orders o JOIN auth.users u ON o.user_id = u.id WHERE o.created_at BETWEEN :start_date AND :end_date. The Edge Function runs the SQL as-is against your database. There are no restrictions on JOIN complexity.

---

Source: https://www.rapidevelopers.com/how-to-build-lovable/reporting-tool
© RapidDev — https://www.rapidevelopers.com/how-to-build-lovable/reporting-tool