TestFast

Social Graph

Setup profile photo bucket.

  1. Go to Storage in the middle of the left navbar, and then click "New bucket".
  2. Give the bucket name "profilephotos" and make it public.

Setup database tables

  1. In Supabase SQL Editor, run this query to add a profiles table, a contacts table, and a few other goodies. To find the Editor, go to your Supabase dashboard, and open the left sidebar. It's the option below the Table Editor.

It's long, so just click copy in the top right of the code block.

SQL Editor
CREATE TABLE contacts (
    id bigserial PRIMARY KEY,
    created_at timestamp with time zone NULL,
    owner_user_id bigint NULL,
    contact_user_id bigint NULL,
    first_name text NULL,
    last_name text NULL,
    phone_number text NULL,
    relationship text NULL,
    birthday date NULL
);

CREATE TABLE friend_requests (
    id bigserial PRIMARY KEY,
    created_at timestamp with time zone NULL,
    status text NULL,
    updated_at timestamp with time zone NULL,
    sender_id bigint NULL,
    receiver_id bigint NULL
);

CREATE TABLE friendships (
    id bigserial PRIMARY KEY,
    accepted boolean NULL,
    created_at timestamp without time zone NOT NULL,
    updated_at timestamp without time zone NOT NULL,
    user_id bigint NULL,
    friend_id bigint NULL
);

CREATE TABLE profiles (
    id bigserial PRIMARY KEY,
    uuid text NULL,
    name text NULL,
    phone_number text NULL,
    profile_picture_url text NULL,
    blocked_numbers text[] NULL,
    user_name text NULL,
    friend_count bigint NULL,
    push_token text NULL,
    first_name text NULL,
    last_name text NULL,
    gender text NULL,
    age bigint NOT NULL
);

CREATE TABLE uploaded_contacts (
    id bigserial PRIMARY KEY,
    created_at timestamp with time zone NULL,
    owner_user_id bigint NULL,
    contact_user_id bigint NULL,
    first_name text NULL,
    last_name text NULL,
    phone_number text NULL,
    relationship text NULL,
    birthday date NULL,
    avatar bytea NULL
);

CREATE TABLE user_state (
    id bigserial PRIMARY KEY,
    created_at timestamp with time zone NOT NULL,
    onboarding_complete boolean NOT NULL,
    screen text NOT NULL,
    user_id bigint NOT NULL
);

You should see Success. No rows returned as a response if everything is okay. Go to Table Editor and verify that there's the following tables: contacts, friend_requests, friendships, profiles, uploaded_contacts, and user_state.

Setup Friendfinder, Contact upload, friend operations, and more

  1. Hang in there. Run this code in the SQL editor so that your users can find friends, and a few other things like user name generation, user name check, phone number check, and friend operations.

It's long, so just copy it in the top right of the code block.

SQL Editor
CREATE OR REPLACE FUNCTION public.insert_uploaded_contacts(owner_user_id_param bigint)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    contact_record RECORD;
BEGIN
    FOR contact_record IN SELECT * FROM uploaded_contacts WHERE owner_user_id = owner_user_id_param
    LOOP
        INSERT INTO contacts (first_name, last_name, phone_number, birthday, contact_user_id, owner_user_id)
        SELECT 
            contact_record.first_name, 
            contact_record.last_name, 
            contact_record.phone_number, 
            contact_record.birthday, 
            (SELECT id FROM profiles WHERE phone_number = contact_record.phone_number LIMIT 1),
            owner_user_id_param;
    END LOOP;
END;
$function$;

CREATE OR REPLACE FUNCTION public.generate_user_name(first_name text, last_name text)
 RETURNS text
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    base_user_name TEXT;
    final_user_name TEXT;
    counter INT;
    default_prefix TEXT := 'user'; -- Default prefix for user_names
BEGIN
    -- Check if both first_name and last_name are empty
    IF first_name = '' AND last_name = '' THEN
        -- Set base_user_name to a default value
        base_user_name := default_prefix;
    ELSE
        -- Concatenate first_name and last_name to form base user_name
        base_user_name := first_name || last_name;
    END IF;

    -- Initialize counter
    counter := 1;

    -- Initialize final_user_name with base_user_name
    final_user_name := base_user_name;

    -- Loop until an unused user_name is found
    LOOP
        -- Check if the current final_user_name exists in the profiles table
        IF EXISTS (SELECT 1 FROM profiles WHERE user_name = final_user_name) THEN
            -- If it exists, append the counter to base_user_name and increment counter
            final_user_name := base_user_name || counter;
            counter := counter + 1;
        ELSE
            -- If it doesn't exist, exit the loop
            EXIT;
        END IF;
    END LOOP;

    -- Return the final unique user_name
    RETURN final_user_name;
END;
$function$;

CREATE OR REPLACE FUNCTION public.cancel_fr(request_id bigint)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
    -- Attempt to delete the friend request
    DELETE FROM friend_requests WHERE id = request_id;
EXCEPTION
    WHEN OTHERS THEN
        -- In case of an error, output to the server log
        RAISE NOTICE 'Error cancelling friend request: %', SQLERRM;
END;
$function$;

CREATE OR REPLACE FUNCTION public.uuid_to_user_id(user_uuid text)
 RETURNS bigint
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
  RETURN (SELECT id FROM public.profiles WHERE uuid = user_uuid LIMIT 1);
END
$function$;

CREATE OR REPLACE FUNCTION public.increment_friend_count(user_id_param bigint)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
    UPDATE profiles
    SET friend_count = friend_count + 1
    WHERE id = user_id_param;
END;
$function$;

CREATE OR REPLACE FUNCTION public.add_friendship_and_delete_request(friend_req_id bigint, your_user_id bigint, friend_id bigint)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
    -- Delete the friend request
    DELETE FROM friend_requests
    WHERE id = friend_req_id;
    
    -- Insert into friendships
    INSERT INTO friendships(user_id, friend_id, accepted, updated_at)
    VALUES (your_user_id, friend_id, TRUE, CURRENT_TIMESTAMP);
    
    -- Increment friend counts for both users involved
    PERFORM increment_friend_count(your_user_id);
    PERFORM increment_friend_count(friend_id);
    
END;
$function$;

-- needs work

CREATE OR REPLACE FUNCTION public.fetch_users_by_search(input_school text, input_user_id text, search_term text)
 RETURNS TABLE(uuid text, user_name text, first_name text, last_name text, profile_picture_url text)
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY
  SELECT p.uuid, p.user_name, p.first_name, p.last_name, p.profile_picture_url
  FROM profiles p
  WHERE p.school = input_school
    AND p.uuid != input_user_id
    AND (p.user_name ILIKE '%' || search_term || '%'
      OR p.first_name ILIKE '%' || search_term || '%'
      OR p.last_name ILIKE '%' || search_term || '%')
    AND NOT EXISTS (
      SELECT 1
      FROM friendships f
      WHERE (f.user_id = input_user_id AND f.friend_id = p.uuid)
         OR (f.friend_id = input_user_id AND f.user_id = p.uuid)
         AND f.accepted = true
    );
END;
$function$;


-- May be something wrong here

CREATE OR REPLACE FUNCTION public.search_profiles(input_user_id bigint, search_term text)
 RETURNS TABLE(id bigint, uuid text, user_name text, first_name text, last_name text, profile_picture_url text)
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
  RETURN QUERY
  SELECT p.id, p.uuid, p.user_name, p.first_name, p.last_name, p.profile_picture_url
  FROM profiles p
  WHERE p.id != input_user_id
    AND (p.user_name ILIKE '%' || search_term || '%'
      OR p.first_name ILIKE '%' || search_term || '%'
      OR p.last_name ILIKE '%' || search_term || '%')
    AND NOT EXISTS (
      SELECT 1
      FROM friendships f
      WHERE (f.user_id = input_user_id AND f.friend_id = p.id)
         OR (f.friend_id = input_user_id AND f.user_id = p.id)
         AND f.accepted = true
    );
END;
$function$;

CREATE OR REPLACE FUNCTION public.fetch_sent_fr(user_uuid text)
 RETURNS TABLE(id bigint, sender_id bigint, receiver_id bigint, status text, created_at timestamp with time zone, updated_at timestamp with time zone)
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
AS $function$
BEGIN
    RETURN QUERY
    SELECT f.id, f.sender_id, f.receiver_id, f.status, f.created_at, f.updated_at
    FROM public.friend_requests AS f
    WHERE f.sender_id = public.uuid_to_user_id(user_uuid)
      AND f.status = 'pending';
END;
$function$;

CREATE OR REPLACE FUNCTION public.fetch_received_fr(user_uuid text)
 RETURNS TABLE(fr_id bigint, fr_sender_id bigint, fr_status text, sender_first_name text, sender_last_name text, sender_user_name text, sender_profile_picture_url text, sender_uuid text)
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        fr.id AS fr_id, 
        fr.sender_id AS fr_sender_id, 
        fr.status AS fr_status,
        p.first_name AS sender_first_name,
        p.last_name AS sender_last_name,
        p.user_name AS sender_user_name,
        p.profile_picture_url AS sender_profile_picture_url,
        p.uuid AS sender_uuid
    FROM 
        public.friend_requests AS fr
    JOIN 
        public.profiles AS p ON fr.sender_id = p.id
    WHERE 
        fr.receiver_id = public.uuid_to_user_id(user_uuid) AND
        fr.status = 'pending';
END;
$function$;



CREATE OR REPLACE FUNCTION public.insert_fr(sender_id_param bigint, receiver_id_param bigint)
 RETURNS TABLE(id bigint, sender_id bigint, receiver_id bigint, status text, updated_at timestamp with time zone)
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
    RETURN QUERY
    INSERT INTO friend_requests (sender_id, receiver_id, status, updated_at)
    VALUES (sender_id_param, receiver_id_param, 'pending', CURRENT_TIMESTAMP)
    RETURNING friend_requests.id, friend_requests.sender_id, friend_requests.receiver_id, friend_requests.status, friend_requests.updated_at;
END;
$function$;

CREATE OR REPLACE FUNCTION public.check_onboarding_complete(user_id_param bigint)
 RETURNS boolean
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    is_complete BOOLEAN;
BEGIN
    -- Check if the user exists and get their onboarding_complete status
    SELECT onboarding_complete INTO is_complete
    FROM user_state
    WHERE user_id = user_id_param;

    -- If is_complete is NULL, it means the user does not exist in the table
    IF is_complete IS NULL THEN
        -- Insert a new user with onboarding_complete set to FALSE
        INSERT INTO user_state(user_id, onboarding_complete)
        SELECT user_id_param, FALSE
        WHERE NOT EXISTS (
            SELECT 1 FROM user_state WHERE user_id = user_id_param
        );

        -- Return FALSE since the user's onboarding is not complete
        RETURN FALSE;
    ELSE
        -- Return the onboarding status of the existing user
        RETURN is_complete;
    END IF;
END;
$function$;

CREATE OR REPLACE FUNCTION public.set_onboarding_complete(user_id_param bigint)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
    UPDATE user_state
    SET onboarding_complete = TRUE
    WHERE user_id = user_id_param;
END;
$function$;

CREATE OR REPLACE FUNCTION public.check_phone_number_exists(phone_num_param text)
 RETURNS boolean
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM profiles
        WHERE phone_number = phone_num_param
    );
END;
$function$;





CREATE OR REPLACE FUNCTION public.user_name_available(p_username text)
 RETURNS boolean
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
BEGIN
    RETURN NOT EXISTS (
        SELECT 1
        FROM profiles
        WHERE user_name = p_username
    );
END;
$function$;

As with the one before, you should see Success. No rows returned if everything is okay. Be sure to double check these are created by going to Database in the left sidebar which is under SQL Editor and verifying that there are Functions. You can find Functions in the DATABASE MANAGEMENT columns. It's one of the first options.

Was this page helpful?