Skip to content

OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.

11.0 KB
Raw Blame
/**
 * Shared ClickHouse fixture builder for integration tests.
 *
 * Call setupFixtures(projectId) / teardownFixtures(projectId) from any test
 * suite. Each suite uses its own project ID so suites can run concurrently
 * without stomping on each other's data.
 *
 * Fixture dataset (3 users, 8 events, 3 sessions):
 *
 *   Alice   — created 60 days ago, browser: Chrome, country: US
 *             3 events 2 days ago: session_start → page_view(/home) → session_end
 *             1 session  (sess-alice-1, 2d ago, Chrome)
 *
 *   Bob     — created 90 days ago, browser: Chrome, country: SE — NO events (inactive)
 *
 *   Charlie — created 30 days ago, browser: Firefox, country: US
 *             5 events 5 days ago: session_start → screen_view → page_view(/shop) → purchase → session_end
 *             2 sessions (sess-charlie-1 5d ago Firefox, sess-charlie-2 10d ago Firefox bounce)
 *
 * Event UUIDs live in the 00000000-0000-0000-0000-xxxxxxxxxxxx namespace.
 * Because events are scoped by project_id, the same UUIDs are safe across
 * different project IDs (ClickHouse's MergeTree ordering includes project_id).
 */

import { createClient } from '../packages/db/src/clickhouse/client';
import { PrismaClient } from '../packages/db/src/generated/prisma/client';
import { TEST_CLICKHOUSE_URL, TEST_DATABASE_URL } from './databases';

// Lazily create a Prisma client so DATABASE_URL is read at call time,
// not at module-import time (globalSetup runs before env is configured).
function getDb() {
  const url = process.env.DATABASE_URL ?? TEST_DATABASE_URL;
  return new PrismaClient({ datasources: { db: { url } } });
}

export const FIXTURE = {
  profiles: {
    alice: 'profile-alice',
    bob: 'profile-bob',
    charlie: 'profile-charlie',
  },
  sessions: {
    alice1: 'sess-alice-1',
    charlie1: 'sess-charlie-1',
    charlie2: 'sess-charlie-2',
  },
  events: {
    alice: {
      sessionStart: '00000000-0000-0000-0000-000000000001',
      pageView: '00000000-0000-0000-0000-000000000002',
      sessionEnd: '00000000-0000-0000-0000-000000000003',
    },
    charlie: {
      sessionStart: '00000000-0000-0000-0000-000000000004',
      screenView: '00000000-0000-0000-0000-000000000005',
      pageView: '00000000-0000-0000-0000-000000000006',
      purchase: '00000000-0000-0000-0000-000000000007',
      sessionEnd: '00000000-0000-0000-0000-000000000008',
    },
  },
} as const;

type ChClient = ReturnType<typeof createClient>;

function getClient() {
  const url = process.env.CLICKHOUSE_URL ?? TEST_CLICKHOUSE_URL;
  return createClient({ url });
}

export function timeAgo(now: Date, days: number, minutesOffset = 0) {
  return (
    new Date(now.getTime() - days * 86_400_000 - minutesOffset * 60_000)
      .toISOString()
      .replace('T', ' ')
      // biome-ignore lint/performance/useTopLevelRegex: test setup
      .replace(/\.\d+Z$/, '')
  );
}

export function buildEvent(
  now: Date,
  projectId: string,
  id: string,
  name: string,
  profileId: string,
  sessionId: string,
  daysBack: number,
  minutesOffset = 0,
  overrides: Record<string, unknown> = {}
) {
  return {
    id,
    project_id: projectId,
    profile_id: profileId,
    name,
    session_id: sessionId,
    device_id: `dev-${profileId.replace('profile-', '')}`,
    created_at: timeAgo(now, daysBack, minutesOffset),
    path: '/',
    origin: 'https://example.com',
    referrer: '',
    referrer_name: '',
    referrer_type: '',
    revenue: 0,
    duration: 0,
    properties: {},
    groups: [],
    country: 'US',
    city: '',
    region: '',
    sdk_name: 'web',
    sdk_version: '1.0.0',
    os: '',
    os_version: '',
    browser: 'Chrome',
    browser_version: '',
    device: 'desktop',
    brand: '',
    model: '',
    ...overrides,
  };
}

function buildSession(
  now: Date,
  projectId: string,
  id: string,
  profileId: string,
  daysBack: number,
  overrides: Record<string, unknown> = {}
) {
  return {
    id,
    project_id: projectId,
    profile_id: profileId,
    device_id: `dev-${profileId.replace('profile-', '')}`,
    created_at: timeAgo(now, daysBack),
    ended_at: timeAgo(now, daysBack),
    is_bounce: false,
    entry_origin: 'https://example.com',
    entry_path: '/home',
    exit_origin: 'https://example.com',
    exit_path: '/home',
    screen_view_count: 1,
    revenue: 0,
    event_count: 1,
    duration: 120,
    country: 'US',
    region: '',
    city: '',
    device: 'desktop',
    brand: '',
    model: '',
    browser: 'Chrome',
    browser_version: '',
    os: '',
    os_version: '',
    utm_medium: '',
    utm_source: '',
    utm_campaign: '',
    utm_content: '',
    utm_term: '',
    referrer: '',
    referrer_name: '',
    referrer_type: '',
    sign: 1,
    version: 1,
    properties: {},
    ...overrides,
  };
}

async function insertFixtures(client: ChClient, projectId: string) {
  const now = new Date();

  await client.insert({
    table: 'profiles',
    values: [
      {
        id: FIXTURE.profiles.alice,
        project_id: projectId,
        first_name: 'Alice',
        last_name: 'Smith',
        email: '[email protected]',
        avatar: '',
        is_external: false,
        // browser/country in properties so tests can filter profiles by these fields
        properties: { browser: 'Chrome', country: 'US', device: 'desktop' },
        groups: [],
        created_at: timeAgo(now, 60),
        last_seen_at: timeAgo(now, 1),
      },
      {
        id: FIXTURE.profiles.bob,
        project_id: projectId,
        first_name: 'Bob',
        last_name: "O'Brien",
        email: '[email protected]',
        avatar: '',
        is_external: false,
        // Bob is intentionally inactive (no events) — useful for inactiveDays tests
        properties: { browser: 'Chrome', country: 'SE', device: 'desktop' },
        groups: [],
        created_at: timeAgo(now, 90),
        last_seen_at: timeAgo(now, 90),
      },
      {
        id: FIXTURE.profiles.charlie,
        project_id: projectId,
        first_name: 'Charlie',
        last_name: 'Brown',
        email: '[email protected]',
        avatar: '',
        is_external: false,
        properties: { browser: 'Firefox', country: 'US', device: 'desktop' },
        groups: [],
        created_at: timeAgo(now, 30),
        last_seen_at: timeAgo(now, 5),
      },
    ],
    format: 'JSONEachRow',
  });

  // Events are spaced so windowFunnel strict_increase mode works correctly.
  await client.insert({
    table: 'events',
    values: [
      buildEvent(
        now,
        projectId,
        FIXTURE.events.alice.sessionStart,
        'session_start',
        FIXTURE.profiles.alice,
        FIXTURE.sessions.alice1,
        2,
        4
      ),
      buildEvent(
        now,
        projectId,
        FIXTURE.events.alice.pageView,
        'page_view',
        FIXTURE.profiles.alice,
        FIXTURE.sessions.alice1,
        2,
        2,
        { path: '/home', browser: 'Chrome' }
      ),
      buildEvent(
        now,
        projectId,
        FIXTURE.events.alice.sessionEnd,
        'session_end',
        FIXTURE.profiles.alice,
        FIXTURE.sessions.alice1,
        2,
        0,
        { duration: 120_000 }
      ),

      buildEvent(
        now,
        projectId,
        FIXTURE.events.charlie.sessionStart,
        'session_start',
        FIXTURE.profiles.charlie,
        FIXTURE.sessions.charlie1,
        5,
        20,
        { browser: 'Firefox' }
      ),
      buildEvent(
        now,
        projectId,
        FIXTURE.events.charlie.screenView,
        'screen_view',
        FIXTURE.profiles.charlie,
        FIXTURE.sessions.charlie1,
        5,
        15,
        { path: '/shop', browser: 'Firefox' }
      ),
      buildEvent(
        now,
        projectId,
        FIXTURE.events.charlie.pageView,
        'page_view',
        FIXTURE.profiles.charlie,
        FIXTURE.sessions.charlie1,
        5,
        10,
        { path: '/shop', browser: 'Firefox' }
      ),
      buildEvent(
        now,
        projectId,
        FIXTURE.events.charlie.purchase,
        'purchase',
        FIXTURE.profiles.charlie,
        FIXTURE.sessions.charlie1,
        5,
        5,
        { path: '/checkout', revenue: 9900, browser: 'Firefox' }
      ),
      buildEvent(
        now,
        projectId,
        FIXTURE.events.charlie.sessionEnd,
        'session_end',
        FIXTURE.profiles.charlie,
        FIXTURE.sessions.charlie1,
        5,
        0,
        { duration: 300_000, browser: 'Firefox' }
      ),
    ],
    format: 'JSONEachRow',
  });

  await client.insert({
    table: 'sessions',
    values: [
      buildSession(
        now,
        projectId,
        FIXTURE.sessions.alice1,
        FIXTURE.profiles.alice,
        2
      ),
      buildSession(
        now,
        projectId,
        FIXTURE.sessions.charlie1,
        FIXTURE.profiles.charlie,
        5,
        {
          browser: 'Firefox',
          entry_path: '/shop',
          exit_path: '/checkout',
          revenue: 9900,
          duration: 300,
          screen_view_count: 2,
          event_count: 5,
        }
      ),
      buildSession(
        now,
        projectId,
        FIXTURE.sessions.charlie2,
        FIXTURE.profiles.charlie,
        10,
        {
          browser: 'Firefox',
          is_bounce: true,
          entry_path: '/shop',
          exit_path: '/shop',
          duration: 15,
        }
      ),
    ],
    format: 'JSONEachRow',
  });
}

async function deleteFixtures(client: ChClient, projectId: string) {
  await Promise.all([
    client.command({
      query: `DELETE FROM profiles WHERE project_id = '${projectId}'`,
    }),
    client.command({
      query: `DELETE FROM events WHERE project_id = '${projectId}'`,
    }),
    client.command({
      query: `DELETE FROM sessions WHERE project_id = '${projectId}'`,
    }),
    client.command({
      query: `ALTER TABLE distinct_event_names_mv DELETE WHERE project_id = '${projectId}'`,
    }),
  ]);
}

export async function setupPostgresFixtures(
  projectId: string,
  orgId: string
): Promise<void> {
  const db = getDb();
  try {
    await db.organization.upsert({
      where: { id: orgId },
      create: { id: orgId, name: 'Test Org', timezone: 'UTC' },
      update: { timezone: 'UTC' },
    });
    await db.project.upsert({
      where: { id: projectId },
      create: { id: projectId, name: 'Test Project', organizationId: orgId },
      update: {},
    });
  } finally {
    await db.$disconnect();
  }
}

export async function teardownPostgresFixtures(
  projectId: string,
  orgId: string
): Promise<void> {
  const db = getDb();
  try {
    await db.project.deleteMany({ where: { id: projectId } });
    await db.organization.deleteMany({ where: { id: orgId } });
  } finally {
    await db.$disconnect();
  }
}

export async function setupFixtures(projectId: string): Promise<void> {
  const client = getClient();
  await deleteFixtures(client, projectId);
  await insertFixtures(client, projectId);
  await client.close();
}

export async function teardownFixtures(projectId: string): Promise<void> {
  const client = getClient();
  await deleteFixtures(client, projectId);
  await client.close();
}