Project Story: GateGroup Trolley Logistics Inspiration The inspiration for this project came from observing the logistical challenges faced by airline ground crews. The process of restocking airline catering trolleys is often a manual, paper-driven task. This reliance on paper checklists is not only slow but also prone to human error, leading to incorrectly stocked trolleys, potential flight delays, and waste. We saw an opportunity to create a simple, modern solution to digitize this workflow, making it faster, more accurate, and entirely paperless.
How We Built It Our solution is a REST API that serves as the backbone for a mobile application. The core of our project was built using a Node.js/Express backend, chosen for its speed and robust ecosystem. For the database, we selected PostgreSQL due to its stability and powerful features, particularly its advanced support for JSON.
The entire development environment is containerized using Docker, with a docker-compose.yml file orchestrating the API service and the PostgreSQL database. This ensures a consistent and easily reproducible setup. We managed our database schema evolution using Flyway, which allowed us to version-control our database and apply migrations systematically as our data model grew more complex.
The API provides a clear, step-by-step workflow:
Scan: A user scans a trolley's QR code. The API endpoint GET /api/trolley/{qrId}/flight identifies the trolley and its assigned flight route. Configure: Based on the user's current airport location, the GET /api/trolley/{qrId}/config endpoint returns the precise, detailed inventory specification for that leg of the journey. Log: After the user counts the items, the app sends the results to POST /api/replenishment/log, which records the entire activity, including the user, timestamps, and any discrepancies. What We Learned This project was a deep dive into backend development and database design. One of the most significant learning experiences was leveraging advanced PostgreSQL functions to build our API responses. Instead of fetching data with multiple queries and building nested objects in the application layer (which can lead to the N+1 query problem), we wrote complex SQL queries using json_build_object and json_agg.
As you can see in our configurationRepository.js, this allowed us to construct the entire nested JSON response for a trolley's configuration directly within the database. This approach is incredibly efficient and taught us the value of pushing complex data-shaping logic down to the database layer.
// A snippet from our repository showing the use of PostgreSQL's JSON functions // ... SELECT json_build_object( 'trolley_config_id', tc.trolley_config_id, 'name', tc.name, 'baskets', ( SELECT COALESCE(json_agg(json_build_object( 'position_identifier', tcb.position_identifier, 'name', bc.name, 'products', ( SELECT COALESCE(json_agg(/...products.../)) FROM product p /...joins.../ ) )), '[]'::json) FROM basket_config bc /...joins.../ ) ) as config FROM trolley_config tc WHERE tc.trolley_config_id = $1; // ...
Challenges We Faced Our primary challenge was data modeling. Designing a schema that could flexibly handle the relationships between flights, trolleys, and their direction-specific configurations was complex. We had to ensure that a single trolley could have a different stock list for its outbound flight (e.g., from DFW) versus its return flight (e.g., from LHR).
Writing the SQL queries to generate the nested JSON was also a major hurdle. Our initial attempts were flawed and returned null values, forcing us to debug and refine our queries iteratively. We also encountered a classic integration issue after a git merge where a function (db.getClient()) was missing from our database module, causing the application to crash. Debugging this reinforced the importance of careful code integration and understanding the full dependency chain in our application.
Log in or sign up for Devpost to join the conversation.