Support repository rename and issue migration

[dpordomingo]

If I'm not wrong, the PR reviews are currently tied to its causing PR by its owner/repo/number, instead of using github repository.id.

Since a repository can be renamed, the current schema does not let yo to obtain info about comments per PR with this kind of queries:

SELECT pull_request_number, repository_owner, repository_name, count(*) as count
  FROM public.pull_request_reviews
  GROUP BY pull_request_number, repository_owner, repository_name
  ORDER BY count desc

It happens the same with issue comments, and also with issues. Since an issue can be also migrated to a different repo, or it can be renamed, sorting repos by the amount of created issues would not be also possible:

SELECT count(*) as count, repository_owner, repository_name
  FROM issues GROUP BY repository_owner, repository_name
  ORDER BY count desc;

See below :point_down: for more details and proposals to solve the described problem.

[dpordomingo]

When can this problem happen?

• Once the importation takes enough time to let a repository to be renamed, or an issue to be migrated in that timeframe.
  • As a subcase of the previous one: once the import process is a long term process (living forever), adding github data as long it is created, in real time.

Alternatives

(A) To use provider ids as foreign keys

• issue → (fk: repo_id) → repoitories
• issue_comments → (fk: issue_id) → issues
• pull_requests -> (fk: repo_id) → repoitories
• pull_request_reviews -> (fk: pull_request_id) → pull_request
• pull_requests_comments -> (fk: pull_request_id) → pull_requests
  • not a strong requirement bec it exists fk:pull_request_review_id → pull_request_reviews

It could also speed up some queries/charts if it were also added fk: repo_id into issue_comments, pull_request_reviews and pull_requests_comments, so it would be easy to query for these entities per repo.

(B) listen for repo renames and issue migration

If every time it happens, the whole dataset is updated, it would be fully reliable the group: owner/repo/number. (But I think it could be a bit expensive in big orgs and big repos)