Skip to content

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

8.2 KB
Raw Blame
// `protectedProcedure` runs `enforceUserIsAuthed` + `enforceAccess` BEFORE the
// input parser. The explicit checks in the handlers below stay: `enforceAccess`
// only sees a TOP-LEVEL `projectId` / `organizationId`, so anything resolved
// from another id needs its own.

import { z } from 'zod';
import { createTRPCRouter, protectedProcedure } from '../../rpc/base';
import {
  INSIGHT_LIST_ALL_DEFAULT_LIMIT,
  INSIGHT_LIST_ALL_MAX_LIMIT,
  INSIGHT_LIST_DEFAULT_LIMIT,
  INSIGHT_LIST_MAX_LIMIT,
  type InsightPayload,
} from './insight.constants';

const DAY_MS = 24 * 60 * 60 * 1000;
const EXPLAIN_COLUMNS = [
  'referrer_name',
  'country',
  'device',
  'utm_source',
] as const;

// `list` deduplicates by window AFTER the query, so it has to over-fetch or a
// page of duplicates comes back short.
const LIST_DEDUPE_OVERFETCH = 3;

export const insightRouter = createTRPCRouter({
  list: protectedProcedure
    .input(
      z.object({
        projectId: z.string(),
        limit: z
          .number()
          .min(1)
          .max(INSIGHT_LIST_MAX_LIMIT)
          .optional()
          .default(INSIGHT_LIST_DEFAULT_LIMIT),
      })
    )
    .query(async ({ input: { projectId, limit }, ctx }) => {
      const userId = ctx.session.userId;
      await ctx.services.auth.requireProjectAccess({
        userId,
        projectId,
        level: 'read',
      });

      // AI relevanceScore leads (un-enriched insights sort last via
      // nulls:last), with the statistical impactScore as the tiebreaker.
      const allInsights = await ctx.services.insight.listInsights({
        projectId,
        limit: limit * LIST_DEDUPE_OVERFETCH,
      });

      const windowKindPriority: Record<string, number> = {
        yesterday: 1,
        rolling_7d: 2,
        rolling_30d: 3,
      };

      const deduplicated = new Map<string, (typeof allInsights)[0]>();
      for (const insight of allInsights) {
        const key = `${insight.moduleKey}:${insight.dimensionKey}`;
        const existing = deduplicated.get(key);
        const currentPriority = windowKindPriority[insight.windowKind] ?? 999;
        const existingPriority = existing
          ? (windowKindPriority[existing.windowKind] ?? 999)
          : 999;

        if (!existing || currentPriority < existingPriority) {
          deduplicated.set(key, insight);
        }
      }

      return Array.from(deduplicated.values())
        .sort(
          (a, b) =>
            (b.relevanceScore ?? -1) - (a.relevanceScore ?? -1) ||
            (b.impactScore ?? 0) - (a.impactScore ?? 0)
        )
        .slice(0, limit)
        .map(({ impactScore, ...rest }) => rest); // strip impactScore from the response
    }),

  listAll: protectedProcedure
    .input(
      z.object({
        projectId: z.string(),
        limit: z
          .number()
          .min(1)
          .max(INSIGHT_LIST_ALL_MAX_LIMIT)
          .optional()
          .default(INSIGHT_LIST_ALL_DEFAULT_LIMIT),
      })
    )
    .query(async ({ input: { projectId, limit }, ctx }) => {
      const userId = ctx.session.userId;
      await ctx.services.auth.requireProjectAccess({
        userId,
        projectId,
        level: 'read',
      });

      return ctx.services.insight.listAllInsights({ projectId, limit });
    }),

  // Decompose the insight's change across referrer/country/device/utm
  // (current vs baseline window), pull nearby references, and have the AI
  // explain which sub-segment drove it. Cached per insight version so repeat
  // clicks don't re-bill the LLM.
  explain: protectedProcedure
    .input(z.object({ insightId: z.string() }))
    .mutation(async ({ input: { insightId }, ctx }) => {
      const userId = ctx.session.userId;

      const db = ctx.db;
      const { getSegmentDailySeriesCore, getTrafficBreakdownCore } =
        await import('../overview/overview.service');

      const insight = await db.projectInsight.findUniqueOrThrow({
        where: { id: insightId },
        select: {
          projectId: true,
          title: true,
          aiSummary: true,
          summary: true,
          dimensionKey: true,
          windowKind: true,
          windowStart: true,
          windowEnd: true,
          payload: true,
          lastUpdatedAt: true,
        },
      });

      // Reads an existing insight and explains it. Nothing about the project
      // changes, so a read-level member may do it.
      await ctx.services.auth.requireProjectAccess({
        userId,
        projectId: insight.projectId,
        level: 'read',
      });

      const cacheKey = `insight-explain:${insightId}:${insight.lastUpdatedAt.getTime()}`;

      const end = insight.windowEnd ?? new Date();
      const start = insight.windowStart ?? new Date(end.getTime() - 7 * DAY_MS);
      const spanMs = Math.max(end.getTime() - start.getTime(), DAY_MS);
      const baseEnd = new Date(start.getTime());
      const baseStart = new Date(start.getTime() - spanMs);
      const iso = (d: Date) => d.toISOString();

      const breakdowns = await Promise.all(
        EXPLAIN_COLUMNS.map(async (column) => {
          const [cur, base] = await Promise.all([
            getTrafficBreakdownCore(ctx, {
              projectId: insight.projectId,
              column,
              startDate: iso(start),
              endDate: iso(end),
            }),
            getTrafficBreakdownCore(ctx, {
              projectId: insight.projectId,
              column,
              startDate: iso(baseStart),
              endDate: iso(baseEnd),
            }),
          ]);
          const compact = (rows: typeof cur) =>
            rows.slice(0, 8).map((r) => ({
              name: r.name ?? null,
              sessions: Number(r.sessions ?? 0),
            }));
          return { column, current: compact(cur), baseline: compact(base) };
        })
      );

      // Daily series for the insight's own segment, so the model can read
      // the shape of the change (a one-off spike vs sustained growth)
      // instead of only current-vs-baseline totals. Best-effort.
      const payload = insight.payload as InsightPayload | null;
      const segment = payload?.dimensions?.[0];
      const primaryMetric = payload?.primaryMetric ?? 'sessions';
      let dailySeries:
        | {
            metric: string;
            current: { date: string; sessions: number }[];
            baseline: { date: string; sessions: number }[];
          }
        | undefined;

      if (segment?.key && segment.value) {
        const [curSeries, baseSeries] = await Promise.all([
          getSegmentDailySeriesCore(ctx, {
            projectId: insight.projectId,
            column: segment.key,
            value: segment.value,
            startDate: iso(start),
            endDate: iso(end),
          }),
          getSegmentDailySeriesCore(ctx, {
            projectId: insight.projectId,
            column: segment.key,
            value: segment.value,
            startDate: iso(baseStart),
            endDate: iso(baseEnd),
          }),
        ]);

        const toMetric = (points: typeof curSeries) =>
          points.map((p) => ({
            date: p.date.slice(0, 10),
            sessions: primaryMetric === 'pageviews' ? p.pageviews : p.sessions,
          }));

        if (curSeries.length > 0) {
          dailySeries = {
            metric: primaryMetric,
            current: toMetric(curSeries),
            baseline: toMetric(baseSeries),
          };
        }
      }

      const references = await db.reference.findMany({
        where: {
          projectId: insight.projectId,
          date: {
            gte: new Date(start.getTime() - 3 * DAY_MS),
            lte: new Date(end.getTime() + DAY_MS),
          },
        },
        orderBy: { date: 'desc' },
        take: 10,
        select: { title: true, date: true },
      });

      return ctx.services.insight.explainInsight(
        {
          insight: {
            title: insight.aiSummary ?? insight.title,
            dimension: insight.dimensionKey,
            window: insight.windowKind,
            summary: insight.summary ?? undefined,
          },
          dailySeries,
          breakdowns,
          references: references.map((r) => ({
            title: r.title,
            date: r.date.toISOString().slice(0, 10),
          })),
        },
        cacheKey
      );
    }),
});