Merge User, Organization and Team models into a new Principal model

[jace]

The User, Organization and Team models serve well-defined but overlapping needs that has led to some database kludge:

1. Users and Organizations share a single namespace. Since SQL doesn't provide a way to enforce this, we do this in the app layer.
2. Client apps can be owned by either a User or an Organization. We do this by having two nullable foreign key columns and ensuring at least one is set in the app layer.
3. Permissions can be assigned to either a User or a Team. This is done with two separate models that have the exact same columns, only different foreign keys.
4. In future, we will want to track email addresses that belong to a Team or an Organization instead of a User (addresses such as jobs@ or info@). UserEmail is currently hard-linked to User. If we have to introduce a new model, we'll also need to duplicate UserEmailClaim and update all the views that refer to these models.

Since this kludge is only going to grow, I propose merging the User, Organization and Team models into a unified Principal model, with one new column identifying what sort of principal it is. SQLAlchemy's single table inheritance feature will automatically assign the same table to separate User, Organization and Team classes.

The only apparent problems with this approach:

1. Teams currently don't have names. In future they will be names that are unique within the Organization. Since any name will be globally unique as per SQL rules, the name will have to be stored as "org-name/team-name". Renaming the Organization should cascade to all Teams.
2. Client apps cannot be owned by Teams and Permissions cannot be assigned to Organizations. These restrictions will now have to be enforced at the app layer.

[miteshashar]

Permissions cannot be assigned to Organizations
There is no harm visible to me, in doing that. Only gains. Take the example of hasjob. In the application layer, whenever a new organisation shows up in hasjob, permissions to post jobs is auto-assigned. At a certain point in time, if a moderator finds the organisation spamming, he/she can just revoke this permission becoming a single blissful click for the moderators in their mission to control spam.

[jace]

@Mitesh this ties into our previous discussion on automatic team membership based on email domain. The membership is to a team within the organization, not to the organization itself, since organizations have no concept of members. Therefore, a team within the organization is assigned the permission, not the organization itself.

[miteshashar]

Yes, of course. That rings a bell.

[jace]

Reconsidering principal merger

User, Team and Organization are obviously three very different concepts, and combining them into a single table will only cause us trouble when the wrong type is accidentally used. The database can't protect against this. App-level code must.

Instead, let's examine the overlaps and consider alternate solutions.

Usernames

Since usernames need to be unique across tables, perhaps they can be moved out into a separate model, say Username. All three tables (or two if Team is excluded) can have a foreign key reference to the Username model, optional on User and mandatory on Organization.

Pros:

• Simplifies the process by which duplicate usernames are checked for.
• Allows reserving usernames by simply making entries to this table.

Cons:

• Multiple entities can claim the same username, since databases only allow one-to-many foreign key references, not one-to-one. While a unique constraint on the foreign key can prevent two users or two organizations from making the claim, this won't stop cross-table duplications (one user and one organization, both claiming the same username).
• There is no discovery process to go from username to named entity. This will require a database join across all three tables to return one row.

Agency

Only the User model has agency. There can be no condition under which a Team or Organization can login to the website. This is easier to enforce if User is a distinct model with database-level constraints.

Ownership

In contrast, any of these three models can own resources (the original idea behind this ticket, although #186 conflicts by recommending removal of ownership from organizations). Ownership can be disambiguated from agency and other model-specific data by creating a new model named Principal, similar to the Username solution above. SQLAlchemy's joined table inheritance can shield us from common app-level mistakes, but the cons described above remain.

Any model that needs to define ownership across entities can refer to the Principal model instead of User or Team, etc.

[jace]

Client apps should also be considered principals as they can own data, have access rights, and have security credentials in Lastuser. The Wikipedia page for principal (computer security) explains why:

A principal in computer security is an entity that can be authenticated by a computer system or network. It is referred to as a security principal in Java and Microsoft literature.

Principals can be individual people, computers, services, computational entities such as processes and threads, or any group of such things. They need to be identified and authenticated before they can be assigned rights and privileges over resources in the network. A principal typically has an associated identifier (such as a security identifier) that allows it to be referenced for identification or assignment of properties and permissions.

[jace]

Since agency is not a property of all types of principals, the term 'actor' may be used to refer to only those principals that have agency.

A client app using an access token on behalf of a user is not an actor. The represented user is the actor. The client app may be described as an 'agent' here.

Similarly, in #153, one user may access the account of another user. In this case, the first user becomes an 'agent' while the second user is the 'actor'.

However, when a client app is accessing its own resources, it is the 'actor' itself, and there is no 'agent'.

[jace]

Deprecated in favour of #232. We've successfully separated agency and ownership and incorporated some design changes into Coaster, with actual implementation in other apps to follow. This ticket can now be retired in favour of apps doing their own management.