The Same Webhook, Twice

An interviewer asks: "Your payment provider calls a webhook on your app when a payment succeeds. Sometimes the same event arrives twice. How do you make sure the payment is recorded only once?" Providers promise at-least-once delivery: when your answer is slow or gets lost, they send the event again, with the same event id. The expected answer: make the handler idempotent by remembering which event ids you have already processed.

The idea in one sentence: store each event id in a table where it is the primary key; when the insert adds no row, the event is a repeat, so answer 200 and skip the work.

INSERT OR IGNORE skips a row whose primary key is already in the table instead of raising an error, and cursor.rowcount tells you how many rows were added. For example, with a signups table whose primary key is email:

sql = "INSERT OR IGNORE INTO signups (email) VALUES (?)"
print(db.execute(sql, ("ana@example.com",)).rowcount)   # 1: a new row
print(db.execute(sql, ("ana@example.com",)).rowcount)   # 0: already there, skipped

The starting code saves a payment for every event it gets, so a repeated event is paid twice. Your code must answer:

POST /webhooks/payments {"id": "evt_1", "type": "payment.succeeded", "data": {"order_id": 3, "amount": 41}}  →  200  {"received": true}
POST /webhooks/payments  (evt_1 again)  →  200  {"received": true, "duplicate": true}
POST /webhooks/payments  (evt_2: order 5, amount 73)  →  200  {"received": true}
POST /webhooks/payments  (evt_1 again)  →  200  {"received": true, "duplicate": true}
GET /orders/3/payments  →  200  {"order_id": 3, "payments": 1, "paid": 41}
POST /webhooks/payments  (evt_2 again)  →  200  {"received": true, "duplicate": true}
GET /orders/5/payments  →  200  {"order_id": 5, "payments": 1, "paid": 73}

A repeat is not an error, so answer it with 200: the provider keeps resending an event until it gets a 2xx answer.

Your task: create a processed_events table whose primary key is event_id, insert each event's id with INSERT OR IGNORE, and when cursor.rowcount is 0 answer {"received": true, "duplicate": true} before the payment is saved.

Assignment
Follow the Coding Tutorial to remember processed event ids and answer a repeated webhook without saving its payment again.

Hint
Put the insert and the rowcount check above the payment insert, and return inside the if so a repeat never reaches it. A primary key can't hold the same id twice, even when two copies arrive at the same moment.