re-design storage/retrieval of group hierarchy

[Matt-Holland]

This ticket should be discussed with the system architect (Rabble) and divided into smaller pieces. The first challenge is deciding how to manage the permissions conceptually.

Currently, we have a "affiliations" table which is a one-to-one mapping between a group and its affiliate, where an "affiliate" allows the "group" to view/edit it's data. But according to our product plan, if a top-level group (e.g. a Region) has permissions to view/edit a second-level group (e.g. a State), and then that State has view/edit permissions on a third-level group (e.g. a District), then the permissions should be transitive upwards through the hierarchy -- the top-level group should automatically have view/edit permissions on the third-level group, without that being explicitly set. Also, we need to handle the case where there are "n" levels of hierarchy -- we don't know in advance that there will be only 4 levels, and other levels may be added at any time. Currently the only way to deal with this is to recursively search every affiliate, to find all of THEIR affiliates. This is 1) expensive and slow 2) gets slower rapidly as we add more groups 3) requires us to watch out for self-referential loops (where a group is an affiliate of it's own affiliate, etc.)

SO, after we pick a new architecture for storing that info, we'll make tickets for particular results similar to the below:

1. When I add a new Neighborhood (level 4), and share access upward to the District (level 3), then the State (level 2) and Region (level 1) should automatically also have access to the Neighborhood, based on their pre-existing relationship with the District through the State.

2. As a user, I want to see the hierarchy without seeing groups duplicated, or in different parts of the hierarchy at the same time (* unless I intentionally put them there)

3. as a system owner, I’d like this not all depend on n-levels of recursive queries, for obvious reasons.

[Matt-Holland]

Rabble has done a bunch of work on this, but has broken tests, please check with him before any progress on this.