How do you make message consumers idempotent?

Summary

Give each message a unique ID and record processed IDs (in the same transaction as the business change, with a unique constraint), design operations as upserts or absolute state changes ('set status to paid' rather than 'add 10'), and check current state before acting.

Longer Answer

Example: an inbox table processed_messages(message_id PRIMARY KEY); insert it and apply the change in one DB transaction; on duplicate key, skip. Expire old IDs after the redelivery window.

Code Example

BEGIN;
INSERT INTO processed_messages (message_id) VALUES ($1);  -- fails on duplicate → skip
UPDATE orders SET status = 'paid' WHERE id = $2 AND status = 'pending';
COMMIT;

Key Points

• Unique message IDs
• Processed-IDs table in same transaction
• Upserts / absolute updates
• Check state first