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
/**
 * Reports (and optionally deletes) duplicate rows in the ClickHouse `events`
 * table. A "duplicate" is two or more rows that are logically the same event
 * but have different `id`s — the signature of the event being processed more
 * than once.
 *
 * Duplicates are matched by a content hash over the meaningful columns
 * (everything that identifies the event, excluding `id`), so genuinely
 * distinct events are never grouped together — only byte-identical rows.
 *
 * Usage (always dry-run unless --danger-yes-delete is passed):
 *
 *   pnpm --filter @openpanel/db duplicate-events                 # last 24h, per day
 *   pnpm --filter @openpanel/db duplicate-events --since=7d --bucket=day
 *   pnpm --filter @openpanel/db duplicate-events --since=2026-06-01 --bucket=hour
 *   pnpm --filter @openpanel/db duplicate-events --project=my-project
 *   pnpm --filter @openpanel/db duplicate-events --since=48h --danger-yes-delete
 *
 * Flags:
 *   --since=<24h|7d|ISO>   Lookback window (default: 24h)
 *   --bucket=<day|hour>    Reporting granularity (default: day)
 *   --project=<projectId>  Restrict to a single project (default: all)
 *   --limit=<n>            Max example duplicate groups to print (default: 20)
 *   --batch=<n>            Rows per DELETE statement when deleting (default: 2000)
 *   --danger-yes-delete    Actually delete the extra rows. Keeps the lowest `id`
 *                          per duplicate group, deletes the rest. Without this
 *                          flag the script only reports.
 */
import {
  ch,
  getReplicatedTableName,
  TABLE_NAMES,
} from '../src/clickhouse/client';

type Bucket = 'day' | 'hour';

interface Args {
  since: Date;
  bucket: Bucket;
  project: string | null;
  limit: number;
  batch: number;
  delete: boolean;
}

function getArg(name: string): string | undefined {
  const prefix = `--${name}=`;
  const match = process.argv.find((a) => a.startsWith(prefix));
  return match ? match.slice(prefix.length) : undefined;
}

function hasFlag(name: string): boolean {
  return process.argv.includes(`--${name}`);
}

function parseSince(raw: string): Date {
  const relative = raw.match(/^(\d+)([hd])$/);
  if (relative) {
    const amount = Number.parseInt(relative[1]!, 10);
    const unitMs = relative[2] === 'h' ? 3_600_000 : 86_400_000;
    return new Date(Date.now() - amount * unitMs);
  }
  const parsed = new Date(raw);
  if (Number.isNaN(parsed.getTime())) {
    throw new Error(
      `Invalid --since="${raw}". Use e.g. 24h, 7d, or an ISO date like 2026-06-01.`
    );
  }
  return parsed;
}

function parseArgs(): Args {
  const bucket = (getArg('bucket') ?? 'day') as Bucket;
  if (bucket !== 'day' && bucket !== 'hour') {
    throw new Error(`Invalid --bucket="${bucket}". Use day or hour.`);
  }
  return {
    since: parseSince(getArg('since') ?? '24h'),
    bucket,
    project: getArg('project') ?? null,
    limit: Number.parseInt(getArg('limit') ?? '20', 10),
    // Each row inlines (project_id, id, content hash), about 110 bytes with
    // uuid ids, so 2000 rows ≈ 220KB stays under ClickHouse's default
    // max_query_size (256KB).
    batch: Number.parseInt(getArg('batch') ?? '2000', 10),
    delete: hasFlag('danger-yes-delete'),
  };
}

// Format a JS Date as a ClickHouse DateTime64(3) literal (UTC).
function chDateTime(date: Date): string {
  return date.toISOString().replace('T', ' ').replace('Z', '').slice(0, 23);
}

// Content hash identifying a logical event (everything but `id`). Properties
// are normalized to a canonical, order-independent "key=value" list so map
// ordering can never split an otherwise-identical pair. char(0)/char(1) are
// used as separators because they cannot appear in the textual values.
const DEDUP_KEY = `cityHash64(
  project_id, name, profile_id, device_id, session_id,
  toString(created_at),
  path, origin, referrer, referrer_name, referrer_type,
  country, city, region, os, os_version, browser, browser_version,
  device, brand, model, sdk_name, sdk_version,
  toString(revenue), toString(duration),
  arrayStringConcat(
    arraySort(
      arrayMap(
        (k, v) -> concat(k, char(0), v),
        mapKeys(properties), mapValues(properties)
      )
    ),
    char(1)
  )
)`;

function sqlString(value: string): string {
  return `'${value.replace(/'/g, "''")}'`;
}

function projectClause(project: string | null): string {
  return project ? ` AND project_id = ${sqlString(project)}` : '';
}

async function query<T>(sql: string): Promise<T[]> {
  return ch
    .query({ query: sql, format: 'JSONEachRow' })
    .then((res) => res.json<T>());
}

async function report(args: Args): Promise<void> {
  const bucketFn = args.bucket === 'day' ? 'toStartOfDay' : 'toStartOfHour';
  const sinceLiteral = chDateTime(args.since);
  const where = `created_at >= '${sinceLiteral}'${projectClause(args.project)}`;

  console.log(
    `\nScanning ${TABLE_NAMES.events} since ${sinceLiteral} UTC` +
      (args.project ? ` (project=${args.project})` : '') +
      `, bucketed by ${args.bucket}\n`
  );

  const rows = await query<{
    bucket: string;
    total_rows: string;
    unique_rows: string;
    duplicate_rows: string;
  }>(`
    SELECT
      ${bucketFn}(created_at) AS bucket,
      count() AS total_rows,
      uniqExact(${DEDUP_KEY}) AS unique_rows,
      count() - uniqExact(${DEDUP_KEY}) AS duplicate_rows
    FROM ${TABLE_NAMES.events}
    WHERE ${where}
    GROUP BY bucket
    ORDER BY bucket
  `);

  if (rows.length === 0) {
    console.log('No events found in this window.');
    return;
  }

  let totalRows = 0;
  let totalDupes = 0;
  console.table(
    rows.map((r) => {
      const total = Number(r.total_rows);
      const dupes = Number(r.duplicate_rows);
      totalRows += total;
      totalDupes += dupes;
      return {
        bucket: r.bucket,
        total: total.toLocaleString(),
        duplicates: dupes.toLocaleString(),
        pct: total > 0 ? `${((dupes / total) * 100).toFixed(2)}%` : '0%',
      };
    })
  );
  console.log(
    `\nTotal: ${totalRows.toLocaleString()} rows, ` +
      `${totalDupes.toLocaleString()} duplicate rows ` +
      `(${totalRows > 0 ? ((totalDupes / totalRows) * 100).toFixed(2) : '0'}%)\n`
  );

  // Top example groups, so it's clear *what* is duplicating.
  // Note: SELECT aliases must NOT shadow real column names — ClickHouse
  // resolves identifiers in WHERE/GROUP BY against SELECT aliases first, so an
  // alias named `project_id`/`created_at`/etc. (all referenced inside DEDUP_KEY
  // in GROUP BY, and in WHERE) turns into an aggregate-in-GROUP-BY/WHERE error.
  // Hence the de-shadowed alias names below.
  const examples = await query<{
    proj: string;
    event_name: string;
    profile: string;
    first_seen: string;
    occurrences: string;
    ids: string[];
  }>(`
    SELECT
      any(project_id) AS proj,
      any(name) AS event_name,
      any(profile_id) AS profile,
      toString(min(created_at)) AS first_seen,
      count() AS occurrences,
      arraySlice(groupArray(id), 1, 5) AS ids
    FROM ${TABLE_NAMES.events}
    WHERE ${where}
    GROUP BY ${DEDUP_KEY}
    HAVING occurrences > 1
    ORDER BY occurrences DESC
    LIMIT ${args.limit}
  `);

  if (examples.length > 0) {
    console.log(`Top ${examples.length} duplicate groups:`);
    console.table(
      examples.map((e) => ({
        project: e.proj,
        name: e.event_name,
        profile: e.profile,
        created_at: e.first_seen,
        copies: e.occurrences,
        sample_ids: e.ids.join(', '),
      }))
    );
  }
}

function* bucketRanges(
  since: Date,
  bucket: Bucket
): Generator<{ start: Date; end: Date }> {
  const stepMs = bucket === 'day' ? 86_400_000 : 3_600_000;
  const start = new Date(since);
  if (bucket === 'day') {
    start.setUTCHours(0, 0, 0, 0);
  } else {
    start.setUTCMinutes(0, 0, 0);
  }
  const now = Date.now();
  for (let t = start.getTime(); t < now; t += stepMs) {
    yield { start: new Date(t), end: new Date(t + stepMs) };
  }
}

async function deleteDuplicates(args: Args): Promise<void> {
  const target = getReplicatedTableName(TABLE_NAMES.events);
  console.log(
    '\n⚠️  DELETE MODE — keeping the lowest id per group, deleting the rest.\n' +
      `   Target: ${target}\n` +
      '   Note: ClickHouse mutations run asynchronously; rows disappear once the\n' +
      '   mutation completes server-side.\n'
  );

  let grandTotal = 0;
  for (const range of bucketRanges(args.since, args.bucket)) {
    const startLiteral = chDateTime(range.start);
    const endLiteral = chDateTime(range.end);
    const where =
      `created_at >= '${startLiteral}' AND created_at < '${endLiteral}'` +
      projectClause(args.project);

    // Extra rows = every row in a duplicate group except the lowest id. The
    // delete matches on (project_id, id, content), so a copy that shares the
    // survivor's id cannot be removed without removing the survivor too; it is
    // skipped. A distinct event that reuses the id has other content and stays.
    const extras = await query<{
      project_id: string;
      id: string;
      dedup_hash: string;
    }>(`
      SELECT project_id, id, dedup_hash FROM (
        SELECT project_id, id,
          ${DEDUP_KEY} AS dedup_hash,
          row_number() OVER w AS rn,
          min(id) OVER w AS survivor_id
        FROM ${TABLE_NAMES.events}
        WHERE ${where}
        WINDOW w AS (PARTITION BY ${DEDUP_KEY} ORDER BY id ASC)
      ) WHERE rn > 1 AND id != survivor_id
    `);

    if (extras.length === 0) {
      continue;
    }

    console.log(
      `${startLiteral} → ${extras.length.toLocaleString()} duplicate rows to delete`
    );
    grandTotal += extras.length;

    for (let i = 0; i < extras.length; i += args.batch) {
      const slice = extras.slice(i, i + args.batch);
      const rowList = slice
        .map(
          (row) =>
            `(${sqlString(row.project_id)},${sqlString(row.id)},toUInt64(${sqlString(row.dedup_hash)}))`
        )
        .join(',');
      // Scope the mutation to this bucket's time range so it only touches the
      // relevant monthly partition (and the primary index, when --project is
      // set) instead of scanning all history — `id` alone is not in the sort
      // key, so an unbounded `id IN (...)` mutates every partition.
      await ch.command({
        query:
          `ALTER TABLE ${target} DELETE WHERE ` +
          `created_at >= '${startLiteral}' AND created_at < '${endLiteral}'` +
          `${projectClause(args.project)} AND (project_id, id, ${DEDUP_KEY}) IN (${rowList})`,
      });
      await new Promise((resolve) => setTimeout(resolve, 200));
    }
  }

  console.log(
    `\nSubmitted deletes for ${grandTotal.toLocaleString()} duplicate rows.\n` +
      'Watch system.mutations for completion:\n' +
      '  SELECT * FROM system.mutations WHERE is_done = 0\n'
  );
}

async function main(): Promise<void> {
  const args = parseArgs();
  await report(args);

  if (args.delete) {
    await deleteDuplicates(args);
  } else {
    console.log(
      'Dry run only. Re-run with --danger-yes-delete to remove the duplicates.\n'
    );
  }
}

main()
  .then(() => process.exit(0))
  .catch((error) => {
    console.error('Script failed:', error);
    process.exit(1);
  });