Database model reconsideration

[anemoijereja-eden]

The current database setup for Tux has accumulated some issues over the course of development, which we should consider looking over before fully rewriting the database logic.
Since the migration from Prisma is also likely going to be fairly involved and potentially coincide with migrating prod to local postgresql, it's also the ideal time to make large changes that could require an external migration script.

Issues of note:

• The AFK database enforces uniqueness on user ids, making it work very poorly in multi-guild scenarios
• The starboard and starboardmessage database tables are named in a fairly confusing manner
• the notes table does not seem to actually be used anywhere.
• the overall naming scheme for database tables is not applied very consistently

All of these issues can be resolved without significant cog changes.

[kzndotsh]

lgtm

[electron271]

we should also probably rewrite so things are closer to sqlmodel conventions

[anemoijereja-eden]

changing the schemas drastically will require initial migration with an external migration script. a full schema overhaul would be a huge improvement for us though. I'm in favor of it, just keep the extra difficulty with the migration step in mind

[electron271]

changing the schemas drastically will require initial migration with an external migration script. a full schema overhaul would be a huge improvement for us though. I'm in favor of it, just keep the extra difficulty with the migration step in mind

yep, its definitely for the best though

[anemoijereja-eden]

100%, the changes would absolutely be worth the effort. clearing up some of the tech debt would be huge for future development

[electron271]

thoughts on this for guildconfig?

[electron271]

for the migration script maybe on startup it checks for the "AFKModel" table, if it exists it sends a critical type error and tells you to run a specific python file after making a database backup

[electron271]

[electron271]

suggestions by other members

• rename StarboardData (i agree, im not really sure what to call it though)
• maybe not make names plural

[amadaluzia]

StarboardData makes it seem more about data for the starboard then data for the message in the starboard. My idea is to call it StarredMessage, another idea was StarboardEntry and StarboardItem. What do you think?

[electron271]

id like to keep naming similar with Starboard so the other two may be better

[amadaluzia]

Makes sense.

[anemoijereja-eden]

StarboardEntry gets my vote

[electron271]

agreed, renamed

[electron271]

set to plural to follow the already set standard and also seemingly the main standard, feel free to dispute using plural however

[anemoijereja-eden]

I actually disagree. in code the model would be StarboardEntries as an object referring to just one, single entry object. this feels sloppy and makes the object pretty confusing. we should standardize not doing that instead

[electron271]

I don't really see how? it's holding a lot of different starboarded messages

[amadaluzia]

Would it be worth refactoring StarboardEntry to be for each message, and then it could be a table rather than a record?