Update schema

[L-Mario564]

This PR aims to update the current database schema into a more finalized one for our current goal of implementing everything registrations related for tournaments. The goal being that we shouldn't have to apply many more migrations moving forwards, not until we eventually tackle pooling, reffing or stats, for which I'll make a similar PR to this one for those areas too in the future.

The scope of this is just the database schema. Any tRPC procedure, SSR and other backend stuff that needs to be updated according to these changes will be addressed separately.

Changes

• Added deleted property to StaffMember to soft delete staff members rather than fully deleting them. This was added because although a user can be removed from staff, they may have already performed actions tied to them specifically (like them being credited for pooling a specific map in a mappool).
• Replace citext type columns with varchar or text.
• Created team and player related tables. The tables should work well regardless of the chosen type of tournament (draft, solo, teams).
• Created a table for the implementation of team player invites and tournament staff invites.
• Added a field to the Tournament table to store the mod multipliers. The "bl" (BL) mod is the new difficulty increase mod introduced in osu! Lazer, blinds; thought it was worth adding just in case.
• Added form related tables.
• Added and edited many indexes.

About the citext removal

Upon further research, I discovered that there's a caveat to using indexes on citext columns:

It won't use an index, unless you create a functional index using lower. Source: https://www.postgresql.org/docs/current/citext.htm

I used this article as a base to implement proper indexing for full text search, which uses the pg_trgm extension.

About player related tables

Despite the 3 types of tournaments we're aiming to support, the Team and Player tables should be good for all of them, as the only thing that changes is how each element is handled between tournaments. In solo, only the Player table is used; in draft, a user signs up as Player and the admins of the tournament build the Team; in teams, users can either create a Team or sign up as Player (in this context, they would be a free agent).

The player's stats will be updated in production via a CRON job that hits and endpoint that will update their osu! rank as well as the BWS rank in whatever active tournament they're registered in and that uses a BWS formula.

The availability of the player will be displayed in a similar fashion as to how team management spreadsheets do (a team sheet of mine as an example below).

I used ChatGPT (yes, really) to write me encoding and decoding function so it translates an integer into an array of 24 elements, representing the 24 hours of a day, and listing each hour as either available or not available.

About form related tables

I tried my best to make the storing of user created forms straight forward, managing to create just 2 tables for any user created form. The 3rd one is specific for tournament use.

The idea is to essentially copy Google Forms. Not much else to say, no need to reinvent the wheel here.

[L-Mario564]

Why do we need forms and tournament forms be separated?

The idea is that website admins are going to be able to create forms that anyone can submit responses to. For example, a form that asks any Kyoso user what they think of the website, or a form to get new developers in.

Tournament level forms aren't controlled by the website admins, but rather, the admins of the specific tournament it was created for, as well as having the option to set it for everyone, only staff, only players, only team captains. For example, a staff registration form would be for everyone since anyone should be able to apply for staff, while a form for only staff would be useful when a tournament finishes and the host wants to get the opinion on how everything was handled at the staff level.

[L-Mario564]

@Entropy-10 In a similar note to the last observation you made, what are your thoughts on changing the below fields in Round to be timestamps so they can be scheduled?

{
  publishPool: boolean('publish_pool').notNull().default(false),
  publishSchedules: boolean('publish_schedules').notNull().default(false),
  publishStats: boolean('publish_stats').notNull().default(false),
}

(I don't think it's necessary to schedule playtesting).

[Entropy-10]

@L-Mario564 hmmmm I think that would be a nice feature. Though some people might just prefer to hit a button and have it immediately be published. This could probably just be handled by setting the timestamp to the current time though.

[L-Mario564]

This could probably just be handled by setting the timestamp to the current time though.

Yep, if they hit some sort of "publish" button the timestamp can be set to now().

[L-Mario564]

Additional changes

• Instead of deleted and it being a boolean, now we have deletedAt being a timestamp column instead (in all tables that had deleted).
• publishPool, publishSchedules and publishStats are now timestamps.
• Updated the trgm indexes to use gist instead of gin.
• Add manage_theme to staff_permission enum.

Aside from the changes made, let me know if there's anything else that could/should be stored in the database that is related to staff and player registrations.

[L-Mario564]

Things to update:

• updateTournament and deleteTournament procedures to add functionality related to mod multipliers and change the deleted column with deletedAt. (Will resolve in my #54 PR).
• search procedure to use pg_trgm instead of ilike.