Schema Changes

[clxxiii]

The end goal of this API is to allow both the bot repo, and the web repo to be entirely free of database code, by interacting with the API served in this repo. While we are making this big change, there were some lingering grievances we had with how the old schema was set up. Since we are reworking how our application interacts with data already, we are also changing how data is stored in the first place:

• [x] Make all model properties snake_case
• [x] Clear unnecessary properties from Map model (everything except id, star rating, cs/ar/od etc.)
• [x] Allow a mappool to be a member of multiple rounds

---

Scrims

• [x] Move settings-related properties of Tournament to a TournamentSettings model.
• [x] Move settings-related properties of Round to a RoundSettings model, and remove ScrimSettings
• [x] Add support for protects in MapInmatch, and add a protects property to RoundSettings
• [x] Add a MappoolInMatch model that links the two together.

---

Qualifiers

• [x] Add a Score model to keep track of team's scores, this should be a list in MapInMatch
• [x] Add a TeamScore model that keeps track of total team scores for a MapInMatch
• [x] Add map_order to MapInPool
• [x] Add a maps property to QualifierMatch to keep track of team scores

---

General Cleanup & Additions

• [x] Add star_rating_dt, star_rating_hr etc. to Map model
• [x] Add timezone property to teams for automatically generating match times
• [x] Rename CustomBadge and MapInMatch to something more intuitive
• [x] Remove all delete_warning properties
• [x] Remove AutoHost models

[JPW03]

I'll explain some of the changes I made alongside allowing for multiple mappools per round.

• Round to Mappool is now a many-to-many relationship with a linker, so we can have many rounds per mappool as well now.
• Round.mappools and Mappool.rounds are implicitly linked through the MappoolInRound table. I didn't know we could do this until now, but I think it's worth looking for other places in the schema where we can add this.

I have some other suggestions regarding mappools and rounds:

• Removed Mappool.global because I don't see the point in having mappools that are public on a tournament but not shown on the mappool browser, I feel like that defeats the point of the mappool browser and I can't think of an instance where I'd want a mappool to only be shown to the players and not the public.

• Now that round and mappool are many-to-many, the QualiferRound table feels redundant and if we added many-to-many for qualifiers as well, it gets unnecessarily complicated. I suggest we just add a boolean to Round for whether or not it is a qualifier round or not.

[JPW03]

Regarding the Map model reformat: I've omitted a lot of the beatmap/beatmapset attributes and kept all that I feel like could be used in some way at some point, and some of the attributes have different names from what they are in the API just to be more intuitive and consistent.

[clxxiii]

The global property was initially for map pools that were unconnected to rounds, but were kept for old history & matchmaking's sake. I like this solution better so we can remove it.

I can't really think of a situation where one round would need to have multiple map pools. I get a mappool being in multiple rounds, but having it be a many-to-many might make the logic for which mappool to use a bit less intuitive

Love the qualifier idea though! If we are combining them then it might be worth adding a order property to the MapInPool objects so you can specify an order that the maps are played in. (Useful for tryouts)

[clxxiii]

Now might be a good time to do an actual Prisma migration. Assuming we don't want to throw out all the old data, rebinding the new names of attributes might be a hassle if we make too many more changes (Prisma sometimes isn't smart enough to realize it should move 'GuildId' to 'guild_id', so you have to manually change it in the SQL)

[JPW03]

I can't really think of a situation where one round would need to have multiple map pools

In Drenched Program/Charlie's Perfect Math Class they had 3 pools for the entire bracket stage and during matches, the players would ban 2 mappools and play the remaining one. That's the main example that comes to mind but there are several other tournaments that have similar gimmicks.

[JPW03]

Now might be a good time to do an actual Prisma migration

Yeah, I agree. Automatically migrating does currently wipe all the data because of the attribute name changes. Although to reduce work, you could ignore anything that could be retrieved from an API.

it might be worth adding a order property

I agree but there's the QualifierMatch.map_index attribute, I assume it's related but I don't know exactly what it means. QualifierMatch also doesn't contain anything for storing scores, so And while we're talking about qualifiers, we don't have any attributes for seeding or calculating seeds. A blitz tournament is still our priority so this could be out of scope to fix for now.

I've got an idea for the Match object for adapting it for the new round-mappool relation and making it suitable to be reused for non-tournament matches.

• MatchInTournament object where the attributes that link a match to a tournament and its round will be moved to from Match
• Link Match to the mappools (Match.mappools of type Mappool[]) played during the match, so you can assign a mappool to a non-tournament/matchmaking match. (This would also be useful for tournaments similar to Charlie's Perfect Math Class where some mappools are banned and 1 is played)

Thoughts?

[clxxiii]

there's the QualifierMatch.map_index attribute, I assume it's related but I don't know exactly what it means.

From what I remember, that one is to keep track of which map the lobby is currently on. We would need another property though to keep a hold of scores. I'd say we just add a team_score_1 property (or something of the sort to have proper linking) to MapInMatch, that way we can also hold the statistics for scores in the bracket matches, bet some stats nerds would thank us for that.

Other two ideas look good. I think it would be a great idea for the matchmaking matches to have a similar mappool ban system. The more we can make this thing feel like faceit, the better lmao

[JPW03]

Some other ideas for schema changes

• Add a user list of poolers to a mappool
• Add score_mode to a score so we can easily tell apart scorev1/scorev2/lazer
• Add a seed attribute to a team
• Completely forgot about issue #5, so a skillset (and secondary_skillset) attribute in either MapInPool or Map (probably the former)