<!doctype html>

Storyboard for Food Node

Food Module — Road-Trip Quest

A progressive, gamified learning segment using a coastal road-trip (San Diego → Los Angeles → San Francisco → Seattle) to teach relational database fundamentals through food-themed pitstop tasks.

🎯 Learning Objectives

  • ✅
    Understand and implement basic CRUD operations against a relational database.
  • ✅
    Design a normalized schema for a food-related domain (dishes, ingredients, join tables).
  • ✅
    Write parameterized queries and avoid SQL injection.
  • ✅
    Use transactions to maintain consistency across multi-table updates.
  • ✅
    Build and consume RESTful endpoints for create/read/update/delete operations.
  • ✅
    Implement client-side UX for creating, editing, and archiving records with accessibility in mind.
  • ✅
    Demonstrate soft-delete vs hard-delete and archival strategies.
  • ✅
    Interpret aggregated results using GROUP BY, COUNT, SUM and simple analytics queries.
  • ✅
    Apply basic indexing and measure performance differences for common queries.

🎬 Storyboard: Scenes & Flow

Opening — Road-Trip Map

Orient the player visually: show route with SD → LA → SF → SEA markers and a progress/gas XP gauge. 'Start trip' unlocks San Diego.

Steps

  • Map SVG with four markers and tooltips
  • Progress bar / gas gauge representing XP
  • 'Start trip' button to fetch the SD manifest

Technical notes

Client route state machine; server returns pitstop config at /api/module/food/manifest; seed data returned on initial load.

San Diego — Build the Baja Bowl

Learner creates a dish using a form; dish and ingredient records are created with join entries. Reward XP and update pitstop pantry.

Steps

  • Create Dish form with accessible fields and photo upload
  • Toast notification on success (+50 XP)
  • Pantry list showing created dishes for SD

Technical notes

POST /api/dishes creates dish, ingredients, and dish_ingredients in a transaction; server-side validation and file storage (S3/local).

Los Angeles — Food Truck Tasting

Learner filters and searches dishes, uses Dev Console to see safe sample SQL, and observes index impact.

Steps

  • Filter panel (ingredient, min_cal, category) with keyboard support
  • Dev Console panel showing parameterized SQL examples
  • Paginated list with quick detail drawer

Technical notes

GET /api/dishes supports pagination and filter params; database index demo (ingredient.name) and simulated metrics.

San Francisco — Gourmet Pop-Up

Learner edits a menu with transactional updates, previews diffs, and resolves concurrent edit conflicts.

Steps

  • Edit modal with 'Preview Changes' diff
  • Conflict resolution modal when version mismatch occurs
  • Clear success/failure feedback for transactions

Technical notes

PUT/PATCH endpoints wrap multi-table updates in transactions; optimistic locking via version column; rollback simulation endpoints for testing.

Seattle — Sustainable Cleanup with Clam Chowder

Learner archives or hard-deletes the clam chowder, observes cascading effects, and views analytics reports.

Steps

  • Archive toggle and archived list in admin view
  • Hard delete confirmation modal with cascade warning
  • Analytics dashboard with charts and exportable CSV

Technical notes

Soft delete implemented with deleted_at; hard deletes cascade to dish_ingredients; analytics endpoints aggregate data with GROUP BY.

📊 Analytics & Acceptance Criteria

Analytics & Metrics

  • Top ingredients used across all cities (ingredient name, usage_count).
  • Average calories by city.
  • Number of dishes per category (bowl/taco/salad/etc.).
  • Total archived vs active dishes (by city).
  • Most common pairings (joined ingredient pairs) for advanced analytic example.

Acceptance Criteria

  • San Diego: Created dish is visible in SD pitstop pantry (GET /api/dishes?city=sd).
  • Los Angeles: Filter by ingredient and city returns correct results and uses at least one indexed field in the demo.
  • San Francisco: Transaction rollback behavior is verified with integration tests (failed nested operation leaves DB consistent).
  • Seattle: Soft archive hides items from normal lists but they appear in archived admin list; hard delete cascades remove related records.
  • Secure queries: endpoints use parameterized queries (no SQL injection).
  • Accessibility: Forms and controls meet ARIA and keyboard navigation standards; color contrast >= 4.5:1.
  • Seed data: At least three seeded dishes per city (including clam chowder in Seattle).
  • Story/UX copy exists for each pitstop.

🧱 Task Checklist

Made with ❤️ for the class project.