stack-auth · madster456 · Mar 27, 2026 · Mar 27, 2026 · Mar 27, 2026 · Mar 27, 2026
diff --git a/apps/backend/src/app/api/latest/internal/session-replays/[session_replay_id]/route.tsx b/apps/backend/src/app/api/latest/internal/session-replays/[session_replay_id]/route.tsx
@@ -0,0 +1,65 @@
+import { Prisma } from "@/generated/prisma/client";
+import { getPrismaClientForTenancy, getPrismaSchemaForTenancy } from "@/prisma-client";
+import { createSmartRouteHandler } from "@/route-handlers/smart-route-handler";
+import { KnownErrors } from "@stackframe/stack-shared";
+import { adaptSchema, adminAuthTypeSchema, yupNumber, yupObject, yupString } from "@stackframe/stack-shared/dist/schema-fields";
+import {
+  aggregateSessionReplayChunksByReplayIds,
+  querySessionReplayAdminRows,
+  sessionReplayAdminRowToApiItem,
+} from "../session-replay-admin-rows";
+
+export const GET = createSmartRouteHandler({
+  metadata: { hidden: true },
+  request: yupObject({
+    auth: yupObject({
+      type: adminAuthTypeSchema.defined(),
+      tenancy: adaptSchema.defined(),
+    }).defined(),
+    params: yupObject({
+      session_replay_id: yupString().defined(),
+    }).defined(),
+  }),
+  response: yupObject({
+    statusCode: yupNumber().oneOf([200]).defined(),
+    bodyType: yupString().oneOf(["json"]).defined(),
+    body: yupObject({
+      id: yupString().defined(),
+      project_user: yupObject({
+        id: yupString().defined(),
+        display_name: yupString().nullable().defined(),
+        primary_email: yupString().nullable().defined(),
+      }).defined(),
+      started_at_millis: yupNumber().defined(),
+      last_event_at_millis: yupNumber().defined(),
+      chunk_count: yupNumber().defined(),
+      event_count: yupNumber().defined(),
+    }).defined(),
+  }),
+  async handler({ auth, params }) {
+    const prisma = await getPrismaClientForTenancy(auth.tenancy);
+    const schema = await getPrismaSchemaForTenancy(auth.tenancy);
+    const sessionReplayId = params.session_replay_id;
+
+    const rows = await querySessionReplayAdminRows({
+      prisma,
+      schema,
+      tenancyId: auth.tenancy.id,
+      suffixSql: Prisma.sql`AND sr."id" = ${sessionReplayId} LIMIT 1`,
+    });
+
+    const row = rows.at(0);
+    if (row == null) {
+      throw new KnownErrors.ItemNotFound(sessionReplayId);
+    }
+
+    const aggById = await aggregateSessionReplayChunksByReplayIds(prisma, auth.tenancy.id, [sessionReplayId]);
+    const agg = aggById.get(sessionReplayId) ?? { chunkCount: 0, eventCount: 0 };
+
+    return {
+      statusCode: 200,
+      bodyType: "json",
+      body: sessionReplayAdminRowToApiItem(row, agg),
+    };
+  },
+});
diff --git a/apps/backend/src/app/api/latest/internal/session-replays/route.tsx b/apps/backend/src/app/api/latest/internal/session-replays/route.tsx
@@ -1,6 +1,11 @@
-import { getClickhouseExternalClient } from "@/lib/clickhouse";
 import { Prisma } from "@/generated/prisma/client";
+import { getClickhouseExternalClient } from "@/lib/clickhouse";
 import { getPrismaClientForTenancy, getPrismaSchemaForTenancy, sqlQuoteIdent } from "@/prisma-client";
+import {
+  aggregateSessionReplayChunksByReplayIds,
+  querySessionReplayAdminRows,
+  sessionReplayAdminRowToApiItem,
+} from "./session-replay-admin-rows";
 import { createSmartRouteHandler } from "@/route-handlers/smart-route-handler";
 import { KnownErrors } from "@stackframe/stack-shared";
 import { adaptSchema, adminAuthTypeSchema, yupArray, yupNumber, yupObject, yupString } from "@stackframe/stack-shared/dist/schema-fields";
@@ -171,36 +176,7 @@ export const GET = createSmartRouteHandler({
       }
     }
 
-    type ReplayRow = {
-      id: string,
-      projectUserId: string,
-      startedAt: Date,
-      lastEventAt: Date,
-      projectUserDisplayName: string | null,
-      primaryEmail: string | null,
-    };
-
-    const rows = await prisma.$queryRaw<ReplayRow[]>`
-      SELECT
-        sr."id",
-        sr."projectUserId",
-        sr."startedAt",
-        sr."lastEventAt",
-        pu."displayName" AS "projectUserDisplayName",
-        (
-          SELECT cc."value"
-          FROM ${sqlQuoteIdent(schema)}."ContactChannel" cc
-          WHERE cc."projectUserId" = sr."projectUserId"
-            AND cc."tenancyId" = sr."tenancyId"
-            AND cc."type" = 'EMAIL'
-            AND cc."isPrimary" = 'TRUE'::"BooleanTrue"
-          LIMIT 1
-        ) AS "primaryEmail"
-      FROM ${sqlQuoteIdent(schema)}."SessionReplay" sr
-      JOIN ${sqlQuoteIdent(schema)}."ProjectUser" pu
-        ON pu."projectUserId" = sr."projectUserId"
-        AND pu."tenancyId" = sr."tenancyId"
-      WHERE sr."tenancyId" = ${auth.tenancy.id}::UUID
+    const suffixSql = Prisma.sql`
         ${userIdsFilter.length > 0 ? Prisma.sql`AND sr."projectUserId" IN (${Prisma.join(userIdsFilter)})` : Prisma.empty}
         ${lastEventAtFrom ? Prisma.sql`AND sr."lastEventAt" >= ${lastEventAtFrom}` : Prisma.empty}
         ${lastEventAtTo ? Prisma.sql`AND sr."lastEventAt" <= ${lastEventAtTo}` : Prisma.empty}
@@ -221,46 +197,27 @@ export const GET = createSmartRouteHandler({
       LIMIT ${limit + 1}
     `;
 
+    const rows = await querySessionReplayAdminRows({
+      prisma,
+      schema,
+      tenancyId: auth.tenancy.id,
+      suffixSql,
+    });
+
     const hasMore = rows.length > limit;
     const page = hasMore ? rows.slice(0, limit) : rows;
     const nextCursor = hasMore ? page[page.length - 1]!.id : null;
 
     const sessionIds = page.map((row) => row.id);
-    const chunkAggs = sessionIds.length
-      ? await prisma.sessionReplayChunk.groupBy({
-        by: ["sessionReplayId"],
-        where: { tenancyId: auth.tenancy.id, sessionReplayId: { in: sessionIds } },
-        _count: { _all: true },
-        _sum: { eventCount: true },
-      })
-      : [];
-
-    const aggBySessionId = new Map<string, { chunkCount: number, eventCount: number }>();
-    for (const a of chunkAggs) {
-      aggBySessionId.set(a.sessionReplayId, {
-        chunkCount: a._count._all,
-        eventCount: a._sum.eventCount ?? 0,
-      });
-    }
+    const aggBySessionId = await aggregateSessionReplayChunksByReplayIds(prisma, auth.tenancy.id, sessionIds);
 
     return {
       statusCode: 200,
       bodyType: "json",
       body: {
         items: page.map((row) => {
           const agg = aggBySessionId.get(row.id) ?? { chunkCount: 0, eventCount: 0 };
-          return {
-            id: row.id,
-            project_user: {
-              id: row.projectUserId,
-              display_name: row.projectUserDisplayName ?? null,
-              primary_email: row.primaryEmail ?? null,
-            },
-            started_at_millis: row.startedAt.getTime(),
-            last_event_at_millis: row.lastEventAt.getTime(),
-            chunk_count: agg.chunkCount,
-            event_count: agg.eventCount,
-          };
+          return sessionReplayAdminRowToApiItem(row, agg);
         }),
         pagination: { next_cursor: nextCursor },
       },

diff --git a/apps/backend/src/app/api/latest/internal/session-replays/session-replay-admin-rows.ts b/apps/backend/src/app/api/latest/internal/session-replays/session-replay-admin-rows.ts
@@ -0,0 +1,92 @@
+import { Prisma, PrismaClient } from "@/generated/prisma/client";
+import { type PrismaClientWithReplica, sqlQuoteIdent } from "@/prisma-client";
+
+/** Row shape from the admin session replay list / get SQL (SessionReplay + ProjectUser + primary email). */
+export type SessionReplayAdminListRow = {
+  id: string,
+  projectUserId: string,
+  startedAt: Date,
+  lastEventAt: Date,
+  projectUserDisplayName: string | null,
+  primaryEmail: string | null,
+};
+
+export type SessionReplayChunkAgg = { chunkCount: number, eventCount: number };
+
+/**
+ * Base query used by the internal session replay list and single-replay routes.
+ * `suffixSql` is everything after `WHERE sr."tenancyId" = …` (filters, ORDER BY, LIMIT).
+ */
+export async function querySessionReplayAdminRows(options: {
+  prisma: PrismaClientWithReplica<PrismaClient>,
+  schema: string,
+  tenancyId: string,
+  suffixSql: Prisma.Sql,
+}): Promise<SessionReplayAdminListRow[]> {
+  const { prisma, schema, tenancyId, suffixSql } = options;
+  return await prisma.$queryRaw<SessionReplayAdminListRow[]>`
+    SELECT
+      sr."id",
+      sr."projectUserId",
+      sr."startedAt",
+      sr."lastEventAt",
+      pu."displayName" AS "projectUserDisplayName",
+      (
+        SELECT cc."value"
+        FROM ${sqlQuoteIdent(schema)}."ContactChannel" cc
+        WHERE cc."projectUserId" = sr."projectUserId"
+          AND cc."tenancyId" = sr."tenancyId"
+          AND cc."type" = 'EMAIL'
+          AND cc."isPrimary" = 'TRUE'::"BooleanTrue"
+        LIMIT 1
+      ) AS "primaryEmail"
+    FROM ${sqlQuoteIdent(schema)}."SessionReplay" sr
+    JOIN ${sqlQuoteIdent(schema)}."ProjectUser" pu
+      ON pu."projectUserId" = sr."projectUserId"
+      AND pu."tenancyId" = sr."tenancyId"
+    WHERE sr."tenancyId" = ${tenancyId}::UUID
+      ${suffixSql}
+  `;
+}
+
+export async function aggregateSessionReplayChunksByReplayIds(
+  prisma: PrismaClientWithReplica<PrismaClient>,
+  tenancyId: string,
+  sessionReplayIds: string[],
+): Promise<Map<string, SessionReplayChunkAgg>> {
+  if (sessionReplayIds.length === 0) {
+    return new Map();
+  }
+  const chunkAggs = await prisma.sessionReplayChunk.groupBy({
+    by: ["sessionReplayId"],
+    where: { tenancyId, sessionReplayId: { in: sessionReplayIds } },
+    _count: { _all: true },
+    _sum: { eventCount: true },
+  });
+  const map = new Map<string, SessionReplayChunkAgg>();
+  for (const a of chunkAggs) {
+    map.set(a.sessionReplayId, {
+      chunkCount: a._count._all,
+      eventCount: a._sum.eventCount ?? 0,
+    });
+  }
+  return map;
+}
+
+export function sessionReplayAdminRowToApiItem(
+  row: SessionReplayAdminListRow,
+  agg: SessionReplayChunkAgg,
+) {
+  return {
+    id: row.id,
+    project_user: {
+      id: row.projectUserId,
+      display_name: row.projectUserDisplayName ?? null,
+      primary_email: row.primaryEmail ?? null,
+    },
+    started_at_millis: row.startedAt.getTime(),
+    last_event_at_millis: row.lastEventAt.getTime(),
+    chunk_count: agg.chunkCount,
+    event_count: agg.eventCount,
+  };
+}
diff --git a/...ard/src/app/(main)/(protected)/projects/[projectId]/analytics/replays/[replayId]/page.tsx b/...ard/src/app/(main)/(protected)/projects/[projectId]/analytics/replays/[replayId]/page.tsx
@@ -0,0 +1,10 @@
+import PageClient from "../page-client";
+
+export default async function Page(props: {
+  params: Promise<{
+    replayId: string,
+  }>,
+}) {
+  const params = await props.params;
+  return <PageClient initialReplayId={params.replayId} />;
+}