OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
import { TABLE_NAMES } from '../clickhouse/client';
import {
createMaterializedView,
createTable,
dropTable,
getExistingTables,
runClickhouseMigrationCommands,
} from '../clickhouse/migration';
import { type CodeMigrationEnv, getIsCluster, writeSqlDump } from './helpers';
export async function up(env: CodeMigrationEnv) {
const replicatedVersion = '1';
const existingTables = await getExistingTables();
const isClustered = getIsCluster(env);
const sqls: string[] = [];
// 1. cohort_members — which profile_ids belong to which cohorts.
// ReplacingMergeTree(version) = newer compute wins on merge.
if (
!(
existingTables.includes(`${TABLE_NAMES.cohort_members}_distributed`) ||
existingTables.includes(TABLE_NAMES.cohort_members)
)
) {
sqls.push(
...createTable({
name: TABLE_NAMES.cohort_members,
columns: [
'`project_id` String CODEC(ZSTD(3))',
'`cohort_id` String CODEC(ZSTD(3))',
'`profile_id` String CODEC(ZSTD(3))',
'`matched_at` DateTime DEFAULT now()',
'`matching_properties` Map(String, String) CODEC(ZSTD(3))',
'`version` UInt64 DEFAULT 1',
],
indices: [
'INDEX idx_profile profile_id TYPE bloom_filter GRANULARITY 1',
'INDEX idx_cohort cohort_id TYPE bloom_filter GRANULARITY 1',
],
engine: 'ReplacingMergeTree(version)',
orderBy: ['project_id', 'cohort_id', 'profile_id'],
partitionBy: 'toYYYYMM(matched_at)',
settings: {
index_granularity: 8192,
},
distributionHash: 'cityHash64(project_id, cohort_id)',
replicatedVersion,
isClustered,
})
);
}
// 2. cohort_metadata — cached member count + sample profiles per cohort.
if (
!(
existingTables.includes(`${TABLE_NAMES.cohort_metadata}_distributed`) ||
existingTables.includes(TABLE_NAMES.cohort_metadata)
)
) {
sqls.push(
...createTable({
name: TABLE_NAMES.cohort_metadata,
columns: [
'`project_id` String',
'`cohort_id` String',
'`member_count` UInt64',
'`last_computed_at` DateTime',
'`sample_profiles` Array(String)',
'`version` UInt64 DEFAULT 1',
],
engine: 'ReplacingMergeTree(version)',
orderBy: ['project_id', 'cohort_id'],
settings: {
index_granularity: 8192,
},
distributionHash: 'cityHash64(project_id, cohort_id)',
replicatedVersion,
isClustered,
})
);
}
// 3. profile_event_summary_mv — aggregates events per (profile, event, day).
// Used by cohort compute when frequency filters are present.
// populate: false — we have a cluster with lots of events; backfill ad-hoc.
if (
!(
existingTables.includes(
`${TABLE_NAMES.profile_event_summary_mv}_distributed`
) || existingTables.includes(TABLE_NAMES.profile_event_summary_mv)
)
) {
sqls.push(
...createMaterializedView({
name: TABLE_NAMES.profile_event_summary_mv,
tableName: 'events',
engine: 'AggregatingMergeTree()',
orderBy: ['project_id', 'profile_id', 'name', 'event_date'],
partitionBy: 'toYYYYMM(event_date)',
query: `SELECT
project_id,
profile_id,
name,
toStartOfDay(created_at) AS event_date,
countState() AS event_count,
minState(created_at) AS first_event_time,
maxState(created_at) AS last_event_time,
sumState(duration) AS total_duration
FROM {events}
WHERE profile_id != device_id
GROUP BY project_id, profile_id, name, event_date`,
distributionHash: 'cityHash64(project_id, profile_id)',
replicatedVersion,
isClustered,
populate: false,
})
);
}
writeSqlDump(import.meta.url, sqls);
if (!process.argv.includes('--dry')) {
await runClickhouseMigrationCommands(sqls);
}
}
export async function down(env: CodeMigrationEnv) {
const isClustered = getIsCluster(env);
const sqls = [
dropTable(
`${TABLE_NAMES.profile_event_summary_mv}_distributed`,
isClustered
),
dropTable(
`${TABLE_NAMES.profile_event_summary_mv}_replicated`,
isClustered
),
dropTable(TABLE_NAMES.profile_event_summary_mv, isClustered),
dropTable(`${TABLE_NAMES.cohort_metadata}_distributed`, isClustered),
dropTable(`${TABLE_NAMES.cohort_metadata}_replicated`, isClustered),
dropTable(TABLE_NAMES.cohort_metadata, isClustered),
dropTable(`${TABLE_NAMES.cohort_members}_distributed`, isClustered),
dropTable(`${TABLE_NAMES.cohort_members}_replicated`, isClustered),
dropTable(TABLE_NAMES.cohort_members, isClustered),
];
await runClickhouseMigrationCommands(sqls);
}