The problem

When software sends emails on a broker's behalf, "did it arrive?" isn't a nice-to-have. It's a liability question. Managers had no in-app way to see delivery status, and a customer go-live was waiting on exactly that visibility.

The design

I led the system design and built the pipeline end to end:

Ingest. The email provider calls a webhook for every delivery event. Each request is signature-verified before anything is trusted, then stored with an idempotent upsert, because webhooks get retried and the same event must never count twice.
Aggregate. A service rolls events up per renewal and per recipient, so the UI asks simple questions ("delivered? opened? failed?") instead of scanning raw events.
Serve. A small set of read endpoints feeds a delivery dashboard and a per-row status in the existing tables, where managers already work.

Proving it was right

Analytics that are almost right are worse than none, because people stop trusting them. So before calling it done I reconciled our numbers against the email provider's own analytics, across every sending domain.

The result: zero mismatches across 42 sending domains. The reconcile also caught a timestamp bug before anyone else saw it.

Keeping it fast and correct

Two follow-ups mattered:

Performance. As data grew, a few queries slowed down. I fixed them from the query plans, with targeted denormalisation and a covering index, rather than guessing.
Correctness. Delivery status originally counted every recipient. A single failing CC address could make an email look undelivered when the actual client received it. Status is now based on the primary recipients only.

What I took away

The most valuable part of the project was the reconcile, not the dashboard. Checking your numbers against an independent source is cheap, and it's what turns a chart into something people rely on.