Social Graph
Setup profile photo bucket.
- Go to Storage in the middle of the left navbar, and then click "New bucket".
- Give the bucket name "profilephotos" and make it public.
Setup database tables
- In Supabase SQL Editor, run this query to add a
profiles
table, acontacts
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
- 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.