Reports are a scheduling problem

I built call reporting for a phone platform — the part where a manager finds out how many calls the sales team missed last Tuesday, how long people waited in the queue, and which agent is drowning.

I expected the difficulty to be in the queries. It was not. Aggregating call records is the most tractable part of the whole thing. The difficulty is that a report is not a query — it is a saved definition, a recurrence, a delivery mechanism, a set of people who receive it, and an agreement about what the numbers mean.

A report is a stored object

The first realisation is that you are not building reports. You are building a system in which users build reports.

That means report definitions are first-class stored entities with their own lifecycle: created, edited, disabled, deleted, duplicated as a template. Almost none of the work is in producing numbers — it is in the fact that a saved definition can be edited while a scheduled run is pending, deleted while it has history attached, or disabled temporarily in a way that must not lose its configuration.

One small change from this work sums it up: if a report is deleted, delete its schedule. Obvious in hindsight, and the sort of thing you discover because an orphaned schedule kept firing for a report that no longer existed. Once definitions are entities with dependents, you are doing referential integrity, not analytics.

Recurrence is the hard part

"Every Monday at 9" is easy to say and awkward to hold.

You need a next-run time per report, something that advances it after each run, and something that notices when a run is due. That last one sounds like a scheduler and mostly is, but the failure modes are specific: if advancing the next-run time happens before the work, a failure loses the run; if it happens after, a crash mid-run means it fires twice. Neither is obviously worse in the abstract, and which you choose depends on whether a duplicated report is more annoying than a missing one. For management reports it usually is.

Then: what happens to the schedule when somebody edits the definition, and does changing the recurrence move the next run forwards or leave it alone? Both are defensible and users have strong opinions.

The part I got wrong first time was assuming next-run maintenance could be derived on read. It cannot, reliably, once recurrences are editable and runs can fail — the derived answer depends on history you would then have to keep anyway. Storing and advancing it explicitly is less elegant and correct.

Drilldowns are a second product

A summary number is useless without the ability to ask "which ones?". A manager looking at forty-two missed calls wants the forty-two calls.

Supporting that is not a small addition. Every aggregate needs to know how to explain itself: which underlying records contributed, what type of thing they are, and what metadata to carry so the detail view is useful rather than a wall of identifiers. Direction matters — inbound and outbound are different questions. Disposition matters — answered, missed, abandoned, and the boundaries between those are decisions. Call feedback, where it exists, belongs there too.

And because different aggregates drill down into different kinds of thing, the type of a drilldown has to travel with the response so the interface knows what it is rendering, including on custom templates where the author chose the shape.

Nobody agrees what a number means

The genuinely contentious part was not technical.

If a call rings a queue for twenty seconds, is answered by an agent who transfers it, and the second agent handles it for four minutes — how many calls is that? Whose answer time? If a caller hangs up after two seconds, is that abandoned, or noise? Should a queue's statistics be split by agent, and if so, what happens to calls that rang three agents before one picked up?

Every one of those has a defensible answer and different people in the same company want different ones. A significant share of the work was splitting queue statistics out by agent because the aggregate view answered a different question from the one people were asking.

I also spent an unreasonable amount of time on things like null handling — treating absent values as zero rather than propagating nothing — and on percentage rounding, because a report where the columns do not add to a hundred generates more emails than one that is a day late.

Ship it turned off

One decision I would repeat: the whole subsystem went out behind a feature toggle, with the ability to disable reporting and deactivate anything running.

Reporting runs on a schedule against production data. If something is wrong, you often find out at nine on a Monday when a hundred reports fire at once, and the useful question is not "how do we fix this" but "how do we stop it while we think". A kill switch that also stops the schedules is worth more on that morning than any amount of pre-release testing.

What I took from it

Saved user-defined things are entities, not configuration. The moment users can create, edit and delete definitions that have dependents, you have a data model with integrity constraints — and you will find them by breaking them.

Decide explicitly whether you tolerate duplicates or gaps. Every scheduled system has to choose, the choice is domain-specific, and leaving it to the order in which you wrote two lines is how you get whichever one is worse.

An aggregate that cannot be expanded is barely useful. Design the drilldown alongside the summary rather than afterwards, because the metadata it needs has to be carried through from the start.

Definitions are a negotiation. The most valuable hours were spent agreeing what counted as an answered call, not implementing the count. Get that written down and agreed before building, because the implementation is cheap and re-doing it after an argument is not.