Как на уровне SQL понять, что уже есть такой ключ идемпотентности? Как использовать ON CONFLICT?

Создайте UNIQUE-ограничение на ключ идемпотентности и используйте INSERT ... ON CONFLICT. Отдельная проверка SELECT перед INSERT не защищает от гонки.

INSERT INTO operations (idempotency_key, status)
VALUES ($1, 'pending')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING id;

Если строка не вернулась, запрос не создал новую операцию: прочитайте существующую и проверьте совпадение payload. Сохранение бизнес-результата должно быть согласовано с этой записью; конфликт уникальности не означает, что предыдущая операция уже завершена.