Reporting & Dashboards¶

SCOPE_ITEM: Data visualisation, reporting, and export capabilities for internal tools. Covers real-time dashboards, chart libraries, data export, and scheduled report delivery.

Decision Tree¶

IF: Client needs a single overview page with KPIs. THEN: Implement fixed dashboard with configurable date range.

IF: Client needs multiple dashboards for different roles. THEN: Implement role-based dashboard with widget selection.

IF: Client needs self-service reporting (build own charts). THEN: Include chart builder module (higher complexity).

IF: Client needs automated report delivery via email. THEN: Include scheduled reports module.

Dashboard Patterns¶

Fixed Dashboard¶

SCOPE_ITEM: Pre-designed dashboard with known widget layout.

INCLUDES: - KPI cards (numeric value + trend indicator + comparison period). - 3-5 chart widgets arranged in responsive grid. - Global date range filter (today, 7d, 30d, 90d, custom). - Manual refresh button. - Loading skeletons for perceived performance.

OPTIONAL: - Auto-refresh interval (30s, 1m, 5m — user selectable). - Fullscreen mode for wall-mounted displays.

Role-Based Dashboard¶

SCOPE_ITEM: Different dashboard views per user role.

INCLUDES: - Dashboard template per role (admin, manager, team member). - Widget visibility tied to role permissions. - Shared filter context across widgets. - Data scoping (managers see team data, users see own data).

OPTIONAL: - Per-user widget rearrangement (drag-and-drop, persisted). - Pin/unpin widgets. - Dashboard sharing (save as link, embed in other tools).

Widget	Data Type	Implementation
KPI Card	Single metric	Server component, cached query
Bar Chart	Categorical comparison	Recharts `<BarChart>`
Line Chart	Time series	Recharts `<LineChart>`
Area Chart	Cumulative time series	Recharts `<AreaChart>`
Pie / Donut	Proportional breakdown	Recharts `<PieChart>`
Data Table	Tabular listing	TanStack Table with sorting/pagination
Progress Bar	Percentage complete	Custom component
Status List	Item status overview	Custom component

Chart Library¶

SCOPE_ITEM: Standardised chart components for consistent visualisation.

Recommended: Recharts¶

INCLUDES: - React-native charting library (composable components). - Responsive containers (<ResponsiveContainer>). - Tooltips, legends, axis labels. - Colour palette aligned with client branding.

CHECK: Recharts renders SVG — performant up to ~5000 data points per chart. IF: >5000 data points. THEN: Aggregate server-side before rendering. IF: >50000 data points. THEN: Evaluate Canvas-based library (e.g., ECharts).

Chart Design Standards¶

INCLUDES: - Consistent colour palette (max 8 colours, accessible contrast). - Number formatting (locale-aware, thousands separator, decimal places). - Date formatting (ISO 8601 stored, locale-aware display). - Empty state handling ("No data for selected period"). - Error state handling ("Failed to load — Retry" button). - Loading state (skeleton matching chart dimensions).

OPTIONAL: - Dark mode support. - Print-friendly chart rendering (white background, no animations). - Chart annotation (add notes to specific data points).

Data Export¶

CSV Export¶

SCOPE_ITEM: Export tabular data as CSV.

INCLUDES: - Export current filtered/sorted view. - Column headers from display labels. - UTF-8 encoding with BOM (Excel compatibility). - Date values in ISO 8601 format. - Server-side generation for large datasets.

CHECK: Export must respect user's data access scope. CHECK: Maximum export size: 100k rows via UI, larger via scheduled report.

PDF Export¶

OPTIONAL: SCOPE_ITEM: Formatted report export as PDF.

INCLUDES: - Report template with header (logo, title, date range, generated by). - Table rendering with pagination. - Chart rendering as embedded images (server-side via Puppeteer or @react-pdf/renderer). - Footer with page numbers and generation timestamp.

CHECK: PDF generation is CPU-intensive — queue via BullMQ, not inline. CHECK: PDF must be generated server-side (not client-side print-to-PDF).

Excel Export (XLSX)¶

OPTIONAL: SCOPE_ITEM: Export with Excel formatting and multiple sheets.

INCLUDES: - Multi-sheet workbook (summary + detail). - Column type formatting (numbers, dates, currency). - Auto-filter headers. - Implemented with exceljs or xlsx library.

CHECK: XLSX generation is memory-intensive — stream for large datasets.

Scheduled Reports¶

SCOPE_ITEM: Automated report generation and delivery on a schedule.

Schedule Configuration¶

INCLUDES: - Schedule frequency: daily, weekly (pick day), monthly (pick date). - Report time: configurable (default: 07:00 CET). - Recipient list: individual users or role groups. - Report format: CSV attachment or inline HTML summary. - Enable/disable toggle per schedule.

OPTIONAL: - Custom cron expression for advanced schedules. - Conditional sending (only send if data changed / threshold breached). - Slack / Teams channel delivery. - Multiple report formats per schedule (CSV + PDF).

Implementation¶

BullMQ repeatable job (per schedule definition)
  └── Evaluate schedule (is it time to run?)
  └── Execute report query with date range
  └── Generate output (CSV / PDF / HTML)
  └── Send via email (Brevo / Mailjet API preferred. Resend / Postmark secondary — US-based)
  └── Log delivery status

INCLUDES: - Delivery log (sent_at, recipient, status, error). - Retry on transient email failure (3 attempts, exponential backoff). - Admin UI to view delivery history.

CHECK: Scheduled report queries must use read replicas if available. CHECK: Schedule execution must be idempotent (same report not sent twice). CHECK: All scheduled reports must respect RBAC — report content scoped to the recipient's data access level, not the schedule creator's.

Data Model¶

CREATE TABLE report_schedules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  report_type TEXT NOT NULL,        -- references a known report definition
  frequency TEXT NOT NULL,           -- daily, weekly, monthly
  day_of_week INTEGER,               -- 0-6 for weekly
  day_of_month INTEGER,              -- 1-28 for monthly
  time_of_day TIME DEFAULT '07:00',
  timezone TEXT DEFAULT 'Europe/Amsterdam',
  recipients JSONB NOT NULL,         -- [{user_id, email}]
  filters JSONB DEFAULT '{}',        -- report-specific filter values
  format TEXT DEFAULT 'csv',         -- csv, pdf, html
  is_active BOOLEAN DEFAULT true,
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE report_deliveries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  schedule_id UUID REFERENCES report_schedules(id),
  status TEXT NOT NULL,              -- queued, sent, failed
  sent_at TIMESTAMPTZ,
  recipient_count INTEGER,
  error_message TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

Real-Time vs Batch Data¶

Batch (Default for Internal Tools)¶

SCOPE_ITEM: Data refreshed on demand or on short intervals.

INCLUDES: - Data queries executed on page load. - Manual refresh button. - Cache layer (5-15 minute TTL, invalidated on write). - "Last updated" timestamp displayed on dashboard.

CHECK: Acceptable staleness for most internal tools: 5-15 minutes. CHECK: Cache invalidation on write prevents stale data after user actions.

Real-Time (Ops Dashboards Only)¶

OPTIONAL: SCOPE_ITEM: Live data updates without page refresh.

IF: Dashboard is used for operations monitoring (support queue, incident tracking, production floor). THEN: Implement real-time updates.

INCLUDES: - Server-Sent Events (SSE) for one-way data push. - Event-driven updates (publish on data change, not polling). - Graceful degradation (fall back to polling if SSE disconnects). - Connection status indicator (connected / reconnecting).

CHECK: SSE preferred over WebSocket for dashboards (simpler, HTTP-native). CHECK: Rate limit update frequency (max 1 update per second per widget). CHECK: Real-time adds infrastructure complexity — only include when client explicitly needs live data updates.

Performance Optimisation¶

Query Optimisation¶

INCLUDES: - Materialised views for complex aggregations (refresh on schedule). - Partial indexes for common filter combinations. - Query timeout (10 seconds max for dashboard queries). - Connection pooling (Drizzle + pg pool, max 20 connections).

Caching Strategy¶

INCLUDES: - Per-widget cache key: report:{type}:{filters_hash}:{date_range}. - Cache storage: Redis (same instance, port 6381). - TTL: 5 minutes for dashboards, 1 hour for scheduled reports. - Cache warming: pre-compute popular reports during off-peak hours.

CHECK: Cache must be invalidated when underlying data changes. CHECK: Never cache user-specific data in shared cache — scope cache key to user's data access level.

Scoping Questions¶

CHECK: How many distinct dashboard views are needed? CHECK: What KPIs and metrics does the client track? CHECK: What is the acceptable data staleness (real-time vs batch)? CHECK: Does the client need scheduled email reports? CHECK: What export formats are required (CSV, PDF, Excel)? CHECK: How much historical data should be queryable? CHECK: Are there wall-mounted / kiosk display requirements? CHECK: Does the client need self-service chart building?