[FEATURE] Reports Tab

[WimvandenHeijkant]

Problem & value — what problem, for whom, and why now?

We have a lot of valuable data in Identity Atlas. Although most of this data can be searched for and be shown it would be ideal if we had a dedicated reports tab. The reports tab should then allow users to generate a pre-defined reports. Reports like "orphaned accounts", "in active accounts" and many more. Create a pluggable report framework where people can add report templates with everything from the select statement.. as well as the look and feel. A simple list.. or a pi-chart or a matrix.. a combination of both.

What should it let someone do?

It should be easy to get a pre-defined report that gives them the insights you they need. Maybe download the report as PDF.. and or some other way to share. Also it should be easy to update the contents of the report based on the latest data.

✅ I'm delighted when…

• There is a really simple way to get to templated reports

❌ I'm disappointed when…

• Disappointed when it is hard to add new report templates
• Disappointed when it hard to find the report you are looking for

How general should this be?

A general capability (this case is only the first example)

How complete? (optional)

Start with 1 or 2 default reports. Allow us to extend from there.

What it deliberately does NOT do (optional)

No response

Where should it appear? (optional)

No response

Related issues / dependencies (optional)

No response

Screenshots / examples (optional)

No response

What's driving this now? (optional)

None

[github-actions]

Hi @WimvandenHeijkant — I ran the DoR review (interview gap-check + build-readiness probe against the live code and schema) on the Reports Tab. The good news: the data for both of your example reports already exists in the model, and the codebase already has a proven pattern for exactly the kind of pluggability you're asking for. There is one scope-ambition fork only you can choose (question 1 below — it defines what this feature is), plus two smaller scope pins. Everything else I've resolved to a proposed decision you can bulk-ratify in the same reply. One consolidated answer and this moves on.

What I verified in the code (so you know the proposals are grounded)

• "Orphaned accounts" is already computed today — the orphaned-accounts context plugin (app/api/src/contexts/plugins/orphaned-accounts.js) emits a generated Context of all Principals with no IdentityMembers link (excluding service principals / managed identities / AI agents), refreshed by every Account Linking run.
• "Inactive accounts" is buildable on real data — the PrincipalActivity table (migration 017_principal_activity.sql) stores lastSignInDateTime / lastSuccessfulSignInDateTime per principal, and the risk-scoring engine already flags "stale account" at >90 days (app/api/src/riskscoring/engine.js).
• The pluggable pattern exists: context algorithms, crawler manifests, and crawler UI wizards are all code-registered plugin registries — a new one drops in as a file + a registry entry, no core changes. A report-template registry would be the fourth instance of a proven pattern.
• Adding the tab itself is trivial — one entry in app/ui/src/pageRegistry.jsx.
• Export today: Excel export utilities and a Power Query workbook exist; there is no PDF capability anywhere in the stack, and no charting library (the dashboard's charts are deliberately hand-rolled SVG).

Questions only you can answer

1. Who adds report templates — and how? (This is the big one.)
Your text says "people can add report templates with everything from the select statement.. as well as the look and feel." That reads two very different ways:

• (a) Developer-authored, code-registered templates — templates are files in the repo (SQL + renderer spec + tests), shipped with the product; anyone can contribute one via a small PR. "Easy to add" = drop one file. This matches the existing plugin architecture, is safe, and is buildable now.
• (b) End-user/admin-authored templates in the UI — an admin types a SELECT statement and picks a visualization inside Identity Atlas. This is a far bigger and riskier build: executing user-entered SQL against the identity database — which also holds encrypted crawler credentials and API-key tables — requires a sandboxed read-only database role, a table allowlist, query timeouts, and its own security review. As the security lens on an authorization product, I'd want that as a deliberately designed phase 2, not a v1 side effect.

My recommendation: (a) for v1, with (b) recorded as an explicit phase-2 candidate. But this is your ambition call, not mine to default — please choose.

2. Sharing/export — pin the "maybe PDF".
Proposal to ratify: v1 ships a print-optimized report view (browser Print → PDF gives every user a PDF with zero new dependencies) plus CSV/Excel download per report (reusing the existing export utilities). Dedicated server-side pixel-perfect PDF rendering is out of scope for v1. OK, or is a "real" generated PDF a hard requirement?

3. The two launch reports — ratify the exact definitions.

• Orphaned accounts = the same definition the existing context plugin uses (Principals with no Identity link, excluding SP/MI/AI-agent), so the report and the Contexts surface can never disagree.
• Inactive accounts = enabled user principals whose last sign-in is older than N days or who have never signed in (shown as "never"), with N a report parameter defaulting to 90 — the same threshold the risk engine uses. OK?

Proposed decisions — ratify in bulk (or object to specific ones)

1. Registry framework: a server-side report-template registry mirroring the context-plugin registry — each template declares name, description, category, a typed parameter schema, a parameterized SQL query, and a renderer spec. Never string-interpolated SQL.
2. Always-fresh data: reports run live against the database when opened (with a Refresh button) — your "update the contents based on the latest data" requirement is then automatic, no snapshot/staleness machinery.
3. Renderers: the renderer abstraction (list / chart / matrix / combinations) is part of the template contract from day one, but v1 implements the table renderer plus summary stat tiles. Pie/matrix renderers land with the first template that needs them — which also defers the chart-library-vs-hand-rolled-SVG decision to the architect at the moment it's actually needed.
4. Surface: a new top-level Reports tab (pageRegistry entry) backed by a new documented /api/reports router (list templates, run template); dark mode + WCAG AA from the start, per house rules.
5. Findability (your second "disappointed"): v1 is a card list with search and category grouping — thin at 2 reports, but the template metadata (category, description) is there so the catalog scales without rework.
6. Empty results are a first-class state: "0 orphaned accounts" renders as an explicit healthy/no-findings message, never a blank page.

Backlog context

• [Feature] export for all object types #84 (export for all object types) overlaps on the sharing angle — I've scoped export here to per-report download so the two don't collide.
• [FEATURE] matrix to see which access packages a certain group of users have #931 (access-packages-per-user-group matrix) is a natural future report template — a point in favour of the general framework you chose.
• Overlap guard: orphaned accounts (Contexts) and stale accounts (Risk Scoring) already exist as surfaces. The reports will read the same definitions, not fork them — one concept, one computation.
• [Refactor] Re-architect risk scoring into a plugin tier (findings + explainability + override) #672 (risk-scoring plugin tier) — risk findings are an obvious later source of report templates.

What happens next

Once you answer 1–3 (and ratify or amend the decisions), this feature is too large for one PR and will route to decompose — the likely slices: ① framework + API + Reports tab + table renderer + the two launch reports; ② CSV/Excel download + print view; ③ chart renderers with their first template. Each independently buildable.

Route: state:awaiting-requestor — one reply from you unblocks this.

[WimvandenHeijkant]

1. Who adds report templates — and how? (This is the big one.)
   A.. developer for now.. we can make this more friendly later.. if we need to.

2. Print to PDF is fine.. excel sound like a good solution for now. CSV does not.. because I want to reports to have colors, Pi Charts.. a good looking report.. not just a simple CSV.

3. let's start with the following reports

• orphaned accounts (as you described.. without identity link)
• AI agents without an owner

[github-actions]

Thanks @WimvandenHeijkant — that answered everything. I re-ran the build-readiness probe against the live code and schema for your changed launch-report list and the Excel/no-CSV requirement. No open questions remain; the spec below is pinned. The feature spans a new API router, a plugin registry, a new UI tab, two renderers, a print view, and a styled Excel exporter — too large for one PR, so it now routes to decompose with the slice plan at the bottom.

Your answers, recorded

1. Ambition (pinned): report templates are developer-authored, code-registered files shipped with the product (option a). UI-authored templates by admins are recorded as an explicit phase-2 candidate, deliberately out of scope now.
2. Sharing (pinned): on-screen report with colors and charts, browser Print → PDF for a shareable copy, and a styled Excel download per report. CSV is explicitly out — rejected because it can't carry the visual quality you want.
3. Launch reports (pinned): ① Orphaned accounts, ② AI agents without an owner (replacing the earlier "inactive accounts" candidate).

Probe results on the new pieces

"AI agents without an owner" is fully buildable on real storage — verified:

• AI agents are first-class principals: principalType='AIAgent' is in the accepted type list (app/api/src/ingest/validation.js).
• Agent ownership is first-class too: the PrincipalRelationships table (migration 057_principal_relationships.sql) stores exactly this — an agent's owners are rows WHERE principalId=<agent> AND relationshipType='Owner'. The Entra crawler has a dedicated phase (EntraIDCrawler.PrincipalRelationships.ps1) and the ingest API a dedicated surface (principal-relationships) feeding it.
• So the report is a clean anti-join: AI-agent principals with zero Owner rows in PrincipalRelationships. No new schema, no derived client-side logic — the report reads the same model the relations tab reads.
• Demo-data note for the build step: the demo dataset contains an AI agent ("Copilot Assistant") but seeds no owner links, so today every demo agent is unowned. Slice ① tops up the demo dataset with one owned agent so functional validation shows both sides of the report.

Excel with "colors and pie charts" — one proposed decision to record (the only new one):
The API already ships ExcelJS (it builds the styled Power Query workbook — header fills, fonts, colors). ExcelJS can style richly but cannot embed native Excel charts. So: the Excel download is a styled, color-formatted workbook (colored headers, summary sheet with the key numbers, data sheet); the pie charts and full visual layout live in the on-screen report and its print/PDF view, which is also the artifact you'd share for looks. Building native in-file Excel charts would need a new library or hand-built OOXML and isn't warranted for v1. This is recorded in the decisions register — object there if a chart inside the .xlsx is a hard requirement, otherwise no reply needed.

Final decisions register (consolidated; earlier ratified decisions carried forward)

#	Decision	Owner
1	Server-side report-template registry mirroring the context-plugin registry: each template is one file declaring name, description, category, typed parameter schema, parameterized SQL (never string-interpolated), and a renderer spec. Fourth instance of the proven plugin pattern.	Ratified
2	Reports run live on open, with a Refresh button — "latest data" is automatic, no snapshots.	Ratified
3	Revised per your answer 2: v1 renderers = data table + summary stat tiles + pie/donut chart, all implemented — the pie renderer is no longer deferred, since a good-looking visual report is now an explicit v1 requirement. Charts are hand-rolled SVG per the existing house pattern (TimeSeriesChart.jsx); no chart library dependency.	Revised → ratify
4	New top-level Reports tab (pageRegistry.jsx entry) + documented /api/reports router (list templates / run template); dark mode + WCAG AA from the start.	Ratified
5	Findability: card list with search + category grouping, driven by template metadata.	Ratified
6	Empty result = explicit first-class "healthy / no findings" state, never a blank page.	Ratified
7	Orphaned accounts definition = the existing context plugin's definition verbatim (Principals with no IdentityMembers link, excluding ServicePrincipal/ManagedIdentity/AIAgent), sub-grouped by the same detected account type (Admin/Guest/Service/Shared/Secondary) — the pie shows orphans by detected type. One concept, one computation.	Ratified
8	AI agents without an owner definition = principals principalType='AIAgent' with zero PrincipalRelationships Owner rows; all agents included regardless of enabled state, with an enabled/disabled column, and the pie showing owned vs. unowned agents.	Proposed → ratify
9	Excel download = styled color workbook via ExcelJS (no embedded native charts); visual/chart fidelity lives in the on-screen + print/PDF view.	Proposed → ratify
10	CSV export: out. UI-authored templates: phase 2. Server-side pixel-perfect PDF rendering: out (browser print covers it).	Ratified

Acceptance criteria (fixture-backed, per report)

• Orphaned accounts: on the demo dataset, the report lists exactly the principals the orphaned-accounts context plugin emits (same rows, same count), grouped by detected type; the stat tiles and pie total equal the row count. Zero orphans → healthy-state message.
• AI agents without owner: demo dataset topped up to ≥1 owned + ≥1 unowned agent → report lists only the unowned one(s); the owned agent appears in the pie's "owned" slice but not in the table. Zero agents in the tenant → healthy-state message (not an error).
• Framework: a template file added to the registry appears in the tab (card, search, category) with no core-code change; a template whose query fails renders an error state, not a blank page.
• Exports: Excel download opens in Excel with styled headers + summary sheet matching on-screen numbers; print view produces a paginated, chart-inclusive PDF via browser print, in both light and dark app themes (print always light).

Decomposition — three independently buildable slices

1. Framework + first reports (the core slice): template registry, /api/reports router (list + run, documented in openapi.yaml), Reports tab with card list/search/categories, table renderer + stat tiles, both launch reports, empty/error states, demo-data top-up (owned AI agent), contract + unit tests, docs + changelog.
2. Visual layer: pie/donut SVG renderer (house pattern, dark-mode + WCAG AA) wired into both launch reports, plus the print-optimized view (Print → PDF).
3. Excel download: per-report styled ExcelJS workbook endpoint + download button.

Order: ① first; ② and ③ are independent of each other on top of ①. Backlog fit unchanged: #84 (generic export) stays separate from per-report download; #931's access-package matrix is a natural future template; risk findings (#672) likewise.

Next step: the split above becomes child issues; slice ① carries the framework spec and goes first.

Route: state:decompose — no further input needed from you; objections to decisions 8–9 can be dropped here anytime before slice ① builds.