🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import { FastifyReply, FastifyRequest } from "fastify";
import { SESSION_CHANNEL_AGG, SESSION_REFERRER_AGG } from "../utils/sessionAttribution.js";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { getSessionGoalMatcher, resolveSessionGoals, SessionGoal } from "./sessionGoals.js";
export interface SessionDetails {
session_id: string;
user_id: string;
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;
session_end: string;
session_start: string;
session_duration: number;
pageviews: number;
events: number;
entry_page: string;
exit_page: string;
ip: string;
}
export interface Event {
timestamp: string;
pathname: string;
hostname: string;
querystring: string;
page_title: string;
referrer: string;
type: string;
event_name?: string;
properties?: string;
// Goals this event completed. Empty for most events.
goals: SessionGoal[];
}
export interface SessionPageviewsAndEvents {
session: SessionDetails;
events: Event[];
pagination: {
total: number;
limit: number;
offset: number;
hasMore: boolean;
};
}
export interface GetSessionRequest {
Params: {
sessionId: string;
siteId: string;
};
Querystring: {
limit?: string;
offset?: string;
minutes?: string;
};
}
// Array(UInt32) of the goal ids an event completes; see buildSessionGoalMatcher.
const NO_GOALS_EXPRESSION = "emptyArrayUInt32()";
export const buildSessionQueries = (query: GetSessionRequest["Querystring"], goalExpression = NO_GOALS_EXPRESSION) => {
const minutes = query.minutes ? parseInt(query.minutes) : undefined;
// Add time filter if minutes is provided
const timeFilter = minutes ? `timestamp > now() - interval ${minutes} minute` : "";
// Add the WHERE clause connector if timeFilter exists
const timeFilterWithConnector = timeFilter ? `AND ${timeFilter}` : "";
// 1. First query: Get session data derived from events
const sessionQuery = `
SELECT
session_id,
any(user_id) as 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_width) as screen_width,
any(screen_height) as screen_height,
${SESSION_REFERRER_AGG} as referrer,
${SESSION_CHANNEL_AGG} as channel,
min(timestamp) as session_start,
max(timestamp) as session_end,
dateDiff('second', min(timestamp), max(timestamp)) as session_duration,
countIf(type = 'pageview') as pageviews,
count() as events,
argMinIf(pathname, timestamp_ms, type = 'pageview') as entry_page,
argMaxIf(pathname, timestamp_ms, type = 'pageview') as exit_page,
any(ip) AS ip
FROM events
WHERE
site_id = {siteId:Int32}
AND session_id = {sessionId:String}
${timeFilterWithConnector}
GROUP BY session_id
LIMIT 1
`;
// 2. Query to get total count of pageviews
const countQuery = `
SELECT
COUNT(*) as total
FROM events
WHERE
site_id = {siteId:Int32}
AND session_id = {sessionId:String}
AND type != 'performance'
${timeFilterWithConnector}
`;
// 3. Query to get paginated pageviews
const eventsQuery = `
SELECT
timestamp_ms AS timestamp,
pathname,
hostname,
querystring,
page_title,
referrer,
type,
event_name,
props,
${goalExpression} AS goal_ids
FROM events
WHERE
site_id = {siteId:Int32}
AND session_id = {sessionId:String}
AND type != 'performance'
${timeFilterWithConnector}
ORDER BY timestamp_ms ASC
LIMIT {limit:Int32}
OFFSET {offset:Int32}
`;
return { sessionQuery, countQuery, eventsQuery };
};
export const getSession = analyticsRoute<GetSessionRequest>(
"session data",
async (req: FastifyRequest<GetSessionRequest>, res: FastifyReply) => {
const { sessionId, siteId } = req.params;
const limit = req.query.limit ? parseInt(req.query.limit) : 100;
const offset = req.query.offset ? parseInt(req.query.offset) : 0;
// The same conditions the Goals page counts with, so an event marked here
// is one that page counted as a conversion.
const matcher = await getSessionGoalMatcher(Number(siteId), req);
const { sessionQuery, countQuery, eventsQuery } = buildSessionQueries(req.query, matcher.expression);
// Execute queries in parallel
const [sessionData, countData, eventsData] = await Promise.all([
runAnalyticsQuery<SessionDetails>({
query: sessionQuery,
params: {
siteId: Number(siteId),
sessionId,
},
}),
runAnalyticsQuery<{ total: number }>({
query: countQuery,
params: {
siteId: Number(siteId),
sessionId,
},
}),
runAnalyticsQuery<Omit<Event, "goals"> & { goal_ids: number[] }>({
query: eventsQuery,
params: {
siteId: Number(siteId),
sessionId,
limit,
offset,
},
}),
]);
if (!sessionData || sessionData.length === 0) {
return res.status(404).send({ error: "Session not found" });
}
// Combine results
const response: SessionPageviewsAndEvents = {
session: sessionData[0],
events: eventsData.map(({ goal_ids, ...event }) => ({
...event,
goals: resolveSessionGoals(matcher, [], goal_ids ?? []),
})),
pagination: {
total: countData[0].total,
limit,
offset,
hasMore: offset + eventsData.length < countData[0].total,
},
};
return res.send({ data: response });
}
);