Skip to content

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

10.6 KB
Raw Blame
// `get`/`toggle`/`updateOptions` require a login; `counter`/`badge`/
// `realtimeData` are deliberately open, because a widget is embedded on a
// public page.
//
// Every statement is built with the project's timezone, so `chQuery` is given
// the same value as `clickhouse_settings.session_timezone`.

import { sql } from '@openpanel/db/src/clickhouse/sql';
import { getCache } from '@openpanel/redis';
import ShortUniqueId from 'short-unique-id';
import { z } from 'zod';
import { chQuery } from '../../ch-query';
import type { Ctx } from '../../context';
import {
  createTRPCRouter,
  protectedProcedure,
  publicProcedure,
} from '../../rpc/base';
import { TRPCNotFoundError } from '../../rpc/errors';
import { TABLE_NAMES } from '../../shared/ch-tables';
import { getSettingsForProject } from '../organization/organization.service';
import {
  zCounterWidgetOptions,
  zRealtimeWidgetOptions,
  zWidgetOptions,
  zWidgetType,
} from '../report/report.constants';

const uid = new ShortUniqueId({ length: 6 });
const BADGE_CACHE_TTL_SECONDS = 5 * 60; // queries 30 days of data
const REALTIME_TOP_LIST_LIMIT = 10;

const EVENTS_TABLE = sql.id(TABLE_NAMES.events);
const REALTIME_WINDOW = sql`created_at >= (now() - INTERVAL 30 MINUTE)`;

// The type lives in the `options` JSON, so nothing stops two concurrent
// enables from each creating a row; reads take the oldest, writes hit all.
function widgetsOfType(projectId: string, type: z.infer<typeof zWidgetType>) {
  return { projectId, options: { path: ['type'], equals: type } };
}

function findWidgetByType(
  db: Ctx['db'],
  projectId: string,
  type: z.infer<typeof zWidgetType>
) {
  return db.shareWidget.findFirst({
    where: widgetsOfType(projectId, type),
    orderBy: { createdAt: 'asc' },
  });
}

// Shared by toggle/updateOptions: same update-or-create shape, differing only
// in which field is written and the create-time default. The update covers
// every row of the type, so disabling a widget revokes duplicate links too.
async function upsertWidgetByType(
  db: Ctx['db'],
  params: {
    projectId: string;
    organizationId: string;
    type: z.infer<typeof zWidgetType>;
    update: { public: boolean } | { options: z.infer<typeof zWidgetOptions> };
    create: { public: boolean; options: z.infer<typeof zWidgetOptions> };
  }
) {
  const { count } = await db.shareWidget.updateMany({
    where: widgetsOfType(params.projectId, params.type),
    data: params.update,
  });
  const existing =
    count > 0
      ? await findWidgetByType(db, params.projectId, params.type)
      : null;
  if (existing) {
    return existing;
  }

  return db.shareWidget.create({
    data: {
      id: uid.rnd(),
      projectId: params.projectId,
      organizationId: params.organizationId,
      public: params.create.public,
      options: params.create.options,
    },
  });
}

// Shared by counter/badge: both require the widget to exist, be public and
// be a counter widget before doing anything else.
async function findPublicCounterWidgetOrThrow(db: Ctx['db'], shareId: string) {
  const widget = await db.shareWidget.findUnique({
    where: { id: shareId },
  });

  if (!widget?.public) {
    throw new TRPCNotFoundError('Widget not found');
  }

  if (widget.options.type !== 'counter') {
    throw new TRPCNotFoundError('Invalid widget type');
  }

  return widget;
}

export const widgetRouter = createTRPCRouter({
  get: protectedProcedure
    .input(z.object({ projectId: z.string(), type: zWidgetType }))
    .query(async ({ input, ctx }) => {
      const widget = await findWidgetByType(
        ctx.db,
        input.projectId,
        input.type
      );

      if (!widget) {
        return null;
      }

      return widget;
    }),

  toggle: protectedProcedure
    .input(
      z.object({
        projectId: z.string(),
        organizationId: z.string(),
        type: zWidgetType,
        enabled: z.boolean(),
      })
    )
    .mutation(async ({ input, ctx }) => {
      await ctx.services.auth.requireProjectAccess({
        userId: ctx.session.userId,
        projectId: input.projectId,
        level: 'write',
      });

      const defaultOptions =
        input.type === 'realtime'
          ? zRealtimeWidgetOptions.parse({ type: 'realtime' })
          : zCounterWidgetOptions.parse({ type: 'counter' });

      return upsertWidgetByType(ctx.db, {
        projectId: input.projectId,
        organizationId: input.organizationId,
        type: input.type,
        update: { public: input.enabled },
        create: { public: input.enabled, options: defaultOptions },
      });
    }),

  updateOptions: protectedProcedure
    .input(
      z.object({
        projectId: z.string(),
        organizationId: z.string(),
        options: zWidgetOptions,
      })
    )
    .mutation(async ({ input, ctx }) => {
      await ctx.services.auth.requireProjectAccess({
        userId: ctx.session.userId,
        projectId: input.projectId,
        level: 'write',
      });

      return upsertWidgetByType(ctx.db, {
        projectId: input.projectId,
        organizationId: input.organizationId,
        type: input.options.type,
        update: { options: input.options },
        create: { public: false, options: input.options },
      });
    }),

  counter: publicProcedure
    .input(z.object({ shareId: z.string() }))
    .query(async ({ input, ctx }) => {
      const widget = await findPublicCounterWidgetOrThrow(
        ctx.db,
        input.shareId
      );
      const eventBuffer = ctx.buffers.event;

      return {
        projectId: widget.projectId,
        counter: await eventBuffer.getActiveVisitorCount(widget.projectId),
      };
    }),

  badge: publicProcedure
    .input(z.object({ shareId: z.string() }))
    .query(async ({ input, ctx }) => {
      const widget = await findPublicCounterWidgetOrThrow(
        ctx.db,
        input.shareId
      );
      const { projectId } = widget;
      const { timezone } = await getSettingsForProject(ctx, projectId);

      const cacheKey = `widget:badge:${projectId}`;
      const visitors = await getCache(
        cacheKey,
        BADGE_CACHE_TTL_SECONDS,
        async () => {
          const result = await chQuery<{ count: number }>(
            ctx,
            sql`SELECT uniq(profile_id) as count FROM ${EVENTS_TABLE} WHERE project_id = ${sql.string(projectId)} AND created_at >= (now() - INTERVAL 30 DAY)`,
            { session_timezone: timezone }
          );
          return result[0]?.count || 0;
        }
      );

      return {
        projectId,
        visitors,
      };
    }),

  realtimeData: publicProcedure
    .input(z.object({ shareId: z.string() }))
    .query(async ({ input, ctx }) => {
      const db = ctx.db;
      const widget = await db.shareWidget.findUnique({
        where: {
          id: input.shareId,
        },
        include: {
          project: {
            select: {
              domain: true,
              name: true,
            },
          },
        },
      });

      if (!widget?.public) {
        throw new TRPCNotFoundError('Widget not found');
      }

      const { projectId, options } = widget;

      if (options.type !== 'realtime') {
        throw new TRPCNotFoundError('Invalid widget type');
      }

      const { timezone } = await getSettingsForProject(ctx, projectId);

      const settings = { session_timezone: timezone };
      const totalSessionsStatement = sql`SELECT uniq(session_id) as total_sessions FROM ${EVENTS_TABLE} WHERE project_id = ${sql.string(projectId)} AND ${REALTIME_WINDOW}`;

      const minuteCountsStatement = sql`SELECT toStartOfMinute(created_at) as minute, uniq(session_id) as session_count, uniq(profile_id) as visitor_count FROM ${EVENTS_TABLE} WHERE project_id = ${sql.string(projectId)} AND ${REALTIME_WINDOW} GROUP BY minute ORDER BY minute ASC WITH FILL FROM toStartOfMinute(now() - INTERVAL 30 MINUTE) TO toStartOfMinute(now()) STEP INTERVAL 1 MINUTE`;

      const countriesQueryPromise = options.countries
        ? chQuery<{ country: string; count: number }>(
            ctx,
            sql`SELECT country, uniq(session_id) as count FROM ${EVENTS_TABLE} WHERE project_id = ${sql.string(projectId)} AND ${REALTIME_WINDOW} AND country != '' AND country IS NOT NULL GROUP BY country ORDER BY count DESC LIMIT ${sql.uint64(REALTIME_TOP_LIST_LIMIT)}`,
            settings
          )
        : Promise.resolve<Array<{ country: string; count: number }>>([]);

      const referrersQueryPromise = options.referrers
        ? chQuery<{ referrer: string; count: number }>(
            ctx,
            sql`SELECT referrer_name as referrer, uniq(session_id) as count FROM ${EVENTS_TABLE} WHERE project_id = ${sql.string(projectId)} AND ${REALTIME_WINDOW} AND referrer_name != '' AND referrer_name IS NOT NULL GROUP BY referrer_name ORDER BY count DESC LIMIT ${sql.uint64(REALTIME_TOP_LIST_LIMIT)}`,
            settings
          )
        : Promise.resolve<Array<{ referrer: string; count: number }>>([]);

      const pathsQueryPromise = options.paths
        ? chQuery<{ path: string; count: number }>(
            ctx,
            sql`SELECT path, uniq(session_id) as count FROM ${EVENTS_TABLE} WHERE project_id = ${sql.string(projectId)} AND ${REALTIME_WINDOW} AND path != '' AND path IS NOT NULL GROUP BY path ORDER BY count DESC LIMIT ${sql.uint64(REALTIME_TOP_LIST_LIMIT)}`,
            settings
          )
        : Promise.resolve<Array<{ path: string; count: number }>>([]);

      const [totalSessions, minuteCounts, countries, referrers, paths] =
        await Promise.all([
          chQuery<{ total_sessions: number }>(
            ctx,
            totalSessionsStatement,
            settings
          ),
          chQuery<{
            minute: string;
            session_count: number;
            visitor_count: number;
          }>(ctx, minuteCountsStatement, settings),
          countriesQueryPromise,
          referrersQueryPromise,
          pathsQueryPromise,
        ]);

      return {
        projectId,
        liveCount: totalSessions[0]?.total_sessions || 0,
        project: widget.project,
        histogram: minuteCounts.map((item) => ({
          minute: item.minute,
          sessionCount: item.session_count,
          visitorCount: item.visitor_count,
          timestamp: new Date(item.minute).getTime(),
          time: new Date(item.minute).toLocaleTimeString([], {
            hour: '2-digit',
            minute: '2-digit',
          }),
        })),
        countries: countries.map((item) => ({
          country: item.country,
          count: item.count,
        })),
        referrers: referrers.map((item) => ({
          referrer: item.referrer,
          count: item.count,
        })),
        paths: paths.map((item) => ({
          path: item.path,
          count: item.count,
        })),
      };
    }),
});