Skip to content

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

7.2 KB
Raw Blame
import { FastifyReply, FastifyRequest } from "fastify";
import { sql } from "drizzle-orm";
import { z } from "zod";
import { db } from "../../../db/postgres/postgres.js";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { effectiveUserId } from "../utils/effectiveUserId.js";
import { pagingParam } from "./userScope.js";

export interface GetUserTraitKeysRequest {
  Params: { siteId: string };
}

export interface GetUserTraitValuesRequest {
  Params: { siteId: string };
  Querystring: { key: string; limit?: string; offset?: string };
}

export interface GetUserTraitValueUsersRequest {
  Params: { siteId: string };
  Querystring: { key: string; value: string; limit?: string; offset?: string };
}

const traitPageSchema = z.object({ limit: pagingParam("limit", 1), offset: pagingParam("offset", 0) });

/**
 * A page of a public route's results: malformed params are an error, and the
 * limit is capped (the users page also sends its ids to ClickHouse as one list).
 */
export const parseTraitPage = (
  query: unknown,
  defaultLimit: number,
  maxLimit: number
): { ok: true; limit: number; offset: number } | { ok: false; error: string } => {
  const parsed = traitPageSchema.safeParse(query);
  if (!parsed.success) {
    return { ok: false, error: parsed.error.issues[0]?.message ?? "Invalid pagination parameters" };
  }
  return {
    ok: true,
    limit: Math.min(parsed.data.limit ?? defaultLimit, maxLimit),
    offset: parsed.data.offset ?? 0,
  };
};

export const getUserTraitKeys = analyticsRoute<GetUserTraitKeysRequest>(
  "user trait keys",
  async (req: FastifyRequest<GetUserTraitKeysRequest>, res: FastifyReply) => {
    const siteId = Number(req.params.siteId);

    const result = await db.execute<{ key: string; user_count: number }>(sql`
      SELECT key, COUNT(*)::int AS user_count
      FROM user_profiles,
           LATERAL jsonb_object_keys(COALESCE(traits, '{}'::jsonb)) AS key
      WHERE site_id = ${siteId}
      GROUP BY key
      ORDER BY user_count DESC
    `);

    return res.send({
      keys: (result as any[]).map((row) => ({
        key: row.key,
        userCount: row.user_count,
      })),
    });
  }
);

export const getUserTraitValues = analyticsRoute<GetUserTraitValuesRequest>(
  "user trait values",
  async (req: FastifyRequest<GetUserTraitValuesRequest>, res: FastifyReply) => {
    const siteId = Number(req.params.siteId);
    const { key } = req.query;

    if (!key) {
      return res.status(400).send({ error: "key query parameter is required" });
    }

    const page = parseTraitPage(req.query, 1000, 10_000);
    if (!page.ok) {
      return res.status(400).send({ error: page.error });
    }
    const { limit, offset } = page;

    const [valuesResult, countResult] = await Promise.all([
      db.execute<{ value: string; user_count: number }>(sql`
        SELECT traits->>${key} AS value, COUNT(*)::int AS user_count
        FROM user_profiles
        WHERE site_id = ${siteId} AND traits ? ${key}
        GROUP BY value
        ORDER BY user_count DESC
        LIMIT ${limit} OFFSET ${offset}
      `),
      db.execute<{ total: number }>(sql`
        SELECT COUNT(DISTINCT traits->>${key})::int AS total
        FROM user_profiles
        WHERE site_id = ${siteId} AND traits ? ${key}
      `),
    ]);

    const total = (countResult as any[])[0]?.total ?? 0;

    return res.send({
      values: (valuesResult as any[]).map((row) => ({
        value: row.value,
        userCount: row.user_count,
      })),
      total,
      hasMore: offset + limit < total,
    });
  }
);

// Session counts + metadata from ClickHouse for a set of identified users.
// Use events.identified_user_id to avoid conflict with the argMax alias
export const buildTraitValueUsersQuery = () => `
      SELECT
        ${effectiveUserId("events")} AS effective_user_id,
        argMax(events.user_id, timestamp) AS user_id,
        argMax(events.identified_user_id, timestamp) AS identified_user_id,
        argMax(country, timestamp) AS country,
        argMax(region, timestamp) AS region,
        argMax(city, timestamp) AS city,
        argMax(browser, timestamp) AS browser,
        argMax(operating_system, timestamp) AS operating_system,
        argMax(device_type, timestamp) AS device_type,
        count(DISTINCT session_id) AS sessions
      FROM events
      WHERE site_id = {siteId:Int32}
        AND events.identified_user_id IN ({userIds:Array(String)})
      GROUP BY effective_user_id
    `;

export const getUserTraitValueUsers = analyticsRoute<GetUserTraitValueUsersRequest>(
  "trait value users",
  async (req: FastifyRequest<GetUserTraitValueUsersRequest>, res: FastifyReply) => {
    const siteId = Number(req.params.siteId);
    const { key, value } = req.query;

    if (!key || value === undefined) {
      return res.status(400).send({ error: "key and value query parameters are required" });
    }

    const page = parseTraitPage(req.query, 50, 1000);
    if (!page.ok) {
      return res.status(400).send({ error: page.error });
    }
    const { limit, offset } = page;

    // Step 1: Find matching user IDs and their traits from Postgres
    const [profilesResult, countResult] = await Promise.all([
      db.execute<{ user_id: string; traits: Record<string, unknown> | null }>(sql`
        SELECT user_id, traits
        FROM user_profiles
        WHERE site_id = ${siteId} AND traits->>${key} = ${value}
        ORDER BY user_id
        LIMIT ${limit} OFFSET ${offset}
      `),
      db.execute<{ total: number }>(sql`
        SELECT COUNT(*)::int AS total
        FROM user_profiles
        WHERE site_id = ${siteId} AND traits->>${key} = ${value}
      `),
    ]);

    const profiles = profilesResult as any[];
    const total = (countResult as any[])[0]?.total ?? 0;

    if (profiles.length === 0) {
      return res.send({ users: [], total, hasMore: offset + limit < total });
    }

    const userIds = profiles.map((p: any) => p.user_id);

    // Step 2: Get session counts + metadata from ClickHouse
    const chData = await runAnalyticsQuery<{
      effective_user_id: string;
      user_id: string;
      identified_user_id: string;
      country: string;
      region: string;
      city: string;
      browser: string;
      operating_system: string;
      device_type: string;
      sessions: number;
    }>({
      query: buildTraitValueUsersQuery(),
      params: {
        siteId,
        userIds,
      },
    });

    // Step 3: Build lookup from ClickHouse data keyed by identified_user_id,
    // then iterate Postgres profiles so every profile is returned even if
    // ClickHouse has no matching events (zero-session fallback).
    const chLookup = new Map(
      chData.map((row) => [row.identified_user_id, row])
    );

    const users = profiles.map((p: any) => {
      const ch = chLookup.get(p.user_id);
      return {
        user_id: ch?.user_id ?? p.user_id,
        identified_user_id: p.user_id,
        traits: p.traits ?? null,
        country: ch?.country ?? "",
        region: ch?.region ?? "",
        city: ch?.city ?? "",
        browser: ch?.browser ?? "",
        operating_system: ch?.operating_system ?? "",
        device_type: ch?.device_type ?? "",
        sessions: ch?.sessions ?? 0,
      };
    });

    return res.send({
      users,
      total,
      hasMore: offset + limit < total,
    });
  }
);