🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import { FastifyReply, FastifyRequest } from "fastify";
import { z } from "zod";
import { enrichWithTraits } from "../utils/utils.js";
import { getTimeStatement } from "../utils/timeWindow.js";
import { FilterParams } from "@rybbit/shared";
import { SESSION_CHANNEL_AGG, SESSION_REFERRER_AGG } from "../utils/sessionAttribution.js";
import { buildFilteredSessionsCTE } from "../utils/sessionFilters.js";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { effectiveUserId } from "../utils/effectiveUserId.js";
import {
buildUserNarrowing,
pagingParam,
resolveSearchScope,
resolveTraitCohort,
UserNarrowingParams,
userNarrowingSchema,
UserScope,
} from "./userScope.js";
export type GetUsersResponse = {
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;
browser: string;
operating_system: string;
device_type: string;
pageviews: number;
events: number;
sessions: number;
hostname: string;
last_seen: string;
first_seen: string;
}[];
export interface GetUsersRequest {
Params: {
siteId: string;
};
Querystring: FilterParams<
UserNarrowingParams & {
page?: string;
page_size?: string;
sort_by?: string;
sort_order?: string;
}
>;
}
const DEFAULT_PAGE_SIZE = 100;
// The dashboard asks for at most 100 rows; larger pages are capped, as the
// sessions list caps its own.
const MAX_PAGE_SIZE = 1000;
// The offset is bound as Int32.
const MAX_OFFSET = 2_147_483_647;
const usersPaginationSchema = z.object({
page: pagingParam("page", 1),
page_size: pagingParam("page_size", 1),
});
/** The list's page and offset: a malformed value is an error, an oversized page is capped. */
export const parseUsersPagination = (
query: unknown
): { ok: true; page: number; pageSize: number; offset: number } | { ok: false; error: string } => {
const parsed = usersPaginationSchema.safeParse(query);
if (!parsed.success) {
return { ok: false, error: parsed.error.issues[0]?.message ?? "Invalid pagination parameters" };
}
const page = parsed.data.page ?? 1;
const pageSize = Math.min(parsed.data.page_size ?? DEFAULT_PAGE_SIZE, MAX_PAGE_SIZE);
const offset = (page - 1) * pageSize;
if (offset > MAX_OFFSET) {
return { ok: false, error: "page is out of range" };
}
return { ok: true, page, pageSize, offset };
};
export const buildUsersQuery = (
query: GetUsersRequest["Querystring"],
siteId: number,
scope: UserScope | null,
isCountQuery: boolean = false
) => {
const { filters, sort_by: sortBy = "last_seen", sort_order: sortOrder = "desc" } = query;
const narrowing = buildUserNarrowing(query, scope ?? {});
// Ids resolved in Postgres (a profile search, a trait group) force the
// identified-only view: profiles only exist for identified users.
const filterIdentified = query.identified_only === "true" || !!scope?.matchingUserIds;
// Validate sort parameters
const validSortFields = ["first_seen", "last_seen", "pageviews", "sessions", "events"];
const actualSortBy = validSortFields.includes(sortBy) ? sortBy : "last_seen";
const actualSortOrder = sortOrder === "asc" ? "ASC" : "DESC";
// Dimension filters qualify sessions. User aggregates then consume every
// event in those sessions, so an acquisition value on the landing event does
// not discard later pageviews or custom events from the same session.
const timeStatement = getTimeStatement(query);
const filteredSessionsCTE = buildFilteredSessionsCTE(filters, siteId, timeStatement);
const filteredSessionsJoin = filteredSessionsCTE ? "INNER JOIN FilteredSessions USING (session_id)" : "";
const withFilteredSessions = filteredSessionsCTE ? `WITH ${filteredSessionsCTE}` : "";
// Query to get total count
if (isCountQuery) {
// A minimum session count or "new" is a property of the user, not of any
// one event, so the count has to build the per-user rows it filters.
if (narrowing.needsUserAggregate) {
return `
${withFilteredSessions}
SELECT count() AS total_count
FROM (
SELECT
${effectiveUserId("events")} AS effective_user_id,
argMax(identified_user_id, timestamp) AS identified_user_id,
count(DISTINCT session_id) AS sessions
FROM (
SELECT *
FROM events
${filteredSessionsJoin}
WHERE
site_id = {siteId:Int32}
${timeStatement}
${narrowing.eventConditions}
) AS events
GROUP BY effective_user_id
)
WHERE 1 = 1
${narrowing.userConditions}
`;
}
return filterIdentified
? `
${withFilteredSessions}
SELECT count(*) AS total_count
FROM (
SELECT DISTINCT identified_user_id
FROM events
${filteredSessionsJoin}
WHERE
site_id = {siteId:Int32}
AND identified_user_id != ''
${timeStatement}
${narrowing.eventConditions}
)
`
: `
${withFilteredSessions}
SELECT
count(DISTINCT ${effectiveUserId("events")}) AS total_count
FROM events
${filteredSessionsJoin}
WHERE
site_id = {siteId:Int32}
${timeStatement}
${narrowing.eventConditions}
`;
}
return `
WITH ${filteredSessionsCTE ? `${filteredSessionsCTE},` : ""}
AggregatedUsers AS (
SELECT
-- Group by effective user: identified_user_id for identified users, user_id (device) for anonymous
${effectiveUserId("events")} AS effective_user_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(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(device_type, timestamp) AS device_type,
argMax(screen_width, timestamp) AS screen_width,
argMax(screen_height, timestamp) AS screen_height,
${SESSION_REFERRER_AGG} AS referrer,
${SESSION_CHANNEL_AGG} AS channel,
argMin(hostname, timestamp) AS hostname,
countIf(type = 'pageview') AS pageviews,
countIf(type = 'custom_event') AS events,
count(distinct session_id) AS sessions,
max(timestamp) AS last_seen,
min(timestamp) AS first_seen,
argMax(tag, timestamp) AS tag
FROM (
SELECT *
FROM events
${filteredSessionsJoin}
WHERE
site_id = {siteId:Int32}
${timeStatement}
${narrowing.eventConditions}
) AS events
GROUP BY
effective_user_id
)
SELECT
*
FROM AggregatedUsers
WHERE 1 = 1
${narrowing.userConditions}
ORDER BY ${actualSortBy} ${actualSortOrder}, effective_user_id ASC
LIMIT {limit:Int32} OFFSET {offset:Int32}
`;
};
export const getUsers = analyticsRoute<GetUsersRequest>(
"users",
async (req: FastifyRequest<GetUsersRequest>, res: FastifyReply) => {
const parsed = userNarrowingSchema.safeParse(req.query);
if (!parsed.success) {
return res.status(400).send({ error: parsed.error.issues[0]?.message ?? "Invalid query parameters" });
}
const pagination = parseUsersPagination(req.query);
if (!pagination.ok) {
return res.status(400).send({ error: pagination.error });
}
const { trait_key: traitKey, trait_value: traitValue, trait_missing: traitMissing } = parsed.data;
const siteId = Number(req.params.siteId);
const { page: pageNum, pageSize: pageSizeNum, offset } = pagination;
if (traitKey && traitValue === undefined && traitMissing !== "true") {
return res.status(400).send({ error: "trait_key needs trait_value or trait_missing=true" });
}
const empty = (flags: { searchLimited?: boolean; breakdownLimited?: boolean } = {}) =>
res.send({ data: [], totalCount: 0, page: pageNum, pageSize: pageSizeNum, ...flags });
// A name, username or email search resolves to identified ids in Postgres.
const search = await resolveSearchScope(req.query, siteId);
if (search.matchingUserIds?.length === 0) {
return empty();
}
let scope: UserScope = { matchingUserIds: search.matchingUserIds };
// One group of a trait breakdown: the people in the period whose trait has
// this value, or who have no value for it.
if (traitKey) {
const cohort = await resolveTraitCohort(req.query, siteId, traitKey, scope);
if (cohort.limited) {
return empty({ searchLimited: search.limited, breakdownLimited: true });
}
if (traitMissing === "true" && scope.matchingUserIds) {
// Already limited to the searched ids: drop the ones with a value
// rather than sending a second list.
const ids = scope.matchingUserIds.filter(userId => !cohort.values.has(userId));
if (ids.length === 0) {
return empty({ searchLimited: search.limited });
}
scope = { matchingUserIds: ids };
} else if (traitMissing === "true") {
scope = { excludedUserIds: [...cohort.values.keys()] };
} else {
const ids = [...cohort.values].filter(([, value]) => value === traitValue).map(([userId]) => userId);
if (ids.length === 0) {
return empty({ searchLimited: search.limited });
}
scope = { matchingUserIds: ids };
}
}
const { params: narrowingParams } = buildUserNarrowing(req.query, scope);
// Execute both queries in parallel
const [data, countData] = await Promise.all([
runAnalyticsQuery<Omit<GetUsersResponse[number], "traits">>({
query: buildUsersQuery(req.query, siteId, scope, false),
params: {
siteId,
limit: pageSizeNum,
offset,
...narrowingParams,
},
}),
runAnalyticsQuery<{ total_count: number }>({
query: buildUsersQuery(req.query, siteId, scope, true),
params: {
siteId,
...narrowingParams,
},
}),
]);
const totalCount = countData[0]?.total_count || 0;
// Enrich with traits from Postgres
const dataWithTraits = await enrichWithTraits(data, siteId);
return res.send({
data: dataWithTraits,
totalCount,
page: pageNum,
pageSize: pageSizeNum,
// True when more profiles matched the search than can be looked up at once.
searchLimited: search.limited,
});
}
);