Skip to content

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

3.6 KB
Raw Blame
import { FastifyReply, FastifyRequest } from "fastify";
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 { getOrganizationSitesForCaller } from "../../lib/auth-utils.js";
import {
  MAX_CUSTOM_QUERY_LENGTH,
  normalizeCustomQuery,
  sanitizeClickhouseError,
  validateScopedQuery,
} from "./utils/customQueryValidation.js";

const requestBodySchema = z.object({
  query: z.string().trim().min(1).max(MAX_CUSTOM_QUERY_LENGTH),
  // When provided, scope scoped_events to this single site (the per-site Query
  // page). Omitted → query spans every site the caller can access in the org.
  siteId: z.number().int().positive().optional(),
});

export async function runCustomQuery(
  request: FastifyRequest<{
    Params: {
      organizationId: string;
    };
    Body: unknown;
  }>,
  reply: FastifyReply
) {
  const body = requestBodySchema.safeParse(request.body);
  if (!body.success) {
    return reply.status(400).send({ error: body.error.errors[0]?.message ?? "Invalid request body" });
  }

  const validationError = validateScopedQuery(body.data.query);
  if (validationError) {
    return reply.status(400).send({ error: validationError });
  }

  // Read fresh: raw event access must not outlive a site move or a revoked
  // grant by even the few seconds the per-worker cache holds.
  const userSites = await getOrganizationSitesForCaller(request, request.params.organizationId);
  const accessibleSiteIds = userSites
    .filter(site => site.organizationId === request.params.organizationId)
    .map(site => site.siteId);

  if (accessibleSiteIds.length === 0) {
    return reply.status(403).send({ error: "No access to organization or no sites found" });
  }

  let siteIds = accessibleSiteIds;
  if (body.data.siteId !== undefined) {
    if (!accessibleSiteIds.includes(body.data.siteId)) {
      return reply.status(403).send({ error: "No access to the requested site" });
    }
    siteIds = [body.data.siteId];
  }

  if (!(await isQueryUserIsolated())) {
    return reply.status(503).send({ error: QUERY_USER_UNAVAILABLE_ERROR });
  }

  const query = `
    WITH scoped_events AS (
      SELECT *
      FROM events
      PREWHERE site_id IN {siteIds:Array(UInt16)}
    )
    SELECT *
    FROM (
      ${normalizeCustomQuery(body.data.query)}
    )
    LIMIT {limit:UInt32}
  `;

  try {
    const result = await clickhouseQuery.query({
      query,
      format: "JSONEachRow",
      query_params: {
        siteIds,
        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(siteIds),
      // 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 custom analytics query");
    return reply.status(400).send({ error: sanitizeClickhouseError(error) });
  }
}