OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
/**
* Integration tests for the chart module against the isolated `openpanel_test`
* databases (pinned by test/preload.ts) — the positive-row counterpart of
* src/sql.test.ts's EXPLAIN checks, and the one place the cohort and
* all-cohorts paths run against seeded `cohort_members` + a Postgres cohort.
*
* Fixture (test/fixtures.ts, seeded per suite under its own project id so it
* can run concurrently with other suites, with the clock pinned to
* test/fixture-clock.ts's 12:00 UTC anchor): Alice — 3 events 2 days ago
* (Chrome, page_view /home); Charlie — 5 events 5 days ago (Firefox,
* screen_view + page_view /shop, purchase 9900); Bob — no events. This suite
* adds one static cohort ("Firefox users") holding Charlie.
*/
import { afterAll, beforeAll, describe, expect, it, mock } from 'bun:test';
import {
pinFixtureClock,
releaseFixtureClock,
} from '../../../test/fixture-clock';
import type { IChartEventItem, IReportInput } from '../report/report.constants';
const TEST_PROJECT_ID = 'chart-integration-test';
const TEST_ORG_ID = 'chart-integration-org';
const COHORT_ID = 'dddddddd-0000-4000-8000-000000000001';
const COHORT_NAME = 'Firefox users';
const DAY_MS = 24 * 60 * 60 * 1000;
const PROJECT_CARD_DAYS = 90;
// Bypass Redis caching so a stale entry from another run cannot gate an
// assertion. Captured before mock.module runs.
const actualRedis = await import('@openpanel/redis');
mock.module('@openpanel/redis', () => ({
...actualRedis,
getCache: async <T>(_key: string, _ttl: number, fn: () => Promise<T>) => fn(),
}));
let FIXTURE: typeof import('../../../../../test/fixtures').FIXTURE;
let fixtureNow: Date;
let service: ReturnType<typeof import('./chart.service').createChartService>;
let ch: typeof import('@openpanel/db/src/clickhouse/client').ch;
type EventSeriesItem = Extract<IChartEventItem, { type: 'event' }>;
const pageView = (
overrides: Partial<EventSeriesItem> = {}
): EventSeriesItem => ({
type: 'event',
id: 'A',
name: 'page_view',
segment: 'event',
filters: [],
...overrides,
});
const report = (overrides: Partial<IReportInput> = {}): IReportInput => ({
projectId: TEST_PROJECT_ID,
chartType: 'linear',
interval: 'day',
series: [pageView()],
breakdowns: [],
range: '30d',
previous: false,
metric: 'sum',
...overrides,
});
beforeAll(async () => {
const fixtures = await import('../../../../../test/fixtures');
FIXTURE = fixtures.FIXTURE;
({ ch } = await import('@openpanel/db/src/clickhouse/client'));
const { testServiceDeps, testServices } = await import(
'../../../test/service-deps'
);
const { createChartService } = await import('./chart.service');
service = createChartService(await testServiceDeps(), testServices());
const { bootstrapTestDatabases } = await import(
'../../../../../test/bootstrap-databases'
);
await bootstrapTestDatabases();
await fixtures.setupPostgresFixtures(TEST_PROJECT_ID, TEST_ORG_ID);
fixtureNow = pinFixtureClock();
await fixtures.setupFixtures(TEST_PROJECT_ID);
const { db } = await import('@openpanel/db/src/prisma-client');
await db.cohort.upsert({
where: { id: COHORT_ID },
create: {
id: COHORT_ID,
name: COHORT_NAME,
projectId: TEST_PROJECT_ID,
isStatic: true,
},
update: { name: COHORT_NAME, projectId: TEST_PROJECT_ID },
});
await ch.command({
query: `DELETE FROM cohort_members WHERE project_id = '${TEST_PROJECT_ID}'`,
});
await ch.insert({
table: 'cohort_members',
values: [
{
project_id: TEST_PROJECT_ID,
cohort_id: COHORT_ID,
profile_id: FIXTURE.profiles.charlie,
matching_properties: {},
version: 1,
},
],
format: 'JSONEachRow',
});
}, 30_000);
afterAll(async () => {
const fixtures = await import('../../../../../test/fixtures');
const { db } = await import('@openpanel/db/src/prisma-client');
await ch.command({
query: `DELETE FROM cohort_members WHERE project_id = '${TEST_PROJECT_ID}'`,
});
await db.cohort.deleteMany({ where: { id: COHORT_ID } });
await fixtures.teardownFixtures(TEST_PROJECT_ID);
await fixtures.teardownPostgresFixtures(TEST_PROJECT_ID, TEST_ORG_ID);
releaseFixtureClock();
});
function seriesNamed<TSerie extends { names: string[] }>(
chart: { series: TSerie[] },
label: string
): TSerie | undefined {
return chart.series.find((serie) => serie.names.includes(label));
}
describe('executeChart', () => {
it('counts both page views over the last 30 days', async () => {
const chart = await service.execute(report());
expect(chart.series).toHaveLength(1);
expect(chart.series[0]?.names).toEqual(['page_view']);
expect(chart.metrics.sum).toBe(2);
expect(
chart.series[0]?.data.filter((point) => point.count > 0)
).toHaveLength(2);
});
it('breaks down by browser', async () => {
const chart = await service.execute(
report({ breakdowns: [{ id: 'b', name: 'browser' }] })
);
expect(seriesNamed(chart, 'Chrome')?.metrics.sum).toBe(1);
expect(seriesNamed(chart, 'Firefox')?.metrics.sum).toBe(1);
expect(seriesNamed(chart, 'Chrome')?.event.breakdowns).toEqual({
browser: 'Chrome',
});
});
it('filters on cohort membership (inCohort / notInCohort)', async () => {
const inCohort = await service.execute(
report({
series: [
pageView({
filters: [
{
id: 'c',
name: 'cohort',
operator: 'inCohort',
value: [],
cohortIds: [COHORT_ID],
},
],
}),
],
})
);
const notInCohort = await service.execute(
report({
series: [
pageView({
filters: [
{
id: 'c',
name: 'cohort',
operator: 'notInCohort',
value: [],
cohortIds: [COHORT_ID],
},
],
}),
],
})
);
expect(inCohort.metrics.sum).toBe(1);
expect(notInCohort.metrics.sum).toBe(1);
});
it('breaks down by a single cohort and by all cohorts', async () => {
const single = await service.execute(
report({ breakdowns: [{ id: 'b', name: `cohort:${COHORT_ID}` }] })
);
const all = await service.execute(
report({ breakdowns: [{ id: 'b', name: 'cohort' }] })
);
// Alice is outside the cohort, Charlie inside — both rows must survive.
expect(single.metrics.sum).toBe(2);
expect(single.series).toHaveLength(2);
// All-cohorts is an INNER JOIN on membership: non-members drop out.
expect(all.series.map((serie) => [serie.names, serie.metrics.sum])).toEqual(
[[['page_view', COHORT_NAME], 1]]
);
});
it('narrows profile properties into the profile CTE and still matches', async () => {
const chart = await service.execute(
report({
series: [
pageView({
filters: [
{
id: 'p',
name: 'profile.properties.browser',
operator: 'is',
value: ['Firefox'],
},
],
}),
],
breakdowns: [{ id: 'b', name: 'profile.properties.country' }],
})
);
expect(chart.metrics.sum).toBe(1);
expect(seriesNamed(chart, 'US')?.metrics.sum).toBe(1);
});
it('joins profiles created before the window that have events inside it', async () => {
const chart = await service.execute(
report({
range: '7d',
breakdowns: [{ id: 'b', name: 'profile.properties.browser' }],
})
);
expect(seriesNamed(chart, 'Chrome')?.metrics.sum).toBe(1);
expect(seriesNamed(chart, 'Firefox')?.metrics.sum).toBe(1);
});
});
describe('executeAggregateChart', () => {
it('sums revenue per browser without a time axis', async () => {
const chart = await service.executeAggregate(
report({
chartType: 'bar',
series: [
{
type: 'event',
id: 'A',
name: 'purchase',
segment: 'property_sum',
property: 'revenue',
filters: [],
},
],
breakdowns: [{ id: 'b', name: 'browser' }],
})
);
expect(chart.series).toHaveLength(1);
expect(seriesNamed(chart, 'Firefox')?.metrics.sum).toBe(9900);
expect(chart.series[0]?.data).toHaveLength(1);
});
});
describe('picker queries', () => {
it('lists event names with counts, `*` first', async () => {
const events = await service.listChartEvents(TEST_PROJECT_ID);
// Counts are `sum(event_count)` over distinct_event_names_mv, i.e. real event
// totals; `count(name)` would count insert parts.
expect(events[0]?.name).toBe('*');
expect(events[0]?.count).toBe(
events.slice(1).reduce((total, event) => total + event.count, 0)
);
expect(
events
.slice(1)
.map((event) => event.name)
.sort()
).toEqual([
'page_view',
'purchase',
'screen_view',
'session_end',
'session_start',
]);
});
// `count(name)` over distinct_event_names_mv counts one row per name PER INSERT
// PART, not per event, so only an assertion against the events table catches it.
it('counts real events, not materialized-view rows', async () => {
const events = await service.listChartEvents(TEST_PROJECT_ID);
const actual = await ch.query({
query:
'SELECT name, count() AS count FROM events WHERE project_id = {projectId:String} GROUP BY name',
query_params: { projectId: TEST_PROJECT_ID },
format: 'JSONEachRow',
});
const truth = Object.fromEntries(
(await actual.json<{ name: string; count: string }>()).map((row) => [
row.name,
Number(row.count),
])
);
const fromPicker = Object.fromEntries(
events
.filter((event) => event.name !== '*')
.map((event) => [event.name, event.count])
);
expect(Object.keys(truth).length).toBeGreaterThan(0);
expect(fromPicker).toEqual(truth);
});
it('lists filterable properties, `name` only for the wildcard event', async () => {
const all = await service.listChartProperties({
projectId: TEST_PROJECT_ID,
event: '*',
});
const single = await service.listChartProperties({
projectId: TEST_PROJECT_ID,
event: 'page_view',
});
expect(all).toContain('name');
expect(all).toContain('path');
expect(all).toContain('profile.email');
expect(new Set(all).size).toBe(all.length);
expect(single).not.toContain('name');
});
it('lists values for a top-level column, a profile property and has_profile', async () => {
const path = await service.getChartPropertyValues({
projectId: TEST_PROJECT_ID,
event: 'page_view',
property: 'path',
});
const profileBrowser = await service.getChartPropertyValues({
projectId: TEST_PROJECT_ID,
event: '*',
property: 'profile.properties.browser',
});
const hasProfile = await service.getChartPropertyValues({
projectId: TEST_PROJECT_ID,
event: '*',
property: 'has_profile',
});
const unknown = await service.getChartPropertyValues({
projectId: TEST_PROJECT_ID,
event: '*',
property: 'totally_made_up_column',
});
expect(path.values.sort()).toEqual(['/home', '/shop']);
expect(profileBrowser.values.sort()).toEqual(['Chrome', 'Firefox']);
expect(hasProfile.values).toEqual(['true', 'false']);
expect(unknown.values).toEqual([]);
});
});
describe('getProjectCard', () => {
it('returns a filled 3-month chart, metrics and a trend', async () => {
const card = await service.getProjectCard(TEST_PROJECT_ID);
expect(card.chart.length).toBeGreaterThanOrEqual(PROJECT_CARD_DAYS);
expect(card.chart[0]?.date).toBeInstanceOf(Date);
expect(card.metrics?.months_3).toBe(2);
expect(card.trend.direction).toBe('neutral');
});
});
describe('getChartBucketProfiles', () => {
it('returns the profiles behind one data point, honoring breakdowns', async () => {
const twoDaysAgo = new Date(
fixtureNow.getTime() - 2 * DAY_MS
).toISOString();
const profiles = await service.bucketProfiles({
projectId: TEST_PROJECT_ID,
date: twoDaysAgo,
interval: 'day',
series: [pageView()],
breakdowns: { browser: 'Chrome' },
});
const none = await service.bucketProfiles({
projectId: TEST_PROJECT_ID,
date: twoDaysAgo,
interval: 'day',
series: [pageView()],
breakdowns: { browser: 'Firefox' },
});
expect(profiles.map((profile) => profile.id)).toEqual([
FIXTURE.profiles.alice,
]);
expect(none).toEqual([]);
});
it('selects only whitelisted profile columns for profile.* references', async () => {
const twoDaysAgo = new Date(
fixtureNow.getTime() - 2 * DAY_MS
).toISOString();
const profiles = await service.bucketProfiles({
projectId: TEST_PROJECT_ID,
date: twoDaysAgo,
interval: 'day',
series: [
pageView({
filters: [
{
id: 'p',
name: 'profile.email',
operator: 'is',
value: ['[email protected]'],
},
],
}),
],
});
expect(profiles.map((profile) => profile.id)).toEqual([
FIXTURE.profiles.alice,
]);
await expect(
service.bucketProfiles({
projectId: TEST_PROJECT_ID,
date: twoDaysAgo,
interval: 'day',
series: [pageView()],
breakdowns: { 'profile.evil; DROP TABLE profiles': 'x' },
})
).rejects.toThrow(/Refusing to inline identifier/);
});
});
describe('getRetentionChart from a shared report', () => {
const nameFilter = (operator: 'is' | 'isNot', value: string) => ({
id: 'n',
name: 'name',
operator,
value: [value],
});
const pathFilter = {
id: 'p',
name: 'path',
operator: 'is' as const,
value: ['/signup'],
};
const savedReport = (
chartType: IReportInput['chartType'],
operator: 'is' | 'isNot',
leadingFilters: (typeof pathFilter)[] = []
) =>
({
...report({
chartType,
series: [
pageView({
id: 'A',
filters: [...leadingFilters, nameFilter(operator, 'signup')],
}),
pageView({ id: 'B', filters: [nameFilter('is', 'purchase')] }),
],
}),
id: 'report_1',
}) as unknown as Parameters<typeof service.getRetentionChart>[0];
const input = {
projectId: TEST_PROJECT_ID,
firstEvent: ['page_view'],
secondEvent: ['page_view'],
criteria: 'on_or_after' as const,
interval: 'day' as const,
range: '30d' as const,
};
it('refuses a report that is not a retention report', async () => {
await expect(
service.getRetentionChart(savedReport('linear', 'is'), input)
).rejects.toThrow('Report is not a retention report');
});
it('refuses an excluded event as the cohort selector', async () => {
await expect(
service.getRetentionChart(savedReport('retention', 'isNot'), input)
).rejects.toThrow('Report must have at least 2 event series');
});
it('refuses a series whose selected events are all excluded', async () => {
const excludedSelection = {
...savedReport('retention', 'is'),
series: [
pageView({
id: 'A',
filters: [nameFilter('isNot', 'signup'), nameFilter('is', 'signup')],
}),
pageView({ id: 'B', filters: [nameFilter('is', 'purchase')] }),
],
} as unknown as Parameters<typeof service.getRetentionChart>[0];
await expect(
service.getRetentionChart(excludedSelection, input)
).rejects.toThrow('Report must have at least 2 event series');
});
it('finds the event selector behind a property filter', async () => {
await expect(
service.getRetentionChart(
savedReport('retention', 'is', [pathFilter]),
input
)
).resolves.toBeDefined();
});
});