Commission Calculation (Unilevel)

Commission Plan Schema

CREATE TABLE mlm.commission_plans (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    source_type VARCHAR(20) NOT NULL,  -- 'INVESTMENT', 'PRODUCT', 'ALL'
    max_levels INT NOT NULL DEFAULT 10,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE mlm.commission_tiers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    plan_id UUID NOT NULL REFERENCES mlm.commission_plans(id),
    level_depth INT NOT NULL,           -- 1 = direct, 2 = second level, etc.
    percentage DECIMAL(5,2) NOT NULL,   -- Commission percentage
    min_rank_id UUID REFERENCES mlm.ranks(id),  -- Optional rank requirement

    UNIQUE(plan_id, level_depth)
);

---

Calculation Flow

                    Order/Investment Completed
                              │
                              ▼
                    ┌─────────────────────┐
                    │ Get Partner from    │
                    │ Referral Attribution│
                    └──────────┬──────────┘
                               │
                              ▼
                    ┌─────────────────────┐
                    │ Get Commission Plan │
                    │ for Source Type     │
                    └──────────┬──────────┘
                               │
                              ▼
                    ┌─────────────────────┐
                    │ Get Upline Chain    │
                    │ (Closure Table)     │
                    └──────────┬──────────┘
                               │
                              ▼
              ┌────────────────────────────────┐
              │ For Each Level (1 to max_levels)│
              │  1. Get tier config            │
              │  2. Check rank qualification   │
              │  3. Calculate commission       │
              │  4. Create transaction record  │
              │  5. Update balance             │
              └────────────────────────────────┘
                               │
                              ▼
                    ┌─────────────────────┐
                    │ Emit Commission     │
                    │ Calculated Events   │
                    └─────────────────────┘

---

Commission Job with pg-boss

// Commission calculation as a pg-boss job with built-in retry
interface CommissionJobPayload {
  idempotencyKey: string;  // Prevent duplicate processing
  sourceType: 'ORDER' | 'INVESTMENT';
  sourceId: string;
  amount: number;
  currency: string;
  referringPartnerId: string;
}

// Job handler with concurrency-safe implementation
async function processCommission(job: CommissionJobPayload) {
  const { idempotencyKey, sourceType, sourceId, referringPartnerId } = job;

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

  // 2. Process with proper locking and retry
  const result = await withRetry(
    () => processCommissionTransaction(job),
    RETRY_CONFIGS.commission,
    `commission:${sourceId}`
  );

  return result;
}

// Concurrency-safe transaction implementation
async function processCommissionTransaction(job: CommissionJobPayload) {
  const { idempotencyKey, sourceType, sourceId, referringPartnerId } = job;

  return await db.$transaction(async (tx) => {
    // 2a. Lock source record to prevent double processing
    const sourceTable = sourceType === 'ORDER'
      ? Prisma.sql`product.orders`
      : Prisma.sql`investment.participations`;

    await tx.$queryRaw`
      SELECT id FROM ${sourceTable}
      WHERE id = ${sourceId}::uuid
      FOR UPDATE NOWAIT
    `;

    // 2b. Re-check idempotency inside transaction (race condition protection)
    const existingInTx = await tx.idempotencyKey.findUnique({
      where: { key: idempotencyKey }
    });
    if (existingInTx) return existingInTx.response;

    // 2c. Get upline chain (read-only, no lock needed)
    const upline = await tx.partnerTreePath.findMany({
      where: {
        descendantId: referringPartnerId,
        depth: { gte: 1, lte: 10 }
      },
      orderBy: { depth: 'asc' }
    });

    if (upline.length === 0) {
      // No upline to pay - still save idempotency key
      await tx.idempotencyKey.create({
        data: {
          key: idempotencyKey,
          response: { commissions: [] },
          requestHash: createHash('sha256').update(idempotencyKey).digest('hex'),
          expiresAt: addDays(new Date(), 30)
        }
      });
      return { commissions: [] };
    }

    // 2d. Lock ALL affected partner balances in consistent order (prevent deadlock)
    const partnerIds = upline.map(u => u.ancestorId).sort();

    await tx.$queryRaw`
      SELECT id FROM mlm.partner_balances
      WHERE partner_id = ANY(${partnerIds}::uuid[])
      ORDER BY partner_id
      FOR UPDATE
    `;

    // 2e. Get active commission plan
    const activePlan = await tx.commissionPlan.findFirst({
      where: {
        sourceType: { in: [job.sourceType, 'ALL'] },
        isActive: true,
        validFrom: { lte: new Date() },
        OR: [
          { validTo: null },
          { validTo: { gte: new Date() } }
        ]
      },
      include: { tiers: true }
    });

    if (!activePlan) {
      throw new Error(`No active commission plan found for ${job.sourceType}`);
    }

    // 2f. Calculate and create commission records
    const commissions = [];

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

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

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

      // Check rank requirement if tier has one
      if (tier.minRankId && partner.currentRankId !== tier.minRankId) {
        // Could add rank level comparison here if ranks have levels
        continue;
      }

      const grossAmount = job.amount * (Number(tier.percentage) / 100);
      const netAmount = grossAmount; // Apply fees here if needed
      const careerPoints = job.amount * (Number(tier.careerPointsPercentage || 0) / 100);

      // Create commission transaction with per-partner idempotency
      const commissionIdempotencyKey = `${idempotencyKey}:${ancestor.ancestorId}:${ancestor.depth}`;

      const commission = await tx.commissionTransaction.create({
        data: {
          partnerId: ancestor.ancestorId,
          sourceType: job.sourceType,
          sourceId,
          sourcePartnerId: referringPartnerId,
          levelDepth: ancestor.depth,
          planId: activePlan.id,
          grossAmount,
          netAmount,
          careerPoints,
          currency: job.currency,
          status: 'PENDING',
          idempotencyKey: commissionIdempotencyKey
        }
      });

      // Update partner balance atomically
      await tx.partnerBalance.update({
        where: { partnerId: ancestor.ancestorId },
        data: {
          pendingBalance: { increment: netAmount },
          careerPointsPeriod: { increment: careerPoints },
          careerPointsTotal: { increment: careerPoints },
          version: { increment: 1 },
          lastCalculatedAt: new Date(),
          updatedAt: new Date()
        }
      });

      commissions.push(commission);
    }

    // 2g. Save idempotency key
    await tx.idempotencyKey.create({
      data: {
        key: idempotencyKey,
        response: { commissionIds: commissions.map(c => c.id) },
        requestHash: createHash('sha256').update(idempotencyKey).digest('hex'),
        expiresAt: addDays(new Date(), 30)
      }
    });

    return { commissions };
  }, {
    isolationLevel: 'Serializable',
    timeout: 30000  // 30 second timeout
  });
}

// pg-boss job registration with concurrency control
boss.work('commission-calculation', {
  teamSize: 5,        // Number of concurrent workers
  teamConcurrency: 2  // Jobs per worker
}, async (job) => {
  try {
    return await processCommission(job.data);
  } catch (error) {
    // Log for monitoring
    logger.error('Commission calculation failed', {
      jobId: job.id,
      sourceId: job.data.sourceId,
      error: error.message
    });
    throw error;  // pg-boss will handle retry based on job config
  }
});

See Also: Concurrency Patterns for retry configuration and error handling details.

---

Retry Strategy

Attempt	Delay	Action
1	Immediate	First try
2	30 seconds	Auto-retry
3	2 minutes	Auto-retry
4	10 minutes	Auto-retry
5	Failed	Move to dead letter, alert admin

---

Dual Reward System

Partners can configure how rewards are split between themselves and new referrals.

Configuration Schema

CREATE TABLE mlm.reward_distribution_configs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    partner_id UUID NOT NULL REFERENCES mlm.partners(id),
    reward_type VARCHAR(20) NOT NULL,  -- 'MONETARY', 'CAREER_POINTS'

    -- Where rewards go when this partner brings a referral
    to_self_percentage DECIMAL(5,2) NOT NULL DEFAULT 100,
    to_referral_percentage DECIMAL(5,2) NOT NULL DEFAULT 0,

    is_default BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),

    CONSTRAINT valid_percentages CHECK (to_self_percentage + to_referral_percentage = 100)
);

Distribution Scenarios

Scenario	Monetary (Self/Referral)	Points (Self/Referral)
All to self	100% / 0%	100% / 0%
Share money	70% / 30%	100% / 0%
Share points	100% / 0%	50% / 50%
Full share	50% / 50%	50% / 50%

---

Related Documents

• MLM Tree Implementation
• Database Schema - MLM