triggerdotdev · ericallam · Aug 12, 2026 · Aug 12, 2026
diff --git a/.server-changes/billing-limit-reconcile-index.md b/.server-changes/billing-limit-reconcile-index.md
@@ -0,0 +1,6 @@
+---
+area: webapp
+type: improvement
+---
+
+Reduced recurring background database load from the billing-limit recovery check, so paused environments are reconciled with less overhead.
diff --git a/apps/webapp/app/v3/services/billingLimit/billingLimitReconciliation.server.ts b/apps/webapp/app/v3/services/billingLimit/billingLimitReconciliation.server.ts
@@ -1,4 +1,5 @@
 import { EnvironmentPauseSource } from "@trigger.dev/database";
+import type { PrismaClient } from "@trigger.dev/database";
 import pMap from "p-map";
 import { prisma } from "~/db.server";
 import type { BillingLimitResult } from "~/services/billingLimit.schemas";
@@ -47,15 +48,14 @@ export function resolveReconcileTargetFromBillingLimit(
   return resolveConvergeTargetFromBillingLimit(billingLimit);
 }
 
-export async function getOrgIdsWithBillingPauseSource(): Promise<string[]> {
-  const rows = await prisma.runtimeEnvironment.findMany({
+export async function getOrgIdsWithBillingPauseSource(
+  db: PrismaClient = prisma
+): Promise<string[]> {
+  const rows = await db.runtimeEnvironment.groupBy({
+    by: ["organizationId"],
     where: {
       pauseSource: EnvironmentPauseSource.BILLING_LIMIT,
     },
-    select: {
-      organizationId: true,
-    },
-    distinct: ["organizationId"],
   });
 
   return rows.map((row) => row.organizationId);

diff --git a/apps/webapp/test/billingLimitReconciliation.test.ts b/apps/webapp/test/billingLimitReconciliation.test.ts
@@ -1,7 +1,10 @@
+import { postgresTest } from "@internal/testcontainers";
+import type { PrismaClient } from "@trigger.dev/database";
 import { describe, expect, it } from "vitest";
 import type { BillingLimitResult } from "~/services/billingLimit.schemas";
 import {
   collectOrgIdsNeedingBillingLimitLookup,
+  getOrgIdsWithBillingPauseSource,
   resolveConvergeTargetFromBillingLimit,
   resolveReconcileTargetFromBillingLimit,
   resolveReconcileTargetsForOrgLookups,
@@ -97,3 +100,70 @@ describe("billingLimitReconciliation", () => {
     expect(new Set(lookedUpOrgIds)).toEqual(new Set(["org_ok", "org_fail", "org_grace"]));
   });
 });
+
+let envSeedCounter = 0;
+
+async function seedEnvironment(
+  prisma: PrismaClient,
+  opts: { organizationId: string; projectId: string; pauseSource: "BILLING_LIMIT" | null }
+) {
+  const n = envSeedCounter++;
+  return prisma.runtimeEnvironment.create({
+    data: {
+      slug: `env-${n}`,
+      type: "PRODUCTION",
+      projectId: opts.projectId,
+      organizationId: opts.organizationId,
+      apiKey: `api-${n}`,
+      pkApiKey: `pk-${n}`,
+      shortcode: `sc-${n}`,
+      pauseSource: opts.pauseSource,
+    },
+  });
+}
+
+describe("getOrgIdsWithBillingPauseSource", () => {
+  postgresTest(
+    "returns each org once and ignores envs without the billing-limit pause source",
+    async ({ prisma }) => {
+      const seed: Record<string, Array<"BILLING_LIMIT" | null>> = {
+        org_a: ["BILLING_LIMIT", "BILLING_LIMIT"],
+        org_b: ["BILLING_LIMIT"],
+        org_c: [null],
+      };
+
+      const orgIdBySlug = new Map<string, string>();
+
+      for (const [slug, pauseSources] of Object.entries(seed)) {
+        const organization = await prisma.organization.create({
+          data: { title: slug, slug: `${slug}-${envSeedCounter}` },
+        });
+        const project = await prisma.project.create({
+          data: {
+            name: slug,
+            slug: `proj-${slug}-${envSeedCounter}`,
+            organizationId: organization.id,
+            externalRef: `ext-${slug}-${envSeedCounter}`,
+          },
+        });
+        orgIdBySlug.set(slug, organization.id);
+
+        for (const pauseSource of pauseSources) {
+          await seedEnvironment(prisma, {
+            organizationId: organization.id,
+            projectId: project.id,
+            pauseSource,
+          });
+        }
+      }
+
+      const orgIds = await getOrgIdsWithBillingPauseSource(prisma);
+
+      expect(orgIds.length).toBe(new Set(orgIds).size);
+      expect([...orgIds].sort()).toEqual(
+        [orgIdBySlug.get("org_a")!, orgIdBySlug.get("org_b")!].sort()
+      );
+    },
+    30_000
+  );
+});
diff --git a/...prisma/migrations/20260812120000_add_runtime_environment_pause_source_index/migration.sql b/...prisma/migrations/20260812120000_add_runtime_environment_pause_source_index/migration.sql
@@ -0,0 +1,3 @@
+CREATE INDEX CONCURRENTLY IF NOT EXISTS "RuntimeEnvironment_pauseSource_organizationId_idx"
+ON "RuntimeEnvironment" ("pauseSource", "organizationId")
+WHERE "pauseSource" IS NOT NULL;