Do It Later: A Job Queue in Python

Sending the email inside pay_order has two problems. The customer waits for the mail service before they see "paid", and a mail service can take seconds. Worse, if it is down, the email is lost, or the request crashes after the money has been taken.

So pay_order no longer sends the email (the call is gone from your code). It writes a note, "send the receipt for order 8", and answers at once; something else does the work later. Each note is a job, and a table of jobs waiting their turn is a job queue. A job needs little: its id, the order_id whose receipt to send, and a status such as 'waiting'.

Where you add the job matters. Database changes become permanent at the commit, all together, as one transaction. A classic example, on another table: moving money between two accounts.

db.execute("UPDATE accounts SET balance = balance - 50 WHERE id = 1")
db.execute("UPDATE accounts SET balance = balance + 50 WHERE id = 2")
db.commit()   # both changes are saved together, or neither

Add the job the same way, between the UPDATE that marks the order paid and the commit: there is never a paid order without its job. A declined card and a retry with the same idempotency key return earlier, so they add no job. The checker adds GET /check/jobs and GET /check/sent, so you can see the jobs and the emails the mail service received:

POST /orders/8/pay  →  201  {"amount": 80, "order_id": 8, "payment_id": "pay_1"}
GET /check/jobs  →  200  [{"id": 1, "order_id": 8, "status": "waiting"}]
GET /check/sent  →  200  []
POST /orders/9/pay  →  402  {"error": "Payment declined", "reason": "card_declined"}
POST /orders/10/pay  (Idempotency-Key: 9b3e6f20-…)  →  201  {"amount": 21, "order_id": 10, "payment_id": "pay_2"}
POST /orders/10/pay  (the same key)  →  201  {"amount": 21, "order_id": 10, "payment_id": "pay_2"}
GET /check/jobs  →  200  [{"id": 1, "order_id": 8, "status": "waiting"}, {"id": 2, "order_id": 10, "status": "waiting"}]

Your task: when the app starts, create the table jobs with id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL and status TEXT NOT NULL. In pay_order, add a 'waiting' job for the order in the same transaction that marks it paid.

Assignment
Follow the Coding Tutorial to create the jobs table and queue a receipt job for every paid order.

Hint
Use CREATE TABLE IF NOT EXISTS, so the line is safe every time the app starts. The INSERT INTO jobs goes after the UPDATE and before db.commit().