perf: add database indexes for insights queries

apps/documentation/pages/developers/self-hosting/how-to.mdx

@@ -275,15 +275,7 @@ The environment variables listed above are a subset of those available for confi
 | `NEXT_PRIVATE_ENCRYPTION_SECONDARY_KEY`      | The secondary encryption key for symmetric encryption and decryption (at least 32 characters).      |
 | `NEXT_PRIVATE_GOOGLE_CLIENT_ID`              | The Google client ID for Google authentication (optional).                                          |
 | `NEXT_PRIVATE_GOOGLE_CLIENT_SECRET`          | The Google client secret for Google authentication (optional).                                      |
-| `NEXT_PRIVATE_MICROSOFT_CLIENT_ID`           | The Microsoft client ID for Microsoft authentication (optional).                                    |
-| `NEXT_PRIVATE_MICROSOFT_CLIENT_SECRET`       | The Microsoft client secret for Microsoft authentication (optional).                                |
-| `NEXT_PRIVATE_OIDC_CLIENT_ID`                | The OIDC client ID for OIDC authentication (optional).                                              |
-| `NEXT_PRIVATE_OIDC_CLIENT_SECRET`            | The OIDC client secret for OIDC authentication (optional).                                          |
-| `NEXT_PRIVATE_OIDC_WELL_KNOWN`               | The well-known URL for the OIDC provider (optional).                                                |
-| `NEXT_PRIVATE_OIDC_PROVIDER_LABEL`           | The label to display for the OIDC provider button (optional).                                       |
-| `NEXT_PRIVATE_OIDC_SKIP_VERIFY`              | Whether to skip email verification for OIDC accounts (optional, default `false`).                   |
 | `NEXT_PUBLIC_WEBAPP_URL`                     | The URL for the web application.                                                                    |
-| `NEXT_PUBLIC_SUPPORT_EMAIL`                  | The support email address displayed to users (default `support@documenso.com`).                     |
 | `NEXT_PRIVATE_DATABASE_URL`                  | The URL for the primary database connection (with connection pooling).                              |
 | `NEXT_PRIVATE_DIRECT_DATABASE_URL`           | The URL for the direct database connection (without connection pooling).                            |
 | `NEXT_PRIVATE_SIGNING_TRANSPORT`             | The signing transport to use. Available options: local (default)                                    |
@@ -305,7 +297,6 @@ The environment variables listed above are a subset of those available for confi
 | `NEXT_PRIVATE_SMTP_APIKEY_USER`              | The API key user for the SMTP server for the `smtp-api` transport.                                  |
 | `NEXT_PRIVATE_SMTP_APIKEY`                   | The API key for the SMTP server for the `smtp-api` transport.                                       |
 | `NEXT_PRIVATE_SMTP_SECURE`                   | Whether to force the use of TLS for the SMTP server for SMTP transports.                            |
-| `NEXT_PRIVATE_SMTP_UNSAFE_IGNORE_TLS`        | Whether to ignore TLS errors for the SMTP server (useful for self-signed certificates).             |
 | `NEXT_PRIVATE_SMTP_FROM_ADDRESS`             | The email address for the "from" address.                                                           |
 | `NEXT_PRIVATE_SMTP_FROM_NAME`                | The sender name for the "from" address.                                                             |
 | `NEXT_PRIVATE_RESEND_API_KEY`                | The API key for Resend.com for the `resend` transport.                                              |
@@ -317,7 +308,6 @@ The environment variables listed above are a subset of those available for confi
 | `NEXT_PUBLIC_DOCUMENT_SIZE_UPLOAD_LIMIT`     | The maximum document upload limit displayed to the user (in MB).                                    |
 | `NEXT_PUBLIC_POSTHOG_KEY`                    | The optional PostHog key for analytics and feature flags.                                           |
 | `NEXT_PUBLIC_DISABLE_SIGNUP`                 | Whether to disable user signups through the /signup page.                                           |
-| `NEXT_PRIVATE_BROWSERLESS_URL`               | The URL for a Browserless.io instance to generate PDFs (optional).                                  |
 
 ## Run as a Service
 

packages/lib/jobs/definitions/internal/seal-document.handler.ts

@@ -25,6 +25,7 @@ import { signPdf } from '@documenso/signing';
 
 import { AppError, AppErrorCode } from '../../../errors/app-error';
 import { sendCompletedEmail } from '../../../server-only/document/send-completed-email';
+import PostHogServerClient from '../../../server-only/feature-flags/get-post-hog-server-client';
 import { getAuditLogsPdf } from '../../../server-only/htmltopdf/get-audit-logs-pdf';
 import { getCertificatePdf } from '../../../server-only/htmltopdf/get-certificate-pdf';
 import { addRejectionStampToPdf } from '../../../server-only/pdf/add-rejection-stamp-to-pdf';
@@ -61,120 +62,171 @@ export const run = async ({
 }) => {
   const { documentId, sendEmail = true, isResealing = false, requestMetadata } = payload;
 
-  const { envelopeId, envelopeStatus, isRejected } = await io.runTask('seal-document', async () => {
-    const envelope = await prisma.envelope.findFirstOrThrow({
-      where: {
-        type: EnvelopeType.DOCUMENT,
-        secondaryId: mapDocumentIdToSecondaryId(documentId),
-      },
-      include: {
-        documentMeta: true,
-        recipients: true,
-        envelopeItems: {
-          include: {
-            documentData: true,
-            field: {
-              include: {
-                signature: true,
-              },
+  const envelope = await prisma.envelope.findFirstOrThrow({
+    where: {
+      type: EnvelopeType.DOCUMENT,
+      secondaryId: mapDocumentIdToSecondaryId(documentId),
+    },
+    include: {
+      documentMeta: true,
+      recipients: true,
+      envelopeItems: {
+        include: {
+          documentData: true,
+          field: {
+            include: {
+              signature: true,
             },
           },
         },
       },
+    },
+  });
+
+  if (envelope.envelopeItems.length === 0) {
+    throw new Error('At least one envelope item required');
+  }
+
+  const settings = await getTeamSettings({
+    userId: envelope.userId,
+    teamId: envelope.teamId,
+  });
+
+  const isComplete =
+    envelope.recipients.some((recipient) => recipient.signingStatus === SigningStatus.REJECTED) ||
+    envelope.recipients.every((recipient) => recipient.signingStatus === SigningStatus.SIGNED);
+
+  if (!isComplete) {
+    throw new AppError(AppErrorCode.UNKNOWN_ERROR, {
+      message: 'Document is not complete',
     });
+  }
 
-    if (envelope.envelopeItems.length === 0) {
-      throw new Error('At least one envelope item required');
-    }
+  // Seems silly but we need to do this in case the job is re-ran
+  // after it has already run through the update task further below.
+  // eslint-disable-next-line @typescript-eslint/require-await
+  const documentStatus = await io.runTask('get-document-status', async () => {
+    return envelope.status;
+  });
 
-    const settings = await getTeamSettings({
-      userId: envelope.userId,
-      teamId: envelope.teamId,
-    });
-
-    const isComplete =
-      envelope.recipients.some((recipient) => recipient.signingStatus === SigningStatus.REJECTED) ||
-      envelope.recipients.every((recipient) => recipient.signingStatus === SigningStatus.SIGNED);
-
-    if (!isComplete) {
-      throw new AppError(AppErrorCode.UNKNOWN_ERROR, {
-        message: 'Document is not complete',
-      });
-    }
-
-    let envelopeItems = envelope.envelopeItems;
-
-    if (envelopeItems.length < 1) {
-      throw new Error(`Document ${envelope.id} has no envelope items`);
-    }
-
-    const recipients = await prisma.recipient.findMany({
-      where: {
-        envelopeId: envelope.id,
-        role: {
-          not: RecipientRole.CC,
-        },
-      },
-    });
-
-    // Determine if the document has been rejected by checking if any recipient has rejected it
-    const rejectedRecipient = recipients.find(
-      (recipient) => recipient.signingStatus === SigningStatus.REJECTED,
-    );
-
-    const isRejected = Boolean(rejectedRecipient);
-
-    // Get the rejection reason from the rejected recipient
-    const rejectionReason = rejectedRecipient?.rejectionReason ?? '';
-
-    const fields = await prisma.field.findMany({
-      where: {
-        envelopeId: envelope.id,
-      },
-      include: {
-        signature: true,
-      },
-    });
-
-    // Skip the field check if the document is rejected
-    if (!isRejected && fieldsContainUnsignedRequiredField(fields)) {
-      throw new Error(`Document ${envelope.id} has unsigned required fields`);
-    }
-
-    if (isResealing) {
-      // If we're resealing we want to use the initial data for the document
-      // so we aren't placing fields on top of eachother.
-      envelopeItems = envelopeItems.map((envelopeItem) => ({
-        ...envelopeItem,
-        documentData: {
-          ...envelopeItem.documentData,
-          data: envelopeItem.documentData.initialData,
-        },
+  // This is the same case as above.
+  let envelopeItems = await io.runTask(
+    'get-document-data-id',
+    // eslint-disable-next-line @typescript-eslint/require-await
+    async () => {
+      // eslint-disable-next-line unused-imports/no-unused-vars
+      return envelope.envelopeItems.map(({ field, ...rest }) => ({
+        ...rest,
       }));
-    }
+    },
+  );
 
-    if (!envelope.qrToken) {
-      await prisma.envelope.update({
-        where: {
-          id: envelope.id,
-        },
-        data: {
-          qrToken: prefixedId('qr'),
-        },
-      });
-    }
+  if (envelopeItems.length < 1) {
+    throw new Error(`Document ${envelope.id} has no envelope items`);
+  }
 
-    const legacyDocumentId = mapSecondaryIdToDocumentId(envelope.secondaryId);
+  const recipients = await prisma.recipient.findMany({
+    where: {
+      envelopeId: envelope.id,
+      role: {
+        not: RecipientRole.CC,
+      },
+    },
+  });
 
-    const { certificateData, auditLogData } = await getCertificateAndAuditLogData({
-      legacyDocumentId,
-      documentMeta: envelope.documentMeta,
-      settings,
+  // Determine if the document has been rejected by checking if any recipient has rejected it
+  const rejectedRecipient = recipients.find(
+    (recipient) => recipient.signingStatus === SigningStatus.REJECTED,
+  );
+
+  const isRejected = Boolean(rejectedRecipient);
+
+  // Get the rejection reason from the rejected recipient
+  const rejectionReason = rejectedRecipient?.rejectionReason ?? '';
+
+  const fields = await prisma.field.findMany({
+    where: {
+      envelopeId: envelope.id,
+    },
+    include: {
+      signature: true,
+    },
+  });
+
+  // Skip the field check if the document is rejected
+  if (!isRejected && fieldsContainUnsignedRequiredField(fields)) {
+    throw new Error(`Document ${envelope.id} has unsigned required fields`);
+  }
+
+  if (isResealing) {
+    // If we're resealing we want to use the initial data for the document
+    // so we aren't placing fields on top of eachother.
+    envelopeItems = envelopeItems.map((envelopeItem) => ({
+      ...envelopeItem,
+      documentData: {
+        ...envelopeItem.documentData,
+        data: envelopeItem.documentData.initialData,
+      },
+    }));
+  }
+
+  if (!envelope.qrToken) {
+    await prisma.envelope.update({
+      where: {
+        id: envelope.id,
+      },
+      data: {
+        qrToken: prefixedId('qr'),
+      },
     });
+  }
 
-    const newDocumentData: Array<{ oldDocumentDataId: string; newDocumentDataId: string }> = [];
+  const legacyDocumentId = mapSecondaryIdToDocumentId(envelope.secondaryId);
 
-    for (const envelopeItem of envelopeItems) {
+  const { certificateData, auditLogData } = await getCertificateAndAuditLogData({
+    legacyDocumentId,
+    documentMeta: envelope.documentMeta,
+    settings,
+  });
+
+  // !: The commented out code is our desired implementation but we're seemingly
+  // !: running into issues with inngest parallelism in production.
+  // !: Until this is resolved we will do this sequentially which is slower but
+  // !: will actually work.
+  // const decoratePromises: Array<Promise<{ oldDocumentDataId: string; newDocumentDataId: string }>> =
+  //   [];
+
+  // for (const envelopeItem of envelopeItems) {
+  //   const task = io.runTask(`decorate-${envelopeItem.id}`, async () => {
+  //     const envelopeItemFields = envelope.envelopeItems.find(
+  //       (item) => item.id === envelopeItem.id,
+  //     )?.field;
+
+  //     if (!envelopeItemFields) {
+  //       throw new Error(`Envelope item fields not found for envelope item ${envelopeItem.id}`);
+  //     }
+
+  //     return decorateAndSignPdf({
+  //       envelope,
+  //       envelopeItem,
+  //       envelopeItemFields,
+  //       isRejected,
+  //       rejectionReason,
+  //       certificateData,
+  //       auditLogData,
+  //     });
+  //   });
+
+  //   decoratePromises.push(task);
+  // }
+
+  // const newDocumentData = await Promise.all(decoratePromises);
+
+  // TODO: Remove once parallelization is working
+  const newDocumentData: Array<{ oldDocumentDataId: string; newDocumentDataId: string }> = [];
+
+  for (const envelopeItem of envelopeItems) {
+    const result = await io.runTask(`decorate-${envelopeItem.id}`, async () => {
       const envelopeItemFields = envelope.envelopeItems.find(
         (item) => item.id === envelopeItem.id,
       )?.field;
@@ -183,7 +235,7 @@ export const run = async ({
         throw new Error(`Envelope item fields not found for envelope item ${envelopeItem.id}`);
       }
 
-      const result = await decorateAndSignPdf({
+      return decorateAndSignPdf({
         envelope,
         envelopeItem,
         envelopeItemFields,
@@ -192,10 +244,25 @@ export const run = async ({
         certificateData,
         auditLogData,
       });
+    });
 
-      newDocumentData.push(result);
-    }
+    newDocumentData.push(result);
+  }
 
+  const postHog = PostHogServerClient();
+
+  if (postHog) {
+    postHog.capture({
+      distinctId: nanoid(),
+      event: 'App: Document Sealed',
+      properties: {
+        documentId: envelope.id,
+        isRejected,
+      },
+    });
+  }
+
+  await io.runTask('update-document', async () => {
     await prisma.$transaction(async (tx) => {
       for (const { oldDocumentDataId, newDocumentDataId } of newDocumentData) {
         const newData = await tx.documentData.findFirstOrThrow({
@@ -237,24 +304,18 @@ export const run = async ({
         }),
       });
     });
-
-    return {
-      envelopeId: envelope.id,
-      envelopeStatus: envelope.status,
-      isRejected,
-    };
   });
 
   await io.runTask('send-completed-email', async () => {
     let shouldSendCompletedEmail = sendEmail && !isResealing && !isRejected;
 
-    if (isResealing && !isDocumentCompleted(envelopeStatus)) {
+    if (isResealing && !isDocumentCompleted(envelope.status)) {
       shouldSendCompletedEmail = sendEmail;
     }
 
     if (shouldSendCompletedEmail) {
       await sendCompletedEmail({
-        id: { type: 'envelopeId', id: envelopeId },
+        id: { type: 'envelopeId', id: envelope.id },
         requestMetadata,
       });
     }
@@ -262,7 +323,7 @@ export const run = async ({
 
   const updatedEnvelope = await prisma.envelope.findFirstOrThrow({
     where: {
-      id: envelopeId,
+      id: envelope.id,
     },
     include: {
       documentMeta: true,

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/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 {