πΈ Rybbit - open-source and privacy-friendly alternative to Google Analytics that is 10x more intuitive.
import { FastifyReply, FastifyRequest } from "fastify";
import SqlString from "sqlstring";
import { z } from "zod";
import { clickhouseQuery } from "../../db/clickhouse/clickhouse.js";
import { QUERY_USER_LIMITS } from "../../db/clickhouse/queryLimits.js";
import { isQueryUserIsolated, QUERY_USER_UNAVAILABLE_ERROR, siteScopeHeaders } from "../../db/clickhouse/queryUser.js";
import {
MAX_CUSTOM_QUERY_LENGTH,
normalizeCustomQuery,
sanitizeClickhouseError,
validateScopedQuery,
} from "./utils/customQueryValidation.js";
import { validateHttpTimeParams } from "./utils/query-validation.js";
import { bucketIntervalMap, getTimeStatement } from "./utils/timeWindow.js";
const BUCKET_TOKEN = /\{\{\s*bucket\s*\}\}/gi;
const TZ_TOKEN = /\{\{\s*tz\s*\}\}/gi;
// Only the two fields the time window doesn't own. The bounds are checked by
// validateHttpTimeParams below rather than re-described here: a second schema
// that merely looked similar would accept an unpaired, reversed or impossible
// bound (`25:00:00` matches the format), which the window then drops β silently
// widening the card to all time.
const requestBodySchema = z.object({
query: z.string().trim().min(1).max(MAX_CUSTOM_QUERY_LENGTH),
startDate: z.string().optional(),
endDate: z.string().optional(),
timeZone: z.string().optional(),
startDateTime: z.string().optional(),
endDateTime: z.string().optional(),
pastMinutesStart: z.number().optional(),
pastMinutesEnd: z.number().optional(),
bucket: z
.enum(["minute", "five_minutes", "ten_minutes", "fifteen_minutes", "hour", "day", "week", "month", "year"])
.optional(),
});
export async function runDashboardCardQuery(
request: FastifyRequest<{
Params: {
siteId: string;
};
Body: unknown;
}>,
reply: FastifyReply
) {
const siteId = parseInt(request.params.siteId, 10);
if (isNaN(siteId) || siteId <= 0) {
return reply.status(400).send({ error: "Invalid site ID" });
}
const body = requestBodySchema.safeParse(request.body);
if (!body.success) {
return reply.status(400).send({ error: body.error.errors[0]?.message ?? "Invalid request body" });
}
// The bounds arrive in the body, so validateTimeParams β a query-param
// preHandler β never sees them. Run the same validator the routes run, on the
// same param names, so this endpoint rejects exactly what they reject.
const timeParams = {
start_date: body.data.startDate,
end_date: body.data.endDate,
time_zone: body.data.timeZone,
start_datetime: body.data.startDateTime,
end_datetime: body.data.endDateTime,
past_minutes_start: body.data.pastMinutesStart,
past_minutes_end: body.data.pastMinutesEnd,
};
const timeError = validateHttpTimeParams(timeParams);
if (timeError) {
return reply.status(400).send({ error: timeError });
}
// Substitute {{bucket}} and {{tz}} BEFORE validation so the validator never
// sees the template tokens. {{bucket}} comes from an allowlisted enum mapped
// to a constant interval string; {{tz}} is SqlString-escaped to a quoted
// literal β both injection-safe. {{tz}} lets time-series cards bucket in the
// viewer's timezone (e.g. toStartOfInterval(toTimeZone(timestamp, {{tz}}), ...))
// so day buckets align to local calendar days, matching the standard charts.
const bucketInterval = bucketIntervalMap[body.data.bucket ?? "hour"];
const timeZoneLiteral = SqlString.escape(body.data.timeZone || "UTC");
const substitutedQuery = body.data.query.replace(BUCKET_TOKEN, bucketInterval).replace(TZ_TOKEN, timeZoneLiteral);
const validationError = validateScopedQuery(substitutedQuery);
if (validationError) {
return reply.status(400).send({ error: validationError });
}
if (!(await isQueryUserIsolated())) {
return reply.status(503).send({ error: QUERY_USER_UNAVAILABLE_ERROR });
}
// Auto-scope the timestamp to the global time range. getTimeStatement returns
// an "AND timestamp >= ... AND timestamp < ..." fragment (or "" for all-time),
// with all values Zod-sanitized and SqlString-escaped internally.
const timeStatement = getTimeStatement(timeParams);
const query = `
WITH scoped_events AS (
SELECT *
FROM events
PREWHERE site_id IN {siteIds:Array(UInt16)}
WHERE 1=1 ${timeStatement}
)
SELECT *
FROM (
${normalizeCustomQuery(substitutedQuery)}
)
LIMIT {limit:UInt32}
`;
try {
const result = await clickhouseQuery.query({
query,
format: "JSONEachRow",
query_params: {
siteIds: [siteId],
limit: QUERY_USER_LIMITS.maxResultRows,
},
// The rybbit_query row policy only admits rows from these sites, so the
// scoped_events CTE isn't the only thing keeping the query in tenant.
http_headers: siteScopeHeaders([siteId]),
// Execution limits (readonly, max_execution_time, max_memory_usage,
// max_result_rows, β¦) come from the rybbit_query settings profile and are
// pinned there with constraints; sending them here would be rejected.
});
const data = await result.json<Record<string, unknown>>();
return reply.send({
data,
meta: {
queryId: result.query_id,
rowCount: data.length,
maxExecutionTimeSeconds: QUERY_USER_LIMITS.maxExecutionTimeSeconds,
maxRows: QUERY_USER_LIMITS.maxResultRows,
},
});
} catch (error) {
request.log.error(error, "Failed to run dashboard card query");
return reply.status(400).send({ error: sanitizeClickhouseError(error) });
}
}