“Proven” is not the same as “the cause.”

It began with a correct fix.

Customers were being charged shipping twice on re-shipped packages. A cleanup sweep already existed to stop stale shipping charges from being billed. The fix was one extra OR arm on that sweep's Prisma delete, to also catch charges from an earlier attempt that never got linked to an invoice.

// before: drop automatic charges on this unpaid invoice
await prisma.charge.deleteMany({
  where: {
    isAutomatic: true,
    invoiceId,
    invoice: { status: 'UNPAID' },
  },
})

// after the "correct fix": one extra OR arm, to also catch
// charges from an earlier attempt that never got linked to an invoice
await prisma.charge.deleteMany({
  where: {
    isAutomatic: true,
    OR: [
      { invoiceId, invoice: { status: 'UNPAID' } },
      { invoiceId: null, task: { packages: { some: { id: packageId } } } },
    ],
  },
})

Correct. Reviewed. Tested. Merged.

Behind the scenes, Prisma generated roughly this:

-- what Prisma generated, roughly
DELETE FROM charges
WHERE is_automatic
  AND (
    (invoice_id = $1
      AND invoice_id IN (SELECT id FROM invoices WHERE status = 'UNPAID'))
    OR
    (invoice_id IS NULL
      AND task_id IN (SELECT ... FROM tasks JOIN ... WHERE package_id = $2))
  )

The first arm is anchored by invoice_id = $1: indexed, a few rows. The new arm's anchor is invoice_id IS NULL, which matches an enormous and ever-growing slice of the table. Postgres walks that slice and evaluates the subquery against it, inside a DELETE, on the busiest table, on every checkout. Each execution correct. Each one a little slower every day, until the shared database CPU pinned. Nobody ran the plan against production data.

Every morning, at peak.

Database saturates. App starts returning 429s. A watchdog I'd built earlier notices and rolls out a fresh revision. Two minutes, back to normal. Slack lights up anyway.

429s come from the runtime. So that's where I looked.

I found something real.

Digging into the runtime, I found a file descriptor limit set far below what the platform allowed. I wrote a stress test that recreated the whole incident on demand: descriptors ran out, the app returned 429s, the watchdog rolled out a fresh revision, and two minutes later it recovered. Then I raised the limit and ran the same stress test again. And again. No outage. Everything looked fixed, so I wrote this in the ticket:

Then, the next morning:

Twenty-eight seconds.

The database had been telling me all week.

I opened Query Insights for the first time in seven days. One query family was consuming almost all available CPU during every incident. Including the two I'd already proven.

It was the query from Section 2. Mine.

The repair was to invert the shape. Never make the database resolve relations inside a DELETE. Read the ids through indexed lookups, then delete by primary key.

// the repair: read first through indexed lookups,
// then delete by primary key
const stale = await findStaleCharges({ invoiceId, packageId })

await prisma.charge.deleteMany({
  where: { id: { in: stale.map((c) => c.id) } },
})

Then it became a standing rule, written up as an architecture decision: Prisma queries are anchored on an index, or they don't ship. The pattern is banned, not just patched.