perf: add database indexes for insights queries

apps/remix/app/components/embed/authoring/configure-fields-view.tsx

@@ -2,7 +2,7 @@ import { useCallback, useEffect, useMemo, useRef, useState } from 'react';
 
 import { useLingui } from '@lingui/react';
 import { Trans } from '@lingui/react/macro';
-import type { EnvelopeItem, FieldType } from '@prisma/client';
+import type { DocumentData, FieldType } from '@prisma/client';
 import { ReadStatus, type Recipient, SendStatus, SigningStatus } from '@prisma/client';
 import { base64 } from '@scure/base';
 import { ChevronsUpDown } from 'lucide-react';
@@ -40,8 +40,7 @@ const DEFAULT_WIDTH_PX = MIN_WIDTH_PX * 2.5;
 
 export type ConfigureFieldsViewProps = {
   configData: TConfigureEmbedFormSchema;
-  presignToken?: string | undefined;
-  envelopeItem?: Pick<EnvelopeItem, 'id' | 'envelopeId'>;
+  documentData?: DocumentData;
   defaultValues?: Partial<TConfigureFieldsFormSchema>;
   onBack?: (data: TConfigureFieldsFormSchema) => void;
   onSubmit: (data: TConfigureFieldsFormSchema) => void;
@@ -49,8 +48,7 @@ export type ConfigureFieldsViewProps = {
 
 export const ConfigureFieldsView = ({
   configData,
-  presignToken,
-  envelopeItem,
+  documentData,
   defaultValues,
   onBack,
   onSubmit,
@@ -84,25 +82,17 @@ export const ConfigureFieldsView = ({
   }, []);
 
   const normalizedDocumentData = useMemo(() => {
-    if (envelopeItem) {
-      return undefined;
+    if (documentData) {
+      return documentData.data;
     }
 
     if (!configData.documentData) {
-      return undefined;
+      return null;
     }
 
     return base64.encode(configData.documentData.data);
   }, [configData.documentData]);
 
-  const normalizedEnvelopeItem = useMemo(() => {
-    if (envelopeItem) {
-      return envelopeItem;
-    }
-
-    return { id: '', envelopeId: '' };
-  }, [envelopeItem]);
-
   const recipients = useMemo(() => {
     return configData.signers.map<Recipient>((signer, index) => ({
       id: signer.nativeId || index,
@@ -544,50 +534,56 @@ export const ConfigureFieldsView = ({
             )}
 
             <Form {...form}>
-              <div>
-                <PDFViewer
-                  presignToken={presignToken}
-                  overrideData={normalizedDocumentData}
-                  envelopeItem={normalizedEnvelopeItem}
-                  token={undefined}
-                  version="signed"
-                />
+              {normalizedDocumentData && (
+                <div>
+                  <PDFViewer
+                    overrideData={normalizedDocumentData}
+                    envelopeItem={{
+                      id: '',
+                      envelopeId: '',
+                    }}
+                    token={undefined}
+                    version="signed"
+                  />
 
-                <ElementVisible
-                  target={`${PDF_VIEWER_PAGE_SELECTOR}[data-page-number="${highestPageNumber}"]`}
-                >
-                  {localFields.map((field, index) => {
-                    const recipientIndex = recipients.findIndex((r) => r.id === field.recipientId);
+                  <ElementVisible
+                    target={`${PDF_VIEWER_PAGE_SELECTOR}[data-page-number="${highestPageNumber}"]`}
+                  >
+                    {localFields.map((field, index) => {
+                      const recipientIndex = recipients.findIndex(
+                        (r) => r.id === field.recipientId,
+                      );
 
-                    return (
-                      <FieldItem
-                        key={field.formId}
-                        field={field}
-                        minHeight={MIN_HEIGHT_PX}
-                        minWidth={MIN_WIDTH_PX}
-                        defaultHeight={DEFAULT_HEIGHT_PX}
-                        defaultWidth={DEFAULT_WIDTH_PX}
-                        onResize={(node) => onFieldResize(node, index)}
-                        onMove={(node) => onFieldMove(node, index)}
-                        onRemove={() => remove(index)}
-                        onDuplicate={() => onFieldCopy(null, { duplicate: true })}
-                        onDuplicateAllPages={() => onFieldCopy(null, { duplicateAll: true })}
-                        onFocus={() => setLastActiveField(field)}
-                        onBlur={() => setLastActiveField(null)}
-                        onAdvancedSettings={() => {
-                          setCurrentField(field);
-                          setShowAdvancedSettings(true);
-                        }}
-                        recipientIndex={recipientIndex}
-                        active={activeFieldId === field.formId}
-                        onFieldActivate={() => setActiveFieldId(field.formId)}
-                        onFieldDeactivate={() => setActiveFieldId(null)}
-                        disabled={selectedRecipient?.id !== field.recipientId}
-                      />
-                    );
-                  })}
-                </ElementVisible>
-              </div>
+                      return (
+                        <FieldItem
+                          key={field.formId}
+                          field={field}
+                          minHeight={MIN_HEIGHT_PX}
+                          minWidth={MIN_WIDTH_PX}
+                          defaultHeight={DEFAULT_HEIGHT_PX}
+                          defaultWidth={DEFAULT_WIDTH_PX}
+                          onResize={(node) => onFieldResize(node, index)}
+                          onMove={(node) => onFieldMove(node, index)}
+                          onRemove={() => remove(index)}
+                          onDuplicate={() => onFieldCopy(null, { duplicate: true })}
+                          onDuplicateAllPages={() => onFieldCopy(null, { duplicateAll: true })}
+                          onFocus={() => setLastActiveField(field)}
+                          onBlur={() => setLastActiveField(null)}
+                          onAdvancedSettings={() => {
+                            setCurrentField(field);
+                            setShowAdvancedSettings(true);
+                          }}
+                          recipientIndex={recipientIndex}
+                          active={activeFieldId === field.formId}
+                          onFieldActivate={() => setActiveFieldId(field.formId)}
+                          onFieldDeactivate={() => setActiveFieldId(null)}
+                          disabled={selectedRecipient?.id !== field.recipientId}
+                        />
+                      );
+                    })}
+                  </ElementVisible>
+                </div>
+              )}
             </Form>
           </div>
         </div>

apps/remix/app/routes/embed+/v1+/authoring+/document.edit.$id.tsx

@@ -75,7 +75,6 @@ export const loader = async ({ request, params }: Route.LoaderArgs) => {
   }));
 
   return {
-    token,
     document: {
       ...document,
       fields,
@@ -87,7 +86,7 @@ export default function EmbeddingAuthoringDocumentEditPage() {
   const { _ } = useLingui();
   const { toast } = useToast();
 
-  const { document, token } = useLoaderData<typeof loader>();
+  const { document } = useLoaderData<typeof loader>();
 
   const [hasFinishedInit, setHasFinishedInit] = useState(false);
 
@@ -322,8 +321,7 @@ export default function EmbeddingAuthoringDocumentEditPage() {
 
           <ConfigureFieldsView
             configData={configuration!}
-            presignToken={token}
-            envelopeItem={document.envelopeItems[0]}
+            documentData={document.documentData}
             defaultValues={fields ?? undefined}
             onBack={canGoBack ? handleBackToConfig : undefined}
             onSubmit={handleConfigureFieldsSubmit}

apps/remix/app/routes/embed+/v1+/authoring+/template.edit.$id.tsx

@@ -75,7 +75,6 @@ export const loader = async ({ request, params }: Route.LoaderArgs) => {
   }));
 
   return {
-    token,
     template: {
       ...template,
       fields,
@@ -87,7 +86,7 @@ export default function EmbeddingAuthoringTemplateEditPage() {
   const { _ } = useLingui();
   const { toast } = useToast();
 
-  const { template, token } = useLoaderData<typeof loader>();
+  const { template } = useLoaderData<typeof loader>();
 
   const [hasFinishedInit, setHasFinishedInit] = useState(false);
 
@@ -322,8 +321,7 @@ export default function EmbeddingAuthoringTemplateEditPage() {
 
           <ConfigureFieldsView
             configData={configuration!}
-            presignToken={token}
-            envelopeItem={template.envelopeItems[0]}
+            documentData={template.templateDocumentData}
             defaultValues={fields ?? undefined}
             onBack={canGoBack ? handleBackToConfig : undefined}
             onSubmit={handleConfigureFieldsSubmit}

apps/remix/server/api/files/files.ts

@@ -5,7 +5,6 @@ import { Hono } from 'hono';
 import { getOptionalSession } from '@documenso/auth/server/lib/utils/get-session';
 import { APP_DOCUMENT_UPLOAD_SIZE_LIMIT } from '@documenso/lib/constants/app';
 import { AppError, AppErrorCode } from '@documenso/lib/errors/app-error';
-import { verifyEmbeddingPresignToken } from '@documenso/lib/server-only/embedding-presign/verify-embedding-presign-token';
 import { getTeamById } from '@documenso/lib/server-only/team/get-team';
 import { putNormalizedPdfFileServerSide } from '@documenso/lib/universal/upload/put-file.server';
 import { getPresignPostUrl } from '@documenso/lib/universal/upload/server-actions';
@@ -17,7 +16,6 @@ import {
   type TGetPresignedPostUrlResponse,
   ZGetEnvelopeItemFileDownloadRequestParamsSchema,
   ZGetEnvelopeItemFileRequestParamsSchema,
-  ZGetEnvelopeItemFileRequestQuerySchema,
   ZGetEnvelopeItemFileTokenDownloadRequestParamsSchema,
   ZGetEnvelopeItemFileTokenRequestParamsSchema,
   ZGetPresignedPostUrlRequestSchema,
@@ -70,24 +68,12 @@ export const filesRoute = new Hono<HonoEnv>()
   .get(
     '/envelope/:envelopeId/envelopeItem/:envelopeItemId',
     sValidator('param', ZGetEnvelopeItemFileRequestParamsSchema),
-    sValidator('query', ZGetEnvelopeItemFileRequestQuerySchema),
     async (c) => {
       const { envelopeId, envelopeItemId } = c.req.valid('param');
-      const { token } = c.req.query();
 
       const session = await getOptionalSession(c);
 
-      let userId = session.user?.id;
-
-      if (token) {
-        const presignToken = await verifyEmbeddingPresignToken({
-          token,
-        }).catch(() => undefined);
-
-        userId = presignToken?.userId;
-      }
-
-      if (!userId) {
+      if (!session.user) {
         return c.json({ error: 'Unauthorized' }, 401);
       }
 
@@ -118,7 +104,7 @@ export const filesRoute = new Hono<HonoEnv>()
       }
 
       const team = await getTeamById({
-        userId: userId,
+        userId: session.user.id,
         teamId: envelope.teamId,
       }).catch((error) => {
         console.error(error);

apps/remix/server/api/files/files.types.ts

@@ -36,14 +36,6 @@ export type TGetEnvelopeItemFileRequestParams = z.infer<
   typeof ZGetEnvelopeItemFileRequestParamsSchema
 >;
 
-export const ZGetEnvelopeItemFileRequestQuerySchema = z.object({
-  token: z.string().optional(),
-});
-
-export type TGetEnvelopeItemFileRequestQuery = z.infer<
-  typeof ZGetEnvelopeItemFileRequestQuerySchema
->;
-
 export const ZGetEnvelopeItemFileTokenRequestParamsSchema = z.object({
   token: z.string().min(1),
   envelopeItemId: z.string().min(1),

packages/lib/server-only/admin/get-organisation-detailed-insights.ts

@@ -116,28 +116,28 @@ async function getTeamInsights(
 ): Promise<OrganisationDetailedInsights> {
   const teamsQuery = kyselyPrisma.$kysely
     .selectFrom('Team as t')
-    .leftJoin('Envelope as e', (join) =>
-      join
-        .onRef('t.id', '=', 'e.teamId')
-        .on('e.deletedAt', 'is', null)
-        .on('e.type', '=', sql.lit(EnvelopeType.DOCUMENT)),
-    )
-    .leftJoin('TeamGroup as tg', 'tg.teamId', 't.id')
-    .leftJoin('OrganisationGroup as og', 'og.id', 'tg.organisationGroupId')
-    .leftJoin('OrganisationGroupMember as ogm', 'ogm.groupId', 'og.id')
-    .leftJoin('OrganisationMember as om', 'om.id', 'ogm.organisationMemberId')
     .where('t.organisationId', '=', organisationId)
-    .select([
-      't.id as id',
-      't.name as name',
-      't.createdAt as createdAt',
-      sql<number>`COUNT(DISTINCT om."userId")`.as('memberCount'),
-      (createdAtFrom
-        ? sql<number>`COUNT(DISTINCT CASE WHEN e.id IS NOT NULL AND e."createdAt" >= ${createdAtFrom} THEN e.id END)`
-        : sql<number>`COUNT(DISTINCT e.id)`
-      ).as('documentCount'),
+    .select((eb) => [
+      't.id',
+      't.name',
+      't.createdAt',
+      eb
+        .selectFrom('TeamGroup as tg')
+        .innerJoin('OrganisationGroup as og', 'og.id', 'tg.organisationGroupId')
+        .innerJoin('OrganisationGroupMember as ogm', 'ogm.groupId', 'og.id')
+        .innerJoin('OrganisationMember as om', 'om.id', 'ogm.organisationMemberId')
+        .whereRef('tg.teamId', '=', 't.id')
+        .select(sql<number>`count(distinct om."userId")`.as('count'))
+        .as('memberCount'),
+      eb
+        .selectFrom('Envelope as e')
+        .whereRef('e.teamId', '=', 't.id')
+        .where('e.deletedAt', 'is', null)
+        .where('e.type', '=', sql.lit(EnvelopeType.DOCUMENT))
+        .$if(!!createdAtFrom, (qb) => qb.where('e.createdAt', '>=', createdAtFrom!))
+        .select(sql<number>`count(e.id)`.as('count'))
+        .as('documentCount'),
     ])
-    .groupBy(['t.id', 't.name', 't.createdAt'])
     .orderBy('documentCount', 'desc')
     .limit(perPage)
     .offset(offset);
@@ -164,48 +164,38 @@ async function getUserInsights(
   perPage: number,
   createdAtFrom: Date | null,
 ): Promise<OrganisationDetailedInsights> {
-  const usersBase = kyselyPrisma.$kysely
+  const usersQuery = kyselyPrisma.$kysely
     .selectFrom('OrganisationMember as om')
     .innerJoin('User as u', 'u.id', 'om.userId')
     .where('om.organisationId', '=', organisationId)
-    .leftJoin('Envelope as e', (join) =>
-      join
-        .onRef('e.userId', '=', 'u.id')
-        .on('e.deletedAt', 'is', null)
-        .on('e.type', '=', sql.lit(EnvelopeType.DOCUMENT)),
-    )
-    .leftJoin('Team as td', (join) =>
-      join.onRef('td.id', '=', 'e.teamId').on('td.organisationId', '=', organisationId),
-    )
-    .leftJoin('Recipient as r', (join) =>
-      join.onRef('r.email', '=', 'u.email').on('r.signedAt', 'is not', null),
-    )
-    .leftJoin('Envelope as se', (join) =>
-      join
-        .onRef('se.id', '=', 'r.envelopeId')
-        .on('se.deletedAt', 'is', null)
-        .on('se.type', '=', sql.lit(EnvelopeType.DOCUMENT)),
-    )
-    .leftJoin('Team as ts', (join) =>
-      join.onRef('ts.id', '=', 'se.teamId').on('ts.organisationId', '=', organisationId),
-    );
-
-  const usersQuery = usersBase
-    .select([
-      'u.id as id',
-      'u.name as name',
-      'u.email as email',
-      'u.createdAt as createdAt',
-      (createdAtFrom
-        ? sql<number>`COUNT(DISTINCT CASE WHEN e.id IS NOT NULL AND td.id IS NOT NULL AND e."createdAt" >= ${createdAtFrom} THEN e.id END)`
-        : sql<number>`COUNT(DISTINCT CASE WHEN td.id IS NOT NULL THEN e.id END)`
-      ).as('documentCount'),
-      (createdAtFrom
-        ? sql<number>`COUNT(DISTINCT CASE WHEN e.id IS NOT NULL AND td.id IS NOT NULL AND e.status = 'COMPLETED' AND e."createdAt" >= ${createdAtFrom} THEN e.id END)`
-        : sql<number>`COUNT(DISTINCT CASE WHEN e.id IS NOT NULL AND td.id IS NOT NULL AND e.status = 'COMPLETED' THEN e.id END)`
-      ).as('signedDocumentCount'),
+    .select((eb) => [
+      'u.id',
+      'u.name',
+      'u.email',
+      'u.createdAt',
+      eb
+        .selectFrom('Envelope as e')
+        .innerJoin('Team as t', 't.id', 'e.teamId')
+        .whereRef('e.userId', '=', 'u.id')
+        .where('t.organisationId', '=', organisationId)
+        .where('e.deletedAt', 'is', null)
+        .where('e.type', '=', sql.lit(EnvelopeType.DOCUMENT))
+        .$if(!!createdAtFrom, (qb) => qb.where('e.createdAt', '>=', createdAtFrom!))
+        .select(sql<number>`count(e.id)`.as('count'))
+        .as('documentCount'),
+      eb
+        .selectFrom('Recipient as r')
+        .innerJoin('Envelope as e', 'e.id', 'r.envelopeId')
+        .innerJoin('Team as t', 't.id', 'e.teamId')
+        .whereRef('r.email', '=', 'u.email')
+        .where('r.signedAt', 'is not', null)
+        .where('t.organisationId', '=', organisationId)
+        .where('e.deletedAt', 'is', null)
+        .where('e.type', '=', sql.lit(EnvelopeType.DOCUMENT))
+        .$if(!!createdAtFrom, (qb) => qb.where('e.createdAt', '>=', createdAtFrom!))
+        .select(sql<number>`count(e.id)`.as('count'))
+        .as('signedDocumentCount'),
     ])
-    .groupBy(['u.id', 'u.name', 'u.email', 'u.createdAt'])
     .orderBy('u.createdAt', 'desc')
     .limit(perPage)
     .offset(offset);
@@ -292,72 +282,51 @@ async function getOrganisationSummary(
   organisationId: string,
   createdAtFrom: Date | null,
 ): Promise<OrganisationSummary> {
-  const summaryQuery = kyselyPrisma.$kysely
-    .selectFrom('Organisation as o')
-    .where('o.id', '=', organisationId)
-    .select([
-      sql<number>`(SELECT COUNT(DISTINCT t2.id) FROM "Team" AS t2 WHERE t2."organisationId" = o.id)`.as(
-        'totalTeams',
-      ),
-      sql<number>`(SELECT COUNT(DISTINCT om2."userId") FROM "OrganisationMember" AS om2 WHERE om2."organisationId" = o.id)`.as(
-        'totalMembers',
-      ),
-      sql<number>`(
-        SELECT COUNT(DISTINCT e2.id)
-        FROM "Envelope" AS e2
-        INNER JOIN "Team" AS t2 ON t2.id = e2."teamId"
-        WHERE t2."organisationId" = o.id AND e2."deletedAt" IS NULL AND e2.type = 'DOCUMENT'
-      )`.as('totalDocuments'),
-      sql<number>`(
-        SELECT COUNT(DISTINCT e2.id)
-        FROM "Envelope" AS e2
-        INNER JOIN "Team" AS t2 ON t2.id = e2."teamId"
-        WHERE t2."organisationId" = o.id AND e2."deletedAt" IS NULL AND e2.type = 'DOCUMENT' AND e2.status IN ('DRAFT', 'PENDING')
-      )`.as('activeDocuments'),
-      sql<number>`(
-        SELECT COUNT(DISTINCT e2.id)
-        FROM "Envelope" AS e2
-        INNER JOIN "Team" AS t2 ON t2.id = e2."teamId"
-        WHERE t2."organisationId" = o.id AND e2."deletedAt" IS NULL AND e2.type = 'DOCUMENT' AND e2.status = 'COMPLETED'
-      )`.as('completedDocuments'),
-      (createdAtFrom
-        ? sql<number>`(
-            SELECT COUNT(DISTINCT e2.id)
-            FROM "Envelope" AS e2
-            INNER JOIN "Team" AS t2 ON t2.id = e2."teamId"
-            WHERE t2."organisationId" = o.id
-              AND e2."deletedAt" IS NULL
-              AND e2.type = 'DOCUMENT'
-              AND e2.status = 'COMPLETED'
-              AND e2."createdAt" >= ${createdAtFrom}
-          )`
-        : sql<number>`(
-            SELECT COUNT(DISTINCT e2.id)
-            FROM "Envelope" AS e2
-            INNER JOIN "Team" AS t2 ON t2.id = e2."teamId"
-            WHERE t2."organisationId" = o.id
-              AND e2."deletedAt" IS NULL
-              AND e2.type = 'DOCUMENT'
-              AND e2.status = 'COMPLETED'
-          )`
-      ).as('volumeThisPeriod'),
-      sql<number>`(
-        SELECT COUNT(DISTINCT e2.id)
-        FROM "Envelope" AS e2
-        INNER JOIN "Team" AS t2 ON t2.id = e2."teamId"
-        WHERE t2."organisationId" = o.id AND e2."deletedAt" IS NULL AND e2.type = 'DOCUMENT' AND e2.status = 'COMPLETED'
-      )`.as('volumeAllTime'),
-    ]);
+  const teamCountQuery = kyselyPrisma.$kysely
+    .selectFrom('Team')
+    .where('organisationId', '=', organisationId)
+    .select(sql<number>`count(id)`.as('count'))
+    .executeTakeFirst();
 
-  const result = await summaryQuery.executeTakeFirst();
+  const memberCountQuery = kyselyPrisma.$kysely
+    .selectFrom('OrganisationMember')
+    .where('organisationId', '=', organisationId)
+    .select(sql<number>`count(id)`.as('count'))
+    .executeTakeFirst();
+
+  const envelopeStatsQuery = kyselyPrisma.$kysely
+    .selectFrom('Envelope as e')
+    .innerJoin('Team as t', 't.id', 'e.teamId')
+    .where('t.organisationId', '=', organisationId)
+    .where('e.deletedAt', 'is', null)
+    .where('e.type', '=', sql.lit(EnvelopeType.DOCUMENT))
+    .select([
+      sql<number>`count(e.id)`.as('totalDocuments'),
+      sql<number>`count(case when e.status in ('DRAFT', 'PENDING') then 1 end)`.as(
+        'activeDocuments',
+      ),
+      sql<number>`count(case when e.status = 'COMPLETED' then 1 end)`.as('completedDocuments'),
+      sql<number>`count(case when e.status = 'COMPLETED' then 1 end)`.as('volumeAllTime'),
+      (createdAtFrom
+        ? sql<number>`count(case when e.status = 'COMPLETED' and e."createdAt" >= ${createdAtFrom} then 1 end)`
+        : sql<number>`count(case when e.status = 'COMPLETED' then 1 end)`
+      ).as('volumeThisPeriod'),
+    ])
+    .executeTakeFirst();
+
+  const [teamCount, memberCount, envelopeStats] = await Promise.all([
+    teamCountQuery,
+    memberCountQuery,
+    envelopeStatsQuery,
+  ]);
 
   return {
-    totalTeams: Number(result?.totalTeams || 0),
-    totalMembers: Number(result?.totalMembers || 0),
-    totalDocuments: Number(result?.totalDocuments || 0),
-    activeDocuments: Number(result?.activeDocuments || 0),
-    completedDocuments: Number(result?.completedDocuments || 0),
-    volumeThisPeriod: Number(result?.volumeThisPeriod || 0),
-    volumeAllTime: Number(result?.volumeAllTime || 0),
+    totalTeams: Number(teamCount?.count || 0),
+    totalMembers: Number(memberCount?.count || 0),
+    totalDocuments: Number(envelopeStats?.totalDocuments || 0),
+    activeDocuments: Number(envelopeStats?.activeDocuments || 0),
+    completedDocuments: Number(envelopeStats?.completedDocuments || 0),
+    volumeThisPeriod: Number(envelopeStats?.volumeThisPeriod || 0),
+    volumeAllTime: Number(envelopeStats?.volumeAllTime || 0),
   };
 }

packages/lib/server-only/admin/get-signing-volume.ts

@@ -33,25 +33,32 @@ export async function getSigningVolume({
 
   let findQuery = kyselyPrisma.$kysely
     .selectFrom('Organisation as o')
-    .leftJoin('Team as t', 'o.id', 't.organisationId')
-    .leftJoin('Envelope as e', (join) =>
-      join
-        .onRef('t.id', '=', 'e.teamId')
-        .on('e.status', '=', sql.lit(DocumentStatus.COMPLETED))
-        .on('e.deletedAt', 'is', null)
-        .on('e.type', '=', sql.lit(EnvelopeType.DOCUMENT)),
-    )
     .where((eb) =>
-      eb.or([eb('o.name', 'ilike', `%${search}%`), eb('t.name', 'ilike', `%${search}%`)]),
+      eb.or([
+        eb('o.name', 'ilike', `%${search}%`),
+        eb.exists(
+          eb
+            .selectFrom('Team as t')
+            .whereRef('t.organisationId', '=', 'o.id')
+            .where('t.name', 'ilike', `%${search}%`),
+        ),
+      ]),
     )
-    .select([
+    .select((eb) => [
       'o.id as id',
       'o.createdAt as createdAt',
       'o.customerId as customerId',
       sql<string>`COALESCE(o.name, 'Unknown')`.as('name'),
-      sql<number>`COUNT(DISTINCT e.id)`.as('signingVolume'),
-    ])
-    .groupBy(['o.id', 'o.name', 'o.customerId']);
+      eb
+        .selectFrom('Envelope as e')
+        .innerJoin('Team as t', 't.id', 'e.teamId')
+        .whereRef('t.organisationId', '=', 'o.id')
+        .where('e.status', '=', sql.lit(DocumentStatus.COMPLETED))
+        .where('e.deletedAt', 'is', null)
+        .where('e.type', '=', sql.lit(EnvelopeType.DOCUMENT))
+        .select(sql<number>`count(e.id)`.as('count'))
+        .as('signingVolume'),
+    ]);
 
   switch (sortBy) {
     case 'name':
@@ -71,11 +78,18 @@ export async function getSigningVolume({
 
   const countQuery = kyselyPrisma.$kysely
     .selectFrom('Organisation as o')
-    .leftJoin('Team as t', 'o.id', 't.organisationId')
     .where((eb) =>
-      eb.or([eb('o.name', 'ilike', `%${search}%`), eb('t.name', 'ilike', `%${search}%`)]),
+      eb.or([
+        eb('o.name', 'ilike', `%${search}%`),
+        eb.exists(
+          eb
+            .selectFrom('Team as t')
+            .whereRef('t.organisationId', '=', 'o.id')
+            .where('t.name', 'ilike', `%${search}%`),
+        ),
+      ]),
     )
-    .select(() => [sql<number>`COUNT(DISTINCT o.id)`.as('count')]);
+    .select(({ fn }) => [fn.countAll().as('count')]);
 
   const [results, [{ count }]] = await Promise.all([findQuery.execute(), countQuery.execute()]);
 
@@ -104,64 +118,77 @@ export async function getOrganisationInsights({
   const offset = Math.max(page - 1, 0) * perPage;
 
   const now = new Date();
-  let dateCondition = sql`1=1`;
+  let dateCondition = sql<boolean>`1=1`;
 
   if (startDate && endDate) {
-    dateCondition = sql`e."createdAt" >= ${startDate} AND e."createdAt" <= ${endDate}`;
+    dateCondition = sql<boolean>`e."createdAt" >= ${startDate} AND e."createdAt" <= ${endDate}`;
   } else {
     switch (dateRange) {
       case 'last30days': {
         const thirtyDaysAgo = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);
-        dateCondition = sql`e."createdAt" >= ${thirtyDaysAgo}`;
+        dateCondition = sql<boolean>`e."createdAt" >= ${thirtyDaysAgo}`;
         break;
       }
       case 'last90days': {
         const ninetyDaysAgo = new Date(now.getTime() - 90 * 24 * 60 * 60 * 1000);
-        dateCondition = sql`e."createdAt" >= ${ninetyDaysAgo}`;
+        dateCondition = sql<boolean>`e."createdAt" >= ${ninetyDaysAgo}`;
         break;
       }
       case 'lastYear': {
         const oneYearAgo = new Date(now.getFullYear() - 1, now.getMonth(), now.getDate());
-        dateCondition = sql`e."createdAt" >= ${oneYearAgo}`;
+        dateCondition = sql<boolean>`e."createdAt" >= ${oneYearAgo}`;
         break;
       }
       case 'allTime':
       default:
-        dateCondition = sql`1=1`;
+        dateCondition = sql<boolean>`1=1`;
         break;
     }
   }
 
   let findQuery = kyselyPrisma.$kysely
     .selectFrom('Organisation as o')
-    .leftJoin('Team as t', 'o.id', 't.organisationId')
-    .leftJoin('Envelope as e', (join) =>
-      join
-        .onRef('t.id', '=', 'e.teamId')
-        .on('e.status', '=', sql.lit(DocumentStatus.COMPLETED))
-        .on('e.deletedAt', 'is', null)
-        .on('e.type', '=', sql.lit(EnvelopeType.DOCUMENT)),
-    )
-    .leftJoin('OrganisationMember as om', 'o.id', 'om.organisationId')
     .leftJoin('Subscription as s', 'o.id', 's.organisationId')
     .where((eb) =>
-      eb.or([eb('o.name', 'ilike', `%${search}%`), eb('t.name', 'ilike', `%${search}%`)]),
+      eb.or([
+        eb('o.name', 'ilike', `%${search}%`),
+        eb.exists(
+          eb
+            .selectFrom('Team as t')
+            .whereRef('t.organisationId', '=', 'o.id')
+            .where('t.name', 'ilike', `%${search}%`),
+        ),
+      ]),
     )
-    .select([
+    .select((eb) => [
       'o.id as id',
       'o.createdAt as createdAt',
       'o.customerId as customerId',
       sql<string>`COALESCE(o.name, 'Unknown')`.as('name'),
-      sql<number>`COUNT(DISTINCT CASE WHEN e.id IS NOT NULL AND ${dateCondition} THEN e.id END)`.as(
-        'signingVolume',
-      ),
-      sql<number>`GREATEST(COUNT(DISTINCT t.id), 1)`.as('teamCount'),
-      sql<number>`COUNT(DISTINCT om."userId")`.as('memberCount'),
       sql<string>`CASE WHEN s.status IS NOT NULL THEN s.status ELSE NULL END`.as(
         'subscriptionStatus',
       ),
-    ])
-    .groupBy(['o.id', 'o.name', 'o.customerId', 's.status']);
+      eb
+        .selectFrom('Team as t')
+        .whereRef('t.organisationId', '=', 'o.id')
+        .select(sql<number>`count(t.id)`.as('count'))
+        .as('teamCount'),
+      eb
+        .selectFrom('OrganisationMember as om')
+        .whereRef('om.organisationId', '=', 'o.id')
+        .select(sql<number>`count(om.id)`.as('count'))
+        .as('memberCount'),
+      eb
+        .selectFrom('Envelope as e')
+        .innerJoin('Team as t', 't.id', 'e.teamId')
+        .whereRef('t.organisationId', '=', 'o.id')
+        .where('e.status', '=', sql.lit(DocumentStatus.COMPLETED))
+        .where('e.deletedAt', 'is', null)
+        .where('e.type', '=', sql.lit(EnvelopeType.DOCUMENT))
+        .where(dateCondition)
+        .select(sql<number>`count(e.id)`.as('count'))
+        .as('signingVolume'),
+    ]);
 
   switch (sortBy) {
     case 'name':
@@ -181,11 +208,18 @@ export async function getOrganisationInsights({
 
   const countQuery = kyselyPrisma.$kysely
     .selectFrom('Organisation as o')
-    .leftJoin('Team as t', 'o.id', 't.organisationId')
     .where((eb) =>
-      eb.or([eb('o.name', 'ilike', `%${search}%`), eb('t.name', 'ilike', `%${search}%`)]),
+      eb.or([
+        eb('o.name', 'ilike', `%${search}%`),
+        eb.exists(
+          eb
+            .selectFrom('Team as t')
+            .whereRef('t.organisationId', '=', 'o.id')
+            .where('t.name', 'ilike', `%${search}%`),
+        ),
+      ]),
     )
-    .select(() => [sql<number>`COUNT(DISTINCT o.id)`.as('count')]);
+    .select(({ fn }) => [fn.countAll().as('count')]);
 
   const [results, [{ count }]] = await Promise.all([findQuery.execute(), countQuery.execute()]);
 

packages/lib/utils/envelope-download.ts

@@ -8,17 +8,15 @@ export type EnvelopeItemPdfUrlOptions =
       envelopeItem: Pick<EnvelopeItem, 'id' | 'envelopeId'>;
       token: string | undefined;
       version: 'original' | 'signed';
-      presignToken?: undefined;
     }
   | {
       type: 'view';
       envelopeItem: Pick<EnvelopeItem, 'id' | 'envelopeId'>;
       token: string | undefined;
-      presignToken?: string | undefined;
     };
 
 export const getEnvelopeItemPdfUrl = (options: EnvelopeItemPdfUrlOptions) => {
-  const { envelopeItem, token, type, presignToken } = options;
+  const { envelopeItem, token, type } = options;
 
   const { id, envelopeId } = envelopeItem;
 
@@ -26,11 +24,11 @@ export const getEnvelopeItemPdfUrl = (options: EnvelopeItemPdfUrlOptions) => {
     const version = options.version;
 
     return token
-      ? `${NEXT_PUBLIC_WEBAPP_URL()}/api/files/token/${token}/envelopeItem/${id}/download/${version}${presignToken ? `?presignToken=${presignToken}` : ''}`
+      ? `${NEXT_PUBLIC_WEBAPP_URL()}/api/files/token/${token}/envelopeItem/${id}/download/${version}`
       : `${NEXT_PUBLIC_WEBAPP_URL()}/api/files/envelope/${envelopeId}/envelopeItem/${id}/download/${version}`;
   }
 
   return token
-    ? `${NEXT_PUBLIC_WEBAPP_URL()}/api/files/token/${token}/envelopeItem/${id}${presignToken ? `?presignToken=${presignToken}` : ''}`
+    ? `${NEXT_PUBLIC_WEBAPP_URL()}/api/files/token/${token}/envelopeItem/${id}`
     : `${NEXT_PUBLIC_WEBAPP_URL()}/api/files/envelope/${envelopeId}/envelopeItem/${id}`;
 };

packages/prisma/migrations/20251119022255_add_indexes_for_insights/migration.sql

@@ -0,0 +1,29 @@
+-- CreateIndex
+CREATE INDEX "Envelope_teamId_deletedAt_type_status_idx" ON "Envelope"("teamId", "deletedAt", "type", "status");
+
+-- CreateIndex
+CREATE INDEX "Envelope_teamId_deletedAt_type_createdAt_idx" ON "Envelope"("teamId", "deletedAt", "type", "createdAt");
+
+-- CreateIndex
+CREATE INDEX "Envelope_userId_deletedAt_type_idx" ON "Envelope"("userId", "deletedAt", "type");
+
+-- CreateIndex
+CREATE INDEX "Envelope_status_deletedAt_type_idx" ON "Envelope"("status", "deletedAt", "type");
+
+-- CreateIndex
+CREATE INDEX "Organisation_name_idx" ON "Organisation"("name");
+
+-- CreateIndex
+CREATE INDEX "OrganisationMember_organisationId_idx" ON "OrganisationMember"("organisationId");
+
+-- CreateIndex
+CREATE INDEX "Recipient_email_idx" ON "Recipient"("email");
+
+-- CreateIndex
+CREATE INDEX "Recipient_signedAt_idx" ON "Recipient"("signedAt");
+
+-- CreateIndex
+CREATE INDEX "Recipient_envelopeId_signedAt_idx" ON "Recipient"("envelopeId", "signedAt");
+
+-- CreateIndex
+CREATE INDEX "Team_organisationId_name_idx" ON "Team"("organisationId", "name");

packages/prisma/schema.prisma

@@ -433,6 +433,10 @@ model Envelope {
   @@index([folderId])
   @@index([teamId])
   @@index([userId])
+  @@index([teamId, deletedAt, type, status])
+  @@index([teamId, deletedAt, type, createdAt])
+  @@index([userId, deletedAt, type])
+  @@index([status, deletedAt, type])
 }
 
 model EnvelopeItem {
@@ -585,6 +589,9 @@ model Recipient {
 
   @@index([envelopeId])
   @@index([token])
+  @@index([email])
+  @@index([signedAt])
+  @@index([envelopeId, signedAt])
 }
 
 enum FieldType {
@@ -694,6 +701,8 @@ model Organisation {
 
   organisationAuthenticationPortalId String                           @unique
   organisationAuthenticationPortal   OrganisationAuthenticationPortal @relation(fields: [organisationAuthenticationPortalId], references: [id])
+
+  @@index([name])
 }
 
 model OrganisationMember {
@@ -710,6 +719,7 @@ model OrganisationMember {
   organisationGroupMembers OrganisationGroupMember[]
 
   @@unique([userId, organisationId])
+  @@index([organisationId])
 }
 
 model OrganisationMemberInvite {
@@ -884,6 +894,7 @@ model Team {
   teamGlobalSettings   TeamGlobalSettings @relation(fields: [teamGlobalSettingsId], references: [id], onDelete: Cascade)
 
   @@index([organisationId])
+  @@index([organisationId, name])
 }
 
 model TeamEmail {

packages/ui/primitives/pdf-viewer.tsx

@@ -56,7 +56,6 @@ export type PDFViewerProps = {
   className?: string;
   envelopeItem: Pick<EnvelopeItem, 'id' | 'envelopeId'>;
   token: string | undefined;
-  presignToken?: string | undefined;
   version: 'original' | 'signed';
   onDocumentLoad?: (_doc: LoadedPDFDocument) => void;
   onPageClick?: OnPDFViewerPageClick;
@@ -68,7 +67,6 @@ export const PDFViewer = ({
   className,
   envelopeItem,
   token,
-  presignToken,
   version,
   onDocumentLoad,
   onPageClick,
@@ -168,7 +166,6 @@ export const PDFViewer = ({
           type: 'view',
           envelopeItem: envelopeItem,
           token,
-          presignToken,
         });
 
         const bytes = await fetch(documentUrl).then(async (res) => await res.arrayBuffer());