Issue: Some fields should have unique constraint in openedx/edx-platform

1. Constraint in class CertificateInvalidation

generated_certificate should be unique in the model CertificateInvalidation, as one certificate should not be invalidated twice. This would cause exceptions in the program which hurts the data integrity. We can add unique=True to prevent that.

In file lms/djangoapps/certificates/

class CertificateInvalidation(TimeStampedModel):
    Model for storing Certificate Invalidation.

    .. no_pii:
    generated_certificate = models.ForeignKey(GeneratedCertificate, on_delete=models.CASCADE)
    invalidated_by = models.ForeignKey(User, on_delete=models.CASCADE)

2. Constraint in class BadgeAssertion

Each user should only have one completion badge. We can add this data constraint with
unique_together=('user', 'badge_class') to avoid invalid data to get in the database.

# Usage in lms/djangoapps/badges/events/
    if BadgeAssertion.objects.filter(user=user, badge_class=badge_class):"Completion badge already exists for this user on this course.")

# definition in  lms/djangoapps/badges/
class BadgeAssertion(TimeStampedModel):
    Tracks badges on our side of the badge baking transaction

    .. no_pii:
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    badge_class = models.ForeignKey(BadgeClass, on_delete=models.CASCADE)

3. Constraint in class LoginFailures

Similar issue, user field should be unique for the LoginFailures, this would prevent duplicate entries for the same user, due to potential race conditions of get_or_create() in the first place.

class LoginFailures(models.Model):
    This model will keep track of failed login attempts.

    .. no_pii:
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    failure_count = models.IntegerField(default=0)
    lockout_until = models.DateTimeField(null=True)

I can work on the PR for this issue if needed, please let me know your thoughts. Thanks!

I think the best way to proceed is to open a draft pull request with a full description of the problem (like you’ve given here). You don’t have to start on the implementation yet. There may be a reason we can’t proceed with a change to the constraints, but the pull request will be a good place to have the discussion.

1 Like

FWIW, it might be best to just start with one of them. Depending on the size of these tables and the amount of broken data inside, it might be operationally painful to run these migrations.