ScyllaDB mutual friend relation data modeling

I'm working on a mobile App with social features, I'm currently trying on implement mutual friend relations with Scylla. I have chosen Scylla because the Friend Service will be a key feature with high TPS since a lot of other services need access to a users friends and friend relations are generally a good fit for NoSQL.
Mutual friend relation means, if user_1 accepts the friend request of another user_2, user_2 should have user_1 as friend, but user_1 should also automatically be friends with user_2.

My goal is to design a Schema that allows all my access patterns with minimal response time, so optimally one request for every access pattern. Less data duplication would be nice but not mandatory.

Access Patterns:

• Get Friends of a user by the user's id
• Get Incoming Friend Requests by user id
• Get Relation between two users by specifying both user id's
  • The query should allow us to conclude if the two users are friends, if one requested the other to be friends or if they are not friends.

Data Manipulation

• Create Friend Request
• Decline Friend Request
• Accept Friend Request
  • This should result in a mutual friend relation between the requester and requested.
• Remove Friend Relation
  • This should remove the friend relation for both users.

My Current Design

Here I will explain my current Schema and which access patterns it allows me to do and what could be improved.
This is my Schema, no data duplication just one table with the Primary Key user_id, accepted, friend_id

CREATE TABLE IF NOT EXISTS friend_relations (
    user_id text,
    friend_id text,
    accepted boolean,
    requested_at timestamp,
    accepted_at timestamp,
    PRIMARY KEY (user_id, accepted, friend_id)
);

• GetFriends

  • SELECT * FROM friend_relations WHERE user_id=? AND accepted=true;

• GetIncomingFriendRequests

  • When a Friend Request is inserted the receiver is the user_id so it is only possible to get Incoming Friend Request but not outgoing.
    SELECT * FROM friend_relations WHERE user_id=? AND accepted=false;

• GetFriendRelation

  This is my current Pain Point. It would be easy to know if user_1 is friends with user_2 since their would be a record for both of them saying the other user is their friend (because of mutual friend relation). But when I want to know if one user requested the other, I would need to do a query like below:
  • SELECT * FROM friend_relations WHERE user_id IN (user_1,user_2) AND accepted=false AND friend_id IN (user_1,user_2)
    This way I would know if either user_1 requested user_2 or the other way.
    I combined both queries into:
    SELECT * FROM friend_relations WHERE user_id IN (user_1,user_2) AND accepted IN (true, false) AND friend_id IN (user_1,user_2).
    The many IN relations give some headaches since I'm not sure if this could cause unpredictable performance when one id is in a completely different partition on a different Node.

Data Manipulation

• Create Friend Request

  • INSERT INTO friend_relations (user_id, friend_id, accepted, requested_at) VALUES (requested_user, requester, false, now).
    Here I'm switching the requester to the friend_id and the person who get's requested (requested_user) is the user_id. That way we can find all friend requests to a user which is more important than finding the friend requests sent by a user.

• Decline Friend Request

  • DELETE FROM friend_relations WHERE user_id=? AND accepted=false AND friend_id=?; This is simple since the person who wants to decline is the user_id and the friend_id is the person who requested.

• Accept Friend Request

  • This is more complicated since I need to batch three cql statements.
    1. Delete the friend request where the accepting user is the user_id, accepted=false and the friend_id is the requester. I can't update the relation since accepting is part of the primary key.
    2. Insert mutual friend relation by inserting two friend relations with accepted=true and switching the user_id and friend_id for one of the inserts, as this way the other user will be found by GetFriends method for both users.

• Remove Friend Relation

  • DELETE FROM friend_relations WHERE user_id IN (user_1,user_2) AND accepted=true AND friend_id IN (user_1,user_2);.
    Relatively easy, just delete relation for both sides of users.

These are all my thoughts that went into designing my current Schema but I'm still not completly convinced by it, since a very important access pattern GetRelation uses so many IN relations and accepting a Friend Request is quite complicated. But I also find it hard to imagine a better Schema which could include Materialized Views, perhaps to separate the friend requests from the friend relations, because then the GetRelation method would need to query the other table if one doesn't return a record (Two network requests).