current insert_facade_contributors logic not linking previously resolved contributors

[collectoss-issue-migrator]

Note

Migrated from augurlabs/augur#3779
Originally opened by @MoralCode on 2026-03-19

---

https://github.com/chaoss/augur/blob/49a008ab97c43472339e400cb316a5323110d78d/augur/tasks/github/facade_github/tasks.py#L210-L246

Query error
this query, per the comments and what i understand of the implementation is getting all of the commit data's emails and names from the commit table that do not appear in the contributors table or the contributors_aliases table.

This works fine for new contributors that are not yet in the contributors table.

Where this fails is if a record slips through (maybe commits were made using an email that later got linked to a github account). In that case, the records with NULL cmt_ght_author_id never get revisited and properly linked to a contributor that IS resolved, just not for the commits that slipped through.

Last Collection Date

since we added a filter for last collection date (added by @IsaacMilarky added it in 8539825bb217c388735dfa1bc43d25dc4cee0d51, PR augurlabs/augur#3253 ), this problem is worse, since anything that slipped through or didnt get properly linked will now be systematically ignored by its older last collection date.

When this PR was filed, I called out that the change it was making seemed unrelated to the analyze_commits_in_parallel problem that was being solved at the time (augurlabs/augur#3253 (comment)).

Since this query is being run as essentially a precondition check to establish what records we should attempt to run contributor resolution on.

currently our logic is to find all email addresses (looking up against two sources, the contributors table and the aliases table, see #237) haven't been matched yet

That is way less simple than "we should run contributor resolution on all commits we don't currently have linked to a contributor" AKA "is the cmt_ght_author_id NULL?"

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @cdolfi on 2026-03-19

---

This could be greatly simplified and improved by just changing the query to check for null cmt_ght_author_id values and filter based on the emails in the unresolved_commit_emails. This would prevent a constant rerunning of null cmt_ght_author_id values that have emails that can not be resolved but also allows for the greatest chance of contributor resolution. Also the date filtering in this case also is limiting augurs ability to run the contributor resolution on commits with no cmt_ght_author_id historically but can now be resolved. The query could be something like:

SELECT DISTINCT
    commits.cmt_author_name AS NAME,
    commits.cmt_commit_hash AS hash,
    commits.cmt_author_raw_email AS email_raw
FROM
    commits
WHERE
    commits.repo_id = :repo_id AND
    commits.cmt_ght_author_id IS NULL AND
    commits.cmt_author_raw_email NOT IN (
        SELECT email FROM augur_data.unresolved_commit_emails
    )

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @MoralCode on 2026-03-19

---

making this change will also make it way easier for augur to ship code using its own logic to self-recover from the secondary effects of #233 (i.e. incorrectly linked contributors resulting from an empty author name bug) because the cmt_ght_author_id can simply be set to null to cause it to be re-evaluated on the next collection

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @MoralCode on 2026-03-20

---

i also wonder if the case where a repo gets deleted before contributor resolution has had a chance to process (such as an email address getting stuck in the unresolved emails table due to a github glitch) could also cause a large number of commits that could have been linkable to simply be missed.

if a repo was deleted, the commits API wont be able to resolve them since the commits don't exist in the given repo (and resolution is done on a per-repo basis)

Essentially the idea is that this query should be updated to look for all records where there is not yet a resolved contributor (by looking for a null cmt_ght_author_id), rather than trying to re-create the logic of deciding whether a contributor is resolved by email

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @MoralCode on 2026-03-20

---

hmmm actually.... there's also a second large query beneath this main one that seems to control how records are linked to contributors. (i.e. this query just controls the creation of new contributors/updating of aliases)

I strongly suspect we would need to change both at once - to me it would make way more sense to adjust the contributor resolution process to be:

• one iteration per contributor to ensure they exist - this would give the resolution process more than one commit for better processing (as mentioned in order contributors to resolve by unique email for improved efficiency and github API fault tolerance #253)
• the existing SQL that links a contributor to all their commits can then be run at the end of this iteration, eliminating the separate lookup + batch + process loop for re-querying and linking contributors

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @cdolfi on 2026-03-24

---

https://github.com/chaoss/augur/blob/49a008ab97c43472339e400cb316a5323110d78d/augur/tasks/github/facade_github/tasks.py#L283-L311 code for second comment

[collectoss-issue-migrator]

Note

Migrated comment from original
Originally posted by @cdolfi on 2026-03-24

---

My suggested query for resolve_email_to_cntrb_id_sql:

WITH email_to_contributor AS (
    SELECT cntrb_email AS email, cntrb_id
    FROM augur_data.contributors
    WHERE cntrb_email IS NOT NULL

    UNION ALL

    SELECT canonical_email AS email, cntrb_id
    FROM augur_data.contributors
    WHERE canonical_email IS NOT NULL

    UNION ALL

    SELECT alias_email AS email, cntrb_id
    FROM augur_data.contributors_aliases
    WHERE alias_email IS NOT NULL
),
deduplicated AS (
    SELECT DISTINCT ON (email) email, cntrb_id
    FROM email_to_contributor
    ORDER BY email
)
SELECT
    d.cntrb_id,
    c.cmt_author_email AS cntrb_email
FROM
    augur_data.commits c
INNER JOIN
    deduplicated d
    ON c.cmt_author_email = d.email
WHERE
    c.cmt_ght_author_id IS NULL AND
    c.repo_id = :repo_id

[MoralCode]

This was merged in augur after the fork but before the issues were copied. It is not resolved, but will be when the linked PR merges