Affiliates - Leaderboard Generation

Overview

The Affiliates Leaderboard Generation module creates monthly rankings of top-performing affiliates based on commission earnings. It runs on the 1st of each month at 1:00 PM, calculating both all-time and last-month rankings using MongoDB aggregation pipelines with transaction safety. The system maintains rank history by tracking previous positions, enabling rank change visualization in the dashboard.

Key Features:

Monthly Execution: Runs on 1st of each month at 13:00 (1:00 PM)
Dual Rankings: All-time totals and last-month performance
Top 10 Lists: Limits rankings to top 10 affiliates per category
Rank Change Tracking: Stores previous rank for comparison
Transaction Safety: Uses MongoDB transactions for atomic updates
Soft Commission Tracking: Based on amount.software field (20% commission)

Critical Business Impact:

Affiliate Motivation: Public rankings encourage competition
Performance Recognition: Highlights top performers
Historical Tracking: Shows rank changes over time
Dashboard Integration: Powers leaderboard UI components
Revenue Insights: Identifies highest-earning affiliates

Architecture

Execution Flow

sequenceDiagram
    participant Cron as Cron Scheduler
    participant Service as Leaderboard Service
    participant Queue as Bull Queue
    participant Processor as Leaderboard Processor
    participant DB as MongoDB
    participant Payout as AffiliatePayout
    participant Board as AffiliatesLeaderboard

    Note over Cron,Board: 1st of Month at 13:00

    Cron->>Service: Trigger leaderboard update
    Service->>Queue: Add job to queue
    Note over Queue: 4 attempts, exponential backoff

    Queue->>Processor: Process leaderboard job
    Processor->>DB: Start transaction

    Processor->>Payout: Aggregate all-time rankings
    Note over Payout: Sum amount.software<br/>Sort by total DESC<br/>Limit 10
    Payout-->>Processor: Top 10 all-time affiliates

    Processor->>Payout: Aggregate last-month rankings
    Note over Payout: Filter cutoff_date >= last month<br/>Sum amount.software<br/>Sort DESC, Limit 10
    Payout-->>Processor: Top 10 last-month affiliates

    Processor->>Board: Find previous rankings
    Board-->>Processor: Previous rank data

    Processor->>Processor: Calculate rank changes
    Note over Processor: Compare current vs previous<br/>Set previous_rank field

    Processor->>Board: Delete all existing rankings
    Processor->>Board: Insert new rankings (20 docs)
    Note over Board: 10 all_time + 10 month

    Processor->>DB: Commit transaction
    DB-->>Processor: Transaction complete
    Processor->>Processor: Log completion

Component Structure

queue-manager/
├── crons/
│   └── affiliates/
│       └── leaderboard.js         # Cron scheduler (monthly)
├── services/
│   └── affiliates/
│       └── leaderboard.js         # Service logic (queue addition)
└── queues/
    └── affiliates/
        └── leaderboard.js         # Processor (aggregation + ranking)

Cron Schedule

File: queue-manager/crons/affiliates/leaderboard.js

'0 13 1 * *'; // 1st of every month at 13:00 (1:00 PM)

Pattern: Monthly execution with specific timing

Day: 1st of month
Hour: 13:00 (1:00 PM)
In-Progress Locking: Prevents concurrent executions
Purpose: Generate monthly leaderboard after previous month completes

Configuration

Queue Retry Configuration

Pattern: queue-manager/services/affiliates/leaderboard.js

{
    attemps: 4,  // Note: typo in source code (should be "attempts")
    backoff: 'exponential',
    delay: 60000  // 60 seconds base delay
}

Retry Schedule:

Attempt	Delay	Total Wait
1	0s	0s
2	60s	60s
3	120s	180s
4	240s	420s

Total: ~7 minutes of retry attempts

Service Implementation

Queue Addition

File: queue-manager/services/affiliates/leaderboard.js

module.exports = async Queue => {
  try {
    await Queue.add(
      {}, // Empty payload - no job data needed
      {
        attemps: 4, // Typo in source
        backoff: 'exponential',
        delay: 60000,
      },
    );
  } catch (err) {
    console.log(err.message, err.stack);
  }
};

Payload: Empty object (no data needed)

Self-Contained: Processor calculates rankings from database
Stateless: No input parameters required

Queue Processor

Leaderboard Calculation

File: queue-manager/queues/affiliates/leaderboard.js

Transaction Wrapper

const session = await mongoose.startSession();
await session.withTransaction(async session => {
  // All ranking operations within transaction
});

Purpose: Atomic updates to leaderboard

Consistency: All-or-nothing update
Rollback: Failures don't leave partial rankings
Isolation: No partial state visible to readers

All-Time Rankings Aggregation

let rankings = await Payout.aggregate([
  {
    $match: {}, // Match all payouts
  },
  {
    $facet: {
      all_time: [
        {
          $group: {
            _id: '$account',
            amount: {
              $sum: '$amount.software',
            },
          },
        },
        {
          $sort: {
            amount: -1, // Descending order
          },
        },
        {
          $limit: 10,
        },
      ],
      // ... last_month facet
    },
  },
]);

Pipeline Stages:

$match: Match all payout documents (no filter)
$facet: Parallel aggregation pipelines
$group: Sum amount.software by account
$sort: Order by total amount descending
$limit: Top 10 only

Output Structure:

[
  {
    all_time: [
      { _id: ObjectId('account1'), amount: 15000 },
      { _id: ObjectId('account2'), amount: 12000 },
      // ... up to 10
    ],
    last_month: [
      // ... similar structure
    ],
  },
];

Last-Month Rankings Aggregation

let lastMonth = moment().subtract(1, 'M');

last_month: [
  {
    $match: {
      cutoff_date: { $gte: lastMonth.toDate() },
    },
  },
  {
    $group: {
      _id: '$account',
      amount: {
        $sum: '$amount.software',
      },
    },
  },
  {
    $sort: {
      amount: -1,
    },
  },
  {
    $limit: 10,
  },
];

Time Filter: cutoff_date >= (current month - 1 month)

Purpose: Ranks based on last month's commissions only

Rolling Window: Always looks at previous month
Execution Time: Runs on 1st, so looks at completed previous month

Example: If run on January 1st, calculates December rankings

Previous Rank Lookup

let previousRankings = await Leaderboard.find();

Purpose: Fetch all existing rankings before deletion

Rank Changes: Compare new rank to old rank
Dashboard Display: Show "↑3" or "↓2" indicators

Rank Document Construction

let allTimeDocs = (rankings[0]?.all_time || []).map((r, i) => {
  return {
    account: r._id,
    rank: i + 1, // Position in top 10
    previous_rank:
      previousRankings.find(
        r2 => r2.account.toString() == r._id.toString() && r2?._doc?.type == 'all_time',
      )?.rank || 9999, // Default: unranked
    type: 'all_time',
  };
});

let lastMonthDocs = (rankings[0]?.last_month || []).map((r, i) => {
  return {
    account: r._id,
    rank: i + 1,
    previous_rank:
      previousRankings.find(
        r2 => r2.account.toString() == r._id.toString() && r2?._doc?.type == 'month',
      )?.rank || 9999,
    type: 'month',
  };
});

Rank Calculation:

rank: Current position (1-10)
previous_rank: Previous month's position
- If found: Previous position
- If not found: 9999 (indicates "new to leaderboard")

Type Field:

all_time: Lifetime total commissions
month: Last month's commissions

Leaderboard Update

await Leaderboard.deleteMany({}, { session });

let promises = [...allTimeDocs, ...lastMonthDocs].map(d => {
  return new Leaderboard(d).save({ session });
});

await Promise.all(promises);

Update Pattern:

Delete All: Clear existing leaderboard
Insert New: Save 20 documents (10 all-time + 10 monthly)
Transaction: Both operations succeed or both rollback

Document Count: Exactly 20 documents after each run

10 all-time rankings
10 last-month rankings

Data Models

AffiliatePayout Collection

Collection: affiliate-payout

{
    _id: ObjectId,
    account: ObjectId,              // Affiliate account ID
    amount: {
        software: Number,           // 20% commission on software sales
        managed: Number             // 10% commission on managed services
    },
    status: String,                 // 'PENDING' | 'PAID' | 'EXPIRED'
    cutoff_date: Date,              // Payout period end date
    created_at: Date,
    // ... other fields
}

Key Fields for Leaderboard:

account: Groups by affiliate account
amount.software: Used for ranking (20% commission rate)
cutoff_date: Filters last month's commissions

AffiliatesLeaderboard Collection

Collection: affiliates-leaderboard

{
    _id: ObjectId,
    account: ObjectId,              // Affiliate account ID
    rank: Number,                   // Current rank (1-10)
    previous_rank: Number,          // Previous month's rank (or 9999)
    type: String,                   // 'all_time' | 'month'
    created_at: Date,
    updated_at: Date
}

Type Values:

all_time: Lifetime commission totals
month: Last month's commission totals

Rank Change Calculation:

const change = previous_rank - rank;
// Positive: Moved up (was #5, now #2 = +3)
// Negative: Moved down (was #2, now #5 = -3)
// 9999 - rank: New to leaderboard

Leaderboard Logic

Ranking Algorithm

All-Time Rankings:

Sum amount.software for each account across all payouts
Sort accounts by total descending
Take top 10
Assign ranks 1-10

Last-Month Rankings:

Filter payouts with cutoff_date >= lastMonth
Sum amount.software for each account in filtered set
Sort accounts by total descending
Take top 10
Assign ranks 1-10

Rank Change Examples

Scenario 1: Moved Up

{
    account: "affiliate_123",
    rank: 2,              // Current: #2
    previous_rank: 5,     // Previous: #5
    type: "all_time"
}
// Rank change: +3 (moved up 3 positions)

Scenario 2: New to Leaderboard

{
    account: "affiliate_456",
    rank: 7,              // Current: #7
    previous_rank: 9999,  // Previous: Not ranked
    type: "month"
}
// Display: "NEW" or "↑" indicator

Scenario 3: Dropped Out

If previously ranked but not in new top 10
Document not created (only top 10 stored)
Dashboard shows as "Not Ranked" if queried

Error Handling

Transaction Failures

Causes:

MongoDB connection loss during transaction
Aggregation errors
Document save failures

Behavior:

await session.withTransaction(async session => {
  // If any operation fails, entire transaction rolls back
});

Recovery: Job retries with exponential backoff

Aggregation Errors

Potential Issues:

Empty AffiliatePayout collection
All payouts have zero amount.software
Invalid cutoff_date values

Handling:

let allTimeDocs = (rankings[0]?.all_time || []).map(...)

Fallback: Empty array if aggregation returns no results

Creates 0 all-time documents
Creates 0-10 month documents (depending on last month)

Previous Rank Lookup Failures

Scenario: Leaderboard.find() fails

Impact: No previous rank data available

Default Behavior:

previous_rank: previousRankings.find(...) || 9999

Result: All affiliates treated as "new to leaderboard"

Testing Scenarios

1. First Leaderboard Generation

Setup:

// No existing leaderboard documents
await Leaderboard.deleteMany({});

// Create payouts for 15 affiliates
await AffiliatePayout.insertMany([
  { account: affiliate1._id, amount: { software: 5000 }, cutoff_date: lastMonth },
  { account: affiliate2._id, amount: { software: 4500 }, cutoff_date: lastMonth },
  // ... 13 more
]);

Expected:

All previous_rank: 9999
Top 10 ranked 1-10
5 affiliates not included (only top 10)

2. Rank Changes

Setup:

// Previous leaderboard
await Leaderboard.insertMany([
  { account: affiliate1._id, rank: 3, type: 'all_time' },
  { account: affiliate2._id, rank: 1, type: 'all_time' },
]);

// New payouts push affiliate1 to #1
await AffiliatePayout.create({
  account: affiliate1._id,
  amount: { software: 10000 },
});

Expected:

affiliate1: rank: 1, previous_rank: 3 (+2)
affiliate2: rank: 2, previous_rank: 1 (-1)

3. Monthly vs All-Time Divergence

Setup:

// affiliate1: High all-time, low last month
await AffiliatePayout.insertMany([
  { account: affiliate1._id, amount: { software: 50000 }, cutoff_date: '2024-01-01' }, // Old
  { account: affiliate1._id, amount: { software: 100 }, cutoff_date: lastMonth }, // Recent
]);

// affiliate2: Low all-time, high last month
await AffiliatePayout.insertMany([
  { account: affiliate2._id, amount: { software: 1000 }, cutoff_date: '2024-01-01' },
  { account: affiliate2._id, amount: { software: 8000 }, cutoff_date: lastMonth },
]);

Expected:

All-time: affiliate1 ranked higher
Month: affiliate2 ranked higher

4. Transaction Rollback

Scenario: Simulate failure during save

// Mock Leaderboard.save to fail on 11th document
let saveCount = 0;
Leaderboard.prototype.save = function () {
  saveCount++;
  if (saveCount === 11) throw new Error('Save failed');
  return originalSave.call(this);
};

Expected:

Transaction rolls back
No new leaderboard documents created
Old leaderboard remains intact
Job retries

5. Empty Payouts

Setup:

await AffiliatePayout.deleteMany({});

Expected:

Aggregation returns empty arrays
0 documents created in leaderboard
No errors thrown
Transaction completes successfully

Performance Considerations

Aggregation Optimization

Index Requirements:

// AffiliatePayout collection
{ account: 1, cutoff_date: 1 }
{ 'amount.software': -1 }

Query Patterns:

Group by account
Filter by cutoff_date
Sort by computed amount

Transaction Duration

Operations:

Aggregation: ~100-500ms (depending on payout count)
Previous rank lookup: ~10-50ms
Delete: ~10ms
Insert 20 docs: ~50-100ms

Total: ~200-700ms transaction duration

Low Lock Time: Short transaction reduces contention
Monthly Frequency: Only runs once per month

Document Count

Leaderboard Size: Always exactly 20 documents

10 all-time
10 monthly

Storage: Minimal (~1KB per document)

Monitoring & Logging

Log Patterns

Cron Logs:

logger.log({
  initiator: 'QM/affiliates/leaderboard',
  message: 'Execution Started for processLeaderboard()',
});

logger.log({
  initiator: 'QM/affiliates/leaderboard',
  message: 'Execution Finished for processLeaderboard()',
});

Error Logs:

logger.error({
  initiator: 'QM/affiliates/leaderboard',
  error: err,
});

Metrics to Monitor

Execution Time: Duration of leaderboard generation
Affiliate Count: Number of affiliates with payouts
Top 10 Threshold: Minimum amount for #10 spot
Rank Changes: Count of affiliates moving up/down
New Entrants: Affiliates with previous_rank: 9999
Transaction Failures: Rollback count

Alerting Scenarios

Execution Failure: Job fails after 4 retries
Empty Leaderboard: 0 documents created
Transaction Rollback: Repeated rollbacks
Long Execution: Takes > 5 seconds
Aggregation Timeout: MongoDB query timeout

Dashboard Integration

Leaderboard Query

// All-time leaderboard
const allTimeBoard = await AffiliatesLeaderboard.find({ type: 'all_time' })
  .populate('account')
  .sort({ rank: 1 });

// Monthly leaderboard
const monthlyBoard = await AffiliatesLeaderboard.find({ type: 'month' })
  .populate('account')
  .sort({ rank: 1 });

Rank Change Display

const rankChange = board.previous_rank - board.rank;

if (board.previous_rank === 9999) {
  display = 'NEW';
} else if (rankChange > 0) {
  display = `↑${rankChange}`; // Moved up
} else if (rankChange < 0) {
  display = `↓${Math.abs(rankChange)}`; // Moved down
} else {
  display = '─'; // No change
}

Affiliates Commissions - Commission calculation and payout creation
Affiliates Expire Payouts - Payout expiration (60-day timeout)
Affiliates Module Overview - Module overview

Summary

The Affiliates Leaderboard Generation module provides monthly ranking updates for affiliate performance tracking. Its transaction-safe aggregation pipeline calculates both all-time and last-month rankings, maintaining rank history for change visualization. The monthly execution on the 1st ensures rankings reflect the completed previous month's performance.

Key Strengths:

Dual Rankings: All-time and monthly performance views
Transaction Safety: Atomic updates with rollback protection
Rank Change Tracking: Maintains previous rank for comparison
Top 10 Focus: Highlights best performers
Efficient Aggregation: $facet enables parallel pipeline execution

Critical for:

Affiliate motivation and competition
Performance recognition
Historical trend analysis
Dashboard leaderboard UI
Revenue attribution insights

Overview​

Architecture​

Execution Flow​

Component Structure​

Cron Schedule​

Configuration​

Queue Retry Configuration​

Service Implementation​

Queue Addition​

Queue Processor​

Leaderboard Calculation​

Transaction Wrapper​

All-Time Rankings Aggregation​

Last-Month Rankings Aggregation​

Previous Rank Lookup​

Rank Document Construction​

Leaderboard Update​

Data Models​

AffiliatePayout Collection​

AffiliatesLeaderboard Collection​

Leaderboard Logic​

Ranking Algorithm​

Rank Change Examples​

Error Handling​

Transaction Failures​

Aggregation Errors​

Previous Rank Lookup Failures​

Testing Scenarios​

1. First Leaderboard Generation​

2. Rank Changes​

3. Monthly vs All-Time Divergence​

4. Transaction Rollback​

5. Empty Payouts​

Performance Considerations​

Aggregation Optimization​

Transaction Duration​

Document Count​

Monitoring & Logging​

Log Patterns​

Metrics to Monitor​

Alerting Scenarios​

Dashboard Integration​

Leaderboard Query​

Rank Change Display​

Related Documentation​

Summary​

Documentation Assistant

Overview

Architecture

Execution Flow

Component Structure

Cron Schedule

Configuration

Queue Retry Configuration

Service Implementation

Queue Addition

Queue Processor

Leaderboard Calculation

Transaction Wrapper

All-Time Rankings Aggregation

Last-Month Rankings Aggregation

Previous Rank Lookup

Rank Document Construction

Leaderboard Update

Data Models

AffiliatePayout Collection

AffiliatesLeaderboard Collection

Leaderboard Logic

Ranking Algorithm

Rank Change Examples

Error Handling

Transaction Failures

Aggregation Errors

Previous Rank Lookup Failures

Testing Scenarios

1. First Leaderboard Generation

2. Rank Changes

3. Monthly vs All-Time Divergence

4. Transaction Rollback

5. Empty Payouts

Performance Considerations

Aggregation Optimization

Transaction Duration

Document Count

Monitoring & Logging

Log Patterns

Metrics to Monitor

Alerting Scenarios

Dashboard Integration

Leaderboard Query

Rank Change Display

Related Documentation

Summary