Skip to content

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

7.8 KB
Raw Blame
import {
  chMigrationClient,
  createTable,
  renameTable,
  runClickhouseMigrationCommands,
} from '../clickhouse/migration';
import { type CodeMigrationEnv, getIsCluster, writeSqlDump } from './helpers';

// Restructures the `profiles` table so the column names actually mean what
// they say:
//
//   - `created_at`    → first time this profile was seen (preserved across
//                       upserts, like a real created_at). NOT the RMT version.
//   - `last_seen_at`  → most recent activity. NEW column. ReplacingMergeTree
//                       uses this as its version, so dedup naturally keeps the
//                       latest.
//
// Before this migration we abused `created_at` as both the version column and
// (de facto, due to the buffer's merge logic) the first-seen marker. That
// double duty was confusing and fragile.
//
// Strategy mirrors `8-order-keys.ts`:
//   1. Create `profiles_new_<DATE>` with the new schema.
//   2. Batch-copy month-by-month from the existing `profiles` (FINAL),
//      seeding `last_seen_at` from `created_at`. The buffer's session-boundary
//      upserts will start moving `last_seen_at` forward immediately after
//      cutover; profiles that go inactive simply keep `last_seen_at = created_at`,
//      which is honest — they haven't been seen since.
//   3. Rename current `profiles` → `profiles_<DATE>` and the new table into
//      place as `profiles`.
//
// If a deployment specifically wants accurate `last_seen_at` for historically
// active profiles right after the migration, run a one-off backfill against
// events:
//
//   INSERT INTO profiles SELECT
//     id, is_external, first_name, last_name, email, avatar, properties,
//     project_id, groups, created_at,
//     greatest(last_seen_at, e.last_seen_at) AS last_seen_at
//   FROM profiles AS p
//   ANY LEFT JOIN (
//     SELECT project_id, profile_id, max(created_at) AS last_seen_at
//     FROM events
//     WHERE profile_id != '' AND device_id != profile_id
//     GROUP BY project_id, profile_id
//   ) AS e
//     ON e.project_id = p.project_id AND e.profile_id = p.id
//   SETTINGS distributed_product_mode = 'allow';
//
// (RMT's version column resolves the dedup — the row with the higher
// `last_seen_at` wins.) Skip this on small/self-hosted deployments; it's not
// required for correctness.
//
// During the migration window, profile upserts hitting the renamed-away
// `profiles` table will be lost. Stop the worker (or accept brief data loss)
// before running in production.

const SUFFIX = '20260504';
const NEW_TABLE = `profiles_new_${SUFFIX}`;
const OLD_TABLE = `profiles_${SUFFIX}`;

const replicated = (table: string) => `${table}_replicated`;

export async function up(env: CodeMigrationEnv) {
  const isClustered = getIsCluster(env);
  const sqls: string[] = [];

  // 1. New profiles table. ReplacingMergeTree's version is now `last_seen_at`,
  //    so the latest activity timestamp wins on dedup — and `created_at` is
  //    preserved naturally (no longer the version, no special merge handling).
  const profileTables = createTable({
    name: NEW_TABLE,
    columns: [
      '`id` String CODEC(ZSTD(3))',
      '`is_external` Bool',
      '`first_name` String CODEC(ZSTD(3))',
      '`last_name` String CODEC(ZSTD(3))',
      '`email` String CODEC(ZSTD(3))',
      '`avatar` String CODEC(ZSTD(3))',
      '`properties` Map(String, String) CODEC(ZSTD(3))',
      '`project_id` String CODEC(ZSTD(3))',
      '`groups` Array(String) DEFAULT [] CODEC(ZSTD(3))',
      '`created_at` DateTime64(3) CODEC(Delta(4), LZ4)',
      '`last_seen_at` DateTime64(3) CODEC(Delta(4), LZ4)',
    ],
    indices: [
      'INDEX idx_first_name first_name TYPE bloom_filter GRANULARITY 1',
      'INDEX idx_last_name last_name TYPE bloom_filter GRANULARITY 1',
      'INDEX idx_email email TYPE bloom_filter GRANULARITY 1',
    ],
    engine: 'ReplacingMergeTree(last_seen_at)',
    orderBy: ['project_id', 'id'],
    partitionBy: 'toYYYYMM(created_at)',
    settings: { index_granularity: 8192 },
    distributionHash: 'cityHash64(project_id)',
    replicatedVersion: '2',
    isClustered,
  });
  sqls.push(...profileTables);

  // 2. Find the oldest profile so we know how far back to batch.
  const firstProfileResp = await chMigrationClient.query({
    query: 'SELECT min(created_at) AS created_at FROM profiles',
    format: 'JSONEachRow',
  });
  const firstProfileJson = await firstProfileResp.json<{
    created_at: string;
  }>();
  const firstDate = firstProfileJson[0]?.created_at;

  if (firstDate && !firstDate.startsWith('1970')) {
    const startDate = new Date(firstDate);
    const endDate = new Date();
    endDate.setMonth(endDate.getMonth() + 1);
    endDate.setDate(1);

    const monthBoundary = (d: Date) =>
      `${d.getFullYear()}-${String(d.getMonth() + 1).padStart(2, '0')}-01`;

    let cursor = new Date(endDate);
    while (true) {
      const lower = new Date(cursor);
      lower.setMonth(lower.getMonth() - 1);
      // Don't go below the first profile's month.
      if (
        lower.getFullYear() < startDate.getFullYear() ||
        (lower.getFullYear() === startDate.getFullYear() &&
          lower.getMonth() < startDate.getMonth())
      ) {
        lower.setFullYear(startDate.getFullYear());
        lower.setMonth(startDate.getMonth());
        lower.setDate(1);
      }

      sqls.push(
        // Aliases are intentionally distinct from column names. ClickHouse
        // resolves identifiers against SELECT-list aliases first, so naming an
        // alias `created_at` would shadow the source column inside `argMax(..., created_at)`
        // and trigger ILLEGAL_AGGREGATION ("aggregate inside another aggregate").
        // INSERT INTO ... (col list) SELECT maps positionally, so the alias
        // names don't need to match the target columns.
        `INSERT INTO ${NEW_TABLE}
           (id, is_external, first_name, last_name, email, avatar, properties,
            project_id, groups, created_at, last_seen_at)
         SELECT
           id,
           argMax(is_external, created_at) as v_is_external,
           argMax(nullIf(first_name, ''), created_at) as v_first_name,
           argMax(nullIf(last_name, ''), created_at) as v_last_name,
           argMax(nullIf(email, ''), created_at) as v_email,
           argMax(nullIf(avatar, ''), created_at) as v_avatar,
           argMax(properties, created_at) as v_properties,
           project_id,
           argMax(groups, created_at) as v_groups,
           min(created_at) as v_created_at,
           min(created_at) as v_last_seen_at
         FROM profiles FINAL
         WHERE created_at >= toDateTime('${monthBoundary(lower)}')
           AND created_at <  toDateTime('${monthBoundary(cursor)}')
         GROUP BY id, project_id
         SETTINGS insert_distributed_sync = 1`
      );

      if (
        lower.getFullYear() === startDate.getFullYear() &&
        lower.getMonth() === startDate.getMonth()
      ) {
        break;
      }
      cursor = lower;
    }
  }

  // 3. Atomic-ish swap. Same dance as 8-order-keys.ts: rename the existing
  //    table aside, then promote the new one in place. In cluster mode we
  //    rename the underlying replicated table and rebuild the Distributed
  //    wrapper.
  sqls.push(...renameTable({ from: 'profiles', to: OLD_TABLE, isClustered }));

  if (isClustered && profileTables[1]) {
    sqls.push(
      `DROP TABLE IF EXISTS ${NEW_TABLE} ON CLUSTER '{cluster}'`,
      `RENAME TABLE ${replicated(NEW_TABLE)} TO ${replicated('profiles')} ON CLUSTER '{cluster}'`,
      // Rebuild the distributed wrapper pointing at the new replicated table.
      profileTables[1].replaceAll(NEW_TABLE, 'profiles')
    );
  } else {
    sqls.push(...renameTable({ from: NEW_TABLE, to: 'profiles', isClustered }));
  }

  writeSqlDump(import.meta.url, sqls);

  if (!process.argv.includes('--dry')) {
    await runClickhouseMigrationCommands(sqls);
  }
}