# How to Build a Reporting Tool with Replit

- Tool: How to Build with Replit
- Difficulty: Intermediate
- Compatibility: Replit Free
- Last updated: April 2026

## TL;DR

Build a custom report builder in Replit using Express and PostgreSQL in 1-2 hours. Users define reports by picking columns, adding filters, and choosing aggregates — the engine safely translates their selections into SQL queries using Drizzle's query builder. Exports to CSV or PDF with a Scheduled Deployment for emailed reports.

## Before you start

- A Replit account (Free tier is sufficient)
- A SendGrid account (free tier) for scheduled report email delivery
- SENDGRID_API_KEY added to Replit Secrets and Deployment Secrets
- Some data in your PostgreSQL tables to report on — the tool works on your existing tables

## Step-by-step guide

### 1. Scaffold the project with Replit Agent

Create a new Replit App and paste this prompt. Agent builds the complete reporting backend with all four tables and the React wizard frontend.

```
// Build a custom reporting tool with Express and PostgreSQL using Drizzle ORM.
// Use Replit Auth for authentication.
//
// Tables:
// 1. data_sources: id serial primary key, user_id text not null, name text not null,
//    type text default 'postgres' (enum: postgres/csv/api),
//    connection_config jsonb (stores table name for postgres type),
//    created_at timestamp default now()
// 2. reports: id serial primary key, user_id text not null, name text not null,
//    data_source_id integer references data_sources not null,
//    columns jsonb not null (array of {field, label, aggregate: null/sum/count/avg/min/max}),
//    filters jsonb (array of {field, operator: eq/neq/gt/lt/gte/lte/like/in, value}),
//    group_by text[] (PostgreSQL text array of field names),
//    sort_by jsonb ({field, direction: asc/desc}),
//    created_at timestamp default now(), updated_at timestamp default now()
// 3. report_schedules: id serial primary key,
//    report_id integer references reports not null,
//    frequency text not null (enum: daily/weekly/monthly),
//    email_to text not null, last_run_at timestamp, next_run_at timestamp
// 4. report_exports: id serial primary key,
//    report_id integer references reports not null,
//    format text not null (enum: csv/pdf/json),
//    file_url text, created_at timestamp default now()
//
// Routes:
// GET/POST /api/data-sources
// GET /api/data-sources/:id/schema (introspect columns using information_schema)
// GET/POST /api/reports, GET /api/reports/:id
// POST /api/reports/:id/run (execute report, return JSON results)
// POST /api/reports/:id/export (generate CSV or PDF)
// POST /api/report-schedules
//
// Bind to 0.0.0.0:3000. Use Replit Auth.
```

> Pro tip: After scaffolding, register your first data source via POST /api/data-sources with {name: 'Orders', type: 'postgres', connection_config: {table: 'orders'}}. Then call GET /api/data-sources/1/schema to see all columns auto-detected from information_schema.

**Expected result:** Running Express app with four tables. GET /api/data-sources returns empty array. Replit Auth login gate on all routes.

### 2. Build the schema introspection route

This route queries PostgreSQL's information_schema to return the columns and data types for any registered table — powering the dynamic column picker in the report wizard.

```
const express = require('express');
const { db } = require('../db');
const { dataSources } = require('../schema');
const { eq, and } = require('drizzle-orm');

const router = express.Router();

router.get('/api/data-sources/:id/schema', async (req, res) => {
  if (!req.user) return res.status(401).json({ error: 'Login required' });

  const [source] = await db.select().from(dataSources)
    .where(and(
      eq(dataSources.id, parseInt(req.params.id)),
      eq(dataSources.userId, req.user.id)
    )).limit(1);

  if (!source) return res.status(404).json({ error: 'Data source not found' });

  const tableName = source.connectionConfig?.table;
  if (!tableName) return res.status(400).json({ error: 'No table configured for this source' });

  // Validate table name is alphanumeric to prevent SQL injection
  if (!/^[a-zA-Z0-9_]+$/.test(tableName)) {
    return res.status(400).json({ error: 'Invalid table name' });
  }

  const columns = await db.execute({
    sql: `
      SELECT column_name, data_type, is_nullable, column_default
      FROM information_schema.columns
      WHERE table_schema = 'public'
        AND table_name = $1
      ORDER BY ordinal_position
    `,
    params: [tableName],
  });

  // Map PostgreSQL types to friendlier labels
  const typeMap = {
    'integer': 'number', 'bigint': 'number', 'numeric': 'number',
    'text': 'string', 'character varying': 'string', 'varchar': 'string',
    'timestamp with time zone': 'date', 'timestamp without time zone': 'date',
    'date': 'date', 'boolean': 'boolean', 'jsonb': 'json', 'json': 'json',
    'ARRAY': 'array',
  };

  const schema = columns.rows.map(col => ({
    field: col.column_name,
    type: typeMap[col.data_type] || col.data_type,
    nullable: col.is_nullable === 'YES',
    // Suggest aggregates based on type
    supportedAggregates: typeMap[col.data_type] === 'number'
      ? ['count', 'sum', 'avg', 'min', 'max']
      : ['count'],
  }));

  return res.json({ sourceId: source.id, tableName, columns: schema });
});

module.exports = router;
```

> Pro tip: The table name validation regex (alphanumeric + underscore only) is essential. Without it, a malicious user could inject SQL via the table name field — even though it's used in a parameterized query context, table names can't be parameterized in PostgreSQL.

**Expected result:** GET /api/data-sources/1/schema returns an array of columns with types. A table with 8 columns returns 8 schema entries, each with field name, type, and supported aggregates.

### 3. Build the safe SQL query executor

The report execution engine reads the report's JSON config and builds a parameterized SQL query using Drizzle's query builder. Never string-interpolate user values — always use parameterized queries.

```
const express = require('express');
const { db } = require('../db');
const { reports, dataSources } = require('../schema');
const { eq, and } = require('drizzle-orm');
const { sql } = require('drizzle-orm');

const router = express.Router();

// Map operator names to SQL operators
const OPERATORS = {
  eq: '=', neq: '!=', gt: '>', lt: '<', gte: '>=', lte: '<=', like: 'LIKE',
};

router.post('/api/reports/:id/run', express.json(), async (req, res) => {
  if (!req.user) return res.status(401).json({ error: 'Login required' });

  const [report] = await db.select().from(reports)
    .where(and(eq(reports.id, parseInt(req.params.id)), eq(reports.userId, req.user.id)))
    .limit(1);
  if (!report) return res.status(404).json({ error: 'Report not found' });

  const [source] = await db.select().from(dataSources)
    .where(eq(dataSources.id, report.dataSourceId)).limit(1);
  const tableName = source?.connectionConfig?.table;

  if (!tableName || !/^[a-zA-Z0-9_]+$/.test(tableName)) {
    return res.status(400).json({ error: 'Invalid data source table' });
  }

  // Build SELECT clause
  const columnDefs = report.columns;
  const selectParts = columnDefs.map(col => {
    const safeField = col.field.replace(/[^a-zA-Z0-9_]/g, '');
    if (col.aggregate && col.aggregate !== 'none') {
      return `${col.aggregate.toUpperCase()}(${safeField}) AS "${col.label || safeField}"`;
    }
    return `${safeField} AS "${col.label || safeField}"`;
  });

  // Build WHERE clause with parameterized values
  const params = [];
  const whereParts = (report.filters || []).map(filter => {
    const safeField = filter.field.replace(/[^a-zA-Z0-9_]/g, '');
    const op = OPERATORS[filter.operator] || '=';
    params.push(filter.value);
    return `${safeField} ${op} $${params.length}`;
  });

  // Build GROUP BY
  const groupByFields = (report.groupBy || []).map(f => f.replace(/[^a-zA-Z0-9_]/g, ''));

  // Build ORDER BY
  let orderBy = '';
  if (report.sortBy?.field) {
    const safeSort = report.sortBy.field.replace(/[^a-zA-Z0-9_]/g, '');
    const dir = report.sortBy.direction === 'desc' ? 'DESC' : 'ASC';
    orderBy = `ORDER BY ${safeSort} ${dir}`;
  }

  const query = [
    `SELECT ${selectParts.join(', ')}`,
    `FROM ${tableName}`,
    whereParts.length ? `WHERE ${whereParts.join(' AND ')}` : '',
    groupByFields.length ? `GROUP BY ${groupByFields.join(', ')}` : '',
    orderBy,
    'LIMIT 1000',
  ].filter(Boolean).join('\n');

  const result = await db.execute({ sql: query, params });
  return res.json({ rows: result.rows, count: result.rows.length });
});

module.exports = router;
```

> Pro tip: The LIMIT 1000 at the end prevents memory exhaustion on large tables. For exports, the CSV streaming route can remove this limit and stream rows in chunks of 500 to avoid loading the entire result into memory.

**Expected result:** POST /api/reports/1/run returns {rows: [{column1: val, column2: val}], count: 47} based on the report's saved configuration.

### 4. Add CSV export and scheduled report delivery

The export route generates a CSV from the report results. A Scheduled Deployment checks for due report schedules and emails the CSV attachment via SendGrid.

```
// Ask Agent to add CSV export and scheduled delivery with this prompt:
// Add to server/routes/reports.js:
//
// POST /api/reports/:id/export:
//   1. Require auth, load report config
//   2. Run the same SQL query as /run (extract to a shared executeReport(reportId) function)
//   3. If format='csv':
//      - Generate CSV string: first row = column labels, remaining rows = data values
//      - Use Array.join() to build CSV, escape values with quotes if they contain commas
//      - Set Content-Type: text/csv, Content-Disposition: attachment; filename=report.csv
//      - Send the CSV string directly (no file storage needed for on-demand exports)
//   4. If format='pdf':
//      - Use pdfkit npm package
//      - Create a PDF doc with the report name as title, run date, and data as a simple table
//      - Send as application/pdf response
//   5. Insert into report_exports table with format and created_at
//
// Create server/jobs/sendScheduledReports.js:
//   1. Query report_schedules where next_run_at <= now()
//   2. For each due schedule, call executeReport(scheduleId) to get rows
//   3. Generate CSV attachment
//   4. Send email via SendGrid (SENDGRID_API_KEY from process.env) with CSV attachment
//   5. Update last_run_at = now(), calculate next_run_at based on frequency
//      (daily: + 1 day, weekly: + 7 days, monthly: + 1 month)
//   6. Deploy as Scheduled Deployment running every hour
```

> Pro tip: For the CSV export, handle commas and newlines in cell values by wrapping all values in double quotes and escaping internal double quotes as '""'. This prevents broken CSV files when report data contains punctuation.

**Expected result:** POST /api/reports/1/export with {format:'csv'} triggers a CSV file download. The Scheduled Deployment sends the report CSV via email at the configured frequency.

### 5. Build the three-step React report wizard

Ask Agent to create the React frontend with the wizard for building reports and a saved reports list.

```
// Ask Agent to build the React frontend with this prompt:
// Build a React reporting tool frontend with three pages:
//
// 1. Reports List page (/):
//    - List of user's saved reports with name, data source, and created date
//    - Each row has: Run (executes and shows results), Export CSV, Export PDF, Schedule buttons
//    - 'Create Report' button navigates to the wizard
//
// 2. Report Builder Wizard (/reports/new and /reports/:id/edit):
//    Step 1 — Select Data Source and Columns:
//    - Dropdown of GET /api/data-sources sources
//    - On selection, fetch GET /api/data-sources/:id/schema for column list
//    - Checkbox list of columns, each with a label input and aggregate selector dropdown
//      (none/count/sum/avg/min/max — only show numeric aggregates for number-type columns)
//
//    Step 2 — Add Filters:
//    - 'Add Filter' button adds a row: field selector, operator selector, value input
//    - Operators: equals, not equals, greater than, less than, contains, is empty
//    - A 'Sort by' row with field selector and ASC/DESC toggle
//
//    Step 3 — Preview and Save:
//    - 'Preview' button calls POST /api/reports/:id/run and shows results in a data table
//    - Results table with sortable column headers
//    - Row count badge
//    - 'Save Report' saves the config, 'Export CSV' triggers download
//
// 3. Schedule Dialog:
//    - Opens when clicking 'Schedule' on a saved report
//    - Frequency selector (daily/weekly/monthly)
//    - Email input
//    - Calls POST /api/report-schedules on save
```

**Expected result:** The wizard shows column checkboxes populated from schema introspection. Adding filters shows the correct operators. Preview renders a data table with real query results.

## Complete code example

File: `server/routes/dataSources.js`

```javascript
const express = require('express');
const { db } = require('../db');
const { dataSources } = require('../schema');
const { eq, and } = require('drizzle-orm');
const { withDbRetry } = require('../lib/retryDb');

const router = express.Router();

const TYPE_MAP = {
  integer: 'number', bigint: 'number', numeric: 'number', real: 'number',
  text: 'string', 'character varying': 'string', varchar: 'string', char: 'string',
  'timestamp with time zone': 'date', 'timestamp without time zone': 'date', date: 'date',
  boolean: 'boolean', jsonb: 'json', json: 'json', 'ARRAY': 'array',
};

router.get('/api/data-sources', async (req, res) => {
  if (!req.user) return res.status(401).json({ error: 'Login required' });
  const rows = await db.select().from(dataSources)
    .where(eq(dataSources.userId, req.user.id))
    .orderBy(dataSources.name);
  return res.json({ sources: rows });
});

router.post('/api/data-sources', express.json(), async (req, res) => {
  if (!req.user) return res.status(401).json({ error: 'Login required' });
  const { name, type, connectionConfig } = req.body;
  if (!name || !type) return res.status(400).json({ error: 'name and type are required' });
  if (type === 'postgres' && !/^[a-zA-Z0-9_]+$/.test(connectionConfig?.table)) {
    return res.status(400).json({ error: 'Invalid table name — alphanumeric and underscores only' });
  }
  const row = await withDbRetry(() =>
    db.insert(dataSources).values({
      userId: req.user.id, name, type,
      connectionConfig: connectionConfig || null,
    }).returning()
  );
  return res.status(201).json(row[0]);
});

router.get('/api/data-sources/:id/schema', async (req, res) => {
  if (!req.user) return res.status(401).json({ error: 'Login required' });
  const [source] = await db.select().from(dataSources)
    .where(and(eq(dataSources.id, parseInt(req.params.id)), eq(dataSources.userId, req.user.id)))
    .limit(1);
  if (!source) return res.status(404).json({ error: 'Data source not found' });
  const tableName = source.connectionConfig?.table;
  if (!tableName || !/^[a-zA-Z0-9_]+$/.test(tableName)) {
    return res.status(400).json({ error: 'Invalid table name in data source config' });
  }
  const result = await db.execute({
    sql: `SELECT column_name, data_type, is_nullable FROM information_schema.columns
          WHERE table_schema = 'public' AND table_name = $1 ORDER BY ordinal_position`,
    params: [tableName],
  });
  const columns = result.rows.map(col => ({
    field: col.column_name,
    type: TYPE_MAP[col.data_type] || col.data_type,
    nullable: col.is_nullable === 'YES',
    supportedAggregates: TYPE_MAP[col.data_type] === 'number'
      ? ['none', 'count', 'sum', 'avg', 'min', 'max']
module.exports = router;
```

## Common mistakes

- **String-interpolating user-selected field names directly into SQL** — A malicious user could name their column 'name; DROP TABLE users --' and execute arbitrary SQL via the report builder. Fix: Always sanitize field names with a regex that only allows alphanumeric characters and underscores: field.replace(/[^a-zA-Z0-9_]/g, ''). Never use raw user input as table or column names without this validation.
- **Running reports without a LIMIT clause** — A report on a 10 million row table without a LIMIT will try to load all rows into memory, crash the Express process, and potentially exhaust PostgreSQL connections. Fix: Always add LIMIT 1000 (or configurable max) to the generated query for preview. For exports, stream rows in batches of 500 rather than loading the full result set at once.
- **Using the same data source for the reporting tool and the application it reports on** — Heavy report queries can lock rows or compete for connections with your main app's queries, causing slowdowns during report execution. Fix: Reporting queries use SELECT only, so they shouldn't lock rows. But limit max concurrent report queries to 3 using a simple in-memory counter middleware to prevent overloading the connection pool.
- **Forgetting to add SENDGRID_API_KEY to Deployment Secrets** — Scheduled report emails tested in the workspace will fail silently after deployment because workspace Secrets don't carry over to deployed apps. Fix: Add SENDGRID_API_KEY to both Workspace Secrets (lock icon) and Deployment Secrets (Publish pane → Secrets). Redeploy after adding.

## Best practices

- Validate all user-provided field names and table names against a strict alphanumeric regex before using them in any SQL context — never trust user input for identifiers.
- Always add LIMIT to generated queries. Let users configure a higher limit (up to 10,000) only for exports, and stream those in chunks.
- Use PostgreSQL information_schema for column discovery — it's always accurate and eliminates maintenance of a separate schema definition file.
- Store report configs as JSONB rather than separate relational columns — report definitions change shape often, and JSONB handles this without schema migrations.
- Deploy on Autoscale for the main report API. Create a separate Scheduled Deployment for the email delivery job — run every hour to check for due schedules.
- Add SENDGRID_API_KEY to Replit Secrets via the lock icon in the sidebar, and remember to add it again in Deployment Secrets in the Publish pane — they are completely separate.
- Use Drizzle Studio (Database tab in Replit sidebar) to inspect report_schedules and verify next_run_at is being calculated correctly after each delivery.

## Frequently asked questions

### Can I report on tables from a different database, not Replit's built-in PostgreSQL?

The current build uses Replit's built-in PostgreSQL and the information_schema for introspection. To connect to an external database, store the external connection string in Replit Secrets and create a second Drizzle client in server/externalDb.js. Note that external databases require whitelisting 0.0.0.0/0 since Replit has dynamic outbound IPs.

### Is the report execution safe from SQL injection?

Yes, if you follow the pattern in step 3: sanitize all field and table names with regex (only alphanumeric + underscore), use parameterized queries for all filter values via the params array, and never string-interpolate user input directly into SQL strings. The OPERATORS whitelist ensures only valid comparison operators are used.

### What Replit plan do I need?

Free tier is sufficient. The main app runs on Autoscale, and the Scheduled Deployment for email delivery also runs on Free tier. SendGrid has a free tier of 100 emails/day — enough for most scheduled report needs.

### Can non-technical users add data to the reporting tool without SQL?

That's the whole point — the three-step wizard lets users pick columns, add filter conditions, and choose aggregates through dropdowns and checkboxes. They never write SQL. The introspection API shows only the columns that actually exist in the table.

### How do I handle reports that join multiple tables?

The current build supports single-table reports. For joins, extend the report schema with a joins JSONB field (array of {table, join_type, on_field}). The query builder would need to generate JOIN clauses for each entry. This is the main complexity increase for a v2 build.

### Should I deploy on Autoscale or Reserved VM?

Autoscale works for the main report API. Reports are run on-demand and users tolerate a short cold-start wait. The scheduled report delivery job runs as a separate Scheduled Deployment, completely independent of the main app's deployment target.

### Can RapidDev help build a custom reporting tool for my business?

Yes. RapidDev builds custom reporting systems with multi-table joins, role-based access to specific data sources, white-labeled report exports, and integration with your existing BI stack. 600+ apps built, free consultation available.

### What happens if a report returns too many rows and crashes the server?

The LIMIT 1000 in the query execution prevents loading too many rows for preview. For CSV exports, stream rows in batches of 500 using a cursor-based approach rather than loading the full result set. Add a 30-second query timeout on the database connection to prevent runaway queries.

---

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