Design a SaaS Solution for an Airline

Doing this for fun!

Wilson

Apr 10, 2023

I’m going to try to do these exercises periodically for fun

I saw this tweet regarding outdated software in aviation and wanted to tackle this exercise for fun.

Let me know what you think and here we go

Goals:

A solution that manages fleet allocation and flight scheduling
A solution that incorporates resource allocation data
We are building this product for the employees of the airline and not consumers

Assumptions and Scope:

Product needs to cover several elements:
1. Accept data of customers purchasing flights, changing flights, and cancelling flights through integrations
2. Fleet management and resource (crew members and pilots) allocation
  1. Crew members/pilots will choose their preferences on schedule and destinations 1-12 months before flights
  2. They will see their schedule one month prior
3. Managing flight cancellations and delays
Out of Scope:
1. Go-to-Market strategy (the analysis of should we actually penetrate the market and how vs. not)
2. Consumers buying flight tickets
3. Anything pricing related: pricing strategy, pricing optimizer, loyalty programs
4. Assigning gates

Architecture Design

Normal OAuth - 3 roles: System Admin, Captain, Flight Attendant
API gateway of all API’s
1. We expose our API’s so that our system can accept customer bookings via an Integration and update seat info
2. Delays and Cancellations
  1. Expose our API's so that our system can accept delays and cancellations data
  2. If Delayed, send a webhook to trigger a creation of new Flight record
3. More details are at bottom in Details - Appendix section.
Load balancers to scale the global system horizontally
Database
1. I chose a NoSQL Document Database like CouchDB or MongoDB to handle the large amounts of data that can be retrieved instantaneously

Platform Design - Entities

There will be four entities. Reminder that our database is like MongoDB - NoSQL Document Database.

Note: I’ve provided more a concrete examples at bottom in Details - Appendix section.

Team Member

{
  "id": 264,
  "name": "Connor Captain",
  "role": "Captain",
  "home_base": "San Francisco",
  "date_started": "2008-06-23"
}

Fleet
```
{
  "id": 359
  "Make": "Boeing",
  "Model": "737",
  "Status": "Active"
}
```
1. Fleet records needs to be consistent with Flight schedule. For example, if the fleet ID: 623599 is being used for the SFO-IAH route on April 3rd, then that fleet record cannot be reused during that exact time

Flight

All flights will have an origin and a destination. A scheduled departure DateTime and arrival DateTime. Crew members. Status. Fleet.

{
  "Origin": "New York",
  "Destination": "Los Angeles",
  "ScheduledDateTimeDeparture": "2023-05-01T10:00:00Z",
  "ScheduledDateTimeArrival": "2023-05-01T13:00:00Z",
  "TeamMemberCaptain": {
    "id": 578,
  },
  "TeamMemberFlightAttendants": [
    {
      "id": 6969,
    },
    {
      "id": 985,
    }
  ],
  "FlightStatus": "On time",
  "Fleet": {
    "id": 8592963
  },
  "DomesticOrInternational": "International"
}

Adding the Scheduler Entity to Team Member

Cabin crew will log into the system and create their schedules more than one month ahead of time. They bid for their routes according and submit time off. They have lower and upper limits on how much they work per week as determined by admins.
New example of Team Member record in JSON format:

{
  "id": 264,
  "name": "Connor Captain",
  "role": "Captain",
  "home_base": "San Francisco",
  "date_started": "2008-06-23"
   "scheduler": {
      "flight": {
         "id": ObjectId("61520a6d74f6b612bf8ce40b"),
      "flight": {
         "id": ObjectId("58204b6y07f5b367iq8eo58v"),
      "flight": {
         "id": ObjectId("40190d2p73e4r109ou5nk65c"),
      }
   },
   "time_off": [
      {
         "start_date": ("2022-02-01T00:00:00Z"),
         "end_date": ("2022-02-07T23:59:59Z"),
         "status": "approved",
      },
      {
         "start_date": ("2022-03-01T00:00:00Z"),
         "end_date": ("2022-03-07T23:59:59Z"),
         "status": "approved",
      }
   ]
}

Platform Design - Workflows and Events Requirements

When the flight becomes full, there will be an event driven trigger that changes the Status of the Flight to “Full” and the API endpoint will return errors to additional POST requests
A fleet cannot have a flight within 2 hours (Scheduled DateTime Arrival - Scheduled DateTime Departure) of another flight
The Fleet of a Flight’s Origin must be the same airport as the Fleet of it’s next Flight’s Destination
The cabin crew members create Scheduler records. There will be lower and upper limits as set by admins. For example, if a Flight Attendant tries to submit their time for 10 hours for the week and the system requires them to submit for 20 hours a week, then the Flight Attendant will receive an error and will not be able to submit their entries.
There is an automatic scheduler job that iterates through all of the team members, fleets, and flights and is a linear programming problem. A human needs to approve the proposed schedule on a monthly basis

How Would I Measure Success of Beta and First Releases

Adoption
1. How many users are logging in per week?
2. Is the Flight entity being used. Is the automatic scheduler being used
Response times to delays and cancellations - have they been reduced?
Customer feedback - because of our MVP solution (all fields are required, we meet the minimum requirements for a functioning system), we’ll want to see what appeals to the end-user that solves pain points.

Competitive and Market Landscape

NavBlue - a software scheduling product and direct competitor
PagerDuty / ServiceNow for the immediate response time problem (for delays and cancellations)

Feature Requests for Post-MVP

Mobile app for Cabin Crew members submitting their schedules
Notifications to Cabin Crew members that they need to update their schedules
Integration with Okta or other SSO providers
Reports and Dashboards
1. As a flight attendant, I’d like to know how often I’m working and how much time I’m taking off
2. As a Regional Director, I’d like to see which flight segments are getting the most full so that I can consider adding a flight segment for that route per day/week
Enhancement to above: Generative AI on Reports and Dashboards

How Would I Prioritize Future Features

Definitely mobile app first because cabin crew would like to submit their schedule requests and updated requests

Where Do I See this Product in 3-5 Years after Beta?

As a consumer, when you’re booking a flight, you sometimes wait until the price goes down. Sometimes you wait too long and the prices increase. How do the algorithms work and decide when/how to increase/decrease prices?
1. I can see our product expanding to be a scheduler and pricing strategy optimization product
Gate scheduling - this would expand to be a SaaS solution for airports and not just airlines. This is also a major pain point for not only airlines but also consumers

Details - Appendix

Bonus requirement
1. Admins will be able to configure certain settings. For example, frequency of ability for crew members to update their schedule. Set minimum and maximum employee working hours per week
API gateway of all of your API’s, such as:
1. We expose our API’s so that our system can accept customer bookings via an Integration and update seat info
  1. POST /flight/seats
    1. Parameters: flight_id, seat_id
2. Delays and Cancellations
  1. Expose our API's so that our system can accept delays and cancellations data
    1. PUT /flight
      1. Parameters: flight_id, flight_status
  2. If Delayed, send a webhook to trigger a creation of new flight time record
    1. POST /flight/flighttime
      1. Parameters: flight_id, new_flight_time
    2. Trigger a notification and hit external API’s to notify consumers of new flight time
Database Decision
1. This product will be looking to handle and utilize large amounts of data (and won’t need to do a lot of conditional queries). Therefore and per the CAP Theorem, we will prioritize partition tolerance and availability over consistency, and therefore we will choose a NoSQL database as opposed to a SQL database.
2. I chose a Document NoSQL over a Key Value NoSQL Database because a Document Database typically supports more advanced indexing capabilities. For example, as a flight attendant, it might be very important for me to look at specific destinations and schedules within a small timeframe, and querying for that will be important for me to schedule my time off. This would not be a simple, key-based database.
We want Load Balancers to scale horizontally because in case one node fails, this would be catastrophic if didn’t have alternative nodes, since we expect our customers to use this product as their global system.
Microservices like Kubernetes and Dockers
Encryption with AWS KMS
Platform Design that includes the relationship among entities using Documents and Subdocuments for a database like MongoDB

{
  "Origin": "New York",
  "Destination": "Los Angeles",
  "ScheduledDateTimeDeparture": "2023-05-01T10:00:00Z",
  "ScheduledDateTimeArrival": "2023-05-01T13:00:00Z",
  "TeamMemberCaptain": {
    "id": 5498,
    "name": "Connor Captain",
    "role": "Captain",
    "department": "Flight Deck",
    "hire_date": "2010-01-01",
    "last_review_date": "2022-02-01"
  },
  "TeamMemberFlightAttendants": [
    {
      "id": 458943,
      "name": "Jane Smith",
      "role": "Senior Flight Attendant",
      "department": "Cabin Crew",
      "hire_date": "2018-09-01",
      "last_review_date": "2022-03-15"
    },
    {
      "id": 458940,
      "name": "Bob Johnson",
      "role": "Flight Attendant",
      "department": "Cabin Crew",
      "hire_date": "2019-03-01",
      "last_review_date": "2022-03-15"
    }
  ],
  "FlightStatus": "On time",
  "Fleet": {
    "Make": "Boeing",
    "Model": "737",
    "Status": "Active"
  },
  "DomesticOrInternational": "International"
}

Resources

https://www.mongodb.com/nosql-explained/data-modeling
https://developer.pagerduty.com/docs/db0fa8c8984fc-overview
https://www.navblue.aero/product/n-crew-planning/
ChatGPT
My friend, Natalie, who is a flight attendant

WFH Nomad

Discussion about this post