---
title: "Database Architecture"
description: "Dub’s database architecture powers a high-performance link attribution and affiliate network platform built on MySQL, Prisma ORM, and PlanetScale serverless infrastructure. It utilizes a dual-clien..."
last_updated: "2026-10-12T07:00:10.095215+00:00"
canonical_url: "https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/core-architecture/database-architecture"
---

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

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

- [apps/web/lib/prisma/edge.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/prisma/edge.ts)
- [apps/web/prisma/schema/schema.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/schema.prisma)
- [apps/web/prisma/schema/jackson.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/jackson.prisma)
- [apps/web/scripts/dev/seed.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/dev/seed.ts)
- [apps/web/prisma/schema/oauth.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/oauth.prisma)
- [apps/web/prisma/schema/integration.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/integration.prisma)
- [apps/web/lib/planetscale/index.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/index.ts)
- [apps/web/prisma/schema/workspace.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/workspace.prisma)
- [apps/web/lib/prisma/index.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/prisma/index.ts)
- [apps/web/prisma/schema/program.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/program.prisma)
- [apps/web/prisma/schema/misc.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/misc.prisma)
- [apps/web/prisma/schema/comment.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/comment.prisma)
- [apps/web/lib/planetscale/connection.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/connection.ts)
- [apps/web/prisma/schema/network.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/network.prisma)
- [apps/web/prisma/schema/tag.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/tag.prisma)
- [apps/web/prisma/schema/application.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/application.prisma)
- [apps/web/prisma/schema/reward.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/reward.prisma)
- [apps/web/lib/planetscale/types.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/types.ts)
- [apps/web/prisma/schema/token.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/token.prisma)
- [apps/web/scripts/dev/seed-100k-partners.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/dev/seed-100k-partners.ts)
- [apps/web/lib/fetchers/index.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/fetchers/index.ts)
- [apps/web/prisma/schema/domain.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/domain.prisma)
- [apps/web/prisma/schema/job.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/job.prisma)
- [apps/web/prisma/schema/utm.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/utm.prisma)
- [apps/web/scripts/misc/restore-program-enrollments.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/misc/restore-program-enrollments.ts)
- [apps/web/prisma/schema/platform.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/platform.prisma)
- [apps/web/prisma/schema/link.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/link.prisma)
- [apps/web/lib/planetscale/get-shortlink-via-edge.ts](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/get-shortlink-via-edge.ts)
- [apps/web/prisma/schema/discount.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/discount.prisma)
- [apps/web/prisma/schema/fraud.prisma](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/fraud.prisma)
</details>

## Overview

Dub’s database architecture powers a high-performance link attribution and affiliate network platform built on MySQL, Prisma ORM, and PlanetScale serverless infrastructure. It utilizes a dual-client setup with `@prisma/client` for traditional Node.js runtimes and `@prisma/adapter-planetscale` running over the `@planetscale/database` HTTP driver for edge environments, alongside raw SQL connection helpers. Sources: [apps/web/lib/prisma/edge.ts:1-13](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/prisma/edge.ts#L1-L13), [apps/web/lib/prisma/index.ts:1-20](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/prisma/index.ts#L1-L20), [apps/web/lib/planetscale/connection.ts:1-5](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/connection.ts#L1-L5)

The relational schema is modularly decomposed into dedicated Prisma files covering core user identity, workspaces, domains, links, tags, [partner programs](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/partner-programs/partner-programs), [OAuth apps](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/integrations-subsystem/oauth-apps), security tokens, enterprise SSO indexes, fraud rule tracking, and fallback background jobs. Sources: [apps/web/prisma/schema/schema.prisma:1-95](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/schema.prisma#L1-L95), [apps/web/prisma/schema/workspace.prisma:1-153](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/workspace.prisma#L1-L153), [apps/web/prisma/schema/link.prisma:1-107](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/link.prisma#L1-L107), [apps/web/prisma/schema/program.prisma:1-201](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/program.prisma#L1-L201), [apps/web/prisma/schema/fraud.prisma:1-112](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/fraud.prisma#L1-L112)

## Dual database client architecture

### Node.js Client and Singleton Pattern

In standard Node.js execution environments, database operations are managed through a singleton instance of `PrismaClient` exported from the main prisma library file. To prevent connection exhaustion during hot-reloading cycles in development, the client instance is attached to the global Node.js namespace (`global.prisma`), falling back to a factory function `prismaClientSingleton()` if undefined.

```typescript
const prismaClientSingleton = () =>
  new PrismaClient({
    omit: {
      user: { passwordHash: true },
    },
  });

type OmittedPrismaClient = ReturnType<typeof prismaClientSingleton>;

declare global {
  var prisma: OmittedPrismaClient | undefined;
}

export const prisma = global.prisma ?? prismaClientSingleton();

if (process.env.NODE_ENV !== "production") {
  global.prisma = prisma;
}
```
Sources: [apps/web/lib/prisma/index.ts:3-20](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/prisma/index.ts#L3-L20)

> [!NOTE]
> The Node.js singleton explicitly configures Prisma's `omit` capability on the `user` model to strip `passwordHash` by default from all query return objects.

```typescript
  omit: {
    user: { passwordHash: true },
  },
});
```
Sources: [apps/web/lib/prisma/index.ts:5-8](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/prisma/index.ts#L5-L8)

Additionally, this module provides the `sanitizeFullTextSearch` utility function, which strips special query characters (`*`, `+`, `-`, `(`, `)`, `~`, `@`, `%`, `<`, `>`, `=`, `!`, `:`, `?`) from raw search strings before executing database filters.

```source
apps/web/lib/prisma/22-25
```
Sources: [apps/web/lib/prisma/index.ts:22-25](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/prisma/index.ts#L22-L25)

### Edge Runtime and PlanetScale Driver Integration

For serverless edge runtimes where TCP connections are unsupported, database interactions utilize the `@planetscale/database` HTTP driver coupled with `@prisma/adapter-planetscale`. The edge client initializes by reading either `PLANETSCALE_DATABASE_URL` or `DATABASE_URL` from environment variables, instantiating a raw driver `Client`, wrapping it in the Prisma PlanetScale adapter, and exporting `prismaEdge`.

```typescript
import { Client } from "@planetscale/database";
import { PrismaPlanetScale } from "@prisma/adapter-planetscale";
import { PrismaClient } from "@prisma/client";

const client = new Client({
  url: process.env.PLANETSCALE_DATABASE_URL || process.env.DATABASE_URL,
});

const adapter = new PrismaPlanetScale(client);

export const prismaEdge = new PrismaClient({
  adapter,
});
```
Sources: [apps/web/lib/prisma/edge.ts:1-13](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/prisma/edge.ts#L1-L13)

Separately, raw serverless SQL connections are established for direct query execution helpers via the `connect` function from `@planetscale/database`.

```typescript
import { connect } from "@planetscale/database";

export const conn = connect({
  url: process.env.PLANETSCALE_DATABASE_URL || process.env.DATABASE_URL,
});
```
Sources: [apps/web/lib/planetscale/connection.ts:1-5](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/connection.ts#L1-L5)

## Multi-file schema organization and core models

### Modular Schema Architecture and Core Configuration

Dub structures its database definition across specialized schema files under `apps/web/prisma/schema/`. The base configuration file specifies the MySQL datasource provider with `relationMode = "prisma"` and generates the standard `prisma-client-js` client. The user identity model handles security fields including `passwordHash`, `invalidLoginAttempts`, `lockedAt`, and default preferences alongside relational data.

```
datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id            String    @id @default(cuid())
```
Sources: [apps/web/prisma/schema/schema.prisma:1-12](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/schema.prisma#L1-L12)

> [!NOTE]
> Setting `relationMode = "prisma"` informs Prisma to emulate foreign key constraints at the application layer rather than relying on native database constraints, enabling compatibility with serverless database providers like PlanetScale.

### Workspaces and Plan Configurations

Workspace management is centralized in `workspace.prisma`, defining quotas, limits, billing details, and feature flags. Subscription states are tied to `PlanPeriod` intervals and `DubProduct` feature entitlements.

```
enum PlanPeriod {
  monthly
  yearly
  quarterly
  biweekly
}

enum DubProduct {
  program
  links
}

model Project {
  id               String     @id
  name             String
  slug             String     @unique
  logo             String?
  inviteCode       String?    @unique
  defaultProduct   DubProduct @default(links)
  defaultProgramId String?    @unique // default affiliate program ID for the workspace

  plan                   String      @default("free")
  planTier               Int         @default(1)
  planPeriod             PlanPeriod? // monthly, yearly, etc. from Stripe price.recurring.interval
```
Sources: [apps/web/prisma/schema/workspace.prisma:1-24](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/workspace.prisma#L1-L24)

Workspace users and invitations utilize the `WorkspaceRole` enumeration to control access rights across projects.

| Role Name | Description |
| :--- | :--- |
| `owner` | Full administrative control over the workspace configuration, billing, and members |
| `member` | Standard participant with full link and [domain management](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/link-attribution/domain-management) permissions |
| `viewer` | Read-only access to workspace statistics, links, and analytics datasets |
| `billing` | Limited permissions scoped strictly to subscription management and invoices |

Sources: [apps/web/prisma/schema/workspace.prisma:107-112](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/workspace.prisma#L107-L112), [apps/web/prisma/schema/workspace.prisma:119-119](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/workspace.prisma#L119-L119)

### Domains, Links, and Tagging Structures

Custom domains, registered domains, and default redirect mappings are structured inside `domain.prisma`. Each `Domain` entity supports verification status, expiration redirects, retention settings, and associated deep-view configurations.

```
model Domain {
  id                      String   @id @default(cuid())
  slug                    String   @unique
  verified                Boolean  @default(false)
  placeholder             String?
  expiredUrl              String?  @db.LongText // URL to redirect to for expired links
  notFoundUrl             String?  @db.LongText // URL to redirect to for links that don't exist
  primary                 Boolean  @default(false)
  archived                Boolean  @default(false)
  lastChecked             DateTime @default(now())
  logo                    String?
  appleAppSiteAssociation Json?
  assetLinks              Json?
  deepviewData            Json?    @default("{}")

```
Sources: [apps/web/prisma/schema/domain.prisma:1-15](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/domain.prisma#L1-L15)

Short links are managed via `link.prisma`, maintaining core attributes like target URLs, password protection, UTM parameters, geo-targeting rules, and click metrics. Tagging relationships are handled through `tag.prisma` using many-to-many associations between links and tags.

```
model Tag {
  id        String    @id
  name      String
  color     String    @default("blue")
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  project   Project?  @relation(fields: [projectId], references: [id], onUpdate: Cascade, onDelete: Cascade)
  projectId String?
  links     LinkTag[]

  @@unique([name, projectId])
  @@index(projectId)
}
```
Sources: [apps/web/prisma/schema/tag.prisma:1-13](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/tag.prisma#L1-L13)

## Partner program and marketplace schemas

### Affiliate Programs and Enrollments

The core partner infrastructure is anchored by the `Program` model in `program.prisma`, which establishes workspace-scoped affiliate networks, payout rules, and marketplace listing parameters. Each program defines a primary reward event (`EventType`), a minimum payout amount, and a payout mode (`ProgramPayoutMode`).

```
enum ProgramPayoutMode {
  internal // All payouts are handled by Dub
  hybrid // Partners with payouts enabled are paid by Dub, others via external
  external // All payouts are processed through external
}
```
Sources: [apps/web/prisma/schema/program.prisma:21-25](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/program.prisma#L21-L25)

Program enrollment states and partner lifecycle statuses are tracked via the `ProgramEnrollmentStatus` enumeration.

| Status Enum | Meaning |
| :--- | :--- |
| `pending` | Pending applications that need administrative approval |
| `approved` | Partners who have been approved and actively enrolled |
| `rejected` | Program rejected the partner application |
| `invited` | Partner who has been invited to join the program |
| `declined` | Partner declined the program invitation |
| `deactivated` | Partner is deactivated by the program administration |
| `banned` | Partner is banned from participating in the program |
| `archived` | Partner is archived by the program |

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

> [!NOTE]
> When a partner is banned from a program, the `ProgramEnrollment` record retains a `bannedAt` timestamp alongside a standardized `bannedReason` from the `PartnerBannedReason` enum (such as `tos_violation`, `fake_traffic`, or `fraud`).

```
enum PartnerBannedReason {
  tos_violation
  inappropriate_content
  fake_traffic
  fraud
  spam
  brand_abuse
}
```
Sources: [apps/web/prisma/schema/program.prisma:12-19](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/program.prisma#L12-L19)

### Applications and Marketplace Networks

Partner applications are modeled separately in `application.prisma` via `ProgramApplication`, capturing metadata such as website URLs, social profiles, and submission form data. Application workflows track changes through `ProgramApplicationStatus` states and record review metadata.

```
enum ProgramApplicationStatus {
  pending
  approved
  rejected
}
```
Sources: [apps/web/prisma/schema/application.prisma:8-12](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/application.prisma#L8-L12)

Rejections require a specific rationale drawn from the `ProgramApplicationRejectionReason` enumeration.

| Rejection Reason | Description |
| :--- | :--- |
| `needsMoreDetail` | Application lacks sufficient information for evaluation |
| `doesNotMeetRequirements` | Partner fails to satisfy baseline program criteria |
| `notTheRightFit` | Strategic or vertical mismatch with the program goals |
| `other` | Custom or unclassified rejection grounds |

Sources: [apps/web/prisma/schema/application.prisma:1-6](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/application.prisma#L1-L6)

Network categories and discovery features span `network.prisma`, which organizes programs into vertical markets using the `Category` enum.

```
enum Category {
  AI
  Development
  Design
  Productivity
  Finance
  Marketing
  Ecommerce
  Security
  Education
  Health
  Consumer
  Support
}
```
Sources: [apps/web/prisma/schema/network.prisma:1-14](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/network.prisma#L1-L14)

### Rewards and Discount Structures

Compensation rules are decoupled into `reward.prisma` and `discount.prisma`. The `Reward` model handles event-based commissions across five distinct event types.

```
enum EventType {
  click
  lead
  sale
  referral
  custom
}
```
Sources: [apps/web/prisma/schema/reward.prisma:1-7](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/reward.prisma#L1-L7)

Reward calculation models support both percentage and flat fee structures via `RewardStructure`, with optional spend limits governed by `RewardSpendLimitInterval` (scoped to `allTime`, `day`, `week`, or `month`).

```
enum RewardStructure {
  percentage
  flat
}

enum RewardSpendLimitInterval {
  allTime
  day
  week
  month
}
```
Sources: [apps/web/prisma/schema/reward.prisma:9-19](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/reward.prisma#L9-L19)

> [!WARNING]
> Discount providers integration is strictly typed through `DiscountProvider`, which accepts only `stripe`, `shopify`, or `custom` values when provisioning partner coupon codes.

```
enum DiscountProvider {
  stripe
  shopify
  custom
}
```
Sources: [apps/web/prisma/schema/discount.prisma:1-5](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/discount.prisma#L1-L5)

## Platform security and operational entities

### Authentication Tokens and Integrations

The token infrastructure distinguishes standard user tokens from scoped API keys. The `Token` model provisions primary user credentials, while `RestrictedToken` binds generated keys to specific workspaces, users, and optional OAuth installations with space-separated permission scopes.

| Token Model | Key Fields | Associated Relations |
| :--- | :--- | :--- |
| `Token` | `hashedKey`, `partialKey`, `expires`, `lastUsed` | `User` |
| `RestrictedToken` | `hashedKey`, `partialKey`, `scopes`, `installationId` | `User`, `Project`, `InstalledIntegration`, `OAuthRefreshToken` |
| `VerificationToken` | `identifier`, `token`, `isAdminImpersonation` | None (Composite unique on identifier and token) |
| `EmailVerificationToken` | `identifier`, `token`, `expires` | None (Composite unique on identifier and token) |
| `PasswordResetToken` | `identifier`, `token`, `expires` | None (Composite unique on identifier and token) |

Sources: [apps/web/prisma/schema/token.prisma:1-66](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/token.prisma#L1-L66)

OAuth applications and authorization flows are modeled across `oauth.prisma` and `integration.prisma`. The `OAuthApp` entity registers client credentials, redirect URIs, and PKCE requirements, bridging third-party integrations with authorization codes and refresh tokens.

```
model OAuthApp {
  id                  String  @id
  integrationId       String  @unique
  clientId            String  @unique
  hashedClientSecret  String
  partialClientSecret String
  redirectUris        Json
  pkce                Boolean @default(false)

  oAuthCodes  OAuthCode[]
  integration Integration? @relation(fields: [integrationId], references: [id], onDelete: Cascade)
}
```
Sources: [apps/web/prisma/schema/oauth.prisma:1-12](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/oauth.prisma#L1-L12)

> [!NOTE]
> `OAuthRefreshToken` records cascade deletions through both their associated restricted access token and installed integration to prevent orphaned authorization states.

```
model OAuthRefreshToken {
  id                 String   @id @default(cuid())
  installationId     String
  accessTokenId      String
  hashedRefreshToken String   @unique
  expiresAt          DateTime
  createdAt          DateTime @default(now())

  accessToken          RestrictedToken      @relation(fields: [accessTokenId], references: [id], onDelete: Cascade)
  installedIntegration InstalledIntegration @relation(fields: [installationId], references: [id], onDelete: Cascade)

  @@index([installationId])
  @@index([accessTokenId])
}
```
Sources: [apps/web/prisma/schema/oauth.prisma:36-49](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/oauth.prisma#L36-L49)

### Enterprise SAML Indexing and Fallback Queues

BoxyHQ Jackson enterprise single sign-on data is indexed and persisted through dedicated database models in `jackson.prisma`. These models handle relational indexing, encrypted storage values with initialization vectors and tags, and Time-To-Live expiration handling.

| Jackson Model | Purpose | Key Fields |
| :--- | :--- | :--- |
| `jackson_index` | Composite lookup indexing | `key`, `storeKey` |
| `jackson_store` | Encrypted payload persistence | `key`, `value`, `iv`, `tag`, `namespace` |
| `jackson_ttl` | Expiration tracking | `key`, `expiresAt` |

Sources: [apps/web/prisma/schema/jackson.prisma:1-26](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/jackson.prisma#L1-L26)

Operational resilience for background processing is maintained via `job.prisma`. When jobs cannot be published directly to QStash, they are captured in the fallback `Job` table and replayed by downstream cron handlers.

```
model Job {
  id          String   @id
  name        String
  payload     Json
  options     Json? // dispatch options replayed verbatim: deduplicationId, retries, queue, flowControl, label
  scheduledAt DateTime @default(now()) // when we should publish the job
  attempts    Int      @default(0)
  lastError   String?  @db.Text
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  @@index([scheduledAt, attempts]) // retry cron: due jobs under the attempt cap
}
```
Sources: [apps/web/prisma/schema/job.prisma:3-15](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/job.prisma#L3-L15)

### Fraud Tracking and Partner Security Rules

Network security and affiliate fraud detection are managed through rules, event groupings, and individual telemetry logs defined in `fraud.prisma`. Fraud rules evaluate partner activity against six distinct rule types.

| Fraud Rule Type | Description |
| :--- | :--- |
| `customerEmailMatch` | Matches customer email against blocked lists or patterns |
| `customerEmailSuspiciousDomain` | Flags disposable or suspicious email service domains |
| `referralSourceBanned` | Detects traffic originating from prohibited referrers |
| `paidTrafficDetected` | Identifies unauthorized paid search or ad network traffic |
| `partnerCrossProgramBan` | Enforces network-level bans originating from other programs |
| `partnerDuplicateAccount` | Flags duplicate identities shared across multiple partners |

Sources: [apps/web/prisma/schema/fraud.prisma:7-14](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/fraud.prisma#L7-L14)

Fraud events aggregate under `FraudEventGroup` entities, tracking lifecycle statuses governed by the `FraudEventStatus` enum (`pending`, `resolved`, or `expired`). Individual events record specific link interactions, customer identifiers, hashes, and source program origins.

```
model FraudEventGroup {
  id               String           @id
  programId        String
  partnerId        String
  type             FraudRuleType
  lastEventAt      DateTime?
  eventCount       Int              @default(0)
  userId           String?
  resolutionReason String?          @db.Text
  resolvedAt       DateTime?
  status           FraudEventStatus @default(pending)
  createdAt        DateTime         @default(now())
  updatedAt        DateTime         @updatedAt

  program           Program           @relation(fields: [programId], references: [id], onDelete: Cascade)
  partner           Partner           @relation(fields: [partnerId], references: [id], onDelete: Cascade)
  programEnrollment ProgramEnrollment @relation(fields: [programId, partnerId], references: [programId, partnerId], onDelete: Cascade)
  fraudEvents       FraudEvent[]
  user              User?             @relation(fields: [userId], references: [id])

  @@index([programId, partnerId, type, status])
  @@index(partnerId)
  @@index(userId)
  @@index(lastEventAt)
}
```
Sources: [apps/web/prisma/schema/fraud.prisma:30-54](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/fraud.prisma#L30-L54)

> [!WARNING]
> Administrative reviews of suspicious activity are handled separately by `FraudAlert`, which tracks review notes and reviewer assignments using the `FraudAlertStatus` states `pending`, `confirmed`, or `dismissed`.

```
enum FraudAlertStatus {
  pending
  confirmed
  dismissed
}

model FraudAlert {
  id           String           @id @default(cuid())
  partnerId    String
  programId    String
  reason       String           @db.Text
  status       FraudAlertStatus @default(pending)
  reviewedById String?
  reviewedAt   DateTime?
  reviewNote   String?          @db.Text
  createdAt    DateTime         @default(now())
  updatedAt    DateTime         @updatedAt

  partner           Partner           @relation(fields: [partnerId], references: [id], onDelete: Cascade)
  program           Program           @relation(fields: [programId], references: [id], onDelete: Cascade)
  reviewedBy        User?             @relation("FraudAlertReviewer", fields: [reviewedById], references: [id])
  programEnrollment ProgramEnrollment @relation(fields: [programId, partnerId], references: [programId, partnerId], onDelete: Cascade)

  @@index(partnerId)
  @@index(status)
  @@index(createdAt)
}
```
Sources: [apps/web/prisma/schema/fraud.prisma:86-112](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/prisma/schema/fraud.prisma#L86-L112)

## Edge direct SQL access patterns

### PlanetScale Connection and Edge Exports

Low-latency database interactions on edge routes are handled using the `@planetscale/database` client configured with connection variables. The connection retrieves credentials from either `PLANETSCALE_DATABASE_URL` or fallback `DATABASE_URL` environment variables.

```typescript
import { connect } from "@planetscale/database";

export const conn = connect({
  url: process.env.PLANETSCALE_DATABASE_URL || process.env.DATABASE_URL,
});
```
Sources: [apps/web/lib/planetscale/connection.ts:1-5](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/connection.ts#L1-L5)

The PlanetScale integration layer exports modular connection utilities, edge resolution helpers, and TypeScript interfaces from a centralized index.

```typescript
export * from "./check-if-key-exists";
export * from "./check-if-user-exists";
export * from "./connection";
export * from "./get-link-via-edge";
export * from "./get-random-key";
export * from "./get-shortlink-via-edge";
export * from "./get-workspace-via-edge";
export * from "./types";
```
Sources: [apps/web/lib/planetscale/index.ts:1-8](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/index.ts#L1-L8)

### Edge Link Resolution and Type Definitions

Edge routes execute parameterised SQL statements directly against the `Link` table to retrieve shortlink payloads without heavy ORM overhead.

```typescript
export const getShortLinkViaEdge = async (shortLink: string) => {
  const { rows } =
    (await conn.execute("SELECT * FROM Link WHERE shortLink = ?", [
      shortLink,
    ])) || {};

  return rows && Array.isArray(rows) && rows.length > 0
    ? (rows[0] as EdgeLinkProps)
    : null;
};
```
Sources: [apps/web/lib/planetscale/get-shortlink-via-edge.ts:4-13](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/get-shortlink-via-edge.ts#L4-L13)

> [!NOTE]
> `getShortLinkViaEdge` validates that the returned query rows consist of a non-empty array before casting the first element to `EdgeLinkProps`.

```typescript
return rows && Array.isArray(rows) && rows.length > 0
  ? (rows[0] as EdgeLinkProps)
  : null;
```
Sources: [apps/web/lib/planetscale/get-shortlink-via-edge.ts:10-12](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/get-shortlink-via-edge.ts#L10-L12)

Entity properties for edge records are strictly typed to mirror relational schema fields across domains and shortlinks.

| Edge Property Interface | Target Entity | Core Fields |
| :--- | :--- | :--- |
| `EdgeLinkProps` | `Link` | `id`, `domain`, `key`, `url`, `shortLink`, `proxy`, `password`, `expiresAt`, `ios`, `android`, `geo`, `projectId` |
| `EdgeDomainProps` | `Domain` | `id`, `slug`, `logo`, `verified`, `placeholder`, `expiredUrl`, `notFoundUrl`, `primary`, `archived`, `projectId` |

Sources: [apps/web/lib/planetscale/types.ts:1-40](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/lib/planetscale/types.ts#L1-L40)

## Database seeding and operational scripts

### Development Database Population

The seed script initializes a workspace along with its associated users, domains, email domains, folders, rewards, partner groups, programs, partners, and integrations by loading payload data from a local JSON configuration file. Passwords for generated users are hashed prior to insertion.

```typescript
const parseJSON = (): SeedData => {
  const jsonPath = path.join(__dirname, "data.json");
  const jsonData = JSON.parse(fs.readFileSync(jsonPath, "utf-8"));
  return jsonData;
};
```
Sources: [apps/web/scripts/dev/seed.ts:137-141](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/dev/seed.ts#L137-L141)

```typescript
const passwordHash = await hashPassword("password");

const { count } = await prisma.user.createMany({
  data: users.map((user) => ({
    id: user.id,
    name: user.name,
    email: user.email,
    emailVerified: new Date(user.emailVerified),
    passwordHash,
  })),
});
```
Sources: [apps/web/scripts/dev/seed.ts:163-173](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/dev/seed.ts#L163-L173)

### Benchmark Data Generation

Benchmark and local testing data are populated via a bulk-seeding script targeted at partner search performance evaluations. The script writes `User`, `Partner`, `PartnerUser`, `ProgramEnrollment`, `PartnerPlatform`, `Link`, and `ProgramPartnerTag` rows in chunks to prevent memory bloat.

| Constant Name | Value | Purpose |
| :--- | :--- | :--- |
| `DEFAULT_COUNT` | `100,000` | Default number of partner records to generate |
| `MAX_COUNT` | `1,000,000` | Upper limit restriction for bulk generation |
| `DEFAULT_SEED` | `"partners-search"` | Seed string used for deterministic key generation |
| `CHUNK_SIZE` | `2,500` | Atomic write chunk size for database inserts |

Sources: [apps/web/scripts/dev/seed-100k-partners.ts:37-40](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/dev/seed-100k-partners.ts#L37-L40)

> [!CAUTION]
> The bulk-seed runner inserts login-capable users containing known passwords, and therefore explicitly rejects non-local connection strings unless `--allowRemoteDatabase` is provided, while refusing production environments (`NODE_ENV` or `VERCEL_ENV`) outright.

```typescript
* This inserts login-capable users with a known password, so it refuses a non-local
* DATABASE_URL unless `--allowRemoteDatabase` is passed, and refuses a production
* environment (NODE_ENV/VERCEL_ENV) outright.
```
Sources: [apps/web/scripts/dev/seed-100k-partners.ts:18-20](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/dev/seed-100k-partners.ts#L18-L20)

### Administrative Data Restoration

Data migration and restoration tooling manages the transfer of partner enrollments, payouts, commissions, messages, bounty submissions, and associated link metadata from legacy database connections (`prismaOld`) into active instances (`prisma`).

```typescript
const programEnrollmentsToRestore =
  await prismaOld.programEnrollment.findMany({
    where: {
      partnerId,
    },
  });

const res = await prisma.programEnrollment.createMany({
  data: programEnrollmentsToRestore,
});
console.log(`Restored ${res.count} program enrollments`);
```
Sources: [apps/web/scripts/misc/restore-program-enrollments.ts:37-47](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/misc/restore-program-enrollments.ts#L37-L47)

Links restored through this operational script trigger downstream analytics recording to telemetry services alongside associated customer restoration.

```typescript
const restoredLinks = await prisma.link.findMany({
  where: { partnerId },
  include: {
    ...includeTags,
    ...includeProgramEnrollment,
  },
});

const tbRes = await recordLink(restoredLinks);
console.log("tbRes", tbRes);
```
Sources: [apps/web/scripts/misc/restore-program-enrollments.ts:130-139](https://github.com/blade47/dub/blob/7c93d97abf0ecd37fb669c6a68a1f7ef9109daea/apps/web/scripts/misc/restore-program-enrollments.ts#L130-L139)

## Related

- [Link Dispatch](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/link-attribution/link-dispatch)
- [Tinybird Analytics](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/analytics-pipeline/tinybird-analytics)


## Sitemap

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