Affiliates - Expire Payouts

Overview

The Affiliates Expire Payouts module automatically expires unclaimed affiliate payouts after 60 days. It runs every 12 hours to identify pending payouts created more than 60 days ago and updates their status to EXPIRED, preventing indefinite accumulation of unclaimed commissions and maintaining clean financial records.

Key Features:

12-Hour Schedule: Runs twice daily to check for expired payouts
60-Day Timeout: Payouts expire after 60 days from creation
Bulk Update: Processes all expired payouts in single database operation
Status Tracking: Sets status: 'EXPIRED' and expired_at timestamp
Automatic Cleanup: No manual intervention required
In-Progress Locking: Prevents concurrent executions

Critical Business Impact:

Financial Cleanup: Prevents indefinite payout liability
Affiliate Accountability: Encourages timely payout claims
Reporting Accuracy: Separates pending from expired payouts
Compliance: Maintains clear financial records
Resource Management: Clears stale pending status

Architecture

Execution Flow

sequenceDiagram
    participant Cron as Cron Scheduler
    participant Service as Expire Service
    participant DB as MongoDB
    participant Payout as AffiliatePayout

    Note over Cron,Payout: Every 12 Hours

    Cron->>Service: Trigger expiration check
    Service->>Service: Check in_progress flag
    Service->>Service: Set in_progress = true

    Service->>DB: Calculate 60 days ago
    Note over Service: moment().subtract(60, 'days')

    Service->>Payout: Find expired payouts
    Note over Payout: status: 'PENDING'<br/>created_at < 60 days ago

    Service->>Payout: Bulk update
    Note over Payout: Set status: 'EXPIRED'<br/>Set expired_at: now()

    Payout-->>Service: Update result
    Service->>Service: Log success
    Service->>Service: Set in_progress = false

Component Structure

queue-manager/
├── crons/
│   └── affiliates/
│       └── expirePayouts.js       # Cron scheduler (12 hours)
└── services/
    └── affiliates/
        └── expirePayouts.js       # Service logic (bulk update)

Cron Schedule

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

'0 */12 * * *'; // Every 12 hours at minute 0

Pattern: Twice-daily execution

Frequency: 0:00 and 12:00 (midnight and noon)
In-Progress Locking: Prevents overlapping executions
Purpose: Regular cleanup of expired payouts

Configuration

Timeout Duration

Constant: 60 days

moment().subtract(60, 'days').toDate();

Policy: Payouts older than 60 days automatically expire

Non-Configurable: Hard-coded in service
Business Rule: Gives affiliates 2 months to claim payouts

No Queue Configuration

Direct Execution: Service runs without Bull queue

Synchronous: Executes in cron callback
No Retry: Single attempt per cron cycle
Simple: No queue overhead needed

Service Implementation

Payout Expiration Logic

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

Date Calculation

const moment = require('moment-timezone');

const sixtyDaysAgo = moment().subtract(60, 'days').toDate();

Date Logic:

Current time: moment() (server timezone)
Subtract: 60 days
Convert: To JavaScript Date object

Example: If run on January 31, 2025 at 12:00:

sixtyDaysAgo = December 2, 2024 at 12:00

Bulk Update Query

await Payout.updateMany(
  {
    status: 'PENDING',
    created_at: {
      $lt: moment().subtract(60, 'days').toDate(),
    },
  },
  {
    $set: {
      status: 'EXPIRED',
      expired_at: moment().toDate(),
    },
  },
);

Query Conditions:

status: 'PENDING' - Only pending payouts
created_at < sixtyDaysAgo - Older than 60 days

Update Operations:

status: 'EXPIRED' - Mark as expired
expired_at: moment().toDate() - Record expiration timestamp

Bulk Operation: Updates all matching documents in single query

Efficient: One database round trip
Atomic: All updates succeed or none

Logging

logger.log({
  initiator: 'QM/affiliates/expirePayouts',
  message: 'Expired payouts older than 60 days',
});

Success Logging: Confirms expiration completed

No Count: Doesn't log number of expired payouts
Simple: Just confirms execution

Data Models

AffiliatePayout Document Structure

Collection: affiliate-payout

{
    _id: ObjectId,
    account: ObjectId,              // Affiliate account ID
    amount: {
        software: Number,           // 20% commission
        managed: Number             // 10% commission
    },
    status: String,                 // 'PENDING' | 'PAID' | 'EXPIRED'
    created_at: Date,               // Payout creation timestamp
    expired_at: Date,               // Expiration timestamp (if expired)
    cutoff_date: Date,              // Commission period end date
    stripe_transfer_id: String,     // Stripe transfer ID (if paid)
    // ... other fields
}

Status Lifecycle:

stateDiagram-v2
    [*] --> PENDING: Payout Created
    PENDING --> PAID: Affiliate Claims
    PENDING --> EXPIRED: 60 Days Pass
    PAID --> [*]
    EXPIRED --> [*]

Status Transitions:

PENDING → PAID: Affiliate successfully claims payout
PENDING → EXPIRED: 60 days pass without claim

Update Impact

Before Expiration:

{
    _id: ObjectId("..."),
    account: ObjectId("affiliate_123"),
    amount: { software: 500, managed: 200 },
    status: 'PENDING',
    created_at: ISODate('2024-11-01T10:00:00Z'),
    expired_at: null
}

After Expiration:

{
    _id: ObjectId("..."),
    account: ObjectId("affiliate_123"),
    amount: { software: 500, managed: 200 },
    status: 'EXPIRED',
    created_at: ISODate('2024-11-01T10:00:00Z'),
    expired_at: ISODate('2025-01-31T12:00:00Z')  // Set on expiration
}

Expiration Logic

Timeline Example

Scenario: Payout created November 1, 2024 at 10:00

Date	Days Elapsed	Status	Notes
Nov 1	0	PENDING	Payout created
Nov 30	29	PENDING	Still within window
Dec 15	44	PENDING	Approaching expiration
Dec 31 (0:00)	60	EXPIRED	Cron runs, 60 days passed

Expiration Time: December 31 at 00:00 (midnight cron)

Created: Nov 1 at 10:00
Expired: Dec 31 at 00:00
Elapsed: 60 days

Multiple Payouts

Scenario: 5 affiliates with payouts at different ages

// Payouts created on different dates
[
  { account: 'A1', created_at: '2024-10-01', status: 'PENDING' }, // 92 days - EXPIRED
  { account: 'A2', created_at: '2024-11-01', status: 'PENDING' }, // 61 days - EXPIRED
  { account: 'A3', created_at: '2024-12-01', status: 'PENDING' }, // 31 days - Still PENDING
  { account: 'A4', created_at: '2024-12-15', status: 'PENDING' }, // 17 days - Still PENDING
  { account: 'A5', created_at: '2024-09-15', status: 'PAID' }, // 108 days - Ignored (PAID)
];

Expiration Run on January 1, 2025:

A1: Expired (92 > 60)
A2: Expired (61 > 60)
A3: Still pending (31 < 60)
A4: Still pending (17 < 60)
A5: Ignored (status not PENDING)

Result: 2 payouts expired, 2 remain pending

Error Handling

Database Errors

Scenarios:

MongoDB connection lost
Update operation timeout
Write concern errors

Handling:

try {
    await Payout.updateMany(...);
    logger.log({ message: 'Expired payouts older than 60 days' });
} catch (err) {
    logger.error({ initiator: 'QM/affiliates/expirePayouts', error: err });
}

Recovery: Next cron cycle (12 hours later) will retry

No Matching Documents

Scenario: No payouts older than 60 days

Behavior: updateMany returns { modifiedCount: 0 }

No Error: Operation succeeds with 0 updates
Logging: Still logs success message
Expected: Common when all payouts are recent or claimed

Testing Scenarios

1. Single Payout Expiration

Setup:

const sixtyOneDaysAgo = moment().subtract(61, 'days').toDate();

await AffiliatePayout.create({
  account: affiliate._id,
  amount: { software: 500 },
  status: 'PENDING',
  created_at: sixtyOneDaysAgo,
});

Expected:

Payout updated to EXPIRED
expired_at field set to current timestamp

2. Multiple Payouts at Different Ages

Setup:

await AffiliatePayout.insertMany([
  { status: 'PENDING', created_at: moment().subtract(80, 'days').toDate() }, // Expired
  { status: 'PENDING', created_at: moment().subtract(65, 'days').toDate() }, // Expired
  { status: 'PENDING', created_at: moment().subtract(30, 'days').toDate() }, // Not expired
  { status: 'PENDING', created_at: moment().subtract(10, 'days').toDate() }, // Not expired
]);

Expected:

2 payouts expired
2 payouts remain pending

3. Paid Payouts Not Affected

Setup:

await AffiliatePayout.create({
  status: 'PAID',
  created_at: moment().subtract(100, 'days').toDate(),
});

Expected:

Payout remains PAID (not updated)
Query doesn't match PAID status

4. Already Expired Payouts

Setup:

await AffiliatePayout.create({
  status: 'EXPIRED',
  created_at: moment().subtract(80, 'days').toDate(),
  expired_at: moment().subtract(20, 'days').toDate(),
});

Expected:

Payout remains EXPIRED
Query doesn't match EXPIRED status

5. Boundary Case: Exactly 60 Days

Setup:

const exactlySixtyDaysAgo = moment().subtract(60, 'days').toDate();

await AffiliatePayout.create({
  status: 'PENDING',
  created_at: exactlySixtyDaysAgo,
});

Expected:

NOT Expired (created_at < sixtyDaysAgo, but not equal)
Query uses $lt (less than), not $lte (less than or equal)
Needs to be 60 days + 1 millisecond to expire

6. No Pending Payouts

Setup:

await AffiliatePayout.deleteMany({ status: 'PENDING' });

Expected:

updateMany returns modifiedCount: 0
No errors thrown
Success log still written

Performance Considerations

Query Optimization

Index Requirements:

// AffiliatePayout collection
{ status: 1, created_at: 1 }

Query Pattern: Compound index for status + date filtering

Efficient: Index scan instead of collection scan
Selective: status: 'PENDING' filters majority of documents

Bulk Update Performance

Operation: Single updateMany query

Performance Factors:

Document Count: Number of expired payouts
Typical: 0-100 documents per execution
Duration: ~10-50ms for typical volumes

Write Load: Minimal

Frequency: Twice daily
Volume: Small subset of collection

Execution Time

Total Duration: < 100ms typically

Date calculation: ~1ms
Database query: ~10-50ms
Logging: ~1ms

No Queue Overhead: Direct execution saves time

Monitoring & Logging

Log Patterns

Cron Logs:

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

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

Success Logs:

logger.log({
  initiator: 'QM/affiliates/expirePayouts',
  message: 'Expired payouts older than 60 days',
});

Error Logs:

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

Metrics to Monitor

Expired Payout Count: Number of payouts expired per run
Pending Payout Age: Distribution of pending payout ages
Execution Duration: Time to complete expiration
Error Rate: Failed executions
Payout Status Distribution: Count by status (PENDING/PAID/EXPIRED)

Enhanced Logging Suggestion:

const result = await Payout.updateMany(...);
logger.log({
    initiator: 'QM/affiliates/expirePayouts',
    message: 'Expired payouts older than 60 days',
    expired_count: result.modifiedCount
});

Alerting Scenarios

High Expiration Count: > 100 payouts expired in single run
- Indicates: Many unclaimed payouts
- Action: Investigate affiliate notification system
Execution Failures: Repeated errors
- Indicates: Database connectivity issues
- Action: Check MongoDB status
Zero Expirations for Extended Period: No expirations for > 30 days
- Indicates: Either no payouts or all claimed (good) OR cron not running (bad)
- Action: Verify cron execution

Business Rules

Expiration Policy

60-Day Timeout: Industry-standard unclaimed funds timeout

Balance: Gives affiliates reasonable claim window
Cleanup: Prevents indefinite liability accumulation

Status Permanence

Expired Status is Final: No automatic reversion

Manual Intervention: Requires admin action to reverse
Audit Trail: expired_at timestamp preserved

Notification Implications

No Automatic Notification: Module only updates status

Separate System: Notifications handled by different module
Recommendation: Send reminder emails at 45, 55, 59 days

Integration Points

Leaderboard Impact

Expired Payouts Excluded: Leaderboard sums only non-expired payouts

// From leaderboard.js
{
  $match: {
  } // Matches ALL payouts including EXPIRED
}

Current Behavior: Expired payouts still counted in all-time totals

May be unintended: Consider filtering status !== 'EXPIRED'

Recommendation: Update leaderboard aggregation:

{
  $match: {
    status: {
      $ne: 'EXPIRED';
    }
  }
}

Payout Claim System

Integration: Payout claim endpoints should check status

if (payout.status === 'EXPIRED') {
  throw new Error('Payout has expired and can no longer be claimed');
}

Reporting

Financial Reports: Should separate PENDING from EXPIRED

Liability: PENDING = actual liability
Historical: EXPIRED = unclaimed (no longer owed)

Affiliates Commissions - Payout creation logic
Affiliates Leaderboard - Ranking generation
Affiliates Module Overview - Module overview

Summary

The Affiliates Expire Payouts module provides automated cleanup of unclaimed affiliate commissions after 60 days. Its twice-daily execution with bulk update queries ensures efficient processing while maintaining clean financial records. The simple direct-execution pattern (no queue) provides reliable expiration with minimal overhead.

Key Strengths:

Automatic Cleanup: No manual intervention required
Efficient Bulk Update: Single query updates all expired payouts
Simple Architecture: Direct execution without queue complexity
Audit Trail: Records expiration timestamp
Twice-Daily Execution: Ensures timely expiration

Critical for:

Financial liability management
Unclaimed fund cleanup
Reporting accuracy
Compliance and audit trails
Affiliate accountability

Improvement Opportunities:

Enhanced Logging: Include expired payout count in logs
Notification Integration: Send reminders before expiration
Leaderboard Exclusion: Filter expired payouts from rankings
Configuration: Make 60-day timeout configurable via environment variable

Overview​

Architecture​

Execution Flow​

Component Structure​

Cron Schedule​

Configuration​

Timeout Duration​

No Queue Configuration​

Service Implementation​

Payout Expiration Logic​

Date Calculation​

Bulk Update Query​

Logging​

Data Models​

AffiliatePayout Document Structure​

Update Impact​

Expiration Logic​

Timeline Example​

Multiple Payouts​

Error Handling​

Database Errors​

No Matching Documents​

Testing Scenarios​

1. Single Payout Expiration​

2. Multiple Payouts at Different Ages​

3. Paid Payouts Not Affected​

4. Already Expired Payouts​

5. Boundary Case: Exactly 60 Days​

6. No Pending Payouts​

Performance Considerations​

Query Optimization​

Bulk Update Performance​

Execution Time​

Monitoring & Logging​

Log Patterns​

Metrics to Monitor​

Alerting Scenarios​

Business Rules​

Expiration Policy​

Status Permanence​

Notification Implications​

Integration Points​

Leaderboard Impact​

Payout Claim System​

Reporting​

Related Documentation​

Summary​

Documentation Assistant

Overview

Architecture

Execution Flow

Component Structure

Cron Schedule

Configuration

Timeout Duration

No Queue Configuration

Service Implementation

Payout Expiration Logic

Date Calculation

Bulk Update Query

Logging

Data Models

AffiliatePayout Document Structure

Update Impact

Expiration Logic

Timeline Example

Multiple Payouts

Error Handling

Database Errors

No Matching Documents

Testing Scenarios

1. Single Payout Expiration

2. Multiple Payouts at Different Ages

3. Paid Payouts Not Affected

4. Already Expired Payouts

5. Boundary Case: Exactly 60 Days

6. No Pending Payouts

Performance Considerations

Query Optimization

Bulk Update Performance

Execution Time

Monitoring & Logging

Log Patterns

Metrics to Monitor

Alerting Scenarios

Business Rules

Expiration Policy

Status Permanence

Notification Implications

Integration Points

Leaderboard Impact

Payout Claim System

Reporting

Related Documentation

Summary