Contests and Questions tracker

[ridhishjain-zepto]

Tables

• contests table

id (primary key)
show_leaderboard (tiny int, 0/1)
public (tiny int, 0/1)
confidential_questions (tiny int, default 0)
creator (username, admin only, foreign key from users table)
name (string)
start_time (timestamp)
end_time (timestamp)
participants_count (int)
about (string)
rules (string, can be null)
prizes (string, can be null)

• contests_groups_map table

id (primary key)
contest_id (foreign key from `contests` table)
group_id (foreign key from `groups` table)

• contests_moderators_map table

id (primary key)
contest_id (foreign key from `contests` table)
moderator_username (foreign key from `users` table)

• contests_participants_map table

id (primary key)
contest_id (foreign key from `contests` table)
participant_username (foreign key from `users` table)

• questions table

id (primary key)
creator (foreign key from `users` table)
type (tiny int, 0 - MCQ, similar)
name (string)
problem statement (string)
input_format (string)
output_format (string)
constraints (string)
options (string, JSON array)
correct (int, index of correct option)
difficulty (int, 1-easy, 2-medium, 3-hard, can be 0 by default - not specified)

• questions_editors_map table

id (primary key)
question_id (foreign key from `questions` table)
editor_username (foreign key from `users` table)

• tags table

id (primary key)
description (string)
name (string)

• questions_tags_map table

id (primary key)
question_id (foreign key from `questions` table)
tag_id (foreign key from `tags` table)

• contests_questions_map table

id (primary key)
question_id (foreign key from `questions` table)
contest_id (foreign key from `contests` table)
max_score (int)

• mcq_submissions table

id (primary key)
question_id (foreign_key from `questions` table)
contest_id (foreign_key from `contests` table)
username (foreign_key from `users` table)
response (int, index of option selected),
submission_time (timestamp)
judged (tiny int, default 0)
score (int, default 0)

• subjective_submissions table

id (primary key)
question_id (foreign_key from `questions` table)
contest_id (foreign_key from `contests` table)
username (foreign_key from `users` table)
response (text),
submission_time (timestamp)
judged (tiny int, default 0)
score (int, default 0)
feedback (varchar, if the moderator wants to give any feedback after checking)

• leaderboard table

id (primary key)
uesrname (foreign key from `users` table)
contest_id (foreign_key from `contests` table)
score (int, total score)
total_time (time, total penalty yet)
attempted_count (int)

API

Contests API

• createContest
POST /contests/ Admin only

• updateContest
POST /contests/:contest_id/update Moderator only

• addModerator
POST /contests/:contest_id/moderator Moderator only

• removeModerator
DELETE /contests/:contest_id/moderator Moderator only

• addGroup
POST /contests/:contest_id/:groups Moderator only

• removeGroup
DELETE /contests/:contest_id/groups Moderator only

• getContests
GET /contests?status=active&limit=5

• getModeratorContests
GET /contests/moderator_contests?limit=5 Moderator only

• getContest
GET /contests/:contest_id

• getContestDetails
GET /contests/:contest_id/details?only_name=false Moderator only

• getContestModerators
GET /contests/:contest_id/moderators Moderator only

• getAllParticipants
GET /contests/:contest_id/participants

• getAllParticipantsDetails
GET /contests/:contest_id/participants_details Moderator only

• participate
POST /contests/:contest_id/participate

• addQuestion
POST /contests/:contest_id/:questions

• removeQuestion
DELETE /contests/:contest_id/questions

• getAllQuestions
GET /contests/:contest_id/questions

• getQuestion
GET /contests/:contest_id/questions/:question_id

Questions API:

• createQuestion
POST /questions/ Admin only: to create a question

• getModeratorQuestions
GET /questions/moderator_questions?limit=5&search=’’ Moderator only: show the questions for which user is a moderator

• getQuestion
GET /questions/:question_id, Editor only: to get the question

• updateQuestion
POST /questions/:question_id/update, Editor only: to edit the questions, the change will be reflected in all instances of the question

• getAllQuestions
GET /questions/: - send all public questions with their contest_id (for practice section)

• addEditor
POST /questions/:question_id/editor Editor only: Add editor to a question

• removeEditor
DELETE /questions/:question_id/editor Editor only: Remove editor to a question (creator can't be removed)

• forkQuestion
POST /questions/:question_id/fork Editor only: Return the id of the forked new question

Submissions API

• createSubmission
POST /contests/:contest_id/questions/:question_id/submit All participants: everyone eligible for the contest should be allowed (For MCQ, allow only 1 attempt)

• getAllSubmissions
GET /contests/:contest_id/submissions Contest Moderator only: See all submissions (pagination, sorting, filtering and searching enabled)

• getUserContestSubmissions
GET /contests/:contest_id/users/:user_id/submissions The user only: get all submissions of a user

• gradeSubjectiveSubmission
POST /contests/:contest_id/subjective_submissions/:submission_id/grade Contest Moderator only: Grade a subjective submission providing a score along with an optional feedback. (If submission is already judged/graded, update it)

• getMCQSubmission
GET /contests/:contest_id/mcq_submissions/:submission_id Only the specific user: Get a specific MCQ submission
  GET /contests/:contest_id/mcq_submissions/:submission_id?moderator=true Contest Moderator only: Get a specific MCQ submission

• getSubjectiveSubmission
GET /contests/:contest_id/subjective_submissions/:submission_id Only the specific user: Get a specific subjective submission.
  GET /contests/:contest_id/subjective_submissions/:submission_id?moderator=true Contest Moderator only: Get a specific subjective submission

• getContestLeaderboard
GET /contests/:contest_id/leaderboard All participants: Everyone should be allowed, if show_leaderboard is true, otherwise allow only contest moderators.

• getQuestionLeaderboard
GET /contests/:contest_id/questions/:question_id/leaderboard:Everyone should be allowed, if show_leaderboard is true, otherwise allow only contest moderators.

• getPracticeLeaderboard
GET /practice/leaderboard: accessible to all

Tags API:

• createTag
POST /tag Admin only:

• getTag
GET /tag?search=keyword

[ridhishjain-zepto]

@cjchirag7 @NBNARADHYA see if it's fine

[cjchirag7]

Suggested changes :

1. We need 1 more field in submissions table -
   judged (tinyint - default 0)
   This will show whether the submission is judged or not (mainly for coding questions, this would be needed)

2. In contests table, we can have a field -
   confidential_questions (tinyint - default 0)
   Because sometimes the user may want that the questions of a contest should not be visible to anyone after the contest. Like for a recruitment test of club. But for other contests, the tendency can be to move the questions of past contests to practice section.

3. In the endpoint GET /contests?active=1&past=1&upcoming=1&limit=5 :
   The response should be like :
   {
   "active": [...array of max 5 active contests],
   "past": [...array of max 5 past contests],
   "upcoming": [...array of max 5 upcoming contests],
   }

4. In GET /contest/:contest_id/submissions?user=username Only admin or the user with username username : See all submissions of the user username. He should not be allowed to see other's submissions directly. If no query parameter is passed and if admin has sent the request, he should receive all the submissions of that contest.

5. In GET /contest/:contest_id/submissions/:submission_id It should be accessible both, by the admin of that contest as well as the user who made that particular submission.

Rest looks good to me.

[NBNARADHYA]

@ridhishjain main table headers as checkboxes instead of individual fields looks cleaner I think ?

[NBNARADHYA]

@ridhishjain Also could you keep the tables task in another issue ? and assign it to me also give edit access to me so that I can make changes to issue as and when I create tables and make PRs ?

[NBNARADHYA]

@ridhishjain Could u make those checkboxes as well? Table names ?

[ridhishjain-zepto]

working on it

[ridhishjain-zepto]

3. In the endpoint GET /contests?active=1&past=1&upcoming=1&limit=5 :
The response should be like :
{
"active": [...array of max 5 active contests],
"past": [...array of max 5 past contests],
"upcoming": [...array of max 5 upcoming contests],
}
4. In GET /contest/:contest_id/submissions?user=username Only admin or the user with username username : See all submissions of the user username. He should not be allowed to see other's submissions directly. If no query parameter is passed and if admin has sent the request, he should receive all the submissions of that contest.
5. In GET /contest/:contest_id/submissions/:submission_id It should be accessible both, by the admin of that contest as well as the user who made that particular submission.

these changes are incorporated

[cjchirag7]

We need the following endpoints for tags -

1. POST '/tags' - (admin only) create a tag
2. GET '/tags?search=keyword' - search for a tag

[ridhishjain-zepto]

added, schema created as well

[NBNARADHYA]

@ridhishjain change getModeratorQuestions to getEditorQuestions

[cjchirag7]

Once these endpoints are completed, we can also create an endpoint to download the whole leaderboard data of a contest as "CSV" and the details of all group members as "CSV", using csv-express

[ridhishjain-zepto]

Yeah once getter APIs get merged, i'll update the checks here and APIs in postman as well

[cjchirag7]

For generating, automatic notifications, when someone is added / removed from a group, we can generate automatic notifications using MySQL Triggers - AFTER DELETE Trigger and AFTER INSERT TRIGGER. I shall send it in a separate PR, later.

[ridhishjain-zepto]

Only getter for practice leaderboard is remaining as of now.