perf: add database indexes for insights queries

## Description

Adds the following languages since we updated our PDF sealing to support
special characters
- Japanese
- Korean
- Chinese (Simplified)

## Tests

Ran through the signing process in these new languages.

apps/remix/app/components/general/envelope-editor/envelope-editor-fields-drag-drop.tsx

@@ -278,20 +278,8 @@ export const EnvelopeEditorFieldDragDrop = ({
             onMouseDown={() => setSelectedField(field.type)}
             data-selected={selectedField === field.type ? true : undefined}
             className={cn(
-              'group flex h-12 cursor-pointer items-center justify-center rounded-lg border px-4 transition-colors',
+              'border-border group flex h-12 cursor-pointer items-center justify-center rounded-lg border px-4 transition-colors',
               RECIPIENT_COLOR_STYLES[selectedRecipientColor].fieldButton,
-              {
-                'text-recipient-green border-recipient-green/30':
-                  selectedRecipientColor === 'green',
-                'text-recipient-blue border-recipient-blue/30': selectedRecipientColor === 'blue',
-                'text-recipient-purple border-recipient-purple/30':
-                  selectedRecipientColor === 'purple',
-                'text-recipient-orange border-recipient-orange/30':
-                  selectedRecipientColor === 'orange',
-                'text-recipient-yellow border-recipient-yellow/30':
-                  selectedRecipientColor === 'yellow',
-                'text-recipient-pink border-recipient-pink/30': selectedRecipientColor === 'pink',
-              },
             )}
           >
             <p
@@ -299,12 +287,12 @@ export const EnvelopeEditorFieldDragDrop = ({
                 'text-muted-foreground font-noto group-data-[selected]:text-foreground flex items-center justify-center gap-x-1.5 text-sm font-normal',
                 field.className,
                 {
-                  'text-recipient-green': selectedRecipientColor === 'green',
-                  'text-recipient-blue': selectedRecipientColor === 'blue',
-                  'text-recipient-purple': selectedRecipientColor === 'purple',
-                  'text-recipient-orange': selectedRecipientColor === 'orange',
-                  'text-recipient-yellow': selectedRecipientColor === 'yellow',
-                  'text-recipient-pink': selectedRecipientColor === 'pink',
+                  'group-hover:text-recipient-green': selectedRecipientColor === 'green',
+                  'group-hover:text-recipient-blue': selectedRecipientColor === 'blue',
+                  'group-hover:text-recipient-purple': selectedRecipientColor === 'purple',
+                  'group-hover:text-recipient-orange': selectedRecipientColor === 'orange',
+                  'group-hover:text-recipient-yellow': selectedRecipientColor === 'yellow',
+                  'group-hover:text-recipient-pink': selectedRecipientColor === 'pink',
                 },
               )}
             >

apps/remix/app/components/general/envelope-editor/envelope-editor-fields-page.tsx

@@ -5,7 +5,7 @@ import { msg } from '@lingui/core/macro';
 import { Trans, useLingui } from '@lingui/react/macro';
 import { FieldType, RecipientRole } from '@prisma/client';
 import { FileTextIcon } from 'lucide-react';
-import { Link } from 'react-router';
+import { Link, useSearchParams } from 'react-router';
 import { isDeepEqual } from 'remeda';
 import { match } from 'ts-pattern';
 
@@ -65,6 +65,8 @@ const FieldSettingsTypeTranslations: Record<FieldType, MessageDescriptor> = {
 };
 
 export const EnvelopeEditorFieldsPage = () => {
+  const [searchParams] = useSearchParams();
+
   const { envelope, editorFields, relativePath } = useCurrentEnvelopeEditor();
 
   const { currentEnvelopeItem } = useCurrentEnvelopeRender();
@@ -106,13 +108,6 @@ export const EnvelopeEditorFieldsPage = () => {
     editorFields.setSelectedRecipient(firstSelectableRecipient?.id ?? null);
   }, []);
 
-  const canRecipientBeModified = useMemo(
-    () =>
-      editorFields.selectedRecipient !== null &&
-      canRecipientFieldsBeModified(editorFields.selectedRecipient, envelope.fields),
-    [editorFields.selectedRecipient, envelope.fields],
-  );
-
   return (
     <div className="relative flex h-full">
       <div className="flex w-full flex-col overflow-y-auto">
@@ -182,35 +177,32 @@ export const EnvelopeEditorFieldsPage = () => {
               align="end"
             />
 
-            {!canRecipientBeModified && (
-              <Alert className="mt-4" variant="warning">
-                <AlertDescription>
-                  <Trans>
-                    This recipient can no longer be modified as they have signed a field, or
-                    completed the document.
-                  </Trans>
-                </AlertDescription>
-              </Alert>
-            )}
+            {editorFields.selectedRecipient &&
+              !canRecipientFieldsBeModified(editorFields.selectedRecipient, envelope.fields) && (
+                <Alert className="mt-4" variant="warning">
+                  <AlertDescription>
+                    <Trans>
+                      This recipient can no longer be modified as they have signed a field, or
+                      completed the document.
+                    </Trans>
+                  </AlertDescription>
+                </Alert>
+              )}
           </section>
 
-          {canRecipientBeModified && (
-            <>
-              <Separator className="my-4" />
+          <Separator className="my-4" />
 
-              {/* Add fields section. */}
-              <section className="px-4">
-                <h3 className="text-foreground mb-2 text-sm font-semibold">
-                  <Trans>Add Fields</Trans>
-                </h3>
+          {/* Add fields section. */}
+          <section className="px-4">
+            <h3 className="text-foreground mb-2 text-sm font-semibold">
+              <Trans>Add Fields</Trans>
+            </h3>
 
-                <EnvelopeEditorFieldDragDrop
-                  selectedRecipientId={editorFields.selectedRecipient?.id ?? null}
-                  selectedEnvelopeItemId={currentEnvelopeItem?.id ?? null}
-                />
-              </section>
-            </>
-          )}
+            <EnvelopeEditorFieldDragDrop
+              selectedRecipientId={editorFields.selectedRecipient?.id ?? null}
+              selectedEnvelopeItemId={currentEnvelopeItem?.id ?? null}
+            />
+          </section>
 
           {/* Field details section. */}
           <AnimateGenericFadeInOut key={editorFields.selectedField?.formId}>
@@ -218,6 +210,37 @@ export const EnvelopeEditorFieldsPage = () => {
               <section>
                 <Separator className="my-4" />
 
+                {searchParams.get('devmode') && (
+                  <>
+                    <div className="px-4">
+                      <h3 className="text-foreground mb-3 text-sm font-semibold">
+                        <Trans>Developer Mode</Trans>
+                      </h3>
+
+                      <div className="bg-muted/50 border-border text-foreground space-y-2 rounded-md border p-3 text-sm">
+                        <p>
+                          <span className="text-muted-foreground min-w-12">Pos X:&nbsp;</span>
+                          {selectedField.positionX.toFixed(2)}
+                        </p>
+                        <p>
+                          <span className="text-muted-foreground min-w-12">Pos Y:&nbsp;</span>
+                          {selectedField.positionY.toFixed(2)}
+                        </p>
+                        <p>
+                          <span className="text-muted-foreground min-w-12">Width:&nbsp;</span>
+                          {selectedField.width.toFixed(2)}
+                        </p>
+                        <p>
+                          <span className="text-muted-foreground min-w-12">Height:&nbsp;</span>
+                          {selectedField.height.toFixed(2)}
+                        </p>
+                      </div>
+                    </div>
+
+                    <Separator className="my-4" />
+                  </>
+                )}
+
                 <div className="[&_label]:text-foreground/70 px-4 [&_label]:text-xs">
                   <h3 className="text-sm font-semibold">
                     {t(FieldSettingsTypeTranslations[selectedField.type])}

apps/remix/package.json

@@ -106,5 +106,5 @@
     "vite-plugin-babel-macros": "^1.0.6",
     "vite-tsconfig-paths": "^5.1.4"
   },
-  "version": "2.0.12"
+  "version": "2.0.13"
 }

package-lock.json

@@ -1,12 +1,12 @@
 {
   "name": "@documenso/root",
-  "version": "2.0.12",
+  "version": "2.0.13",
   "lockfileVersion": 3,
   "requires": true,
   "packages": {
     "": {
       "name": "@documenso/root",
-      "version": "2.0.12",
+      "version": "2.0.13",
       "workspaces": [
         "apps/*",
         "packages/*"
@@ -101,7 +101,7 @@
     },
     "apps/remix": {
       "name": "@documenso/remix",
-      "version": "2.0.12",
+      "version": "2.0.13",
       "dependencies": {
         "@cantoo/pdf-lib": "^2.5.2",
         "@documenso/api": "*",

package.json

@@ -1,6 +1,6 @@
 {
   "private": true,
-  "version": "2.0.12",
+  "version": "2.0.13",
   "scripts": {
     "build": "turbo run build",
     "dev": "turbo run dev --filter=@documenso/remix",

packages/lib/constants/i18n.ts

@@ -1,6 +1,16 @@
 import { z } from 'zod';
 
-export const SUPPORTED_LANGUAGE_CODES = ['de', 'en', 'fr', 'es', 'it', 'pl'] as const;
+export const SUPPORTED_LANGUAGE_CODES = [
+  'de',
+  'en',
+  'fr',
+  'es',
+  'it',
+  'pl',
+  'ja',
+  'ko',
+  'zh',
+] as const;
 
 export const ZSupportedLanguageCodeSchema = z.enum(SUPPORTED_LANGUAGE_CODES).catch('en');
 
@@ -54,6 +64,18 @@ export const SUPPORTED_LANGUAGES: Record<string, SupportedLanguage> = {
     short: 'pl',
     full: 'Polish',
   },
+  ja: {
+    short: 'ja',
+    full: 'Japanese',
+  },
+  ko: {
+    short: 'ko',
+    full: 'Korean',
+  },
+  zh: {
+    short: 'zh',
+    full: 'Chinese',
+  },
 } satisfies Record<SupportedLanguageCodes, SupportedLanguage>;
 
 export const isValidLanguageCode = (code: unknown): code is SupportedLanguageCodes =>

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/server-only/document/viewed-document.ts

@@ -31,26 +31,16 @@ export const viewedDocument = async ({
         type: EnvelopeType.DOCUMENT,
       },
     },
-    include: {
-      envelope: {
-        include: {
-          documentMeta: true,
-          recipients: true,
-        },
-      },
-    },
   });
 
   if (!recipient) {
     return;
   }
 
-  const { envelope } = recipient;
-
   await prisma.documentAuditLog.create({
     data: createDocumentAuditLogData({
       type: DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_VIEWED,
-      envelopeId: envelope.id,
+      envelopeId: recipient.envelopeId,
       user: {
         name: recipient.name,
         email: recipient.email,
@@ -86,7 +76,7 @@ export const viewedDocument = async ({
     await tx.documentAuditLog.create({
       data: createDocumentAuditLogData({
         type: DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_OPENED,
-        envelopeId: envelope.id,
+        envelopeId: recipient.envelopeId,
         user: {
           name: recipient.name,
           email: recipient.email,
@@ -103,6 +93,16 @@ export const viewedDocument = async ({
     });
   });
 
+  const envelope = await prisma.envelope.findUniqueOrThrow({
+    where: {
+      id: recipient.envelopeId,
+    },
+    include: {
+      documentMeta: true,
+      recipients: true,
+    },
+  });
+
   await triggerWebhook({
     event: WebhookTriggerEvents.DOCUMENT_OPENED,
     data: ZWebhookDocumentSchema.parse(mapEnvelopeToWebhookDocumentPayload(envelope)),

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/lib/recipient-colors.ts

@@ -39,7 +39,7 @@ export const RECIPIENT_COLOR_STYLES = {
     baseRing: 'rgba(122, 195, 85, 1)',
     baseRingHover: 'rgba(122, 195, 85, 0.3)',
     baseTextHover: 'rgba(122, 195, 85, 1)',
-    fieldButton: 'hover:border-recipient-green bg-recipient-green/5 hover:bg-recipient-green/30 ',
+    fieldButton: 'hover:border-recipient-green hover:bg-recipient-green/30 ',
     fieldItem: 'group/field-item rounded-[2px]',
     fieldItemInitials: 'group-hover/field-item:bg-recipient-green',
     comboxBoxTrigger:
@@ -52,7 +52,7 @@ export const RECIPIENT_COLOR_STYLES = {
     baseRing: 'rgba(56, 123, 199, 1)',
     baseRingHover: 'rgba(56, 123, 199, 0.3)',
     baseTextHover: 'rgba(56, 123, 199, 1)',
-    fieldButton: 'hover:border-recipient-blue bg-recipient-blue/5 hover:bg-recipient-blue/30',
+    fieldButton: 'hover:border-recipient-blue hover:bg-recipient-blue/30',
     fieldItem: 'group/field-item rounded-[2px]',
     fieldItemInitials: 'group-hover/field-item:bg-recipient-blue',
     comboxBoxTrigger:
@@ -65,7 +65,7 @@ export const RECIPIENT_COLOR_STYLES = {
     baseRing: 'rgba(151, 71, 255, 1)',
     baseRingHover: 'rgba(151, 71, 255, 0.3)',
     baseTextHover: 'rgba(151, 71, 255, 1)',
-    fieldButton: 'hover:border-recipient-purple bg-recipient-purple/5 hover:bg-recipient-purple/30',
+    fieldButton: 'hover:border-recipient-purple hover:bg-recipient-purple/30',
     fieldItem: 'group/field-item rounded-[2px]',
     fieldItemInitials: 'group-hover/field-item:bg-recipient-purple',
     comboxBoxTrigger:
@@ -78,7 +78,7 @@ export const RECIPIENT_COLOR_STYLES = {
     baseRing: 'rgba(246, 159, 30, 1)',
     baseRingHover: 'rgba(246, 159, 30, 0.3)',
     baseTextHover: 'rgba(246, 159, 30, 1)',
-    fieldButton: 'hover:border-recipient-orange bg-recipient-orange/5 hover:bg-recipient-orange/30',
+    fieldButton: 'hover:border-recipient-orange hover:bg-recipient-orange/30',
     fieldItem: 'group/field-item rounded-[2px]',
     fieldItemInitials: 'group-hover/field-item:bg-recipient-orange',
     comboxBoxTrigger:
@@ -91,7 +91,7 @@ export const RECIPIENT_COLOR_STYLES = {
     baseRing: 'rgba(219, 186, 0, 1)',
     baseRingHover: 'rgba(219, 186, 0, 0.3)',
     baseTextHover: 'rgba(219, 186, 0, 1)',
-    fieldButton: 'hover:border-recipient-yellow bg-recipient-yellow/5 hover:bg-recipient-yellow/30',
+    fieldButton: 'hover:border-recipient-yellow hover:bg-recipient-yellow/30',
     fieldItem: 'group/field-item rounded-[2px]',
     fieldItemInitials: 'group-hover/field-item:bg-recipient-yellow',
     comboxBoxTrigger:
@@ -104,7 +104,7 @@ export const RECIPIENT_COLOR_STYLES = {
     baseRing: 'rgba(217, 74, 186, 1)',
     baseRingHover: 'rgba(217, 74, 186, 0.3)',
     baseTextHover: 'rgba(217, 74, 186, 1)',
-    fieldButton: 'hover:border-recipient-pink bg-recipient-pink/5 hover:bg-recipient-pink/30',
+    fieldButton: 'hover:border-recipient-pink hover:bg-recipient-pink/30',
     fieldItem: 'group/field-item rounded-[2px]',
     fieldItemInitials: 'group-hover/field-item:bg-recipient-pink',
     comboxBoxTrigger: