[Backend] Implement RPC Functions to Retrieve Users from Supabase

[linear[bot]]

Create two RPC functions in Supabase to retrieve user data:

1. get_users(): Returns all users.
2. get_users_by_emails(emails): Returns users based on a provided list of emails.

Acceptance Criteria:

1. Function Implementation:
   • Implement the get_users() function to retrieve all users.
   • Implement the get_users_by_emails(emails) function to retrieve users based on a list of emails provided.

2. Sample Responses:

   • get_users() should return a response in the following format:

     [
     {
      "id": "68dabebb-0cd8-4eff-a5d3-797a6d0332c6",
      "email": "me@example.com",
      "raw_user_meta_data": {}
     },
     {
      "id": "32705b60-36d2-40d1-b2cd-dff45014536b",
      "email": "phong.nguyen@stanyangroup.com",
      "raw_user_meta_data": {
        "iss": "https://accounts.google.com",
        "sub": "106046552322731182849",
        "name": "Phong Nguyen",
        "email": "phong.nguyen@stanyangroup.com",
        "picture": "https://lh3.googleusercontent.com/a/ACg8ocKzNhgpL8qfZGWo6eEZBsJ-PY6-PgI9OuKWQJwPlKFo-qZqoKIU=s96-c",
        "full_name": "Phong Nguyen",
        "avatar_url": "https://lh3.googleusercontent.com/a/ACg8ocKzNhgpL8qfZGWo6eEZBsJ-PY6-PgI9OuKWQJwPlKFo-qZqoKIU=s96-c",
        "provider_id": "106046552322731182849",
        "custom_claims": {
          "hd": "stanyangroup.com"
        },
        "email_verified": true,
        "phone_verified": false
      }
     }
     // additional users
     ]

   • get_users_by_emails(emails) should be callable as:

     get_users_by_emails('phong.nguyen@stanyangroup.com', 'dat.h.tran@stanyangroup.com')

   • It should return a response like:

     [
     {
      "id": "32705b60-36d2-40d1-b2cd-dff45014536b",
      "email": "phong.nguyen@stanyangroup.com",
      "raw_user_meta_data": {
        "iss": "https://accounts.google.com",
        "sub": "106046552322731182849",
        "name": "Phong Nguyen",
        "email": "phong.nguyen@stanyangroup.com",
        "picture": "https://lh3.googleusercontent.com/a/ACg8ocKzNhgpL8qfZGWo6eEZBsJ-PY6-PgI9OuKWQJwPlKFo-qZqoKIU=s96-c",
        "full_name": "Phong Nguyen",
        "avatar_url": "https://lh3.googleusercontent.com/a/ACg8ocKzNhgpL8qfZGWo6eEZBsJ-PY6-PgI9OuKWQJwPlKFo-qZqoKIU=s96-c",
        "provider_id": "106046552322731182849",
        "custom_claims": {
          "hd": "stanyangroup.com"
        },
        "email_verified": true,
        "phone_verified": false
      }
     }
     // additional users
     ]

3. Authentication:
   • Ensure that requests to these functions are authenticated.

Notes:

• Ensure that the functions handle cases where no users or no emails are found.
• Collaborate with the security team to ensure the authentication mechanism is robust.

Tasks:

• [ ] Design and implement the get_users() RPC function.
• [ ] Design and implement the get_users_by_emails(emails) RPC function.
• [ ] Write tests to verify the correct retrieval of user data.
• [ ] Ensure proper authentication is in place for these functions.

[linear[bot]]

VER-78 [Backend] Implement RPC Functions to Retrieve Users from Supabase