Section sync may delete observations

[xalbd]

Is this an issue and how should we address it?

[jaydent4]

this might be an email the user to notify them to fix it type of thing

[xalbd]

im gonna do this rq

[xalbd]

new issue: if someone does an observation and then the section gets deleted on delete cascade deletes their observation so it's cooked for future records

[xalbd]

This should be done before Monday because that's when the blast radius gets bad. The observation system is absolutely killing me right now

I have realized that we need to be able to handle section changes (effectively, drops) at any point in the cycle:

• before an observation is signed up for that section
  • this is easy: drop all availability and change the section. 0 blast radius
• after an observation is signed up but not done
  • we could simply email the observer and drop their observation. do we want to drop the observation from records? i think it would be better to note that it was cancelled due to a drop
• after an observation is signed up and done
  • we need to keep this for future records: i.e. when auditing for grades the observation was actually done.

Currently, we handle the first one well, the second one poorly, and the third one not at all.

Proposed Solution:
Add a soft delete for section assignments: mark with a "dropped_at" column that defaults to NULL

• filter queries on NULL almost always, dropped_at exists to allows us to not break fkey relationship with observations table

Add a lifecycle marker for observations: scheduled, completed, cancelled

• mark any observations in the future that are "scheduled" as "cancelled" if a section is dropped, email and notify at this point for an LA to switch
• bonus: allows us to mark observations as completed when feedback comes in for query simplicity

Drop database cascades

• more trouble than they are worth. Sorry Jayden because I low key pushed for cascades last week

As a bonus, though, this should provide us an easier route to work with LA withdrawals.

I forsee us needing to edit the section-assignments cron job and the withdraws cron job primarily; any queries regarding section assignments and availability will need to be slightly updated as well, although those should be easily Claudable. I also think this should generally be Claudable with this level of spec but especially with another complex migration should be tested locally and rolled out carefully.

[jaydent4]

Another proposal, but does require more storage:

1. Do the email thing for case 2

2. New table that records completed observations

• On observation feedback form submission, push a new entry that marks the completion of an observation
• Estimate roughly ~1200 entries
• Store actual identifiable information (i.e. observer/observee pair, section info)

Pros: might make the query to get all observation info more efficient (basically only requires a return on the table), completely separates logic of deleting sections/section changes from the observations and we can continue storing observation records for future use
Cons: more storage for another table (might be negligible, not too sure what the storage costs are), could be more bloat than necessary since the first approach does not add extra storage usage on observation feedback.

[xalbd]

Let's do this for round 2. We're robust enough already for round 1 IMO

[jaydent4]

should we start doing this now