Back-end for the "friend request" journey

[michaelsnook]

Friends data model: we need to fill out this friend request action table. We're currently just using an actions table and a view; want to add a unique relationship identification table and have that be the feeding ground for the view, and have actions reference that table specifically.

• Solution seems to be a single friend_relationship type table that has unique set for the uid_from and uid_to.
  • The initial friend request action will just create this friend_relationship table; that action will no longer appear on the actions table.
• then all the actions can have their own ID as well as a friend_relationship_id and do not need to store the to and from fields.
• then some view like friend_relationship_current can be a view that takes the most recent record from the friend_request_action table for each of these unique relationship_ids and also calulates another overall status of "no | yes | pending"
• then the RLS for the actions can validate inserts based on this "current" status
  • initial invites can only be sent when overall status is "no" or the relationship is not present
• the client can respond to RLS mismatches by invalidating the cache only

[michaelsnook]

Starting over on the implementation here:

1. We'll have 2 tables and 1 view:
   • friend_relationship - a stable reference with an ID of its own
   • friend_request_action - actions table for when someone invites, declines, accepts, ends, etc., keeps track of who made the action and to whom it was directed, and the relationship ID
   • friend_relationship_current - a view for the current status of the relationship, whose "go" is it, etc.
2. The actions table will always have by/for where "by" is always the current user ID and "for" is always the other person. So if person A sends a request to person B, and person B accepts, person A's uid will be the value for the first action's uid_by and the second action's uid_for.
3. The friend relationship will have uid_less and uid_more -- clients will be responsible for knowing the values of both UIDs and sending them along. (if we want the client to be able to be more ignorant, we can use serverless functions!) RLS will reject any insert where uid_less > uid_more
4. The view will provide additional data for the friend_relationship:
   • is the overall status "friends" "pending" or "unconnected" (or "blocked", for later)
   • what was the most recent action's type: accept, NULL/invite, cancel/remove/decline
   • who took the most recent action (uid_by), and who is the other person (uid_for)
5. From this we will derive who can take what actions -- the relationship table will have a uniqueness constraint on uid_first_from and uid_first_to (as well as the aforementioned greater/less-than constraint), and when there is already a relationship present the UI will instead attempt to insert a friend request action, whose RLS will have some subquery stuff that joins to the view of the current friendship:
   • if status is pending: uid_by can "cancel"; uid_for can "accept" or "decline"
   • if status is friends: either party can "remove"
   • if status is unconnected (or the relationship is not found): either party can "invite"

__NB: I get to the end of this and realize actually we can probably do away with the friend_relationship table and keep the original/current structure of just the actions and the summary; contrary to points above and below.__

As for the UI

1. When searching for people, we'll return a public_profile with friend_relationship_current attached (if present)
2. The "friend requests" section will show friend_relationship_current where status = 'pending' and uid_by is me (the user)
3. The "invitations from friends" section will show the same where uid_for is me
4. The "friends" section will show the same where status = 'friends'
5. When doing "send a friend request", we will just create the relationship if it's not present (an action will not be created; at least not at this time, just bc it's redundant and requires two requests)

[michaelsnook]

Trying again but back to just the actions and the summary:

1. We'll have a friendship_summary - a view with

   • uid_less, uid_more - where they are sorted alphabetically
   • from the most recent action, what was the id, uid_by, uid_for
   • what was the most recent action type: accept, invite, cancel, remove, decline
   • is the overall status friends, pending, unconnected (or blocked, for later)

2. User interactions will happen on friend_request_action - an actions table for when someone invites, accepts etc.

   • who made the action uid_by and who is it for uid_for
   • the uid_less and uid_more fields, whose validity will be enforced by RLS and ensured by the client. This order-enforced pairing becomes the unique identifier for the relationship, preventing mismatches where some records use B,A and others use A,B and so the full picture becomes fragmented.
   • This means that if person A sends a request to person B, and person B accepts, person A's will be the first action's uid_by and the second action's uid_for, but for both events the uid_less and uid_more will be the same for any given pair of users.
   • the action_type itself, the real star of the show: accept, invite, cancel, remove, decline

3. From this friendship summary we derive who can take what actions:

   • if status is pending: uid_by can "cancel"; uid_for can "accept" or "decline"
   • if status is friends: either party can "remove"
   • if status is unconnected (or NULL): either party can "invite"

As for the UI

1. When searching for people, we'll return a public_profile with friend_relationship_current attached (if present)
2. The "friend requests" section will show friend_relationship_current where status = 'pending' and uid_by is the user
3. The "invitations from friends" section will show the same where uid_for is the user
4. The "friends" section will show where status = 'friends'