Database Schema and Prisma | blade47/dub Wiki | doc0

Public

Database Schema and Prisma | blade47/dub Wiki | doc0

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.

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

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.

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,
  };
}

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
};

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
Unique Constraint	`[projectId, externalId]`	API usage and multi-tenant lookups via external identifiers. Sources: [apps/web/prisma/schema/link.prisma:97
Index	`[projectId, tenantId]`	Tenant-scoped filtering for workspace resource queries. Sources: [apps/web/prisma/schema/link.prisma:98
Index	`[projectId, url(length: 500)]`	High-performance URL upserts and duplicate detection. Sources: [apps/web/prisma/schema/link.prisma:99
Index	`[programId, partnerId]`	Referral link lookups combining program and partner associations. Sources: [apps/web/prisma/schema/link.prisma:101
Index	`[domain, createdAt]`	Bulk link deletion workflows and cleanup of short-lived links. Sources: [apps/web/prisma/schema/link.prisma:103

Status Name	Description / Meaning
`pending`	Pending applications that need administrative approval. Sources: [apps/web/prisma/schema/program.prisma:2
`approved`	Partner who has been approved or actively enrolled. Sources: [apps/web/prisma/schema/program.prisma:3
`rejected`	Program rejected the partner application. Sources: [apps/web/prisma/schema/program.prisma:4
`invited`	Partner who has received an invitation to join. Sources: [apps/web/prisma/schema/program.prisma:5
`declined`	Partner declined the program invitation. Sources: [apps/web/prisma/schema/program.prisma:6
`deactivated`	Partner is manually deactivated by the program. Sources: [apps/web/prisma/schema/program.prisma:7
`banned`	Partner is banned from participating in the program. Sources: [apps/web/prisma/schema/program.prisma:8
`archived`	Partner record is archived by the program. Sources: [apps/web/prisma/schema/program.prisma:9

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.

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,
  });
}

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
`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, apps/web/scripts/migrations/backfill-commissions-metadata.ts:26
`THROTTLE_MS`	`1000`	Millisecond sleep duration between migration batches to prevent resource exhaustion.	apps/web/scripts/migrations/backfill-commissions-metadata.ts:27
`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
`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

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