Do It Later: A Job Queue in C#

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 SendReceipt 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 your app needs can be created when it starts, with CREATE TABLE IF NOT EXISTS, so the line is safe on every start. Here is the same idea on another table, reviews, with one row added:

new SqliteCommand("CREATE TABLE IF NOT EXISTS reviews (id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, stars INTEGER NOT NULL)", db).ExecuteNonQuery();

var add = new SqliteCommand("INSERT INTO reviews (product_id, stars) VALUES ($id, 5)", db);
add.Parameters.AddWithValue("$id", 3);
add.ExecuteNonQuery();   // saved at once

Microsoft.Data.Sqlite saves each command the moment its ExecuteNonQuery runs. Add the job right after the UPDATE that marks the order paid, so every paid order gets 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, right after the order is marked 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 in one line with new SqliteCommand(…, db).ExecuteNonQuery(). The INSERT INTO jobs goes right after the UPDATE's cmd.ExecuteNonQuery();, and it needs its own ExecuteNonQuery(), or no job is saved.