Course module completion tables / APIs for registering them to the study registry

[nygrenh]

Tables

Course module completion

export type CourseModuleCompletion = {
  completion_language: string
  course_id: string 
  course_module_id: string
  created_at: Date
  updated_at: Date
  eligible_for_ects: boolean
 // Email at the time of completing the course, this is used to match the student to the data that they will fill to the open university and it will remain unchanged in the event of email change because changing this would break the matching
  email: string
 // Can be a number or "passed" or "failed"
  grade: string
  id: string
  user_id: string
 // Not the same as created at. For example, if a course has an exam, this record would be created once the teachers manually grade the exam but the completion date should be set to the date of the exam. This distinction is desired for the study registry.
  completion_date: Date
}

completion_registration_attempt_date

CourseModuleCompletionRegisteredToStudyRegistry

export type CourseModuleCompletionRegisteredToStudyRegistry = {
  course_module_completion_id: string 
  course_id: string
  course_module_id: string
  created_at: Date
  id: string
  study_registry_registerer_id: string
  // Used by administrators and support staff to confirm the completion was registered to the correct student
  real_student_number: string
  updated_at: Date
  user_id: string
}

Study registry registerer

export type StudyRegistryRegisterer = {
  id: string
  // Used the authenticate the requests to the endpoints
  secret_key: string
  name: string
}

The authentication with the secret key works like this, just substitute Organization with StudyRegistryRegisterer: https://github.com/rage/mooc.fi/blob/7b8c450d1235a922910447ab43d42687b2870492/backend/util/server-functions.ts#L147-L180

Endpoints

• A new namespace for the endpoints, since these are used by other programs. Maybe something like /api/v0/study-registry

GET /api/v0/study-registry/completions/{:course_id | course_slug | uh_course_code} / GET /api/v0/study-registry/completions/{:course_id | course_slug | uh_course_code}/{:module_id}

• Streams a list of entries from the completions table
• Maybe joined with CourseModuleCompletionRegisteredToStudyRegistry?
• https://github.com/rage/mooc.fi/blob/7b8c450d1235a922910447ab43d42687b2870492/backend/api/completions.ts#L56-L68

POST /api/v0/study-registry/completion-registered-to-study-registry

interface RegisteredCompletion {
  completion_id: string
  student_number: string
  registration_date: string
}

https://github.com/rage/mooc.fi/blob/7b8c450d1235a922910447ab43d42687b2870492/backend/api/registerCompletions.ts#L15

[nygrenh]

Sisu grading format

Grades are registered to sisu with a combination of grade_scale id and grade localId. We are only interested in the grade scales sis-0-5 and sis-hyl-hyv. All ids can be found here: https://sis-helsinki-test.funidata.fi/api/graphql with the following query:

query {
  grade_scales {
    id
    name {
      fi
      en
      sv
    }
    grades {
      name {
        fi
        en
        sv
      }
      passed
      localId
      abbreviation {
        fi
        en
        sv
      }
    }
    abbreviation {
        fi
        en
        sv
      }

  }
}

Idea: We could save the grading scale and and the localId number as two different fields. Also, we prolly also want to know whether a certain combination is passed or not. A generated column for passed: boolean could work here: https://www.postgresql.org/docs/14/ddl-generated-columns.html

Information Suotari uses atm:

id: 054654065405 // Suorituksen id, tämä unohtu mainita, mutta käytetään itse asiassa myös suorituksen yksilöimiseen meillä
user_upstream_id: 307780 // käytetään erityisesti Elements of AI -kurssisuoritusten esitarkistukseen
email: matti.meikalainen@hotmail.it // tarvitaan, jotta voidaan yhdistää eduweb-ilmoittautuminen ja suoritus
student_number: 014235486 // Tämäki voidaan ottaa ihmisille joilla se on
completion_date: 2021-04-26T17:34:48.863Z // Opettajan järjestelmään kirjaama suorituspäivä (esim. Ohpen tenttipäivä)
completion_registration_attempt_date: 2021-04-26T17:34:48.863Z // opiskelijan itserekisteröimissä kursseissa se päivä, jolloin opiskelija painaa rekisteröimis-/ilmoittautumisnappulaa. Tärkeää että on nimenomaan se ilmoittautumispäivä, koska materiaaleissa on näin myös opiskelijoille ilmoitettu. 
completion_language: en_US // Käytetään kursseissa suorituskielen määrittämiseen. 
grade: null // Luonnollisesti arvosana on käytössä.
tier: 3 // Käytössä Building AI -kurssilla eri osien erotteluun, eli onko opiskelija suorittanut Intermediaten vai Advancedin 

[jolampi]

When sending information to Suotari, how should the grade field look like? It is a nullable string as far as I can tell, but how does one distinguish between passed grades and different grading scales? Or are we supposed to filter out any completions that were not passed?

[nygrenh]

We could pass a special struct to Suotari that contains one field for the sisu grading scale id and one field for the sisu grade localId