Database Architecture

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, apps/web/lib/prisma/index.ts:1-20, apps/web/lib/planetscale/connection.ts:1-5

The relational schema is modularly decomposed into dedicated Prisma files covering core user identity, workspaces, domains, links, tags, partner programs, OAuth apps, security tokens, enterprise SSO indexes, fraud rule tracking, and fallback background jobs. Sources: apps/web/prisma/schema/schema.prisma:1-95, apps/web/prisma/schema/workspace.prisma:1-153, apps/web/prisma/schema/link.prisma:1-107, apps/web/prisma/schema/program.prisma:1-201, apps/web/prisma/schema/fraud.prisma:1-112

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.

Copyconst 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

Copy  omit: {
    user: { passwordHash: true },
  },
});

Sources: apps/web/lib/prisma/index.ts:5-8

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

Copyapps/web/lib/prisma/22-25

Sources: apps/web/lib/prisma/index.ts:22-25

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.

Copyimport { 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

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

Copyimport { 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

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

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

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

Sources: apps/web/prisma/schema/workspace.prisma:107-112, apps/web/prisma/schema/workspace.prisma:119-119

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

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

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

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

Sources: apps/web/prisma/schema/program.prisma:1-10

enum PartnerBannedReason {
  tos_violation
  inappropriate_content
  fake_traffic
  fraud
  spam
  brand_abuse
}

Sources: apps/web/prisma/schema/program.prisma:12-19

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

Rejections require a specific rationale drawn from the ProgramApplicationRejectionReason enumeration.

Sources: apps/web/prisma/schema/application.prisma:1-6

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

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

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

enum DiscountProvider {
  stripe
  shopify
  custom
}

Sources: apps/web/prisma/schema/discount.prisma:1-5

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.

Sources: apps/web/prisma/schema/token.prisma:1-66

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

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

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.

Sources: apps/web/prisma/schema/jackson.prisma:1-26

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

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.

Sources: apps/web/prisma/schema/fraud.prisma:7-14

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

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

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.

Copyimport { 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

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

Copyexport * 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

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.

Copyexport 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

Copyreturn rows && Array.isArray(rows) && rows.length > 0
  ? (rows[0] as EdgeLinkProps)
  : null;

Sources: apps/web/lib/planetscale/get-shortlink-via-edge.ts:10-12

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

Sources: apps/web/lib/planetscale/types.ts:1-40

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.

Copyconst 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

Copyconst 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

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.

Sources: apps/web/scripts/dev/seed-100k-partners.ts:37-40

Copy* 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

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).

Copyconst 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

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

Copyconst 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

Related

• Link Dispatch
• Tinybird Analytics