A teammate's dedupe logic is: SELECT the message id from processed_messages; if not found, process the message, then INSERT the id. What is wrong with it, and what other ways are there to make processing idempotent?

It is check-then-act without atomicity. The check (SELECT) and the action (INSERT) are separate steps, so something can happen between them.

Two ways it fails:

• Two consumers run at the same time. Or a redelivered copy arrives while the first copy is still running. Both see no row, both process the message, and both insert the id.
• The consumer crashes after processing but before the insert. No record exists, so the retry processes the message again.

The fix: let the database enforce uniqueness. Insert the id first, with a unique constraint, in the same transaction as the business change. Treat a conflict as a duplicate.

Other ways to make processing idempotent:

• Natural idempotency: the operation gives the same result when repeated. Examples are upserts, or SET status = 'shipped' instead of an increment.
• Conditional writes: for example, update only WHERE version = $expected. A stale or repeated event then changes nothing.
• Idempotency keys passed to external APIs. The provider removes the duplicate side effect, which you cannot roll back yourself.