stack-auth · BilalG1 · Feb 3, 2026 · Feb 2, 2026 · Feb 2, 2026 · Feb 2, 2026
diff --git a/apps/backend/src/app/api/latest/internal/analytics/query/route.ts b/apps/backend/src/app/api/latest/internal/analytics/query/route.ts
@@ -1,4 +1,4 @@
-import { getClickhouseExternalClient, getQueryTimingStats, isClickhouseConfigured } from "@/lib/clickhouse";
+import { getClickhouseExternalClient, isClickhouseConfigured } from "@/lib/clickhouse";
 import { createSmartRouteHandler } from "@/route-handlers/smart-route-handler";
 import { KnownErrors } from "@stackframe/stack-shared";
 import { adaptSchema, adminAuthTypeSchema, jsonSchema, yupBoolean, yupMixed, yupNumber, yupObject, yupRecord, yupString } from "@stackframe/stack-shared/dist/schema-fields";
@@ -7,6 +7,9 @@ import { captureError, StackAssertionError } from "@stackframe/stack-shared/dist
 import { Result } from "@stackframe/stack-shared/dist/utils/results";
 import { randomUUID } from "crypto";
 
+const MAX_QUERY_TIMEOUT_MS = 120_000;
+const DEFAULT_QUERY_TIMEOUT_MS = 10_000;
+
 export const POST = createSmartRouteHandler({
   metadata: { hidden: true },
   request: yupObject({
@@ -18,18 +21,15 @@ export const POST = createSmartRouteHandler({
       include_all_branches: yupBoolean().default(false),
       query: yupString().defined().nonEmpty(),
       params: yupRecord(yupString().defined(), yupMixed().defined()).default({}),
-      timeout_ms: yupNumber().integer().min(1_000).default(10_000),
+      timeout_ms: yupNumber().integer().min(1_000).max(MAX_QUERY_TIMEOUT_MS).default(DEFAULT_QUERY_TIMEOUT_MS),
     }).defined(),
   }),
   response: yupObject({
     statusCode: yupNumber().oneOf([200]).defined(),
     bodyType: yupString().oneOf(["json"]).defined(),
     body: yupObject({
       result: jsonSchema.defined(),
-      stats: yupObject({
-        cpu_time: yupNumber().defined(),
-        wall_clock_time: yupNumber().defined(),
-      }).defined(),
+      query_id: yupString().defined(),
     }).defined(),
   }),
   async handler({ body, auth }) {
@@ -40,7 +40,7 @@ export const POST = createSmartRouteHandler({
       throw new StackAssertionError("ClickHouse is not configured");
     }
     const client = getClickhouseExternalClient();
-    const queryId = randomUUID();
+    const queryId = `${auth.tenancy.project.id}:${auth.tenancy.branchId}:${randomUUID()}`;
     const resultSet = await Result.fromPromise(client.query({
       query: body.query,
       query_id: queryId,
@@ -64,17 +64,12 @@ export const POST = createSmartRouteHandler({
     }
 
     const rows = await resultSet.data.json<Record<string, unknown>[]>();
-    const stats = await getQueryTimingStats(client, queryId);
-
     return {
       statusCode: 200,
       bodyType: "json",
       body: {
         result: rows,
-        stats: {
-          cpu_time: stats.cpu_time_ms,
-          wall_clock_time: stats.wall_clock_time_ms,
-        },
+        query_id: queryId,
       },
     };
   },

diff --git a/apps/backend/src/app/api/latest/internal/analytics/query/timing/route.ts b/apps/backend/src/app/api/latest/internal/analytics/query/timing/route.ts
@@ -0,0 +1,56 @@
+import { getClickhouseExternalClient, getQueryTimingStatsForProject, isClickhouseConfigured } from "@/lib/clickhouse";
+import { createSmartRouteHandler } from "@/route-handlers/smart-route-handler";
+import { KnownErrors } from "@stackframe/stack-shared";
+import { adaptSchema, serverOrHigherAuthTypeSchema, yupNumber, yupObject, yupString } from "@stackframe/stack-shared/dist/schema-fields";
+import { StackAssertionError } from "@stackframe/stack-shared/dist/utils/errors";
+
+export const POST = createSmartRouteHandler({
+  metadata: { hidden: true },
+  request: yupObject({
+    auth: yupObject({
+      type: serverOrHigherAuthTypeSchema,
+      tenancy: adaptSchema,
+    }).defined(),
+    body: yupObject({
+      query_id: yupString().defined().nonEmpty(),
+    }).defined(),
+  }),
+  response: yupObject({
+    statusCode: yupNumber().oneOf([200]).defined(),
+    bodyType: yupString().oneOf(["json"]).defined(),
+    body: yupObject({
+      stats: yupObject({
+        cpu_time: yupNumber().defined(),
+        wall_clock_time: yupNumber().defined(),
+      }).defined(),
+    }).defined(),
+  }),
+  async handler({ body, auth }) {
+    if (!isClickhouseConfigured()) {
+      throw new StackAssertionError("ClickHouse is not configured");
+    }
+
+    const expectedPrefix = `${auth.tenancy.project.id}:${auth.tenancy.branchId}:`;
+    if (!body.query_id.startsWith(expectedPrefix)) {
+      throw new KnownErrors.ItemNotFound(body.query_id);
+    }
+
+    const client = getClickhouseExternalClient();
+    const stats = await getQueryTimingStatsForProject(client, body.query_id);
+
+    if (!stats) {
+      throw new KnownErrors.ItemNotFound(body.query_id);
+    }
+
+    return {
+      statusCode: 200,
+      bodyType: "json",
+      body: {
+        stats: {
+          cpu_time: stats.cpu_time_ms,
+          wall_clock_time: stats.wall_clock_time_ms,
+        },
+      },
+    };
+  },
+});
diff --git a/apps/backend/src/lib/clickhouse.tsx b/apps/backend/src/lib/clickhouse.tsx
@@ -89,3 +89,52 @@ export const getQueryTimingStats = async (client: ClickHouseClient, queryId: str
 
   throw new StackAssertionError("Unexpected number of query log results: 0", { data: [] });
 };
+
+export const getQueryTimingStatsForProject = async (
+  client: ClickHouseClient,
+  queryId: string,
+) => {
+  const queryProfile = async () => {
+    const profile = await client.query({
+      query: `
+      SELECT
+        ProfileEvents['CPUTimeMicroseconds'] / 1000 AS cpu_time_ms,
+        ProfileEvents['RealTimeMicroseconds'] / 1000 AS wall_clock_time_ms
+      FROM system.query_log
+      WHERE query_id = {query_id:String}
+        AND type = 'QueryFinish'
+      ORDER BY event_time DESC
+      LIMIT 1
+    `,
+      query_params: {
+        query_id: queryId,
+      },
+      auth: {
+        username: clickhouseAdminUser,
+        password: clickhouseAdminPassword,
+      },
+      format: "JSON",
+    });
+
+    return await profile.json<{
+      cpu_time_ms: number,
+      wall_clock_time_ms: number,
+    }>();
+  };
+
+  const retryDelaysMs = [75, 150, 300, 600, 1200, 2400, 4800];
+  for (let attempt = 0; attempt <= retryDelaysMs.length; attempt += 1) {
+    const stats = await queryProfile();
+    if (stats.data.length === 1) {
+      return stats.data[0];
+    }
+    if (stats.data.length > 1) {
+      throw new StackAssertionError(`Unexpected number of query log results: ${stats.data.length}`, { data: stats.data });
+    }
+    if (attempt < retryDelaysMs.length) {
+      await new Promise((resolve) => setTimeout(resolve, retryDelaysMs[attempt]));
+    }
+  }
+
+  return null;
+};
diff --git a/apps/e2e/tests/backend/endpoints/api/v1/analytics-events.test.ts b/apps/e2e/tests/backend/endpoints/api/v1/analytics-events.test.ts
@@ -2,6 +2,18 @@ import { wait } from "@stackframe/stack-shared/dist/utils/promises";
 import { it } from "../../../../helpers";
 import { Auth, Project, backendContext, bumpEmailAddress, niceBackendFetch } from "../../../backend-helpers";
 
+type ExpectLike = ((value: unknown) => { toEqual: (value: unknown) => void }) & {
+  any: (constructor: unknown) => unknown,
+};
+
+const stripQueryId = <T extends { status: number, body?: Record<string, unknown> | null }>(response: T, expect: ExpectLike) => {
+  if (response.status === 200 && response.body) {
+    expect(response.body.query_id).toEqual(expect.any(String));
+    delete response.body.query_id;
+  }
+  return response;
+};
+
 const queryEvents = async (params: {
   userId?: string,
   eventType?: string,
@@ -82,7 +94,7 @@ it("cannot read events from other projects", async ({ expect }) => {
     userId: projectBUserId,
     eventType: "$token-refresh",
   });
-  expect(projectBResponse).toMatchInlineSnapshot(`
+  expect(stripQueryId(projectBResponse, expect)).toMatchInlineSnapshot(`
     NiceResponse {
       "status": 200,
       "body": {
@@ -95,10 +107,6 @@ it("cannot read events from other projects", async ({ expect }) => {
             "user_id": "<stripped UUID>",
           },
         ],
-        "stats": {
-          "cpu_time": <stripped field 'cpu_time'>,
-          "wall_clock_time": <stripped field 'wall_clock_time'>,
-        },
       },
       "headers": Headers { <some fields may have been hidden> },
     }
@@ -112,16 +120,10 @@ it("cannot read events from other projects", async ({ expect }) => {
     userId: projectBUserId,
     eventType: "$token-refresh",
   });
-  expect(queryResponse).toMatchInlineSnapshot(`
+  expect(stripQueryId(queryResponse, expect)).toMatchInlineSnapshot(`
     NiceResponse {
       "status": 200,
-      "body": {
-        "result": [],
-        "stats": {
-          "cpu_time": <stripped field 'cpu_time'>,
-          "wall_clock_time": <stripped field 'wall_clock_time'>,
-        },
-      },
+      "body": { "result": [] },
       "headers": Headers { <some fields may have been hidden> },
     }
   `);