docs: add missing environment variables to self-hosting guide

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

@@ -275,7 +275,15 @@ 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)                                    |
@@ -297,6 +305,7 @@ 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.                                              |
@@ -308,6 +317,7 @@ 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,7 +25,6 @@ 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';
@@ -62,171 +61,120 @@ export const run = async ({
 }) => {
   const { documentId, sendEmail = true, isResealing = false, requestMetadata } = payload;
 
-  const envelope = await prisma.envelope.findFirstOrThrow({
+  const { envelopeId, envelopeStatus, isRejected } = await io.runTask('seal-document', async () => {
-    where: {
+    const envelope = await prisma.envelope.findFirstOrThrow({
-      type: EnvelopeType.DOCUMENT,
+      where: {
-      secondaryId: mapDocumentIdToSecondaryId(documentId),
+        type: EnvelopeType.DOCUMENT,
-    },
+        secondaryId: mapDocumentIdToSecondaryId(documentId),
-    include: {
+      },
-      documentMeta: true,
+      include: {
-      recipients: true,
+        documentMeta: true,
-      envelopeItems: {
+        recipients: true,
-        include: {
+        envelopeItems: {
-          documentData: true,
+          include: {
-          field: {
+            documentData: true,
-            include: {
+            field: {
-              signature: true,
+              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',
     });
-  }
 
-  // Seems silly but we need to do this in case the job is re-ran
+    if (envelope.envelopeItems.length === 0) {
-  // after it has already run through the update task further below.
+      throw new Error('At least one envelope item required');
-  // eslint-disable-next-line @typescript-eslint/require-await
+    }
-  const documentStatus = await io.runTask('get-document-status', async () => {
-    return envelope.status;
-  });
 
-  // This is the same case as above.
+    const settings = await getTeamSettings({
-  let envelopeItems = await io.runTask(
+      userId: envelope.userId,
-    'get-document-data-id',
+      teamId: envelope.teamId,
-    // 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 (envelopeItems.length < 1) {
+    const isComplete =
-    throw new Error(`Document ${envelope.id} has no envelope items`);
+      envelope.recipients.some((recipient) => recipient.signingStatus === SigningStatus.REJECTED) ||
-  }
+      envelope.recipients.every((recipient) => recipient.signingStatus === SigningStatus.SIGNED);
 
-  const recipients = await prisma.recipient.findMany({
+    if (!isComplete) {
-    where: {
+      throw new AppError(AppErrorCode.UNKNOWN_ERROR, {
-      envelopeId: envelope.id,
+        message: 'Document is not complete',
-      role: {
+      });
-        not: RecipientRole.CC,
+    }
-      },
-    },
-  });
 
-  // Determine if the document has been rejected by checking if any recipient has rejected it
+    let envelopeItems = envelope.envelopeItems;
-  const rejectedRecipient = recipients.find(
-    (recipient) => recipient.signingStatus === SigningStatus.REJECTED,
-  );
 
-  const isRejected = Boolean(rejectedRecipient);
+    if (envelopeItems.length < 1) {
+      throw new Error(`Document ${envelope.id} has no envelope items`);
+    }
 
-  // Get the rejection reason from the rejected recipient
+    const recipients = await prisma.recipient.findMany({
-  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,
+        envelopeId: envelope.id,
-      },
+        role: {
-      data: {
+          not: RecipientRole.CC,
-        qrToken: prefixedId('qr'),
+        },
       },
     });
-  }
 
-  const legacyDocumentId = mapSecondaryIdToDocumentId(envelope.secondaryId);
+    // 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 { certificateData, auditLogData } = await getCertificateAndAuditLogData({
+    const isRejected = Boolean(rejectedRecipient);
-    legacyDocumentId,
-    documentMeta: envelope.documentMeta,
-    settings,
-  });
 
-  // !: The commented out code is our desired implementation but we're seemingly
+    // Get the rejection reason from the rejected recipient
-  // !: running into issues with inngest parallelism in production.
+    const rejectionReason = rejectedRecipient?.rejectionReason ?? '';
-  // !: 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 fields = await prisma.field.findMany({
-  //   const task = io.runTask(`decorate-${envelopeItem.id}`, async () => {
+      where: {
-  //     const envelopeItemFields = envelope.envelopeItems.find(
+        envelopeId: envelope.id,
-  //       (item) => item.id === envelopeItem.id,
+      },
-  //     )?.field;
+      include: {
+        signature: true,
+      },
+    });
 
-  //     if (!envelopeItemFields) {
+    // Skip the field check if the document is rejected
-  //       throw new Error(`Envelope item fields not found for envelope item ${envelopeItem.id}`);
+    if (!isRejected && fieldsContainUnsignedRequiredField(fields)) {
-  //     }
+      throw new Error(`Document ${envelope.id} has unsigned required fields`);
+    }
 
-  //     return decorateAndSignPdf({
+    if (isResealing) {
-  //       envelope,
+      // If we're resealing we want to use the initial data for the document
-  //       envelopeItem,
+      // so we aren't placing fields on top of eachother.
-  //       envelopeItemFields,
+      envelopeItems = envelopeItems.map((envelopeItem) => ({
-  //       isRejected,
+        ...envelopeItem,
-  //       rejectionReason,
+        documentData: {
-  //       certificateData,
+          ...envelopeItem.documentData,
-  //       auditLogData,
+          data: envelopeItem.documentData.initialData,
-  //     });
+        },
-  //   });
+      }));
+    }
 
-  //   decoratePromises.push(task);
+    if (!envelope.qrToken) {
-  // }
+      await prisma.envelope.update({
+        where: {
+          id: envelope.id,
+        },
+        data: {
+          qrToken: prefixedId('qr'),
+        },
+      });
+    }
 
-  // const newDocumentData = await Promise.all(decoratePromises);
+    const legacyDocumentId = mapSecondaryIdToDocumentId(envelope.secondaryId);
 
-  // TODO: Remove once parallelization is working
+    const { certificateData, auditLogData } = await getCertificateAndAuditLogData({
-  const newDocumentData: Array<{ oldDocumentDataId: string; newDocumentDataId: string }> = [];
+      legacyDocumentId,
+      documentMeta: envelope.documentMeta,
+      settings,
+    });
 
-  for (const envelopeItem of envelopeItems) {
+    const newDocumentData: Array<{ oldDocumentDataId: string; newDocumentDataId: string }> = [];
-    const result = await io.runTask(`decorate-${envelopeItem.id}`, async () => {
+
+    for (const envelopeItem of envelopeItems) {
       const envelopeItemFields = envelope.envelopeItems.find(
         (item) => item.id === envelopeItem.id,
       )?.field;
@@ -235,7 +183,7 @@ export const run = async ({
         throw new Error(`Envelope item fields not found for envelope item ${envelopeItem.id}`);
       }
 
-      return decorateAndSignPdf({
+      const result = await decorateAndSignPdf({
         envelope,
         envelopeItem,
         envelopeItemFields,
@@ -244,25 +192,10 @@ 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({
@@ -304,18 +237,24 @@ 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(envelope.status)) {
+    if (isResealing && !isDocumentCompleted(envelopeStatus)) {
       shouldSendCompletedEmail = sendEmail;
     }
 
     if (shouldSendCompletedEmail) {
       await sendCompletedEmail({
-        id: { type: 'envelopeId', id: envelope.id },
+        id: { type: 'envelopeId', id: envelopeId },
         requestMetadata,
       });
     }
@@ -323,7 +262,7 @@ export const run = async ({
 
   const updatedEnvelope = await prisma.envelope.findFirstOrThrow({
     where: {
-      id: envelope.id,
+      id: envelopeId,
     },
     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((eb) => [
+    .select([
-      't.id',
+      't.id as id',
-      't.name',
+      't.name as name',
-      't.createdAt',
+      't.createdAt as createdAt',
-      eb
+      sql<number>`COUNT(DISTINCT om."userId")`.as('memberCount'),
-        .selectFrom('TeamGroup as tg')
+      (createdAtFrom
-        .innerJoin('OrganisationGroup as og', 'og.id', 'tg.organisationGroupId')
+        ? sql<number>`COUNT(DISTINCT CASE WHEN e.id IS NOT NULL AND e."createdAt" >= ${createdAtFrom} THEN e.id END)`
-        .innerJoin('OrganisationGroupMember as ogm', 'ogm.groupId', 'og.id')
+        : sql<number>`COUNT(DISTINCT e.id)`
-        .innerJoin('OrganisationMember as om', 'om.id', 'ogm.organisationMemberId')
+      ).as('documentCount'),
-        .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,38 +164,48 @@ async function getUserInsights(
   perPage: number,
   createdAtFrom: Date | null,
 ): Promise<OrganisationDetailedInsights> {
-  const usersQuery = kyselyPrisma.$kysely
+  const usersBase = kyselyPrisma.$kysely
     .selectFrom('OrganisationMember as om')
     .innerJoin('User as u', 'u.id', 'om.userId')
     .where('om.organisationId', '=', organisationId)
-    .select((eb) => [
+    .leftJoin('Envelope as e', (join) =>
-      'u.id',
+      join
-      'u.name',
+        .onRef('e.userId', '=', 'u.id')
-      'u.email',
+        .on('e.deletedAt', 'is', null)
-      'u.createdAt',
+        .on('e.type', '=', sql.lit(EnvelopeType.DOCUMENT)),
-      eb
+    )
-        .selectFrom('Envelope as e')
+    .leftJoin('Team as td', (join) =>
-        .innerJoin('Team as t', 't.id', 'e.teamId')
+      join.onRef('td.id', '=', 'e.teamId').on('td.organisationId', '=', organisationId),
-        .whereRef('e.userId', '=', 'u.id')
+    )
-        .where('t.organisationId', '=', organisationId)
+    .leftJoin('Recipient as r', (join) =>
-        .where('e.deletedAt', 'is', null)
+      join.onRef('r.email', '=', 'u.email').on('r.signedAt', 'is not', null),
-        .where('e.type', '=', sql.lit(EnvelopeType.DOCUMENT))
+    )
-        .$if(!!createdAtFrom, (qb) => qb.where('e.createdAt', '>=', createdAtFrom!))
+    .leftJoin('Envelope as se', (join) =>
-        .select(sql<number>`count(e.id)`.as('count'))
+      join
-        .as('documentCount'),
+        .onRef('se.id', '=', 'r.envelopeId')
-      eb
+        .on('se.deletedAt', 'is', null)
-        .selectFrom('Recipient as r')
+        .on('se.type', '=', sql.lit(EnvelopeType.DOCUMENT)),
-        .innerJoin('Envelope as e', 'e.id', 'r.envelopeId')
+    )
-        .innerJoin('Team as t', 't.id', 'e.teamId')
+    .leftJoin('Team as ts', (join) =>
-        .whereRef('r.email', '=', 'u.email')
+      join.onRef('ts.id', '=', 'se.teamId').on('ts.organisationId', '=', organisationId),
-        .where('r.signedAt', 'is not', null)
+    );
-        .where('t.organisationId', '=', organisationId)
+
-        .where('e.deletedAt', 'is', null)
+  const usersQuery = usersBase
-        .where('e.type', '=', sql.lit(EnvelopeType.DOCUMENT))
+    .select([
-        .$if(!!createdAtFrom, (qb) => qb.where('e.createdAt', '>=', createdAtFrom!))
+      'u.id as id',
-        .select(sql<number>`count(e.id)`.as('count'))
+      'u.name as name',
-        .as('signedDocumentCount'),
+      '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'),
     ])
+    .groupBy(['u.id', 'u.name', 'u.email', 'u.createdAt'])
     .orderBy('u.createdAt', 'desc')
     .limit(perPage)
     .offset(offset);
@@ -282,51 +292,72 @@ async function getOrganisationSummary(
   organisationId: string,
   createdAtFrom: Date | null,
 ): Promise<OrganisationSummary> {
-  const teamCountQuery = kyselyPrisma.$kysely
+  const summaryQuery = kyselyPrisma.$kysely
-    .selectFrom('Team')
+    .selectFrom('Organisation as o')
-    .where('organisationId', '=', organisationId)
+    .where('o.id', '=', organisationId)
-    .select(sql<number>`count(id)`.as('count'))
-    .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>`(SELECT COUNT(DISTINCT t2.id) FROM "Team" AS t2 WHERE t2."organisationId" = o.id)`.as(
-      sql<number>`count(case when e.status in ('DRAFT', 'PENDING') then 1 end)`.as(
+        'totalTeams',
-        'activeDocuments',
       ),
-      sql<number>`count(case when e.status = 'COMPLETED' then 1 end)`.as('completedDocuments'),
+      sql<number>`(SELECT COUNT(DISTINCT om2."userId") FROM "OrganisationMember" AS om2 WHERE om2."organisationId" = o.id)`.as(
-      sql<number>`count(case when e.status = 'COMPLETED' then 1 end)`.as('volumeAllTime'),
+        '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>`count(case when e.status = 'COMPLETED' and e."createdAt" >= ${createdAtFrom} then 1 end)`
+        ? sql<number>`(
-        : sql<number>`count(case when e.status = 'COMPLETED' then 1 end)`
+            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>`(
-    .executeTakeFirst();
+        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 [teamCount, memberCount, envelopeStats] = await Promise.all([
+  const result = await summaryQuery.executeTakeFirst();
-    teamCountQuery,
-    memberCountQuery,
-    envelopeStatsQuery,
-  ]);
 
   return {
-    totalTeams: Number(teamCount?.count || 0),
+    totalTeams: Number(result?.totalTeams || 0),
-    totalMembers: Number(memberCount?.count || 0),
+    totalMembers: Number(result?.totalMembers || 0),
-    totalDocuments: Number(envelopeStats?.totalDocuments || 0),
+    totalDocuments: Number(result?.totalDocuments || 0),
-    activeDocuments: Number(envelopeStats?.activeDocuments || 0),
+    activeDocuments: Number(result?.activeDocuments || 0),
-    completedDocuments: Number(envelopeStats?.completedDocuments || 0),
+    completedDocuments: Number(result?.completedDocuments || 0),
-    volumeThisPeriod: Number(envelopeStats?.volumeThisPeriod || 0),
+    volumeThisPeriod: Number(result?.volumeThisPeriod || 0),
-    volumeAllTime: Number(envelopeStats?.volumeAllTime || 0),
+    volumeAllTime: Number(result?.volumeAllTime || 0),
   };
 }

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

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

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

@@ -1,29 +0,0 @@
--- 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,10 +433,6 @@ 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 {
@@ -589,9 +585,6 @@ model Recipient {
 
   @@index([envelopeId])
   @@index([token])
-  @@index([email])
-  @@index([signedAt])
-  @@index([envelopeId, signedAt])
 }
 
 enum FieldType {
@@ -701,8 +694,6 @@ model Organisation {
 
   organisationAuthenticationPortalId String                           @unique
   organisationAuthenticationPortal   OrganisationAuthenticationPortal @relation(fields: [organisationAuthenticationPortalId], references: [id])
-
-  @@index([name])
 }
 
 model OrganisationMember {
@@ -719,7 +710,6 @@ model OrganisationMember {
   organisationGroupMembers OrganisationGroupMember[]
 
   @@unique([userId, organisationId])
-  @@index([organisationId])
 }
 
 model OrganisationMemberInvite {
@@ -894,7 +884,6 @@ model Team {
   teamGlobalSettings   TeamGlobalSettings @relation(fields: [teamGlobalSettingsId], references: [id], onDelete: Cascade)
 
   @@index([organisationId])
-  @@index([organisationId, name])
 }
 
 model TeamEmail {