Queue Multi-Organization Support

This document is a WIP and might contain errors, omissions, or incomplete thoughts. Help me polish it!

This document will outline my thoughts on how to modify the Queue to support multiple organizations. In #99, I first talked about the need for this in the context of UBC. In #186, I talked about expanding our authentication/login system to support more than just Shibboleth. The work that will be outlined here will supercede both of those issues.

The goal for this work is for queue.illinois.edu to be able to serve and support more than just individuals at Illinois. #99 was approaching this from the perspective of how to make the Queue more easily deployable for other institutions, but I think a better long-term strategy is to consider the Queue as software-as-a-service. The Queue will still be hosted by us, but it'll be able to simultaneously serve many other schools/organizations/etc.

Organizations

An organization can be thought of as analogous to a GitHub organization, whereas courses/queues are akin to a GitHub repository. An organization is a grouping of courses and queues with a common purpose. For instance, there could be an "Illinois" organization and a "UBC" organization. There could also be more purpose-built organizations, like one dedicated for Sail in which queues are a form of asking questions to presenters.

Organizations can have a domain associated with them to allow restricting them to members of the organization. For instance, Illinois would have the "illinois.edu" domain associated with it. Owners of queues could then mark queues as "organization-only", thereby restricting the queues to only being viewed/interacted with by users with "@illinois.edu" email addresses.

We'll need to add another level of "admin" to our permissions levels. So, there will be global admins and organization admins. Global admins are still all-powerful. Organization admins can create and delete courses within their organization and add and remove other organization admins, but they cannot modify other organizations or view global admin pages.

Users

Currently, there's a one-to-one mapping between Illinois Shibboleth identities and users. This is too restrictive; for multi-organization support to work well, we need to support multiple login methods too. To start with, we'll support the following:

Local email/password combos
Illinois Shibboleth
Google

In the future, we could support more providers, including SSO for other educational organizations. This is roughly what Gradescope does: defaults to login with email/password, but allows you to link SSO from select organizations or a Google login to your account.

To support this, we'll separate the concept of a user from the account that they use to log in. A single user could be simultaneously associated with multiple accounts. A user can link and unlink themselves from accounts at will. An account may or may not be a login provider - for instance, I could link my personal zzz@gmail.com account with my school yyy@illinois.edu email to gain access to organization-only queues.

To check if a user belongs to an organization, we'd query all of their accounts and check if any of them match the domain of the relevant organization.

Necessary Changes

This would be a relatively large departure from the way things are currently organized, and we'll need a variety of changes to account for it.

Database changes

We'll need to add a new organizations table to store information about organizations. At a minimum, we'll need:

id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(255),
domain VARCHAR(255) # e.g. "illinois.edu"

We could also have shortcodes for organizations like we do for courses. Good/bad idea? Let me know in the comments.

We'll need to adjust the courses schema to have an organizationId foreign key so that we know which organization each course belongs to.

We'll need to adjust the queues table to include a flag to indicate if a queue is organization-only.

We'll need to add an accounts table to store information about user accounts. I'm envisioning something like this:

id INTEGER PRIMARY KEY AUTOINCREMENT,
type VARCHAR(255), # One of 'google', 'local', 'email', 'illinois', etc
uid VARCHAR(255), # A unique identifier for the given type (an email address, account id, etc.)
password_hash VARCHAR(255), # Will be null for anything but local accounts
CONSTRAINT accounts UNIQUE (type, uid)

API changes

We'll need new CRUD endpoints for organizations at /api/organizations/. We'll also want to add endpoints under that, such as /api/organizations/:organizationId/courses and /api/organizations/:organizationId/queues for listing courses and queues under a certain organization, respectively.

We'll need to change the create endpoint for queues to support an organization-only flag.

Frontend changes

We'll need a new landing page for users that will allow them to choose their "home" organization.
We'll need to extend the login page to include all supported account types.
We'll need to enable the creation and management of email/password-based accounts.
We'll need to add an organization-only toggle to the queue creation+settings pages.

Remaining questions

There are a few things that I'm still not totally sure how to handle or otherwise don't have a definitive answer to:

[ ] Say I sign in with my "zzz@gmail.com" and then independently sign in with my "yyy@illinois.edu" address. In this case, two new accounts would be created. Should I be able to merge those two independent accounts? I'm leaning towards no; our recommended solution here would just be to delete one of them.
[ ] For organization-only queues, should they be invisible to users not in the organization? Or should they still be visible, but just unable to be interacted with?
[ ] Should one be able to deem an entire organization as "organization-only", as in all queues are unable to be viewed by anyone not in the organization?
[ ] Should organizations have shortcodes? I'm leaning towards yes, as that would allow course shortlinks to work sensibly. Two organizations might both have courses with the cs225 shortcode, so if organizations have shortcodes too, I could have course shortlinks such as /q/illinois/cs225 and /q/ubc/cs225.

illinois / queue

Multi-organization support #282