A LIMIT that overwrote a payment

Invariant has an expiry worker. Every ten minutes it finds payments that are still pending past their deadline, marks them expired, and writes a payment.expired webhook event for each, in the same transaction. The rule for any status change in this project is a conditional update: only change the row if it's still in the state you expect, then check how many rows changed.

My first version did the expiry in batches of 100 with Prisma's limit option, and it read like exactly that rule:

await tx.payment.updateMany({
  where: { status: 'PENDING', expiresAt: { lte: now } },
  data: { status: 'EXPIRED' },
  limit: 100,
});

It failed when an approval landed at the same moment the worker ran. The balances were fine. A constraint fired, which meant something had tried to write a state that shouldn't exist.

What Prisma sends

Postgres has no LIMIT on UPDATE, so Prisma moves the conditions into a subquery:

UPDATE "Payment" SET status = 'EXPIRED'
WHERE id IN (
  SELECT id FROM "Payment"
  WHERE status = 'PENDING' AND "expiresAt" <= $1
  LIMIT 100
);

That's fine until a row is locked. Say an approval has claimed payment P, setting it to SUCCESS, and hasn't committed yet. The worker's subquery runs on its snapshot, sees P as pending, and puts it in the list. The outer UPDATE then waits on P's row lock.

When the approval commits, Postgres under READ COMMITTED re-checks the waiting update's WHERE clause against the new version of the row. The update's own WHERE is only id IN (...), and P's id is still in that list. The status condition lived in the subquery, and the subquery isn't re-run. So the worker overwrote a committed SUCCESS with EXPIRED.

What stopped it

Two constraints I'd added for other reasons. A CHECK on Payment says userId is set if and only if the status is SUCCESS, so an expired payment with a payer is illegal. And the outbox table has one event per payment, so the worker's payment.expired row collided with the approval's payment.succeeded row. Either one was enough to roll the batch back. The money was never wrong, but only because the schema refused to store the wrong answer.

The fix

Pick candidates with a read, then put the full state condition in the update's own WHERE, and take every side effect from the rows the update returned:

const due = await tx.payment.findMany({
  where: { status: 'PENDING', expiresAt: { lte: now } },
  select: { id: true },
  take: 100,
});

const expired = await tx.payment.updateManyAndReturn({
  where: { id: { in: due.map((p) => p.id) }, status: 'PENDING' },
  data: { status: 'EXPIRED' },
});

await tx.webhookEvent.createMany({
  data: expired.map((p) => ({ paymentId: p.id })),
});

Now when the update waits on P's lock and re-checks, status = 'PENDING' is part of what it re-checks. P has moved on, so it drops out, and no event is written for it because events come from expired, not from due.

I wrote the rule into the project notes so I'd stop relearning it: the condition that makes a write safe has to be in that write's own WHERE, and anything that depends on the write uses the rows it returned, never an earlier lookup. Later, for manual webhook replay, I turned on Postgres statement logging to check what Prisma generated for a relation filter before trusting it. It compiles to an EXISTS inside the update's own WHERE, which is safe. limit is the one to watch.