Function up

packages/db/code-migrations/16-restructure-profiles.ts:66–210

()

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

export async function up() {
  const isClustered = getIsCluster();
  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) {