---
title: "Commission Engine"
description: "The Commission Engine powers Dub's partner programs by tracking, calculating, and managing financial rewards across affiliate click, lead, sale, referral, and custom commission types. It processes ..."
last_updated: "2026-10-12T07:00:10.100727+00:00"
canonical_url: "https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/billing-and-payouts/commission-engine"
---
Relevant source files
The following files were used as context for generating this wiki page:
- [apps/web/lib/lemonsqueezy/import-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/lemonsqueezy/import-commissions.ts)
- [apps/web/lib/partnerstack/import-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partnerstack/import-commissions.ts)
- [apps/web/app/ee/api/workflows/create-partner-commission/route.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts)
- [apps/web/lib/tapfiliate/import-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tapfiliate/import-commissions.ts)
- [apps/web/lib/tolt/import-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tolt/import-commissions.ts)
- [apps/web/scripts/programs/backfill-reuse-commission.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/programs/backfill-reuse-commission.ts)
- [apps/web/lib/rewardful/import-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/rewardful/import-commissions.ts)
- [apps/web/app/ee/api/cron/payouts/aggregate-due-commissions/process/route.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/payouts/aggregate-due-commissions/process/route.ts)
- [apps/web/lib/firstpromoter/import-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/firstpromoter/import-commissions.ts)
- [apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts)
- [apps/web/app/ee/api/commissions/route.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/commissions/route.ts)
- [apps/web/lib/api/commissions/create-manual-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/create-manual-commissions.ts)
- [apps/web/lib/partner-referrals/create-referral-commission.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-referral-commission.ts)
- [apps/web/app/ee/admin.dub.co/dashboard/commissions/page.tsx](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/admin.dub.co/(dashboard)/commissions/page.tsx)
- [apps/web/ui/activity-logs/commission-activity.tsx](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/ui/activity-logs/commission-activity.tsx)
- [apps/web/lib/api/sales/calculate-sale-earnings.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/sales/calculate-sale-earnings.ts)
- [apps/web/scripts/customers/framer/tally-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/customers/framer/tally-commissions.ts)
- [apps/web/lib/api/commissions/get-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/get-commissions.ts)
- [apps/web/lib/partner-referrals/create-network-referral-commission.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-network-referral-commission.ts)
- [apps/web/scripts/customers/upheal/sync-stripe-invoices.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/customers/upheal/sync-stripe-invoices.ts)
- [apps/web/lib/api/customers/reattribute-customer.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/customers/reattribute-customer.ts)
- [apps/web/scripts/programs/5-import-customer-sales.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/programs/5-import-customer-sales.ts)
- [apps/web/app/ee/api/admin/commissions/get-commissions-timeseries.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/admin/commissions/get-commissions-timeseries.ts)
- [apps/web/lib/api/partners/sync-total-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/partners/sync-total-commissions.ts)
- [apps/web/scripts/programs/update-commissions-canceled.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/programs/update-commissions-canceled.ts)
- [apps/web/lib/api/commissions/track-commission-update-activity-log.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/track-commission-update-activity-log.ts)
- [apps/web/scripts/programs/create-zero-commissions.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/programs/create-zero-commissions.ts)
- [apps/web/prisma/schema/commission.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma)
- [apps/web/app/ee/api/cron/commissions/referrals/backfill/route.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/commissions/referrals/backfill/route.ts)
- [apps/web/app/ee/api/cron/commissions/referrals/create/route.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/commissions/referrals/create/route.ts)
## Overview
The Commission Engine powers Dub's [partner programs](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/partner-programs/partner-programs) by tracking, calculating, and managing financial rewards across affiliate click, lead, sale, referral, and custom commission types. It processes asynchronous event pipelines, evaluates complex [reward rules](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/partner-programs/reward-rules), handles multi-currency conversions, and executes historical batch migrations from platforms such as Lemon Squeezy, PartnerStack, Tolt, Tapfiliate, FirstPromoter, and Rewardful. Sources: [apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts:182-218](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts#L182-L218)
Sources: [apps/web/prisma/schema/commission.prisma:37-62](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma#L37-L62)
Sources: [apps/web/lib/lemonsqueezy/import-commissions.ts:336-375](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/lemonsqueezy/import-commissions.ts#L336-L375)
## Data model and commission states
### Commission Enumerations and Status Lifecycle
The commission engine relies on a relational Prisma schema centered around the `Commission` model, backed by enums for lifecycle status (`CommissionStatus`), event classification (`CommissionType`), and data ingestion origin (`CommissionSource`). Sources: [apps/web/prisma/schema/commission.prisma:1-35](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma#L1-L35)
```
enum CommissionStatus {
pending
processed
paid
refunded
duplicate
fraud
canceled
hold
}
enum CommissionType {
click
lead
sale
referral
custom
}
enum CommissionSource {
api // track lead/sale API
user // manual creates with userId (manual commissions, bounty submission approvals, submitted leads/sales)
stripe
shopify
hubspot
appsflyer
singular
rewardful
partnerstack
firstpromoter
tolt
tapfiliate
lemonsqueezy
affiliatewp
}
```
Sources: [apps/web/prisma/schema/commission.prisma:1-35](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma#L1-L35)
The `Commission` model records financial metrics, idempotency identifiers, external references, and relations to programs, partners, payouts, invoices, and customers. Sources: [apps/web/prisma/schema/commission.prisma:37-87](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma#L37-L87)
```
model Commission {
id String @id
programId String
partnerId String
rewardId String?
linkId String?
payoutId String?
invoiceId String? // only for sales (idempotency key, each sale event is associated with a unique invoice)
customerId String? // only for leads and sales
eventId String? @unique // only for leads and sales
description String? @db.Text
type CommissionType
amount Int // only for sales (amount of the sale event)
quantity Int // only for clicks/leads (quantity of the event)
earnings Int @default(0) // amount earned by the partner
currency String @default("usd")
status CommissionStatus @default(pending)
userId String? // user who created the manual commission
source CommissionSource?
sourceCommissionId String?
sourcePartnerId String?
bountySubmissionId String?
metadata Json? @db.Json
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
program Program @relation(fields: [programId], references: [id])
partner Partner @relation(fields: [partnerId], references: [id])
programEnrollment ProgramEnrollment @relation(fields: [programId, partnerId], references: [programId, partnerId])
payout Payout? @relation(fields: [payoutId], references: [id])
link Link? @relation(fields: [linkId], references: [id])
customer Customer? @relation(fields: [customerId], references: [id])
reward Reward? @relation(fields: [rewardId], references: [id])
bountySubmission BountySubmission? @relation(fields: [bountySubmissionId], references: [id], onDelete: SetNull)
user User? @relation(fields: [userId], references: [id])
@@unique([invoiceId, programId])
@@index([earnings, programId, partnerId, status]) // old index
@@index([programId, partnerId]) // for programEnrollment relation (used in cron/payouts/aggregate-due-commissions)
@@index([programId, status, createdAt, amount, earnings]) // for program/commissions (scoped by programId)
@@index([createdAt, status, programId, earnings]) // for admin/commissions
@@index([partnerId, customerId, type, createdAt]) // for finding the firstCommission in create-partner-commission workflow
@@index(payoutId)
@@index(customerId)
@@index(linkId)
@@index(type)
@@index(status)
@@index(rewardId)
@@index(userId)
@@index(bountySubmissionId)
}
```
Sources: [apps/web/prisma/schema/commission.prisma:37-87](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma#L37-L87)
Commissions move through distinct lifecycle states managed by background processing crons and administrative scripts. The table below outlines the available enums for status, type, and source. Sources: [apps/web/prisma/schema/commission.prisma:1-35](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma#L1-L35)
| Enum Category | Allowed Values | Sources |
| --- | --- | --- |
| `CommissionStatus` | `pending`, `processed`, `paid`, `refunded`, `duplicate`, `fraud`, `canceled`, `hold` | [apps/web/prisma/schema/commission.prisma:1-10](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma#L1-L10) |
| `CommissionType` | `click`, `lead`, `sale`, `referral`, `custom` | [apps/web/prisma/schema/commission.prisma:12-18](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma#L12-L18) |
| `CommissionSource` | `api`, `user`, `stripe`, `shopify`, `hubspot`, `appsflyer`, `singular`, `rewardful`, `partnerstack`, `firstpromoter`, `tolt`, `tapfiliate`, `lemonsqueezy`, `affiliatewp` | [apps/web/prisma/schema/commission.prisma:20-35](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/commission.prisma#L20-L35) |
> [!WARNING]
> When updating commission statuses from `pending` or `processed` to `canceled` via maintenance scripts, associated payout references (`payoutId`) must be explicitly nulled and payout balances must be retallied using `retallyPayoutsAmount` to prevent accounting discrepancies. Sources: [apps/web/scripts/programs/update-commissions-canceled.ts:42-58](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/programs/update-commissions-canceled.ts#L42-L58)
## Partner commission creation workflow
### Execution Pipeline and Earnings Calculation
The step-based commission creation workflow (`stepCreateCommission`) evaluates incoming events through a structured validation and calculation pipeline. Click and referral events are explicitly skipped as they are not processed by this workflow. For lead and sale events, the engine validates that the partner's program enrollment has an eligible status before proceeding. Sources: [apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts:212-230](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts#L212-L230)
```typescript
// Skip invalid reward events (should never happen, but just in case)
if (event === "click" || event === "referral") {
return logAndReturn({
commission: null,
outputLog: `"${event}" commissions are not created via this workflow. Skipping commission creation...`,
});
}
// for lead / sale rewards, we need to check if the partner is eligible for commissions
if (
["lead", "sale"].includes(event) &&
!COMMISSION_ELIGIBLE_ENROLLMENT_STATUSES.includes(programEnrollment.status)
) {
return logAndReturn({
commission: null,
outputLog: `Partner ${partnerId} is not eligible for commissions (status: ${programEnrollment.status}) in program ${programId}, skipping ${event} commission creation...`,
});
}
```
Sources: [apps/web/app/ee/api/workflows/create-partner-commission/route.ts:212-230](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts#L212-L230)
When a customer identifier is present, the pipeline queries historical records to locate the initial commission for that partner-customer pair. This establishes the subscription start date and duration months, classifying sales as either `new` or `recurring`. The engine then determines applicable partner rewards and calculates sale earnings based on reward type and sale quantity or amount. Sources: [apps/web/lib/api/sales/calculate-sale-earnings.ts:8-28](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/sales/calculate-sale-earnings.ts#L8-L28)
```typescript
export const calculateSaleEarnings = ({
reward,
sale,
}: {
reward: Pick;
sale: Pick;
}) => {
if (!reward) {
return 0;
}
const amount = getRewardAmount(reward);
if (reward.type === "flat") {
return sale.quantity * amount;
} else if (reward.type === "percentage") {
return Math.round((sale.amount * amount) / 100);
}
return 0;
};
```
Sources: [apps/web/lib/api/sales/calculate-sale-earnings.ts:8-28](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/sales/calculate-sale-earnings.ts#L8-L28)
> [!CAUTION]
> If a prior commission for the customer is marked as `fraud` or `canceled`, the execution pipeline terminates immediately and skips commission creation entirely, preventing fraudulent or revoked transactions from generating new payouts. Sources: [apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts:312-319](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts#L312-L319)
### Deduplication and Spend Limit Windows
The workflow enforces strict deduplication and duration boundaries for lead and sale events. If a lead reward has already been issued for a customer, duplicate lead commissions are dropped. For recurring sales, the pipeline evaluates `maxDuration` constraints against subscription duration months to ensure commissions are only created within allowed windows. Sources: [apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts:321-384](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts#L321-L384)
```typescript
// for lead events, we need to check if the partner has already been issued a lead reward for this customer
if (event === "lead") {
return logAndReturn({
commission: null,
outputLog: `Partner ${partnerId} has already been issued a lead reward for this customer ${customerId}, skipping commission creation...`,
});
// for sale rewards, we need to check if partner's reward was updated and different from the first commission's reward
// we need to make sure it wasn't changed from one-time to recurring so we don't create a new commission
} else {
if (
firstCommission.rewardId &&
firstCommission.rewardId !== reward.id
) {
const originalReward = await prisma.reward.findUnique({
where: {
id: firstCommission.rewardId,
},
select: {
id: true,
maxDuration: true,
modifiers: true,
},
});
const originalMaxDuration = originalReward
? getRewardMaxDurationForContext({
reward: originalReward,
context,
})
: null;
if (
typeof originalMaxDuration === "number" &&
originalMaxDuration === 0
) {
return logAndReturn({
commission: null,
outputLog: `Partner ${partnerId} is only eligible for first-sale commissions based on the original reward ${originalReward?.id}, skipping commission creation...`,
});
}
}
// for sale rewards with a max duration, we need to check if the first commission is within the max duration
// if it's beyond the max duration, we should not create a new commission
if (typeof reward?.maxDuration === "number") {
// One-time sale reward (maxDuration === 0)
if (reward.maxDuration === 0) {
return logAndReturn({
commission: null,
outputLog: `Partner ${partnerId} is only eligible for first-sale commissions, skipping commission creation...`,
});
}
// Recurring sale reward (maxDuration > 0)
else {
const subscriptionDurationMonths = differenceInMonths(
createdAt ?? new Date(), // account for custom commission creation date
firstCommission.createdAt,
);
if (subscriptionDurationMonths >= reward.maxDuration) {
return logAndReturn({
commission: null,
```
Sources: [apps/web/app/ee/api/workflows/create-partner-commission/route.ts:321-384](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts#L321-L384)
> [!NOTE]
> Custom commission types bypass standard reward evaluation entirely, directly assigning the input amount as earnings while zeroing out the base amount. Sources: [apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts:239-242](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/workflows/create-partner-commission/route.ts#L239-L242)
## Manual and batch commission ingestion
Workspace users with owner or member roles on business, advanced, or enterprise plans can create manual commissions through the [REST API](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/administration-and-api/rest-api) endpoint at `/api/commissions`. The endpoint parses incoming payloads using Zod validation schemas and executes manual creation handlers. When the request specifies a custom commission with a negative amount, the response designates the transaction as a clawback queued for the partner with a 202 status code. Sources: [apps/web/app/(ee)/api/commissions/route.ts:74-105](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/commissions/route.ts#L74-L105)
```typescript
export const POST = withWorkspace(
async ({ workspace, session, req }) => {
const programId = getDefaultProgramIdOrThrow(workspace);
const body = createManualCommissionBodySchema.parse(
await parseRequestBody(req),
);
console.time("createManualCommissions");
await createManualCommissions({
...body,
workspace,
programId,
user: session.user,
});
console.timeEnd("createManualCommissions");
const isClawback = body.type === "custom" && body.amount < 0;
const response = createCommissionResponseSchema.parse({
success: true,
message: isClawback
? "A clawback has been queued for the partner!"
: "Your commissions are being created and will appear shortly.",
});
return NextResponse.json(response, {
status: 202,
});
},
```
Sources: [apps/web/app/ee/api/commissions/route.ts:74-105](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/commissions/route.ts#L74-L105)
The ingestion logic handles custom, lead, and sale event types. For custom commissions, it queues partner commission creation directly with user source attribution and aggregate trigger flags. For sale events, if `importStripeInvoices` is enabled, the handler validates that the workspace maintains an active Stripe Connect integration and that the target customer has an associated Stripe Customer ID. It also performs invoice binding checks to verify whether a commission for the given invoice identifier already exists within the program. Sources: [apps/web/lib/api/commissions/create-manual-commissions.ts:123-164](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/create-manual-commissions.ts#L123-L164)
```typescript
if (type === "sale") {
const { importStripeInvoices, sale } = args;
if (importStripeInvoices) {
if (!workspace.stripeConnectId) {
throw new DubApiError({
code: "bad_request",
message: `Your workspace isn't connected to Stripe yet. Please install the Stripe integration to continue: https://app.dub.co/${workspace.slug}/settings/integrations/stripe`,
});
}
if (targetCustomer && !targetCustomer.stripeCustomerId) {
throw new DubApiError({
code: "bad_request",
message: `Customer ${targetCustomer.id} does not have a Stripe Customer ID configured. Please update the customer record at https://app.dub.co/${workspace.slug}/program/customers/${targetCustomer.id}`,
});
}
}
const invoiceId = sale?.invoiceId ?? args.invoiceId;
if (invoiceId) {
const commission = await prisma.commission.findUnique({
where: {
invoiceId_programId: {
invoiceId,
programId,
},
},
select: {
id: true,
},
});
if (commission) {
throw new DubApiError({
code: "conflict",
message: `There is already a commission for the invoice ${invoiceId}.`,
});
}
}
}
```
Sources: [apps/web/lib/api/commissions/create-manual-commissions.ts:123-164](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/create-manual-commissions.ts#L123-L164)
> [!WARNING]
> Attempting to ingest a manual sale commission with an `invoiceId` that already exists for the program throws a conflict API error (`409`), preventing duplicate payouts for the same invoice. Sources: [apps/web/lib/api/commissions/create-manual-commissions.ts:144-163](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/create-manual-commissions.ts#L144-L163)
Batch ingestion scripts can also synchronize paid invoices directly from Stripe for enrolled customers. The synchronization script queries paid invoices, filters out zero-amount invoices, and checks for existing commission records by matching either the `invoiceId` or checking if the amount matches within a one-hour timestamp window of the invoice creation time. Sources: [apps/web/scripts/customers/upheal/sync-stripe-invoices.ts:69-84](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/customers/upheal/sync-stripe-invoices.ts#L69-L84)
```typescript
const invoicesToProcess = invoices.data.filter((invoice) => {
// skip $0 invoices
if (invoice.amount_paid === 0) {
return false;
}
const existingCommission = existingCommissions.find(
(commission) =>
commission.invoiceId === invoice.id ||
(commission.amount === invoice.amount_paid &&
new Date(commission.createdAt).getTime() >=
new Date(invoice.created * 1000).getTime() - 60 * 60 * 1000 &&
new Date(commission.createdAt).getTime() <=
new Date(invoice.created * 1000).getTime() + 60 * 60 * 1000),
);
return !existingCommission;
});
```
Sources: [apps/web/scripts/customers/upheal/sync-stripe-invoices.ts:69-84](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/customers/upheal/sync-stripe-invoices.ts#L69-L84)
> [!TIP]
> Stripe invoice synchronization scripts match existing commissions by checking invoice IDs or by verifying amount equality within a 60-minute window of the Stripe invoice creation timestamp to prevent duplicate batch creations. Sources: [apps/web/scripts/customers/upheal/sync-stripe-invoices.ts:74-82](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/customers/upheal/sync-stripe-invoices.ts#L74-L82)
## Partner and network referral commissions
### Scheduled Cron Pipelines and Backfill Mechanics
Referral commissions evaluate multi-tier partner relationships and platform network payouts through automated cron jobs. The backfill endpoint at `/api/cron/commissions/referrals/backfill` inspects program application events to locate a referring partner (`referredByPartnerId`) and evaluates the referrer's `referralReward` configuration triggers. Depending on the trigger configuration, the cron dispatches batch jobs to the creation handler. Sources: [apps/web/app/(ee)/api/cron/commissions/referrals/backfill/route.ts:75-130](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/commissions/referrals/backfill/route.ts#L75-L130)
| Trigger Type | Eligible Source Event | Enqueued Job Target | Sources |
| :--- | :--- | :--- | :--- |
| `commissionThreshold` / `partnerApproved` | Program application or partner threshold approval | `/api/cron/commissions/referrals/create` with `programId` and `partnerId` | [apps/web/app/(ee)/api/cron/commissions/referrals/backfill/route.ts:75-91](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/commissions/referrals/backfill/route.ts#L75-L91) |
| `saleRecorded` / `commissionEarned` | Existing sale commissions with `pending`, `processed`, or `paid` status | `/api/cron/commissions/referrals/create` chunked in batches of 50 with `sourceCommissionId` | [apps/web/app/(ee)/api/cron/commissions/referrals/backfill/route.ts:93-130](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/cron/commissions/referrals/backfill/route.ts#L93-L130) |
The execution endpoint at `/api/cron/commissions/referrals/create` receives the payload and delegates execution to `createReferralCommission`. This function blocks self-referrals by checking whether the target `partnerId` matches the `referredByPartnerId`. Sources: [apps/web/lib/partner-referrals/create-referral-commission.ts:30-35](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-referral-commission.ts#L30-L35)
```typescript
if (partnerId && referredByPartnerId && partnerId === referredByPartnerId) {
console.log(
`Skipping referral commission creation for self-referral (partner ${partnerId}).`,
);
return null;
}
```
Sources: [apps/web/lib/partner-referrals/create-referral-commission.ts:30-35](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-referral-commission.ts#L30-L35)
> [!WARNING]
> If a partner's ID matches their `referredByPartnerId`, the referral commission pipeline halts immediately and skips creation to prevent circular self-referral payouts. Sources: [apps/web/lib/partner-referrals/create-referral-commission.ts:30-35](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-referral-commission.ts#L30-L35)
### Network Program Payout Fees and Duration Limits
Network referral commissions governed by `createNetworkReferralCommission` calculate earnings based on a partner's payout event within the Dub network workspace (`NETWORK_WORKSPACE_ID`). The function validates that the payout does not originate from internal platform programs (`NETWORK_PROGRAM_ID`, `ACME_PROGRAM_ID`, or `DEMO_PROGRAM_ID`) and estimates a 3% average payout fee (`payoutFeeEarned`) from the total payout amount. Sources: [apps/web/lib/partner-referrals/create-network-referral-commission.ts:44-55](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-network-referral-commission.ts#L44-L55)
```typescript
if (
[NETWORK_PROGRAM_ID, ACME_PROGRAM_ID, DEMO_PROGRAM_ID].includes(
payout.programId,
)
) {
console.error(
`Payout ${payout.id} is from Network, Acme, or Demo program, skipping...`,
);
return null;
}
const payoutFeeEarned = Math.floor(payout.amount * 0.03); // assumes 3% average payout fee for all payouts
```
Sources: [apps/web/lib/partner-referrals/create-network-referral-commission.ts:44-55](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-network-referral-commission.ts#L44-L55)
The pipeline checks if the referring partner is actively enrolled in the network program and inspects prior commission history against maximum duration bounds. Sources: [apps/web/lib/partner-referrals/create-network-referral-commission.ts:199-211](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-network-referral-commission.ts#L199-L211)
```typescript
if (firstCommission) {
const durationMonths = differenceInMonths(
new Date(),
firstCommission.createdAt,
);
if (durationMonths >= NETWORK_REFERRAL_REWARD.maxDuration) {
console.log(
`Referrer partner ${partner.referredByPartnerId} has reached max duration for network bonus.`,
);
return null;
}
}
```
Sources: [apps/web/lib/partner-referrals/create-network-referral-commission.ts:199-211](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-network-referral-commission.ts#L199-L211)
> [!NOTE]
> Network referral bonuses enforce a strict maximum duration (`NETWORK_REFERRAL_REWARD.maxDuration`) calculated from the creation date of the very first network commission (`firstCommission.createdAt`). Sources: [apps/web/lib/partner-referrals/create-network-referral-commission.ts:199-211](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-network-referral-commission.ts#L199-L211)
When database unique constraint violations (`P2002`) occur during commission insertion due to race conditions on the `invoiceId`, the handler catches the error, logs the skip event, and prevents duplicate commission creation. Sources: [apps/web/lib/partner-referrals/create-network-referral-commission.ts:247-252](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-network-referral-commission.ts#L247-L252)
```typescript
if (error.code === "P2002") {
console.log(
`Referral commission already exists for invoiceId ${commissionData.invoiceId}, skipping creation.`,
);
return null;
}
```
Sources: [apps/web/lib/partner-referrals/create-network-referral-commission.ts:247-252](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partner-referrals/create-network-referral-commission.ts#L247-L252)
## Legacy platform commission migration
### Platform Importers and Currency Conversion
Migrating historical affiliate programs into Dub requires dedicated batch importers that process external payloads from Lemon Squeezy, PartnerStack, Tolt, Tapfiliate, and FirstPromoter. Each platform importer normalizes source-specific records into unified Prisma commission schemas while resolving associated partner links, customer identifiers, and lead events. Sources: [apps/web/lib/lemonsqueezy/import-commissions.ts:263-281](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/lemonsqueezy/import-commissions.ts#L263-L281)
Importers retrieve historical conversions, invoices, or commissions in paginated chunks and evaluate currency formats. Non-USD values are converted into standard amounts using real-time foreign exchange rates via `convertCurrencyWithFxRates`. Lemon Squeezy imports map affiliate invoices to `saleEvents`, falling back to `firstOrderItemPrice` when initial order subtotals are zero. PartnerStack, Tolt, Tapfiliate, and FirstPromoter handle manual line items or transactional sales by querying matching customer records and verifying deduplication windows. Sources: [apps/web/lib/partnerstack/import-commissions.ts:177-185](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partnerstack/import-commissions.ts#L177-L185)
| Platform Importer Source | Entity Identifier | Core Deduplication Key / Fallback | Currency Handling & Conversion | Sources |
| :--- | :--- | :--- | :--- | :--- |
| Lemon Squeezy | `invoiceId` (`ls_invoice_${id}`) | `invoiceId_programId` unique index | Resolved via `resolveAmountUsd` or fx rates | [apps/web/lib/lemonsqueezy/import-commissions.ts:265-274](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/lemonsqueezy/import-commissions.ts#L265-L274) |
| PartnerStack | `commission.key` | `invoiceId_programId` or $\pm 1$ hour window check | `convertCurrencyWithFxRates` if not USD | [apps/web/lib/partnerstack/import-commissions.ts:156-166](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partnerstack/import-commissions.ts#L156-L166) |
| Tapfiliate | `commission.id` | `invoiceId_programId` using conversion ID | `convertCurrencyWithFxRates` for sub-amounts and earnings | [apps/web/lib/tapfiliate/import-commissions.ts:167-176](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tapfiliate/import-commissions.ts#L167-L176) |
| Tolt | `commission.id` | `transaction_id` or $\pm 1$ hour window check | Converted via `convertCurrencyWithFxRates` against program currency | [apps/web/lib/tolt/import-commissions.ts:178-199](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tolt/import-commissions.ts#L178-L199) |
| Rewardful | `commission.id` | `sale.id` or Stripe customer ID $\pm 1$ hour window check | Converted via `convertCurrencyWithFxRates` for cents and earnings | [apps/web/lib/rewardful/import-commissions.ts:163-197](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/rewardful/import-commissions.ts#L163-L197) |
| FirstPromoter | `${commission.id}` | `${commission.id}` or customer $\pm 1$ hour window check | `convertCurrencyWithFxRates` for original sale amount | [apps/web/lib/firstpromoter/import-commissions.ts:178-209](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/firstpromoter/import-commissions.ts#L178-L209) |
> [!WARNING]
> Platforms like PartnerStack, Tolt, and Rewardful that omit native Stripe invoice identifiers during migration rely on a fallback window query checking for matching customer IDs and amounts within a $\pm 1$ hour timeframe of `chargedAt` to prevent duplicate imports during transition periods. Sources: [apps/web/lib/partnerstack/import-commissions.ts:290-306](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/partnerstack/import-commissions.ts#L290-L306)
Sources: [apps/web/lib/tolt/import-commissions.ts:251-264](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/tolt/import-commissions.ts#L251-L264)
Sources: [apps/web/lib/rewardful/import-commissions.ts:237-252](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/rewardful/import-commissions.ts#L237-L252)
## Customer reattribution and clawback reconciliation
### Transfer execution and adjustments
When a customer is reattributed between partners, the engine manages clawback adjustments and replacement commission generation. If paid commissions exist on the old customer record, their `eventId` and `invoiceId` fields are cleared to prevent constraint violations, and a negative adjustment commission with the type `custom`, description `tracking_error`, and earnings set to negative paid earnings is queued for the old partner using a unique execution key. Sources: [apps/web/lib/api/customers/reattribute-customer.ts:729-771](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/customers/reattribute-customer.ts#L729-L771)
```typescript
if (plan.shouldClearIdentifiers && plan.paidCommissionIds.length > 0) {
await prisma.commission.updateMany({
where: {
id: { in: plan.paidCommissionIds },
},
data: {
eventId: null,
invoiceId: null,
},
});
}
await runOnce({
key: `reattribute-customer:${oldCustomerId}:clawback`,
fn: async () => {
const existingClawback = await prisma.commission.findFirst({
where: {
customerId: oldCustomerId,
partnerId: oldPartnerId,
programId,
type: "custom",
description: "tracking_error",
earnings: { lt: 0 },
},
select: { id: true },
});
if (existingClawback) {
return;
}
await queuePartnerCommissionCreation({
event: "custom",
partnerId: oldPartnerId,
programId,
customerId: oldCustomerId,
amount: -plan.paidEarnings,
quantity: 1,
description: "tracking_error",
skipWorkflow: true,
});
},
});
```
Sources: [apps/web/lib/api/customers/reattribute-customer.ts:729-771](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/customers/reattribute-customer.ts#L729-L771)
### Replacement commission routing
New lead and sale events are queried for the incoming customer and filtered against existing commissions to prevent duplicates. Lead reattribution evaluates whether the old customer generated a paid lead while the new partner has no existing lead. Sale events match paid invoice identifiers or supplement invoiceless sales up to the prior paid count. Sources: [apps/web/lib/api/customers/reattribute-customer.ts:773-821](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/customers/reattribute-customer.ts#L773-L821)
```typescript
const [newEvents, existingNewCommissions] = await Promise.all([
getCustomerReattributeEvents(newCustomerId),
prisma.commission.findMany({
where: {
customerId: newCustomerId,
partnerId: newPartnerId,
type: { in: ["lead", "sale"] },
},
select: {
type: true,
invoiceId: true,
},
}),
]);
const hasExistingLead = existingNewCommissions.some(
(commission) => commission.type === "lead",
);
const leadEvent =
plan.hasPaidLead && !hasExistingLead
? newEvents.find((event) => event.event === "lead") ?? null
: null;
const paidInvoiceIds = new Set(plan.paidInvoiceIds);
const existingInvoiceIds = new Set(
existingNewCommissions
.map((commission) => commission.invoiceId)
.filter((invoiceId): invoiceId is string => Boolean(invoiceId)),
);
const existingInvoicelessCount = existingNewCommissions.filter(
(commission) => commission.type === "sale" && !commission.invoiceId,
).length;
const saleEvents = [
...newEvents.filter(
(event) =>
event.event === "sale" &&
event.invoice_id &&
paidInvoiceIds.has(event.invoice_id) &&
!existingInvoiceIds.has(event.invoice_id),
),
...newEvents
.filter((event) => event.event === "sale" && !event.invoice_id)
.slice(
0,
Math.max(0, plan.paidInvoicelessCount - existingInvoicelessCount),
),
];
```
Sources: [apps/web/lib/api/customers/reattribute-customer.ts:773-821](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/customers/reattribute-customer.ts#L773-L821)
> [!CAUTION]
> Customer reattribution requires running synchronization across both old and old/new partner totals via `syncTotalCommissions` immediately after queueing replacement events to prevent payout imbalances. Sources: [apps/web/lib/api/customers/reattribute-customer.ts:864-874](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/customers/reattribute-customer.ts#L864-L874)
## Status tracking and balance aggregation
### Activity log auditing
Dub tracks commission updates and status transitions by parsing activity change sets and recording structured snapshots. The `trackCommissionStatusUpdate` utility captures bulk operations such as aggregation, bans, and payouts by mapping commission records and generating activity logs through `trackCommissionActivityLog` with `amount`, `earnings`, and `status` fields. Sources: [apps/web/lib/api/commissions/track-commission-update-activity-log.ts:79-99](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/track-commission-update-activity-log.ts#L79-L99)
```typescript
export async function trackCommissionStatusUpdate({
commissions,
newStatus,
...baseInput
}: {
commissions: Pick[];
newStatus: CommissionStatus;
} & Pick) {
if (commissions.length === 0) {
return;
}
return trackCommissionActivityLog({
...baseInput,
old: commissions,
new: commissions.map((commission) => ({
...commission,
status: newStatus,
})),
});
}
```
Sources: [apps/web/lib/api/commissions/track-commission-update-activity-log.ts:79-99](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/track-commission-update-activity-log.ts#L79-L99)
> [!NOTE]
> When processing payouts spanning multiple programs, `trackCommissionStatusUpdatesByProgram` groups commissions by program ID and resolves the correct workspace context before invoking individual status updates. Sources: [apps/web/lib/api/commissions/track-commission-update-activity-log.ts:103-142](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/track-commission-update-activity-log.ts#L103-L142)
### Redis stream events and synchronization
Partner commission totals are synchronized using Upstash Redis streams via `syncTotalCommissions`. When an event is published, the system attempts to dispatch through the Redis stream infrastructure. If a failure occurs, it falls back to a direct database transaction that aggregates earnings across non-zero, active commission states (`pending`, `processed`, and `paid`) to update the corresponding `ProgramEnrollment` record. Sources: [apps/web/lib/api/partners/sync-total-commissions.ts:37-59](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/partners/sync-total-commissions.ts#L37-L59)
```typescript
export const syncTotalCommissions = async ({
partnerId,
programId,
}: {
partnerId: string;
programId: string;
}) => {
try {
return await publishPartnerActivityEvent({
programId,
partnerId,
eventType: "commission",
timestamp: new Date().toISOString(),
});
} catch (error) {
console.error(
`[syncTotalCommissionsError]: Failed to sync total commissions for partner ${partnerId} in program ${programId}, falling back to direct database update...`,
error,
);
return await aggregateAndUpdateTotalCommissions({ partnerId, programId });
}
};
```
Sources: [apps/web/lib/api/partners/sync-total-commissions.ts:37-59](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/partners/sync-total-commissions.ts#L37-L59)
> [!CAUTION]
> Direct database aggregation inside `aggregateAndUpdateTotalCommissions` executes within a Prisma transaction (`tx`) to prevent race conditions when concurrent payouts or status updates modify enrollment totals. Sources: [apps/web/lib/api/partners/sync-total-commissions.ts:11-34](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/partners/sync-total-commissions.ts#L11-L34)
## Commission querying and analytics reporting
### Filtering and pagination parameters
The commission retrieval engine processes requests through `getCommissions` by evaluating filters across identifiers, metadata queries, and pagination constraints. When an `invoiceId` is supplied, the query searches uniquely within the specified program and metadata parameters. Otherwise, pagination queries use cursor-based navigation validated against `startingAfter` or `endingBefore` parameters. Sources: [apps/web/lib/api/commissions/get-commissions.ts:35-89](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/get-commissions.ts#L35-L89)
```typescript
export async function getCommissions(filters: CommissionsFilters) {
const {
invoiceId,
programId,
partnerId,
status,
type,
customerId,
payoutId,
bountySubmissionId,
groupId,
partnerTagId,
fraudEventGroupId,
start,
end,
interval,
timezone,
startingAfter,
endingBefore,
query,
} = filters;
// Metadata filter
const parsedMetadataQuery = parseCommissionMetadataQuery(query);
const metadataWhere = buildCommissionMetadataWhere(parsedMetadataQuery);
// InvoiceId is unique within a program
if (invoiceId) {
return await prisma.commission.findMany({
where: {
invoiceId,
programId,
...metadataWhere,
},
include: {
...commissionIncludes,
},
});
}
const paginationQuery = buildPaginationQuery(filters);
// Validate the provided cursor ID
const cursorId = startingAfter || endingBefore;
if (cursorId) {
const commission = await prisma.commission.findUnique({
where: {
id: cursorId,
},
select: {
id: true,
programId: true,
},
});
```
Sources: [apps/web/lib/api/commissions/get-commissions.ts:35-89](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/get-commissions.ts#L35-L89)
> [!CAUTION]
> If a cursor ID is provided in commission queries, the engine performs a validation lookup to ensure the record exists and belongs to the active `programId`, throwing a `unprocessable_entity` API error if the check fails. Sources: [apps/web/lib/api/commissions/get-commissions.ts:80-97](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/api/commissions/get-commissions.ts#L80-L97)
### Time-series aggregation endpoints
Time-series metrics are compiled via raw SQL queries in `getCommissionsTimeseries` using granularity mappings from `sqlGranularityMap`. The pipeline aggregates earnings grouped by time intervals, excluding duplicate, fraudulent, or canceled commission states while filtering out internal programs (`ACME_PROGRAM_ID` and `DEMO_PROGRAM_ID`) when no specific program is requested. Sources: [apps/web/app/(ee)/api/admin/commissions/get-commissions-timeseries.ts:13-41](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/admin/commissions/get-commissions-timeseries.ts#L13-L41)
```typescript
export async function getCommissionsTimeseries({
programId,
startDate,
endDate,
granularity,
timezone,
}: {
programId?: string;
startDate: Date;
endDate: Date;
granularity: string;
timezone: string;
}) {
const { dateFormat, dateIncrement, startFunction, formatString } =
sqlGranularityMap[granularity];
const commissions = await prisma.$queryRaw`
SELECT
DATE_FORMAT(CONVERT_TZ(createdAt, "UTC", ${timezone || "UTC"}), ${dateFormat}) AS start,
SUM(earnings) AS commissions
FROM Commission
WHERE
createdAt >= ${startDate}
AND createdAt < ${endDate}
AND status IN ("pending", "processed", "paid")
AND ${programId ? Prisma.sql`programId = ${programId}` : Prisma.sql`programId NOT IN (${ACME_PROGRAM_ID}, ${DEMO_PROGRAM_ID})`}
GROUP BY start
ORDER BY start ASC;`;
```
Sources: [apps/web/app/ee/api/admin/commissions/get-commissions-timeseries.ts:13-41](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/app/(ee)/api/admin/commissions/get-commissions-timeseries.ts#L13-L41)
## Related
- [Conversion Tracking](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/analytics-pipeline/conversion-tracking)
- [Reward Rules](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/partner-programs/reward-rules)
- [Payout Settlement](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/billing-and-payouts/payout-settlement)
## Sitemap
See the full [sitemap](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/llms.txt) for all pages in this wiki.