EP-012D: Report Builder & Scheduled Delivery
Parent Epic: EP-012 Analytics, Reporting & Night Audit Split Note: EP-012 has been split into sub-epics EP-012A, EP-012B, EP-012C, EP-012D, EP-012E. This file covers the custom report builder, report scheduler, multi-format export, and report catalog management.
| Field | Value |
|---|---|
| Epic ID | EP-012D |
| Epic Name | Report Builder & Scheduled Delivery |
| Parent Epic | EP-012 Analytics, Reporting & Night Audit |
| Owner | Product — Revenue Intelligence & Operations |
| Created | 2026-03-27 |
| Last Updated | 2026-03-27 |
| Status | Partial |
| Target Release | R4 (Q3 2026) |
1. Business Objective
Every hotel has unique reporting needs that canned reports cannot satisfy. A resort GM wants a single report combining F&B revenue by outlet with room occupancy by floor; a chain CFO wants a monthly YoY comparison across all properties. The custom report builder allows non-technical users to design and save these reports without IT involvement. Report scheduling eliminates the manual task of running and emailing reports — a 30-minute/day time sink for Finance Managers.
2. Audience
| Role | Interaction |
|---|---|
| Finance Manager | Schedules monthly P&L, aging receivable reports for email delivery |
| General Manager | Creates custom combined reports; schedules daily KPI summary email |
| Super Admin | Manages report templates, controls which roles can run which reports |
| Revenue Manager | Builds custom pivot tables with dimensions of choice |
3. Scope
3.1 In Scope
- Custom report builder: select data source (reservations, folios, POS, housekeeping), choose dimensions and measures, apply filters, preview
- Save and share custom report templates within the property
- Report catalog: listing of all built-in and custom reports with descriptions
- Report preview and execute: runs query against analytics database
- Multi-format export from any report: CSV, PDF (hotel letterhead), Excel (.xlsx)
- Report scheduler: define a cron schedule (daily, weekly, monthly) for any report
- Scheduled report delivery: email the report as an attachment to configured recipients
- Report run history: log of past runs with status, duration, and download link
- Role-based report access: Finance Manager sees financial reports; Housekeeping Manager sees operational reports; configurable by Super Admin
3.2 Out of Scope
- Real-time KPI dashboard (EP-012A)
- GST compliance reports (EP-012C)
- Financial P&L (EP-012B) — these are built-in reports, not custom
- BI tool embedding (Metabase, Superset) — future phase
- Natural language query (NLQ) — EP-013 AI analytics
4. Current Implementation Status
4.1 Backend — analytics-service
| Controller / Endpoint | Status | Notes |
|---|---|---|
GET /reports/catalog | Built | Lists all available built-in reports |
POST /reports/custom | Not Started | Custom query engine not built; only UI exists |
GET /reports/custom | Not Started | List saved custom reports |
GET /reports/custom/:id/run | Not Started | Execute custom report query |
POST /reports/custom/:id/export | Not Started | Export custom report results |
POST /reports/schedule | Not Started | Create scheduled report |
GET /reports/schedule | Not Started | List all schedules |
DELETE /reports/schedule/:id | Not Started | Remove schedule |
GET /reports/history | Not Started | Run history log |
4.2 Dashboard — 02-hmp-web-dashboard
| Component | Status | Notes |
|---|---|---|
| Report Catalog page | Built | Lists all reports with descriptions and run buttons |
| Custom Report Builder UI | Built | Drag-and-drop dimension/measure selector |
| Report Preview (UI-only) | Partial | Preview shows schema but can't execute against DB |
| Report Scheduler UI | Not Started | No scheduling configuration UI |
| Report Run History UI | Not Started | - |
5. Gap Analysis
| # | Gap | Impact | Effort | Priority |
|---|---|---|---|---|
| 1 | Custom report backend query engine completely missing | High | L | P1 |
| 2 | Scheduled report delivery (cron + email) not implemented | High | M | P1 |
| 3 | PDF export formatting — needs hotel letterhead template | Medium | S | P2 |
| 4 | Excel export (.xlsx) not implemented | Medium | S | P2 |
| 5 | Role-based report access control not enforced at API level | Medium | S | P2 |
6. User Stories
| Story ID | Title | Priority | Points | Sprint | Status |
|---|---|---|---|---|---|
| US-012-16 | Custom Report Builder | P2 | 21 | 5 | Partial |
| US-012-20 | Scheduled Report Delivery | P1 | 5 | 4 | Not Started |
| US-012D-01 | Custom Report Query Engine (Backend) | P1 | 13 | 4 | Not Started |
| US-012D-02 | Report Catalog with Role-Based Access | P1 | 5 | 4 | Partial |
| US-012D-03 | PDF Export with Hotel Letterhead | P2 | 5 | 4 | Partial |
| US-012D-04 | Excel (.xlsx) Export | P2 | 5 | 4 | Not Started |
| US-012D-05 | Report Run History Log | P2 | 3 | 5 | Not Started |
| US-012D-06 | Save and Share Custom Report Templates | P2 | 5 | 5 | Not Started |
7. Detailed User Stories
US-012-16: Custom Report Builder
As a General Manager, I want to build a custom report by selecting the data dimensions and measures I need, So that I can get insights that canned reports don't provide without asking IT for a custom extract.
Priority: P2 | Points: 21 | Sprint: 5 | Status: Partial
Implementation Status
The UI drag-and-drop builder is built in the dashboard. Backend query engine does not exist. Users can visually design a report but clicking "Run" returns an error.
Acceptance Criteria
Scenario: Build and run a custom combined report
Given I am logged in as General Manager
When I open "Custom Report Builder"
And I select data source: "Reservations + Folios"
And I add dimensions: "Booking Source", "Room Type", "Month"
And I add measures: "Total Revenue", "Avg ADR", "Room Nights"
And I apply filter: "Date Range = Q1 2026"
And I click "Run Report"
Then the report executes and returns results within 10 seconds
And results are displayed in a pivot table
And I can click "Save as Template" with a name "Q1 Channel Mix Report"
Scenario: Share custom report with Finance Manager
Given I saved template "Q1 Channel Mix Report"
When I click "Share" and select Finance Manager role
Then Finance Managers can see and run "Q1 Channel Mix Report" from their report catalog
US-012-20: Scheduled Report Delivery
As a Finance Manager, I want to schedule the P&L report to run on the 1st of every month and email it to me and the owner, So that I don't have to manually run and email the report each month.
Priority: P1 | Points: 5 | Sprint: 4 | Status: Not Started
Implementation Notes
POST /reports/schedule—{ reportId, cron: "0 8 1 * *", format: "PDF", recipients: ["cfo@hotel.com"] }- Uses NestJS
@Cron()or BullMQ delayed jobs (EP-014 BullMQ dependency) - On execution: runs report query, generates PDF/Excel, sends via notification service (EP-014)
- On failure: retry once after 15 minutes, then send failure alert to admin
Acceptance Criteria
Scenario: Schedule monthly P&L email
Given the P&L report template is configured
When I create a schedule: cron="1st of month at 08:00 IST", format=PDF, recipients=["gm@hotel.com", "owner@hotel.com"]
Then on 2026-04-01 at 08:00 IST, the system runs the P&L report for March 2026
And emails a PDF attachment to both recipients with subject "HMP Report: P&L March 2026"
And the run is logged in report history with status "DELIVERED"
Scenario: Scheduled report delivery failure
Given a scheduled report fails to generate (database error)
Then a retry is attempted after 15 minutes
If the retry also fails
Then an alert email is sent to the Super Admin: "Scheduled report failed: P&L March 2026"
US-012D-01: Custom Report Query Engine
As a System, I need to execute custom report queries built by users against the analytics database, So that the custom report builder can return real data.
Priority: P1 | Points: 13 | Sprint: 4 | Status: Not Started
Implementation Notes
POST /reports/custom— parses the report definition (dimensions, measures, filters) into a safe parameterized SQL query- Query builder: maps dimension/measure names to table columns; never exposes raw SQL to user (injection prevention)
- Allowed sources:
reservations,folio_charges,folio_payments,housekeeping_tasks,pos_orders,guests - Cross-tenant isolation enforced: all generated queries include
WHERE tenant_id = $1 - Query timeout: 30 seconds; returns 429 with "Query too large" if estimated rows > 100,000
- Results paginated: max 1,000 rows per page; export can generate full dataset
Acceptance Criteria
Scenario: Valid custom query executes safely
Given a report definition: source=reservations, dimensions=[booking_source, month], measures=[count, total_revenue]
When the query engine processes the definition
Then the generated SQL is parameterized with tenant_id and date range
And results return within 10 seconds for < 50,000 rows
And no raw SQL from the user definition is executed
Scenario: Query injection attempt blocked
Given a malicious dimension name: "booking_source; DROP TABLE reservations"
When the query engine parses the definition
Then the system rejects with 400 Bad Request: "Invalid dimension: 'booking_source; DROP TABLE reservations'"
And no SQL is executed
US-012D-03: PDF Export with Hotel Letterhead
As a Finance Manager, I want to export any report as a PDF with the hotel's name, logo, and proper formatting, So that I can share professional-looking reports with owners and auditors.
Priority: P2 | Points: 5 | Sprint: 4 | Status: Partial
Implementation Status
PDF export functionality exists but generates plain PDFs without hotel branding. Headers, footers, page numbers, and logo are missing.
Acceptance Criteria
Scenario: Export revenue report as branded PDF
Given the hotel has uploaded a logo in property settings
When I export the Revenue Report as PDF
Then the PDF includes:
- Hotel name and logo in the page header
- Report title, period, and "Generated by HMP" in the sub-header
- Footer: "Page X of Y | Confidential | Hotel Sunrise, Jaipur | GSTIN: 08AAACH8719R1ZH"
- All INR amounts with comma separators (e.g., ₹48,75,000)
- Table borders and alternating row shading for readability
Scenario: Multi-page report
Given a report with 200 rows
When exported as PDF
Then each page has the header and footer
And the table header row repeats on each page
8. Dependencies
| Dependency | Type | Epic / Service | Status |
|---|---|---|---|
| Analytics DB schema (query targets) | Internal | analytics-service / Prisma | Available |
| Notification service (scheduled email) | Downstream | EP-014 / notification | Available |
| BullMQ (scheduled job execution) | Infra | Docker / Redis | Partial |
| Auth / RBAC (report permission control) | Upstream | EP-001 / auth | Available |
| Report data sources (reservations, folios) | Upstream | EP-005B, EP-003 | Available |
9. Acceptance Criteria (Sub-Epic Level)
- AC-EP-012D-01: Custom report builder executes queries against the analytics database and returns results within 10 seconds for datasets up to 50,000 rows.
- AC-EP-012D-02: All user-supplied report definitions are sanitized; no raw SQL injection is possible.
- AC-EP-012D-03: Scheduled reports run at the configured time, generate the report, and email it to recipients.
- AC-EP-012D-04: PDF exports include hotel letterhead, report metadata, page numbers, and properly formatted INR amounts.
- AC-EP-012D-05: Excel exports generate valid .xlsx files with a summary tab.
- AC-EP-012D-06: Role-based access control prevents Finance-only reports from being visible to Housekeeping roles.
- AC-EP-012D-07: Cross-tenant isolation is enforced at the query engine level —
tenant_idfilter is always applied.
10. Technical Notes
- Reports controller:
01-hmp-backend/apps/analytics-service/src/reports/reports.controller.ts - Query engine: New
CustomReportModulein analytics-service — implement as aReportQueryBuilderclass that maps dimension/measure names to SQL fragments; uses Prisma raw query with parameterized values - PDF generation: analytics-service should use
pdfmake(preferred for server-side) with a hotel branding template; logo stored in S3 / property config - Excel export:
exceljslibrary —Workbook.xlsx.writeBuffer()for streaming download - Scheduler: NestJS
SchedulerRegistry+@Cron()for fixed schedules; BullMQ delayed jobs for one-off scheduled runs. BullMQ is the Sprint 6 Guest Communications deliverable — this can piggyback on that infrastructure - Report catalog:
GET /reports/catalogalready built; extend to includerequiredRoles[]field per report entry; enforce inReportsGuard