The platform's reporting engine for six years — before there was a business-intelligence (BI) team

Multi-view analytics over million-row, long-window queries on the primary database — 140+ reporting changes, plus regulatory filings on time at millions of rows.

millions of rows/day hard deadlines, met 140+ reporting changes

Situation

Before a BI team existed, operations, partners, and management all needed data — and later, a regulator needed daily filings in exact formats: millions of rows per day, hard deadline, aggregated over long windows on the production database.

Design

Reporting numbers feed commercial reconciliation, so I optimized for both speed and correctness rather than trading one for the other — a precompute + recompute dual track: read from precomputed daily summaries for latency, keep a recompute window open so late corrections still land. An independent settlement process streams per-user transaction flows to workers in batches, landing those daily summaries; ad-hoc queries run tuned aggregation pipelines; the scheduler was rewritten for reliable 2-day recompute windows. For regulatory delivery, built the filing pipeline: million-row-per-file slicing, append-based FTP upload, stream batches tuned until multi-million-row days fit the window.

Outcome

The platform had a working BI function before it had a BI team — the formal team later built on these foundations. Regulatory filings shipped on schedule at million-row scale; 140+ reporting changes over six years.

The design, in code

Representative snippet of the production design — rewritten for publication; not company source.

report-read.ts

// Reporting feeds reconciliation — it has to be fast AND correct. Read
// precomputed daily summaries for speed; keep a recompute window open so
// late corrections still land instead of freezing a wrong number.
async function report(view: View, from: Date, to: Date) {
  const settled = await summaries.find({ view, day: { $lt: recomputeCutoff() } });
  const recent  = await aggregateLive(view, recomputeCutoff(), to); // 2-day window
  return merge(settled, recent); // precompute + recompute, both tracks
}

regulatory-filing.ts

// A multi-million-row day has to fit the filing window. Stream in batches,
// slice at a million rows per file, append-upload — never buffer it all.
const ROWS_PER_FILE = 1_000_000;

async function fileDay(day: string) {
  let part = 0, rows = 0, out = openSlice(day, part);
  for await (const batch of streamSettled(day)) {
    await out.appendUpload(batch); // FTP append — constant memory
    if ((rows += batch.length) >= ROWS_PER_FILE) {
      out = openSlice(day, ++part); rows = 0; // roll to the next file
    }
  }
}