In short: insurance prices change at renewal. Each member needs an email with their old price and their new price, and the new price depends on the plan, the coverage level, the member's age and their family.
I built the system that finds the affected members, previews the prices, and sends one personal email per member. The preview always shows the exact numbers the email will contain, and its cost depends on how many plans are involved, not how many people.
The problem
When the Plan Module reported that prices had changed, someone had to filter members by hand, work out who was affected, and send the emails manually. It was slow, easy to get wrong, and the person sending could not see what each member would actually be charged.
Prices are not a single number. Depending on the plan, a member's new premium can be:
- a flat price for their coverage level,
- an age-banded price built from the member, their spouse and their children, or
- a bundle, where several sub-plans are priced separately and added together.
So "send everyone their new price" really means running the right pricing method for every single member, then sending tens of thousands of different emails without disturbing the invoices, alerts and sign-up emails that the same platform sends all day.
What I built: three screens
The admin goes from "prices changed" to "emails queued" in three steps. The mockups below use made-up data.
| Member | Plan | Renews | |
|---|---|---|---|
| ☑ | A. Sharma | Plan A | Jun 1 |
| ☑ | R. Thapa | Plan B | Jun 1 |
| ☐ | K. Rai | Plan A | Jun 1 |
| Plan · tier | Members | Old | New |
|---|---|---|---|
| Plan A · member | 18,402 | $45 | $52 |
| Plan A · family | 9,118 | $128 | $140 |
| Plan B · family | 3,090 | Priced per member at send time | |
Decision 1: the preview can never disagree with the email
The first version of the preview used its own simplified price lookup. That is fast to write, but it means two pieces of code calculate the same price, and for bundled and age-banded plans they gave different answers. An admin could approve one number and members would receive another.
I removed the second implementation. The preview now calls the exact same pricing functions that run when the email is sent. I made those functions reusable and passed them the extra data they needed, such as the member's date of birth and the plan and policy ids.
There is one source of truth for a price. A mismatch between preview and email is now impossible by construction, not something we hope testing catches.
How a price is decided
Nobody chooses "this is a bundle" or "this is age-banded" in the UI. For every policy, the system works it out from the data: is the plan a bundle, and does it, or any plan inside it, use age-banded pricing? That gives three calculation methods. Try them below. All prices are made up.
Something I noticed while tracing it
For age-banded plans, every child is priced from one fixed age band, whatever the child's real age. That is how the existing pricing data is set up, so I kept the behaviour and documented it, so nobody later "fixes" it by accident and changes members' prices.
Decision 2: make the preview scale with plans, not people
My first working version of "Select All" fetched every member and ran the price calculation once per member. With tens of thousands of members, that was slow, and it was mostly wasted work: for a flat-priced plan, every member on the same plan and coverage level gets the same answer.
I moved the counting into the database. One query groups members by plan and coverage level and returns the count and the lowest and highest stored old price for each group. The service then calculates the new price once per group.
Detecting whether a plan is dynamically priced used to cost several queries per member. That answer depends only on the plan, never the member, so I compute it once per plan and reuse it.
For plans whose price depends on each member's own age and family, the preview does not try to compute a price for every person. It shows "Calculated per member at send time" instead. The real price is still calculated correctly for each member when the email goes out. This was a deliberate product decision: an exact preview for those plans would mean doing the whole send twice.
Decision 3: Select All without loading 50,000 people
The obvious way to build Select All is an endpoint that returns every matching member, so the frontend can send the full list back. That moves tens of thousands of records across the network twice for no reason.
Message Center already had a mechanism for bulk sends: you send ALL plus filters, and the recipients are resolved on the server at send time. I reused it. Select All is just a flag in the UI, the preview is the grouped summary above, and the send passes ALL with the selected plans. No member list is ever downloaded.
The send-time filter is slightly different from the one that builds the preview table. For example, it also requires a home address on file. So the final list can differ a little from the preview count. I documented this and the team accepted it, instead of hiding it.
Bugs I caught along the way
The same policy showed up two or three times
The policy list joined an address table on the member id alone. That table keeps address history, so one member with three past addresses produced three rows. I changed every place this join happens to match on member and policy, so each policy appears once.
Selecting one plan could pull in another
When a member enrolls in several plans in one go, all of those plan rows share the same policy id. So "price these policy ids" was ambiguous: picking a member's Plan A row could also pull in their unselected Plan B row. I changed the selection to send exact (policy, plan) pairs and filter on the pair, so only what the admin actually ticked is priced.
Sending 50,000 personal emails
I did not build a new sender. The campaign goes through Message Center's existing pipeline, which already handles invoices, alerts and sign-up emails for 20+ apps:
- The campaign is saved and a background job expands it into one message per member.
- Large campaigns go into the low-priority lane, so a 5-person alert never waits behind 50,000 renewal emails.
- Every minute, the scheduler releases a capped batch from each lane, so the email provider is never flooded.
- For each member, the email is filled in at send time with their own name, old price and new price, using the same pricing functions as the preview.
- Workers send through the email provider with retries, and delivery results come back to update each message's status.
You can watch this pipeline in the interactive platform map.
Trade-offs I documented instead of hiding
- Members on several plans in one batch. If one send covers two plans and a member holds both, both prices appear in their email. Fixing it means carrying exact policy ids through the whole queue pipeline. I left a comment at the exact query explaining the issue and the fix, and the team chose to ship without it.
- A shared filter change. I added "skip members with no email address" to the shared bulk-email filter, but not to the filter SMS also uses, so texting was unaffected. I wrapped it in a clearly marked block and flagged that every email "send to ALL" feature now benefits from it.
- Bad dates of birth. In the existing code, an unreadable date of birth returns the error text instead of a price. I flagged it so it would not be mistaken for a bug in the new feature.
Results
A manual, error-prone process became three screens. The preview is exact where it can be and honest where it cannot. And the whole thing reuses the platform's proven queues, so it added no new failure points.
What I would do next
Carry exact policy ids through the queue so multi-plan members only see the plan they were selected for, and add a send-time report that compares the final recipient count with the preview count.