Razorpay: Webhook Events That Never Got a 2xx Response
Razorpay retries a webhook until the merchant's server answers with a 2xx status.
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #908
Problem statement
Razorpay retries a webhook until the merchant's server answers with a 2xx status. The developer-experience team wants the events that were attempted but never acknowledged, to email the merchants whose integration is broken.
A delivery attempt is acknowledged when http_status is between 200 and 299; a NULL status is a timeout. Return every event with at least one attempt and no acknowledged attempt: event_id, event_type, attempts (its number of attempts) and last_attempt_at (its latest attempt). Events never attempted are not reported. Any order.
A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.
Tables
Table: WebhookEvent
| Column | Type |
|---|---|
| event_id | int |
| merchant_id | int |
| event_type | varchar |
| created_at | datetime |
Primary key: event_id.
One row per event Razorpay raised for a merchant, such as payment.captured.
Table: DeliveryAttempt
| Column | Type |
|---|---|
| attempt_id | int |
| event_id | int |
| attempted_at | datetime |
| http_status | int |
Primary key: attempt_id.
One row per POST to the merchant's webhook URL; http_status is NULL when the request timed out.
Examples
Example 1
WebhookEvent
| event_id | merchant_id | event_type | created_at |
|---|---|---|---|
| 1 | 401 | payment.captured | 2025-03-10 10:00:00 |
| 2 | 401 | refund.processed | 2025-03-10 10:05:00 |
| 3 | 402 | settlement.processed | 2025-03-10 11:00:00 |
| 4 | 402 | payment.failed | 2025-03-10 11:30:00 |
| 5 | 403 | subscription.charged | 2025-03-10 12:00:00 |
DeliveryAttempt
| attempt_id | event_id | attempted_at | http_status |
|---|---|---|---|
| 1 | 1 | 2025-03-10 10:00:02 | 500 |
| 2 | 1 | 2025-03-10 10:05:02 | 200 |
| 3 | 2 | 2025-03-10 10:05:03 | 502 |
| 4 | 2 | 2025-03-10 10:10:03 | NULL |
| 5 | 2 | 2025-03-10 10:40:03 | 404 |
| 6 | 3 | 2025-03-10 11:00:04 | 300 |
| 7 | 5 | 2025-03-10 12:00:01 | 204 |
Output
| event_id | event_type | attempts | last_attempt_at |
|---|---|---|---|
| 2 | refund.processed | 3 | 2025-03-10 10:40:03 |
| 3 | settlement.processed | 1 | 2025-03-10 11:00:04 |
How to solve Razorpay: Webhook Events That Never Got a 2xx Response
The rule is about all of an event's attempts at once — attempted, and never acknowledged — so group the attempts per event. The inner join with DeliveryAttempt already removes events with no attempts, and COUNT(*) and MAX(attempted_at) give the two reported numbers.
"Never acknowledged" becomes HAVING SUM(CASE WHEN http_status BETWEEN 200 AND 299 THEN 1 ELSE 0 END) = 0. Both ends of BETWEEN are inclusive, so 200 and 299 succeed while 199 and 300 do not. A timeout has http_status NULL; NULL BETWEEN … is unknown, so the CASE falls to ELSE 0 and the timeout counts as a failed attempt, as it should. Filtering failures in WHERE instead would be wrong — it would report an event that failed once and succeeded on the retry.
The alternative states the rule with EXISTS / NOT EXISTS and fetches the count and the latest time with scalar subqueries. With an index on DeliveryAttempt(event_id) both read each attempt once.
Reference solution (MySQL)
SELECT e.event_id, e.event_type, COUNT(*) AS attempts, MAX(a.attempted_at) AS last_attempt_at
FROM WebhookEvent e
JOIN DeliveryAttempt a ON a.event_id = e.event_id
GROUP BY e.event_id, e.event_type
HAVING SUM(CASE WHEN a.http_status BETWEEN 200 AND 299 THEN 1 ELSE 0 END) = 0Another way
SELECT e.event_id, e.event_type,
(SELECT COUNT(*) FROM DeliveryAttempt a WHERE a.event_id = e.event_id) AS attempts,
(SELECT MAX(a.attempted_at) FROM DeliveryAttempt a WHERE a.event_id = e.event_id) AS last_attempt_at
FROM WebhookEvent e
WHERE EXISTS (SELECT 1 FROM DeliveryAttempt a WHERE a.event_id = e.event_id)
AND NOT EXISTS (SELECT 1 FROM DeliveryAttempt a WHERE a.event_id = e.event_id AND a.http_status >= 200 AND a.http_status < 300)← Razorpay: Chargeback Rate by Merchant Category · Razorpay: Subscription Cycles Recovered by Retry vs Halted →