Do It Later: A Job Queue in TypeScript

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'.

Where you add the job matters. Each .run saves its change by itself. To save two changes together, put them in one transaction: BEGIN before them, COMMIT after them. A classic example, on another table: moving money between two accounts.

db.exec("BEGIN");
db.prepare("UPDATE accounts SET balance = balance - 50 WHERE id = ?").run(1);
db.prepare("UPDATE accounts SET balance = balance + 50 WHERE id = ?").run(2);
db.exec("COMMIT");   // both changes are saved together, or neither

Add the job the same way, in one transaction with the UPDATE that marks the order paid: 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 the pay route, 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 with db.exec, below the line that opens db, so the line is safe every time the app starts. db.exec("BEGIN"); goes above the UPDATE; the INSERT INTO jobs, run with orderId, and db.exec("COMMIT"); go below it.