Skip to content

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

8.5 KB
Raw Blame
import { FastifyReply, FastifyRequest } from "fastify";
import { z } from "zod";
import { getOrganizationSitesForCaller } from "../../lib/auth-utils.js";
import { OpenRouterError, callOpenRouterWithMetadata, getOpenRouterModel } from "../../lib/openrouter.js";
import { MAX_CUSTOM_QUERY_LENGTH, normalizeCustomQuery, validateScopedQuery } from "./utils/customQueryValidation.js";
import { EVENT_SCHEMA } from "./utils/eventSchema.js";

const OPENROUTER_TEMPERATURE = 0.1;
const OPENROUTER_MAX_TOKENS = 5000;

const generationMessageSchema = z.object({
  role: z.enum(["user", "assistant"]),
  content: z.string().trim().min(1).max(MAX_CUSTOM_QUERY_LENGTH),
});

const requestBodySchema = z.object({
  prompt: z.string().trim().min(1).max(4000),
  currentSiteId: z.number().int().positive().optional(),
  currentQuery: z.string().trim().max(MAX_CUSTOM_QUERY_LENGTH).optional(),
  history: z.array(generationMessageSchema).max(12).optional().default([]),
});

function extractSql(content: string) {
  const trimmed = content.trim();
  const fencedSql = trimmed.match(/```(?:sql)?\s*([\s\S]*?)```/i);
  const sql = fencedSql?.[1] ?? trimmed;
  return normalizeCustomQuery(sql.replace(/^sql\s*:/i, ""));
}

function isAbortError(error: unknown) {
  if (!(error instanceof Error)) return false;
  return error.name === "AbortError" || error.message === "This operation was aborted";
}

function createRequestAbortSignal(request: FastifyRequest, reply: FastifyReply) {
  const abortController = new AbortController();
  const abort = () => {
    if (!reply.raw.writableEnded) {
      abortController.abort();
    }
  };

  request.raw.on("aborted", abort);
  reply.raw.on("close", abort);

  return {
    signal: abortController.signal,
    cleanup: () => {
      request.raw.off("aborted", abort);
      reply.raw.off("close", abort);
    },
  };
}

export async function generateCustomQuery(
  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" });
  }

  // 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 siteIds = userSites
    .filter(site => site.organizationId === request.params.organizationId)
    .map(site => site.siteId);

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

  if (body.data.currentSiteId && !siteIds.includes(body.data.currentSiteId)) {
    return reply.status(403).send({ error: "No access to current site" });
  }

  const currentSiteInstruction = body.data.currentSiteId
    ? `The user is currently viewing site_id ${body.data.currentSiteId}. If they say "this site" or do not ask for an organization-wide result, include WHERE site_id = ${body.data.currentSiteId}.`
    : "The query can summarize all accessible sites unless the prompt asks for a specific site_id.";
  const currentQuery = body.data.currentQuery?.trim();
  const previousMessages = body.data.history.slice(-10).map(message => ({
    role: message.role,
    content:
      message.role === "assistant"
        ? `Previously generated SQL:\n${message.content}`
        : `Previous user request:\n${message.content}`,
  }));
  const messages = [
    {
      role: "system" as const,
      content: `
You generate ClickHouse SQL for Rybbit custom analytics.
Return exactly one SQL query and no Markdown, explanation, comments, or semicolon.
The query must be a SELECT or WITH ... SELECT query.
The only readable table is scoped_events. Never read from events or any other table.
Never define or shadow scoped_events.
Use ClickHouse syntax.
Use LIMIT 1000 or smaller for detail/list queries.
For custom event properties, use JSONExtractString(toString(props), 'property_name').
Use the previous messages and current editor query as context.
If the user asks an incremental follow-up, revise the current editor query.
If the user clearly asks for a new query, a different analysis, or to start over, generate a fresh query.
If the current editor query is empty, generate a fresh query.
${currentSiteInstruction}
${EVENT_SCHEMA}

Good examples:
SELECT pathname, countIf(type = 'pageview') AS pageviews FROM scoped_events GROUP BY pathname ORDER BY pageviews DESC LIMIT 100
SELECT event_name, count() AS events FROM scoped_events WHERE type = 'custom_event' GROUP BY event_name ORDER BY events DESC LIMIT 100
SELECT toStartOfDay(timestamp) AS day, count() AS events FROM scoped_events GROUP BY day ORDER BY day ASC LIMIT 1000
          `.trim(),
    },
    ...previousMessages,
    {
      role: "user" as const,
      content: `
Current editor query:
${currentQuery || "(empty)"}

Current user request:
${body.data.prompt}
          `.trim(),
    },
  ];
  const generationContext = {
    organizationId: request.params.organizationId,
    currentSiteId: body.data.currentSiteId,
    accessibleSiteCount: siteIds.length,
    promptLength: body.data.prompt.length,
    currentQueryLength: currentQuery?.length ?? 0,
    historyCount: body.data.history.length,
    historyRoles: body.data.history.map(message => message.role),
    historyContentLengths: body.data.history.map(message => message.content.length),
    messageCount: messages.length,
    messageCharCount: messages.reduce((total, message) => total + message.content.length, 0),
    openRouterModel: getOpenRouterModel(),
    openRouterTemperature: OPENROUTER_TEMPERATURE,
    openRouterMaxTokens: OPENROUTER_MAX_TOKENS,
  };
  const requestAbort = createRequestAbortSignal(request, reply);

  try {
    request.log.info(generationContext, "Generating custom analytics query");

    const { content: generated, metadata: openRouter } = await callOpenRouterWithMetadata(messages, {
      temperature: OPENROUTER_TEMPERATURE,
      maxTokens: OPENROUTER_MAX_TOKENS,
      signal: requestAbort.signal,
    });

    request.log.info(
      {
        ...generationContext,
        openRouter,
        generatedLength: generated.length,
      },
      "OpenRouter returned custom analytics query candidate"
    );

    const query = extractSql(generated);
    const validationError = validateScopedQuery(query);
    if (validationError) {
      request.log.warn(
        {
          ...generationContext,
          openRouter,
          validationError,
          queryLength: query.length,
        },
        "Generated custom query failed validation"
      );
      return reply.status(422).send({ error: "Generated query failed validation", details: validationError });
    }

    request.log.info(
      {
        ...generationContext,
        openRouter,
        queryLength: query.length,
      },
      "Generated custom analytics query passed validation"
    );

    return reply.send({ query });
  } catch (error) {
    if (requestAbort.signal.aborted || isAbortError(error)) {
      request.log.info(generationContext, "Custom analytics query generation aborted");
      if (reply.raw.destroyed || reply.raw.writableEnded) {
        return reply;
      }
      return reply.status(499).send({ error: "Query generation aborted" });
    }

    request.log.error(
      {
        err: error,
        ...generationContext,
        openRouter: error instanceof OpenRouterError ? error.details : undefined,
      },
      "Failed to generate custom analytics query"
    );

    if (error instanceof Error) {
      if (
        error instanceof OpenRouterError &&
        error.code === "empty_content" &&
        error.details.finishReason === "length"
      ) {
        return reply.status(502).send({
          error: "AI provider hit the output token limit before returning SQL. Try a shorter prompt or simpler query.",
        });
      }

      if (
        error.message === "No response from OpenRouter" ||
        error.message.startsWith("OpenRouter returned an empty response")
      ) {
        return reply.status(502).send({ error: "AI provider returned an empty response. Try again." });
      }

      if (error.message.startsWith("OpenRouter API error")) {
        return reply.status(502).send({ error: "AI query generation provider error" });
      }

      if (error.message === "OPENROUTER_API_KEY is not configured") {
        return reply.status(500).send({ error: "AI query generation is not configured" });
      }
    }

    return reply.status(500).send({ error: "Failed to generate query" });
  } finally {
    requestAbort.cleanup();
  }
}