Backend Developer · Projects

Project 2: A fast, fresh menu

Marking guide version 1 · Lessons: BE3 lessons 1–4, BE4 lessons 1–5, BE8 lesson 3, BE9 lesson 5, BE10 lesson 3

The problem and who it's for

The kitchen menu is TiffinBox's busiest route: every customer opens it several times a day. In the starter it runs 62 database queries per request, and with just 10 customers at once each answer takes over a second. Kitchens also change prices during the day, and a customer who sees ₹90 and is charged ₹110 loses trust.

Scope

• Replace the starter's N+1 queries in api/menu.js with one or two queries.
• Add an index that supports the menu query, as a new migration.
• Cache each kitchen's menu in Redis with a time-to-live, and load it once when many requests miss together.
• Clear the cached menu when a dish changes, so the next request is fresh.
• Keep the menu answering from the database when Redis is down.

Not included

• A content delivery network or HTTP caching in front of the API (discuss it in your report if you like).
• Personalised menus or search.
• Redis clustering or replication.

Before you start

• Core: Module 55 (SQL fundamentals), Module 56 (advanced queries), Module 57 (transactions and indexes), Module 60 (PostgreSQL).
• Path: BE3 and BE4 complete; Project 1 complete (the menu change route needs its scope check).
• npm run test:cache runs (7 of its 9 tests fail at first).

Starter files

• tiffinbox/api/menu.js: the slow menu route with N+1 queries and no cache.
• tiffinbox/db/migrations/001_start.sql: the first migration; add yours beside it as 002_...sql.
• tiffinbox/tools/load.js: the small load test that prints p50 and p95.
• tiffinbox/tests/cache.test.js: the 9 acceptance tests for this project.

Download everything at once: starter/backend-starter.zip.

Milestones

1. Run npm run test:cache and record the starter's load test (npm run load, with the API started with RATE_LIMIT=1000000).
2. Rewrite the menu query and check its plan with EXPLAIN ANALYZE (tests 1 to 3).
3. Add an index migration and run npm run db:migrate.
4. Add the Redis cache with a time-to-live and single-flight loading (tests 4 to 6).
5. Clear the key on change and add the Redis-down fallback (tests 7 to 9).
6. Record the load test again and run all 9 tests three times in a row.

Expected outputs

• 9 of 9 cache tests passing, three runs in a row.
• A before-and-after table: queries per cold request, requests per second and p95, labelled as practice-kit figures.
• The EXPLAIN ANALYZE output before and after your index.

Acceptance tests

Failure and recovery exercises

1. Remove the line that deletes the cached key after a change and run test 7. Explain what customers would see, then restore it.
2. Drop your index and run EXPLAIN ANALYZE on the menu query. Compare the plans and timings, then recreate it.
3. Turn off single-flight loading and run test 5. Explain the stampede in two sentences, then put it back.

Evidence to submit

• The deliverable link to your repository or folder.
• The output of npm run test:cache showing 9 passed, pasted into test evidence.
• Your before-and-after load-test table and EXPLAIN ANALYZE output.
• A short recording of a price change showing on the next request, and the menu answering with Redis stopped.

Marking guide and self-check

Pass rule: every Safety-critical (S) and Mandatory (M) item must be met. A single unmet safety item means the project is not complete, whatever else is done. Quality items are for your own improvement.

Your answers are saved in this browser on this device. Self-checked means you checked your own work against this guide; it is not a reviewer's approval. Never paste passwords, keys, tokens or real customer data here.