tux Reconsider some aspects of our curent database setup before we fully migrate ORMs

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.

Jun 22 '25 23:06 anemoijereja-eden

lgtm

Jun 24 '25 01:06 kzndotsh

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

Jul 14 '25 06:07 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

Jul 14 '25 21:07 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

yep, its definitely for the best though

Jul 14 '25 21:07 electron271

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

Jul 14 '25 21:07 anemoijereja-eden

Seems to me that this ought to be split into multiple sub-issues

Jul 14 '25 22:07 evolvewithevan

@evolvewithevan will not be done as this is all being done at once

Jul 14 '25 22:07 electron271

thoughts on this for guildconfig?

Jul 14 '25 22:07 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

Jul 15 '25 00:07 electron271

Jul 15 '25 03:07 electron271

suggestions by other members

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

Jul 15 '25 03:07 electron271

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?

Jul 15 '25 03:07 amadaluzia

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

Jul 15 '25 03:07 electron271

Makes sense.

Jul 15 '25 03:07 amadaluzia

StarboardEntry gets my vote

Jul 15 '25 04:07 anemoijereja-eden

agreed, renamed

Jul 15 '25 07:07 electron271

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

Jul 15 '25 07:07 electron271

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

Jul 15 '25 09:07 anemoijereja-eden

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

Jul 15 '25 17:07 electron271

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

Jul 15 '25 17:07 amadaluzia