Note: I have no idea how to do database migrations in Heroku Postgres, and there's a chance I'll have to do some database stuff by hand if this needs to be updated later.
Schema Rationale
If I want to get fancy at some point, I can probably enforce in the database that a DiscordID in the authenticated user list has at least one of lichess data, chess.com, and I can definitely enforce with this schema that if a DiscordID is deleted (which is not currently supported or roadmapped functionality) it will delete the linked profiles in a cascading way.
Making usernames a primary key ensures that two users don't grab the same profile somehow. This might not be necessary, so maybe I shouldn't enforce this.
SMALLINT supports ELOs well above 3600 which isn't really a human elo.
Storing data in site-specific tables will make it easier to add database support for other sites later if desired. This also makes daily updates pretty easy, since we'll just need to update all the data in the two site-specific tables, and then update all the belts in authenticated users.
Database needs to be able to store:
Schema things
Authenticated Users
Lichess Data
Chess.com Data
Note: I have no idea how to do database migrations in Heroku Postgres, and there's a chance I'll have to do some database stuff by hand if this needs to be updated later.
Schema Rationale
If I want to get fancy at some point, I can probably enforce in the database that a DiscordID in the authenticated user list has at least one of lichess data, chess.com, and I can definitely enforce with this schema that if a DiscordID is deleted (which is not currently supported or roadmapped functionality) it will delete the linked profiles in a cascading way.
Making usernames a primary key ensures that two users don't grab the same profile somehow. This might not be necessary, so maybe I shouldn't enforce this.
SMALLINT supports ELOs well above 3600 which isn't really a human elo.
Storing data in site-specific tables will make it easier to add database support for other sites later if desired. This also makes daily updates pretty easy, since we'll just need to update all the data in the two site-specific tables, and then update all the belts in authenticated users.