Schema/API Design Comments Ethan Vosburg

[EthanV1920]

1 Returning Team ID

I would expect that when a custom draft is made, a team ID should be returned. Otherwise, the user is unaware of the team ID that they have just been assigned and cannot perform any actions with that.

2 Separate Draft and Team

Creating a custom draft and a team seem like atomic actions that should be split out into separate actions. I would consider removing the capability to make a team in the custom draft and only allow that in the create team endpoint.

3 Player ID

Players should have unique ID numbers that can be refered to. This will offer an easier way to search through the players and give you the ability to change the alphanumeric strings as necessary.

4 Player Data

There is no player's endpoint that offers a way for all of the players to be listed out. Instead, that seems to be handled in the search. It might be beneficial to add another endpoint that allows this with a simple command to quickly get that data.

5 Room Data

There is no way to query who is in a specific room or see the capacity that has been taken up. This might be a good thing to return when a player is adding themselves to a room. Passing back team ID and capacity information

6 Consistent Feedback

When running through the different time controls for a draft (drafts, there is not a consistent feedback message. Consider changing the messages so that they are consistent and any program interfacing with those can expect the same result.

7 CRON Job

Consider adding a CRON job to your SQL database to keep the website live so that there are no errors when other programs are interfacing with your website. Example code below:

-- Cron Job Instantiation 
-- Run this first 
create extension pg_cron with schema extensions;
grant usage on schema cron to Postgres;
grant all privileges on all tables in schema cron to Postgres;

-- Run Second
create extension pg_net;

-- Run Third and change the URL to yours keeping the /?ping=true
SELECT cron.schedule('keep alive job', '*/10 * * * *', $$
    select
      net.http_get(
          url:='https://csc365-databasesgroupproject.onrender.com/?ping=true'
      );
;$$);

-- Run this last
select * from cron.job_run_details where end_time is not null order by end_time desc limit 10;

-- Check the cron jobs that have been run
select
  *
from cron.job_run_details
order by start_time desc
limit 10;

8 Position Requirements

It seems like the `position_requirements' table may not be in use. I would possibly add it to the endpoint.

9 Unused Table Values

Going though you might benefit from cleaning up unused values in tables. There seem to be some redundant values that may not be needed to properly link tables together

10 Missing Visibility Flag

In your documented flows you suggest the presence of a visibility flag. That does not seem to be reflected in the schema. Consider adding another attribute to the drafts table that would contain the visibility for each team.

11 Adding Players

There seems to be no way to link players to teams. Consider adding a way to link players to teams by creating a roster_table that would contain who is on each team much like a cart_items table

12 Adding Players to Teams

There seems to be no way to add players to teams through the endpoints. This could be achieved by adding logic to the teams endpoint that would allow the addition of players to teams.