WPI Greenboard

Noah Cyr, Weaver Goldman, Surbhi Kapoor, Willem van Oosterum

Final Presentation

CS 542

Meet the Team

Project Overview

Application Capabilities

• Display anonymized leaderboards of students and majors with top carbon emissions
• Be able to filter students by major
• Visualize as a bar chart
• Display a timeline of emissions and provide statistics on the packages from that timeframe
• Display detailed package history and emissions breakdown for a selected student
• Display and visualize emission data for different service types
• Filter students by major and class year
• Visualize as a bar chart
• A feature that allows users to add packages to their profile via its tracking number and other information
• Utilize package carrier APIs when available to provide additional information about a package

Target Users

• WPI Students - Primary users of the application
• View personal package details & carbon footprint
• Compare habits on anonymized leaderboard
• Receive recommendations for reducing emissions
• WPI Faculty
• View aggregated data across majors
• Use insights in class discussions, research, or sustainability initiatives
• WPI Mailroom Workers
• View mailroom trends
• WPI Administrators
• See global emission trends
• Implement policies to encourage reduced emissions

Development Approach

• We took an Agile-esque approach to the design and development of our system
• Weekly meetings where’d we work as a team and assigned tasks
• Branches for different purposes. Ended up having 21 PRs
• We often reviewed each other’s work

Tracking API Details

• Two functional and free APIs - FedEx and UPS
• Get real weight and distance information
• Get carrier service type to determine emission factor (based on mode of transport)
• Geocode or use cached location information
• Use emission factor (from research) values to determine final emission
• Built out synthetic data script for Amazon
• All others were minority number of packages with carriers that did not provide open APIs

Emissions Calculations

Database Details

Data Sources:

• Data Sourcing
• Primary data is sourced from QTrak
• Transaction reports are extracted in weekly batches.
• Each record captures a package event: Stored, Routed, or Delivered.
• Anonymization & Privacy
• Remove all Personally Identifiable Information (PII)
• Hash student box numbers for anonymous tracking
• Carrier APIs
• Query APIs to add external data:
• Origin, dimensions, weight, transit history

Entity Relationship Diagram

UML Diagram

Final Architecture

• Front-end
• UI
• Framework: Streamlit
• Back-end
• Database communication
• Additional computations
• Framework: FastAPI
• Database
• PostgreSQL

Leaderboard Workflow

Query Analysis

Our most popular query:

• Get Student Leaderboard

​

Join options considered:

Computed on a few days of data

• Unclustered Index Join
• 476 I/Os
• Hash Join
• 27 I/Os

We see reasonable agreement with the engine’s chosen plan

Query Timing

• Student Leaderboard
• Runtime: 0.0147s
• Major Leaderboard
• Runtime: 0.0075s
• Full package history for a specific person
• Runtime: 0.0196s
• Individual carbon emissions timeline
• Runtime: 0.0017s

​

• Carbon emissions timeline for a major
• Runtime: 0.0097s
• Carbon emissions timeline for all people
• Runtime: 0.0281s
• Display emissions calculations
• Runtime: 0.0096s
• Statistics on shipping service types
• Runtime: 0.0172s

​

Alternative Emissions Format Trigger

We automatically compute equivalent trees planted and equivalent miles driven for each package.

Person Carrier Stats View

• Computes carrier (UPS, Amazon, …) statistics for each person

​

• Gracefully handles null values

Leaderboard Page

Leaderboard Page (Bar Chart View)

Single Person View

Timeline View

Add Package - Tracking# Lookup

Add Package - Emission Calculator

Service Types (Table View)

Service Types (Bar Chart View and Controls)

​

Demo

Add Package Demo

Service Types Page Demo

Issues

• Incorrect ERD (pointed out during the proposal - now fixed)
• Iterating with Streamlit was slow until adjusting the Docker setup
• Limited access to USPS and Amazon data: restricted completeness of package coverage
• Overall data inconsistencies between the different carriers
• Timeline view can be misleading because most people do not have packages delivered everyday

Lessons Learned

• Real-world implementation is far more complex than theory
• Turning classroom ideas into working features required deeper problem-solving
• Full-stack development significantly expanded our technical skills
• Gained experience with PostgreSQL, Docker, FastAPI, SQLModel, and Streamlit
• Choosing the right tools enabled fast iteration and efficient progress
• Selecting frameworks aligned with our experience made development more efficient
• Collaboration strengthened our problem-solving and system-level thinking

Future Work

• Leaderboard Query Caching
• Cron jobs
• Enhanced Data Sources
• More data sources
• Automated data pipelines
• Authentication
• WPI SSO

Thanks!

Noah Cyr, Weaver Goldman, Surbhi Kapoor, Willem van Oosterum

CS 542