---
title: "Database Seeding and Testing"
description: "Database seeding and testing utilities provide a comprehensive toolkit for initializing local environments, populating large-scale synthetic datasets, and simulating end-to-end partner conversion p..."
last_updated: "2026-10-05T05:07:35.166923+00:00"
canonical_url: "https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/developer-tools/database-seeding-and-testing"
---

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

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

- [apps/web/scripts/dev/seed-100k-partners.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-100k-partners.ts)
- [apps/web/scripts/dev/seed.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed.ts)
- [apps/web/scripts/dev/data.json](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/data.json)
- [apps/web/scripts/dev/seed-application-events.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-application-events.ts)
- [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/scripts/dev/seed-partner-enrollment.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-partner-enrollment.ts)
- [apps/web/scripts/partners/aggregate-stats-seeding.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/partners/aggregate-stats-seeding.ts)
- [apps/web/scripts/dev/seed-commissions.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-commissions.ts)
- [apps/web/scripts/dev/test-partner-referrals.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/test-partner-referrals.ts)
- [apps/web/scripts/dev/simulate-shopify-conversion.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/simulate-shopify-conversion.ts)
- [apps/web/lib/tinybird/record-fake-click.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-fake-click.ts)
- [apps/web/ui/analytics/events/example-data.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/ui/analytics/events/example-data.ts)
- [apps/web/scripts/programs/backfill-reuse-commission.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/backfill-reuse-commission.ts)
- [apps/web/scripts/programs/5-import-customer-sales.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/5-import-customer-sales.ts)
- [apps/web/scripts/misc/restore-program-enrollments.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/misc/restore-program-enrollments.ts)
- [apps/web/scripts/migrations/backfill-application-events.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-application-events.ts)
- [apps/web/scripts/customers/upheal/sync-stripe-invoices.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/upheal/sync-stripe-invoices.ts)
- [apps/web/scripts/dev/seed-integration.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-integration.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)
- [apps/web/scripts/programs/create-zero-commissions.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/create-zero-commissions.ts)
- [apps/web/scripts/programs/backfill-discount-codes.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/backfill-discount-codes.ts)
- [apps/web/lib/lemonsqueezy/import-partners.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/lemonsqueezy/import-partners.ts)
- [apps/web/scripts/customers/annature/import-domains.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/annature/import-domains.ts)
- [apps/web/scripts/programs/3-import-customer-leads.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/3-import-customer-leads.ts)
- [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/scripts/customers/beehiiv/fix-case-c.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-c.ts)
- [apps/web/scripts/programs/2-import-partner-links.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/2-import-partner-links.ts)
- [apps/web/scripts/customers/beehiiv/fix-case-b.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-b.ts)
- [apps/web/scripts/lead-form-sample.json](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/lead-form-sample.json)
</details>

## Overview

Database seeding and testing utilities provide a comprehensive toolkit for initializing local environments, populating large-scale synthetic datasets, and simulating end-to-end partner conversion pipelines. These scripts and fixtures enable developers to stress-test search indexes, validate attribution logic, and debug complex commission calculations without relying on live production data. By establishing deterministic baselines for workspaces, users, partners, and analytics events, the testing framework ensures reliable behavior across affiliate workflows and billing reconciliations. Sources: [apps/web/scripts/dev/seed-100k-partners.ts:1-27](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-100k-partners.ts#L1-L27), [apps/web/scripts/dev/seed.ts:1-23](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed.ts#L1-L23), [apps/web/scripts/dev/simulate-shopify-conversion.ts:1-26](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/simulate-shopify-conversion.ts#L1-L26)

## Local Workspace and Domain Seeding

### Overview

Local database bootstrapping relies on automated scripts to provision baseline workspaces, administrative users, custom domains, and pre-installed third-party integrations. The primary initialization routine parses a structured JSON fixture (`data.json`) containing entity definitions for the development environment. Prior to execution, scripts validate local environment variables and connection parameters using database assertion utilities to prevent accidental staging or production modification. Sources: [apps/web/scripts/dev/seed.ts:1-23](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed.ts#L1-L23), [apps/web/scripts/dev/data.json:1-25](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/data.json#L1-L25)

### Workspace and User Seeding Workflow

The local seeding process executes sequentially through designated helper functions. The execution call-chain proceeds as follows: `parseJSON()` reads the raw seed payload → `createWorkspace()` instantiates the primary project record via Prisma → `createUsers()` hashes default credentials and bulk-inserts user accounts, project membership links, and notification preferences → `createDomains()` and `createEmailDomains()` register routing identifiers and email verification slugs against the workspace. Sources: [apps/web/scripts/dev/seed.ts:137-250](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed.ts#L137-L250)

> [!NOTE]
> `createUsers()` queries `prisma.projectUsers.findMany` immediately after bulk creation because Prisma's `createMany` operation does not return auto-generated relational row IDs, which are required subsequently to link user notification preferences. Sources: [apps/web/scripts/dev/seed.ts:177-205](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed.ts#L177-L205)

### Seed Data Schema and Entity Configuration

The workspace seed configuration defines resource limits, billing cycle boundaries, and feature flags for the default development organization (`Acme, Inc.`). Associated user fixtures provision accounts across four distinct organizational permission tiers. Sources: [apps/web/scripts/dev/data.json:1-55](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/data.json#L1-L55)

| Entity Type | Identifier / Slug | Role / Status | Key Attributes |
| :--- | :--- | :--- | :--- |
| Workspace | `ws_1KETZ919F83ZJH6A80HWEHW6E` (`acme`) | enterprise plan | `usageLimit`: 100000, `conversionEnabled`: true, `webhookEnabled`: true |
| Owner User | `user_cludszk1h0000wmd2e0ea2b0p` | owner | `email`: owner@dub-internal-test.com |
| Member User | `user_cludszk1h0000wmd2e0ea2b0q` | member | `email`: member@dub-internal-test.com |
| Viewer User | `user_cludszk1h0000wmd2e0ea2b0r` | viewer | `email`: viewer@dub-internal-test.com |
| Billing User | `user_cludszk1h0000wmd2e0ea2b0s` | billing | `email`: billing@dub-internal-test.com |
| Domain | `dom_1KETZ919F83ZJH6A80HWEHW6E` (`dub.sh`) | verified: true | `projectId`: ws_1KETZ919F83ZJH6A80HWEHW6E |
| Email Domain | `dom_1KETZ919F83ZJH6A80EMAILDOM` (`getacme.link`) | status: verified | Associated with program resources |

Sources: [apps/web/scripts/dev/seed.ts:24-135](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed.ts#L24-L135), [apps/web/scripts/dev/data.json:1-69](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/data.json#L1-L69)

### Core Integration Seeding

In addition to core workspace fixtures, dedicated integration seeding scripts provision third-party service connections for local testing. The integration script targets installed applications using composite unique keys and encrypted credential stores. Sources: [apps/web/scripts/dev/seed-integration.ts:1-32](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-integration.ts#L1-L32)

```typescript
await prisma.installedIntegration.upsert({
  where: {
    userId_integrationId_projectId: {
      userId: "cl7p1s07k000687rbuhpwqkqa",
      integrationId: INTERCOM_INTEGRATION_ID,
      projectId: ACME_WORKSPACE_ID,
    },
  },
  create: {
    userId: "cl7p1s07k000687rbuhpwqkqa",
    integrationId: INTERCOM_INTEGRATION_ID,
    projectId: ACME_WORKSPACE_ID,
    credentials: {
      appId: "xxx",
      accessToken: encrypt("xxx"),
    },
  },
  update: {},
});
```

Sources: [apps/web/scripts/dev/seed-integration.ts:8-28](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-integration.ts#L8-L28)

## High-Volume Partner Data Generation

### Overview

High-volume synthetic data generation scripts populate local development databases and partner-search benchmarking environments with thousands of records. The seeding subsystem handles chunked bulk creation of users, partners, program enrollments, platforms, links, and tags, alongside targeted partner enrollment scripts and external Lemon Squeezy integration importers. Sources: [apps/web/scripts/dev/seed-100k-partners.ts:1-16](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-100k-partners.ts#L1-L16), [apps/web/scripts/dev/seed-partner-enrollment.ts:1-87](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-partner-enrollment.ts#L1-L87), [apps/web/lib/lemonsqueezy/import-partners.ts:15-173](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/lemonsqueezy/import-partners.ts#L15-L173)

### Partner Enrollment and Importer Workflows

The partner enrollment process creates standalone testing fixtures and imports active affiliates from external platforms. The targeted enrollment flow executes sequentially through designated record creations: `prisma.program.findUnique()` queries default group properties → `prisma.user.create()` instantiates login-capable accounts → `prisma.partner.create()` provisions partner records → `prisma.partnerUser.create()` assigns owner roles with notification preferences → `prisma.programEnrollment.create()` establishes pending program memberships → `prisma.programApplicationEvent.create()` records application lifecycle events. Sources: [apps/web/scripts/dev/seed-partner-enrollment.ts:10-84](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-partner-enrollment.ts#L10-L84)

```typescript
const program = await prisma.program.findUnique({
  where: { id: programId },
  select: { defaultGroupId: true },
});

const userId = createId({ prefix: "user_" });
const partnerId = createId({ prefix: "pn_" });

await prisma.user.create({
  data: {
    id: userId,
    email,
    name,
    emailVerified: new Date(),
    defaultPartnerId: partnerId,
  },
});
```

Sources: [apps/web/scripts/dev/seed-partner-enrollment.ts:10-39](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-partner-enrollment.ts#L10-L39)

> [!WARNING]
> High-volume partner seeding scripts refuse non-local database hosts unless `--allowRemoteDatabase` is explicitly passed, and reject production environments (`NODE_ENV` or `VERCEL_ENV`) outright to prevent accidental data corruption or staging modification. Sources: [apps/web/scripts/dev/seed-100k-partners.ts:18-21](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-100k-partners.ts#L18-L21)

### External Partner Import Mechanisms

External partner ingestion parses pagination batches from Lemon Squeezy affiliates, filtering active records for automated enrolment and link generation. The import pipeline executes via `importPartners()` which evaluates affiliate statuses, logs import errors for inactive records, and triggers search index synchronization. Sources: [apps/web/lib/lemonsqueezy/import-partners.ts:15-166](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/lemonsqueezy/import-partners.ts#L15-L166)

| Import Parameter / Entity | Target Field or Check | Action on Match | Fallback Behavior |
| :--- | :--- | :--- | :--- |
| Affiliate Status | `affiliate.status === "active"` | Invoke `createPartnerAndLinks()` | Push to `notImportedAffiliates` and log `INACTIVE_PARTNER` error |
| Affiliate Email | `affiliate.user_email` | Proceed with upsert workflow | Log `PARTNER_NOT_FOUND` error and return |
| Partner Link Key | `partnerLink.key === affiliate.id` | Skip link creation and return partner ID | Call `createLink()` or log `LINK_NOT_FOUND` on mismatch |

Sources: [apps/web/lib/lemonsqueezy/import-partners.ts:99-162](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/lemonsqueezy/import-partners.ts#L99-L162), [apps/web/lib/lemonsqueezy/import-partners.ts:202-319](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/lemonsqueezy/import-partners.ts#L202-L319)

## Simulated Analytics Clicks and Conversions

### Overview

Testing clickstreams and e-commerce conversions locally requires injecting synthetic analytics payloads and webhook triggers that emulate client-side tracking pixels and payment provider notifications. The simulation utilities validate database schemas, Tinybird analytics event ingestion pipelines, and Shopify webhook verification flows without requiring live client interactions or external network roundtrips.

Sources: [apps/web/scripts/dev/simulate-shopify-conversion.ts:1-26](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/simulate-shopify-conversion.ts#L1-L26), [apps/web/lib/tinybird/record-fake-click.ts:24-46](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-fake-click.ts#L24-L46)

### Synthetic Tinybird Click Generation

The `recordFakeClick()` function constructs valid Request objects with localized geo-IP headers and user-agent metadata before invoking the core click recording logic. ByteString encoding validation ensures non-Latin-1 header values fall back to safe ASCII strings to prevent `fetch` exceptions.

```typescript
export async function recordFakeClick({
  link,
  customer,
  timestamp,
  referrer,
  userAgent,
}: {
  link: Pick<Link, "id" | "url" | "domain" | "key" | "projectId"> & {
    programId?: string | null;
    partnerId?: string | null;
  };
  customer?: {
    country?: string | null;
    region?: string | null;
    continent?: string | null;
    city?: string | null;
    latitude?: string | null;
    longitude?: string | null;
  };
  timestamp?: string | number;
  referrer?: string | null;
  userAgent?: string | null;
}) {
  const country = toSafeHeaderValue(customer?.country) || "US";
  const continent =
    toSafeHeaderValue(customer?.continent) ||
    COUNTRIES_TO_CONTINENTS[country] ||
    "NA";

  const dummyRequest = new Request(link.url, {
    headers: new Headers({
      "user-agent":
        toSafeHeaderValue(userAgent) ||
        "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)",
      "x-forwarded-for": "127.0.0.1",
      "x-vercel-ip-country": country,
      "x-vercel-ip-country-region": toSafeHeaderValue(customer?.region) || "CA",
      "x-vercel-ip-continent": continent,
      ...(customer?.city && {
        "x-vercel-ip-city": toSafeHeaderValue(customer.city) || "Unknown",
      }),
      ...(customer?.latitude && {
        "x-vercel-ip-latitude":
          toSafeHeaderValue(customer.latitude) || "Unknown",
      }),
      ...(customer?.longitude && {
        "x-vercel-ip-longitude":
          toSafeHeaderValue(customer.longitude) || "Unknown",
      }),
    }),
  });
```

Sources: [apps/web/lib/tinybird/record-fake-click.ts:7-74](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-fake-click.ts#L7-L74)

The click execution pipeline flows through specific tracking handlers: `recordFakeClick()` validates safe headers → constructs `dummyRequest` → invokes `recordClick()` with `skipRatelimit: true` and `shouldCacheClickId: true` → parses and returns the validated payload via `clickEventSchemaTB.parse()`.

Sources: [apps/web/lib/tinybird/record-fake-click.ts:76-101](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-fake-click.ts#L76-L101)

> [!WARNING]
> HTTP headers must be valid Latin-1 ByteStrings; passing non-Latin-1 characters directly into `fetch` or header initializers throws runtime exceptions, which is why `toSafeHeaderValue()` intercepts strings and substitutes `"Unknown"` when character codes exceed 255. Sources: [apps/web/lib/tinybird/record-fake-click.ts:6-20](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-fake-click.ts#L6-L20)

### Shopify Conversion and Webhook Simulation

The Shopify simulation script orchestrates an end-to-end e-commerce conversion flow by first dispatching a tracking pixel association and subsequently triggering an authenticated webhook notification for a paid order.

```typescript
async function main() {
  const clickId = "RZjkGhi04FGxWjcK";
  const checkoutToken = nanoid(10);
  const storeId = "store.dub.co";
  const existingCustomerId = null;
  const discountCode = null;

  await trackPixel({
    clickId,
    checkoutToken,
  });

  await sleep(4000);

  await trackOrderPaid({
    checkoutToken,
    storeId,
    existingCustomerId,
    discountCode,
  });
}
```

Sources: [apps/web/scripts/dev/simulate-shopify-conversion.ts:6-26](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/simulate-shopify-conversion.ts#L6-L26)

Webhook requests sign payload bodies using HMAC-SHA256 authenticated with the `SHOPIFY_WEBHOOK_SECRET` environment variable, transmitting the resulting digest via the `x-shopify-hmac-sha256` header along with topic and domain routing headers.

```typescript
function shopifyWebhookSignature(body: string) {
  const secret = process.env.SHOPIFY_WEBHOOK_SECRET;

  if (!secret) {
    return undefined;
  }

  return createHmac("sha256", secret).update(body, "utf8").digest("base64");
}
```

Sources: [apps/web/scripts/dev/simulate-shopify-conversion.ts:94-102](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/simulate-shopify-conversion.ts#L94-L102)

| Simulation Function / Endpoint | Target URL | HTTP Method | Required Headers / Signatures |
| :--- | :--- | :--- | :--- |
| `trackPixel()` | `http://localhost:8888/api/shopify/pixel` | `POST` | `Content-Type: application/json` |
| `trackOrderPaid()` | `http://localhost:8888/api/shopify/integration/webhook` | `POST` | `x-shopify-topic: orders/paid`, `x-shopify-shop-domain`, `x-shopify-hmac-sha256` |

Sources: [apps/web/scripts/dev/simulate-shopify-conversion.ts:35-44](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/simulate-shopify-conversion.ts#L35-L44), [apps/web/scripts/dev/simulate-shopify-conversion.ts:75-87](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/simulate-shopify-conversion.ts#L75-L87)

## Partner Referral and Commission Ingestion

### Overview

The partner referral and commission ingestion subsystem handles importing synthetic customer leads, processing Stripe invoice sales events, generating partner links from discount codes, and seeding commission ledger records. These operations bridge raw CSV imports with database persistence and Tinybird analytics ingestion.

Sources: [apps/web/scripts/programs/3-import-customer-leads.ts:21-23](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/3-import-customer-leads.ts#L21-L23), [apps/web/scripts/programs/5-import-customer-sales.ts:25-26](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/5-import-customer-sales.ts#L25-L26), [apps/web/scripts/programs/backfill-discount-codes.ts:14-15](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/backfill-discount-codes.ts#L14-L15)

### Partner Link Generation and Discount Code Backfilling

Partner links and associated discount codes are imported via parsing structured inputs or CSV definitions. The `backfill-discount-codes.ts` script iterates over configured partner emails and maps them to enrolled program records before batch-creating links and discount codes.

```typescript
async function main() {
  const program = await prisma.program.findUniqueOrThrow({
    where: { id: programId },
  });

  const programEnrollments = await prisma.programEnrollment.findMany({
    where: {
      programId: program.id,
      partner: { email: { in: Object.keys(partnerDiscountCodes) } },
    },
    select: {
      partner: { select: { id: true, email: true } },
      discountId: true,
    },
  });

  const linksToCreate: Partial<ProcessedLinkProps>[] = [];
  // ... maps and constructs bulk payloads ...
  if (linksToCreate.length > 0) {
    const createdLinks = await bulkCreateLinks({ links: linksToCreate as ProcessedLinkProps[] });
    const createdDiscountCodes = await prisma.discountCode.createMany({
      data: createdLinks.map((link) => ({
        id: createId({ prefix: "dcode_" }),
        code: link.key,
        programId: program.id,
        partnerId: link.partnerId!,
        linkId: link.id,
        discountId: partnerIdInfo[link.partnerId!].discountId,
      })),
      skipDuplicates: true,
    });
  }
}
```

Sources: [apps/web/scripts/programs/backfill-discount-codes.ts:14-113](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/backfill-discount-codes.ts#L14-L113)

Similarly, `2-import-partner-links.ts` parses `partner_links.csv` to bulk-create partner referral links with Redis cache skipping enabled:

```typescript
await bulkCreateLinks({
  links: finalPartnerLinksToImport,
  skipRedisCache: true,
});
```

Sources: [apps/web/scripts/programs/2-import-partner-links.ts:85-88](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/2-import-partner-links.ts#L85-L88)

### Customer Leads and Sales Event Ingestion Workflows

Customer lead and sales event import scripts process raw CSV records, validate foreign key associations against existing database entities, batch-ingest telemetry events into Tinybird, and update aggregate statistics counters.

The lead import execution pipeline flows through specific processing stages: `Papa.parse()` streams raw records → validates external IDs and matching partner links → groups clicks by year for Tinybird partition safety → `prisma.customer.createMany()` persists records → `recordLeadWithTimestamp()` registers sign-up telemetry → batched chunk updates increment link stats and invoke `syncPartnerLinksStats()`.

Sources: [apps/web/scripts/programs/3-import-customer-leads.ts:24-324](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/3-import-customer-leads.ts#L24-L324)

> [!WARNING]
> ClickHouse enforces a strict maximum limit of 12 partitions (months) for any given event backfill operation. Consequently, lead and click backfill scripts must partition events by year using `reduce()` before posting payloads to Tinybird endpoints. Sources: [apps/web/scripts/programs/3-import-customer-leads.ts:157-169](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/3-import-customer-leads.ts#L157-L169), [apps/web/scripts/programs/3-import-customer-leads.ts:237-246](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/3-import-customer-leads.ts#L237-L246)

### Commission Ledger Seeding and Cron Queuing

Commissions and partner payouts can be initialized via direct database seeders or enqueued background jobs. The `seed-commissions.ts` utility bulk-creates referral commission records with explicit earnings and quantities:

```typescript
  const commissions: Prisma.CommissionCreateManyInput[] = [
    {
      id: createId({ prefix: "cm_" }),
      programId,
      partnerId,
      type: "referral",
      amount: 0,
      quantity: 1,
      earnings: 10000,
      createdAt: new Date(),
    },
  ];

  const response = await prisma.commission.createMany({
    data: commissions,
    skipDuplicates: true,
  });
```

Sources: [apps/web/scripts/dev/seed-commissions.ts:10-36](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-commissions.ts#L10-L36)

To test asynchronous partner referral processing, `test-partner-referrals.ts` queries existing payouts for a target program and dispatches batch queue jobs to the referral commission endpoint.

```typescript
async function main() {
  const payouts = await prisma.payout.findMany({
    where: {
      programId: "prog_1K2J9DRWPPJ2F1RX53N92TSGA",
    },
  });

  await enqueueBatchJobs(
    payouts.map(({ id }) => ({
      queueName: "create-referral-commissions",
      url: `${APP_DOMAIN_WITH_NGROK}/api/cron/commissions/referrals/queue`,
      body: { payoutId: id },
    })),
  );
}
```

Sources: [apps/web/scripts/dev/test-partner-referrals.ts:7-22](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/test-partner-referrals.ts#L7-L22)

| Script Path | Primary Target Entity | Associated API / Batch Utility | Key Configuration / Filter Criteria |
| :--- | :--- | :--- | :--- |
| `apps/web/scripts/programs/2-import-partner-links.ts` | `Link` | `bulkCreateLinks()` | `skipRedisCache: true` |
| `apps/web/scripts/programs/3-import-customer-leads.ts` | `Customer`, Tinybird Leads | `recordLeadWithTimestamp()` | Partitioned by calendar year |
| `apps/web/scripts/programs/5-import-customer-sales.ts` | `Commission`, Tinybird Sales | `recordSaleWithTimestamp()`, `calculateSaleEarnings()` | Filters out zero-amount invoices |
| `apps/web/scripts/programs/backfill-discount-codes.ts` | `DiscountCode`, `Link` | `bulkCreateLinks()` | Matches enrolled partner emails |
| `apps/web/scripts/dev/seed-commissions.ts` | `Commission` | `prisma.commission.createMany()` | `skipDuplicates: true` |
| `apps/web/scripts/dev/test-partner-referrals.ts` | Queue Jobs | `enqueueBatchJobs()` | Target queue: `create-referral-commissions` |

Sources: [apps/web/scripts/programs/2-import-partner-links.ts:85-88](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/2-import-partner-links.ts#L85-L88), [apps/web/scripts/programs/3-import-customer-leads.ts:175-189](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/3-import-customer-leads.ts#L175-L189), [apps/web/scripts/programs/5-import-customer-sales.ts:157-191](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/5-import-customer-sales.ts#L157-L191), [apps/web/scripts/programs/backfill-discount-codes.ts:88-110](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/backfill-discount-codes.ts#L88-L110), [apps/web/scripts/dev/seed-commissions.ts:33-36](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-commissions.ts#L33-L36), [apps/web/scripts/dev/test-partner-referrals.ts:14-21](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/test-partner-referrals.ts#L14-L21)

| Design Choice | Benefit | Cost |
| :--- | :--- | :--- |
| **Year-Based Batch Partitioning** | Prevents ClickHouse partition limit errors during high-volume backfills | Requires additional reduction logic and multi-request dispatch loops |
| **SkipDuplicates Prisma Flags** | Allows idempotent script reruns without primary key collision failures | Requires secondary checks if update semantics are needed instead of silent ignores |
| **Chunked Link & Customer Updates** | Avoids overwhelming database connection pools during massive metric increments | Increases overall execution time via serialized chunk iteration |

Sources: [apps/web/scripts/programs/3-import-customer-leads.ts:157-189](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/3-import-customer-leads.ts#L157-L189), [apps/web/scripts/programs/5-import-customer-sales.ts:218-250](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/5-import-customer-sales.ts#L218-L250), [apps/web/scripts/dev/seed-commissions.ts:33-36](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-commissions.ts#L33-L36)

## Application Events and Stats Aggregation

### Overview

Partner application lifecycles and aggregate streaming statistics are populated via specialized development seeding scripts and historical migration backfills. These scripts map the progression of partner program applications through distinct temporal phases—ranging from initial landing page visits and application starts to form submissions and subsequent administrative approvals or rejections—while simultaneously aggregating link activity metrics for real-time publishing to Upstash Redis streams.

Sources: [apps/web/scripts/dev/seed-application-events.ts:1-97](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-application-events.ts#L1-L97), [apps/web/scripts/partners/aggregate-stats-seeding.ts:5-65](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/partners/aggregate-stats-seeding.ts#L5-L65), [apps/web/scripts/migrations/backfill-application-events.ts:9-99](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-application-events.ts#L9-L99)

### Application Event Seeding and Backfill Workflows

Development seeding generates mock `ProgramApplicationEvent` records for the ACME program (`ACME_PROGRAM_ID`) by querying existing partner enrollments that exclude predefined referral partner IDs. For each selected enrollment, timestamps are calculated sequentially: a random visit date within the preceding thirty days, an application start time 1 to 30 minutes later, submission 1 to 90 minutes after starting, and a decision timestamp 5 to 72 hours post-submission. 

```typescript
const { count } = await prisma.programApplicationEvent.createMany({
  data: programEnrollments.map((programEnrollment) => {
    const visitedAt = randomDateBetween(thirtyDaysAgo, now);
    const startedAt = new Date(
      visitedAt.getTime() + randomInt(1, 30) * 60 * 1000,
    );
    const submittedAt = new Date(
      startedAt.getTime() + randomInt(1, 90) * 60 * 1000,
    );
    const decidedAt = new Date(
      submittedAt.getTime() + randomInt(5, 72) * 60 * 60 * 1000,
    );
    const isApproved = Math.random() < 0.7;

    return {
      programId,
      partnerId: programEnrollment.partnerId,
      visitedAt,
      startedAt,
      submittedAt,
      approvedAt: isApproved ? decidedAt : null,
      rejectedAt: isApproved ? null : decidedAt,
      referralSource: sample(referralSources),
      country: programEnrollment.partner.country ?? null,
      referredByPartnerId: sample(referredByPartnerIds),
    };
  }),
  skipDuplicates: true,
});
```

Sources: [apps/web/scripts/dev/seed-application-events.ts:28-92](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-application-events.ts#L28-L92)

For production migrations, `backfill-application-events.ts` iterates continuously through unindexed program applications in batches of 250, mapping each record to a `ProgramApplicationEventCreateManyInput` payload.

```typescript
async function main() {
  while (true) {
    const programApplications = await prisma.programApplication.findMany({
      where: {
        programApplicationEvents: {
          none: {},
        },
      },
      include: {
        enrollment: {
          include: {
            partner: true,
            links: {
              orderBy: {
                createdAt: "asc",
              },
              take: 1,
            },
          },
        },
      },
      take: 250,
    });

    if (programApplications.length === 0) {
      console.log("No program applications found, skipping...");
      break;
    }

    const programApplicationEvents: Prisma.ProgramApplicationEventCreateManyInput[] =
      programApplications.map((programApplication) => {
        const { programId, enrollment } = programApplication;
        const referralSource =
          enrollment &&
          programApplication.createdAt > new Date("2025-12-09") &&
          differenceInSeconds(
            enrollment.createdAt,
            programApplication.createdAt,
          ) < 5
            ? "marketplace"
            : "direct";

        const visitedAt = subSeconds(
          programApplication.createdAt,
          300 + randomInt(0, 300),
        );
        const startedAt = addSeconds(visitedAt, randomInt(15, 45));
        const reviewedAt =
          programApplication.reviewedAt ??
          enrollment?.links?.[0]?.createdAt ??
          programApplication.updatedAt;

        return {
          id: createId({ prefix: "pga_evt_" }),
          programId: programId,
          country: programApplication.country ?? enrollment?.partner?.country,
          referralSource,
          programApplicationId: programApplication.id,
          partnerId: enrollment?.partnerId,
          visitedAt,
          startedAt,
          submittedAt: programApplication.createdAt,
          ...(enrollment
            ? ACTIVE_ENROLLMENT_STATUSES.includes(enrollment.status)
              ? { approvedAt: reviewedAt }
              : enrollment.status === "rejected"
                ? { rejectedAt: reviewedAt }
                : {}
            : {}),
        };
      });

    const { count } = await prisma.programApplicationEvent.createMany({
      data: programApplicationEvents,
    });

    console.log(`Created ${count} program application events`);
  }
}
```

Sources: [apps/web/scripts/migrations/backfill-application-events.ts:9-98](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-application-events.ts#L9-L98)

> [!NOTE]
> The application backfill script evaluates marketplace attribution by checking whether an enrollment was created within 5 seconds of a program application occurring after the December 9, 2025 marketplace launch date.

Sources: [apps/web/scripts/migrations/backfill-application-events.ts:42-52](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-application-events.ts#L42-L52)

### Aggregate Statistics and Stream Publishing

To test partner performance tracking and activity streaming, `aggregate-stats-seeding.ts` groups link records by partner and program where clicks exceed zero, sorting descending by total sale amount. It then slices a 5,000-item batch and publishes lead activity events to Redis streams via `publishPartnerActivityEvent`.

```typescript
async function main() {
  const partnerLinksWithActivity = await prisma.link.groupBy({
    by: ["partnerId", "programId"],
    where: {
      programId: { not: null },
      partnerId: { not: null },
      clicks: { gt: 0 },
    },
    _sum: {
      clicks: true,
      leads: true,
      conversions: true,
      sales: true,
      saleAmount: true,
    },
    orderBy: {
      _sum: {
        saleAmount: "desc",
      },
    },
  });

  const BATCH = 9;
  const batchedPartnerLinksWithActivity = partnerLinksWithActivity.slice(
    BATCH * 5000,
    (BATCH + 1) * 5000,
  );
  
  await Promise.all(
    batchedPartnerLinksWithActivity.map(async (partnerLink) => {
      await publishPartnerActivityEvent({
        partnerId: partnerLink.partnerId!,
        programId: partnerLink.programId!,
        eventType: "lead",
        timestamp: new Date().toISOString(),
      });
    }),
  );
  
  console.log(
    `Seeded ${batchedPartnerLinksWithActivity.length} partner links with activity`,
  );
}
```

Sources: [apps/web/scripts/partners/aggregate-stats-seeding.ts:6-64](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/partners/aggregate-stats-seeding.ts#L6-L64)

| Script File | Target Model / Store | Key Grouping / Filtering Logic | Output / Action |
| :--- | :--- | :--- | :--- |
| `seed-application-events.ts` | `ProgramApplicationEvent` | Filters enrollments excluding `referredByPartnerIds` | Inserts randomized mock application lifecycle timestamps |
| `backfill-application-events.ts` | `ProgramApplicationEvent` | Queries `ProgramApplication` records where `programApplicationEvents` is empty | Batch-creates events with derived marketplace or direct attribution |
| `aggregate-stats-seeding.ts` | `Link` & Redis Streams | Groups links by `partnerId` and `programId` where `clicks > 0` | Publishes batched `lead` activity events via Upstash Redis |

Sources: [apps/web/scripts/dev/seed-application-events.ts:28-92](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/dev/seed-application-events.ts#L28-L92), [apps/web/scripts/partners/aggregate-stats-seeding.ts:7-61](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/partners/aggregate-stats-seeding.ts#L7-L61), [apps/web/scripts/migrations/backfill-application-events.ts:11-95](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/migrations/backfill-application-events.ts#L11-L95)

## Billing Reconciliation and Invoice Sync

### Invoice Synchronization and Commission Reconciliation

Billing reconciliation scripts manage external Stripe invoices, synchronize payments with Tinybird analytics, and correct partner commission imbalances caused by misattributed coupon codes or links.

Sources: [apps/web/scripts/customers/upheal/sync-stripe-invoices.ts:1-242](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/upheal/sync-stripe-invoices.ts#L1-L242), [apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts:1-274](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts#L1-L274)

### Stripe Invoice Synchronization Pipeline

The script `sync-stripe-invoices.ts` iterates through customers enrolled in a program who possess a Stripe customer ID, queries paid Stripe invoices via `stripeAppClient` under the workspace's Stripe Connect account, and filters out zero-dollar invoices or records with matching invoice IDs or close timestamp proximity.

Sources: [apps/web/scripts/customers/upheal/sync-stripe-invoices.ts:13-84](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/upheal/sync-stripe-invoices.ts#L13-L84)

```typescript
const invoices = await stripeAppClient({
  mode: "live",
}).invoices.list(
  {
    customer: customer.stripeCustomerId!,
    status: "paid",
    limit: 100,
  },
  {
    stripeAccount: program.workspace.stripeConnectId!,
  },
);
```

Sources: [apps/web/scripts/customers/upheal/sync-stripe-invoices.ts:49-60](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/upheal/sync-stripe-invoices.ts#L49-L60)

> [!WARNING]
> When evaluating existing commissions against fetched Stripe invoices, the script checks both the `invoiceId` match and whether the commission amount equals `amount_paid` within a +/- 1-hour timestamp window to prevent duplicate records.

Sources: [apps/web/scripts/customers/upheal/sync-stripe-invoices.ts:69-84](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/upheal/sync-stripe-invoices.ts#L69-L84)

### Commission Repair and Overpayment Balancing

When partner attribution needs correction across coupon codes or links, repair scripts such as `fix-case-a-complex.ts` and `fix-case-c.ts` balance ledgers where payouts were incorrectly dispatched.

Sources: [apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts:10-227](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts#L10-L227), [apps/web/scripts/customers/beehiiv/fix-case-c.ts:8-174](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-c.ts#L8-L174)

```typescript
const createdCommission = await prisma.commission.create({
  data: {
    id: createId({ prefix: "cm_" }),
    type: "custom",
    partnerId: link.currenctPartnerId!,
    programId: PROGRAM_ID,
    description: `Overpayment for payout ${payoutId}`,
    earnings: paidCommissionsTotal,
    payoutId,
    amount: 0,
    quantity: 1,
    userId: link.userId,
    status: "paid",
  },
});

const createdClawback = await prisma.commission.create({
  data: {
    id: createId({ prefix: "cm_" }),
    type: "custom",
    partnerId: link.currenctPartnerId!,
    programId: PROGRAM_ID,
    description: `Clawback for commission "${createdCommission.id}" (overpayment from incorrectly imported discount code "${link.code}")`,
    earnings: -paidCommissionsTotal,
    amount: 0,
    quantity: 1,
    userId: link.userId,
  },
});
```

Sources: [apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts:190-223](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/fix-case-a-complex.ts#L190-L223)

## Related

- [Quick Start](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/getting-started/quick-start)
- [Database Schema and Prisma](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/core-architecture/database-schema-and-prisma)


## Sitemap

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