Reporting System¶

The reporting system provides professional, configurable reports with predefined templates, a visual query builder, export to multiple formats, scheduling, and sharing.

Architecture¶

┌──────────────────┐     ┌──────────────────┐     ┌──────────────────┐
│   Admin UI       │     │   API Server     │     │   Database       │
│   (Angular)      │────▶│   (Fastify)      │────▶│   (PostgreSQL)   │
│                  │     │                  │     │                  │
│  Report Catalog  │     │  Template Routes │     │  report_template │
│  Report Viewer   │     │  Saved Routes    │     │  user_fav_tmpl   │
│  Query Builder   │     │  Share Routes    │     │  saved_report    │
│  Saved Reports   │     │  Schedule Routes │     │  report_share    │
│  Style Editor    │     │  Export Service  │     │  report_schedule │
└──────────────────┘     │  Report Engine   │     │  report_exec_log │
                         │  Query Builder   │     └──────────────────┘
                         │  Schedule Worker │──▶ BullMQ / Email
                         └──────────────────┘

Concepts¶

Templates¶

Report templates define a query configuration with parameters. The system ships with 16 predefined templates. Each template has tags for filtering (e.g. compliance, audit, access-review). Users can favorite templates for quick access.

Saved Reports (Snapshots)¶

When a user runs a report, they can click "Save Results" to create a point-in-time snapshot. Snapshots store the actual result data (columns and rows), not just the query spec. Snapshots can be shared with other users.

Scheduling¶

Schedules are attached to templates (not snapshots). A schedule runs a template with predefined parameters on a cron schedule and emails the exported results to recipients.

Features¶

Feature	Description
Predefined templates	16 system templates across workflow, approval, compliance, and access categories
Tags & favorites	Tag-based filtering and per-user favorites on the catalog
Visual query builder	Select data sources, columns, joins, and filters without writing SQL
Configurable styling	Headers, footers, colors, margins, orientation, striped/bordered tables
Multi-format export	PDF (Puppeteer), Excel (ExcelJS), CSV, and Markdown
Saved snapshots	Save report output as point-in-time snapshots
Sharing	Share snapshots with users, roles, or groups
Scheduling	Cron-based recurring template execution and email delivery
Execution log	Track who ran what, when, and how long it took

Permissions¶

Permission	Description
`report:read`	Browse templates, run reports, view data sources, toggle favorites
`report:write`	Save snapshots, share reports
`report:export`	Export reports to PDF, Excel, CSV, Markdown
`report:admin`	View execution log, manage schedules

These permissions are assigned to the admin and resource_manager roles by default.

Data Sources¶

Reports query live data through 8 registered data sources:

Data Source	Description	Key Joins
`workflow_run`	Run instances with status and timing	`workflow_definition`, `user`
`workflow_step`	Step execution with assignees and SLA	`workflow_run`
`approval`	Approval decisions and response times	`user`, `workflow_run`
`audit_log`	Complete system audit trail	—
`role_assignment`	User role grants with expiration	`role_definition`, `user`, `grantor`
`entitlement_instance`	Provisioned entitlements and reconciliation	`entitlement_definition`, `connector_definition`, `user`
`user`	User directory	—
`document`	Document submissions	`user`, `workflow_run`

Predefined Templates¶

Workflow¶

Template	Tags	Description
Workflow Run Summary	`workflow`, `operations`	Runs by status, workflow, and date range
Step Performance	`workflow`, `performance`	Step durations and SLA compliance
Pending Tasks	`workflow`, `operations`, `tasks`	Open tasks with age and assignee
SLA Breach Report	`compliance`, `sla`, `operations`	Overdue steps with breach duration

Approval¶

Template	Tags	Description
Approval Activity	`approval`, `operations`	Decisions by approver with response times

Compliance¶

Template	Tags	Description
Access by Resource	`compliance`, `access-review`, `audit`	Who has access to a specific role/entitlement
Access Changes	`compliance`, `audit`, `access-review`	Who gained or lost access in a time period
Privileged Access Review	`compliance`, `access-review`, `privileged-access`	Users holding high-privilege roles
Expiring Access	`compliance`, `access-review`, `expiration`	Assignments expiring within a configurable window
Orphaned Entitlements	`compliance`, `inventory`, `reconciliation`	Entitlements flagged during reconciliation
Access Audit Trail	`compliance`, `audit`, `access-review`	Role and entitlement lifecycle events
User Access Summary	`compliance`, `access-review`, `user-profile`	Complete access profile for a single user
Audit Trail	`compliance`, `audit`	General filterable audit log
Document Submissions	`compliance`, `documents`	Documents submitted through workflows

Access¶

Template	Tags	Description
Role Assignment Report	`access`, `inventory`	Current role assignments with expiration
Entitlement Inventory	`access`, `inventory`, `compliance`	Provisioned entitlements by user and connector

Query Builder¶

The query builder allows users to create custom reports without predefined templates:

Select a data source — Choose from the 8 available sources
Select columns — Pick which columns to include; cross-table columns automatically add joins
Add filters — Optional filters with operators (equals, contains, gt, between, is_null, etc.)
Preview results — Execute the query and inspect data
Save results — Snapshot the output for later viewing and sharing

Security¶

The query builder prevents SQL injection by: - Validating all columns against registered data source definitions - Sanitizing identifiers (only [a-zA-Z0-9_.] allowed) - Using Kysely's parameterized query builder - Restricting joins to pre-declared available joins on each data source - Whitelisting allowed table names

Export Formats¶

Format	Library	Features
PDF	Puppeteer + Handlebars	Full styling, headers/footers, page numbers, configurable orientation/margins
Excel	ExcelJS	Styled headers, auto-width columns, frozen header row
CSV	Built-in	RFC-compliant escaping, UTF-8
Markdown	Built-in	GitHub-flavored table syntax

Scheduling¶

Report schedules are attached to templates and use cron expressions processed by BullMQ:

POST /api/reports/schedules
{
  "templateId": "tmpl-access-by-resource",
  "cronExpression": "0 8 * * 1",
  "exportFormat": "pdf",
  "recipients": ["alice@example.com", "bob@example.com"],
  "parameters": { "roleDefinitionId": "admin" }
}

The schedule worker loads the template, executes it with the stored parameters, exports the results, and emails attachments to all recipients.

Common cron presets available in the UI: - Daily at 8am / 6pm - Weekly on Monday / Friday - Monthly on the 1st / 15th - Quarterly

Database Schema¶

Seven tables support the reporting system:

report_template — System and user-defined templates with query configs and tags
user_favorite_template — Per-user template favorites (composite PK: user_id + template_id)
saved_report — Point-in-time output snapshots (result columns, data, row count)
report_share — Sharing rules for snapshots (user/role/group targeting)
report_schedule — Cron-based template delivery schedules with parameters
report_execution_log — Audit trail of all report executions

Migrations: 035_reporting, 036_grant_report_permissions, 037_favorites_tags_snapshots

API Reference¶

Templates¶

Method	Path	Permission	Description
`GET`	`/api/reports/templates`	`report:read`	List templates (`?category=`, `?tags=`, `?favorite=true`)
`GET`	`/api/reports/templates/:id`	`report:read`	Get template details (includes `isFavorite`, `tags`)
`POST`	`/api/reports/templates/:id/favorite`	`report:read`	Toggle favorite (returns `{ isFavorite }`)
`GET`	`/api/reports/data-sources`	`report:read`	List available data sources

Execution & Export¶

Method	Path	Permission	Description
`POST`	`/api/reports/execute`	`report:read`	Execute a report (template or custom query)
`POST`	`/api/reports/export/:format`	`report:export`	Export to pdf, excel, csv, or markdown
`GET`	`/api/reports/execution-log`	`report:admin`	List execution history

Saved Reports (Snapshots)¶

Method	Path	Permission	Description
`GET`	`/api/reports/saved`	`report:read`	List user's saved snapshots
`POST`	`/api/reports/saved`	`report:write`	Save a report snapshot (columns, rows, totalRows)
`GET`	`/api/reports/saved/:id`	`report:read`	Get snapshot with stored result data
`DELETE`	`/api/reports/saved/:id`	`report:write`	Delete (owner or admin)

Method	Path	Permission	Description
`GET`	`/api/reports/saved/:id/shares`	`report:write`	List shares for a snapshot
`POST`	`/api/reports/saved/:id/shares`	`report:write`	Add a share (owner only)
`DELETE`	`/api/reports/saved/:id/shares/:shareId`	`report:write`	Remove a share (owner only)

Scheduling¶

Method	Path	Permission	Description
`GET`	`/api/reports/schedules`	`report:write`	List schedules (`?templateId=`)
`POST`	`/api/reports/schedules`	`report:admin`	Create a schedule (requires `templateId`)
`PUT`	`/api/reports/schedules/:id`	`report:admin`	Update a schedule
`DELETE`	`/api/reports/schedules/:id`	`report:admin`	Delete a schedule

Frontend Routes¶

Route	Component	Description
`/reports/catalog`	`ReportCatalogComponent`	Browse templates with favorites, tags, and category filters
`/reports/viewer`	`ReportViewerComponent`	Run reports, view results, save snapshots, export
`/reports/builder`	`QueryBuilderComponent`	Visual custom query builder
`/reports/saved`	`SavedReportsComponent`	View and manage saved snapshots, share

Reporting System¶

Architecture¶

Concepts¶

Templates¶

Saved Reports (Snapshots)¶

Scheduling¶

Features¶

Permissions¶

Data Sources¶

Predefined Templates¶

Workflow¶

Approval¶

Compliance¶

Access¶

Query Builder¶

Security¶

Export Formats¶

Scheduling¶

Database Schema¶

API Reference¶

Templates¶

Execution & Export¶

Saved Reports (Snapshots)¶

Sharing¶

Scheduling¶

Frontend Routes¶