Report Builder

The Report Builder turns recurring client communications into a one-time setup. A scheduled report is a ReportSubscription row that captures what to render (template + sections + locations), when to run (cadence + day + hour + IANA timezone), what window to cover (schedule.period), and who gets it (recipients.to, recipients.cc, optional BCC to the creator). It's built for agencies who need a branded monthly executive summary going to ten clients without anyone clicking Export.

The lifecycle is straightforward. You create a subscription on /insights → Report Builder → Scheduled. The scheduler scans every five minutes (cron('*/5 * * * *')) for any status: 'active' subscription whose nextRunAt has passed, freezes its config into a ReportRun, and pushes it through two BullMQ queues: render (a Puppeteer pass that prints the /insights/print/:runId page to PDF and, if requested, builds a PPTX from the same data) and deliver (Brevo sends the email with attachments to your recipients). The run walks queued → rendering → delivering → delivered, or failed with an error.step of render, deliver, or upload if anything blew up.

Creating a Scheduled Report

1. 1

   Open the Scheduled tab

   From the agency dashboard, go to /insights → Report Builder tab → Scheduled sub-tab → New. The form (ReportSubscriptionForm) maps directly onto the ReportSubscription schema, so every field below is a real persisted field — not a UI-only convenience.

2. 2

   Pick a template

   Choose a templateId from the seeded set: Monthly Executive Summary (monthly-exec-summary), Weekly Performance Pulse (weekly-performance-pulse), Quarterly Business Review (quarterly-business-review), or Review Response Report (review-response-report). Each template ships a defaultSections list (e.g. portfolio_kpi, trend_chart, ai_narrative, mom_comparison, location_breakdown, review_summary) and a defaultPeriod. The subscription stores only your sectionOverrides — the rest is inherited from the template at run time.

3. 3

   Set the scope

   Pick scope.locationIds (multi-select; the validator rejects an empty list) and scope.packaging. 'combined' produces one PDF that aggregates every chosen location; 'per_location' produces one PDF per location, all attached to the same email. Pick combined for an executive summary, per_location when each client wants their own deliverable.

4. 4

   Set the schedule

   Configure schedule.cadence ('daily' | 'weekly' | 'monthly'), plus the cadence-specific knob: schedule.dayOfWeek (0 = Sunday … 6 = Saturday) when weekly, or schedule.dayOfMonth (any number 1–31, or the literal string 'last') when monthly. Then pick schedule.hour (0–23) and schedule.timezone (IANA — e.g. 'America/New_York', 'America/Los_Angeles', 'Asia/Kolkata'). Finally, set schedule.period — the data window the report covers: 'last_calendar_month', 'trailing_30d', 'last_calendar_week', 'trailing_7d', or 'yesterday'.

5. 5

   Set recipients and sender identity

   recipients.to[] is required and must have at least one address. recipients.cc[] is optional. recipients.bccCreator defaults to true so the person who created the subscription always gets a copy. recipients.senderDisplayName is the From name on the email (the actual from address is reports@gmbmantra.com), and recipients.replyToEmail is the address replies route to — typically your own support inbox.

6. 6

   Set output formats and branding overrides

   format is an array — ['pdf'], ['pptx'], or both. PDFs are rendered by Puppeteer at A4; PPTX is built from the same page data plus chart canvas snapshots via pptxRenderer. branding.logoR2Key, branding.primaryColor, and branding.footerText are optional per-subscription overrides that win over agency-level defaults.

7. 7

   Save and activate

   Hit Save & activate to set status: 'active' and stamp nextRunAt to the next scheduled time. Save & send test persists the subscription and queues an immediate test run that only emails you. Preview renders right now without saving. Saving without activating leaves status: 'paused' so the scheduler skips it.

Key Concepts

Cadence vs. period — when it fires vs. what it covers

These are independent. schedule.cadence controls when the report fires: daily at hour H, weekly on dayOfWeek at hour H, or monthly on dayOfMonth at hour H, all in schedule.timezone. schedule.period controls what time range the data covers:

• 'last_calendar_month' — the previous full calendar month.
• 'trailing_30d' — the 30 days ending the run.
• 'last_calendar_week' — the previous full calendar week.
• 'trailing_7d' — the 7 days ending the run.
• 'yesterday' — just the previous day.

A monthly cadence with last_calendar_month is the canonical "first of the month, send last month's numbers." A weekly cadence with trailing_7d is "every Monday, last seven days." Mixing is allowed — e.g. weekly cadence reporting on last_calendar_month — and is what templates' cadenceHint and defaultPeriod are for: sensible defaults without locking you in.

Packaging — combined vs. per-location

scope.packaging decides how the locations turn into files:

• 'combined' — one PDF (and optionally one PPTX) for all selected locations. Best for portfolio-level executive views.
• 'per_location' — one PDF (and optionally one PPTX) per location, all attached to the same email. Best when each client wants their own document and shouldn't see the others.

The render worker iterates snapshot.locationIds when per_location and renders once with locationId = null when combined.

Templates — predefined section sets, subscriptions override

A ReportTemplate is a named bundle: templateId, name, description, cadenceHint ('monthly' | 'weekly' | 'daily' | 'any'), defaultPeriod, and defaultSections (an ordered list of { sectionKey, order, enabled }). Subscriptions store sectionOverrides, not the full section list — at run time, the freezer merges template defaults with your overrides. Toggle a section off in the form and it's stored as { sectionKey, enabled: false }; everything else stays inherited.

Branding fallback chain

Three layers resolve per send, in order:

1. ReportSubscription.branding — logoR2Key, primaryColor, footerText. Per-subscription override.
2. AgencySettings — logoUrl, brandColors.primary, customFooterText. Agency-wide defaults for PDF/PPTX reports.
3. System defaults — fallback color #2e7d32, no logo, no footer.

Whichever value is set first wins. logoR2Key can be either a fully-qualified URL (used as-is) or an R2 storage key (resolved to a 48-hour presigned URL at delivery time).

Run states — queued → rendering → delivering → delivered

Every dispatch creates a ReportRun with these states on run.status:

• 'queued' — created by the scheduler, waiting in BullMQ.
• 'rendering' — Puppeteer is opening /insights/print/:runId and printing PDFs (and PPTX if requested).
• 'delivering' — render finished, files are in R2, Brevo is sending the email.
• 'delivered' — Brevo accepted the message; delivery.messageId is stored, lastRunStatus: 'delivered' on the subscription.
• 'failed' — terminal. error.step is 'render', 'deliver', or 'upload', and error.message carries the underlying reason.

Runs older than 7 days that were never picked up cause the scheduler to flip their subscription to status: 'paused' and skip — that's the pausedLate counter in the scan result.

Frequently Asked

Related