How to Build a Reporting Tool with Replit

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

1const express = require('express');
2const { db } = require('../db');
3const { dataSources } = require('../schema');
4const { eq, and } = require('drizzle-orm');
5
6const router = express.Router();
7
8router.get('/api/data-sources/:id/schema', async (req, res) => {
9  if (!req.user) return res.status(401).json({ error: 'Login required' });
10
11  const [source] = await db.select().from(dataSources)
12    .where(and(
13      eq(dataSources.id, parseInt(req.params.id)),
14      eq(dataSources.userId, req.user.id)
15    )).limit(1);
16
17  if (!source) return res.status(404).json({ error: 'Data source not found' });
18
19  const tableName = source.connectionConfig?.table;
20  if (!tableName) return res.status(400).json({ error: 'No table configured for this source' });
21
22  // Validate table name is alphanumeric to prevent SQL injection
23  if (!/^[a-zA-Z0-9_]+$/.test(tableName)) {
24    return res.status(400).json({ error: 'Invalid table name' });
25  }
26
27  const columns = await db.execute({
28    sql: `
29      SELECT column_name, data_type, is_nullable, column_default
30      FROM information_schema.columns
31      WHERE table_schema = 'public'
32        AND table_name = $1
33      ORDER BY ordinal_position
34    `,
35    params: [tableName],
36  });
37
38  // Map PostgreSQL types to friendlier labels
39  const typeMap = {
40    'integer': 'number', 'bigint': 'number', 'numeric': 'number',
41    'text': 'string', 'character varying': 'string', 'varchar': 'string',
42    'timestamp with time zone': 'date', 'timestamp without time zone': 'date',
43    'date': 'date', 'boolean': 'boolean', 'jsonb': 'json', 'json': 'json',
44    'ARRAY': 'array',
45  };
46
47  const schema = columns.rows.map(col => ({
48    field: col.column_name,
49    type: typeMap[col.data_type] || col.data_type,
50    nullable: col.is_nullable === 'YES',
51    // Suggest aggregates based on type
52    supportedAggregates: typeMap[col.data_type] === 'number'
53      ? ['count', 'sum', 'avg', 'min', 'max']
54      : ['count'],
55  }));
56
57  return res.json({ sourceId: source.id, tableName, columns: schema });
58});
59
60module.exports = router;

1const express = require('express');
2const { db } = require('../db');
3const { reports, dataSources } = require('../schema');
4const { eq, and } = require('drizzle-orm');
5const { sql } = require('drizzle-orm');
6
7const router = express.Router();
8
9// Map operator names to SQL operators
10const OPERATORS = {
11  eq: '=', neq: '!=', gt: '>', lt: '<', gte: '>=', lte: '<=', like: 'LIKE',
12};
13
14router.post('/api/reports/:id/run', express.json(), async (req, res) => {
15  if (!req.user) return res.status(401).json({ error: 'Login required' });
16
17  const [report] = await db.select().from(reports)
18    .where(and(eq(reports.id, parseInt(req.params.id)), eq(reports.userId, req.user.id)))
19    .limit(1);
20  if (!report) return res.status(404).json({ error: 'Report not found' });
21
22  const [source] = await db.select().from(dataSources)
23    .where(eq(dataSources.id, report.dataSourceId)).limit(1);
24  const tableName = source?.connectionConfig?.table;
25
26  if (!tableName || !/^[a-zA-Z0-9_]+$/.test(tableName)) {
27    return res.status(400).json({ error: 'Invalid data source table' });
28  }
29
30  // Build SELECT clause
31  const columnDefs = report.columns;
32  const selectParts = columnDefs.map(col => {
33    const safeField = col.field.replace(/[^a-zA-Z0-9_]/g, '');
34    if (col.aggregate && col.aggregate !== 'none') {
35      return `${col.aggregate.toUpperCase()}(${safeField}) AS "${col.label || safeField}"`;
36    }
37    return `${safeField} AS "${col.label || safeField}"`;
38  });
39
40  // Build WHERE clause with parameterized values
41  const params = [];
42  const whereParts = (report.filters || []).map(filter => {
43    const safeField = filter.field.replace(/[^a-zA-Z0-9_]/g, '');
44    const op = OPERATORS[filter.operator] || '=';
45    params.push(filter.value);
46    return `${safeField} ${op} $${params.length}`;
47  });
48
49  // Build GROUP BY
50  const groupByFields = (report.groupBy || []).map(f => f.replace(/[^a-zA-Z0-9_]/g, ''));
51
52  // Build ORDER BY
53  let orderBy = '';
54  if (report.sortBy?.field) {
55    const safeSort = report.sortBy.field.replace(/[^a-zA-Z0-9_]/g, '');
56    const dir = report.sortBy.direction === 'desc' ? 'DESC' : 'ASC';
57    orderBy = `ORDER BY ${safeSort} ${dir}`;
58  }
59
60  const query = [
61    `SELECT ${selectParts.join(', ')}`,
62    `FROM ${tableName}`,
63    whereParts.length ? `WHERE ${whereParts.join(' AND ')}` : '',
64    groupByFields.length ? `GROUP BY ${groupByFields.join(', ')}` : '',
65    orderBy,
66    'LIMIT 1000',
67  ].filter(Boolean).join('\n');
68
69  const result = await db.execute({ sql: query, params });
70  return res.json({ rows: result.rows, count: result.rows.length });
71});
72
73module.exports = router;

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

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