DB Schema and users for jobs posts/warnings/etc

[royanger]

We've talked about this before, but I wanted to toss up some thoughts on this in more detail.

A few suppositions form these thoughts:

1. Aside from mods/admins, no one will log into the dashboard
2. The users section of the dashboard doesn't benefit from listing lots of non-mod users. It should be a way to see who are mods/admin
3. With that in mind, I don't believe that there is a benefit to creating Account/User entries

If we look at my extended comment at https://github.com/JacobMGEvans/ts-cult-bot/blob/main/bot/src/commands/create-job-posting.ts#L90 we can see that the job posting command is currently creating only a User row and not an Account row, and in doing so is placing the Discord ID into the id field on User (instead of the providerAccountId on Account) and placing the id of the image into the image field (instead of a URL).

At the bare minimum this would need adjusting to that the data matches what next-auth is creating. The comment I made covers that, should we go that route. I don't think that's the solve though. I propose instead a DiscordUsers table, perhaps with the following schema:

• id (cuid())
• discordId
• name*
• createdAt
• updatedAt
• imageId or imageUrl (URL assembled as per the comment linked above)

• this is a maybe since it can become stale. It may be better to see how hard it for the dashboard to query that info live when displaying.

If we go this route then working with users from the bot side will be far simpler, and from the dashboard side we don't need to filter these out of pages that display all mods, etc.

Currently the provider and providerAccountId fields on Account are unique. If we leave that and don't create a DiscordUsers table then we need to test that a user can still login to the dashboard. We probably need to either remove that unique flag which is probably a bad idea or we need to fully commit to creating users in the Account+User table from the bot, up to and including the provider and providerAccountId and test that a user created by the bot can still login through the Dashboard. This would apply to someone who posts a job, etc, and then later becomes a mod.

[JacobMGEvans]

Is there a way to link the User to DiscordUser if they join as Mod in the future?

[royanger]

We have the providerAccountId in Account and the discordId (or whatever we call it) in the DiscordUsers so we can just query/mutate using that.

Right now to get all user info you would find the user in the User table (whether that's through a Account query or something else), then use the User to get all Accounts. Generally this is a needless step and next-auth has, in all cases I've used it with, created a new User and Account row for a different auth provider. IE, I use [email protected] for Google and Twitter and if I logged in with both there would be two rows in each table, instead of one in User referencing the two in Account.

Issues with next-auth aside, once we identify the user we can just go User -> Account (user.id <-> Account.userId) -> DiscordUser (Account.providerAccountId <-> DiscordUser.discordId)

[JacobMGEvans]

Sounds good to me!

[royanger]

Table added with both imageId and imaggeURL. Once once is in use, I will remove the other.