Reporting & Analytics¶

Language: Java 21
Framework: Spring Boot 3.5.5
Source: ~/IdeaProjects/NEXIVO/reporting-service

Purpose¶

The Reporting Service provides real-time and historical analytics across all contact centre operations — calls, agents, queues, AI interactions, SLA, surveys, and campaigns. It exposes 29 report types with XLSX, CSV, and PDF export.

Data Architecture¶

graph LR
    CS[Call Service] -->|Redis Streams| RL[Stream Listeners]
    BS[Billing Service] -->|Redis Streams| RL
    FS[Feedback Service] -->|Redis Streams| RL
    RL -->|Ingest + Enrich| CH[(ClickHouse\nper-tenant DB)]
    CH -->|OLAP Queries| RS[Report Services]
    RS -->|JSON / XLSX / CSV / PDF| API[REST API]
    API --> AD[Agent Desktop]

Storage: ClickHouse (column-oriented OLAP), one database per tenant.
Ingestion: Redis Streams with consumer groups (at-least-once delivery, retry via PendingEntryReclaimService).
Enrichment: ContactServiceClient (Feign) joins call data with agent/queue names at ingest time.

ClickHouse Schema¶

Fact Tables (2-year retention)¶

Table	Description
`call_facts`	Core call records — all dimensions per call
`participant_facts`	Per-participant detail (agent, customer, AI agent)
`transfer_facts`	Inter-queue and inter-agent transfers
`call_event_facts`	Call lifecycle events (dialed, connected, disconnected, etc.)
`agent_status_facts`	Agent status changes with timestamps
`feedback_facts`	Survey responses (per question, rating, comment)

Aggregate Tables¶

Table	Retention	Description
`queue_hourly_aggregate`	90 days	Hourly queue throughput
`queue_daily_aggregate`	1 year	Daily queue summary
`queue_monthly_aggregate`	3 years	Monthly queue summary
`agent_daily_aggregate`	1 year	Daily agent performance

Dimension Tables¶

Table	Description
`agent_dimensions`	Agent master data (SCD Type 2)
`scheduled_call_dimension`	Scheduled outbound call metadata
`schedule_dimension`	Campaign schedule definitions

Ingestion Pipeline¶

Each event type has a dedicated Redis Stream listener, keyed per tenant:

Stream	Listener	Data
`{tenant}/call-report-events`	`CallReportStreamListener`	Call ended payloads
`{tenant}/call-disposition-events`	`CallDispositionStreamListener`	Disposition codes
`{tenant}/call-event-events`	`CallEventStreamListener`	Call lifecycle events
`{tenant}/feedback-submitted-events`	`FeedbackEventStreamListener`	Survey responses
`{tenant}/schedule-events`	`ScheduleEventStreamListener`	Schedule metadata
`{tenant}/scheduled-call-events`	`ScheduledCallEventStreamListener`	Scheduled call records
`{tenant}/contact-status-history`	`ContactStatusHistoryStreamListener`	Agent status changes

Report Types¶

Calls¶

Endpoint	Report
`GET /reports/calls/inbound`	Inbound calls — summary + detail rows
`GET /reports/calls/inbound/answered`	Answered inbound calls only
`GET /reports/calls/abandoned`	Abandoned calls (hung up in queue)
`GET /reports/calls/outbound`	Outbound calls
`GET /reports/calls/all`	All calls unified view
`GET /reports/calls/statistics`	KPI summary (totals, avg wait, avg talk, ASA)
`GET /reports/calls/hourly`	Hourly volume breakdown
`GET /reports/calls/breakdowns`	Pivot by channel / direction / status / outcome
`GET /reports/calls/scheduled`	Scheduled / campaign calls
`GET /reports/calls/scheduled/attempts`	Retry attempts per scheduled call

Agents¶

Endpoint	Report
`GET /reports/agents/calls`	All calls for a specific agent
`GET /reports/agents/performance`	Agent performance table (calls, talk time, SLA %)
`GET /reports/agents/login-history`	Login / logout timestamps
`GET /reports/agents/login-sessions`	Aggregated login hours per session
`GET /reports/agents/status-durations`	Time in each status (available, on-call, break, etc.)
`GET /reports/agents/status-transitions`	State change timeline

Queues¶

Endpoint	Report
`GET /reports/queues/hourly`	Hourly: offered, serviced, abandoned, ASA
`GET /reports/queues/summary`	Date-range aggregate per queue
`GET /reports/queues/daily`	Daily queue metrics
`GET /reports/queues/monthly`	Monthly queue metrics

Service Level (SLA)¶

Endpoint	Report
`GET /reports/service-level/overview`	Platform-wide SLA — answered within threshold
`GET /reports/service-level/teams`	SLA breakdown per team/queue
`GET /reports/service-level/agents`	SLA breakdown per agent

Default SLA threshold: 20 seconds (configurable via slThresholdSeconds filter param).

AI Analytics¶

Endpoint	Report
`GET /reports/ai`	AI-handled vs human-handled volumes
`GET /reports/ai/statistics`	AI success rate, transfer rate, avg duration
`GET /reports/ai/status`	AI agent availability/status distribution
`GET /reports/ai/agents`	AI agent leaderboard (calls, completion rate)

Other¶

Endpoint	Report
`GET /reports/overall-summary`	Executive dashboard (KPIs, leaderboard, AI, feedback)
`GET /reports/dispositions`	Disposition codes — count + percentage
`GET /reports/extensions/summary`	Call activity per phone extension
`GET /reports/feedback/survey`	Customer survey responses with dynamic question columns
`GET /reports/schedules`	Campaign schedule list
`GET /reports/lookups`	Filter dropdown data (queues, channels, agents, extensions)

Filters¶

All reports accept a base filter. Most reports extend it with additional dimensions.

Base Filter¶

Parameter	Type	Description
`dateFrom`	Instant	Start of date range (required)
`dateTo`	Instant	End of date range (required)
`timezone`	String	e.g. `Asia/Qatar` (default)
`page`	int	Page number (default 0)
`size`	int	Page size (default 50)

Extended Call Filter¶

Parameter	Description
`queueId`	Filter by team/queue
`agentId`	Filter by agent
`channelId`	Filter by channel
`callCategory`	Business domain classification
`direction`	`inbound` or `outbound`
`search`	Free text (caller phone, agent name)
`slThresholdSeconds`	SLA threshold in seconds (default 20)
`campaignId` / `scheduleId`	Filter to a specific campaign or schedule
`language`	ISO language code or name

Response Format¶

All paginated responses use ReportResult<S, D>:

{
  "summary": { ... },
  "details": [ ... ],
  "total": 1420,
  "page": 0,
  "size": 50
}

Pagination metadata is also returned as response headers:

X-Current-Page: 0
X-Page-Size: 50
X-Total-Count: 1420
X-Total-Pages: 29

Export¶

Formats¶

Format	MIME Type	Notes
`XLSX`	`application/vnd.openxmlformats-officedocument.spreadsheetml.sheet`	Styled multi-section workbook via Apache POI
`CSV`	`text/csv`	Via OpenCSV
`PDF`	`application/pdf`	Branded reports via JasperReports

Export Endpoints¶

Endpoint	Output
`GET /reports/export/{reportType}?format=XLSX\\|CSV\\|PDF`	Any report type
`POST /reports/export/table`	Export the exact on-screen filtered table (headers + rows from frontend)
`GET /reports/export/summary/pdf`	Executive summary PDF
`GET /reports/export/summary/xlsx`	Executive summary workbook
`GET /reports/export/performance-report`	Agent performance XLSX
`GET /reports/export/performance-report/pdf`	Agent performance PDF
`GET /reports/export/callers-report`	Full call export XLSX (all dimensions, grouped by queue/channel)

Multi-Tenancy¶

Each tenant has an isolated ClickHouse database (e.g. tenant_{uuid})
TenantClickHouseRouter routes every query to the correct database
Redis Stream keys are prefixed: {tenant}/call-report-events
TenantContextHolder (from msl-multitenancy-core) is applied per request

Key Environment Variables¶

CH_HOST=localhost
CH_PORT=8123
CH_USER=default
CH_PASSWORD=...

REDIS_HOST=redis
REDIS_PORT=6379

ADMIN_BASE_URL=https://admin.nexivo.corecognitics.com

Admin¶

POST /admin/backfill/calls — bulk import historical call records (accepts List<CallEndedPayload>). Used for data recovery or migrations.