Upsert grading information in DB on Blackboard assignment launch

[seanh]

Belongs to epic: https://github.com/hypothesis/lms/issues/662
Proof of concept: https://github.com/hypothesis/lms/pull/945

Whenever a student launches an assignment in Blackboard we need to persist some information about that launch in our LMS app's database, so that we'll be able to submit grades for that student and assignment to Blackboard later (in subsequent LTI launches by the course teacher).

Task list

• [x] Create the new model class(es) and DB table(s) (including DB migration, and running the migration on QA and prod)
• [x] Change the LTI launch views to upsert rows into the table(s) on launches
• [x] Don't crash when a teacher launches assignments (and the grading-related launch params are missing)
• Implement a solution for expiring old rows from the table(s), so they doesn't grow forever (edited with consent by @lyzadanger)

What needs to be persisted

The information that needs to be saved to the DB is:

• LTI launch parameter lis_result_sourcedid

• LTI launch parameter lis_outcome_service_url

  We should save a separate copy of lis_result_sourcedid and lis_outcome_service_url for each student/assignment, and we should update them with the new given values if the student launches the assignment again, these are not guaranteed to remain the same for the same LMS or to remain the same over time. Do not try to save work here by saving these only once.

• LTI launch parameter oauth_consumer_key

• LTI launch parameter user_id

• LTI launch parameter context_id (the course ID)

• LTI launch parameter resource_link_id (the assignment ID)

• The user's Hypothesis username (request.context.h_user.username)

  This is not a request parameter. It's a value generated by LTILaunchResource based on some LTI launch params.

• The user's Hypothesis display name (request.context.h_user.display_name)

  This is not a request parameter. It's a value generated by LTILaunchResource based on some LTI launch params.

This requires a new database table with a unique constraint on the oauth_consumer_key, user_id, context_id and resource_link_id fields. We never want to have two rows of grading information with the same oauth_consumer_key, user_id, context_id and resource_link_id.

Logic for how the fields need to be created and updated

These fields need to be upserted on every assignment launch. When a user launches an assignment, if the request contains a lis_result_sourcedid parameter, we should:

• Find the existing database row(s) matching this oauth_consumer_key, user_id, context_id and resource_link_id if it already exists, and update it/them:
  • The lis_result_sourcedid and lis_outcome_service_url fields should be updated with the values from the new LTI launch, as the LMS may change these over time
  • The Hypothesis username and display name should be recomputed and updated in the DB, because these may have changed (e.g. if the user has changed their name in the LMS)
• If no row(s) exists, create a new row(s) in the DB

Not every LTI launch contains the fields

When a teacher launches an assignment there are no lis_result_sourcedid and lis_outcome_service_url fields. You can't grade teachers. We need to detect this and not try to record the data in the DB, for teachers.

This may require more than one DB table

We may want a separate database table for recording the user information: the LTI oauth_consumer_key and user_id and our generated Hypothesis username and display_name. The other new DB table (the one containing the lis_result_sourcedid and other grading-specific fields) will then have foreign keys to this user table. That way, we can update the user's display name whenever that user launches any Hypothesis assignment, rather than having a separate copy of their display name per assignment and only updating it when they launch that particular assignment.

It may be that even more than two DB tables are needed...

Blackboard only

We should only save grading-specific data into the DB for Blackboard launches. This grading data is not used for grading in Canvas. And we do not yet support grading in other LMS's.

We can identify Blackboard based on these launch params that it sends:

• tool_consumer_info_product_family_code: "BlackboardLearn" (this is a standard LTI launch param)

After we've shipped grading for Blackboard we will likely very quickly ship it for Brightspace and Moodle, and maybe also Sakai (all the other LMS's that we have test sites for) based on the same code, and this will likely involve loosening this Blackboard-only logic in the code, but we still will not need this information for Canvas, and let's leave other LMS's out of this until Blackboard has been shipped and then deal with them separately.

Expiring old rows

Since we add a new row to the new DB table every time a new student launches a new assignment, this table grows forever. We should delete old rows. Maybe just delete rows more than one year old? (Would require a date column on the table.)

Note that we cannot expire rows after they have been used because they might need to be used again. A teacher can submit more than one grade for one student/assignment (overwriting the previous grade).

[lyzadanger]

We should delete old rows. Maybe just delete rows more than one year old? (Would require a date column on the table.)

What should kick off this pruning process?

[lyzadanger]

We should only save grading-specific data into the DB for Blackboard launches. This grading data is not used for grading in Canvas. And we do not yet support grading in other LMS's.

We can identify Blackboard based on these launch params that it sends:

tool_consumer_info_product_family_code: "BlackboardLearn" (this is a standard LTI launch param)

Given that we're planning on extending this beyond Blackboard eventually, I wonder if it would be useful to store the product_family info in the LMS DB as well, for metrics/debug purposes? i.e. be able to tell quickly that student/assignment combinations are Blackboard-originated? Everything will be Blackboard for now, but later...perhaps this could be useful.