chore: remove cummulative mau since meaningless

apps/remix/app/routes/_authenticated+/admin+/organisation-insights.$id.tsx

@@ -1,6 +1,10 @@
+import { Trans } from '@lingui/react/macro';
+import { Link } from 'react-router';
+
 import { getOrganisationDetailedInsights } from '@documenso/lib/server-only/admin/get-organisation-detailed-insights';
 import type { DateRange } from '@documenso/lib/types/search-params';
 import { getAdminOrganisation } from '@documenso/trpc/server/admin-router/get-admin-organisation';
+import { Button } from '@documenso/ui/primitives/button';
 
 import { OrganisationInsightsTable } from '~/components/tables/organisation-insights-table';
 
@@ -38,12 +42,17 @@ export async function loader({ params, request }: Route.LoaderArgs) {
 }
 
 export default function OrganisationInsights({ loaderData }: Route.ComponentProps) {
-  const { insights, page, perPage, dateRange, view, organisationName } = loaderData;
+  const { insights, page, perPage, dateRange, view, organisationName, organisationId } = loaderData;
 
   return (
     <div>
       <div className="flex items-center justify-between">
         <h2 className="text-4xl font-semibold">{organisationName}</h2>
+        <Button variant="outline" asChild>
+          <Link to={`/admin/organisations/${organisationId}`}>
+            <Trans>Manage organisation</Trans>
+          </Link>
+        </Button>
       </div>
       <div className="mt-8">
         <OrganisationInsightsTable

apps/remix/app/routes/_authenticated+/admin+/organisation-insights._index.tsx

@@ -44,7 +44,7 @@ export async function loader({ request }: Route.LoaderArgs) {
   const typedOrganisations: OrganisationOverview[] = organisations.map((item) => ({
     id: String(item.id),
     name: item.name || '',
-    signingVolume: item.signingVolume,
+    signingVolume: item.signingVolume || 0,
     createdAt: item.createdAt || new Date(),
     customerId: item.customerId || '',
     subscriptionStatus: item.subscriptionStatus,

apps/remix/app/routes/_authenticated+/admin+/organisations.$id.tsx

@@ -162,7 +162,13 @@ export default function OrganisationGroupSettingsPage({ params }: Route.Componen
       <SettingsHeader
         title={t`Manage organisation`}
         subtitle={t`Manage the ${organisation.name} organisation`}
-      />
+      >
+        <Button variant="outline" asChild>
+          <Link to={`/admin/organisation-insights/${organisationId}`}>
+            <Trans>View insights</Trans>
+          </Link>
+        </Button>
+      </SettingsHeader>
 
       <GenericOrganisationAdminForm organisation={organisation} />
 

apps/remix/app/routes/_authenticated+/admin+/stats.tsx

@@ -152,12 +152,6 @@ export default function AdminStatsPage({ loaderData }: Route.ComponentProps) {
         <div className="mt-5 grid grid-cols-2 gap-8">
           <MonthlyActiveUsersChart title={_(msg`MAU (signed in)`)} data={monthlyActiveUsers} />
 
-          <MonthlyActiveUsersChart
-            title={_(msg`Cumulative MAU (signed in)`)}
-            data={monthlyActiveUsers}
-            cummulative
-          />
-
           <AdminStatsUsersWithDocumentsChart
             data={monthlyUsersWithDocuments}
             title={_(msg`MAU (created document)`)}

package.json

@@ -20,7 +20,7 @@
     "commitlint": "commitlint --edit",
     "clean": "turbo run clean && rimraf node_modules",
     "d": "npm run dx && npm run translate:compile && npm run dev",
-    "dx": "npm i && npm run dx:up && npm run prisma:migrate-dev && npm run prisma:seed",
+    "dx": "npm ci && npm run dx:up && npm run prisma:migrate-dev && npm run prisma:seed",
     "dx:up": "docker compose -f docker/development/compose.yml up -d",
     "dx:down": "docker compose -f docker/development/compose.yml down",
     "ci": "turbo run build --filter=@documenso/remix && turbo run test:e2e",

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/audit-log-query.ts

@@ -1,110 +0,0 @@
-import type { DocumentAuditLog, Envelope, Prisma } from '@prisma/client';
-
-import { prisma } from '@documenso/prisma';
-
-import { DOCUMENT_AUDIT_LOG_TYPE } from '../../types/document-audit-logs';
-import type { FindResultResponse } from '../../types/search-params';
-import { parseDocumentAuditLogData } from '../../utils/document-audit-logs';
-
-const RECENT_ACTIVITY_EVENT_TYPES = [
-  DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_COMPLETED,
-  DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_CREATED,
-  DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_DELETED,
-  DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_OPENED,
-  DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_RECIPIENT_COMPLETED,
-  DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_RECIPIENT_REJECTED,
-  DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_SENT,
-  DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_MOVED_TO_TEAM,
-];
-
-export interface AuditLogQueryOptions {
-  envelope: Envelope;
-  page?: number;
-  perPage?: number;
-  orderBy?: {
-    column: keyof DocumentAuditLog;
-    direction: 'asc' | 'desc';
-  };
-  cursor?: string;
-  filterForRecentActivity?: boolean;
-}
-
-function buildAuditLogWhereClause(
-  envelope: Envelope,
-  filterForRecentActivity?: boolean,
-): Prisma.DocumentAuditLogWhereInput {
-  const baseWhereClause: Prisma.DocumentAuditLogWhereInput = {
-    envelopeId: envelope.id,
-  };
-
-  if (!filterForRecentActivity) {
-    return baseWhereClause;
-  }
-
-  const recentActivityConditions: Prisma.DocumentAuditLogWhereInput['OR'] = [
-    {
-      type: {
-        in: RECENT_ACTIVITY_EVENT_TYPES,
-      },
-    },
-    {
-      type: DOCUMENT_AUDIT_LOG_TYPE.EMAIL_SENT,
-      data: {
-        path: ['isResending'],
-        equals: true,
-      },
-    },
-  ];
-
-  return {
-    ...baseWhereClause,
-    OR: recentActivityConditions,
-  };
-}
-
-export async function queryAuditLogs({
-  envelope,
-  page = 1,
-  perPage = 30,
-  orderBy,
-  cursor,
-  filterForRecentActivity,
-}: AuditLogQueryOptions) {
-  const orderByColumn = orderBy?.column ?? 'createdAt';
-  const orderByDirection = orderBy?.direction ?? 'desc';
-
-  const whereClause = buildAuditLogWhereClause(envelope, filterForRecentActivity);
-
-  const normalizedPage = Math.max(page, 1);
-  const skip = (normalizedPage - 1) * perPage;
-
-  const [data, count] = await Promise.all([
-    prisma.documentAuditLog.findMany({
-      where: whereClause,
-      skip,
-      take: perPage + 1,
-      orderBy: {
-        [orderByColumn]: orderByDirection,
-      },
-      cursor: cursor ? { id: cursor } : undefined,
-    }),
-    prisma.documentAuditLog.count({
-      where: whereClause,
-    }),
-  ]);
-
-  const allParsedData = data.map((auditLog) => parseDocumentAuditLogData(auditLog));
-
-  const hasNextPage = allParsedData.length > perPage;
-  const parsedData = hasNextPage ? allParsedData.slice(0, perPage) : allParsedData;
-  const nextCursor = hasNextPage ? allParsedData[perPage].id : undefined;
-
-  return {
-    data: parsedData,
-    count,
-    currentPage: normalizedPage,
-    perPage,
-    totalPages: Math.ceil(count / perPage),
-    nextCursor,
-  } satisfies FindResultResponse<typeof parsedData> & { nextCursor?: string };
-}

packages/lib/server-only/document/find-document-audit-logs.ts

@@ -1,15 +1,17 @@
-import type { DocumentAuditLog } from '@prisma/client';
-import { EnvelopeType } from '@prisma/client';
+import { type DocumentAuditLog, EnvelopeType, type Prisma } from '@prisma/client';
 
 import { prisma } from '@documenso/prisma';
 
 import { AppError, AppErrorCode } from '../../errors/app-error';
+import { DOCUMENT_AUDIT_LOG_TYPE } from '../../types/document-audit-logs';
+import type { FindResultResponse } from '../../types/search-params';
+import { parseDocumentAuditLogData } from '../../utils/document-audit-logs';
 import { getEnvelopeWhereInput } from '../envelope/get-envelope-by-id';
-import { queryAuditLogs } from './audit-log-query';
 
-interface BaseAuditLogOptions {
+export interface FindDocumentAuditLogsOptions {
   userId: number;
   teamId: number;
+  documentId: number;
   page?: number;
   perPage?: number;
   orderBy?: {
@@ -20,24 +22,19 @@ interface BaseAuditLogOptions {
   filterForRecentActivity?: boolean;
 }
 
-export interface FindDocumentAuditLogsOptions extends BaseAuditLogOptions {
-  documentId: number;
-}
-
-export interface FindEnvelopeAuditLogsOptions extends BaseAuditLogOptions {
-  envelopeId: string;
-}
-
 export const findDocumentAuditLogs = async ({
   userId,
   teamId,
   documentId,
-  page,
-  perPage,
+  page = 1,
+  perPage = 30,
   orderBy,
   cursor,
   filterForRecentActivity,
 }: FindDocumentAuditLogsOptions) => {
+  const orderByColumn = orderBy?.column ?? 'createdAt';
+  const orderByDirection = orderBy?.direction ?? 'desc';
+
   const { envelopeWhereInput } = await getEnvelopeWhereInput({
     id: {
       type: 'documentId',
@@ -56,53 +53,67 @@ export const findDocumentAuditLogs = async ({
     throw new AppError(AppErrorCode.NOT_FOUND);
   }
 
-  return queryAuditLogs({
-    envelope,
-    page,
-    perPage,
-    orderBy,
-    cursor,
-    filterForRecentActivity,
-  });
-};
+  const whereClause: Prisma.DocumentAuditLogWhereInput = {
+    envelopeId: envelope.id,
+  };
 
-export const findEnvelopeAuditLogs = async ({
-  userId,
-  teamId,
-  envelopeId,
-  page,
-  perPage,
-  orderBy,
-  cursor,
-  filterForRecentActivity,
-}: FindEnvelopeAuditLogsOptions) => {
-  const isLegacyDocumentId = /^\d+$/.test(envelopeId);
-
-  const idConfig = isLegacyDocumentId
-    ? { type: 'documentId' as const, id: Number(envelopeId) }
-    : { type: 'envelopeId' as const, id: envelopeId };
-
-  const { envelopeWhereInput } = await getEnvelopeWhereInput({
-    id: idConfig,
-    type: isLegacyDocumentId ? EnvelopeType.DOCUMENT : null,
-    userId,
-    teamId,
-  });
-
-  const envelope = await prisma.envelope.findUnique({
-    where: envelopeWhereInput,
-  });
-
-  if (!envelope) {
-    throw new AppError(AppErrorCode.NOT_FOUND);
+  // Filter events down to what we consider recent activity.
+  if (filterForRecentActivity) {
+    whereClause.OR = [
+      {
+        type: {
+          in: [
+            DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_COMPLETED,
+            DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_CREATED,
+            DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_DELETED,
+            DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_OPENED,
+            DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_RECIPIENT_COMPLETED,
+            DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_RECIPIENT_REJECTED,
+            DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_SENT,
+            DOCUMENT_AUDIT_LOG_TYPE.DOCUMENT_MOVED_TO_TEAM,
+          ],
+        },
+      },
+      {
+        type: DOCUMENT_AUDIT_LOG_TYPE.EMAIL_SENT,
+        data: {
+          path: ['isResending'],
+          equals: true,
+        },
+      },
+    ];
   }
 
-  return queryAuditLogs({
-    envelope,
-    page,
+  const [data, count] = await Promise.all([
+    prisma.documentAuditLog.findMany({
+      where: whereClause,
+      skip: Math.max(page - 1, 0) * perPage,
+      take: perPage + 1,
+      orderBy: {
+        [orderByColumn]: orderByDirection,
+      },
+      cursor: cursor ? { id: cursor } : undefined,
+    }),
+    prisma.documentAuditLog.count({
+      where: whereClause,
+    }),
+  ]);
+
+  let nextCursor: string | undefined = undefined;
+
+  const parsedData = data.map((auditLog) => parseDocumentAuditLogData(auditLog));
+
+  if (parsedData.length > perPage) {
+    const nextItem = parsedData.pop();
+    nextCursor = nextItem!.id;
+  }
+
+  return {
+    data: parsedData,
+    count,
+    currentPage: Math.max(page, 1),
     perPage,
-    orderBy,
-    cursor,
-    filterForRecentActivity,
-  });
+    totalPages: Math.ceil(count / perPage),
+    nextCursor,
+  } satisfies FindResultResponse<typeof parsedData> & { nextCursor?: string };
 };

packages/lib/translations/de/web.po

@@ -2793,10 +2793,6 @@ msgstr "Erstellt am {0}"
 msgid "CSV Structure"
 msgstr "CSV-Struktur"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "Kumulative MAU (angemeldet)"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "Aktuell"

packages/lib/translations/en/web.po

@@ -2788,10 +2788,6 @@ msgstr "Created on {0}"
 msgid "CSV Structure"
 msgstr "CSV Structure"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "Cumulative MAU (signed in)"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "Current"

packages/lib/translations/es/web.po

@@ -2793,10 +2793,6 @@ msgstr "Creado el {0}"
 msgid "CSV Structure"
 msgstr "Estructura CSV"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "MAU acumulativo (con sesión iniciada)"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "Actual"

packages/lib/translations/fr/web.po

@@ -2793,10 +2793,6 @@ msgstr "Créé le {0}"
 msgid "CSV Structure"
 msgstr "Structure CSV"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "MAU cumulatif (connecté)"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "Actuel"

packages/lib/translations/it/web.po

@@ -2793,10 +2793,6 @@ msgstr "Creato il {0}"
 msgid "CSV Structure"
 msgstr "Struttura CSV"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "MAU cumulativi (autenticati)"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "Corrente"

packages/lib/translations/ja/web.po

@@ -2793,10 +2793,6 @@ msgstr "作成日 {0}"
 msgid "CSV Structure"
 msgstr "CSV 構造"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "累積 MAU（サインイン済み）"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "現在"

packages/lib/translations/ko/web.po

@@ -2793,10 +2793,6 @@ msgstr "{0}에 생성됨"
 msgid "CSV Structure"
 msgstr "CSV 구조"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "누적 MAU(로그인 기준)"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "현재"

packages/lib/translations/nl/web.po

@@ -2793,10 +2793,6 @@ msgstr "Gemaakt op {0}"
 msgid "CSV Structure"
 msgstr "CSV-Structuur"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "Cumulatieve MAU (ingelogd)"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "Huidig"

packages/lib/translations/pl/web.po

@@ -2793,10 +2793,6 @@ msgstr "Utworzono {0}"
 msgid "CSV Structure"
 msgstr "Struktura CSV"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "Łączna liczba MAU (zalogowani)"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "Obecna"

packages/lib/translations/pt-BR/web.po

@@ -2769,10 +2769,6 @@ msgstr "Criado em {0}"
 msgid "CSV Structure"
 msgstr "Estrutura do CSV"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "MAU acumulado (logados)"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "Atual"

packages/lib/translations/sq/web.po

@@ -2298,10 +2298,6 @@ msgstr "Krijuar më {0}"
 msgid "CSV Structure"
 msgstr "Struktura CSV"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr ""
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "Aktual"

packages/lib/translations/zh/web.po

@@ -2793,10 +2793,6 @@ msgstr "创建于 {0}"
 msgid "CSV Structure"
 msgstr "CSV 结构"
 
-#: apps/remix/app/routes/_authenticated+/admin+/stats.tsx
-msgid "Cumulative MAU (signed in)"
-msgstr "累计月活跃用户（已登录）"
-
 #: apps/remix/app/routes/_authenticated+/settings+/security.sessions.tsx
 msgid "Current"
 msgstr "当前"

packages/prisma/migrations/20251126053402_add_insights_indexes/migration.sql

@@ -0,0 +1,26 @@
+-- CreateIndex
+CREATE INDEX "Envelope_type_idx" ON "Envelope"("type");
+
+-- CreateIndex
+CREATE INDEX "Envelope_status_idx" ON "Envelope"("status");
+
+-- CreateIndex
+CREATE INDEX "Envelope_createdAt_idx" ON "Envelope"("createdAt");
+
+-- CreateIndex
+CREATE INDEX "Organisation_name_idx" ON "Organisation"("name");
+
+-- CreateIndex
+CREATE INDEX "Organisation_ownerUserId_idx" ON "Organisation"("ownerUserId");
+
+-- 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 "Team_name_idx" ON "Team"("name");

packages/prisma/schema.prisma

@@ -430,9 +430,12 @@ model Envelope {
 
   envelopeAttachments EnvelopeAttachment[]
 
-  @@index([folderId])
-  @@index([teamId])
+  @@index([type])
+  @@index([status])
   @@index([userId])
+  @@index([teamId])
+  @@index([folderId])
+  @@index([createdAt])
 }
 
 model EnvelopeItem {
@@ -583,8 +586,10 @@ model Recipient {
   fields            Field[]
   signatures        Signature[]
 
-  @@index([envelopeId])
   @@index([token])
+  @@index([email])
+  @@index([envelopeId])
+  @@index([signedAt])
 }
 
 enum FieldType {
@@ -694,6 +699,9 @@ model Organisation {
 
   organisationAuthenticationPortalId String                           @unique
   organisationAuthenticationPortal   OrganisationAuthenticationPortal @relation(fields: [organisationAuthenticationPortalId], references: [id])
+
+  @@index([name])
+  @@index([ownerUserId])
 }
 
 model OrganisationMember {
@@ -710,6 +718,7 @@ model OrganisationMember {
   organisationGroupMembers OrganisationGroupMember[]
 
   @@unique([userId, organisationId])
+  @@index([organisationId])
 }
 
 model OrganisationMemberInvite {
@@ -883,6 +892,7 @@ model Team {
   teamGlobalSettingsId String             @unique
   teamGlobalSettings   TeamGlobalSettings @relation(fields: [teamGlobalSettingsId], references: [id], onDelete: Cascade)
 
+  @@index([name])
   @@index([organisationId])
 }
 

packages/trpc/server/document-router/find-document-audit-logs.ts

@@ -10,18 +10,32 @@ export const findDocumentAuditLogsRoute = authenticatedProcedure
   .input(ZFindDocumentAuditLogsRequestSchema)
   .output(ZFindDocumentAuditLogsResponseSchema)
   .query(async ({ input, ctx }) => {
-    const { orderByColumn, orderByDirection, ...auditLogParams } = input;
+    const { teamId } = ctx;
+
+    const {
+      page,
+      perPage,
+      documentId,
+      cursor,
+      filterForRecentActivity,
+      orderByColumn,
+      orderByDirection,
+    } = input;
 
     ctx.logger.info({
       input: {
-        documentId: input.documentId,
+        documentId,
       },
     });
 
     return await findDocumentAuditLogs({
-      ...auditLogParams,
       userId: ctx.user.id,
-      teamId: ctx.teamId,
+      teamId,
+      page,
+      perPage,
+      documentId,
+      cursor,
+      filterForRecentActivity,
       orderBy: orderByColumn ? { column: orderByColumn, direction: orderByDirection } : undefined,
     });
   });

packages/trpc/server/envelope-router/find-envelope-audit-logs.ts

@@ -1,29 +0,0 @@
-import { findEnvelopeAuditLogs } from '@documenso/lib/server-only/document/find-document-audit-logs';
-
-import { authenticatedProcedure } from '../trpc';
-import {
-  ZFindEnvelopeAuditLogsRequestSchema,
-  ZFindEnvelopeAuditLogsResponseSchema,
-  findEnvelopeAuditLogsMeta,
-} from './find-envelope-audit-logs.types';
-
-export const findEnvelopeAuditLogsRoute = authenticatedProcedure
-  .meta(findEnvelopeAuditLogsMeta)
-  .input(ZFindEnvelopeAuditLogsRequestSchema)
-  .output(ZFindEnvelopeAuditLogsResponseSchema)
-  .query(async ({ input, ctx }) => {
-    const { orderByColumn, orderByDirection, ...auditLogParams } = input;
-
-    ctx.logger.info({
-      input: {
-        envelopeId: input.envelopeId,
-      },
-    });
-
-    return await findEnvelopeAuditLogs({
-      ...auditLogParams,
-      userId: ctx.user.id,
-      teamId: ctx.teamId,
-      orderBy: orderByColumn ? { column: orderByColumn, direction: orderByDirection } : undefined,
-    });
-  });

packages/trpc/server/envelope-router/find-envelope-audit-logs.types.ts

@@ -1,35 +0,0 @@
-import { z } from 'zod';
-
-import { ZDocumentAuditLogSchema } from '@documenso/lib/types/document-audit-logs';
-import { ZFindResultResponse, ZFindSearchParamsSchema } from '@documenso/lib/types/search-params';
-
-import type { TrpcRouteMeta } from '../trpc';
-
-export const findEnvelopeAuditLogsMeta: TrpcRouteMeta = {
-  openapi: {
-    method: 'GET',
-    path: '/envelope/{envelopeId}/audit-log',
-    summary: 'Get envelope audit logs',
-    description:
-      'Returns paginated audit logs for an envelope given an ID. Accepts both envelope IDs (string) and legacy document IDs (number).',
-    tags: ['Envelope'],
-  },
-};
-
-export const ZFindEnvelopeAuditLogsRequestSchema = ZFindSearchParamsSchema.extend({
-  envelopeId: z
-    .string()
-    .describe('Envelope ID (e.g., envelope_xxx) or legacy document ID (e.g., 12345)'),
-  cursor: z.string().optional(),
-  filterForRecentActivity: z.boolean().optional(),
-  orderByColumn: z.enum(['createdAt', 'type']).optional(),
-  orderByDirection: z.enum(['asc', 'desc']).default('desc'),
-});
-
-export const ZFindEnvelopeAuditLogsResponseSchema = ZFindResultResponse.extend({
-  data: ZDocumentAuditLogSchema.array(),
-  nextCursor: z.string().optional(),
-});
-
-export type TFindEnvelopeAuditLogsRequest = z.infer<typeof ZFindEnvelopeAuditLogsRequestSchema>;
-export type TFindEnvelopeAuditLogsResponse = z.infer<typeof ZFindEnvelopeAuditLogsResponseSchema>;

packages/trpc/server/envelope-router/router.ts

@@ -18,7 +18,6 @@ import { createEnvelopeRecipientsRoute } from './envelope-recipients/create-enve
 import { deleteEnvelopeRecipientRoute } from './envelope-recipients/delete-envelope-recipient';
 import { getEnvelopeRecipientRoute } from './envelope-recipients/get-envelope-recipient';
 import { updateEnvelopeRecipientsRoute } from './envelope-recipients/update-envelope-recipients';
-import { findEnvelopeAuditLogsRoute } from './find-envelope-audit-logs';
 import { getEnvelopeRoute } from './get-envelope';
 import { getEnvelopeItemsRoute } from './get-envelope-items';
 import { getEnvelopeItemsByTokenRoute } from './get-envelope-items-by-token';
@@ -66,9 +65,6 @@ export const envelopeRouter = router({
     set: setEnvelopeFieldsRoute,
     sign: signEnvelopeFieldRoute,
   },
-  auditLog: {
-    find: findEnvelopeAuditLogsRoute,
-  },
   get: getEnvelopeRoute,
   create: createEnvelopeRoute,
   use: useEnvelopeRoute,