SaaS Reporting Engine: Scheduled & Custom Reports

Every reporting feature starts innocently — a couple of hardcoded SQL views behind a dashboard — and works fine until a premium client opens it, picks a six-month date range, and asks for a summary across a few million rows. Run that on your primary database and the connection pool freezes, threads starve, and the entire multi-tenant app goes down for everyone, usually at 9am Monday when everyone runs their weekly report at once. A real SaaS reporting engine exists to make sure that query never touches the database your live users depend on.

The architecture that holds: isolate reports onto a read replica, generate them on a background queue, stream the output, cache the expensive ones, and scope every query to the tenant. For over 90% of mid-to-large enterprises an hour of downtime now costs more than $300,000 (ITIC, 2024) — a report query taking the app down isn't a slow page, it's a financial event. Here are the seven rules that keep reporting from becoming an outage.

SaaS Reporting Engine Rule 1: Separate OLTP From Reporting

Split the workload into two layers and never blur them:

• OLTP core — your write-heavy primary database, handling signups, payments, the live app.
• Read-replica (OLAP) layer — a read-only clone where every reporting query runs.

1[App writes] ──> [OLTP primary] ──┐
2                                  │  replication
3                                  ▼
4[Reporting]  ──> [BullMQ worker] ──> [read replica]

Point the reporting engine exclusively at the replica and heavy aggregations can't starve live traffic — the analytical load lands on a database that isn't serving your users.

Rule 2: Allowlist Custom Report Columns

Custom report builders are where SQL injection sneaks in, because you cannot parameterize column or table names — only values. So allowlist the structure. Validate every requested column against a fixed set of permitted fields and build the query from that, never from raw strings:

1// Only these columns can ever appear in a report.
2const ALLOWED_COLUMNS = new Set(['invoice_id', 'amount_usd', 'status', 'created_at']);
3
4export class CustomReportDto {
5  @IsArray() @IsString({ each: true })
6  columns: string[];
7
8  @IsObject()
9  filters: { dateStart: string; dateEnd: string; status?: string };
10}
11
12function safeColumns(requested: string[]): string[] {
13  const cols = requested.filter((c) => ALLOWED_COLUMNS.has(c));
14  if (cols.length === 0) throw new BadRequestException('No valid columns selected.');
15  return cols; // safe to interpolate — every value came from the allowlist
16}

Allowlist the column names, bind the filter values as parameters, and a dynamic builder stays injection-proof.

Rule 3: Generate Reports on a Queue

Enterprise clients schedule reports to land in their inbox Monday at 6am. Never generate those in the web request — push them to a BullMQ queue and let a worker do the heavy lifting:

1// src/reporting/scheduled-report.processor.ts
2import { Processor, WorkerHost } from '@nestjs/bullmq';
3import { Job } from 'bullmq';
4
5@Processor('scheduled-reports')
6export class ScheduledReportProcessor extends WorkerHost {
7  async process(job: Job<{ tenantId: string; def: unknown; recipients: string[] }>) {
8    const data = await this.queryReplica(job.data.tenantId, job.data.def); // read replica only
9    const file = await this.buildFile(data);                               // streamed
10    await this.emailReport(job.data.recipients, file);
11    return { ok: true };
12  }
13  // ...
14}

A queue gives you retries, concurrency limits, and isolation — a slow Excel build for one tenant never touches the API everyone else is using.

Rule 4: Predefine the Common Reports

Most clients run the same handful — "Monthly Sales," "Daily Activity." Since the parameters are identical for everyone, pre-compute those during off-peak hours into summary tables and serve them instantly. Save the expensive on-the-fly work for genuinely custom requests, and the common case stops hitting the replica at all.

Rule 5: Stream Every Output Format

The wrong file library OOM-crashes the worker. Stream all three formats:

• CSV — lightest; write rows chunk-by-chunk with backpressure.
• Excel — use a streaming-capable library like exceljs, not one that builds a giant in-memory array.
• PDF — needs a headless browser (Puppeteer) to render CSS to vectors, so run it in an isolated worker container, never on the main web process.

The rule is constant: never hold the full dataset in RAM at once.

Rule 6: Cache the Expensive Queries

When a whole team reloads the same Monday dashboard, you're running one heavy aggregation dozens of times. Hash the report parameters into a key (report:{tenantId}:{paramsHash}), store the result in Redis with a sensible TTL (say an hour), and serve repeat views from cache. The first run pays for the query; everyone after it loads instantly.

Rule 7: Lock Down Tenant and Column Access

Custom report builders are a prime spot for a BOLA leak — change a filter in the browser dev tools and pull another company's financials. Two controls:

• Tenant scoping — force a server-derived tenant_id into the root WHERE of every generated query, never one from the client, and back it with PostgreSQL row-level security so the database refuses cross-tenant rows even if a query forgets.
• Column governance — check the user's role before returning sensitive columns; strip salary and the like for staff-level roles before the query runs.

When to Reach for Columnar OLAP

Once you're past a few hundred million rows, a columnar store like ClickHouse reads only the columns a query needs instead of whole rows — dramatically faster for analytics (it's frequently cited as up to 100x on large datasets). Most SaaS never needs it; the read replica carries you a very long way.

Isolate, queue, stream, cache, and scope. Build the reporting engine on those five and the worst-case report — the million-row, six-month, run-by-the-whole-team-at-once query — becomes a background job that emails a file, not the incident that takes your Monday morning down. The boring architecture is the one nobody notices, which for a reporting engine is exactly the point.