PRD: Revenue report by time range
| Module | Reports (CORE-11) | PRD ID | PRD-SLS-002 |
| Status | Proposed | Owner | Reports squad |
| Date | 2026-06-05 | Version | v0.1 |
| Packages | @nx/sale · apps/client · apps/bo | URD | SLS · ACC |
TL;DR
Enables flexible aggregation and grouping of revenue data by Day, Month, or Year cycles over any selected date range, supporting interactive charts and detailed statistical data tables.
1. Context & Problem
In business operations, store owners and managers need to analyze revenue performance under various time intervals (e.g., monthly or quarterly) rather than just daily flat lists (PRD-SLS-001) in order to make strategic financial and marketing planning decisions.
Currently, the system only supports viewing sales aggregates grouped by individual calendar days within a time range. When users request reports over longer periods (e.g., 6 months, 1 year, or multiple years), rendering detailed daily rows overflows the data table, and chart markers become too dense to read, leading to information overload.
The system needs to provide a flexible database aggregation mechanism and custom interface options (Day, Month, Year grouping) to serve this macro-level analytics requirement.
2. Goals & Non-Goals
Goals
- Flexible Date Range Filtering: Support quick selection of common periods (Today, Yesterday, This Week, Last Week, This Month, Last Month, This Quarter, This Year, Last Year) or custom date ranges.
- Selectable Grouping (Group By): Allow users to toggle grouping dynamically between Day, Month, or Year.
- Interactive Chart Visualization: Display a bar or line chart depicting net revenue and order count trends matching the selected group interval.
- Detailed Data Table: Display a structured statistical table matching the grouping cycles (e.g., if grouped by Month, each row represents a month in the date range) consisting of gross revenue, deductions/discounts, tax, net revenue, and order count, with a sticky totals row at the bottom.
- Security & Performance Enforcements: Scoped strictly to the requesting user's Merchant ID (
x-merchant-id), aggregate only completed orders (status = 'completed'), and support correct timezone boundaries (GMT+7).
Non-Goals
- Detailed cost and profit margin breakdowns (owned by advanced analytics
ADV). - Exporting reports to Excel/PDF formats (owned by advanced analytics
ADV).
3. Success Metrics
| Metric | Target / signal |
|---|---|
| Data accuracy | 100% of revenue figures grouped by Month or Year match the cumulative sum of the daily reports within the same range |
| Response time | Aggregation API resolves in under 500ms for date ranges encompassing up to 2 years of order history |
| UI Experience | Recharts dynamically configures X-axis interval spacing to prevent overlapping text markers based on the results count |
4. Personas & Use Cases
| Persona | Goal in this feature |
|---|---|
| Owner | View the current year's revenue grouped by Month to analyze high/low seasons and compare monthly performances |
| Manager | View the current quarter's revenue grouped by Day/Month to reconcile figures and draft periodic reports |
Core scenarios: User accesses the Reporting Dashboard -> Selects the filter range (e.g., last 12 months) -> Toggles grouping to Month -> The system sends an API aggregation request -> The client renders a trend chart of the 12 months and a detailed data table showing 12 corresponding rows with a summary row at the bottom.
5. User Stories
- As an owner, I want to see revenue summaries grouped by month so that I can easily identify seasonal business trends.
- As a manager, I want to toggle the aggregation grouping interval between day, month, or year depending on the active date range, so that I am not overwhelmed by unnecessary granular details.
- As a developer, I want the database to perform aggregation directly rather than loading raw orders into memory, to reduce bandwidth consumption and page load latency.
6. Functional Requirements
| # | Requirement | URD ref |
|---|---|---|
| FR-1 | Date range picker on UI supports Quick Presets and Custom Range | URD-ACC-002 |
| FR-2 | User can toggle the Group By interval (day, month, year) on the UI | URD-SLS-006 |
| FR-3 | API GET /v1/api/sale/reports/revenue-period supports query parameters startDate, endDate, and groupBy | URD-SLS-006 |
| FR-4 | API response returns aggregated cumulative summary and period details based on the groupBy parameter | URD-SLS-006 |
| FR-5 | Calculations aggregate only completed orders (status = 'completed') of the requested merchant | URD-ACC-001, URD-ACC-003 |
| FR-6 | Dashboard interface updates charts (bar/line) and data table according to the chosen group interval | URD-SLS-006 |
| FR-7 | Empty result sets render cumulative zero totals instead of throwing errors | URD-ACC-004 |
Business Rules
- BR-01: Revenue is recognized by timestamp/time of completion.
- BR-02: Only sales orders in
completedstatus are aggregated. - BR-03: Cycles with no data must still be displayed with a value of 0.
- BR-04: Data must be sorted in ascending order of time.
7. Non-Functional Requirements
| Area | Requirement |
|---|---|
| Tenancy & Security | Scope every query strictly to the user's active Merchant ID; enforce cross-merchant isolation at the database layer |
| Performance | Database queries use indices on merchant_id, status, and completed_at (or created_at) columns to prevent table scans |
| i18n | Time-period labels automatically localize based on the user's active language preference (e.g., "Tháng 01/2026" vs "January 2026") |
| Timezone | Handle GMT+7 timezone offsets uniformly on the server to ensure correct boundary aggregation |
8. UX & Flows
9. Data & Domain
| Entity | Role |
|---|---|
SaleOrder | Main order entity used to aggregate revenue totals (grossAmount, discountAmount, taxAmount, netAmount, orderCount) based on the completion date (completedAt) |
10. Dependencies & Assumptions
Dependencies
- Orders (CORE-07) - Completed orders are the single source of truth for revenue calculations.
- Identity & Authz (CORE-02) - Resolves the active Merchant ID from the Request Context.
Assumptions
- The system defaults to Vietnam timezone (GMT+7) for daily/monthly/yearly groupings.
11. Risks & Open Questions
| Risk / open question | Mitigation / status |
|---|---|
| Query latency over large historical order datasets | Ensure appropriate index configurations are set on merchant_id + status + completed_at to avoid table scans |
| Overlapping text labels on Recharts graph | Client dynamically calculates and offsets tick display intervals on the X-axis based on result density |
12. Release Plan & Launch Criteria
| Aspect | Kế hoạch |
|---|---|
| Phase | SLS P2 increment - periodic revenue reports |
| Rollout | Available to all merchants |
| Migration | No database migrations required (only read-side aggregation queries over existing tables) |
| Launch criteria | Drizzle queries resolve all groupBy intervals correctly; charts and tables update smoothly; zero lint errors |
13. FAQ
Are draft or cancelled orders included in the calculations? No, only sales orders in completed status are aggregated.
What happens if there are no sales in the selected range? The system returns zero totals and renders an empty chart, without throwing errors.
References
- URD: Reports - Sales Reports · Access & Scoping
- Module: Reports - overview + traceability
- Developer: @nx/sale