[issue 2927] Expand issue_history table to include project and sprint

[DavidDudas-Intuitial]

Summary

Fixes #2927

Time to review: 5 mins

Changes proposed

What was added, updated, or removed in this PR.

• Extends transform/load to support the case in which an issue occurs multiple times in an input json file but with different project and sprint value for each instance
• Alters gh_issue_history table to add columns project_id and sprint_id, and updates the issue transform/load step accordingly
• Changes the unique constraint on gh_issue_history from (issue_id, d_effective) to (issue_id, project_id, d_effective) thereby allowing an issue to be mapped to concurrent sprints in separate projects
• Updates tests

Context for reviewers

Testing instructions, background context, more in-depth details of the implementation, and anything else you'd like to call out or ask reviewers. Explain how the changes were verified.

The current schema does not allow for an issue to be mapped to multiple concurrent sprints (e.g. sprint 1.4 in project 13 and sprint 1.4 in project 17). The limitation causes minor calculation errors in percent complete metrics. This PR removes the limitation by extending the schema and the transform/load logic.

Note: With this PR, the table gh_issue_sprint_map becomes obsolete, but it still exists in the database and transform/load is still writing to it. After the new iteration of gh_issue_history is verified to be sufficient for percent complete metrics calculation, the obsolete table and code will be deleted.

Additional information

Screenshots, GIF demos, code examples or output to help show the changes working as expected.

In schema version 4, gh_issue_history looks like this:

   Column    |            Type             | Collation | Nullable |                   Default
-------------+-----------------------------+-----------+----------+----------------------------------------------
 id          | integer                     |           | not null | nextval('gh_issue_history_id_seq'::regclass)
 issue_id    | integer                     |           | not null |
 status      | text                        |           |          |
 is_closed   | integer                     |           | not null |
 points      | integer                     |           | not null | 0
 d_effective | date                        |           | not null |
 t_created   | timestamp without time zone |           |          | CURRENT_TIMESTAMP
 t_modified  | timestamp without time zone |           |          | CURRENT_TIMESTAMP
Indexes:
    "gh_issue_history_pkey" PRIMARY KEY, btree (id)
    "gh_ih_i1" btree (issue_id, d_effective)
    "gh_issue_history_issue_id_d_effective_key" UNIQUE CONSTRAINT, btree (issue_id, d_effective)

In schema version 5, gh_issue_history looks like this:

   Column    |            Type             | Collation | Nullable |                   Default
-------------+-----------------------------+-----------+----------+----------------------------------------------
 id          | integer                     |           | not null | nextval('gh_issue_history_id_seq'::regclass)
 issue_id    | integer                     |           | not null |
 status      | text                        |           |          |
 is_closed   | integer                     |           | not null |
 points      | integer                     |           | not null | 0
 d_effective | date                        |           | not null |
 t_created   | timestamp without time zone |           |          | CURRENT_TIMESTAMP
 t_modified  | timestamp without time zone |           |          | CURRENT_TIMESTAMP
 project_id  | integer                     |           |          | 0
 sprint_id   | integer                     |           |          | 0
Indexes:
    "gh_issue_history_pkey" PRIMARY KEY, btree (id)
    "gh_ih_i1" btree (issue_id, d_effective)
    "gh_issue_history_issue_id_project_id_d_effective_key" UNIQUE CONSTRAINT, btree (issue_id, project_id, d_effective)

[DavidDudas-Intuitial]

Here's what I see in my local db after running the past several days of data into the new schema:

app=# select id, ghid, title from gh_issue where  ghid like '%2887';
  id   |                ghid                |                                 title
-------+------------------------------------+------------------------------------------------------------------------
 10588 | HHS/simpler-grants-gov/issues/2887 | Add new columns to analytics database to enable sprint burndown charts
(1 row)

app=# select issue_id, status, is_closed, points, d_effective, project_id, sprint_id from gh_issue_history where issue_id = 10588 order by project_id, d_effective;
 issue_id |   status    | is_closed | points | d_effective | project_id | sprint_id
----------+-------------+-----------+--------+-------------+------------+-----------
    10588 | Icebox      |         0 |      0 | 2024-11-18  |          1 |
    10588 | Icebox      |         0 |      0 | 2024-11-19  |          1 |
    10588 | Icebox      |         0 |      0 | 2024-11-20  |          1 |
    10588 | Done        |         1 |      1 | 2024-11-21  |          1 |         3
    10588 | In Progress |         0 |      3 | 2024-11-18  |          2 |        26
    10588 | In Review   |         0 |      3 | 2024-11-19  |          2 |        26
    10588 | In Review   |         0 |      3 | 2024-11-20  |          2 |        26
    10588 | Done        |         1 |      3 | 2024-11-21  |          2 |        26
(8 rows)