Designing tier auto-promotion that doesn't flip-flop

The setup

Tiers in an affiliate program work like loyalty levels: Bronze gets 10%, Silver gets 15%, Gold gets 20%. The merchant sets thresholds — $10k revenue in 30 days → Silver, $50k → Gold — and a daily cron promotes affiliates who cross the line.

Sounds simple. The first naive implementation we shipped looked like this:

for (const aff of allAffiliates) {
  for (const rule of promotionRules) {
    const metric = await measure(aff, rule.metric);
    if (metric >= rule.threshold) {
      aff.tier_id = rule.target_tier_id;  // BUG
      break;
    }
  }
}

Three things go wrong with that loop:

Problem 1: the rules aren't ordered

If a Gold rule and a Silver rule both qualify (because the affiliate's metric exceeds both thresholds), the loop assigns whichever it sees first — which depends on the row order from Postgres. We've seen affiliates get demoted from Gold to Silver simply because a new rule was inserted that came earlier in the SELECT order.

The fix is to rank candidates by the target tier's sort_order, not by SQL row order. We pull every qualifying rule, then keep the one whose target tier has the highest sort_order:

const candidates = promotionRules.filter((r) =>
  measure(aff, r.metric) >= r.threshold,
);
if (candidates.length === 0) continue;

const winner = candidates.reduce((best, r) => {
  const a = tierById.get(best.target_tier_id)?.sort_order ?? -Infinity;
  const b = tierById.get(r.target_tier_id)?.sort_order ?? -Infinity;
  return b > a ? r : best;
});

Problem 2: silent demotions

The loop above assigned aff.tier_id = rule.target_tier_id unconditionally. Which means an affiliate sitting at Gold who has a slow month and dips below the Silver threshold gets demoted not just to Silver, but to whatever tier their current 30-day metric matches — possibly Bronze, possibly none at all.

Affiliates hate this. They earned Gold, they showed it on their dashboard, they told their audience “I'm a Gold-tier partner” — and now they're Bronze again because last month was slow. The trust hit is huge.

Cobalz only ever promotes — never silently demotes. The rule engine compares the candidate winner's tier sort_order to the affiliate's current tier sort_order, and keeps the higher one:

const currentSort = tierById.get(aff.tier_id)?.sort_order ?? -Infinity;
const winnerSort = tierById.get(winner.target_tier_id)?.sort_order ?? -Infinity;
if (winnerSort <= currentSort) continue;  // already at this tier or higher
aff.tier_id = winner.target_tier_id;

Demotions are explicit operator actions — through the merchant dashboard, with a reason and an audit-log entry. Never automatic.

Problem 3: the timezone trap

The naive cron runs once a day, at midnight UTC. Sounds reasonable. Except your merchants are in PST, AEDT, JST — and their definition of “today” is their local time, not UTC.

Two failures result. First, payouts and tier evaluations fire at midnight UTC instead of midnight in the merchant's timezone — so a Sunday-night payout becomes a Monday-morning payout for half the world. Second, the daylight-saving transition causes some merchants to see two evaluations on the spring-forward day and zero on the fall-back day.

Cobalz uses Intl.DateTimeFormat to compute the merchant's local hour from UTC, then runs the evaluation only when the local hour matches the configured trigger hour. Intl handles DST correctly. The cron still runs every hour at the platform level, but each merchant sees exactly one evaluation per local day.

What this looks like in production

The TZ-aware daily evaluator at apps/platform/lib/tiers/promote.ts runs nightly. Each affiliate gets at most one promotion per day. Crossings are emitted as outbound webhooks (affiliate.tier_promoted) so the merchant can route them to Slack, their CRM, or wherever else they care.

The full flow takes about 60 lines of logic and an hour of design thinking. Most affiliate platforms either ship the buggy version above or punt on auto-promotion entirely (“manual tier promotion only, on the Enterprise plan”). Both options are worse than getting it right.

Tier rules in the engine: /features/commissions