Skip to content

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

7.2 KB
Raw Blame
import { FilterParams } from "@rybbit/shared";
import { FastifyReply, FastifyRequest } from "fastify";
import { getSessionFilterStatement } from "../utils/sessionFilters.js";
import { enrichWithTraits } from "../utils/utils.js";
import { getTimeStatement } from "../utils/timeWindow.js";
import { analyticsRoute, runAnalyticsQuery, QuerySpec } from "../utils/analyticsQuery.js";
import { getSessionGoalMatcher, resolveSessionGoals, SessionGoal, SessionGoalMatcher } from "./sessionGoals.js";
import {
  buildAggregatedSessionsCTE,
  buildRangeConditions,
  buildSessionOrder,
  buildViewCondition,
  parseSessionListParams,
  REPLAY_LOOKUP_QUERY,
  sessionListParamsSchema,
} from "./sessionQuery.js";

export type GetSessionsResponse = {
  session_id: string;
  user_id: string; // Device fingerprint
  identified_user_id: string; // Custom user ID when identified, empty string otherwise
  traits: Record<string, unknown> | null;
  country: string;
  region: string;
  city: string;
  language: string;
  device_type: string;
  browser: string;
  browser_version: string;
  operating_system: string;
  operating_system_version: string;
  screen_width: number;
  screen_height: number;
  referrer: string;
  channel: string;
  hostname: string;
  page_title: string;
  querystring: string;
  utm_source: string;
  utm_medium: string;
  utm_campaign: string;
  utm_term: string;
  utm_content: string;
  session_end: string;
  session_start: string;
  session_duration: number;
  entry_page: string;
  exit_page: string;
  pageviews: number;
  events: number;
  errors: number;
  outbound: number;
  button_clicks: number;
  copies: number;
  form_submits: number;
  input_changes: number;
  ip: string;
  lat: number;
  lon: number;
  has_replay: number;
  // Goals the session completed, the most recent conversion first. Present
  // when the request asked for goals (include_goals, or the converted view).
  converted_goals?: SessionGoal[];
}[];

export interface GetSessionsRequest {
  Params: {
    siteId: string;
  };
  Querystring: FilterParams<{
    limit: number;
    page: number;
    user_id?: string;
    session_id?: string;
    identified_only?: string;
    view?: string;
    sort_by?: string;
    sort_order?: string;
    include_goals?: string;
    min_pageviews?: string;
    max_pageviews?: string;
    min_events?: string;
    max_events?: string;
    min_duration?: string;
    max_duration?: string;
  }>;
}

const DEFAULT_LIMIT = 100;
// The Globe timeline pages through sessions 10,000 at a time; nothing asks for more.
const MAX_LIMIT = 10_000;
// Session ids per replay lookup. Query parameters travel in the request URL,
// so a 10,000-row page is looked up in slices rather than as one long list.
const REPLAY_LOOKUP_CHUNK = 1000;

const NO_GOALS: SessionGoalMatcher = { goals: [], expression: "emptyArrayUInt32()" };

// Untrusted `limit`/`page`: anything unusable falls back to the defaults, and
// one request can never ask for more than MAX_LIMIT rows.
const toPositiveInt = (value: unknown, fallback: number) => {
  const parsed = parseInt(String(value), 10);
  return Number.isFinite(parsed) && parsed >= 1 ? parsed : fallback;
};

export const buildSessionsQuery = (
  query: GetSessionsRequest["Querystring"],
  siteId: number,
  matcher: SessionGoalMatcher = NO_GOALS
): QuerySpec => {
  const { filters, user_id: userId, session_id: sessionId, identified_only: identifiedOnly = "false" } = query;
  const params = sessionListParamsSchema.parse(query);
  const limit = Math.min(toPositiveInt(query.limit, DEFAULT_LIMIT), MAX_LIMIT);
  const page = toPositiveInt(query.page, 1);

  const timeStatement = getTimeStatement(query);

  // Use composable filter options:
  // - sessionLevelParams: per-event fields filter at session level (finds sessions
  //   containing a matching event) — required for any parameter the aggregated CTE
  //   below doesn't project, otherwise the outer WHERE hits an unknown identifier
  // - fieldMappings: CTE extracts UTM params as separate columns, so we need to map the field names
  const filterStatement = getSessionFilterStatement(filters, siteId, timeStatement);

  const ranges = buildRangeConditions(params);
  const conditions = [
    identifiedOnly === "true" ? "identified_user_id != ''" : null,
    buildViewCondition(params.view, query),
    ...ranges.conditions,
  ].filter((condition): condition is string => !!condition);

  // The ORDER BY belongs to the statement that carries the LIMIT. It used to
  // sit inside the CTE, where the outer query was free to ignore it, so
  // consecutive pages could repeat or skip sessions.
  const querySQL = `
  WITH ${buildAggregatedSessionsCTE(query, matcher)}
  SELECT *
  FROM AggregatedSessions
  WHERE 1 = 1 ${filterStatement}
  ${conditions.map(condition => `AND ${condition}`).join("\n  ")}
  ${buildSessionOrder(params.sort_by, params.sort_order)}
  LIMIT {limit:Int32} OFFSET {offset:Int32}
  `;

  return {
    query: querySQL,
    params: {
      siteId,
      user_id: userId,
      session_id: sessionId,
      limit,
      offset: (page - 1) * limit,
      ...ranges.queryParams,
    },
  };
};

type SessionRow = Omit<GetSessionsResponse[number], "traits" | "has_replay" | "converted_goals"> & {
  converted_goal_ids: number[];
  last_goal_ids: number[];
};

/** Slices of at most `size` ids, in order. */
export const chunkIds = (ids: string[], size: number): string[][] =>
  Array.from({ length: Math.ceil(ids.length / size) }, (_, index) => ids.slice(index * size, (index + 1) * size));

/**
 * Which of these sessions have a replay. A key lookup for the rows of one page,
 * instead of the join that used to read all of the site's replay metadata on
 * every list request.
 */
async function findSessionsWithReplay(siteId: number, sessionIds: string[]): Promise<Set<string>> {
  const found = await Promise.all(
    chunkIds(sessionIds, REPLAY_LOOKUP_CHUNK).map(ids =>
      runAnalyticsQuery<{ session_id: string }>({ query: REPLAY_LOOKUP_QUERY, params: { siteId, sessionIds: ids } })
    )
  );
  return new Set(found.flat().map(row => row.session_id));
}

export const getSessions = analyticsRoute<GetSessionsRequest>(
  "sessions",
  async (req: FastifyRequest<GetSessionsRequest>, res: FastifyReply) => {
    const siteId = Number(req.params.siteId);

    const parsed = parseSessionListParams(req.query);
    if (!parsed.ok) {
      return res.status(400).send({ error: parsed.error });
    }

    // Goals are matched only for a request that shows or filters on them.
    const wantsGoals = parsed.params.include_goals === "true" || parsed.params.view === "converted";
    const matcher = wantsGoals ? await getSessionGoalMatcher(siteId, req) : NO_GOALS;
    const rows = await runAnalyticsQuery<SessionRow>(buildSessionsQuery(req.query, siteId, matcher));

    const withReplay = await findSessionsWithReplay(
      siteId,
      rows.map(row => row.session_id)
    );

    const data = rows.map(({ converted_goal_ids, last_goal_ids, ...row }) => ({
      ...row,
      has_replay: withReplay.has(row.session_id) ? 1 : 0,
      ...(wantsGoals
        ? { converted_goals: resolveSessionGoals(matcher, last_goal_ids ?? [], converted_goal_ids ?? []) }
        : {}),
    }));

    // Enrich with traits from Postgres
    const dataWithTraits = await enrichWithTraits(data, siteId);

    return res.send({ data: dataWithTraits });
  }
);