Skip to content

🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.

12.1 KB
Raw Blame
import { FilterParams } from "@rybbit/shared";
import { FastifyReply, FastifyRequest } from "fastify";
import { eq, and } from "drizzle-orm";
import { db } from "../../../db/postgres/postgres.js";
import { userProfiles, userAliases } from "../../../db/postgres/schema.js";
import { SESSION_CHANNEL_AGG, SESSION_REFERRER_AGG } from "../utils/sessionAttribution.js";
import { runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { buildUserEventScope } from "./userEventScope.js";

interface UserPageviewData {
  sessions: number;
  duration: number;
  user_id: string; // Device fingerprint
  identified_user_id: string; // Custom user ID when identified
  country: string;
  region: string;
  city: string;
  language: string;
  device_type: string;
  browser: string;
  browser_version: string;
  operating_system: string;
  operating_system_version: string;
  screen_height: number;
  screen_width: number;
  last_seen: string;
  first_seen: string;
  pageviews: number;
  events: number;
  ip: string;
  first_referrer: string;
  first_channel: string;
  first_entry_page: string;
  first_utm_source: string;
  first_utm_medium: string;
  first_utm_campaign: string;
  last_referrer: string;
  last_channel: string;
  timezone: string;
}

interface UserVitalsData {
  lcp_p75: number | null;
  cls_p75: number | null;
  inp_p75: number | null;
  fcp_p75: number | null;
  ttfb_p75: number | null;
  performance_events: number;
}

interface UserLocationBreakdown {
  country: string;
  region: string;
  city: string;
  sessions: number;
  last_seen: string;
}

interface UserDeviceBreakdown {
  device_type: string;
  browser: string;
  browser_version: string;
  operating_system: string;
  operating_system_version: string;
  screen_width: number;
  screen_height: number;
  sessions: number;
  last_seen: string;
}

interface LinkedDevice {
  anonymous_id: string;
  created_at: string;
}

export interface UserInfoResponse {
  data: UserPageviewData & {
    traits: Record<string, unknown> | null;
    linked_devices: LinkedDevice[];
    vitals: UserVitalsData | null;
    locations: UserLocationBreakdown[];
    devices: UserDeviceBreakdown[];
  };
}

export const buildUserInfoQueries = (query: FilterParams, siteId: number) => {
  const { filteredSessionsCTE, withFilteredSessions, scopedEvents } = buildUserEventScope(query, siteId);

  const sessionsQuery = `
    WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""}
    sessions AS (
        SELECT
            session_id,
            argMax(user_id, timestamp) AS user_id,
            argMax(identified_user_id, timestamp) AS identified_user_id,
            argMax(country, timestamp) AS country,
            argMax(region, timestamp) AS region,
            argMax(city, timestamp) AS city,
            argMax(language, timestamp) AS language,
            argMax(device_type, timestamp) AS device_type,
            argMax(browser, timestamp) AS browser,
            argMax(browser_version, timestamp) AS browser_version,
            argMax(operating_system, timestamp) AS operating_system,
            argMax(operating_system_version, timestamp) AS operating_system_version,
            argMax(screen_width, timestamp) AS screen_width,
            argMax(screen_height, timestamp) AS screen_height,
            ${SESSION_REFERRER_AGG} AS referrer,
            ${SESSION_CHANNEL_AGG} AS channel,
            argMinIf(url_parameters['utm_source'], timestamp, url_parameters['utm_source'] != '') AS utm_source,
            argMinIf(url_parameters['utm_medium'], timestamp, url_parameters['utm_medium'] != '') AS utm_medium,
            argMinIf(url_parameters['utm_campaign'], timestamp, url_parameters['utm_campaign'] != '') AS utm_campaign,
            argMaxIf(timezone, timestamp, timezone != '') AS user_timezone,
            MAX(timestamp) AS session_end,
            MIN(timestamp) AS session_start,
            dateDiff('second', MIN(timestamp), MAX(timestamp)) AS session_duration,
            argMinIf(pathname, timestamp_ms, type = 'pageview') AS entry_page,
            argMaxIf(pathname, timestamp_ms, type = 'pageview') AS exit_page,
            countIf(type = 'pageview') AS pageviews,
            countIf(type = 'custom_event') AS events,
            argMax(ip, timestamp) AS ip
        FROM ${scopedEvents}
        GROUP BY
            session_id
        ORDER BY
            session_end DESC
    )
    SELECT
        COUNT(DISTINCT session_id) AS sessions,
        ROUND(avg(session_duration)) AS duration,
        any(user_id) AS user_id,
        any(identified_user_id) AS identified_user_id,
        any(country) as country,
        any(region) AS region,
        any(city) AS city,
        any(language) AS language,
        any(device_type) AS device_type,
        any(browser) AS browser,
        any(browser_version) AS browser_version,
        any(operating_system) AS operating_system,
        any(operating_system_version) AS operating_system_version,
        any(screen_height) AS screen_height,
        any(screen_width) AS screen_width,
        MAX(session_end) AS last_seen,
        MIN(session_start) AS first_seen,
        SUM(pageviews) AS pageviews,
        SUM(events) AS events,
        any(ip) AS ip,
        argMin(referrer, session_start) AS first_referrer,
        argMin(channel, session_start) AS first_channel,
        argMinIf(entry_page, session_start, entry_page != '') AS first_entry_page,
        argMinIf(utm_source, session_start, utm_source != '') AS first_utm_source,
        argMinIf(utm_medium, session_start, utm_medium != '') AS first_utm_medium,
        argMinIf(utm_campaign, session_start, utm_campaign != '') AS first_utm_campaign,
        argMax(referrer, session_end) AS last_referrer,
        argMax(channel, session_end) AS last_channel,
        argMaxIf(user_timezone, session_end, user_timezone != '') AS timezone
    FROM
        sessions
    -- An aggregate without GROUP BY returns one all-default row for no input;
    -- return none, so an unknown user 404s and a claimed device follows its alias.
    HAVING count() > 0
      `;

  // p75 Web Vitals across every performance event this user produced.
  // Separate query: the sessions CTE collapses rows per session, which
  // would turn an event-level quantile into a quantile of session picks.
  const vitalsQuery = `
    ${withFilteredSessions}
    SELECT
        quantile(0.75)(lcp) AS lcp_p75,
        quantile(0.75)(cls) AS cls_p75,
        quantile(0.75)(inp) AS inp_p75,
        quantile(0.75)(fcp) AS fcp_p75,
        quantile(0.75)(ttfb) AS ttfb_p75,
        COUNT(*) AS performance_events
    FROM ${scopedEvents}
    WHERE type = 'performance'
      `;

  // Every location this user was seen in, by session share. A session that
  // moves between cities counts once per city, so shares are approximate.
  const locationsQuery = `
    ${withFilteredSessions}
    SELECT
        country,
        region,
        city,
        uniq(session_id) AS sessions,
        MAX(timestamp) AS last_seen
    FROM ${scopedEvents}
    WHERE country != ''
    GROUP BY
        country, region, city
    ORDER BY
        sessions DESC, last_seen DESC
    LIMIT 20
      `;

  // Every device this user was seen on. Grouped without versions so a
  // browser update doesn't split one physical device into many rows;
  // versions and screen are argMax'd to the latest sighting instead.
  const devicesQuery = `
    ${withFilteredSessions}
    SELECT
        device_type,
        browser,
        operating_system,
        argMax(browser_version, timestamp) AS browser_version,
        argMax(operating_system_version, timestamp) AS operating_system_version,
        argMax(screen_width, timestamp) AS screen_width,
        argMax(screen_height, timestamp) AS screen_height,
        uniq(session_id) AS sessions,
        MAX(timestamp) AS last_seen
    FROM ${scopedEvents}
    WHERE NOT (device_type = '' AND browser = '' AND operating_system = '')
    GROUP BY
        device_type, browser, operating_system
    ORDER BY
        sessions DESC, last_seen DESC
    LIMIT 20
      `;

  return { sessionsQuery, vitalsQuery, locationsQuery, devicesQuery };
};

export async function getUserInfo(
  req: FastifyRequest<{
    Params: {
      siteId: string;
      userId: string;
    };
    Querystring: FilterParams;
  }>,
  res: FastifyReply
) {
  const { userId, siteId } = req.params;

  const numericSiteId = Number(siteId);

  const { sessionsQuery, vitalsQuery, locationsQuery, devicesQuery } = buildUserInfoQueries(req.query, numericSiteId);

  const loadUser = (effectiveUserId: string) => {
    const chParams = { userId: effectiveUserId, site: siteId };
    return Promise.all([
      runAnalyticsQuery<UserPageviewData>({ query: sessionsQuery, params: chParams }),
      runAnalyticsQuery<UserVitalsData>({ query: vitalsQuery, params: chParams }),
      runAnalyticsQuery<UserLocationBreakdown>({ query: locationsQuery, params: chParams }),
      runAnalyticsQuery<UserDeviceBreakdown>({ query: devicesQuery, params: chParams }),
      // Get user profile traits from Postgres
      db
        .select()
        .from(userProfiles)
        .where(and(eq(userProfiles.siteId, numericSiteId), eq(userProfiles.userId, effectiveUserId)))
        .limit(1),
      // Get linked devices (all anonymous IDs for this user) from Postgres
      db
        .select({
          anonymous_id: userAliases.anonymousId,
          created_at: userAliases.createdAt,
        })
        .from(userAliases)
        .where(and(eq(userAliases.siteId, numericSiteId), eq(userAliases.userId, effectiveUserId))),
    ]);
  };

  try {
    let [data, vitalsData, locations, devices, profileResult, aliasesResult] = await loadUser(userId);

    // A device whose events have all been claimed by an identity — the dashboard's
    // "Identify User" action backfills the full history — no longer has anonymous
    // rows, so the anonymous branch of the query matches nothing. Follow the alias
    // rather than 404ing a route the operator was just looking at. Only on a miss:
    // while a shared fingerprint still has anonymous activity of its own, that
    // activity is what this route is about, and resolving early would put someone
    // else's identity on it.
    if (data.length === 0) {
      const alias = await db
        .select({ userId: userAliases.userId })
        .from(userAliases)
        .where(and(eq(userAliases.siteId, numericSiteId), eq(userAliases.anonymousId, userId)))
        .limit(1);
      if (alias.length > 0 && alias[0].userId !== userId) {
        [data, vitalsData, locations, devices, profileResult, aliasesResult] = await loadUser(alias[0].userId);
      }
    }

    // If no data found for user
    if (data.length === 0) {
      return res.status(404).send({
        error: "User not found",
      });
    }

    let identifiedUserId = data[0].identified_user_id;
    let traits = profileResult[0]?.traits || null;

    // The identify backfill mutation in ClickHouse is async, so a freshly
    // identified device can still have all-blank identified_user_id here.
    // Fall back to the alias table so identity and traits show immediately.
    if (!identifiedUserId) {
      const alias = await db
        .select({ userId: userAliases.userId })
        .from(userAliases)
        .where(and(eq(userAliases.siteId, numericSiteId), eq(userAliases.anonymousId, userId)))
        .limit(1);
      if (alias.length > 0) {
        identifiedUserId = alias[0].userId;
        const aliasProfile = await db
          .select()
          .from(userProfiles)
          .where(and(eq(userProfiles.siteId, numericSiteId), eq(userProfiles.userId, identifiedUserId)))
          .limit(1);
        traits = aliasProfile[0]?.traits || traits;
      }
    }

    const linked_devices = aliasesResult.map(alias => ({
      anonymous_id: alias.anonymous_id,
      created_at: alias.created_at,
    }));

    const vitals = vitalsData[0]?.performance_events > 0 ? vitalsData[0] : null;

    return res.send({
      data: {
        ...data[0],
        identified_user_id: identifiedUserId,
        traits,
        linked_devices,
        vitals,
        locations,
        devices,
      },
    });
  } catch (error) {
    req.log.error({ err: error }, "Error fetching user info");
    return res.status(500).send({
      error: "Internal server error",
    });
  }
}