TL;DR — Multi-tenant reporting needs two surfaces — per-tenant dashboards visible to the tenant, and cross-tenant analytics visible only to the platform operator. Both run on the same data layer; tenant isolation is enforced at the query layer, not by reporting tools or conventions.
Per-tenant dashboards are easy. Cross-tenant analytics are easy. Combining both safely on one data layer is where teams get hurt. Nexia Academy‘s reporting layer has both, and the way they coexist is the most-asked-about part of the platform.
Two surfaces, one data layer
- Tenant-facing dashboards — every query is scoped to a single tenant. The dashboard tools never receive a tenant ID from the URL; the API derives it from the authenticated session.
- Platform-facing analytics — cross-tenant metrics (e.g., overall growth, common course patterns) used internally by T-Square. These queries explicitly opt in to cross-tenant scope and never reach a tenant user.
The tenant isolation pattern
One data-access helper, reportingQuery(tenantContext, queryName, params). The tenantContext is either a single-tenant scope (derived from the authenticated request) or an explicit cross-tenant scope (only usable from platform-admin contexts). The helper:
- Refuses cross-tenant queries unless the caller has platform-admin role
- Logs every cross-tenant query with the operator’s identity
- Refuses queries that mix per-tenant and cross-tenant fields in the same result row
Common query patterns
| Pattern | Surface | Aggregation |
|---|---|---|
| Active learners last 30 days | Tenant + platform | Daily roll-up |
| Completion rate by course | Tenant only | Per-course tally |
| Time-to-complete distribution | Tenant only | Histogram, raw if needed |
| Cohort comparison | Tenant only | Window function over enrollments |
| Cross-tenant feature adoption | Platform only | Tenant-level binary metric |
The roll-up strategy
Hot queries run from materialized daily roll-ups; cold queries hit the raw statements table. Roll-ups carry tenant_id forward; no roll-up ever pre-aggregates across tenants. Platform-level cross-tenant metrics aggregate the per-tenant roll-ups at query time, not at storage time.
The reason: a tenant who churns or asks for data deletion can be cleanly removed from per-tenant roll-ups. Removing a tenant from a pre-aggregated cross-tenant roll-up is much harder.
Per-tenant exports
- An export request creates a background job scoped to one tenant
- The job runs through the same
reportingQueryhelper - Output is written to a tenant-scoped object storage path
- The tenant receives a signed URL with 24-hour expiry
- The export is logged with operator and tenant identity for audit
What this prevents
- A tenant admin accidentally seeing another tenant’s data via a dashboard with a missing filter
- A platform analyst running a “for the demo” query that includes a real tenant’s data
- Roll-up corruption that affects multiple tenants when one tenant’s data is fixed or deleted
- Compliance audits getting hard answers like “the system enforces the boundary; here are the tests”
Frequently asked questions
Should I run reporting on the production database?
For mid-scale LMS, yes — keep one Postgres with read replicas for reporting. Move to a separate analytics warehouse when reporting queries start affecting transactional latency, not before.
How do per-tenant exports work?
Per-tenant exports run as background jobs that hit the LRS through the same tenant-scoped helper as everything else. Output gets dropped into a tenant-scoped S3 path with a signed URL.
Working on something similar?
T-Square architects, builds and operates production systems for learning, AI and custom software products. Talk to a senior engineer if you want a second opinion on your design or roadmap.