Building Scheduled Reports That Do Not Melt Your Server

The spike nobody plans for

Scheduled reports have a property that ad-hoc dashboards do not: everyone wants the same time. Monday 9am. Every weekly report for every site, firing at once, each running the heaviest aggregate you have.

An instance that comfortably serves interactive traffic all week can fall over on that one minute.

Jitter the schedule

The cheapest fix is to not run them simultaneously:

// Deterministic per-schedule offset — same report, same slot, every week.
const jitterMs = hash(scheduleId) % (15 * 60_000);

Spreading a hundred reports over fifteen minutes turns a spike into a plateau. Deriving the offset from the schedule id rather than randomly means a given report always lands at the same time, which matters to the person expecting it.

Queue, do not fan out

Generating reports inline in a timer callback means a hundred concurrent queries and no backpressure.

Put them in a queue with a small worker pool — two or three concurrent generations. Total throughput barely changes, because the database is the bottleneck either way, and the instance stays responsive to interactive traffic the whole time.

Idempotency, or people get two emails

Any delivery can fail after the work succeeded. Retry naively and you send twice.

Key each run on (scheduleId, periodStart) and record completion before sending:

INSERT INTO report_runs (schedule_id, period_start, status)
VALUES (?, ?, 'running')
ON CONFLICT (schedule_id, period_start) DO NOTHING
RETURNING id;

No row returned means another worker has it. That single constraint removes an entire class of duplicate-delivery bugs.

Generate once, deliver many

A report going to eight recipients is one generation and eight deliveries. Splitting those stages means a bounced address retries the send without regenerating anything.

Reuse the dashboard's queries

The strongest structural decision is to have reports call the same query functions the dashboard calls, at the same cache keys.

Two payoffs: numbers cannot drift between the email and the screen, and a report generated shortly after someone viewed that dashboard is served from cache. A common pattern — someone opens the dashboard, then the weekly report runs — costs one query rather than two.

Timezones

A "weekly report" needs a timezone to be meaningful. Store the schedule's timezone explicitly and compute period boundaries in it. Defaulting to UTC silently gives a Sydney user a report covering Sunday to Saturday.