🐸 Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import { FastifyReply, FastifyRequest } from "fastify";
import { db } from "../../../db/postgres/postgres.js";
import { goals } from "../../../db/postgres/schema.js";
import { asc, count, desc, eq } from "drizzle-orm";
import { getTimeStatement } from "../utils/timeWindow.js";
import SqlString from "sqlstring";
import { FilterParams } from "@rybbit/shared";
import { analyticsRoute, runAnalyticsQuery } from "../utils/analyticsQuery.js";
import { buildFilteredSessionsCTE } from "../utils/sessionFilters.js";
import { buildGoalCondition } from "./goalConditions.js";
// Types for the response
interface GoalWithConversions {
goalId: number;
name: string | null;
goalType: string;
config: any;
createdAt: string | null;
total_conversions: number;
total_sessions: number;
conversion_rate: number;
}
interface GetGoalsResponse {
data: GoalWithConversions[];
meta: {
total: number;
page: number;
pageSize: number;
totalPages: number;
};
}
interface GetGoalsRequest {
Params: {
siteId: string;
};
Querystring: FilterParams<{
page?: string;
page_size?: string;
sort?: string;
order?: "asc" | "desc";
}>;
}
export const buildGoalsTotalSessionsQuery = (query: GetGoalsRequest["Querystring"], siteId: number) => {
const timeStatement = getTimeStatement(query);
const filteredSessionsCTE = buildFilteredSessionsCTE(query.filters, siteId, timeStatement);
if (filteredSessionsCTE) {
return `
WITH ${filteredSessionsCTE}
SELECT COUNT(*) AS total_sessions
FROM FilteredSessions
`;
}
return `
SELECT COUNT(DISTINCT session_id) AS total_sessions
FROM events
WHERE site_id = ${SqlString.escape(siteId)}
${timeStatement}
`;
};
/**
* Returns null when none of the goals produces a valid conversion condition —
* the handler responds with zero-conversion goals in that case.
*/
export const buildGoalsConversionsQuery = (
query: GetGoalsRequest["Querystring"],
siteId: number,
siteGoals: (typeof goals.$inferSelect)[]
): string | null => {
const timeStatement = getTimeStatement(query);
const filteredSessionsCTE = buildFilteredSessionsCTE(query.filters, siteId, timeStatement);
// Build a single query that calculates all goal conversions at once using conditional aggregation
// This is more efficient than separate queries for each goal
let conditionalClauses: string[] = [];
for (const goal of siteGoals) {
const goalCondition = buildGoalCondition(goal);
if (!goalCondition) continue;
conditionalClauses.push(`
COUNT(DISTINCT IF(
${goalCondition},
session_id,
NULL
)) AS goal_${goal.goalId}_conversions
`);
}
if (conditionalClauses.length === 0) {
return null;
}
return `
${filteredSessionsCTE ? `WITH ${filteredSessionsCTE}` : ""}
SELECT
${conditionalClauses.join(", ")}
FROM events
${filteredSessionsCTE ? "INNER JOIN FilteredSessions USING (session_id)" : ""}
WHERE site_id = ${SqlString.escape(siteId)}
${timeStatement}
`;
};
export const getGoals = analyticsRoute<GetGoalsRequest>(
"goals data",
async (request: FastifyRequest<GetGoalsRequest>, reply: FastifyReply) => {
const { siteId } = request.params;
const { page = "1", page_size: pageSize = "10", sort = "createdAt", order = "desc" } = request.query;
const pageNumber = parseInt(page, 10);
const pageSizeNumber = parseInt(pageSize, 10);
// Validate page and pageSize
if (isNaN(pageNumber) || pageNumber < 1) {
return reply.status(400).send({ error: "Invalid page number" });
}
if (isNaN(pageSizeNumber) || pageSizeNumber < 1 || pageSizeNumber > 100) {
return reply.status(400).send({ error: "Invalid page size, must be between 1 and 100" });
}
// Count total goals for pagination metadata. Drizzle's count() maps the
// Postgres bigint to a number; a raw sql`count(*)` comes back as a string.
const totalGoalsResult = await db
.select({ count: count() })
.from(goals)
.where(eq(goals.siteId, Number(siteId)));
const totalGoals = Number(totalGoalsResult[0]?.count ?? 0);
const totalPages = Math.ceil(totalGoals / pageSizeNumber);
// If no goals exist, return early with empty data
if (totalGoals === 0) {
return reply.send({
data: [],
meta: {
total: 0,
page: pageNumber,
pageSize: pageSizeNumber,
totalPages: 0,
},
});
}
// Apply sorting
let orderBy;
// Only allow sorting by valid columns
const validSortColumns = ["goalId", "name", "goalType", "createdAt"];
const sortColumn = validSortColumns.includes(sort) ? sort : "createdAt";
if (order === "asc") {
if (sortColumn === "goalId") orderBy = asc(goals.goalId);
else if (sortColumn === "name") orderBy = asc(goals.name);
else if (sortColumn === "goalType") orderBy = asc(goals.goalType);
else orderBy = asc(goals.createdAt);
} else {
if (sortColumn === "goalId") orderBy = desc(goals.goalId);
else if (sortColumn === "name") orderBy = desc(goals.name);
else if (sortColumn === "goalType") orderBy = desc(goals.goalType);
else orderBy = desc(goals.createdAt);
}
// Fetch paginated goals for this site from PostgreSQL
const siteGoals = await db
.select()
.from(goals)
.where(eq(goals.siteId, Number(siteId)))
.orderBy(orderBy)
.limit(pageSizeNumber)
.offset((pageNumber - 1) * pageSizeNumber);
if (siteGoals.length === 0) {
// If no goals for this page, return empty data
return reply.send({
data: [],
meta: {
total: totalGoals,
page: pageNumber,
pageSize: pageSizeNumber,
totalPages,
},
});
}
// First, get the total number of unique sessions (denominator for conversion rate)
const totalSessionsData = await runAnalyticsQuery<{ total_sessions: number }>({
query: buildGoalsTotalSessionsQuery(request.query, Number(siteId)),
});
const totalSessions = totalSessionsData[0]?.total_sessions || 0;
const conversionQuery = buildGoalsConversionsQuery(request.query, Number(siteId), siteGoals);
if (conversionQuery === null) {
// If no valid goals to calculate, return the goals without conversion data
const goalsWithZeroConversions = siteGoals.map(goal => ({
...goal,
total_conversions: 0,
total_sessions: totalSessions,
conversion_rate: 0,
}));
return reply.send({
data: goalsWithZeroConversions,
meta: {
total: totalGoals,
page: pageNumber,
pageSize: pageSizeNumber,
totalPages,
},
});
}
// Execute the comprehensive query
const conversionData = await runAnalyticsQuery<Record<string, number>>({ query: conversionQuery });
// If we didn't get any results, use zeros
const conversions = conversionData[0] || {};
// Combine goals data with conversion metrics
const goalsWithConversions: GoalWithConversions[] = siteGoals.map(goal => {
const totalConversions = conversions[`goal_${goal.goalId}_conversions`] || 0;
const conversionRate = totalSessions > 0 ? totalConversions / totalSessions : 0;
return {
...goal,
total_conversions: totalConversions,
total_sessions: totalSessions,
conversion_rate: conversionRate,
};
});
return reply.send({
data: goalsWithConversions,
meta: {
total: totalGoals,
page: pageNumber,
pageSize: pageSizeNumber,
totalPages,
},
});
}
);