An Index for a Slow Search in Python

The orders page finds one customer's orders with WHERE customer_id = ?. How does SQLite find them? You can ask it: put EXPLAIN QUERY PLAN in front of a query, and SQLite doesn't run it; it answers rows that describe how it would run it. Each row has a "detail" column, a sentence about one step.

A plan that says SCAN means SQLite reads every row of the table and keeps the matches: quick for 7 orders, slow for 7 million. An index is a sorted list of one column's values, each pointing at its rows, like the index at the back of a book. With one, the plan says SEARCH: SQLite jumps straight to the matching rows. Here is an index on another table and column, for searches by category:

db.execute("CREATE INDEX IF NOT EXISTS idx_products_category ON products (category)")

IF NOT EXISTS lets the app run this line every time it starts without failing. An index changes how rows are found, never which rows: every answer stays exactly the same. Your code must give these answers:

GET /debug/plan  →  200  {"plan": "SEARCH orders USING INDEX idx_orders_customer_id (customer_id=?)"}
GET /customers/3/orders  →  200  [{"id": 4, …, "status": "shipped"}, {"id": 7, …, "status": "paid"}]
GET /customers/5/orders  →  200  []
GET /debug/queries  →  200  {"queries": 4}

Asking for a plan is not a query the shop runs, so it goes through db.execute, not run_query, and isn't counted: the 4 queries are 3 for customer 3's orders and 1 for customer 5's.

Your task: add GET /debug/plan with a function show_plan that runs the orders page's query with EXPLAIN QUERY PLAN in front, for customer 1, and answers the first row's detail as {"plan": ...}. It will say SCAN orders. Then, below db.row_factory, create the index idx_orders_customer_id on orders (customer_id), and the plan becomes a SEARCH.

Assignment
Follow the Coding Tutorial to show the plan of the orders query at GET /debug/plan, then add the index that turns its SCAN into a SEARCH.

Hint
rows[0]["detail"] is the first row's sentence. A tuple with one value needs a comma: (1,).