A dashboard that runs a large GROUP BY against your production database can turn a routine page refresh into an incident. A reporting engine prevents that by separating report requests from expensive computation, controlling how queries run, and returning results through a predictable API.
The key design choice is to treat a report as a managed workload—not just a SQL query inside an HTTP handler.
1. Choose synchronous or asynchronous execution
Use synchronous queries for small, bounded reports that reliably finish within your API latency budget. For example, fetching the last seven days of pre-aggregated signup counts may be cheap enough to return immediately.
Move reports to a background job when they scan large date ranges, join many tables, export thousands of rows, or call external systems. Return 202 Accepted with a report ID, then let the client poll for status or receive a notification when the file is ready. Do not keep an HTTP connection open while a multi-minute CSV is generated.
2. Separate the request path from the data path
A practical first version has five components:
flowchart LR
U[Dashboard] --> API[Report API]
API --> J[(Report Jobs)]
API --> Q[Queue]
Q --> W[Report Worker]
W --> D[(Analytics Store)]
W --> O[(Object Storage)]
U -->|Poll report ID| API
API -->|Status or signed URL| U
- Report API: authenticates the caller, validates filters, creates a job, and exposes status.
- Queue: absorbs bursts and decouples request traffic from worker capacity.
- Worker: executes an approved report definition, transforms rows, and writes the output.
- Analytics store: serves aggregations and historical queries without competing with transactional traffic.
- Object storage: holds large exports; the API returns a short-lived download URL after authorization.
For a small product, the queue and job table can start in PostgreSQL. Add a dedicated queue or analytical database only when measured workload justifies the operational cost.
3. Persist report state
Keep the job lifecycle explicit so the UI and operators can distinguish queued work from failures.
CREATE TABLE report_jobs (
id uuid PRIMARY KEY,
tenant_id uuid NOT NULL,
requested_by uuid NOT NULL,
report_type text NOT NULL,
filters jsonb NOT NULL DEFAULT '{}',
status text NOT NULL CHECK (
status IN ('queued', 'running', 'succeeded', 'failed')
),
result_key text,
error_code text,
created_at timestamptz NOT NULL DEFAULT now(),
started_at timestamptz,
finished_at timestamptz
);
CREATE INDEX report_jobs_tenant_created_idx
ON report_jobs (tenant_id, created_at DESC);Treat tenant_id as an authorization boundary, not merely a search field. In production, validate state transitions in application code and add retry metadata, expiry, and an idempotency key where users may submit the same request more than once.
4. Execute jobs safely
The request endpoint should validate the report type and filters, insert the job, enqueue its ID, and return the ID. The worker loads that row and resolves the report type through an allowlist. Never accept raw SQL or arbitrary column names from the browser.
A worker’s core flow looks like this:
async function runReport(reportId: string) {
const job = await jobs.claimQueued(reportId);
if (!job) return; // already claimed, cancelled, or not queued
try {
const definition = reportRegistry[job.reportType];
if (!definition) throw new Error("UNSUPPORTED_REPORT");
const rows = await definition.execute({
tenantId: job.tenantId,
filters: validateFilters(job.filters),
timeoutMs: 30_000,
});
const key = await exports.writeCsv(job.id, rows);
await jobs.markSucceeded(job.id, key);
} catch (error) {
await jobs.markFailed(job.id, classifyError(error));
}
}This is an application-level sketch: claimQueued must atomically claim work, and the database, export writer, and error classifier are interfaces you implement. Use a lease or visibility timeout so a worker crash does not strand a job forever. Make output keys deterministic by job ID, and make completion updates safe to repeat.
5. Keep report queries predictable
Do not let every dashboard request scan raw transactional tables. Start with indexes that match common filters, enforce date-range limits, and select only required columns. For recurring metrics, pre-aggregate data at the grain the product displays—for example, daily totals by tenant and event type.
PostgreSQL materialized views persist query results and can make reads faster, but they can be stale until refreshed. That is acceptable for reports with a defined freshness target; it is not appropriate for every operational decision. For event-heavy analytics, a column-oriented store such as ClickHouse may be a better fit. Choose based on query patterns and measured load, not because a database is fashionable.
6. Design for retries and partial failure
A queue usually provides at-least-once delivery, so assume a job can run more than once. Claim jobs atomically, use idempotent writes, and avoid creating a new export every time a message is redelivered. Record a stable error code for the UI while keeping stack traces and diagnostic details in protected logs.
Set limits for execution time, output size, queue depth, and concurrent jobs per tenant. Retry transient network or database errors with bounded backoff; do not endlessly retry invalid filters or unsupported reports. If a job fails after writing a file but before updating its status, a retry should safely overwrite or reuse the same output key.
7. Enforce access and freshness guarantees
Build tenant scope into every report query; do not fetch all rows and filter them after execution. Validate requested fields and sort orders against the report definition. Give workers read-only database permissions where possible, keep exports private by default, and generate short-lived signed URLs only after checking that the requester can access the job.
Expose a dataAsOf timestamp so users can tell how fresh a report is. Monitor queue wait time, execution duration, failure rate, rows scanned, export size, and freshness lag. These measures show whether the bottleneck is worker capacity, query design, or delayed ingestion.
Conclusion
A reliable reporting engine makes expensive work asynchronous, constrains queries to reviewed definitions, stores job state, and delivers results through a controlled path. Start with a job table, a worker, and a few indexed or pre-aggregated queries. Add separate analytics infrastructure when real measurements show that the simpler design no longer meets your latency, freshness, or isolation requirements.
Further reading: PostgreSQL materialized views · ClickHouse materialized views