Commission Lifecycle Flow

Complete lifecycle of commission calculation, from triggering event to payout.

Overview

The commission lifecycle includes:

1. Triggering events (order/investment completion)
2. Job creation and queuing
3. Idempotency and duplicate prevention
4. Upline retrieval and calculation
5. Balance updates and transaction recording
6. Pending to available transition
7. Commission reversal handling
8. Payout eligibility and processing

---

Main Flow Diagram

---

Step Details

1. Triggering Events

Events that trigger commission calculation:

Event	Source Type	Source ID	Amount Basis
Order confirmed	ORDER	order.id	order.total
Investment activated	INVESTMENT	participation.id	participation.amount
Rank bonus	RANK_BONUS	partner.id	Fixed bonus amount

Job Payload Structure:

interface CommissionJobPayload {
  idempotencyKey: string;    // Unique key for deduplication
  sourceType: 'ORDER' | 'INVESTMENT' | 'RANK_BONUS';
  sourceId: string;          // UUID of source record
  amount: number;            // Commission basis amount
  currency: string;          // Currency code
  referringPartnerId: string; // Partner who made the referral
}

Idempotency Key Format:

commission:{sourceType}:{sourceId}:{timestamp}

Example: commission:ORDER:550e8400-e29b-41d4-a716-446655440000:1705315200

---

2. Job Creation and Queuing

pg-boss Job Configuration:

await boss.send('commission-calculation', payload, {
  retryLimit: 5,
  retryDelay: 30000,        // 30 seconds initial delay
  retryBackoff: true,        // Exponential backoff
  expireInMinutes: 60,       // Job expires after 1 hour
  singletonKey: payload.idempotencyKey  // Prevent duplicate jobs
});

Retry Schedule:

Attempt	Delay	Total Time
1	Immediate	0
2	30 seconds	30s
3	2 minutes	2.5m
4	10 minutes	12.5m
5	30 minutes	42.5m
Failed	N/A	Move to dead-letter queue

---

3. Idempotency Check

Double-Check Pattern:

async function processCommission(job: CommissionJobPayload) {
  const { idempotencyKey } = job;

  // 1. Fast path: Check outside transaction
  const existing = await db.idempotencyKey.findUnique({
    where: { key: idempotencyKey }
  });
  if (existing) {
    return existing.response;  // Already processed
  }

  // 2. Process in transaction with re-check
  return await db.$transaction(async (tx) => {
    // Re-check inside transaction (race condition protection)
    const existingInTx = await tx.idempotencyKey.findUnique({
      where: { key: idempotencyKey }
    });
    if (existingInTx) {
      return existingInTx.response;
    }

    // ... process commission ...

    // Save idempotency key
    await tx.idempotencyKey.create({
      data: {
        key: idempotencyKey,
        response: result,
        expiresAt: addDays(new Date(), 30)
      }
    });

    return result;
  });
}

---

4. Source Validation

Lock Source Record:

SELECT id FROM product.orders
WHERE id = $1::uuid
FOR UPDATE NOWAIT;

Validation Checks:

Check	Query	Action on Fail
Source exists	SELECT 1 WHERE id = ?	Fail job permanently
Not already processed	Check idempotency	Return existing result
Source is valid state	status = 'CONFIRMED'	Fail job permanently
Has referring partner	referring_partner_id IS NOT NULL	Complete with no commissions

---

5. Upline Retrieval (Closure Table)

Query:

SELECT
    ancestor_id,
    depth
FROM mlm.partner_tree_paths
WHERE descendant_id = $1  -- referring partner
  AND depth BETWEEN 1 AND 10  -- levels 1-10
ORDER BY depth ASC;

Result Example:

ancestor_id	depth
partner-A	1
partner-B	2
partner-C	3

Depth Interpretation:

• Depth 1: Direct sponsor of referring partner
• Depth 2: Sponsor's sponsor
• Depth N: N levels up the tree

---

6. Commission Calculation

Commission Plan Lookup:

SELECT cp.*, ct.*
FROM mlm.commission_plans cp
JOIN mlm.commission_tiers ct ON ct.plan_id = cp.id
WHERE cp.source_type IN ($1, 'ALL')
  AND cp.is_active = true
  AND cp.valid_from <= NOW()
  AND (cp.valid_to IS NULL OR cp.valid_to >= NOW())
ORDER BY ct.level_depth;

Calculation Per Level:

for (const ancestor of upline) {
  const tier = plan.tiers.find(t => t.levelDepth === ancestor.depth);
  if (!tier) continue;

  // Check partner status
  const partner = await tx.partner.findUnique({
    where: { id: ancestor.ancestorId },
    select: { status: true, currentRankId: true }
  });

  if (partner?.status !== 'ACTIVE') continue;

  // Check rank qualification
  if (tier.minRankId && !isRankSufficient(partner.currentRankId, tier.minRankId)) {
    continue;
  }

  // Calculate amounts
  const grossAmount = amount * (tier.percentage / 100);
  const careerPoints = amount * (tier.careerPointsPercentage / 100);

  // Create commission transaction
  await tx.commissionTransaction.create({
    data: {
      partnerId: ancestor.ancestorId,
      sourceType,
      sourceId,
      sourcePartnerId: referringPartnerId,
      levelDepth: ancestor.depth,
      planId: plan.id,
      grossAmount,
      netAmount: grossAmount,  // No fees applied
      careerPoints,
      currency,
      status: 'PENDING',
      idempotencyKey: `${idempotencyKey}:${ancestor.ancestorId}:${ancestor.depth}`
    }
  });

  // Update balance
  await tx.partnerBalance.update({
    where: { partnerId: ancestor.ancestorId },
    data: {
      pendingBalance: { increment: grossAmount },
      careerPointsPeriod: { increment: careerPoints },
      careerPointsTotal: { increment: careerPoints },
      version: { increment: 1 }
    }
  });
}

Example Calculation:

Order Amount: 10,000 RUB

Level	Percentage	Partner	Commission
1	10%	Alice	1,000 RUB
2	5%	Bob	500 RUB
3	3%	Carol	300 RUB
4	2%	Dave	200 RUB
5	1%	Eve	100 RUB

Total distributed: 2,100 RUB (21%)

---

7. Balance Updates

Balance Record Structure:

{
  "partnerId": "uuid",
  "availableBalance": 45000.00,
  "pendingBalance": 15000.00,
  "totalEarned": 120000.00,
  "totalWithdrawn": 60000.00,
  "careerPointsTotal": 25000.00,
  "careerPointsPeriod": 5000.00,
  "currency": "RUB",
  "version": 42
}

Locking Pattern:

-- Lock ALL affected balances in consistent order
SELECT id FROM mlm.partner_balances
WHERE partner_id = ANY($1::uuid[])
ORDER BY partner_id  -- Consistent order prevents deadlocks
FOR UPDATE;

---

8. Pending to Available Transition

Transition Criteria:

Criterion	Condition
Commission status	= 'PENDING'
Age	> 14 days (configurable)
Source not refunded	No refund/chargeback on source
No fraud flags	Partner not flagged

Confirmation Job (Scheduled Daily):

async function confirmPendingCommissions() {
  const eligibleCommissions = await db.commissionTransaction.findMany({
    where: {
      status: 'PENDING',
      createdAt: { lt: subDays(new Date(), 14) }
    }
  });

  // Group by partner for efficient locking
  const byPartner = groupBy(eligibleCommissions, 'partnerId');

  for (const [partnerId, commissions] of Object.entries(byPartner)) {
    await db.$transaction(async (tx) => {
      // Lock balance
      await tx.$queryRaw`
        SELECT 1 FROM mlm.partner_balances
        WHERE partner_id = ${partnerId}::uuid
        FOR UPDATE
      `;

      const totalAmount = commissions.reduce((sum, c) => sum + c.netAmount, 0);

      // Update commission statuses
      await tx.commissionTransaction.updateMany({
        where: { id: { in: commissions.map(c => c.id) } },
        data: { status: 'APPROVED', processedAt: new Date() }
      });

      // Move pending -> available
      await tx.partnerBalance.update({
        where: { partnerId },
        data: {
          pendingBalance: { decrement: totalAmount },
          availableBalance: { increment: totalAmount },
          version: { increment: 1 }
        }
      });
    });
  }
}

---

9. Commission Reversal

Reversal Triggers:

Trigger	Source	Action
Order refund	Customer requested	Reverse all related commissions
Chargeback	Payment provider	Reverse all related commissions
Fraud detection	Admin/System	Reverse and flag
Investment cancellation	Customer/Admin	Reverse all related commissions

Reversal Process:

async function reverseCommissions(
  sourceType: string,
  sourceId: string,
  reason: string,
  adminId: string
) {
  const commissions = await db.commissionTransaction.findMany({
    where: {
      sourceType,
      sourceId,
      status: { in: ['PENDING', 'APPROVED', 'PAID'] }
    }
  });

  for (const commission of commissions) {
    await db.$transaction(async (tx) => {
      // Lock balance
      await tx.$queryRaw`
        SELECT 1 FROM mlm.partner_balances
        WHERE partner_id = ${commission.partnerId}::uuid
        FOR UPDATE
      `;

      if (commission.status === 'PENDING') {
        // Simply reverse - deduct from pending
        await tx.partnerBalance.update({
          where: { partnerId: commission.partnerId },
          data: {
            pendingBalance: { decrement: commission.netAmount },
            careerPointsPeriod: { decrement: commission.careerPoints },
            careerPointsTotal: { decrement: commission.careerPoints }
          }
        });
      } else {
        // Create clawback transaction
        await tx.commissionTransaction.create({
          data: {
            partnerId: commission.partnerId,
            sourceType: 'CLAWBACK',
            sourceId,
            grossAmount: -commission.grossAmount,
            netAmount: -commission.netAmount,
            careerPoints: -commission.careerPoints,
            status: 'CLAWBACK',
            reversedFromId: commission.id,
            reversalReason: reason,
            reversedBy: adminId
          }
        });

        // Deduct from available
        await tx.partnerBalance.update({
          where: { partnerId: commission.partnerId },
          data: {
            availableBalance: { decrement: commission.netAmount },
            careerPointsTotal: { decrement: commission.careerPoints }
          }
        });
      }

      // Mark original as reversed
      await tx.commissionTransaction.update({
        where: { id: commission.id },
        data: {
          status: 'REVERSED',
          reversalReason: reason,
          reversedAt: new Date(),
          reversedBy: adminId
        }
      });
    });
  }
}

---

10. Payout Eligibility

Eligibility Criteria:

Criterion	Condition	Error Code
KYC Status	= 'APPROVED'	KYC_REQUIRED
Available Balance	>= requested amount	INSUFFICIENT_BALANCE
Min Payout	amount >= 1,000 RUB	BELOW_MINIMUM
Pending Payouts	No pending/processing payouts	PAYOUT_PENDING
Partner Status	= 'ACTIVE'	PARTNER_INACTIVE
Payout Method	At least one configured	NO_PAYOUT_METHOD

Payout Creation:

-- Atomic balance deduction and payout creation
SELECT * FROM mlm.create_payout_request(
    p_partner_id := $1,
    p_amount := $2,
    p_currency := $3,
    p_method_type := $4,
    p_payout_details := $5
);

Payout Status Flow:

PENDING -> APPROVED -> PROCESSING -> COMPLETED
    |          |            |
    v          v            v
CANCELLED  REJECTED      FAILED
                           |
                           v
                    Balance restored

---

Commission Transaction States

State	Description	Balance Location
PENDING	Calculated, awaiting confirmation	pending_balance
APPROVED	Confirmed, ready for payout	available_balance
PAID	Included in completed payout	withdrawn (total_withdrawn)
HELD	On hold for review	pending_balance
REVERSED	Reversed due to refund	Deducted
CLAWBACK	Negative transaction	Deducted from available
CANCELLED	Cancelled before confirmation	N/A

---

Error Scenarios

Job Processing Errors

Error	Handling	Retry
Source not found	Log and fail permanently	No
Partner not found	Log and fail permanently	No
Database connection lost	Retry with backoff	Yes
Lock timeout	Retry immediately	Yes
Commission plan not found	Log and fail permanently	No

Balance Operation Errors

Error	Handling
Negative balance attempt	Transaction rollback, alert
Version mismatch	Retry with fresh data
Deadlock detected	Retry (ordered locking prevents)

---

Monitoring and Alerts

Key Metrics:

Metric	Alert Threshold
Job queue depth	> 1000 jobs
Failed jobs (24h)	> 10
Dead letter queue	> 0
Processing time	> 30 seconds per job
Reversal rate	> 5% of commissions

Audit Log:

CREATE TABLE mlm.financial_audit_log (
    id UUID PRIMARY KEY,
    event_type VARCHAR(50),
    partner_id UUID,
    amount DECIMAL(20,2),
    balance_before DECIMAL(20,2),
    balance_after DECIMAL(20,2),
    source_type VARCHAR(50),
    source_id UUID,
    checksum VARCHAR(64),
    previous_checksum VARCHAR(64),
    created_at TIMESTAMP DEFAULT NOW()
);

---

Related Documents

• Commission Engine
• MLM Schema
• Referral Attribution Flow
• Partner Journey - Payouts
• Concurrency Patterns
• Commission Flow Diagram