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 23 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	23 system templates across workflow, approval, compliance, access, and PAM 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 10 registered data sources:

Data Source	Description	Key Joins
workflow_run	Run instances with status and timing	workflow_definition, user, pam_session
workflow_step	Step execution with assignees and SLA	workflow_run
approval	Approval decisions and response times	user, workflow_step, workflow_run, workflow_definition
audit_log	Complete system audit trail	actor_user (joins user on actor_id for display name and email)
role_assignment	User role grants with expiration	role_definition, user, grantor, role_entitlement, entitlement_definition, connector_definition
entitlement_instance	Provisioned entitlements and reconciliation	entitlement_definition, connector_definition, user, role_assignment
user	User directory	—
document	Document submissions	user, workflow_run
pam_session	Privileged access sessions	pam_policy, user
pam_policy	PAM policies	—

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	Grant/revoke/expiry events; includes actor, previous_state / new_state; From Date defaults to a rolling 30-day window
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	Role assignments with expiration; Status defaults to Active
Entitlement Inventory	access, inventory, compliance	Entitlements by user and connector; Status defaults to Provisioned
Assigned Roles and Entitlements	access, inventory, compliance, entitlements	Role grants expanded to bundled entitlements and connector; Grant Status defaults to Active

PAM

Template	Tags	Description
Active PAM Sessions	pam, privileged-access, operations	Checked-out sessions with policy and expiry
PAM Request History	pam, privileged-access, audit	PAM workflow runs linked to sessions
Privileged Access by User	pam, privileged-access, compliance	Session counts per user
Credential Rotation Audit	pam, audit, compliance	Credential-related audit events
PAM Policy Usage	pam, privileged-access, compliance	Session counts per policy

Default Parameter Values

Every template advertises its starting filters through visible parameter inputs so users can see — and override — exactly what the report is filtering on:

• Rolling date windows — Time-bounded reports (workflow, approval, audit, PAM, etc.) use the sentinel now-30d on From Date. Both the viewer and the report engine resolve this to "30 days ago at execution time", so the picker shows the actual date the report will use.
• Status selects — Inventory reports preselect the most useful state (Role Assignment Report → Active, Entitlement Inventory → Provisioned, Assigned Roles and Entitlements → Active). Pick All to drop the filter.
• All-time reports — Pending Approvals and the Granted After field on Assigned Roles and Entitlements intentionally leave date filters blank to show the full history.

Custom queries created in the Query Builder do not inherit these defaults; they apply only to executions of predefined templates and saved snapshots derived from them.

Query Builder

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

1. Select a data source — Choose from the 10 available sources
2. Select columns — Pick which columns to include; cross-table columns automatically add joins
3. Add filters — Optional filters with operators (equals, contains, gt, between, is_null, etc.)
4. Preview results — Execute the query and inspect data
5. 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)

Sharing

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