Suyog Acharya
Case study · Message Center · Built by me

Pricing 50,000 members individually, without slowing down 20 other apps

I replaced a manual renewal process with a three-screen flow that works out each member's old and new premium and sends personalised emails to 50,000+ members through queues, without slowing down the 20+ other apps that share the platform.

Role
Designed and built it, backend and frontend
Stack
Laravel, MySQL, queues, Vue.js, React
Scale
50,000+ members per campaign
System
Message Center

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.

1 Select policies
Plan A ✓Plan B ✓Plan C
MemberPlanRenews
☑A. SharmaPlan AJun 1
☑R. ThapaPlan BJun 1
☐K. RaiPlan AJun 1
Select all 50,214Next →
2 Preview prices
Plan · tierMembersOldNew
Plan A · member18,402$45$52
Plan A · family9,118$128$140
Plan B · family3,090Priced per member at send time
Grouped by plan and tierNext →
3 Compose and send
Subject: Your plan renews on June 1
Hi {{MEMBER_NAME}}, your premium changes from {{OLD_PRICE}} to {{NEW_PRICE}}…
Filled in per member at send timeSend
The flow I built. Screen 1 lists policies across every renewed plan, Screen 2 previews prices, Screen 3 composes the email with placeholders that are filled in per member.

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.

Why it matters

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.

    New monthly price$0

    Old price is never recalculated: it is the value already stored on the member's policy. Only the new price goes through this decision tree.

    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.

    Per member (first version)
    Grouped (what I shipped)
    Work done to build the preview, as price calculations plus the extra queries needed to detect each plan's type. Bars use a log scale, so the gap is even bigger than it looks. Assumes 24 distinct plan and coverage combinations, a typical batch.

    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.

    Trade-off, written down

    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:

    1. The campaign is saved and a background job expands it into one message per member.
    2. Large campaigns go into the low-priority lane, so a 5-person alert never waits behind 50,000 renewal emails.
    3. Every minute, the scheduler releases a capped batch from each lane, so the email provider is never flooded.
    4. 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.
    5. 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

    50,000+members reached by one personalised campaign
    Dozens, not 50,000price calculations to build a Select All preview
    0ways for the preview and the email to disagree

    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.