OpenPanel is an open-source web and product analytics platform, an open-source alternative to Mixpanel with optional self-hosting.
import { sql } from '@openpanel/db/src/clickhouse/sql';
import { getRedisCache, type Redis } from '@openpanel/redis';
import { getSafeJson } from '@openpanel/shared';
import { replicatedTarget, TABLE_NAMES } from '../shared/ch-tables';
import { BaseBuffer, type BufferDeps } from './base-buffer';
export interface ProfileBackfillEntry {
projectId: string;
sessionId: string;
profileId: string;
}
// Max session IDs per IN clause before we split into another query
const CHUNK_SIZE = 500;
const DEFAULT_BATCH_SIZE = 1000;
export class ProfileBackfillBuffer extends BaseBuffer {
private readonly batchSize =
this.deps.config.buffers.profileBackfill.batchSize ?? DEFAULT_BATCH_SIZE;
private readonly redisKey = 'profile-backfill-buffer';
private redis: Redis;
constructor(deps: BufferDeps) {
super(deps, {
name: 'profile-backfill',
onFlush: async () => {
await this.processBuffer();
},
});
this.redis = getRedisCache();
}
async add(entry: ProfileBackfillEntry) {
return this.timeAdd(async () => {
try {
this.logger.info({ entry }, 'Adding profile backfill entry');
await this.redis.rpush(this.redisKey, JSON.stringify(entry));
} catch (error) {
this.logger.error(
{ err: error },
'Failed to add profile backfill entry'
);
}
});
}
protected getRedisListKey(): string {
return this.redisKey;
}
async processBuffer() {
const lrangeStart = performance.now();
const raw = await this.redis.lrange(this.redisKey, 0, this.batchSize - 1);
const lrangeMs = performance.now() - lrangeStart;
if (raw.length === 0) {
this.reportFlushStats({ rowsProcessed: 0, phases: { lrangeMs } });
return;
}
// Deduplicate by sessionId — last write wins (most recent profileId)
const seen = new Map<string, ProfileBackfillEntry>();
for (const r of raw) {
const parsed = getSafeJson<ProfileBackfillEntry>(r);
if (parsed) {
seen.set(parsed.sessionId, parsed);
}
}
const entries = Array.from(seen.values());
const ch = this.resolveCh();
// `replicatedTarget`, not `sql.id`: the clustered form carries an
// `ON CLUSTER` clause and is not a bare identifier.
const target = replicatedTarget(
this.deps.config.clickhouseClustered,
TABLE_NAMES.events
);
const chunks = this.chunks(entries, CHUNK_SIZE);
let processedChunks = 0;
const chStart = performance.now();
for (const chunk of chunks) {
const caseClause = sql.join(
chunk.map(
({ sessionId, profileId }) =>
sql`WHEN ${sql.string(sessionId)} THEN ${sql.string(profileId)}`
),
'\n'
);
const tupleList = sql.join(
chunk.map(
({ projectId, sessionId }) =>
sql`(${sql.string(projectId)}, ${sql.string(sessionId)})`
),
', '
);
const statement = sql`
UPDATE ${target}
SET profile_id = CASE session_id
${caseClause}
END
WHERE (project_id, session_id) IN (${tupleList})
AND created_at > now() - INTERVAL 6 HOURS`.toStatement();
await ch.command({
query: statement.query,
query_params: statement.query_params,
clickhouse_settings: {
mutations_sync: '0',
allow_experimental_lightweight_update: '1',
},
});
processedChunks++;
}
const chInsertMs = performance.now() - chStart;
let trimMs: number | undefined;
if (processedChunks === chunks.length) {
const trimStart = performance.now();
await this.redis.ltrim(this.redisKey, raw.length, -1);
trimMs = performance.now() - trimStart;
}
this.reportFlushStats({
rowsProcessed: raw.length,
phases: { lrangeMs, chInsertMs, trimMs },
});
}
}