---
title: "Database Schema and Prisma"
description: "Dub's database architecture is built around a multi-file Prisma ORM setup configured for a MySQL datasource, connecting core multi-tenant workspaces, domains, and redirect links with advanced partn..."
last_updated: "2026-10-05T05:07:35.174967+00:00"
canonical_url: "https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/core-architecture/database-schema-and-prisma"
---

<details>
<summary>Relevant source files</summary>

The following files were used as context for generating this wiki page:

- [apps/web/prisma/schema/workspace.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/workspace.prisma)
- [apps/web/prisma/schema/link.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma)
- [apps/web/app/ee/api/partners/links/route.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts)
- [apps/web/prisma/schema/program.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma)
- [apps/web/prisma/schema/commission.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/commission.prisma)
- [apps/web/prisma/schema/reward.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/reward.prisma)
- [apps/web/prisma/schema/schema.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/schema.prisma)
- [apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts)
- [apps/web/lib/planetscale/get-link-with-partner.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/planetscale/get-link-with-partner.ts)
- [apps/web/lib/middleware/link.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/middleware/link.ts)
- [apps/web/scripts/dev/data.json](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/data.json)
- [apps/web/app/ee/admin.dub.co/dashboard/commissions/page.tsx](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/admin.dub.co/(dashboard)/commissions/page.tsx)
- [apps/web/app/ee/partners.dub.co/dashboard/referrals/page.tsx](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/partners.dub.co/(dashboard)/referrals/page.tsx)
- [apps/web/prisma/schema/misc.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/misc.prisma)
- [apps/web/lib/commissions/process-click-aggregation.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/commissions/process-click-aggregation.ts)
- [apps/web/lib/ai/get-program-performance.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/ai/get-program-performance.ts)
- [apps/web/lib/partner-referrals/create-referral-commission.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/partner-referrals/create-referral-commission.ts)
- [apps/web/lib/api/billing/recompute-workspace-usage.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/billing/recompute-workspace-usage.ts)
- [apps/web/prisma/schema/utm.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/utm.prisma)
- [apps/web/lib/fetchers/index.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/fetchers/index.ts)
- [apps/web/prisma/schema/group.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/group.prisma)
- [apps/web/scripts/dub-partner-rewind.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dub-partner-rewind.ts)
- [apps/web/prisma/schema/domain.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/domain.prisma)
- [apps/web/prisma/schema/partner.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/partner.prisma)
- [apps/web/scripts/migrations/backfill-commissions-metadata.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts)
- [apps/web/prisma/schema/discount.prisma](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/discount.prisma)
- [apps/web/scripts/dev/seed-commissions.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-commissions.ts)
- [apps/web/lib/partnerstack/import-commissions.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/partnerstack/import-commissions.ts)
- [apps/web/lib/api/commissions/get-commissions.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/commissions/get-commissions.ts)
- [apps/web/scripts/customers/beehiiv/fix-case-a-simple.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-a-simple.ts)
</details>

## Overview

Dub's database architecture is built around a multi-file Prisma ORM setup configured for a MySQL datasource, connecting core multi-tenant workspaces, domains, and redirect links with advanced partner program management, attribution, and analytics pipelines. Sources: [apps/web/prisma/schema/schema.prisma:1-9](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/schema.prisma#L1-L9), [apps/web/prisma/schema/workspace.prisma:1-105](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/workspace.prisma#L1-L105), [apps/web/prisma/schema/link.prisma:1-107](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L1-L107), [apps/web/prisma/schema/program.prisma:1-179](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L1-L179), [apps/web/prisma/schema/commission.prisma:1-87](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/commission.prisma#L1-L87), [apps/web/prisma/schema/reward.prisma:1-81](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/reward.prisma#L1-L81)

The schema addresses the complexities of distributed edge lookups, multi-tenant link shortening, and high-volume referral attribution by segregating schema logic across specialized domain files while maintaining strict relational constraints and indexing strategies for optimized read and write performance. Sources: [apps/web/prisma/schema/workspace.prisma:103-105](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/workspace.prisma#L103-L105), [apps/web/prisma/schema/link.prisma:96-106](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L96-L106), [apps/web/prisma/schema/commission.prisma:73-86](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/commission.prisma#L73-L86)

## Datasource and Multi-File Prisma Architecture

### Overview

Dub organizes its database schema using a multi-file layout under `apps/web/prisma/schema/`, separating core domain models into dedicated files such as `schema.prisma`, `workspace.prisma`, `link.prisma`, and `misc.prisma`. Sources: [apps/web/prisma/schema/schema.prisma:1-9](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/schema.prisma#L1-L9), [apps/web/prisma/schema/workspace.prisma:1-154](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/workspace.prisma#L1-L154), [apps/web/prisma/schema/link.prisma:1-108](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L1-L108), [apps/web/prisma/schema/misc.prisma:1-36](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/misc.prisma#L1-L36)

### MySQL Datasource Configuration

The primary connection and client code generation are defined in `schema.prisma`, establishing a MySQL provider with an environment variable-backed connection URL and Prisma relation mode. Sources: [apps/web/prisma/schema/schema.prisma:1-9](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/schema.prisma#L1-L9)

| Block / Directive | Type / Value | Default / Setting | Purpose |
| :--- | :--- | :--- | :--- |
| `datasource db` | provider | `"mysql"` | Configures Prisma to target a MySQL database backend. |
| `url` | env var | `env("DATABASE_URL")` | Specifies the connection string retrieved from environment configuration. |
| `relationMode` | string | `"prisma"` | Employs Prisma-emulated relations rather than foreign key constraints at the database engine level. |
| `generator client` | provider | `"prisma-client-js"` | Generates the type-safe JavaScript/TypeScript Prisma client library. |

Sources: [apps/web/prisma/schema/schema.prisma:1-9](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/schema.prisma#L1-L9)

### Client Usage Patterns

Data fetching utilities across Dub leverage the generated Prisma client wrapped with React's `cache` utility to retrieve workspaces, default projects, and individual short links. Sources: [apps/web/lib/fetchers/index.ts:1-68](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/fetchers/index.ts#L1-L68)

> [!NOTE]
> Database fetchers such as `getDefaultWorkspace`, `getWorkspace`, and `getLink` integrate session verification with React caching to avoid redundant queries during request lifecycles. Sources: [apps/web/lib/fetchers/index.ts:5-67](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/fetchers/index.ts#L5-L67)

The following example demonstrates the execution flow of fetching a link record by its domain and key composite identifier using the Prisma client instance:

```typescript
export const getLink = cache(
  async ({ domain, key }: { domain: string; key: string }) => {
    return await prisma.link.findUnique({
      where: {
        domain_key: {
          domain,
          key,
        },
      },
    });
  },
);
```

Sources: [apps/web/lib/fetchers/index.ts:56-67](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/fetchers/index.ts#L56-L67)

## Workspace, Domain, and Link Models

### Workspace, Domain, and Link Models

### Overview

The core data architecture for multi-tenant URL shortening centers around the `Project` (workspace) model, which acts as the parent container for domains, short links, UTM templates, team memberships, and product usage limits. Individual short links belong to specific workspaces and map to verified custom or system domains, while UTM templates standardize parameter injection across campaigns. Sources: [apps/web/prisma/schema/workspace.prisma:13-105](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/workspace.prisma#L13-L105), [apps/web/prisma/schema/link.prisma:1-107](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L1-L107), [apps/web/prisma/schema/domain.prisma:1-28](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/domain.prisma#L1-L28), [apps/web/prisma/schema/utm.prisma:1-28](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/utm.prisma#L1-L28)

### Workspace Usage and Plan Configurations

Workspaces configure billing tiers, subscription states, and quotas governing links, clicks, partner payouts, and AI features. The system tracks consumption against these limits to enforce billing policies and restrict over-quota operations. Sources: [apps/web/prisma/schema/workspace.prisma:13-61](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/workspace.prisma#L13-L61)

| Field Name | Type | Default / Constraints | Purpose |
| :--- | :--- | :--- | :--- |
| `id` | String | `@id` | Unique identifier for the workspace project. |
| `slug` | String | `@unique` | URL-friendly unique handle for the workspace. |
| `plan` | String | `"free"` | Subscription plan identifier (e.g., enterprise, free). |
| `planTier` | Int | `1` | Numeric tier level associated with the current plan. |
| `billingCycleStart` | Int | (required) | Day of the month when the billing cycle begins. |
| `totalLinks` | Int | `0` | Total number of links currently registered in the workspace. |
| `totalClicks` | Int | `0` | Aggregate number of link clicks recorded across the workspace. |
| `usage` | Int | `0` | Current click consumption counter for billing periods. |
| `usageLimit` | Int | `1000` | Maximum click allowance permitted under the active plan. |
| `linksUsage` | Int | `0` | Number of links consumed against the link quota. |
| `linksLimit` | Int | `25` | Maximum link creation cap allowed for the workspace tier. |
| `domainsLimit` | Int | `3` | Maximum custom domains allowed to be linked to the workspace. |
| `usersLimit` | Int | `1` | Maximum number of team members permitted in the workspace. |
| `aiUsage` | Int | `0` | Current consumption count for AI-powered features. |
| `aiLimit` | Int | `10` | Maximum allowance for AI features per cycle. |

Sources: [apps/web/prisma/schema/workspace.prisma:13-61](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/workspace.prisma#L13-L61)

> [!NOTE]
> Workspace usage recomputation executes asynchronously by aggregating raw event and invoice records over the active billing window defined by `billingCycleStart`. Sources: [apps/web/lib/api/billing/recompute-workspace-usage.ts:8-50](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/billing/recompute-workspace-usage.ts#L8-L50)

### Usage Recomputation Call-Chain Walkthrough

The workspace billing and usage synchronization routine evaluates current consumption metrics by querying analytics stores and financial records in parallel. The execution path follows a strict function sequence:

1. `recomputeWorkspaceUsage()` receives the workspace object containing its `id` and `billingCycleStart` property. Sources: [apps/web/lib/api/billing/recompute-workspace-usage.ts:8-10](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/billing/recompute-workspace-usage.ts#L8-L10)
2. `getBillingStartDate()` calculates the exact starting timestamp based on the billing cycle day. Sources: [apps/web/lib/api/billing/recompute-workspace-usage.ts:3-11](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/billing/recompute-workspace-usage.ts#L3-L11)
3. `Promise.all()` concurrently invokes `getWorkspaceUsage()` twice (querying resource `"events"` for clicks and `"links"` for link creations) alongside `prisma.invoice.aggregate()` to sum completed partner payouts. Sources: [apps/web/lib/api/billing/recompute-workspace-usage.ts:14-43](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/billing/recompute-workspace-usage.ts#L14-L43)
4. The helper function `sum()` processes the mapped metric arrays to produce aggregate totals for `usage`, `linksUsage`, and `payoutsUsage`. Sources: [apps/web/lib/api/billing/recompute-workspace-usage.ts:6-49](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/billing/recompute-workspace-usage.ts#L6-L49)

```typescript
export async function recomputeWorkspaceUsage(
  workspace: Pick<Project, "id" | "billingCycleStart">,
) {
  const billingStart = getBillingStartDate(workspace.billingCycleStart);
  const billingEnd = new Date();

  const [clicksData, linksData, payoutsUsage] = await Promise.all([
    getWorkspaceUsage({
      workspaceId: workspace.id,
      resource: "events",
      start: billingStart,
      end: billingEnd,
    }),

    getWorkspaceUsage({
      workspaceId: workspace.id,
      resource: "links",
      start: billingStart,
      end: billingEnd,
    }),

    prisma.invoice.aggregate({
      where: {
        workspaceId: workspace.id,
        type: "partnerPayout",
        status: "completed",
        paidAt: {
          gte: billingStart,
          lte: billingEnd,
        },
      },
      _sum: {
        amount: true,
      },
    }),
  ]);

  return {
    usage: sum(clicksData.map((d) => d.value)),
    linksUsage: sum(linksData.map((d) => d.value)),
    payoutsUsage: payoutsUsage._sum.amount ?? 0,
  };
}
```

Sources: [apps/web/lib/api/billing/recompute-workspace-usage.ts:8-50](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/billing/recompute-workspace-usage.ts#L8-L50)

### Link, Domain, and UTM Schema Models

Short links store routing configurations, Open Graph metadata, A/B testing variants, expiration rules, and native UTM tracking parameters (`utm_source`, `utm_medium`, `utm_campaign`, `utm_term`, `utm_content`). Domains regulate verification status and link retention policies, while UTM templates store reusable parameter sets for campaigns. Sources: [apps/web/prisma/schema/link.prisma:1-95](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L1-L95), [apps/web/prisma/schema/domain.prisma:1-28](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/domain.prisma#L1-L28), [apps/web/prisma/schema/utm.prisma:1-24](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/utm.prisma#L1-L24)

> [!WARNING]
> Short links do not index full target URLs directly; instead, they maintain a length-constrained index on `[projectId, url(length: 500)]` to support high-performance URL upsert and deduplication routines without violating database key length limitations. Sources: [apps/web/prisma/schema/link.prisma:99|99](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L99-L99)

## Edge Link Lookups and Caching

### Edge Link Lookups and Caching

### Overview

Edge link resolution in Dub relies on high-performance caching layers and direct database queries executed via PlanetScale. When an incoming request reaches the edge middleware, the system parses the hostname and URL path to isolate the target domain and link key. Depending on whether the domain is configured for case sensitivity, the lookup key undergoes ASCII punyencoding and potential lowercasing before querying the underlying datastores. Sources: [apps/web/lib/middleware/link.ts:43-63](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/middleware/link.ts#L43-L63), [apps/web/lib/planetscale/get-link-with-partner.ts:24-61](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/planetscale/get-link-with-partner.ts#L24-L61)

### Link Resolution Call-Chain Walkthrough

The runtime resolution of a short link and its associated partner data follows a precise execution sequence across the middleware and PlanetScale query layer:

1. `LinkMiddleware()` extracts the incoming request domain, full key, and search parameters using the `parse()` helper function. Sources: [apps/web/lib/middleware/link.ts:43-44](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/middleware/link.ts#L43-L44)
2. The original key is encoded to ASCII via `punyEncode()`, and if the domain is not case-sensitive, it is normalized to lowercase. Sources: [apps/web/lib/middleware/link.ts:50-56](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/middleware/link.ts#L50-L56)
3. If inspect mode is triggered by a trailing `+` character, the suffix is stripped from the key before further processing. Sources: [apps/web/lib/middleware/link.ts:58-62](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/middleware/link.ts#L58-L62)
4. `getLinkWithPartner()` checks domain case sensitivity to apply either `encodeKey()` or a combination of `safeDecodeURIComponent()` and `punyEncode()` to construct `keyToQuery`. Sources: [apps/web/lib/planetscale/get-link-with-partner.ts:24-34](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/planetscale/get-link-with-partner.ts#L24-L34)
5. `conn.execute()` runs a relational query joining `Link` with `ProgramEnrollment`, `Partner`, `LinkReward`, `Discount`, and `Program` tables using `domain` and `keyToQuery`. Sources: [apps/web/lib/planetscale/get-link-with-partner.ts:37-60](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/planetscale/get-link-with-partner.ts#L37-L60)

```typescript
export const getLinkWithPartner = async ({
  domain,
  key,
}: {
  domain: string;
  key: string;
}): Promise<QueryResult | null> => {
  const keyToQuery = isCaseSensitiveDomain(domain)
    ? encodeKey(key)
    : punyEncode(safeDecodeURIComponent(key));

  console.time("getLinkWithPartner");

  const { rows } =
    (await conn.execute(
      `SELECT 
        Link.*,
        Partner.id as partnerId,
        Partner.name as partnerName,
        Partner.image as partnerImage,
        ProgramEnrollment.groupId as groupId,
        ProgramEnrollment.tenantId as tenantId,
        PartnerDiscount.id as discountId,
        PartnerDiscount.amount as discountAmount,
        PartnerDiscount.type as discountType,
        PartnerDiscount.maxDuration as discountMaxDuration,
        PartnerDiscount.couponId as discountCouponId,
        PartnerDiscount.couponTestId as discountCouponTestId
       FROM Link
       LEFT JOIN ProgramEnrollment ON ProgramEnrollment.programId = Link.programId AND ProgramEnrollment.partnerId = Link.partnerId
       LEFT JOIN Partner ON Partner.id = ProgramEnrollment.partnerId
       LEFT JOIN LinkReward ON LinkReward.linkId = Link.id
       LEFT JOIN Discount PartnerDiscount ON PartnerDiscount.id = COALESCE(LinkReward.discountId, ProgramEnrollment.discountId)
         AND PartnerDiscount.programId IS NOT NULL
       LEFT JOIN Program ON Program.id = Link.programId
       WHERE Link.domain = ? AND Link.key = ?`,
      [domain, keyToQuery],
    )) || {};

  console.timeEnd("getLinkWithPartner");

  const link =
    rows && Array.isArray(rows) && rows.length > 0 ? (rows[0] as any) : null;

  if (!link) {
    return null;
  }
  // ... maps results and returns EdgeLinkProps
};
```

Sources: [apps/web/lib/planetscale/get-link-with-partner.ts:24-70](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/planetscale/get-link-with-partner.ts#L24-L70)

### Link Database Schema Indexing Strategy

The `Link` model defines dedicated unique constraints and indexes to guarantee sub-millisecond retrieval performance across various lookup vectors at the edge.

| Composite Key / Index | Fields Covered | Purpose / Target Operation |
| :--- | :--- | :--- |
| Unique Constraint | `[domain, key]` | Direct short link retrieval by domain and path key. Sources: [apps/web/prisma/schema/link.prisma:96|96](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L96-L96) |
| Unique Constraint | `[projectId, externalId]` | API usage and multi-tenant lookups via external identifiers. Sources: [apps/web/prisma/schema/link.prisma:97|97](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L97-L97) |
| Index | `[projectId, tenantId]` | Tenant-scoped filtering for workspace resource queries. Sources: [apps/web/prisma/schema/link.prisma:98|98](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L98-L98) |
| Index | `[projectId, url(length: 500)]` | High-performance URL upserts and duplicate detection. Sources: [apps/web/prisma/schema/link.prisma:99|99](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L99-L99) |
| Index | `[programId, partnerId]` | Referral link lookups combining program and partner associations. Sources: [apps/web/prisma/schema/link.prisma:101|101](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L101-L101) |
| Index | `[domain, createdAt]` | Bulk link deletion workflows and cleanup of short-lived links. Sources: [apps/web/prisma/schema/link.prisma:103|103](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L103-L103) |

Sources: [apps/web/prisma/schema/link.prisma:96-107](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L96-L107)

> [!NOTE]
> Link expiration timestamps (`expiresAt`) are synchronized with Redis Time-To-Live (TTL) values, whereas target URLs (`url`), proxy configurations (`proxy`), domains (`domain`), and link keys (`key`) are mirrored directly on Redis nodes for edge acceleration alongside primary MySQL storage. Sources: [apps/web/prisma/schema/link.prisma:3-8|13|13](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/link.prisma#L3-L8)

## Partner Programs and Link Associations

### Overview

The partner program architecture in Dub relies on a multi-model relational schema connecting workspaces, programs, partner enrollments, partner groups, and partner-specific link creation endpoints. A partner program (`Program`) acts as the top-level container defined within a workspace (`Project`), establishing primary reward settings, domains, and payout modes. Partners (`Partner`) enroll in programs through `ProgramEnrollment` records, which track performance metrics, reward overrides, and status lifecycle values. Partners can also be organized into `PartnerGroup` entities, defining group-level link structures, default reward assignments, and UTM template configurations.

Sources: [apps/web/prisma/schema/program.prisma:27-99](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L27-L99), [apps/web/prisma/schema/group.prisma:7-54](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/group.prisma#L7-L54)

### Partner Enrollment Statuses and Lifecycle

Program enrollments govern the state of a partner inside a specific program using the `ProgramEnrollmentStatus` enum. Each status handles a distinct phase of partner onboarding, review, or deactivation.

| Status Name | Description / Meaning |
| :--- | :--- |
| `pending` | Pending applications that need administrative approval. Sources: [apps/web/prisma/schema/program.prisma:2|2](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L2-L2) |
| `approved` | Partner who has been approved or actively enrolled. Sources: [apps/web/prisma/schema/program.prisma:3|3](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L3-L3) |
| `rejected` | Program rejected the partner application. Sources: [apps/web/prisma/schema/program.prisma:4|4](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L4-L4) |
| `invited` | Partner who has received an invitation to join. Sources: [apps/web/prisma/schema/program.prisma:5|5](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L5-L5) |
| `declined` | Partner declined the program invitation. Sources: [apps/web/prisma/schema/program.prisma:6|6](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L6-L6) |
| `deactivated` | Partner is manually deactivated by the program. Sources: [apps/web/prisma/schema/program.prisma:7|7](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L7-L7) |
| `banned` | Partner is banned from participating in the program. Sources: [apps/web/prisma/schema/program.prisma:8|8](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L8-L8) |
| `archived` | Partner record is archived by the program. Sources: [apps/web/prisma/schema/program.prisma:9|9](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L9-L9) |

Sources: [apps/web/prisma/schema/program.prisma:1-10](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L1-L10)

> [!IMPORTANT]
> The `ProgramEnrollment` model enforces unique constraints on both `[partnerId, programId]` and `[tenantId, programId]`, ensuring that a single partner or tenant cannot duplicate active enrollments within the same program boundary. Sources: [apps/web/prisma/schema/program.prisma:174-177](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/program.prisma#L174-L177)

### Partner Link Creation Call-Chain Walkthrough

Partner-specific links are created via the POST endpoint at `apps/web/app/ee/api/partners/links/route.ts`. The request execution follows an explicit validation and processing sequence before persisting the link:

1. `createPartnerLinkSchemaInternal.parse()` validates the incoming JSON request body for partner identifiers, custom URLs, keys, and reward IDs. Sources: [apps/web/app/ee/api/partners/links/route.ts:98-108](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts#L98-L108)
2. `getProgramOrThrow()` fetches the target program and verifies that its domain and base URL are configured. Sources: [apps/web/app/ee/api/partners/links/route.ts:110-121](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts#L110-L121)
3. `prisma.programEnrollment.findUnique()` queries the database using either `partnerId` or `tenantId` combined with `programId`, loading partner relation fields and associated `partnerGroup` defaults and UTM templates. Sources: [apps/web/app/ee/api/partners/links/route.ts:125-144](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts#L125-L144)
4. `processLink()` executes core link validation and generation logic, registering the target URL, domain, program ID, tenant ID, and partner ID. Sources: [apps/web/app/ee/api/partners/links/route.ts:165-180](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts#L165-L180)
5. `applyGroupUtmToLink()` applies group UTM template parameters and partner naming conventions to the generated link object. Sources: [apps/web/app/ee/api/partners/links/route.ts:189-193](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts#L189-L193)
6. `throwIfInvalidRewards()` validates link-level reward assignments against the program and partner group constraints. Sources: [apps/web/app/ee/api/partners/links/route.ts:216-220](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts#L216-L220)
7. `createLink()` persists the final constructed link record with optional reward overrides. Sources: [apps/web/app/ee/api/partners/links/route.ts:222-225](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts#L222-L225)

Sources: [apps/web/app/ee/api/partners/links/route.ts:94-225](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts#L94-L225)

> [!WARNING]
> If a partner attempts to create a link with custom link-level rewards (`clickRewardId`, `leadRewardId`, `saleRewardId`, or `discountId`), the workspace plan capability check (`canUseAdvancedRewardLogic`) is evaluated. If the workspace plan does not permit advanced reward logic, the operation throws a forbidden `DubApiError` with the `PARTNER_LEVEL_REWARDS_PLAN_ERROR` constant. Sources: [apps/web/app/ee/api/partners/links/route.ts:197-214](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/partners/links/route.ts#L197-L214)

## Commissions, Rewards, and Aggregations

### Overview

The reward and commission data architecture manages partner compensation, event tracking, and periodic click aggregation. Rewards define payout structures and constraints using fixed or percentage models across event types, while commissions record individual financial ledger entries tied to clicks, leads, sales, or referrals.

Sources: [apps/web/prisma/schema/commission.prisma:37-62](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/commission.prisma#L37-L62), [apps/web/prisma/schema/reward.prisma:21-38](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/reward.prisma#L21-L38)

### Reward and Commission Enumerations

The Prisma schema defines specific enumeration sets governing event classification, payout structures, commission statuses, and data origins.

| Enum Type | Real Values | Description |
| :--- | :--- | :--- |
| `EventType` / `CommissionType` | `click`, `lead`, `sale`, `referral`, `custom` | Categorizes the tracked interaction triggering rewards or commissions. |
| `RewardStructure` | `percentage`, `flat` | Defines whether compensation is calculated as a percentage rate or a flat cash amount. |
| `RewardSpendLimitInterval` | `allTime`, `day`, `week`, `month` | Defines the time window for bounding reward spend caps. |
| `CommissionStatus` | `pending`, `processed`, `paid`, `refunded`, `duplicate`, `fraud`, `canceled`, `hold` | Represents the lifecycle state of a commission entry. |
| `CommissionSource` | `api`, `user`, `stripe`, `shopify`, `hubspot`, `appsflyer`, `singular`, `rewardful`, `partnerstack`, `firstpromoter`, `tolt`, `tapfiliate`, `lemonsqueezy`, `affiliatewp` | Identifies the originating integration platform or creation context for a commission. |

Sources: [apps/web/prisma/schema/commission.prisma:1-35](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/commission.prisma#L1-L35), [apps/web/prisma/schema/reward.prisma:1-20](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/reward.prisma#L1-L20)

### Click Aggregation Pipeline Call-Chain Walkthrough

The periodic click aggregation pipeline processes link engagement data and batches click commissions. The background execution follows an explicit function call sequence:

1. `processClickAggregation()` queries `prisma.programEnrollment.findUnique()` using partner and program IDs, checking whether the enrollment status is commission-eligible. Sources: [apps/web/lib/commissions/process-click-aggregation.ts:39-70](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/commissions/process-click-aggregation.ts#L39-L70)
2. `calculateEarningsByLink()` queries active links with clicks after the start date, fetches geographic click distributions via `getTopLinksByCountries()`, and calculates link-level earnings against configured click rewards. Sources: [apps/web/lib/commissions/process-click-aggregation.ts:90-148](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/commissions/process-click-aggregation.ts#L90-L148)
3. `createClickCommissions()` iterates over link earnings, evaluating reward spend limits via `getHistoricalClicksEarnings()` and building ledger entries with idempotency keys. Sources: [apps/web/lib/commissions/process-click-aggregation.ts:254-349](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/commissions/process-click-aggregation.ts#L254-L349)
4. `prisma.commission.createMany()` persists the batched click commissions with `skipDuplicates: true`. Sources: [apps/web/lib/commissions/process-click-aggregation.ts:359-362](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/commissions/process-click-aggregation.ts#L359-L362)
5. `syncTotalCommissions()` updates aggregate partner program totals following successful insertion. Sources: [apps/web/lib/commissions/process-click-aggregation.ts:369-373](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/commissions/process-click-aggregation.ts#L369-L373)

Sources: [apps/web/lib/commissions/process-click-aggregation.ts:33-374](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/commissions/process-click-aggregation.ts#L33-L374)

> [!NOTE]
> Click commissions rely on `invoiceId` formatted as `${linkId}-${aggregationDate}` combined with a unique constraint on `[invoiceId, programId]` to ensure idempotency during batch aggregation runs. Sources: [apps/web/prisma/schema/commission.prisma:73|73](https://github.com/blade47/dub/blob/HEAD/apps/web/prisma/schema/commission.prisma#L73-L73), [apps/web/lib/commissions/process-click-aggregation.ts:348|348](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/commissions/process-click-aggregation.ts#L348-L348)

### Commission Retrieval and Filtering API

The `getCommissions()` function queries commission records for admin dashboards and partner portals, applying dynamic status filters and metadata predicates.

```typescript
export async function getCommissions(filters: CommissionsFilters) {
  const { invoiceId, programId, partnerId, status, type, ... } = filters;
  const parsedMetadataQuery = parseCommissionMetadataQuery(query);
  const metadataWhere = buildCommissionMetadataWhere(parsedMetadataQuery);

  if (invoiceId) {
    return await prisma.commission.findMany({
      where: { invoiceId, programId, ...metadataWhere },
      include: { ...commissionIncludes },
    });
  }

  const paginationQuery = buildPaginationQuery(filters);
  const statusFilter = status
    ? status
    : type || customerId || payoutId || bountySubmissionId || partnerId
      ? undefined
      : {
          notIn: [
            CommissionStatus.duplicate,
            CommissionStatus.fraud,
            CommissionStatus.canceled,
          ],
        };

  return await prisma.commission.findMany({
    where: {
      earnings: { not: 0 },
      programId,
      status: statusFilter,
      createdAt: { gte: startDate, lte: endDate },
      ...metadataWhere,
    },
    include: { ...commissionIncludes },
    ...paginationQuery,
  });
}
```

Sources: [apps/web/lib/api/commissions/get-commissions.ts:35-209](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/commissions/get-commissions.ts#L35-L209)

> [!WARNING]
> When querying commissions without an explicit status filter, duplicate, fraud, and canceled commissions are automatically excluded from the result set unless specifically requested via filters. Sources: [apps/web/lib/api/commissions/get-commissions.ts:141-151](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/commissions/get-commissions.ts#L141-L151)

## Batch Migrations and Performance Optimizations

### Overview

Bulk data migrations and performance tuning require careful handling of batch sizes, transaction boundaries, and pagination constants when interacting with PlanetScale and Prisma. Large-scale data transformations, such as backfilling metadata or correcting partner link alignments, use bounded iteration loops and explicit limits to prevent memory exhaustion and database lock contention. Sources: [apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts:101-125](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts#L101-L125), [apps/web/scripts/migrations/backfill-commissions-metadata.ts:25-30](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L25-L30)

### Metadata Backfill Execution Workflow

The commission metadata backfilling script processes records in batched loops, pulling source event metadata from Tinybird and performing bulk updates via raw SQL execution. Sources: [apps/web/scripts/migrations/backfill-commissions-metadata.ts:115-246](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L115-L246)

1. `main()` initiates an infinite iteration loop governed by a fixed `BATCH_SIZE` of `1000` records and a throttle delay of `1000` ms. Sources: [apps/web/scripts/migrations/backfill-commissions-metadata.ts:26-27](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L26-L27), [apps/web/scripts/migrations/backfill-commissions-metadata.ts:115-116](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L115-L116)
2. `prisma.commission.findMany()` queries target commissions where `type` is lead or sale, `eventId` is not null, `metadata` is null, and `id` is greater than the current cursor (`startingAfter`), ordered ascending by `id`. Sources: [apps/web/scripts/migrations/backfill-commissions-metadata.ts:116-142](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L116-L142)
3. `getEventsMetadata()` executes a Tinybird pipe query using extracted event IDs to retrieve corresponding event metadata payloads. Sources: [apps/web/scripts/migrations/backfill-commissions-metadata.ts:150-159](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L150-L159)
4. `parseUserProvidedMetadata()` validates raw string payloads against length bounds (`USER_METADATA_MAX_CHARS` = `10,000`), JSON syntax, and internal event fingerprint checks (`looksLikeInternalEventPayload()`) to filter out webhook dumps. Sources: [apps/web/scripts/migrations/backfill-commissions-metadata.ts:28-29](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L28-L29), [apps/web/scripts/migrations/backfill-commissions-metadata.ts:50-98](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L50-L98)
5. `prisma.$executeRaw` applies conditional case statements (`CASE id WHEN ... THEN ... END`) to update commission records in bulk when `DRY_RUN` is disabled. Sources: [apps/web/scripts/migrations/backfill-commissions-metadata.ts:25](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L25), [apps/web/scripts/migrations/backfill-commissions-metadata.ts:212-226](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L212-L226)

Sources: [apps/web/scripts/migrations/backfill-commissions-metadata.ts:100-251](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L100-L251)

> [!WARNING]
> When executing bulk `updateMany` operations on tables with large volumes of pending or un-processed records, always paginate or apply a `limit` parameter (such as `PRISMA_UPDATEMANY_LIMIT`) inside a `while (true)` loop to prevent exceeding lock timeouts or memory thresholds on PlanetScale. Sources: [apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts:101-125](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts#L101-L125)

### Migration Script Constants and Parameters

| Constant Name | Value | Purpose | Sources |
| --- | --- | --- | --- |
| `DRY_RUN` | `true` | Safeguard flag for backfill scripts to log proposed updates without executing database writes. | [apps/web/scripts/migrations/backfill-commissions-metadata.ts:25](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L25) |
| `BATCH_SIZE` | `1000` | Number of records fetched and processed per iteration cycle in backfill and rewind scripts. | [apps/web/scripts/dub-partner-rewind.ts:45](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dub-partner-rewind.ts#L45), [apps/web/scripts/migrations/backfill-commissions-metadata.ts:26](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L26) |
| `THROTTLE_MS` | `1000` | Millisecond sleep duration between migration batches to prevent resource exhaustion. | [apps/web/scripts/migrations/backfill-commissions-metadata.ts:27](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L27) |
| `USER_METADATA_MAX_CHARS` | `10000` | Maximum character length allowed for user-provided metadata strings, matching core Zod schemas. | [apps/web/scripts/migrations/backfill-commissions-metadata.ts:29](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L29) |
| `REWIND_EARNINGS_MINIMUM` | `100` (1 USD) | Minimum total commission earnings threshold in cents required to include a partner in rewind summaries. | [apps/web/scripts/dub-partner-rewind.ts:6](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dub-partner-rewind.ts#L6) |

Sources: [apps/web/scripts/dub-partner-rewind.ts:6-45](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dub-partner-rewind.ts#L6-L45), [apps/web/scripts/migrations/backfill-commissions-metadata.ts:25-29](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L25-L29)

### Design Trade-offs in Bulk Operations

| Design Choice | Benefit | Cost | Sources |
| --- | --- | --- | --- |
| Chunked transactions (`chunk(payloads, 1000)`) | Avoids query size limits and memory spikes during bulk table rewinds. | Requires multiple sequential database round-trips inside transaction blocks. | [apps/web/scripts/dub-partner-rewind.ts:45-61](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dub-partner-rewind.ts#L45-L61) |
| Raw SQL `CASE id WHEN` updates | Enables high-performance batch updates of distinct scalar values across non-uniform rows in a single query. | Bypasses Prisma client-level type safety and middleware hooks for that operation. | [apps/web/scripts/migrations/backfill-commissions-metadata.ts:212-226](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L212-L226) |
| Cursor-based pagination (`id > startingAfter`) | Provides stable, memory-efficient traversal over large sorted datasets without offset degradation. | Requires ordered primary or unique keys and complicates random-access navigation. | [apps/web/scripts/migrations/backfill-commissions-metadata.ts:127-142](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L127-L142) |

Sources: [apps/web/scripts/dub-partner-rewind.ts:45-61](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dub-partner-rewind.ts#L45-L61), [apps/web/scripts/migrations/backfill-commissions-metadata.ts:127-226](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-commissions-metadata.ts#L127-L226)

## Related

- [Caching and Edge Config](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/core-architecture/caching-and-edge-config)
- [Link Creation and Builder UI](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/link-management/link-creation-and-builder-ui)
- [Commission Rules and Rewards](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/affiliate-platform/commission-rules-and-rewards)


## Sitemap

See the full [sitemap](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/llms.txt) for all pages in this wiki.