All or Nothing

An order changes several rows: the order itself, one row per item, and the stock of every product it buys. If an item fails halfway, the shop must not keep the first half. An order has to be all or nothing.

The database gives you this with a transaction. Without one, Microsoft.Data.Sqlite saves each command as soon as it runs. db.BeginTransaction() starts one: from then on every change waits in it until transaction.Commit() saves them all at once. Its opposite, transaction.Rollback(), throws them all away. Here is the idea on a money transfer between two accounts:

app.MapPost("/transfer", (JsonElement data) => {
    var amount = data.GetProperty("amount").GetInt32();
    var sender = data.GetProperty("sender").GetInt32();
    var receiver = data.GetProperty("receiver").GetInt32();
    using var transaction = db.BeginTransaction();
    Execute("UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, sender);
    Execute("UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, receiver);
    var cmd = db.CreateCommand();
    cmd.CommandText = "SELECT balance FROM accounts WHERE id = $1";
    cmd.Parameters.AddWithValue("$1", sender);
    if ((long)cmd.ExecuteScalar() < 0) {
        transaction.Rollback();
        return Results.Json(new { error = "Not enough money" }, statusCode: 409);
    }
    transaction.Commit();
    return Results.Json(new { ok = true });
});

Both updates are saved, or neither: money never leaves one account without reaching the other. 409 Conflict is the status for this: the request is fine in itself, but it clashes with the state of the data. With using var, a transaction that ends without Commit() (a return or a crash) is rolled back too; calling Rollback() says it right where the request fails.

Run in this order on a fresh shop, your POST /orders must give these answers:

POST /orders  {"customer_id": 5, "items": [{"product_id": 3, "quantity": 1}, {"product_id": 1, "quantity": 2}]}
    →  201  {"id": 8}
GET /products/3  →  200  {"id": 3, "name": "Lamp", "price": 29, "category": "home", "stock": 0}
POST /orders  {"customer_id": 4, "items": [{"product_id": 4, "quantity": 10}, {"product_id": 3, "quantity": 1}]}
    →  409  {"error": "Not enough stock", "product_id": 3}
GET /orders/9/items  →  404  {"error": "Order not found"}
GET /products/4  →  200  {"id": 4, "name": "Pen", "price": 2, "category": "office", "stock": 120}
POST /orders  {"customer_id": 2, "items": [{"product_id": 7, "quantity": 1}, {"product_id": 99, "quantity": 1}]}
    →  404  {"error": "Product not found", "product_id": 99}
POST /orders  {"customer_id": 4, "items": [{"product_id": 4, "quantity": 10}]}
    →  201  {"id": 9}

The second order fails on the lamp, so its pens are not saved either: there is no order 9, the pens' stock is still 120, and the next good order takes id 9.

Your task: in POST /orders, start a transaction before the order's INSERT and commit it before the 201 answer. Take each item's quantity off its product's stock. Answer 409 when there isn't enough stock and 404 when the product doesn't exist, and roll back before both answers, so a failed order leaves nothing behind.

Assignment
Follow the Coding Tutorial to wrap the order in a transaction, update the stock, check it before each item, and roll back every order that can't be completed.

Hint
Both checks go right below the line that reads product, before anything is written for that item. The null check comes first: when there is no product, (long)product["stock"] crashes.