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-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();
@@ -208,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/*"
@@ -19,7 +19,6 @@
         "inngest-cli": "^0.29.1",
         "luxon": "^3.5.0",
         "mupdf": "^1.0.0",
-        "pdf2json": "^4.0.0",
         "react": "^18",
         "typescript": "5.6.2",
         "zod": "^3.25.76"
@@ -102,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": "*",
@@ -27502,18 +27501,6 @@
       "dev": true,
       "license": "MIT"
     },
-    "node_modules/pdf2json": {
-      "version": "4.0.0",
-      "resolved": "https://registry.npmjs.org/pdf2json/-/pdf2json-4.0.0.tgz",
-      "integrity": "sha512-WkezNsLK8sGpuFC7+PPP0DsXROwdoOxmXPBTtUWWkCwCi/Vi97MRC52Ly6FWIJjOKIywpm/L2oaUgSrmtU+7ZQ==",
-      "license": "Apache-2.0",
-      "bin": {
-        "pdf2json": "bin/pdf2json.js"
-      },
-      "engines": {
-        "node": ">=20.18.0"
-      }
-    },
     "node_modules/pdfjs-dist": {
       "version": "3.11.174",
       "resolved": "https://registry.npmjs.org/pdfjs-dist/-/pdfjs-dist-3.11.174.tgz",

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",
@@ -86,7 +86,6 @@
     "inngest-cli": "^0.29.1",
     "luxon": "^3.5.0",
     "mupdf": "^1.0.0",
-    "pdf2json": "^4.0.0",
     "react": "^18",
     "typescript": "5.6.2",
     "zod": "^3.25.76"

packages/app-tests/e2e/auto-placing-fields/auto-place-fields-document.spec.ts

@@ -1,129 +0,0 @@
-import { type Page, expect, test } from '@playwright/test';
-import path from 'path';
-
-import { seedUser } from '@documenso/prisma/seed/users';
-
-import { apiSignin } from '../fixtures/authentication';
-
-const SINGLE_PLACEHOLDER_PDF_PATH = path.join(
-  __dirname,
-  '../../../assets/project-proposal-single-recipient.pdf',
-);
-
-const MULTIPLE_PLACEHOLDER_PDF_PATH = path.join(
-  __dirname,
-  '../../../assets/project-proposal-multiple-fields-and-recipients.pdf',
-);
-
-const setupUserAndSignIn = async (page: Page) => {
-  const { user, team } = await seedUser();
-
-  await apiSignin({
-    page,
-    email: user.email,
-    redirectPath: `/t/${team.url}/documents`,
-  });
-
-  return { user, team };
-};
-
-const uploadPdfAndContinue = async (page: Page, pdfPath: string, continueClicks: number = 1) => {
-  const fileInput = page.locator('input[type="file"]').nth(1);
-  await fileInput.waitFor({ state: 'attached' });
-  await fileInput.setInputFiles(pdfPath);
-
-  await page.waitForTimeout(3000);
-
-  for (let i = 0; i < continueClicks; i++) {
-    await page.getByRole('button', { name: 'Continue' }).click();
-  }
-};
-
-test.describe('PDF Placeholders with single recipient', () => {
-  test('[AUTO_PLACING_FIELDS]: should automatically create recipients from PDF placeholders', async ({
-    page,
-  }) => {
-    await setupUserAndSignIn(page);
-    await uploadPdfAndContinue(page, SINGLE_PLACEHOLDER_PDF_PATH, 1);
-
-    await expect(page.getByRole('heading', { name: 'Add Signers' })).toBeVisible();
-    await expect(page.getByPlaceholder('Email')).toHaveValue('recipient.1@documenso.com');
-    await expect(page.getByPlaceholder('Name')).toHaveValue('Recipient 1');
-  });
-
-  test('[AUTO_PLACING_FIELDS]: should automatically place fields from PDF placeholders', async ({
-    page,
-  }) => {
-    await setupUserAndSignIn(page);
-    await uploadPdfAndContinue(page, SINGLE_PLACEHOLDER_PDF_PATH, 2);
-
-    await expect(page.getByRole('heading', { name: 'Add Fields' })).toBeVisible();
-
-    await expect(page.locator('[data-field-type="SIGNATURE"]')).toBeVisible();
-    await expect(page.locator('[data-field-type="EMAIL"]')).toBeVisible();
-    await expect(page.locator('[data-field-type="NAME"]')).toBeVisible();
-    await expect(page.locator('[data-field-type="TEXT"]')).toBeVisible();
-  });
-
-  test('[AUTO_PLACING_FIELDS]: should automatically configure fields from PDF placeholders', async ({
-    page,
-  }) => {
-    await setupUserAndSignIn(page);
-    await uploadPdfAndContinue(page, SINGLE_PLACEHOLDER_PDF_PATH, 2);
-
-    await page.getByText('Text').nth(1).click();
-    await page.getByRole('button', { name: 'Advanced settings' }).click();
-
-    await expect(page.getByRole('heading', { name: 'Advanced settings' })).toBeVisible();
-    await expect(
-      page
-        .locator('div')
-        .filter({ hasText: /^Required field$/ })
-        .getByRole('switch'),
-    ).toBeChecked();
-
-    await expect(page.getByRole('combobox')).toHaveText('Right');
-  });
-});
-
-test.describe('PDF Placeholders with multiple recipients', () => {
-  test('[AUTO_PLACING_FIELDS]: should automatically create recipients from PDF placeholders', async ({
-    page,
-  }) => {
-    await setupUserAndSignIn(page);
-    await uploadPdfAndContinue(page, MULTIPLE_PLACEHOLDER_PDF_PATH, 1);
-
-    await expect(page.getByTestId('signer-email-input').first()).toHaveValue(
-      'recipient.1@documenso.com',
-    );
-    await expect(page.getByLabel('Name').first()).toHaveValue('Recipient 1');
-
-    await expect(page.getByTestId('signer-email-input').nth(1)).toHaveValue(
-      'recipient.2@documenso.com',
-    );
-    await expect(page.getByLabel('Name').nth(1)).toHaveValue('Recipient 2');
-
-    await expect(page.getByTestId('signer-email-input').nth(2)).toHaveValue(
-      'recipient.3@documenso.com',
-    );
-    await expect(page.getByLabel('Name').nth(2)).toHaveValue('Recipient 3');
-  });
-
-  test('[AUTO_PLACING_FIELDS]: should automatically create fields from PDF placeholders', async ({
-    page,
-  }) => {
-    await setupUserAndSignIn(page);
-    await uploadPdfAndContinue(page, MULTIPLE_PLACEHOLDER_PDF_PATH, 2);
-
-    await expect(page.getByRole('heading', { name: 'Add Fields' })).toBeVisible();
-
-    await expect(page.locator('[data-field-type="SIGNATURE"]').first()).toBeVisible();
-    await expect(page.locator('[data-field-type="SIGNATURE"]').nth(1)).toBeVisible();
-    await expect(page.locator('[data-field-type="SIGNATURE"]').nth(2)).toBeVisible();
-    await expect(page.locator('[data-field-type="EMAIL"]').first()).toBeVisible();
-    await expect(page.locator('[data-field-type="EMAIL"]').nth(1)).toBeVisible();
-    await expect(page.locator('[data-field-type="NAME"]')).toBeVisible();
-    await expect(page.locator('[data-field-type="TEXT"]')).toBeVisible();
-    await expect(page.locator('[data-field-type="NUMBER"]')).toBeVisible();
-  });
-});

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/lib/server-only/envelope/create-envelope.ts

@@ -10,11 +10,6 @@ import {
 } from '@prisma/client';
 
 import { AppError, AppErrorCode } from '@documenso/lib/errors/app-error';
-import {
-  extractPlaceholdersFromPDF,
-  insertFieldsFromPlaceholdersInPDF,
-  removePlaceholdersFromPDF,
-} from '@documenso/lib/server-only/pdf/auto-place-fields';
 import { normalizePdf as makeNormalizedPdf } from '@documenso/lib/server-only/pdf/normalize-pdf';
 import { DOCUMENT_AUDIT_LOG_TYPE } from '@documenso/lib/types/document-audit-logs';
 import type { ApiRequestMetadata } from '@documenso/lib/universal/extract-request-metadata';
@@ -39,7 +34,6 @@ import { getFileServerSide } from '../../universal/upload/get-file.server';
 import { putPdfFileServerSide } from '../../universal/upload/put-file.server';
 import { extractDerivedDocumentMeta } from '../../utils/document';
 import { createDocumentAuthOptions, createRecipientAuthOptions } from '../../utils/document-auth';
-import type { EnvelopeIdOptions } from '../../utils/envelope';
 import { buildTeamWhereQuery } from '../../utils/teams';
 import { incrementDocumentId, incrementTemplateId } from '../envelope/increment-id';
 import { getTeamSettings } from '../team/get-team-settings';
@@ -262,7 +256,7 @@ export const createEnvelope = async ({
       ? await incrementDocumentId().then((v) => v.formattedDocumentId)
       : await incrementTemplateId().then((v) => v.formattedTemplateId);
 
-  const createdEnvelope = await prisma.$transaction(async (tx) => {
+  return await prisma.$transaction(async (tx) => {
     const envelope = await tx.envelope.create({
       data: {
         id: prefixedId('envelope'),
@@ -382,12 +376,8 @@ export const createEnvelope = async ({
         recipients: true,
         fields: true,
         folder: true,
+        envelopeItems: true,
         envelopeAttachments: true,
-        envelopeItems: {
-          include: {
-            documentData: true,
-          },
-        },
       },
     });
 
@@ -423,74 +413,4 @@ export const createEnvelope = async ({
 
     return createdEnvelope;
   });
-
-  for (const envelopeItem of createdEnvelope.envelopeItems) {
-    const buffer = await getFileServerSide(envelopeItem.documentData);
-    const pdfToProcess = Buffer.from(buffer);
-
-    const envelopeOptions: EnvelopeIdOptions = {
-      type: 'envelopeId',
-      id: createdEnvelope.id,
-    };
-
-    const placeholders = await extractPlaceholdersFromPDF(pdfToProcess);
-
-    if (placeholders.length > 0) {
-      const pdfWithoutPlaceholders = await removePlaceholdersFromPDF(pdfToProcess);
-
-      await insertFieldsFromPlaceholdersInPDF(
-        pdfWithoutPlaceholders,
-        userId,
-        teamId,
-        envelopeOptions,
-        requestMetadata,
-        envelopeItem.id,
-        createdEnvelope.recipients,
-      );
-
-      const titleToUse = envelopeItem.title || title;
-      const fileName = titleToUse.endsWith('.pdf') ? titleToUse : `${titleToUse}.pdf`;
-
-      const newDocumentData = await putPdfFileServerSide({
-        name: fileName,
-        type: 'application/pdf',
-        arrayBuffer: async () => Promise.resolve(pdfWithoutPlaceholders),
-      });
-
-      await prisma.envelopeItem.update({
-        where: {
-          id: envelopeItem.id,
-        },
-        data: {
-          documentDataId: newDocumentData.id,
-        },
-      });
-    }
-  }
-
-  const finalEnvelope = await prisma.envelope.findFirst({
-    where: {
-      id: createdEnvelope.id,
-    },
-    include: {
-      documentMeta: true,
-      recipients: true,
-      fields: true,
-      folder: true,
-      envelopeAttachments: true,
-      envelopeItems: {
-        include: {
-          documentData: true,
-        },
-      },
-    },
-  });
-
-  if (!finalEnvelope) {
-    throw new AppError(AppErrorCode.NOT_FOUND, {
-      message: 'Envelope not found',
-    });
-  }
-
-  return finalEnvelope;
 };

packages/lib/server-only/pdf/auto-place-fields.ts

@@ -1,347 +0,0 @@
-import { PDFDocument, rgb } from '@cantoo/pdf-lib';
-import type { Recipient } from '@prisma/client';
-import PDFParser from 'pdf2json';
-
-import { AppError, AppErrorCode } from '@documenso/lib/errors/app-error';
-import { getEnvelopeWhereInput } from '@documenso/lib/server-only/envelope/get-envelope-by-id';
-import { createEnvelopeFields } from '@documenso/lib/server-only/field/create-envelope-fields';
-import { type TFieldAndMeta, ZFieldAndMetaSchema } from '@documenso/lib/types/field-meta';
-import type { ApiRequestMetadata } from '@documenso/lib/universal/extract-request-metadata';
-import type { EnvelopeIdOptions } from '@documenso/lib/utils/envelope';
-import { prisma } from '@documenso/prisma';
-
-import { getPageSize } from './get-page-size';
-import {
-  determineRecipientsForPlaceholders,
-  extractRecipientPlaceholder,
-  findRecipientByPlaceholder,
-  parseFieldMetaFromPlaceholder,
-  parseFieldTypeFromPlaceholder,
-} from './helpers';
-
-const PLACEHOLDER_REGEX = /{{([^}]+)}}/g;
-const DEFAULT_FIELD_HEIGHT_PERCENT = 2;
-const WIDTH_ADJUSTMENT_FACTOR = 0.1;
-const MIN_HEIGHT_THRESHOLD = 0.01;
-
-type TextPosition = {
-  text: string;
-  x: number;
-  y: number;
-  w: number;
-};
-
-type CharIndexMapping = {
-  textPositionIndex: number;
-};
-
-type PlaceholderInfo = {
-  placeholder: string;
-  recipient: string;
-  fieldAndMeta: TFieldAndMeta;
-  page: number;
-  x: number;
-  y: number;
-  width: number;
-  height: number;
-  pageWidth: number;
-  pageHeight: number;
-};
-
-type FieldToCreate = TFieldAndMeta & {
-  envelopeItemId?: string;
-  recipientId: number;
-  page: number;
-  positionX: number;
-  positionY: number;
-  width: number;
-  height: number;
-};
-
-export const extractPlaceholdersFromPDF = async (pdf: Buffer): Promise<PlaceholderInfo[]> => {
-  return new Promise((resolve, reject) => {
-    const parser = new PDFParser(null, true);
-
-    parser.on('pdfParser_dataError', (errData) => {
-      reject(errData);
-    });
-
-    parser.on('pdfParser_dataReady', (pdfData) => {
-      const placeholders: PlaceholderInfo[] = [];
-
-      pdfData.Pages.forEach((page, pageIndex) => {
-        /*
-          pdf2json returns the PDF page content as an array of characters.
-          We need to concatenate the characters to get the full text.
-          We also need to get the position of the text so we can place the placeholders in the correct position.
-          
-          Page dimensions from PDF2JSON are in "page units" (relative coordinates)
-        */
-        let pageText = '';
-        const textPositions: TextPosition[] = [];
-        const charIndexMappings: CharIndexMapping[] = [];
-
-        page.Texts.forEach((text) => {
-          /*
-            R is an array of objects containing each character, its position and styling information.
-            The decodedText stores the characters, without any other information.
-
-            textPositions stores each character and its position on the page.
-          */
-          const decodedText = text.R.map((run) => decodeURIComponent(run.T)).join('');
-
-          /*
-            For each character in the decodedText, we store its position in the textPositions array.
-            This allows us to quickly find the position of a character in the textPositions array by its index.
-          */
-          for (let i = 0; i < decodedText.length; i++) {
-            charIndexMappings.push({
-              textPositionIndex: textPositions.length,
-            });
-          }
-
-          pageText += decodedText;
-
-          textPositions.push({
-            text: decodedText,
-            x: text.x,
-            y: text.y,
-            w: text.w || 0,
-          });
-        });
-
-        const placeholderMatches = pageText.matchAll(PLACEHOLDER_REGEX);
-
-        /*
-          A placeholder match has the following format:
-
-          [
-            '{{fieldType,recipient,fieldMeta}}',
-            'fieldType,recipient,fieldMeta',
-            'index: <number>',
-            'input: <pdf-text>'
-          ]
-        */
-        for (const placeholderMatch of placeholderMatches) {
-          const placeholder = placeholderMatch[0];
-          const placeholderData = placeholderMatch[1].split(',').map((property) => property.trim());
-
-          const [fieldTypeString, recipient, ...fieldMetaData] = placeholderData;
-
-          const rawFieldMeta = Object.fromEntries(
-            fieldMetaData.map((property) => property.split('=')),
-          );
-
-          const fieldType = parseFieldTypeFromPlaceholder(fieldTypeString);
-          const parsedFieldMeta = parseFieldMetaFromPlaceholder(rawFieldMeta, fieldType);
-
-          const fieldAndMeta: TFieldAndMeta = ZFieldAndMetaSchema.parse({
-            type: fieldType,
-            fieldMeta: parsedFieldMeta,
-          });
-
-          /*
-            Find the position of where the placeholder starts and ends in the text.
-
-            Then find the position of the characters in the textPositions array.
-            This allows us to quickly find the position of a character in the textPositions array by its index.
-          */
-          const placeholderEndCharIndex = placeholderMatch.index + placeholder.length;
-
-          /*
-            Get the index of the placeholder's first and last character in the textPositions array.
-            Used to retrieve the character information from the textPositions array.
-
-            Example:
-              startTextPosIndex - 1
-              endTextPosIndex - 40
-          */
-          const startTextPosIndex = charIndexMappings[placeholderMatch.index].textPositionIndex;
-          const endTextPosIndex = charIndexMappings[placeholderEndCharIndex - 1].textPositionIndex;
-
-          /*
-            Get the placeholder's first and last character information from the textPositions array.
-
-            Example:
-              placeholderStart = { text: '{', x: 100, y: 100, w: 100 }
-              placeholderEnd = { text: '}', x: 200, y: 100, w: 100 }
-          */
-          const placeholderStart = textPositions[startTextPosIndex];
-          const placeholderEnd = textPositions[endTextPosIndex];
-
-          const width =
-            placeholderEnd.x + placeholderEnd.w * WIDTH_ADJUSTMENT_FACTOR - placeholderStart.x;
-
-          placeholders.push({
-            placeholder,
-            recipient,
-            fieldAndMeta,
-            page: pageIndex + 1,
-            x: placeholderStart.x,
-            y: placeholderStart.y,
-            width,
-            height: 1,
-            pageWidth: page.Width,
-            pageHeight: page.Height,
-          });
-        }
-      });
-
-      resolve(placeholders);
-    });
-
-    parser.parseBuffer(pdf);
-  });
-};
-
-export const removePlaceholdersFromPDF = async (pdf: Buffer): Promise<Buffer> => {
-  const placeholders = await extractPlaceholdersFromPDF(pdf);
-
-  const pdfDoc = await PDFDocument.load(new Uint8Array(pdf));
-  const pages = pdfDoc.getPages();
-
-  for (const placeholder of placeholders) {
-    const pageIndex = placeholder.page - 1;
-    const page = pages[pageIndex];
-
-    const { width: pdfLibPageWidth, height: pdfLibPageHeight } = getPageSize(page);
-
-    /*
-      Convert PDF2JSON coordinates to pdf-lib coordinates:
-      
-      PDF2JSON uses relative "page units":
-      - x, y, width, height are in page units
-      - Page dimensions (Width, Height) are also in page units
-      
-      pdf-lib uses absolute points (1 point = 1/72 inch):
-      - Need to convert from page units to points
-      - Y-axis in pdf-lib is bottom-up (origin at bottom-left)
-      - Y-axis in PDF2JSON is top-down (origin at top-left)
-    */
-
-    const xPoints = (placeholder.x / placeholder.pageWidth) * pdfLibPageWidth;
-    const yPoints = pdfLibPageHeight - (placeholder.y / placeholder.pageHeight) * pdfLibPageHeight;
-    const widthPoints = (placeholder.width / placeholder.pageWidth) * pdfLibPageWidth;
-    const heightPoints = (placeholder.height / placeholder.pageHeight) * pdfLibPageHeight;
-
-    page.drawRectangle({
-      x: xPoints,
-      y: yPoints - heightPoints, // Adjust for height since y is at baseline
-      width: widthPoints,
-      height: heightPoints,
-      color: rgb(1, 1, 1),
-      borderColor: rgb(1, 1, 1),
-      borderWidth: 2,
-    });
-  }
-
-  const modifiedPdfBytes = await pdfDoc.save();
-
-  return Buffer.from(modifiedPdfBytes);
-};
-
-export const insertFieldsFromPlaceholdersInPDF = async (
-  pdf: Buffer,
-  userId: number,
-  teamId: number,
-  envelopeId: EnvelopeIdOptions,
-  requestMetadata: ApiRequestMetadata,
-  envelopeItemId?: string,
-  recipients?: Pick<Recipient, 'id' | 'email'>[],
-): Promise<Buffer> => {
-  const placeholders = await extractPlaceholdersFromPDF(pdf);
-
-  if (placeholders.length === 0) {
-    return pdf;
-  }
-
-  /*
-    A structure that maps the recipient index to the recipient name.
-    Example: 1 => 'Recipient 1'
-  */
-  const recipientPlaceholders = new Map<number, string>();
-
-  for (const placeholder of placeholders) {
-    const { name, recipientIndex } = extractRecipientPlaceholder(placeholder.recipient);
-
-    recipientPlaceholders.set(recipientIndex, name);
-  }
-
-  const { envelopeWhereInput } = await getEnvelopeWhereInput({
-    id: envelopeId,
-    userId,
-    teamId,
-    type: null,
-  });
-
-  const envelope = await prisma.envelope.findFirst({
-    where: envelopeWhereInput,
-    select: {
-      id: true,
-      type: true,
-      secondaryId: true,
-    },
-  });
-
-  if (!envelope) {
-    throw new AppError(AppErrorCode.NOT_FOUND, {
-      message: 'Envelope not found',
-    });
-  }
-
-  const createdRecipients = await determineRecipientsForPlaceholders(
-    recipients,
-    recipientPlaceholders,
-    envelope,
-    userId,
-    teamId,
-    requestMetadata,
-  );
-
-  const fieldsToCreate: FieldToCreate[] = [];
-
-  for (const placeholder of placeholders) {
-    /*
-      Convert PDF2JSON coordinates to percentage-based coordinates (0-100)
-      The UI expects positionX and positionY as percentages, not absolute points
-      PDF2JSON uses relative coordinates: x/pageWidth and y/pageHeight give us the percentage
-    */
-    const xPercent = (placeholder.x / placeholder.pageWidth) * 100;
-    const yPercent = (placeholder.y / placeholder.pageHeight) * 100;
-
-    const widthPercent = (placeholder.width / placeholder.pageWidth) * 100;
-    const heightPercent = (placeholder.height / placeholder.pageHeight) * 100;
-
-    const recipient = findRecipientByPlaceholder(
-      placeholder.recipient,
-      placeholder.placeholder,
-      recipients,
-      createdRecipients,
-    );
-
-    // Default height percentage if too small (use 2% as a reasonable default)
-    const finalHeightPercent =
-      heightPercent > MIN_HEIGHT_THRESHOLD ? heightPercent : DEFAULT_FIELD_HEIGHT_PERCENT;
-
-    fieldsToCreate.push({
-      ...placeholder.fieldAndMeta,
-      envelopeItemId,
-      recipientId: recipient.id,
-      page: placeholder.page,
-      positionX: xPercent,
-      positionY: yPercent,
-      width: widthPercent,
-      height: finalHeightPercent,
-    });
-  }
-
-  await createEnvelopeFields({
-    userId,
-    teamId,
-    id: envelopeId,
-    fields: fieldsToCreate,
-    requestMetadata,
-  });
-
-  return pdf;
-};

packages/lib/server-only/pdf/helpers.ts

@@ -1,266 +0,0 @@
-import { FieldType } from '@prisma/client';
-import { type Envelope, EnvelopeType, RecipientRole } from '@prisma/client';
-import type { Recipient } from '@prisma/client';
-import { match } from 'ts-pattern';
-
-import { AppError, AppErrorCode } from '@documenso/lib/errors/app-error';
-import { createEnvelopeRecipients } from '@documenso/lib/server-only/recipient/create-envelope-recipients';
-import type { ApiRequestMetadata } from '@documenso/lib/universal/extract-request-metadata';
-import { mapSecondaryIdToTemplateId } from '@documenso/lib/utils/envelope';
-import type { EnvelopeIdOptions } from '@documenso/lib/utils/envelope';
-import { prisma } from '@documenso/prisma';
-
-type RecipientPlaceholderInfo = {
-  email: string;
-  name: string;
-  recipientIndex: number;
-};
-
-/*
-  Parse field type string to FieldType enum.
-  Normalizes the input (uppercase, trim) and validates it's a valid field type.
-  This ensures we handle case variations and whitespace, and provides clear error messages.
-*/
-export const parseFieldTypeFromPlaceholder = (fieldTypeString: string): FieldType => {
-  const normalizedType = fieldTypeString.toUpperCase().trim();
-
-  return match(normalizedType)
-    .with('SIGNATURE', () => FieldType.SIGNATURE)
-    .with('FREE_SIGNATURE', () => FieldType.FREE_SIGNATURE)
-    .with('INITIALS', () => FieldType.INITIALS)
-    .with('NAME', () => FieldType.NAME)
-    .with('EMAIL', () => FieldType.EMAIL)
-    .with('DATE', () => FieldType.DATE)
-    .with('TEXT', () => FieldType.TEXT)
-    .with('NUMBER', () => FieldType.NUMBER)
-    .with('RADIO', () => FieldType.RADIO)
-    .with('CHECKBOX', () => FieldType.CHECKBOX)
-    .with('DROPDOWN', () => FieldType.DROPDOWN)
-    .otherwise(() => {
-      throw new AppError(AppErrorCode.INVALID_BODY, {
-        message: `Invalid field type: ${fieldTypeString}`,
-      });
-    });
-};
-
-/*
-  Transform raw field metadata from placeholder format to schema format.
-  Users should provide properly capitalized property names (e.g., readOnly, fontSize, textAlign).
-  Converts string values to proper types (booleans, numbers).
-*/
-export const parseFieldMetaFromPlaceholder = (
-  rawFieldMeta: Record<string, string>,
-  fieldType: FieldType,
-): Record<string, unknown> | undefined => {
-  if (fieldType === FieldType.SIGNATURE || fieldType === FieldType.FREE_SIGNATURE) {
-    return;
-  }
-
-  if (Object.keys(rawFieldMeta).length === 0) {
-    return;
-  }
-
-  const fieldTypeString = String(fieldType).toLowerCase();
-
-  const parsedFieldMeta: Record<string, boolean | number | string> = {
-    type: fieldTypeString,
-  };
-
-  /*
-    rawFieldMeta is an object with string keys and string values.
-    It contains string values because the PDF parser returns the values as strings.
-
-    E.g. { 'required': 'true', 'fontSize': '12', 'maxValue': '100', 'minValue': '0', 'characterLimit': '100' }
-  */
-  const rawFieldMetaEntries = Object.entries(rawFieldMeta);
-
-  for (const [property, value] of rawFieldMetaEntries) {
-    if (property === 'readOnly' || property === 'required') {
-      parsedFieldMeta[property] = value === 'true';
-    } else if (
-      property === 'fontSize' ||
-      property === 'maxValue' ||
-      property === 'minValue' ||
-      property === 'characterLimit'
-    ) {
-      const numValue = Number(value);
-
-      if (!Number.isNaN(numValue)) {
-        parsedFieldMeta[property] = numValue;
-      }
-    } else {
-      parsedFieldMeta[property] = value;
-    }
-  }
-
-  return parsedFieldMeta;
-};
-
-export const extractRecipientPlaceholder = (placeholder: string): RecipientPlaceholderInfo => {
-  const indexMatch = placeholder.match(/^r(\d+)$/i);
-
-  if (!indexMatch) {
-    throw new AppError(AppErrorCode.INVALID_BODY, {
-      message: `Invalid recipient placeholder format: ${placeholder}. Expected format: r1, r2, r3, etc.`,
-    });
-  }
-
-  const recipientIndex = Number(indexMatch[1]);
-
-  return {
-    email: `recipient.${recipientIndex}@documenso.com`,
-    name: `Recipient ${recipientIndex}`,
-    recipientIndex,
-  };
-};
-
-/*
-  Finds a recipient based on a placeholder reference.
-  If recipients array is provided, uses index-based matching (r1 -> recipients[0], etc.).
-  Otherwise, uses email-based matching from createdRecipients.
-*/
-export const findRecipientByPlaceholder = (
-  recipientPlaceholder: string,
-  placeholder: string,
-  recipients: Pick<Recipient, 'id' | 'email'>[] | undefined,
-  createdRecipients: Pick<Recipient, 'id' | 'email'>[],
-): Pick<Recipient, 'id' | 'email'> => {
-  if (recipients && recipients.length > 0) {
-    /*
-      Map placeholder by index: r1 -> recipients[0], r2 -> recipients[1], etc.
-      recipientIndex is 1-based, so we subtract 1 to get the array index.
-    */
-    const { recipientIndex } = extractRecipientPlaceholder(recipientPlaceholder);
-    const recipientArrayIndex = recipientIndex - 1;
-
-    if (recipientArrayIndex < 0 || recipientArrayIndex >= recipients.length) {
-      throw new AppError(AppErrorCode.INVALID_BODY, {
-        message: `Recipient placeholder ${recipientPlaceholder} (index ${recipientIndex}) is out of range. Provided ${recipients.length} recipient(s).`,
-      });
-    }
-
-    return recipients[recipientArrayIndex];
-  }
-
-  /*
-    Use email-based matching for placeholder recipients.
-  */
-  const { email } = extractRecipientPlaceholder(recipientPlaceholder);
-  const recipient = createdRecipients.find((r) => r.email === email);
-
-  if (!recipient) {
-    throw new AppError(AppErrorCode.INVALID_BODY, {
-      message: `Could not find recipient ID for placeholder: ${placeholder}`,
-    });
-  }
-
-  return recipient;
-};
-
-/*
-  Determines the recipients to use for field creation.
-  If recipients are provided, uses them directly.
-  Otherwise, creates recipients from placeholders.
-*/
-export const determineRecipientsForPlaceholders = async (
-  recipients: Pick<Recipient, 'id' | 'email'>[] | undefined,
-  recipientPlaceholders: Map<number, string>,
-  envelope: Pick<Envelope, 'id' | 'type' | 'secondaryId'>,
-  userId: number,
-  teamId: number,
-  requestMetadata: ApiRequestMetadata,
-): Promise<Pick<Recipient, 'id' | 'email'>[]> => {
-  if (recipients && recipients.length > 0) {
-    return recipients;
-  }
-
-  return createRecipientsFromPlaceholders(
-    recipientPlaceholders,
-    envelope,
-    userId,
-    teamId,
-    requestMetadata,
-  );
-};
-
-export const createRecipientsFromPlaceholders = async (
-  recipientPlaceholders: Map<number, string>,
-  envelope: Pick<Envelope, 'id' | 'type' | 'secondaryId'>,
-  userId: number,
-  teamId: number,
-  requestMetadata: ApiRequestMetadata,
-): Promise<Pick<Recipient, 'id' | 'email'>[]> => {
-  const recipientsToCreate = Array.from(
-    recipientPlaceholders.entries(),
-    ([recipientIndex, name]) => {
-      return {
-        email: `recipient.${recipientIndex}@documenso.com`,
-        name,
-        role: RecipientRole.SIGNER,
-        signingOrder: recipientIndex,
-      };
-    },
-  );
-
-  const existingRecipients = await prisma.recipient.findMany({
-    where: {
-      envelopeId: envelope.id,
-    },
-    select: {
-      id: true,
-      email: true,
-    },
-  });
-
-  const existingEmails = new Set(existingRecipients.map((r) => r.email));
-  const recipientsToCreateFiltered = recipientsToCreate.filter(
-    (recipient) => !existingEmails.has(recipient.email),
-  );
-
-  if (recipientsToCreateFiltered.length === 0) {
-    return existingRecipients;
-  }
-
-  const newRecipients = await match(envelope.type)
-    .with(EnvelopeType.DOCUMENT, async () => {
-      const envelopeId: EnvelopeIdOptions = {
-        type: 'envelopeId',
-        id: envelope.id,
-      };
-
-      const { recipients } = await createEnvelopeRecipients({
-        userId,
-        teamId,
-        id: envelopeId,
-        recipients: recipientsToCreateFiltered,
-        requestMetadata,
-      });
-
-      return recipients;
-    })
-    .with(EnvelopeType.TEMPLATE, async () => {
-      const templateId = mapSecondaryIdToTemplateId(envelope.secondaryId ?? '');
-
-      const envelopeId: EnvelopeIdOptions = {
-        type: 'templateId',
-        id: templateId,
-      };
-
-      const { recipients } = await createEnvelopeRecipients({
-        userId,
-        teamId,
-        id: envelopeId,
-        recipients: recipientsToCreateFiltered,
-        requestMetadata,
-      });
-
-      return recipients;
-    })
-    .otherwise(() => {
-      throw new AppError(AppErrorCode.INVALID_BODY, {
-        message: `Invalid envelope type: ${envelope.type}`,
-      });
-    });
-
-  return [...existingRecipients, ...newRecipients];
-};

packages/lib/server-only/pdf/normalize-pdf.ts

@@ -23,7 +23,5 @@ export const normalizePdf = async (pdf: Buffer) => {
   await flattenForm(pdfDoc);
   flattenAnnotations(pdfDoc);
 
-  const normalizedPdfBytes = await pdfDoc.save();
-
-  return Buffer.from(normalizedPdfBytes);
+  return Buffer.from(await pdfDoc.save());
 };

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 {