From 17 Hours to 5 Minutes: Engineering a Production-Grade Reports Microservice

A software engineer’s honest account of inheriting a broken reporting system, diagnosing it from first principles, and rebuilding it into something that actually works.

The Monday Morning Crisis

It was a routine Monday when the operations team flagged it first — the nightly reports hadn’t arrived. Again.

The system was supposed to generate 19 detailed operational reports every day, each aggregating data across 22+ MongoDB collections, powering dashboards that C-level stakeholders used to track field technician performance, work order completion rates, equipment installations, and service delays — nationwide. When a report failed or took too long, blind spots opened up across the entire operation.

I was six months into working on this Field Service Management platform when I inherited the reporting microservice. It was taking 17+ hours to generate a single report cycle. The service crashed intermittently. Reports sometimes generated twice. And no one quite knew why.

This is the story of how I diagnosed, redesigned, and optimised that service — and every lesson that came from it.

Understanding the Beast First

Before touching a single line of code, I mapped out what the service was actually doing.

The reports were built on MongoDB aggregation pipelines — sequences of transformation stages that joined data from 22+ collections: work orders, assignments, technicians, contractors, inventory utilization, serialised equipment, territories, queues, delay reasons, appointment details, and more. Some collections held upward of 7 million records. Every night, the pipeline would start from the primary work orders collection and sequentially join, filter, transform, and project data across all of them.

The result was a 19-column, deeply enriched flat report that operations teams could open in Excel and act on. Simple concept. Catastrophic in execution.

Here’s what was wrong — and how each problem was fixed.

The Database Was Flying Blind: Indexing

The single biggest performance gain — reducing report generation from 17+ hours to under 5 minutes — came not from clever code, but from something foundational: indexes.

MongoDB, like any database, performs a full collection scan when it has no index to guide it. On a collection with 7 million records, every $match, every $lookup join, every filter condition was scanning millions of documents to find the handful it needed. Multiply that across 22 collections and you understand how 17 hours happens.

The fix was systematic indexing across every collection involved in the pipelines.

// Single-field indexes for frequent filter & sort fields
JobSchema.index({ isDeleted: 1, jobReferenceId: 1 });
JobSchema.index({ createdAt: -1 });
JobSchema.index({ status: 1 });

// Compound index matching the exact $match condition at the pipeline entry point
JobSchema.index({ status: 1, requiresDispatch: 1, origin: 1 });

// Indexes for every $lookup foreign key
JobSchema.index({ jobReferenceId: 1 });
JobSchema.index({ regionId: 1 });
JobSchema.index({ zoneId: 1 });
JobSchema.index({ facilityId: 1 });
JobSchema.index({ contractorId: 1 });

// Compound indexes for better performance on filtered lookups
JobSchema.index({ isDeleted: 1, status: 1 });

Why compound indexes matter here: MongoDB can use a compound index for queries that match a prefix of the index fields. The { status: 1, requiresDispatch: 1, origin: 1 } index directly services the opening $match stage of every pipeline, which filters on all three fields simultaneously. Without it, MongoDB scans the entire collection just to pick the eligible work orders.

The same logic applied to the equipment collections. A { serialNumber: 1 } index on the serialised equipment collection — 7 million records — turned every $lookup from a full collection scan into an index seek taking microseconds instead of seconds.

The indexing rule of thumb: Every field that appears in a $match, a $lookup's foreignField, or a $sort in a frequently-run pipeline should have an index. The cost is write-time overhead and storage. On read-heavy reporting workloads, that trade is almost always worth it.

Filter Early, Compute Less: $match at the Top

MongoDB’s aggregation pipeline processes documents stage by stage. Every document that enters a stage must be processed by it. The implication is simple but critical: reduce your dataset as early as possible.

In the original pipelines, filter conditions — status checks, soft-delete filters, null guards — were scattered throughout the pipeline, sometimes only applied several stages deep after expensive $lookup joins had already bloated the working set.

The fix was mechanical but impactful: every $match, date range filter, and exclusion condition was hoisted to the very top of the pipeline.

// Stage 1: Filter FIRST — before any joins happen
{
  $match: {
    updatedAt: { $gte: new Date(start), $lt: new Date(end) },
    status: { $ne: 'PENDING' },
    requiresDispatch: true,
    isDeleted: { $ne: true },
    jobReferenceId: { $exists: true, $ne: null }
  }
}
// Only THEN do lookups, unwinds, groups...

This isn’t just about speed — it’s about respect for your database’s memory budget. MongoDB’s aggregation pipeline has a 100MB memory limit per stage unless allowDiskUse is enabled. Starting with 500,000 work orders and joining them to 7 million equipment records in-memory is a path to crashes. Starting with 3,000 filtered work orders is manageable.

Additionally, $match stages at the top of a pipeline can leverage indexes. Move them down, and MongoDB may no longer be able to use the index at all.

Similarly, within each $lookup sub-pipeline, match conditions were pushed as early as possible and only the necessary fields were projected:

{
  $lookup: {
    from: 'DeviceInventory',
    localField: 'serialNumber',
    foreignField: 'serialNumber',
    as: 'deviceDetails',
    pipeline: [
      { $match: { $expr: { $and: [
        { $eq: ['$isDeleted', false] },
        { $ne: ['$serialNumber', null] }
      ]}}},
      { $project: { _id: 1, serialNumber: 1, catalogCode: 1 }} // only what you need
    ]
  }
}

The $project inside each lookup sub-pipeline is often overlooked. Without it, every matched document from a 7-million-record collection carries its full payload through the rest of the pipeline. With it, only three fields travel through memory per document.

Breaking the Monolith Pipeline: Two-Stage Architecture with a Temp Collection

One of the most architecturally significant changes was breaking each report’s single monolithic pipeline into two discrete stages, with a temporary MongoDB collection acting as a materialised checkpoint between them.

Stage 1 handled the heavy lifting: the initial $match, equipment joins, deduplication, and projection of only the fields needed for Stage 2. Results were written directly into a temporary collection using MongoDB's $merge operator.

Stage 2 read from that temporary collection — a much smaller, pre-filtered dataset — to perform enrichment lookups (customer details, address, appointment, technician, geography) and produce the final projection.

// Stage 1: filter + equipment joins → write to temp collection
await this.aggregateQuery('JobOrders', stage1Pipeline);
// stage1Pipeline ends with:
// { $merge: { into: 'JobReportStaging', whenMatched: 'replace', whenNotMatched: 'insert' } }

// Stage 2: enrich from temp collection → return final data
const results = await this.aggregateQuery('JobReportStaging', stage2Pipeline);

Why does this matter so much? In a single-stage pipeline, every intermediate document — fully joined with equipment data — had to be kept in memory simultaneously while enrichment lookups were being performed. The working set was enormous.

With the two-stage split, Stage 2 operates on a compact, pre-resolved dataset. The enrichment lookups in Stage 2 join against much smaller reference collections (technicians, contractors, regions) and the base documents themselves are lean, containing only the fields that Stage 1 explicitly projected.

This is essentially materialised view thinking applied to a pipeline context. You compute the expensive part once, persist it, and then run lighter operations on the result. The temp collection also provides a natural debugging checkpoint — if Stage 2 fails, you can inspect exactly what Stage 1 produced without re-running the expensive first half.

Cleanup is handled deterministically — before execution starts and after it ends (including on error), ensuring the temp collection never accumulates stale data:

async execute(): Promise<ReportRow[]> {
  await this.cleanupStagingCollection(); // clean before
  try {
    await this.runStage1();
    const results = await this.runStage2();
    await this.cleanupStagingCollection(); // clean after success
    return results;
  } catch (error) {
    await this.cleanupStagingCollection(); // clean on failure too
    throw error;
  }
}

When Memory Is the Enemy: Batch Processing

Even with the two-stage architecture, Stage 2 of certain reports — particularly those enriching work orders with deeply nested documents — was still capable of exhausting the Node.js heap. The documents were large. There were tens of thousands of them. Processing them all at once was simply not viable.

The solution was batch processing: slicing the staging collection into chunks of 2,000 records and running the enrichment pipeline independently on each chunk.

const BATCH_SIZE = 2000;
const totalCount = await StagingModel.countDocuments({});
let processedCount = 0;
const allResults: ReportRow[] = [];

while (processedCount < totalCount) {
  const batchPipeline: PipelineStage[] = [
    { $skip: processedCount },
    { $limit: BATCH_SIZE },
    ...this.buildEnrichmentPipeline()
  ];

  const batchResults = await this.aggregateQuery('JobReportStaging', batchPipeline);
  allResults.push(...batchResults);
  processedCount += BATCH_SIZE;

  // Yield to GC between batches
  if (global.gc) global.gc();
  await new Promise(resolve => setTimeout(resolve, 500));
}

The 500ms pause between batches isn’t arbitrary slowness — it’s a deliberate yield to the garbage collector, allowing V8 to reclaim memory from the previous batch before the next one lands. On large reports, without this pause, memory pressure accumulates batch by batch until the process OOMs.

The batch size of 2,000 was calibrated empirically. Too large and memory spiked. Too small and the overhead of repeated pipeline setup and round-trips to MongoDB slowed things down. 2,000 hit the sweet spot for the document sizes in this system.

The Theoretical Ceiling: Streaming

Batching solved the memory problem for most reports. But for the heaviest ones — work order collections where individual documents carried large embedded arrays (workflow history, notes, configuration details) — even a batch of 2,000 could produce a memory spike.

The architecturally ideal solution for such cases is streaming: processing one document at a time as it flows out of MongoDB, transforming it, and writing it to the output (file, response stream, etc.) without ever holding the full dataset in memory.

import { pipeline } from 'stream/promises';
import { Transform } from 'stream';

const cursor = JobOrderModel.aggregate(stage1Pipeline).cursor({ batchSize: 100 });

const transformStream = new Transform({
  objectMode: true,
  transform(doc, _encoding, callback) {
    const mapped = mapToReportRow(doc);
    this.push(mapped);
    callback();
  }
});

const writableStream = fs.createWriteStream('report-output.csv');

await pipeline(cursor, transformStream, writableStream);

With cursor-based streaming, MongoDB sends documents to your application in small batches (batchSize: 100), your transform function processes each one and emits a row, and the write stream flushes it to output — all without the full dataset ever living simultaneously in memory. Peak memory usage becomes a function of batch size, not total record count.

For a reporting service generating CSV or Excel output, streaming is the gold standard approach. It decouples throughput from memory capacity entirely.

The Deduplication Redesign: Eliminating $sort from the Critical Path

This one deserves careful attention because it’s both a correctness improvement and a significant performance win.

The original pipeline faced a challenging deduplication problem. Each job could have multiple equipment items attached to it (a modem, a SIM card, a telephone set, a voice SIM). After joining with the equipment collections, the pipeline would produce one document per equipment item per job — meaning a job with four items appeared four times in the working set.

The original solution was to assign each row a priority score based on the quality of its equipment data, sort by that priority, and then group by job ID, taking the first (highest priority) row:

// Old approach
assignEquipmentPriorityStage(),         // adds a numeric priority field
{ $sort: { equipmentPriority: 1 } },    // sorts entire working set
{ $group: { _id: '$jobReferenceId', doc: { $first: '$$ROOT' } } }  // keep best row

The fundamental problem: $sort on a large, unbounded working set is one of the most expensive operations in MongoDB's aggregation framework. It requires loading the entire intermediate dataset into memory (or spilling to disk), sorting it, and then discarding most of it. You're paying the full cost of sorting tens of thousands of rows just to pick one per group.

Worse, this approach had a correctness flaw: by picking a single “best” equipment row per job, you inevitably lost data. A job with both a modem and a telephone set would only surface one of them in the report. The other equipment was silently dropped.

The redesign eliminated $sort entirely and fixed the data loss simultaneously, using a two-pass grouping strategy:

// Pass 1: group by (jobReferenceId + deviceCategory) — one row per equipment type per job
{
  $group: {
    _id: { jobReferenceId: '$jobReferenceId', deviceCategory: '$deviceCatalog.deviceCategory' },
    jobRecord: { $first: '$$ROOT' },
    equipment: { $first: {
      serialNumber: '$deviceInventory.serialNumber',
      catalogCode: '$deviceInventory.catalogCode',
      deviceCategory: '$deviceCatalog.deviceCategory'
    }}
  }
}

// Pass 2: group by jobReferenceId — collect all equipment types into an array
{
  $group: {
    _id: '$_id.jobReferenceId',
    doc: { $first: '$jobRecord' },
    allEquipment: { $push: { deviceCategory: '$_id.deviceCategory', data: '$equipment' } }
  }
}

// Then extract each type by filtering the collected array
{
  $addFields: {
    'doc.modemDevice': {
      $arrayElemAt: [
        { $map: {
          input: { $filter: { input: '$allEquipment', cond: { $eq: ['$$this.deviceCategory', 'Modem'] } } },
          in: '$$this.data'
        }},
        0
      ]
    },
    'doc.simDevice': { /* same pattern for SIM, Handset, Voice SIM */ }
  }
}

Instead of sorting and discarding, the pipeline now explicitly collects one representative row per equipment type per job in the first group, then assembles all equipment types together in the second group. The final $addFields stage simply filters the collected array by category to extract each equipment field.

No sort. No data loss. Each job in the output now correctly surfaces its modem, its SIM, its handset, and its voice SIM as distinct fields — because all four were preserved through the two-pass group, not collapsed away by a sort-and-take-first.

From Server Crons to Infrastructure Crons: The Architecture Mistake

Perhaps the most consequential design flaw in the original service wasn’t in the pipeline code at all — it was in the scheduling layer.

The reports ran on a schedule: one at 3 AM, one at 11:30 PM, others at various intervals. The original developers had implemented these schedules as in-process cron jobs — setInterval or node-cron timers living inside the Node.js process itself.

The consequences were severe and interrelated:

The microservice had to run 24 hours a day, 7 days a week — consuming CPU and memory allocations continuously — just to fire a cron job for 20 minutes a day. The allocated resources were sized for the report generation workload (which was intensive) but those resources sat idle for 23+ hours daily.

When the report cron fired and demanded more compute, Kubernetes’ autoscaler would spin up additional pods to handle the load. Each pod had the in-memory cron timer running. So now multiple pods were all attempting to run the same report simultaneously, generating duplicate reports. This was the mysterious duplication bug. It wasn’t a code bug — it was an architectural one baked into the scheduling approach itself.

The fix was to move report scheduling entirely to Kubernetes CronJobs. Instead of a long-running service with internal timers, each report became a containerised job that Kubernetes would spin up at the scheduled time, run to completion, and terminate.

# Kubernetes CronJob: Run report at 3 AM daily
apiVersion: batch/v1
kind: CronJob
metadata:
  name: job-report-3am
spec:
  schedule: "0 3 * * *"
  concurrencyPolicy: Forbid   # The key: never run two instances simultaneously
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: report-runner
            image: report-service:latest
            command: ["node", "run-report.js", "--report=3am"]
          restartPolicy: OnFailure

concurrencyPolicy: Forbid is the architectural guarantee that the old design could never provide: if a previous job is still running when the next scheduled time arrives, Kubernetes simply skips it rather than spawning a duplicate. Run-once, generate-once — guaranteed at the infrastructure level.

The broader principle: scheduling belongs to infrastructure, not application code. In-process schedulers create hidden state, prevent horizontal scaling, and couple availability requirements to task frequency. Offloading to a job scheduler (Kubernetes CronJobs, AWS EventBridge + Lambda, Cloud Scheduler + Cloud Run) separates these concerns cleanly.

Don’t Join What You Already Have

In complex aggregation pipelines, there’s a tendency to reach for $lookup reflexively — if you need data, join the collection that has it. But joins carry real cost: index lookups, document loading, memory allocation, pipeline execution overhead.

Before adding any new lookup stage, it’s worth asking: is this data actually unavailable in what I already have?

In practice, several enrichment lookups in the original pipelines were unnecessary because the data already existed in an already-joined collection, just under a different field name. In other cases, derived data could be computed from existing fields — a display label from two existing string fields, a boolean from a status enum, a count from an array length.

// Instead of joining another collection to get a technician's display name:
technicianDisplayName: {
  $concat: [
    { $ifNull: ['$assignedAgent.firstName', ''] },
    ' ',
    { $ifNull: ['$assignedAgent.lastName', ''] }
  ]
}

// Instead of joining for record count when the array is already present:
numberOfSiteAddresses: {
  $cond: {
    if: { $isArray: '$siteDetails.installationRecords' },
    then: { $size: '$siteDetails.installationRecords' },
    else: 0
  }
}

The discipline of auditing each lookup — asking “do I truly need a separate collection for this?” — trimmed several unnecessary joins from the pipelines. Each eliminated lookup is a round-trip to disk avoided, a sub-pipeline not executed, and a set of joined documents not held in memory.

Observability: Structured Logging at Every Stage

A pipeline that fails silently is worse than one that crashes loudly. In a system generating reports that C-level stakeholders depend on, knowing exactly where and why a failure occurred is non-negotiable.

Every stage of every report was wrapped with structured logging at entry, at checkpoints, and at failure:

logger.info({
  functionName: 'DailyJobReport',
  message: 'Starting Stage 1 (filter + equipment processing)',
  data: { start, end }
});

// ... stage 1 runs ...

logger.info({
  functionName: 'DailyJobReport',
  message: 'Stage 1 complete, beginning Stage 2 enrichment'
});

// ... stage 2 runs ...

logger.info({
  functionName: 'DailyJobReport',
  message: 'Pipeline complete',
  data: { resultCount: results.length }
});

And on failure:

logger.error({
  functionName: 'DailyJobReport',
  message: 'Pipeline execution failed',
  data: { error: error.message, stack: error.stack }
});

Structured logging (objects, not strings) means your log aggregation system (Datadog, ELK, GCP Logging) can index and query on functionName, message, data.resultCount. When a report fails at 3 AM, you don't scroll through raw text — you query functionName = "DailyJobReport" AND level = "error" and the relevant entry surfaces immediately.

The resultCount in success logs is particularly useful: a report that completes successfully but returns 0 rows is a data pipeline issue, not a code crash. That distinction matters enormously when debugging report discrepancies.

Time is Tricky: UTC in the Database, Local Time in the UI

For a system generating reports consumed by operations teams in a specific timezone, time handling is a source of subtle, hard-to-debug data discrepancies.

The principle adopted was straightforward: the database stores everything in UTC, always. createdAt, updatedAt, appointment timestamps, delay timestamps — all UTC. The database is the single source of truth, and UTC ensures there's no ambiguity from daylight saving transitions or regional clock differences.

Time zone conversion happens exactly once, at the presentation layer — in the pipeline’s projection stage when formatting dates for the report output:

// In the aggregation projection: convert UTC to local timezone for display
createdAt: formatDateInPipeline('$createdAt')
// formatDateInPipeline uses $dateToParts with a configured timezone
// then formats as MM/DD/YYYY HH:MM:SS AM/PM for the report consumer's locale

The frontend, separately, reads timestamps from the API and formats them using the user’s browser timezone for real-time displays. Two consumers, two formats — but both working from the same UTC source.

The antipattern to avoid: storing timestamps in application-local time. This makes the database’s timestamps dependent on where the application runs, breaks if the application moves servers, and makes cross-timezone reporting impossible.

Eliminating Single Points of Failure: Pipeline Independence

The original architecture had a particularly fragile pattern: a single aggregation pipeline that queried all job orders — installations, repairs, and miscellaneous types — and then used in-memory JavaScript filtering afterward to split the results into three separate reports.

This meant one pipeline failure killed all three reports. It also meant the pipeline had to be sized for the union of all three workloads, carrying data for all types through every stage even when only one type needed certain lookups.

Each report type was separated into its own independent pipeline:

• Installation Report: $match: { jobType: 'INSTALL' } — optimised for install-specific fields
• Repair Report: $match: { jobType: 'REPAIR' } — includes diagnostic codes, not relevant to installs
• General Report: $match: { jobType: { $nin: ['INSTALL', 'REPAIR'] } }

This breaks the DRY (Don’t Repeat Yourself) principle in the narrow sense — some lookup stages appear in multiple pipelines. But it adheres to a more important systems principle: fault isolation. A data anomaly in repair job orders doesn’t stall the installation report. Each pipeline can be scheduled, monitored, and debugged independently.

Where genuine duplication is a concern, shared pipeline segments can be extracted as factory functions:

function createCustomerLookupStages(collectionName: string): PipelineStage[] {
  return [
    createBaseLookupStage(collectionName, 'jobReferenceId', 'jobReferenceId', 'customerInfo'),
    { $unwind: { path: '$customerInfo', preserveNullAndEmptyArrays: true } }
  ];
}

The factory function pattern gives you reuse at the composition level without coupling pipeline execution. Each report assembles its own pipeline from shared building blocks but runs it independently.

Beyond the Mapper: Smarter Data Shaping

After the aggregation pipeline completed, the raw output passed through a mapper function that translated MongoDB field names to report column headers. It worked, but it was a layer of indirection that added complexity and was easy to break silently — a field name change in the pipeline produced a blank column with no error.

// The mapper pattern: verbose, fragile, hard to keep in sync
function jobReportMapper(data: Record<string, any>): ReportRow {
  return {
    'Job Reference ID': data?.jobReferenceId ?? '',
    'Customer Name': escapeCommas(data?.customerFirstName ?? '') + ' ' + ...,
    'Status': data?.status ?? '',
    // ...60 more fields
  };
}

A more robust approach is to move the column naming into the aggregation projection itself, eliminating the mapper entirely:

// Projection with final column names as field names
{
  $project: {
    'Job Reference ID': '$jobReferenceId',
    'Customer Name': { $concat: ['$customerFirstName', ' ', '$customerLastName'] },
    'Status': { $ifNull: ['$status', ''] }
  }
}

When the projection output IS the report schema, there’s no translation layer to go out of sync. The aggregation result can be streamed directly to CSV serialisation.

For systems where the pipeline and output schema genuinely need to be decoupled, a Zod schema or TypeScript type-safe mapper makes mismatches compile-time errors rather than silent runtime blanks:

const ReportRowSchema = z.object({
  jobReferenceId: z.string(),
  status: z.string(),
  customerFirstName: z.string()
});

// Validation at the boundary: know immediately if pipeline output doesn't match schema
const validated = ReportRowSchema.safeParse(rawDoc);
if (!validated.success) {
  logger.error({ message: 'Schema mismatch in report output', errors: validated.error });
}

The Honest Conversation About Database Choice

MongoDB was the right choice for this project given the constraints — the wider platform was already built on it, the client had existing infrastructure, and the flexible document model genuinely fit the deeply nested, variable-schema nature of field service job orders.

But for a reporting microservice specifically, it’s worth being direct: MongoDB is not the ideal database for analytical reporting workloads.

If the reporting service had been designed from scratch in isolation, the honest recommendation would be:

PostgreSQL with read replicas would have been a strong fit. The report schemas are ultimately flat tabular data. SQL’s declarative joins, window functions, and query planner are purpose-built for the kind of cross-table aggregations this service performs. Read replicas would separate reporting load from transactional load cleanly, preventing report queries from impacting application response times.

BigQuery or a dedicated data warehouse would be the right answer at scale. For 19 daily reports aggregating millions of records, you want columnar storage (which BigQuery, Redshift, and Snowflake provide), separate compute for analytical queries, and a proper ETL pipeline moving data from the operational MongoDB into the warehouse on a schedule. The reporting service then becomes lightweight query execution against pre-optimised analytical storage, rather than a complex MongoDB aggregation marathon.

InfluxDB or TimescaleDB would be specifically appropriate if the reports were primarily time-series in nature — monitoring technician response times over rolling windows, for example.

The pattern to aspire to is CQRS at the infrastructure level: the operational system (MongoDB, optimised for writes and transactional reads) feeds an analytical store (PostgreSQL read replica or BigQuery), and the reporting service reads exclusively from the analytical store. The two workloads never compete.

When you’re handed an existing stack, you optimise within it. But when you have the luxury of design, separate your OLTP and OLAP concerns from day one.

The Numbers, in Summary

What I’d Tell Myself at the Start

Reporting microservices are deceptively complex. They look like read-only, low-stakes services — until the operations team can’t start their day because the overnight report didn’t generate, or it generated three times, or it generated but the equipment serial numbers are all blank.

The optimisations in this piece aren’t exotic. They’re disciplined application of fundamentals: index what you join, filter before you expand, isolate failures, let infrastructure handle scheduling, stream rather than batch rather than all-at-once.

The 17-hour pipeline wasn’t slow because MongoDB is slow or Node.js is slow. It was slow because no one had thought carefully about what the database needed to do its job. Given the right indexes, the right query structure, and the right execution model, the same stack that crawled for 17 hours finished in under 5 minutes.

The tools are rarely the problem. The thinking around them usually is.

If you want momentum, you’ll have to create it yourself, right now, by getting up and getting started.” -Ryan Holiday