Skip to content
This repository was archived by the owner on Apr 11, 2026. It is now read-only.

Getting started

Jump to bottom
Diogo Carvalho edited this page Jul 29, 2023 · 20 revisions

Contents

Installation

Clone the Repository

git clone https://github.com/carvalho28/ReQuest.git

Install Dependencies

Install deps for the request-app:

yarn install

Install deps for the aux servers:

cd request-app/servers/ai && yarn install
cd request-app/servers/hocuspocus && yarn install

Setting up Supabase

Now, create an account in Supabase and a project, you can call it whatever you want.

Updating .env

Change the name of the .env.example in the request-app folder to .env.local and replace the values with the ones found in Project Settings and then selecting API.

NEXT_PUBLIC_SUPABASE_URL=
NEXT_PUBLIC_SUPABASE_ANON_KEY=
NEXT_PUBLIC_IMAGE_DOMAINS=

Create Databases, Functions and Triggers

Execute the following script inside SQL editor, then + New Query and execute the code.

-- public.levels definition
CREATE TABLE public.levels (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
    created_at timestamptz NULL DEFAULT now(),
    xp_needed int8 NULL,
    denomination text NULL,
    CONSTRAINT levels_pkey PRIMARY KEY (id)
);

-- public.trophies definition
CREATE TABLE public.trophies (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
    image text NULL,
    "desc" text NULL,
    CONSTRAINT trophies_pkey PRIMARY KEY (id)
);

-- public.projects definition
CREATE TABLE public.projects (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    "name" text NOT NULL,
    description text NULL,
    status text NOT NULL DEFAULT 'Active'::text,
    deadline timestamptz NOT NULL,
    created_at timestamptz NULL DEFAULT now(),
    CONSTRAINT projects_pkey PRIMARY KEY (id)
);

-- public.profiles definition
CREATE TABLE public.profiles (
    id uuid NOT NULL,
    email varchar NOT NULL,
    updated_at timestamptz NULL,
    "name" text NULL,
    avatar_url json NULL DEFAULT '{"skinColor": ["F2AD9B"], "hair": ["bald"], "hairColor": ["362C47"], "facialHair": ["walrus"], "facialHairProbability": 0, "body": ["squared"], "clothingColor": ["456DFF"], "eyes": ["open"], "mouth": ["smile"], "nose": ["mediumRound"], "backgroundColor": ["93A7FF"], "radius": 50 }'::json,
    requirements_completed int8 NULL DEFAULT 0,
    "level" int8 NULL DEFAULT 1,
    xp int8 NULL DEFAULT 0,
    n_projects int8 NULL DEFAULT 0,
    created_at timestamptz NULL,
    CONSTRAINT profiles_email_key UNIQUE (email),
    CONSTRAINT profiles_pkey PRIMARY KEY (id),
    CONSTRAINT profiles_id_fkey FOREIGN KEY (id) REFERENCES auth.users(id),
    CONSTRAINT profiles_level_fkey FOREIGN KEY ("level") REFERENCES public.levels(id)
);

-- public.chats definition
CREATE TABLE public.chats (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    CONSTRAINT chats_pkey PRIMARY KEY (id)
);

-- public.messages definition
CREATE TABLE public.messages (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    chat_id uuid NULL,
    author_id uuid NULL,
    "content" text NULL,
    created_at timestamptz NULL DEFAULT now(),
    CONSTRAINT messages_pkey PRIMARY KEY (id),
    CONSTRAINT messages_author_id_fkey FOREIGN KEY (author_id) REFERENCES public.profiles(id) ON DELETE CASCADE,
    CONSTRAINT messages_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES public.chats(id) ON DELETE CASCADE
);

-- public.chat_users definition
CREATE TABLE public.chat_users (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
    chat_id uuid NULL,
    user_id uuid NULL,
    CONSTRAINT chat_users_pkey PRIMARY KEY (id),
    CONSTRAINT chat_users_chat_id_fkey FOREIGN KEY (chat_id) REFERENCES public.chats(id) ON DELETE CASCADE,
    CONSTRAINT chat_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.profiles(id) ON DELETE CASCADE
);

-- public.project_profiles definition
CREATE TABLE public.project_profiles (
    id serial4 NOT NULL,
    id_proj uuid NOT NULL,
    id_user uuid NOT NULL,
    CONSTRAINT project_profiles_pkey PRIMARY KEY (id),
    CONSTRAINT project_profiles_id_proj_fkey FOREIGN KEY (id_proj) REFERENCES public.projects(id),
    CONSTRAINT project_profiles_id_user_fkey FOREIGN KEY (id_user) REFERENCES public.profiles(id)
);

-- public.requirements definition
CREATE TABLE public.requirements (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    id_proj uuid NOT NULL,
    "name" text NOT NULL,
    description text NULL DEFAULT '{"default":{"type":"doc","content":[{"type":"paragraph","content":[]}]}}'::text,
    due_date timestamptz NULL,
    priority text NOT NULL DEFAULT 'P3'::text,
    created_at timestamptz NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
    created_by uuid NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT now(),
    updated_by uuid NOT NULL,
    assigned_to _text NULL DEFAULT '{}'::text[],
    status text NULL DEFAULT 'Not started'::text,
    closed_at timestamptz NULL,
    closed_by _text NULL,
    "type" text NULL,
    identifier text NULL,
    CONSTRAINT requirements_pkey PRIMARY KEY (id),
    CONSTRAINT requirements_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.profiles(id) ON DELETE CASCADE,
    CONSTRAINT requirements_id_proj_fkey FOREIGN KEY (id_proj) REFERENCES public.projects(id),
    CONSTRAINT requirements_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES public.profiles(id) ON DELETE CASCADE
);

-- public.trophies_profiles definition
CREATE TABLE public.trophies_profiles (
    id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
    id_trophy int8 NULL,
    id_user uuid NULL,
    CONSTRAINT trophies_profiles_pkey PRIMARY KEY (id),
    CONSTRAINT trophies_profiles_id_trophy_fkey FOREIGN KEY (id_trophy) REFERENCES public.trophies(id) ON DELETE CASCADE,
    CONSTRAINT trophies_profiles_id_user_fkey FOREIGN KEY (id_user) REFERENCES public.profiles(id)
);

-- Table Triggers

create trigger update_identifier_trigger before
insert
    or
update
    on
    public.requirements for each row execute function update_identifier();


-- public.trophies_profiles definition

CREATE TABLE public.trophies_profiles (
	id int8 NOT NULL GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE),
	id_trophy int8 NULL,
	id_user uuid NULL,
	CONSTRAINT trophies_profiles_pkey PRIMARY KEY (id),
	CONSTRAINT trophies_profiles_id_trophy_fkey FOREIGN KEY (id_trophy) REFERENCES public.trophies(id) ON DELETE CASCADE,
	CONSTRAINT trophies_profiles_id_user_fkey FOREIGN KEY (id_user) REFERENCES public.profiles(id)
);


CREATE OR REPLACE FUNCTION public.get_chat_id(user_id_1 uuid, user_id_2 uuid)
 RETURNS TABLE(chat_id uuid)
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY
  SELECT cu1.chat_id
  FROM public.chat_users cu1
  JOIN public.chat_users cu2
  ON cu1.chat_id = cu2.chat_id
  WHERE cu1.user_id = user_id_1
  AND cu2.user_id = user_id_2;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.get_connected_users(my_user_id uuid)
 RETURNS TABLE(id uuid, email character varying, name text, avatar_url json)
 LANGUAGE plpgsql
AS $function$begin
      return query
         SELECT 
        profiles.id,
        profiles.email,
        profiles.name,
        profiles.avatar_url
      FROM profiles
      JOIN project_profiles ON project_profiles.id_user = profiles.id
      WHERE project_profiles.id_proj IN (
        SELECT projects.id
        FROM projects
        WHERE projects.id IN (
          SELECT id_proj
          FROM project_profiles
          WHERE id_user = my_user_id
        ) AND id_user != my_user_id
      )
      GROUP BY profiles.id;
      END;$function$
;

CREATE OR REPLACE FUNCTION public.get_latest_closed_requirements(user_id uuid)
 RETURNS TABLE(requirement_name text, project_name text, project_id uuid, closed_at timestamp with time zone)
 LANGUAGE plpgsql
AS $function$
BEGIN
  RETURN QUERY
    SELECT
      requirements.name AS requirement_name,
      projects.name AS project_name,
      projects.id AS project_id,
      requirements.closed_at AS closed_at
    FROM requirements
    JOIN projects ON requirements.id_proj = projects.id
    WHERE requirements.closed_at IS NOT NULL
    AND requirements.created_by = user_id
    ORDER BY closed_at DESC
    LIMIT 3;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.get_user_projects_within_months(user_id uuid, num_months integer)
 RETURNS TABLE(project_name text, deadline timestamp with time zone)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
        SELECT p.name AS project_name, p.deadline
        FROM public.projects p
        INNER JOIN public.project_profiles pp ON p.id = pp.id_proj
        WHERE pp.id_user = user_id
        AND p.deadline BETWEEN now() AND (now() + INTERVAL '1 month' * num_months)
        ORDER BY p.deadline;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.handle_new_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
begin
  insert into public.profiles (id, email, name)
  values (new.id,
    new.raw_user_meta_data->>'email',
    new.raw_user_meta_data->>'name');
  return new;
end;
$function$
;

CREATE OR REPLACE FUNCTION public.handle_update_user()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
begin
update public.profiles
set email = new.email
where id = new.id;
  return new;
end;
$function$
;

CREATE OR REPLACE FUNCTION public.increment_requirements_completed(user_ids text[])
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  user_level bigint;
  user_xp bigint;
  xp_needed bigint;
  i integer;
BEGIN
  BEGIN
    FOR i IN 1..array_length(user_ids, 1) LOOP
      RAISE NOTICE 'Processing user %', user_ids[i];
      -- get the current user's level and xp
      SELECT level, xp INTO user_level, user_xp FROM profiles WHERE id = user_ids[i]::uuid;

      -- get the amount of xp needed to level up
      SELECT l.xp_needed INTO xp_needed FROM levels l WHERE l.id = user_level;

      -- increment the requirements_completed column by one and add 5 xp
      UPDATE profiles SET requirements_completed = requirements_completed + 1, xp = xp + 5 WHERE id = user_ids[i]::uuid;

      -- check if xp is full for the current level, and if so, increment the level
      IF user_xp + 5 >= xp_needed THEN
        UPDATE profiles SET level = level + 1, xp = user_xp + 5 - xp_needed WHERE id = user_ids[i]::uuid;
      END IF;
    END LOOP;
  EXCEPTION WHEN OTHERS THEN
    RAISE NOTICE 'Error processing user %: %', user_ids[i], SQLERRM;
  END;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.projects_user(user_id uuid)
 RETURNS TABLE(id uuid, name text, description text, status text, deadline timestamp with time zone, created_at timestamp with time zone)
 LANGUAGE plpgsql
AS $function$
  begin
    return query
        SELECT p.*
        FROM projects p
        INNER JOIN project_profiles pp ON p.id = pp.id_proj
        WHERE pp.id_user = user_id;
    END;
$function$
;

CREATE OR REPLACE FUNCTION public.projects_user_people(user_id uuid)
 RETURNS TABLE(id uuid, name text, description text, status text, deadline timestamp with time zone, project_users character varying[])
 LANGUAGE plpgsql
AS $function$
    begin
      return query
        SELECT 
        p.id AS id, 
        p.name AS name,
        p.description AS description,
        p.status AS status,
        p.deadline AS deadline,
        ARRAY_REMOVE(
          ARRAY_AGG(CASE WHEN pp2.id_user <> pp.id_user THEN p2.email END), 
          NULL
        ) AS project_users
      FROM projects p 
      JOIN project_profiles pp ON p.id = pp.id_proj 
      JOIN profiles p1 ON pp.id_user = p1.id 
      JOIN project_profiles pp2 ON p.id = pp2.id_proj 
      JOIN profiles p2 ON pp2.id_user = p2.id 
      WHERE p1.id = user_id
      GROUP BY p.id;
    END;
  $function$
;

CREATE OR REPLACE FUNCTION public.projects_user_req(user_id uuid)
 RETURNS TABLE(id uuid, name text, description text, status text, deadline timestamp with time zone, created_at timestamp with time zone, total_reqs bigint, completed_reqs bigint)
 LANGUAGE plpgsql
AS $function$
begin 
return query
    SELECT 
    p.*,
    COUNT(r.id) AS total_reqs,
    COUNT(CASE WHEN r.status = 'Completed' THEN r.id ELSE NULL END) AS completed_reqs
    FROM 
    projects p
    LEFT JOIN requirements r ON p.id = r.id_proj
    INNER JOIN project_profiles pp ON p.id = pp.id_proj
    WHERE 
    pp.id_user = 'db415242-dfb7-41f4-9574-44835caeea3d' AND p.status = 'Active'
    GROUP BY 
    p.id;
    END;
$function$
;

CREATE OR REPLACE FUNCTION public.ranking_req(proj_id uuid)
 RETURNS TABLE(id uuid, name text, avatar_url json, requirements_closed bigint)
 LANGUAGE plpgsql
AS $function$
    begin
      return query
        SELECT p.id, p.name, p.avatar_url, count(r.id) AS requirements_closed
        FROM profiles p
        LEFT JOIN requirements r ON p.id = ANY(r.closed_by::uuid[]) AND r.id_proj = proj_id
        WHERE p.id = ANY(r.closed_by::uuid[])
        GROUP BY p.id, p.name
        ORDER BY requirements_closed DESC;
      END;
  $function$
;

CREATE OR REPLACE FUNCTION public.requirements_user(user_id uuid)
 RETURNS TABLE(id uuid, name text, due_date timestamp with time zone, priority text, status text)
 LANGUAGE plpgsql
AS $function$
    begin
      return query
        SELECT r.id, r.name, r.due_date, r.priority, r.status
        FROM requirements r
        INNER JOIN projects p ON r.id_proj = p.id
        INNER JOIN project_profiles pp ON p.id = pp.id_proj
        WHERE pp.id_user = user_id
        AND r.due_date IS NOT NULL;
      END;
  $function$
;

CREATE OR REPLACE FUNCTION public.update_identifier()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  -- Check if the type has changed
  IF NEW.type IS DISTINCT FROM OLD.type THEN
    -- Generate a new identifier based on the type
    IF LOWER(NEW.type) = 'functional' THEN
      NEW.identifier := 'FR' || LPAD(CAST((SELECT COUNT(*) + 1 FROM public.requirements WHERE id_proj = NEW.id_proj AND LOWER(type) = 'functional') AS TEXT), 2, '0');
    ELSIF LOWER(NEW.type) = 'non-functional' THEN
      NEW.identifier := 'NFR' || LPAD(CAST((SELECT COUNT(*) + 1 FROM public.requirements WHERE id_proj = NEW.id_proj AND LOWER(type) = 'non-functional') AS TEXT), 2, '0');
    ELSE
      RAISE EXCEPTION 'Invalid requirement type';
    END IF;
  END IF;

  -- Check for existing identifiers within the project
  IF NEW.identifier IS NOT NULL THEN
    -- If there is an existing identifier, generate a new one
    WHILE EXISTS (SELECT 1 FROM public.requirements WHERE id_proj = NEW.id_proj AND identifier = NEW.identifier AND id <> NEW.id) LOOP
      -- Extract the numeric portion from the existing identifier
      NEW.identifier := LEFT(NEW.identifier, 3) || LPAD(CAST(RIGHT(NEW.identifier, 2)::INTEGER + 1 AS TEXT), 2, '0');
    END LOOP;
  END IF;

  RETURN NEW;
END;
$function$
;

-- Triggers

create trigger update_identifier_trigger before
insert
    or
update
    on
    public.requirements for each row execute function update_identifier();

create trigger on_auth_user_created after
insert
    on
    auth.users for each row execute function handle_new_user();

create trigger on_auth_user_updated after
update
    of email on
    auth.users for each row execute function handle_update_user();

-- Data

INSERT INTO levels (id, xp_needed)
SELECT generate_series(1, 50), 100 + (generate_series(0, 49) * 50);

UPDATE levels
SET denomination = 
  CASE 
    WHEN id BETWEEN 1 AND 5 THEN 'CS Rookie'
    WHEN id BETWEEN 6 AND 10 THEN 'Basic Builder'
    WHEN id BETWEEN 11 AND 15 THEN 'Programming Learner'
    WHEN id BETWEEN 16 AND 20 THEN 'Requirements Trainee'
    WHEN id BETWEEN 21 AND 25 THEN 'Software Explorer'
    WHEN id BETWEEN 26 AND 30 THEN 'Advanced Explorer'
    WHEN id BETWEEN 31 AND 35 THEN 'Requirements Expert'
    WHEN id BETWEEN 36 AND 40 THEN 'Development Specialist'
    WHEN id BETWEEN 41 AND 45 THEN 'Requirements Master'
    WHEN id BETWEEN 46 AND 50 THEN 'Requirements Guru'
  END;

Setting up Auxiliary Servers

I have used Fly.io to host both AI and Hocuspocus server, however, you can opt for other alternatives. Or just run it locally.

Just edit the .env inside /servers/ai and /servers/hocuspocus and you will be ready to go.

NOTE: You will need an API key for ChatGPT.

Run

Run app:

yarn dev

Run the aux servers on the base folder:

cd request-app/servers/ai && yarn start
cd request-app/servers/hocuspocus && yarn start

The app is now running on localhost:3000, the hocuposcus server on the localhost:8090 and the AI in localhost:8080.

Sidebar

Clone this wiki locally