Building a Sales Dashboard That Aggregates 6 Affiliate Networks

Why Affiliate Marketers Need a Unified Dashboard Before You Build Sales Analytics Dashboard Next.js Pipelines Across Six Rails

Logging into ClickBank while Digistore24 still emails CSVs—and MaxWeb labels totals differently than BuyGoods—is how affiliate fatigue compounds. Hassan Raza on hassanr.com ships systems where six vendors stop meaning six spreadsheets: one dashboard answers cash flow instead of bouncing between login screens. In production I sync 6 networks hourly on Vercel cron, store AES-256-GCM credentials, and serve KPIs from Postgres in under 60 seconds per run across 10 tools.

See also: NetworkAdapter pattern for affiliate APIs and UTM click tracking and analytics.

Distributed metrics waste expensive founder time

Customers expect “June revenue?” without juggling ClickBank dashboards that say total, Digistore payloads that shout amount, and MaxWeb objects that bury orderTotal. Hotmart nests buyer metadata differently than JVZoo, BuyGoods merges refunds inline, hourly sync rhythms miss each other unless you unify them deliberately, PostgreSQL-backed analytics dashboards become authoritative multi-network sales tracking layers once every payload maps into shared columns.

A single Postgres source of truth becomes the product wedge

An affiliate marketing SaaS I built centralizes KPIs inside Prisma aggregates so Stripe-minded founders consume SaaS dashboards without cloning portal CSV exports. Affiliate network integration at scale mandates adapter pattern TypeScript modules plus encrypted API credentials handling inside Next.js server runtimes—the browser never glimpses ciphertext or plaintext keys while Server Components hydrate cards fed by parameterized SQL. Frontend charts spanning a thirty-day earnings trend and a Recent Sales slice capped at twenty rows stay dumb on purpose.

I stopped treating ClickBank dashboards as the product once retention depended on aggregated intelligence: founders wanted drill-down comparisons between Hotmart and JVZoo without reverse-engineering each vendor glossary. Postgres plus Prisma 7 normalized fields give me deterministic aggregations—the same primitives power trend lines, donut charts, funnel math, AI recommendation prompts, alerting hooks without bolting brittle client-side merges.

The Adapter Pattern: One Interface, Six Implementations

The adapter pattern solves heterogeneous APIs by exporting one interface every network plugs into while the cron job only imports the registry—nothing vendor-specific leaks upstream.

What the adapter interface defines

I codified fetchSales for pagination plus auth quirks, normalizeSale so Prisma persists identical columns, and validateCredentials for Zod-paired forms—roughly fifteen credential fields sprinkled across vendors map into JSON before AES-256-GCM encryption traps them inside AffiliateNetworkAccount. Each vendor file owns header recipes, backoff behavior, discriminators for refunded rows, quirks like MM/DD/YYYY strings pretending to respect EST, even embedded commission math other networks tuck under ledger nodes.

Adding Network #7 skips touching incumbent adapters

Incremental sync timestamps mean each adapter learns since?: Date without rewriting incumbent classes—the hourly worker simply passes the freshest checkpoint it stored per account. Postgres upserts keyed by composite uniqueness keep retries idempotent whenever cron overlap replays the same sale batch. That alone saved me from conditional spaghetti when Digistore24 pagination changed mid-sprint: I edited one file, redeployed, watched ClickBank and Hotmart continue untouched.

import crypto from 'node:crypto';

export type CredentialMap = Record<string, string>;
export interface NormalizedSale {
  externalOrderId: string;
  amount: number;
  currency: string;
  commission: number;
  status: 'pending' | 'completed' | 'refunded';
  productName?: string;
  customerEmail?: string;
  saleDate: Date;
}

/**
 * Canonical contract implemented by ClickBankAdapter, Digistore24Adapter, etc.
 */
export interface NetworkAdapter {
  fetchSales(credentials: CredentialMap, since?: Date): Promise<unknown[]>;
  normalizeSale(raw: unknown): NormalizedSale;
  validateCredentials(credentials: CredentialMap): boolean;
}

type ClickBankCredentials = CredentialMap & {
  apiKey?: string;
  vendorId?: string;
};

/** Example implementation showing vendor-specific quirks without polluting cron code. */
export class ClickBankAdapter implements NetworkAdapter {
  async fetchSales(credentials: CredentialMap, since?: Date): Promise<unknown[]> {
    const typed = credentials as ClickBankCredentials;
    if (!this.validateCredentials(credentials)) {
      throw new Error('INVALID_CLICKBANK_CREDS');
    }

    const searchParams = new URLSearchParams();
    if (since) searchParams.set('startDate', since.toISOString().slice(0, 10)); // naive example

    const response = await fetch(`https://api.clickbank.com/rest/orders?${searchParams}`, {
      headers: {
        Authorization: `Bearer ${typed.apiKey}`,
        Accept: 'application/json',
      },
      cache: 'no-store',
      signal: AbortSignal.timeout(20_000),
    });

    if (response.status === 429) {
      throw new Error('RATE_LIMITED');
    }

    if (!response.ok) {
      const bodySnippet = await response.text();
      throw new Error(`CLICKBANK_HTTP_${response.status}:${bodySnippet.slice(0, 120)}`);
    }

    const payload = await response.json();
    const items = Array.isArray(payload.orders) ? payload.orders : []; // illustrative structure
    return items;
  }

  normalizeSale(raw: unknown): NormalizedSale {
    const row = raw as Record<string, unknown>;
    const amount = Number(row.total ?? row.saleAmount ?? 0); // CB doc drift handled here
    const commission = Number(row.commission ?? row.vendorCommission ?? row.approvedAmount ?? 0);
    const status =
      typeof row.status === 'string' && row.status.toLowerCase().includes('refund')
        ? 'refunded'
        : commission >= amount * 0.99
          ? 'completed'
          : 'pending';

    return {
      externalOrderId: String(row.transactionId ?? row.receipt ?? crypto.randomUUID()),
      amount,
      currency: String(row.currency ?? 'USD').toUpperCase(),
      commission,
      status,
      productName:
        typeof row.productTitle === 'string'
          ? row.productTitle
          : typeof row.title === 'string'
            ? row.title
            : undefined,
      customerEmail:
        typeof row.buyerEmail === 'string'
          ? row.buyerEmail
          : typeof row.email === 'string'
            ? row.email
            : undefined,
      saleDate:
        typeof row.createdAt === 'string'
          ? new Date(row.createdAt)
          : typeof row.utcDate === 'string'
            ? new Date(`${row.utcDate}Z`)
            : new Date(),
    };
  }

  validateCredentials(credentials: CredentialMap): boolean {
    const typed = credentials as ClickBankCredentials;
    return Boolean(typed.apiKey && typed.vendorId);
  }
}

Encrypting API Credentials with AES-256-GCM

API keys sitting in plaintext are breach invitations; hashing them destroys your ability to call affiliate APIs, reversible encryption keyed outside Postgres fixes both problems.

Hashes block outbound vendor calls—encryption does not

I need ciphertext unraveled hourly inside trusted Node runtimes powering sales aggregation dashboards backed by Postgres. AES-256-GCM pairs confidentiality with authenticated integrity: random twelve-byte initialization vectors mutate every write, Galois Counter Mode attaches auth tags proving tampering, Base64 segments keep payloads JSON-safe across Prisma TEXT columns, and bcrypt-style irreversible storage simply cannot hydrate outbound Bearer tokens Digistore24 demands—especially when the reversible key resolves from exactly 64 hex characters totaling 256 symmetric bits validated at build time.

The AES-256-GCM helpers I ship in Server Actions

Each ciphertext string concatenates IV, payload, and tag with delimiter colons—all Base64—for storage inside encryptedCredentials. Decryption rejects malformed triplets before touching vendor hosts—corrupted rows fail fast rather than spraying junk into AffiliateSale. The raw key material never escapes process memory; Prisma persists only ciphertext blobs while strict environment validation insists on sixty-four hex characters totaling two hundred fifty-six symmetric bits.

Before shipping, I stress-tested decryption race conditions concurrent with reconnect flows—server Actions awaited transforms before transactional writes prevented half-written blobs. Twelve-byte IV length matches NIST SP 800-38D defaults for AES-GCM, auth tags stay sixteen bytes, Node’s createCipheriv rejects wrong-length inputs immediately whenever Zod env parsing tightened. The ciphertext column alone stores payloads; decrypted secrets evaporate once requests finish so browsers never hydrate sensitive React props.

import crypto from 'node:crypto';

function encodeTriple(iv: Buffer, encrypted: Buffer, authTag: Buffer): string {
  return [iv.toString('base64'), encrypted.toString('base64'), authTag.toString('base64')].join(':');
}

function decodeTriple(payload: string): [Buffer, Buffer, Buffer] {
  const chunks = payload.split(':');
  if (chunks.length !== 3) {
    throw new Error('INVALID_CIPHER_SHAPE');
  }
  return chunks.map((chunk) => Buffer.from(chunk, 'base64')) as [Buffer, Buffer, Buffer];
}

export function encryptCredentials(plaintext: string, key: Buffer): string {
  if (key.length !== 32) {
    throw new Error('AES256 expects 256-bit keys');
  }
  const iv = crypto.randomBytes(12); // NIST-approved GCM nonce length
  const cipher = crypto.createCipheriv('aes-256-gcm', key, iv);
  const encrypted = Buffer.concat([cipher.update(plaintext, 'utf8'), cipher.final()]);
  const authTag = cipher.getAuthTag();
  return encodeTriple(iv, encrypted, authTag);
}

export function decryptCredentials(ciphertext: string, key: Buffer): string {
  if (key.length !== 32) {
    throw new Error('AES256 expects 256-bit keys');
  }
  try {
    const [iv, encrypted, authTag] = decodeTriple(ciphertext);
    const decipher = crypto.createDecipheriv('aes-256-gcm', key, iv);
    decipher.setAuthTag(authTag);
    return Buffer.concat([decipher.update(encrypted), decipher.final()]).toString('utf8');
  } catch {
    throw new Error('FAILED_TO_DECRYPT_OR_VERIFY');
  }
}

The Normalized Database Schema That Unifies Everything

A normalized schema absorbs every eccentric payload—commission buried under nested JVZoo objects or BuyGoods timestamps masquerading as strings—because adapters emit one Prisma-compatible shape downstream.

AffiliateSale mirrors adapter output verbatim

Identifiers pair userId with network enums and externalOrderId sourced from receipts. Monetary floats land in amount and commission slots, statuses stay vendor-agnostic enums-as-strings for rapid iteration, nullable customerEmail captures partial datasets, saleDate aligns on UTC primitives after parseNetworkDate().

Composite uniqueness enforces PostgreSQL-backed idempotent upserts

Duplicates die at (userId, network, externalOrderId) boundaries so hourly Cron replays safely when partial batches retry without inflating KPI cards. Postgres enforces deterministic uniqueness—even if JVZoo reuses receipt strings ambiguously alongside BuyGoods alphanumeric IDs mapped through adapters, collisions stay isolated per authenticated principal.

model AffiliateNetworkAccount {
  id                    String   @id @default(cuid())
  userId                String
  network               Network  // enum: CLICKBANK, DIGISTORE24, etc.
  encryptedCredentials  String   // AES-256-GCM encrypted JSON
  isActive              Boolean  @default(true)
  lastSyncAt            DateTime?
  createdAt             DateTime @default(now())
  updatedAt             DateTime @updatedAt

  user                  User     @relation(fields: [userId], references: [id])

  @@unique([userId, network])
}

model AffiliateSale {
  id              String   @id @default(cuid())
  userId          String
  network         Network
  externalOrderId String   // Network's order ID
  amount          Float
  currency        String   @default("USD")
  commission      Float
  status          String   // pending, completed, refunded
  productName     String?
  customerEmail   String?
  saleDate        DateTime
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  user            User     @relation(fields: [userId], references: [id])

  @@unique([userId, network, externalOrderId])
  @@index([userId, saleDate])
}

enum Network {
  CLICKBANK
  DIGISTORE24
  BUYGOODS
  MAXWEB
  JVZOO
  HOTMART
}

Background Sync with Vercel Cron: Hourly Incremental Updates

A background job wakes every sixty minutes through Vercel Cron, verifies secret headers, decrypts active accounts independently, invokes adapters while passing lastSyncAt, persists normalized AffiliateSale payloads, bumps telemetry timestamps, logs failures atomically.

The /api/cron/sync-sales choreography

Authentication rejects spoofed traffic immediately—Vercel injects immutable secrets I compare before touching Prisma loaders. Rows flagged isActive = true hydrate affiliate accounts, decrypted JSON survives Zod plus adapter validation, Node pulls adapters from registry files under src/features/sales-tracking/adapters. Each iteration wraps isolation boundaries so MaxWeb outages never stall Hotmart writes; summary payloads log counts aligning with Grafana hooks I mirrored locally during development.

Incremental lastSync timestamps prevent refetch carnage

Dragging ten thousand ClickBank receipts hourly annihilated quota budgets until optional since arguments fed vendor-specific filters capturing deltas after lastSyncAt. That lone timestamp mirrors incremental sync philosophies common in Postgres analytics stacks—difference being affiliate APIs rarely emit cursor tokens, forcing adapter authors to coerce dates carefully. Passing ISO boundaries for Hotmart contrasting Unix seconds for JVZoo keeps fetch windows honest without reloading entire histories each sixty-minute cron tick.

Isolation when one network melts down

Separate try/catch boundaries emit structured telemetry, backoff-aware sleeps respect JVZoo 429 responses, Postgres transactions clamp per AffiliateNetworkAccount identities, summary payloads echo synced versus failed totals. AffiliateSale UPSERT semantics stay idempotent so repeated failures never duplicate KPI rows; hourly cron cadence means flaky vendors retry automatically without operator babysitting. Logs remain server-only today—I still crave user-visible banners warning when decrypted credentials silently rot.

Testing without live sandbox keys forced doc-driven adapters plus deterministic fixtures pretending to be Digistore24 responses—first production sync rewrote edge cases MM/DD/YYYY parsing missed. Rate-limit handling evolved after silent failures traced to swallowed 429 envelopes; catching them now skips the offending cycle while leaving five unaffected networks untouched.

The Dashboard UI: Unified Analytics Across 6 Networks

The dashboard is Server Components querying AffiliateSale aggregates filtered by authenticated user scopes without embedding adapter logic—marketing sees revenue, Hassan Raza trusts TypeScript narrowing.

Cards, charts, and AI strategic insight blocks

My Sales KPI cards surface totals, averages, thirty-day deltas, conversion heuristics comparing seven-day momentum against thirty-day baselines—founders obsess over directional truth more than vanity decimals. Recent Sales clamps at 20 chronological rows annotated with network badges so teams spot BuyGoods anomalies instantly without losing Hotmart refunds in noise. Platform Earnings slices proportional share, yearly bar charts juxtapose normalized months beside Recharts thirty-day slopes, Strategic Insights wraps Gemini prompts around aggregated metrics plus guardrails forbidding hallucinated payouts.

Aggregations plus Gemini context windows

Prisma groupBy calls fan out network enums with _sum on commissions, findMany orders newest first with take: 20, date filters respect UTC-normalized saleDate boundaries, Server Components memoize JSON props feeding Recharts without leaking row-level PII. Gemini Server Actions receive anonymized aggregates—never decrypted strings—so multi-network sales intelligence ships without shipping credentials client-side. That grounding mirrors patterns I unpacked in my multi-step Server Actions wizard teardown and the guarded fetch cadence ethos from my rate-limited SaaS write-up on hassanr.com.

When the Adapter Pattern Is Overkill (And When It's Essential)

Supporting two brittle APIs tolerates inlined switches; sustaining six—with proven appetite for fifteen—means adapters amortize refactoring risk aggressively.

The adapter pattern is over-engineering for 6 networks. But this dashboard was always going to grow past 10. The upfront complexity isn't for today — it's insurance against the refactor I'd face at Network 15 if I'd taken the shortcut.

Next refactor on my backlog: factories replacing manual registry imports so each enum maps cleanly, daily mock-driven health pings surfacing breakage before founders notice, surfaced sync telemetry inside settings modals alerting when decrypted credentials stale—because silent failure erodes PostgreSQL dashboards faster than flashy charts compensate.

Frequently Asked Questions