Skip to content

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

5.4 KB
Raw Blame
// One-off backfill that reconciles `profiles.created_at` (first seen) and
// `profiles.last_seen_at` (last seen) against actual event activity.
//
// After migration `16-restructure-profiles.ts` every profile has
// `last_seen_at = created_at`; historically active profiles with no new
// sessions keep that value until seen again. Run once per environment to make
// both columns match the events table.
//
// For each project, INSERT one new row per profile with:
//     created_at   = least(existing, min(events.created_at))
//     last_seen_at = greatest(existing, max(events.created_at))
//   ReplacingMergeTree(last_seen_at) dedups on the next merge, keeping the row
//   with the higher `last_seen_at` — i.e. our updated row wins whenever it has
//   newer activity, otherwise the existing row wins (greatest = old value).
//
// Per project, so no temp table is needed: events sorts by `project_id`
// first (the WHERE prunes the scan), memory is bounded by one project's
// profiles, and progress is observable.
//
// Usage:
//   cd packages/db
//   pnpm with-env tsx scripts/backfill-profile-seen.ts            # all projects
//   pnpm with-env tsx scripts/backfill-profile-seen.ts --dry-run  # print SQL, no writes
//   pnpm with-env tsx scripts/backfill-profile-seen.ts --project=abc123  # one project
//   pnpm with-env tsx scripts/backfill-profile-seen.ts --concurrency=4   # N projects in parallel

import { parseArgs } from 'node:util';
import { ch, chQuery, TABLE_NAMES } from '../src/clickhouse/client';

const { values } = parseArgs({
  args: process.argv.slice(2),
  options: {
    'dry-run': { type: 'boolean', default: false },
    project: { type: 'string' },
    concurrency: { type: 'string', default: '1' },
  },
  strict: false,
});

const dryRun = Boolean(values['dry-run']);
const onlyProject =
  typeof values.project === 'string' ? values.project : undefined;
const concurrency = Math.max(
  1,
  Number.parseInt(
    typeof values.concurrency === 'string' ? values.concurrency : '1',
    10
  ) || 1
);

function buildSql(): string {
  // `join_use_nulls = 1` makes unmatched LEFT JOIN columns NULL (instead of
  // 1970-01-01 defaults), so `ifNull` cleanly falls back to the profile's
  // existing values for profiles that have no events.
  //
  // `device_id != profile_id` excludes anonymous device-only events from the
  // lookup (matches the convention used in our other profile/event queries).
  return `
    INSERT INTO ${TABLE_NAMES.profiles}
      (id, is_external, first_name, last_name, email, avatar, properties,
       project_id, groups, created_at, last_seen_at)
    SELECT
      p.id,
      p.is_external,
      p.first_name,
      p.last_name,
      p.email,
      p.avatar,
      p.properties,
      p.project_id,
      p.groups,
      least(p.created_at, ifNull(e.first_seen, p.created_at))   AS created_at,
      greatest(p.last_seen_at, ifNull(e.last_seen, p.last_seen_at)) AS last_seen_at
    FROM ${TABLE_NAMES.profiles} AS p FINAL
    LEFT JOIN (
      SELECT
        profile_id,
        min(created_at) AS first_seen,
        max(created_at) AS last_seen
      FROM ${TABLE_NAMES.events}
      WHERE project_id = {projectId:String}
        AND profile_id != ''
        AND device_id != profile_id
      GROUP BY profile_id
    ) AS e ON e.profile_id = p.id
    WHERE p.project_id = {projectId:String}
    SETTINGS
      join_use_nulls = 1,
      -- 'global' rewrites the subquery JOIN to GLOBAL JOIN in cluster mode,
      -- so the events aggregate is gathered across all shards on the
      -- initiator and broadcast. Without it, each shard would only see its
      -- local events slice and the last_seen lookup could undercount. No-op
      -- in non-clustered setups.
      distributed_product_mode = 'global',
      insert_distributed_sync = 1
  `;
}

async function backfillProject(
  projectId: string,
  index: number,
  total: number
) {
  const t0 = Date.now();
  const sql = buildSql();

  if (dryRun) {
    console.log(
      `[backfill] [${index}/${total}] DRY RUN project=${projectId}\n${sql}\n`
    );
    return;
  }

  await ch.command({
    query: sql,
    query_params: { projectId },
  });

  const ms = Date.now() - t0;
  console.log(
    `[backfill] [${index}/${total}] project=${projectId} done in ${ms}ms`
  );
}

async function main() {
  const start = Date.now();

  let projectIds: string[];
  if (onlyProject) {
    projectIds = [onlyProject];
  } else {
    const rows = await chQuery<{ project_id: string }>(
      `SELECT DISTINCT project_id FROM ${TABLE_NAMES.profiles}
       ORDER BY project_id`
    );
    projectIds = rows.map((r) => r.project_id);
  }

  console.log(
    `[backfill] ${projectIds.length} project(s) · concurrency=${concurrency}${dryRun ? ' · DRY RUN' : ''}`
  );

  let cursor = 0;
  await Promise.all(
    Array.from(
      { length: Math.min(concurrency, projectIds.length) },
      async () => {
        while (cursor < projectIds.length) {
          const i = cursor++;
          const id = projectIds[i]!;
          try {
            await backfillProject(id, i + 1, projectIds.length);
          } catch (err) {
            console.error(
              `[backfill] [${i + 1}/${projectIds.length}] project=${id} FAILED`,
              err
            );
          }
        }
      }
    )
  );

  console.log(
    `[backfill] Done in ${((Date.now() - start) / 1000).toFixed(1)}s`
  );
}

main().catch((err) => {
  console.error('[backfill] Fatal error:', err);
  process.exit(1);
});