Cross-contamination between github and gitlab columns in contributors table

[MoralCode]

im seeing lots of gh_* prefixed columns being used for gitlab data too...

Originally posted by @MoralCode in #3435 (comment)

While looking into this migration issue, I noticed that there are duplicate values in our database for the gh_login and cntrb_login fields in our db. Looking at a full row from one dupicate user, its clear that this data came from two different forges (one github, one gitlab).

There seems to be several issues here:

• cntrb_login and gh_login may both be populated together by both the github events task and the gitlab issue comments task.
• for the gitlab user, gl_username is NULL
• gh_avatar_url has a non-null value for the gitlab user (seems incorrect given the table name) while the gl_avatar_url field is NULL
• same thing for gh_url
• gh_user_id has a value for the gitlab user, while gl_id is NULL

Further, it seems like the gitlab username and other contributor data is largely being populated in the columns that have gh_* prefixes. This means that our Database models, which are seemingly written to enforce username uniqueness for gh and GL usernames separately, are not able to actually do so (and may not even be currently being constrained)

[MoralCode]

cntrb_login has a comment in the schema: "Will be a double population with the same value as gh_login for github, but the local value for other systems" - maybe the dual population is somewhat intentional?

[MoralCode]

Ultimately, if we correct this issue by moving gitlab data to the gl_ columns, we paint ourselves into an awkward corner when it comes time to add support for more platforms (i.e. forgejo, see #3236) in the future because adding a new platform may mean duplicating our existing tables.

I'd prefer to solve this issue by instead allowing the columns that we are currently trying to separate to coexist. by that I mean instead of gl_username and gh_username we should just have platform_username and a platform column indicating which platform the data came from.

I'd also like to integrate this system with other platform-oriented improvements, such as enabling the domains and keys for a platform to be changed independently of the underlying API implementation (i.e. to support users who want to use github enterprise #3277)

Ultimately a lot of these changes are going to be very big, very complex, and probably out of scope for newcomers, even (especially) with advanced AI tools.

Originally posted by @MoralCode in #3517 (comment)

I'd be happy to chat about this in more detail over on the CHAOSS Slack in the #wg-augur-8knot channel, but This is going to require a lot of planning and likely a phased implementation approach.

[MoralCode]

i wonder if this could maybe be an opportunity for #3363?