Do It Later: A Job Queue in Go

Sending the email inside the pay route 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 the pay route 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'.

A table is created with db.Exec, and so is a row. In Go, each db.Exec is saved in the database the moment it runs. An example on another table, reminders:

db.Exec("CREATE TABLE IF NOT EXISTS reminders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL)")
db.Exec("INSERT INTO reminders (customer_id) VALUES (?)", 5)   // saved at once

Where you add the job matters. Add it right after the UPDATE that marks the order paid: a declined card and a retry with the same idempotency key answer earlier, so they add no job, and every paid order gets one. 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: in newApp, when the app starts, create the table jobs with id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL and status TEXT NOT NULL. In the pay route, right after the UPDATE that marks the order paid, add a 'waiting' job for the order.

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 newApp runs. The INSERT INTO jobs goes right after the UPDATE, below the declined check, never above it.