A Worker in TypeScript

Since the last lesson, paying an order doesn't send the receipt. It adds a row to the jobs table, with the status 'waiting'. Somebody has to do those jobs: that is a worker. In a real shop it is a second program that takes the waiting jobs every few seconds. Yours is a route, POST /admin/run-jobs, so one request runs it.

A worker does three things: it takes the rows still to do, it does each one, and it marks each one done, so nothing is done twice. Here is the same shape on another table, reminders, with a type for its rows:

type Reminder = { id: number; customer_id: number; sent: number };

const rows = db.prepare("SELECT * FROM reminders WHERE sent = 0").all() as Reminder[];
for (const row of rows) {
  console.log("Reminder for customer", row.customer_id);
  db.prepare("UPDATE reminders SET sent = 1 WHERE id = ?").run(row.id);
}

The UPDATE sits inside the loop, and each .run is saved the moment it finishes: if the program stops halfway, the rows already done stay done. For your jobs, that means no customer gets the same email twice. The route is a POST, because it changes things, and its function is async, because it waits for the mail service. The type Job for a job row is already at the top of the file.

The tests pay two orders, run the worker twice, then pay a third order and run it again. They read /check/sent (the emails the mail service received) and /check/jobs (the jobs table):

POST /orders/8/pay    →  201  {"amount": 80, "order_id": 8, "payment_id": "pay_1"}
POST /orders/10/pay   →  201  {"amount": 21, "order_id": 10, "payment_id": "pay_2"}
GET /check/sent       →  200  []
POST /admin/run-jobs  →  200  {"ran": 2}
GET /check/sent       →  200  [{"subject": "Your order 8 is paid", "to": "eva@example.com", …},
                               {"subject": "Your order 10 is paid", "to": "chloe@example.com", …}]
GET /check/jobs       →  200  [{"id": 1, "order_id": 8, "status": "done"},
                               {"id": 2, "order_id": 10, "status": "done"}]
POST /admin/run-jobs  →  200  {"ran": 0}
POST /orders/11/pay   →  201  {"amount": 18, "order_id": 11, "payment_id": "pay_3"}
POST /admin/run-jobs  →  200  {"ran": 1}
GET /check/sent       →  200  [… the same two, then {"subject": "Your order 11 is paid", "to": "ana@example.com", …}]

Your task: at the end of the file, add app.post for /admin/run-jobs, with an async function typed like the other routes'. It takes the waiting jobs, oldest first, as Job[], sends each job's receipt with your helper sendReceipt, marks the job 'done', then answers {"ran": ...} with the number of jobs it took.

Assignment
Follow the Coding Tutorial to write the worker route POST /admin/run-jobs.

Hint
Take the jobs with WHERE status = 'waiting' ORDER BY id and .all() as Job[]. Put await in front of sendReceipt. The UPDATE goes inside the for loop; res.json goes after it, with jobs.length.