---
title: "Tinybird Analytics Engine"
description: "The Tinybird Analytics Engine powers high-performance event ingestion, real-time conversion tracking, and multi-dimensional timeseries aggregation across clicks, leads, and sales. Built around Tiny..."
last_updated: "2026-10-05T05:07:35.222511+00:00"
canonical_url: "https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/analytics-and-tracking/tinybird-analytics-engine"
---

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

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

- [apps/web/lib/tinybird/record-lead.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-lead.ts)
- [apps/web/lib/postback/record-postback-event.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/postback/record-postback-event.ts)
- [apps/web/lib/tinybird/record-webhook-event.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-webhook-event.ts)
- [apps/web/lib/tinybird/record-sale.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-sale.ts)
- [apps/web/app/ee/api/track/application/route.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/track/application/route.ts)
- [apps/web/app/ee/api/cron/framer/backfill-leads-batch/route.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/cron/framer/backfill-leads-batch/route.ts)
- [apps/web/lib/tinybird/get-lead-events.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-lead-events.ts)
- [apps/web/scripts/tinybird/get-sale-events.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/get-sale-events.ts)
- [apps/web/app/ee/api/track/lead/client/route.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/track/lead/client/route.ts)
- [apps/web/lib/tinybird/record-click-zod.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts)
- [apps/web/lib/tinybird/record-link.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-link.ts)
- [apps/web/lib/tinybird/log-import-error.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/log-import-error.ts)
- [apps/web/app/ee/api/track/lead/route.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/track/lead/route.ts)
- [apps/web/lib/postback/get-postback-events.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/postback/get-postback-events.ts)
- [apps/web/lib/tinybird/get-customer-events-tb.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-customer-events-tb.ts)
- [apps/web/lib/zod/schemas/leads.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/zod/schemas/leads.ts)
- [apps/web/lib/api/conversions/track-lead.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/conversions/track-lead.ts)
- [apps/web/scripts/tinybird/delete-lead-event.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/delete-lead-event.ts)
- [apps/web/scripts/tinybird/update-lead-event.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/update-lead-event.ts)
- [apps/web/lib/tinybird/get-webhook-events.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-webhook-events.ts)
- [apps/web/lib/analytics/get-analytics.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-analytics.ts)
- [apps/web/lib/tinybird/record-click.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click.ts)
- [apps/web/lib/tinybird/get-lead-event.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-lead-event.ts)
- [apps/web/lib/api/audit-logs/record-audit-log.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/audit-logs/record-audit-log.ts)
- [apps/web/scripts/customers/beehiiv/update-sale-events.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/update-sale-events.ts)
- [apps/web/lib/tinybird/index.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/index.ts)
- [apps/web/lib/integrations/segment/transform.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/integrations/segment/transform.ts)
- [apps/web/scripts/tinybird/update-sale-event.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/update-sale-event.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/analytics/get-events.ts](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-events.ts)
</details>

## Overview

The Tinybird Analytics Engine powers high-performance event ingestion, real-time conversion tracking, and multi-dimensional timeseries aggregation across clicks, leads, and sales. Built around Tinybird data sources and pipes, it ingests high-volume click streams, attribute conversions, and synchronizes metadata to drive analytics and reporting dashboards.

Sources: [apps/web/lib/tinybird/record-lead.ts:5-8](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-lead.ts#L5-L8), [apps/web/lib/tinybird/record-sale.ts:5-8](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-sale.ts#L5-L8), [apps/web/lib/analytics/get-analytics.ts:99-123](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-analytics.ts#L99-L123)

## Tinybird Client and Pipeline Architecture

### Overview

The analytics subsystem relies on Tinybird builder utilities to instantiate ingestion endpoints and query pipes, routing streaming click data, webhook notifications, postback events, and customer timeline queries through strongly-typed Zod schemas.

Sources: [apps/web/lib/tinybird/record-click-zod.ts:1-44](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L1-L44), [apps/web/lib/tinybird/record-webhook-event.ts:1-7](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-webhook-event.ts#L1-L7), [apps/web/lib/postback/record-postback-event.ts:1-7](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/postback/record-postback-event.ts#L1-L7), [apps/web/lib/tinybird/get-customer-events-tb.ts:1-25](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-customer-events-tb.ts#L1-L25)

### Ingestion and Query Endpoints

Tinybird ingestion and query pipelines are constructed via `tb.buildIngestEndpoint` and `tb.buildPipe` methods, mapping structured domain models to specific data sources and pipes.

| Endpoint / Pipe Variable | Target Source / Pipe | Configuration / Schema Constraints | Source File |
| :--- | :--- | :--- | :--- |
| `recordClickZod` | `dub_click_events` | `wait: true`, uses `recordClickZodSchema` (with default fallback strings/numbers for geographic, device, and request properties) | [apps/web/lib/tinybird/record-click-zod.ts:39-43](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L39-L43) |
| `recordWebhookEvent` | `dub_webhook_events` | Omits `timestamp` from `webhookEventSchemaTB` | [apps/web/lib/tinybird/record-webhook-event.ts:4-7](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-webhook-event.ts#L4-L7) |
| `recordPostbackEvent` | `dub_postback_events` | Omits `timestamp` from `postbackEventInputSchemaTB` | [apps/web/lib/postback/record-postback-event.ts:4-7](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/postback/record-postback-event.ts#L4-L7) |
| `pipe` (`getCustomerEventsTB`) | `v2_customer_events` | Parameters and data typed via `z.any()` placeholders | [apps/web/lib/tinybird/get-customer-events-tb.ts:4-8](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-customer-events-tb.ts#L4-L8) |

Sources: [apps/web/lib/tinybird/record-click-zod.ts:39-43](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L39-L43), [apps/web/lib/tinybird/record-webhook-event.ts:4-7](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-webhook-event.ts#L4-L7), [apps/web/lib/postback/record-postback-event.ts:4-7](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/postback/record-postback-event.ts#L4-L7), [apps/web/lib/tinybird/get-customer-events-tb.ts:4-8](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-customer-events-tb.ts#L4-L8)

### Customer Events Retrieval Workflow

The customer timeline query flow wraps the underlying Tinybird pipe call inside an asynchronous helper function (`getCustomerEventsTB`) that accepts customer identifiers, optional link filters, and result constraints.

```typescript
export const getCustomerEventsTB = async ({
  customerId,
  linkIds,
  limit,
}: {
  customerId: string;
  linkIds?: string[];
  limit?: number;
}) => {
  return await pipe({
    customerId,
    ...(linkIds ? { linkIds } : {}),
    ...(limit ? { limit } : {}),
  });
};
```

Sources: [apps/web/lib/tinybird/get-customer-events-tb.ts:10-24](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-customer-events-tb.ts#L10-L24)

> [!NOTE]
> Ingestion endpoints such as `recordClickZod` explicitly configure synchronous waiting (`wait: true`), ensuring that event payloads validated by `recordClickZodSchema` are confirmed upon insertion into the `dub_click_events` data source.
> Sources: [apps/web/lib/tinybird/record-click-zod.ts:39-43](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L39-L43)

## Click Ingestion and Redis Buffering

### Overview

The click recording subsystem manages incoming link requests through validation checks, bot filtration, QR code detection, metadata enrichment, and asynchronous background buffering. Handled primarily by `recordClick` in `apps/web/lib/tinybird/record-click.ts` and validated via `recordClickZod` using `recordClickZodSchema` in `apps/web/lib/tinybird/record-click-zod.ts`, the pipeline processes incoming click requests while applying rate-limiting deduplication via Redis.

Sources: [apps/web/lib/tinybird/record-click.ts:1-236](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click.ts#L1-L236), [apps/web/lib/tinybird/record-click-zod.ts:1-44](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L1-L44)

### Execution Walkthrough and Validation

When a click request arrives, the `recordClick` function executes an explicit sequence of validation and enrichment steps before scheduling asynchronous persistence:

1. `!clickId` check: Validates that a `clickId` is present; returns `null` if absent.
2. `dub-no-track` header or query check: Inspects request headers and search parameters for the tracking opt-out flag.
3. `detectBot(req)`: Evaluates bot signatures against incoming user-agent data, skipping bot verification if `trigger === "deeplink"`.
4. `getIdentityHash(req)`: Computes an identity hash representing the client fingerprint for rate-limiting.
5. `recordClickCache.get(...)`: Checks Redis to deduplicate clicks for the domain and key pair from the same IP address within a one-hour window.
6. `detectQr(req)`: Checks if the request originated from a QR code scan, updating `trigger` to `"qr"`.
7. Metadata Enrichment: Extracts geolocation (`continent`, `country`, `region`, `city`, `latitude`, `longitude`, `vercel_region`), IP address, user-agent parsing (device, browser, OS, CPU architecture), and referrer information.
8. `waitUntil(...)`: Dispatches asynchronous background tasks including Tinybird event ingestion, Redis cache updates, and Redis stream event publishing.

Sources: [apps/web/lib/tinybird/record-click.ts:53-233](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click.ts#L53-L233)

> [!WARNING]
> If `skipRatelimit` is false, a cache hit in `recordClickCache` causes the function to return `null` immediately without recording a click. If Redis throws an error during this check, the function catches the exception and returns `null` to prevent overwhelming Tinybird or MySQL.
> Sources: [apps/web/lib/tinybird/record-click.ts:84-100](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click.ts#L84-L100)

### Event Payload Schema and Defaults

The ingested click payload conforms to `recordClickZodSchema`, which defines default fallback values for geographic, device, and request properties.

| Field Name | Type / Zod Definition | Default Value | Source File |
| :--- | :--- | :--- | :--- |
| `timestamp` | `z.string()` | `""` | [apps/web/lib/tinybird/record-click-zod.ts:5](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L5) |
| `identity_hash` | `z.string()` | `""` | [apps/web/lib/tinybird/record-click-zod.ts:6](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L6) |
| `click_id` | `z.string()` | `""` | [apps/web/lib/tinybird/record-click-zod.ts:7](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L7) |
| `workspace_id` | `z.string()` | `""` | [apps/web/lib/tinybird/record-click-zod.ts:8](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L8) |
| `link_id` | `z.string()` | `""` | [apps/web/lib/tinybird/record-click-zod.ts:9](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L9) |
| `domain` | `z.string()` | `""` | [apps/web/lib/tinybird/record-click-zod.ts:10](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L10) |
| `key` | `z.string()` | `""` | [apps/web/lib/tinybird/record-click-zod.ts:11](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L11) |
| `country` | `z.string()` | `"Unknown"` | [apps/web/lib/tinybird/record-click-zod.ts:15](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L15) |
| `device` | `z.string()` | `"Desktop"` | [apps/web/lib/tinybird/record-click-zod.ts:21](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L21) |
| `browser` | `z.string()` | `"Unknown"` | [apps/web/lib/tinybird/record-click-zod.ts:24](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L24) |
| `os` | `z.string()` | `"Unknown"` | [apps/web/lib/tinybird/record-click-zod.ts:28](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L28) |
| `bot` | `z.number()` | `0` | [apps/web/lib/tinybird/record-click-zod.ts:32](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L32) |
| `qr` | `z.number()` | `0` | [apps/web/lib/tinybird/record-click-zod.ts:33](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L33) |
| `referer` | `z.string()` | `"(direct)"` | [apps/web/lib/tinybird/record-click-zod.ts:34](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L34) |
| `trigger` | `z.string()` | `"link"` | [apps/web/lib/tinybird/record-click-zod.ts:36](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L36) |

Sources: [apps/web/lib/tinybird/record-click-zod.ts:4-37](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click-zod.ts#L4-L37)

### Asynchronous Buffering and Redis Integration

Once click metadata is compiled, `recordClick` buffers and persists events concurrently in the background using `waitUntil` and `Promise.allSettled`. The asynchronous block executes four parallel operations:

1. Tinybird Event Ingestion: Sends a POST request to `${process.env.TINYBIRD_API_URL}/v0/events?name=dub_click_events&wait=true` authenticated via Bearer token.
2. Rate-Limit Cache Update: Calls `recordClickCache.set(...)` to store the click identifier in Redis for 1 hour, preventing duplicate clicks from the same identity hash.
3. Link Click Stream: Publishes a link click event via `publishLinkClickEvent(...)`.
4. Workspace Click Stream: Publishes the complete `clickData` payload via `publishWorkspaceClickEvent(clickData)`.

Sources: [apps/web/lib/tinybird/record-click.ts:169-200](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click.ts#L169-L200)

> [!TIP]
> If `shouldCacheClickId` is enabled, the raw `clickData` object is cached directly in Redis under `clickIdCache:${clickId}` with a 5-minute expiration (`ex: 60 * 5`). This bridges the ingestion latency gap before newly recorded clicks become queryable directly inside Tinybird.
> Sources: [apps/web/lib/tinybird/record-click.ts:163-167](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-click.ts#L163-L167)

## Conversion Tracking: Leads and Sales

### Overview

Conversion tracking handles lead and sale events by routing incoming requests through server-side and client-side API endpoints, enforcing workspace authentication, validating payloads with Zod schemas, attributing events to existing or new customers, and recording metrics into Tinybird data sources. The core pipeline resolves customer identifiers, handles click lookups, enforces deduplication via Redis, and fans out side effects including partner commissions, workflows, webhooks, and conversion uploads.

Sources: [apps/web/app/ee/api/track/lead/route.ts:1-65](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/track/lead/route.ts#L1-L65), [apps/web/lib/api/conversions/track-lead.ts:34-407](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/conversions/track-lead.ts#L34-L407)

### Tinybird Ingestion Endpoints

Lead and sale events are pushed to Tinybird datasources using endpoints built with `tb.buildIngestEndpoint`. Each ingestion handler supports standard event records as well as variant payloads featuring explicit timestamp strings.

| Endpoint Function | Datasource | Event Schema Source | Sources |
| :--- | :--- | :--- | :--- |
| `recordLead` | `dub_lead_events` | `leadEventSchemaTB` | [apps/web/lib/tinybird/record-lead.ts:5-8](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-lead.ts#L5-L8) |
| `recordLeadWithTimestamp` | `dub_lead_events` | `leadEventSchemaTB.extend({ timestamp: z.string() })` | [apps/web/lib/tinybird/record-lead.ts:10-15](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-lead.ts#L10-L15) |
| `recordSale` | `dub_sale_events` | `saleEventSchemaTB` | [apps/web/lib/tinybird/record-sale.ts:5-8](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-sale.ts#L5-L8) |
| `recordSaleWithTimestamp` | `dub_sale_events` | `saleEventSchemaTB.extend({ timestamp: z.string() })` | [apps/web/lib/tinybird/record-sale.ts:10-15](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-sale.ts#L10-L15) |

Sources: [apps/web/lib/tinybird/record-lead.ts:1-16](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-lead.ts#L1-L16), [apps/web/lib/tinybird/record-sale.ts:1-16](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-sale.ts#L1-L16)

### Lead Tracking Execution Walkthrough

When a lead conversion request arrives at either the server-side API (`POST /api/track/lead`) or client-side API (`POST /api/track/lead/client`), control passes through authentication wrappers and validation layers before executing `trackLead()`.

The `trackLead` operation executes the following call chain:

1. `prisma.customer.findUnique(...)` — Queries the database to locate any existing customer record matching the workspace and `customerExternalId`.
2. `redis.set(...)` — If `mode` is not `deferred`, attempts to set a 1-week Redis key `trackLead:${workspace.id}:${customerExternalId}:${stringifiedEventName}` with `nx: true` for event deduplication. If `res === null`, `isDuplicateEvent` is marked true.
3. `getClickEvent(...)` — Retrieves click metadata associated with the resolved `clickId`.
4. `prisma.link.findUnique(...)` — Fetches the referral link to verify ownership, active status (`disabledAt`), and workspace alignment.
5. `getOrCreateCustomer(...)` — If no customer record exists in PostgreSQL, provisions a new customer linked to the click, partner program, and country data.
6. `redis.set(...)` (Wait Mode) — If `mode === "wait"`, caches the lead event payload in Redis for 5 minutes under `leadCache:${customer.id}` and `leadCache:${customer.id}:${stringifiedEventName}` to bridge Tinybird ingestion latency.
7. `recordLead(...)` — Invokes the Tinybird ingestion endpoint in a `waitUntil` background block (unless `mode === "deferred"`).
8. Side-effect dispatchers — Concurrently executes `prisma.link.update(...)`, `prisma.project.update(...)`, `queuePartnerCommissionCreation(...)`, `executeWorkflows(...)`, `syncPartnerLinksStats(...)`, `sendWorkspaceWebhook(...)`, `queueGoogleAdsConversionUpload(...)`, and `sendPartnerPostback(...)`.

Sources: [apps/web/lib/api/conversions/track-lead.ts:50-392](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/conversions/track-lead.ts#L50-L392)

> [!WARNING]
> If a request omits `clickId`, `trackLead` requires an existing customer record linked to the provided `customerExternalId` in order to inherit its stored `clickId`. If neither is found, the operation immediately throws a `bad_request` `DubApiError`.
> Sources: [apps/web/lib/api/conversions/track-lead.ts:60-72](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/api/conversions/track-lead.ts#L60-L72)

### Client-Side Tracking and Origin Verification

Client-side lead tracking (`POST /api/track/lead/client`) is protected by publishable keys and verifies that request origins comply with workspace configurations.

```typescript
export const POST = withPublishableKey(
  async ({ req, workspace }) => {
    const body = await parseRequestBody(req);

    const allowRequest = verifyAnalyticsAllowedHostnames({
      allowedHostnames: (workspace?.allowedHostnames ?? []) as string[],
      req,
    });

    if (!allowRequest) {
      throw new DubApiError({
        code: "forbidden",
        message: `Request origin '${getHostnameFromRequest(req)}' is not included in the allowed hostnames for this workspace.`,
      });
    }

    const parsed = trackLeadRequestSchema.parse(body);
    const response = await trackLead({ ...parsed, workspace });
    return NextResponse.json(response, { headers: COMMON_CORS_HEADERS });
  },
  {
    requiredPlan: ["business", "advanced", "enterprise"],
  },
);
```

Sources: [apps/web/app/ee/api/track/lead/client/route.ts:14-60](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/track/lead/client/route.ts#L14-L60)

> [!NOTE]
> Client-side tracking routes automatically respond to `OPTIONS` preflight requests with `COMMON_CORS_HEADERS` and a `204` status code to support cross-origin browser requests.
> Sources: [apps/web/app/ee/api/track/lead/client/route.ts:62-67](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/track/lead/client/route.ts#L62-L67)

## Schema Definitions and Event Types

### Overview

The analytics subsystem relies on rigorous Zod schemas, transformation pipelines, and data models to validate and ingest events into Tinybird. Data structures govern lead conversions, link metadata recordings, error logging, and external integration mappings.

Sources: [apps/web/lib/zod/schemas/leads.ts:1-147](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/zod/schemas/leads.ts#L1-L147), [apps/web/lib/tinybird/record-link.ts:1-90](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-link.ts#L1-L90), [apps/web/lib/tinybird/log-import-error.ts:1-8](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/log-import-error.ts#L1-L8), [apps/web/lib/integrations/segment/transform.ts:1-137](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/integrations/segment/transform.ts#L1-L137)

### Lead and Link Schema Definitions

Incoming tracking requests and database records undergo validation using strongly typed Zod definitions. The `trackLeadRequestSchema` enforces constraints on fields such as `clickId`, `eventName`, `customerExternalId`, and tracking `mode`. 

Sources: [apps/web/lib/zod/schemas/leads.ts:8-69](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/zod/schemas/leads.ts#L8-L69)

| Schema Name | Target Source / Datasource | Key Validation Rules |
| :--- | :--- | :--- |
| `trackLeadRequestSchema` | API Request Payload | `clickId` (string, trim), `eventName` (1-255 chars), `customerExternalId` (1-100 chars), `mode` (`async`, `wait`, `deferred`) |
| `leadEventSchemaTB` | Tinybird Ingestion (`dub_leads`) | Omits `timestamp` (generated by Tinybird), extends `clickEventSchemaTB` with `event_id`, `event_name`, `customer_id`, `metadata` |
| `dubLinksMetadataSchema` | Tinybird Ingestion (`dub_links_metadata`) | Transforms `created_at` date to SQL string format, maps `deleted` boolean to integer (`1`/`0`), sets defaults for nullable IDs |

Sources: [apps/web/lib/zod/schemas/leads.ts:8-101](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/zod/schemas/leads.ts#L8-L101), [apps/web/lib/tinybird/record-link.ts:6-44](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-link.ts#L6-L44)

> [!NOTE]
> Tinybird ingestion schemas omit client-side timestamps so that Tinybird can generate its own authoritative ingestion timestamps.
> Sources: [apps/web/lib/zod/schemas/leads.ts:94-96](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/zod/schemas/leads.ts#L94-L96)

### Transformation and Ingestion Pipelines

The data transformation layer normalizes internal database objects and webhook payloads before dispatching them to Tinybird endpoints or third-party analytics integrations.

```typescript
const transformLinkTB = (link: ExpandedLink) => {
  const key = decodeKeyIfCaseSensitive({
    domain: link.domain,
    key: link.key,
  });

  return {
    link_id: link.id,
    domain: link.domain,
    key,
    url: link.url,
    tag_ids: link.tags?.map(({ tag }) => tag.id) ?? [],
    folder_id: link.folderId ?? "",
    tenant_id: link.tenantId ?? "",
    program_id: link.programId ?? "",
    partner_id: link.partnerId ?? "",
    partner_group_id: link.programEnrollment?.groupId ?? "",
    partner_tag_ids:
      link.programEnrollment?.programPartnerTags?.map(
        ({ partnerTagId }) => partnerTagId,
      ) ?? [],
    workspace_id: link.projectId,
    created_at: link.createdAt,
  };
};
```

Sources: [apps/web/lib/tinybird/record-link.ts:52-76](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/record-link.ts#L52-L76)

For Segment integrations, webhook payloads are normalized through `formatEventForSegment()`, mapping event types to target properties:

| Webhook Event | Segment Event Name | User ID Source | Properties Included |
| :--- | :--- | :--- | :--- |
| `link.clicked` | `Link Clicked` | `click.id` (anonymousId) | `click`, `link` |
| `lead.created` | `capitalize(eventName)` | `customer.externalId` | `click`, `link`, `customer` |
| `sale.created` | `capitalize(eventName)` | `customer.externalId` | `click`, `link`, `customer`, `sale`, `revenue`, `currency` |
| `partner.enrolled` | `Partner Enrolled` | `partner.id` | `partner`, `links` |

Sources: [apps/web/lib/integrations/segment/transform.ts:17-113](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/integrations/segment/transform.ts#L17-L113)

> [!CAUTION]
> Unsupported Segment event types trigger an immediate runtime error (`Event ${event} is not supported for Segment.`), halting the transformation pipeline.
> Sources: [apps/web/lib/integrations/segment/transform.ts:31-33](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/integrations/segment/transform.ts#L31-L33)

## Timeseries Querying and Aggregation Engine

### Overview

Analytics metrics are retrieved and aggregated through Tinybird pipe queries and MySQL fallback paths. The querying subsystem parses parameters, handles timezones, formats dates for ClickHouse, and executes parameterized data pipelines for timeseries, events, and lead lookups.

Sources: [apps/web/lib/analytics/get-analytics.ts:27-196](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-analytics.ts#L27-L196), [apps/web/lib/analytics/get-events.ts:35-168](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-events.ts#L35-L168)

### Pipeline Execution and Query Flow

The analytics retrieval functions execute distinct initialization steps before dispatching calls to Tinybird pipes or relational databases. 

```typescript
export const getLeadEvent = async ({
  customerId,
  eventName,
}: {
  customerId: string;
  eventName?: string | null;
}) => {
  try {
    const cachedLeadEvent = await redis.get<LeadEventTB>(
      `leadCache:${customerId}${eventName ? `:${eventName.toLowerCase().replaceAll(" ", "-")}` : ""}`,
    );

    if (cachedLeadEvent) {
      return cachedLeadEvent;
    }
  } catch (_e) {}

  try {
    const { data } = await getLeadEventTB({ customerId, eventName });
    return data[0];
  } catch (error) {
    console.error(
      `[getLeadEvent] Error getting lead event for customerId: ${customerId}${eventName ? ` and eventName: ${eventName}` : ""}`,
      error,
    );
    return null;
  }
};
```

Sources: [apps/web/lib/tinybird/get-lead-event.ts:16-45](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-lead-event.ts#L16-L45)

> [!NOTE]
> `getLeadEvent` checks Upstash Redis cache using a namespaced key (`leadCache:${customerId}...`) before falling back to querying the Tinybird `get_lead_event` pipe.
> Sources: [apps/web/lib/tinybird/get-lead-event.ts:24-37](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-lead-event.ts#L24-L37)

### Analytics Query Parameters and Endpoints

Query parameters determine whether requests target optimized MySQL tables (such as all-time link clicks) or dynamic Tinybird pipes (`v4_count`, `v4_timeseries`, `v4_group_by`, `v4_events`, `get_lead_events`, `get_lead_event`).

Sources: [apps/web/lib/analytics/get-analytics.ts:54-78](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-analytics.ts#L54-L78), [apps/web/lib/analytics/get-analytics.ts:99-123](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-analytics.ts#L99-L123), [apps/web/lib/analytics/get-events.ts:77-86](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-events.ts#L77-L86), [apps/web/lib/tinybird/get-lead-events.ts:5-11](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-lead-events.ts#L5-L11), [apps/web/lib/tinybird/get-lead-event.ts:7-14](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-lead-event.ts#L7-L14)

| Pipe Function | Target Pipe / Datasource | Parameter Validation Schema | Purpose |
| :--- | :--- | :--- | :--- |
| `getAnalytics` (MySQL shortcut) | `Link` (MySQL table via PlanetScale) | N/A | Fast retrieval of all-time counts when `interval === "all"`, no custom dates/filters are set |
| `tb.buildPipe` (`getAnalytics`) | `v4_count`, `v4_timeseries`, `v4_group_by_link_metadata`, `v4_group_by` | `analyticsFilterTB` | Timeseries and dimensional grouping metrics (clicks, leads, sales, sale amount) |
| `tb.buildPipe` (`getEvents`) | `v4_events` | `eventsFilterTB` | Paginated raw event logs for clicks, leads, and sales with metadata parsing |
| `getLeadEvents` | `get_lead_events` | `z.object({ customerIds: z.string().array() })` | Batch retrieval of lead events for a list of customer identifiers |
| `getLeadEventTB` | `get_lead_event` | `z.object({ customerId: z.string(), eventName: z.string().nullish() })` | Single lead event lookup filtered by customer ID and optional event name |

Sources: [apps/web/lib/analytics/get-analytics.ts:54-78](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-analytics.ts#L54-L78), [apps/web/lib/analytics/get-analytics.ts:99-123](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-analytics.ts#L99-L123), [apps/web/lib/analytics/get-events.ts:77-86](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-events.ts#L77-L86), [apps/web/lib/tinybird/get-lead-events.ts:5-11](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-lead-events.ts#L5-L11), [apps/web/lib/tinybird/get-lead-event.ts:7-14](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/tinybird/get-lead-event.ts#L7-L14)

> [!CAUTION]
> When `groupBy === "count"`, `interval === "all"`, and no custom date ranges or dimensional filters are present, `getAnalytics` bypasses Tinybird entirely and queries PlanetScale MySQL directly.
> Sources: [apps/web/lib/analytics/get-analytics.ts:54-78](https://github.com/blade47/dub/blob/HEAD/apps/web/lib/analytics/get-analytics.ts#L54-L78)

## Operational Maintenance and Event Lifecycle

### Overview

Operational maintenance across the analytics and event infrastructure handles batch backfills, deduplication, administrative corrections, and event deletion. Backfill scripts orchestrate large-scale data imports by reading source records (such as CSV files or webhook payloads), validating them via Zod schemas, checking existing database entries in PostgreSQL via Prisma, and batch-ingesting time-partitioned payloads into Tinybird datasources. Administrative adjustments correct event attribution by fetching historical records through Tinybird pipes, mutating link identifiers, recording updated timestamps, and issuing deletion conditions against base datasources and materialized views.

Sources: [apps/web/app/ee/api/cron/framer/backfill-leads-batch/route.ts:21-75](https://github.com/blade47/dub/blob/HEAD/apps/web/app/(ee)/api/cron/framer/backfill-leads-batch/route.ts#L21-L75), [apps/web/scripts/tinybird/delete-lead-event.ts:4-17](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/delete-lead-event.ts#L4-L17), [apps/web/scripts/tinybird/update-lead-event.ts:16-57](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/update-lead-event.ts#L16-L57), [apps/web/scripts/tinybird/update-sale-event.ts:7-48](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/update-sale-event.ts#L7-L48), [apps/web/scripts/customers/beehiiv/update-sale-events.ts:16-54](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/update-sale-events.ts#L16-L54), [apps/web/scripts/programs/3-import-customer-leads.ts:23-110](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/programs/3-import-customer-leads.ts#L23-L110)

### Batch Import and Backfill Execution

Large-scale customer and lead import operations process data in structured phases. For instance, customer lead imports execute a multi-step sequence: `Papa.parse()` reads CSV data streams $\rightarrow$ records are filtered against existing Prisma customer and link records $\rightarrow$ click events are mapped with generated `nanoid(16)` identifiers $\rightarrow$ clicks are grouped by year to satisfy ClickHouse partition limits $\rightarrow$ newline-delimited JSON batches are posted to Tinybird $\rightarrow$ customer records are bulk-created via `prisma.customer.createMany` with duplicate skipping $\rightarrow$ lead events are recorded via `recordLeadWithTimestamp()` $\rightarrow$ link statistics are updated and synced via `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 maximum of 12 partitions (months) for a given event backfill operation. Backfill scripts must reduce and group records by calendar year or month before submitting NDJSON payloads toTinybird.
> 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)

### Administrative Data Updates and Deletion Scripts

Because Tinybird datasources are immutable append-only logs, updating an event (such as migrating a customer lead or sale to a new link ID) requires a two-step mutation pattern: recording the corrected event with its original timestamp, followed by issuing a programmatic delete condition against both the base datasource and its materialized view.

Sources: [apps/web/scripts/tinybird/delete-lead-event.ts:5-17](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/delete-lead-event.ts#L5-L17), [apps/web/scripts/tinybird/update-lead-event.ts:33-57](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/update-lead-event.ts#L33-L57), [apps/web/scripts/tinybird/update-sale-event.ts:23-48](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/update-sale-event.ts#L23-L48), [apps/web/scripts/customers/beehiiv/update-sale-events.ts:32-52](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/update-sale-events.ts#L32-L52)

| Maintenance Script | Target Datasources | Deletion / Update Mechanism | Purpose |
| :--- | :--- | :--- | :--- |
| `delete-lead-event.ts` | `dub_lead_events`, `dub_lead_events_mv` | POST `delete_condition=customer_id = '...'` | Purge specific customer lead events across base and materialized views |
| `update-lead-event.ts` | `dub_lead_events`, `dub_lead_events_mv` | Fetch via `internal_get_lead_events` pipe $\rightarrow$ record via `recordLeadWithTimestamp` $\rightarrow$ delete old `link_id` | Migrate customer lead association from an old link to a new link |
| `update-sale-event.ts` | `dub_sale_events`, `dub_sale_events_mv` | Fetch via `internal_get_sale_events` pipe $\rightarrow$ record via `recordSaleWithTimestamp` $\rightarrow$ delete old `link_id` | Migrate customer sale records to a corrected partner link |
| `update-sale-events.ts` (Beehiiv) | `dub_sale_events`, `dub_sale_events_mv` | Fetch via `internal_get_events` pipe $\rightarrow$ record via `recordSaleWithTimestamp` $\rightarrow$ delete by `link_id` and customer ID | Batch update Beehiiv customer sale event link attributions |

Sources: [apps/web/scripts/tinybird/delete-lead-event.ts:5-39](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/delete-lead-event.ts#L5-L39), [apps/web/scripts/tinybird/update-lead-event.ts:7-83](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/update-lead-event.ts#L7-L83), [apps/web/scripts/tinybird/update-sale-event.ts:4-73](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/update-sale-event.ts#L4-L73), [apps/web/scripts/customers/beehiiv/update-sale-events.ts:7-74](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/customers/beehiiv/update-sale-events.ts#L7-L74)

> [!CAUTION]
> Administrative deletion requests sent to Tinybird endpoints (`/v0/datasources/{dataSource}/delete`) require `application/x-www-form-urlencoded` payloads containing a `delete_condition` parameter. Both base tables and their corresponding `_mv` materialized views must be updated independently using `Promise.allSettled`.
> Sources: [apps/web/scripts/tinybird/delete-lead-event.ts:8-38](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/delete-lead-event.ts#L8-L38), [apps/web/scripts/tinybird/update-lead-event.ts:44-57](https://github.com/blade47/dub/blob/HEAD/apps/web/scripts/tinybird/update-lead-event.ts#L44-L57)

## Related

- [Conversion and Event Tracking](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/analytics-and-tracking/conversion-and-event-tracking)
- [Analytics Dashboard and Querying](https://www.doc0.dev/docs/934e554a-e6a1-476f-bb2f-23e62d86c3fd/technical/analytics-and-tracking/analytics-dashboard-and-querying)


## Sitemap

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