One active notification per lead: taming event storms with a partial unique index

A loan application is a long journey, and a long journey is an abandonable one. An applicant verifies their phone, runs a credit check, explores their savings, looks at lender offers — and somewhere in the middle, they stop. Our job is to send the right "come back and finish" nudge at the right moment. The difficulty is not deciding what to say. It's that the journey emits events far faster than the nudges are meant to fire, and those two clocks fight each other.

Picture a common sequence. The applicant reaches the initiated step, which schedules a drop-off reminder for thirty minutes later. Four minutes after that, they complete their credit check, which has its own fifteen-minute reminder. If both timers run to completion, the applicant receives two messages — and the second one contradicts the first, nudging them toward a step they've already passed. The entire design of our notification pipeline exists to make that impossible.

Why this mattered

A stale nudge is worse than no nudge. A message saying "you started a credit check, want to finish?" that lands after the applicant has already seen their lender offers doesn't read as a helpful reminder — it reads as broken software, and it quietly erodes trust at the exact moment we're asking someone to trust us with a loan. Now multiply that by the fan-out: each nudge goes to the applicant on WhatsApp and to the internal sales advisor assigned to them. A single mis-ordered event doesn't produce one wrong message; it produces two wrong messages and one confused human.

There was also a correctness trap we knew we couldn't code our way around in the application layer alone. "Is there already a pending nudge for this lead?" is a check-then-act question, and check-then-act is a race. Two events arriving within milliseconds of each other would both read "no, nothing pending," and both would happily schedule a reminder. You cannot make that safe with careful Elixir; you have to make it safe where concurrency is actually arbitrated.

The approach

The invariant we wanted is simple to state: at any instant, a lead has at most one live nudge — and it's always the one for the furthest point they've reached. Everything followed from deciding where to enforce that sentence.

We enforce it in Postgres, not in code. Each nudge is a row whose status moves through pending, scheduled, sent, and cancelled. A partial unique index covers only the live rows — the ones that are pending or scheduled — and keys them by lead. The effect is that a lead can accumulate any number of sent and cancelled rows as a full audit history of everything we ever told them, while being physically incapable of holding two active nudges at once. The database is the source of truth for the invariant; the application code becomes just the happy path that tries to respect it.

create unique_index(:lead_notification_intents, [:lead_id],
  where: "notification_status IN ('pending', 'scheduled')",
  name: :lead_notification_intents_one_active_per_lead
)

Ordering between events is the second decision, and we deliberately kept it boring. Instead of assigning each step a rank number scattered across the codebase — the kind of thing that drifts the moment someone inserts a step in the middle — the order is a single list, and a step's position in that list is its rank. When a new event arrives, we compare positions: if the new event isn't strictly further along than the lead's current live nudge, we drop it. A late-arriving initiated that shows up after credit_check_done is simply ignored, because it can never represent forward progress. A legitimate forward step does the opposite: it cancels the old scheduled job, schedules a new one, and supersedes the existing row in place.

That supersession is the heart of the system. We schedule the delayed nudges as background jobs, and when a later event arrives, we cancel the pending job before scheduling its replacement — so the thirty-minute initiated timer is torn down the instant the credit check completes, and only the credit-check nudge survives. An immediate event, like submitting a lender offer, does the same teardown but sends right away instead of scheduling anything.

We also separate two kinds of events, and the distinction matters. Journey events — the funnel steps — participate in this ordering and supersession. Lifecycle events — your loan was sanctioned, your loan was disbursed, an advisor was assigned — fire independently and never cancel a journey nudge. "Your loan was sanctioned" must go out regardless of where some drop-off timer happens to sit, because it's good news that doesn't compete with a reminder. Conflating those two categories was an early mistake; pulling them apart made the rules legible.

This is the part that took the most discipline to get right. Using the database as a concurrency control only works if the retry is itself idempotent — if re-running the decision against fresh state reliably lands on the correct single outcome. If the retry could create a different inconsistency, the whole pattern becomes a trap dressed up as a safety net. We could be optimistic in the common path — read, decide, write — precisely because we'd proven the rare collision path converges rather than compounds.

What we learned

Pushing the invariant into a partial unique index was the move that made everything above it calm. The orchestrator gets to be optimistic and mostly lock-free, because the worst realistic case isn't a duplicate message reaching an applicant — it's a caught exception and a retry that no one ever sees. The hard guarantee lives in one line of migration; the rest of the code is allowed to be ordinary.

The ordering-by-list-position trick has aged well too. Adding a new step to the funnel is a one-line edit in the right place in the list, and every comparison everywhere updates for free, with no rank numbers to renumber. The tradeoff we'd flag honestly is the retry-on-constraint-error path: it's a deliberate use of the database as a coordination primitive, and it's only safe because we verified convergence. Borrow the pattern, but don't borrow it without checking that your retry can't make things worse.

What surprised us, looking back, is how much of the original bug was really a modeling bug. Once "one live nudge per lead" was a sentence the database could enforce and a list the code could order by, the event storm stopped being a concurrency nightmare and became something close to arithmetic.