Masters inactive ProgramCourseEnrollment gets revived incorrectly during SSO linkage

Description

Problem description:
Some system behavior facts to Know before you can understand the problem:

  • GT and Purdue setup their Program and course relationship where a course run can be part of 2 programs.

  • The ProgramCourseEnrollment table have a 1 to 1 relationship to student_courseenrollment record. This means, if we have multiple ProgramCourseEnrollment trying to save the same student_courseenrollment id, we suffer a database integrity error. This error prevents SSO linking and fails silently for learner

Scenario:
If we have a learner who is enrolled with program A course 1, then later on, gets cancelled enrollment for both the course 1 and program A, then gets enrolled into Program B course 1, then when the learner is trying to SSO into the system and realizing the enrollments for their Program B course 1 enrollment, their "inactive" program course enrollment associated with Program A gets revived and we suffer a duplicated key issue with student_courseenrollment in the ProgramCourseEnrollment table.

Database SQL Script to detect this scenario in production:

```
select distinct pe1.external_user_key
from program_enrollments_programcourseenrollment pce1
join program_enrollments_programenrollment pe1 on pe1.id=pce1.program_enrollment_id
join program_enrollments_programenrollment pe2 on pe1.external_user_key=pe2.external_user_key and pe1.program_uuid <> pe2.program_uuid
join program_enrollments_programcourseenrollment pce2 on pce2.program_enrollment_id=pe2.id
where pce1.course_key = pce2.course_key
```

Solutions

  • Remove the database integrity constraint on the ProgramCourseEnrollment with course_enrollment_id column

  • OR Do not revive the "inactive" ProgramCourseEnrollment record

Steps to Reproduce

None

Current Behavior

None

Expected Behavior

None

Reason for Variance

None

Release Notes

None

User Impact Summary

None

Assignee

Unassigned

Reporter

Simon Chen

Reach

None

Impact

None

Platform Area

None

Customer

None

Partner Manager

None

URL

None

Contributor Name

None

Groups with Read-Only Access

None

Story Points

None

Actual Points

None

Category of Work

None

Platform Map Area (Levels 1 &amp; 2)

None

Platform Map Area (Levels 3 &amp; 4)

None

Priority

Unset
Configure