How a completed course module becomes a credit in the University of Helsinki study registry. Once the student has consented for the course and holds a student number proven to be theirs, one ledger row per completion is pushed to Sisu through Suotar and polled until the registry confirms the attainment.
One row per attempt at registering one completion. Every state write appends an event in the same transaction, so the ledger holds current state and the event table holds history.
| Name | Columns | Comment | Type |
|---|---|---|---|
| public.course_module_completions | 17 | Internal student completions for course modules. | BASE TABLE |
| public.credit_registration_events | 13 | Append-only audit trail for the credit registration ledger. There is no retention sweep here, so anything stored survives the 90-day suotar_api_calls window; every Suotar payload written to details is scrubbed at write time, but only best-effort — email addresses, student-number-shaped digit runs, access tokens and the known person fields are removed, while a personal name quoted in a free-text error message is deliberately left in place because the study registry holds it anyway. Treat this table as holding personal data, not as a guaranteed personal-data-free store. | BASE TABLE |
| public.credit_registrations | 43 | The credit registration ledger: one row per attempt at registering one course module completion into the study registry via Suotar. Every state write goes through credit_registrations::transition, which stamps state_entered_at and appends an event row in the same transaction; nothing else may update state. | BASE TABLE |
Nothing is submitted until the student has consented for the course and a student number has been proven to belong to their account. Both are global per account, not per module, and the token table is what turns an emailed link into a proof.
| Name | Columns | Comment | Type |
|---|---|---|---|
| public.course_credit_registration_consents | 10 | Per (user, course) consent to register completions into the study registry. One consent covers every module of the course. No row means never asked, which is what makes the course-start dialog appear; consent_given = false means asked and declined, which must not re-ask on every page load. | BASE TABLE |
| public.student_number_verification_tokens | 14 | One-click links mailed to the address Sisu holds for a person, to bind a student number to whichever courses.mooc.fi account opens the link while logged in. Deliberately not bound to an account at creation time: the two addresses routinely differ, which is the entire reason this flow exists. | BASE TABLE |
| public.verified_student_numbers | 17 | Student numbers proven to belong to a courses.mooc.fi account. Global per account: one live row per user, one per student number and one per Sisu person id. Relinking soft-deletes the old row and inserts a new one; student_number is never updated in place because the old value is audit-relevant. | BASE TABLE |
Account address ownership is proven by a code mailed to the account and typed back, so the proof lives on user_details and the code table holds nothing but the pending code. The student-number linking mails carry one-click links instead, and go to addresses Sisu holds which frequently have no account here.
| Name | Columns | Comment | Type |
|---|---|---|---|
| public.credit_registration_account_linking_emails | 11 | One row per account-linking mail we queued, keyed on the Sisu person id plus the recipient address. Prevents mailing the same Sisu person twice for the same course and backs the per-person rate caps. | BASE TABLE |
| public.email_deliveries | 14 | An email delivery table, which contains info about if an email has been sent to an user or if it failed | BASE TABLE |
| public.email_templates | 11 | An email template table, which contains the email subject and content written in the Gutenberg Editor. Template is general if course_instance_id is NULL, or specific to a course instance if course_instance_id is set. Supports adding exercise points/completions threshold templates for course instances. | BASE TABLE |
| public.user_details | 13 | Contains the details of user accounts such as their email, names, etc. It is not possible to soft delete these records – only hard deletes are supported in this table. | BASE TABLE |
| public.user_email_codes | 10 | Single-use codes emailed to a user to prove they can read their own mailbox before an action: the administrator login second factor, account deletion, and email address ownership verification. At most one live code per user per purpose; requesting a new one retires the old. | BASE TABLE |
The per-module opt-in and the Sisu-side identifiers it needs: the course unit realisations enrolment discovery polls, and the open university product access tokens that enrolment links are built from.
| Name | Columns | Comment | Type |
|---|---|---|---|
| public.course_module_suotar_realisations | 15 | Which Sisu course unit realisations a module maps to, one per term. Enrolment discovery polls the active ones. The last-run counters give teachers and admins feedback that the configuration is right without reading logs. | BASE TABLE |
| public.course_modules | 18 | A module is a collection of chapters which can be used to categorise the chapters of a course. | BASE TABLE |
| public.open_university_product_access_tokens | 13 | Access tokens used to build working Sisu enrolment links for open university products. The last good token is kept when a refresh fails on purpose: a link that works with a slightly stale token beats a broken page. | BASE TABLE |
What an operator reads and acts on: per-phase scheduling, heartbeats and pauses, the manual-action audit whose targets are often not registrations at all, the per-call Suotar log, and the daily queue depths the ledger cannot reconstruct after the fact.
| Name | Columns | Comment | Type |
|---|---|---|---|
| public.credit_registration_admin_actions | 16 | One row per manual action on the credit registration pipeline, written in the same transaction as the effect. Separate from credit_registration_events because this is a global, actor-ordered read with a before/after pair and targets that are not registrations at all: a phase, a course module, a student-number link. | BASE TABLE |
| public.credit_registration_daily_snapshots | 9 | Daily queue depth per ledger state. The ledger holds current state only, so a row that passed through a state in an hour leaves no depth trace and the dashboard trend charts have nothing to read. Aggregates only, deliberately: anything per-person belongs in the ledger. Roughly sixteen rows a day, so no retention policy is needed. | BASE TABLE |
| public.credit_registration_phase_state | 19 | One row per pipeline phase, not per worker process. Phases are what an operator reasons about, what the dashboard lists and what the system tests tick individually. Rows are seeded by migration and thereafter only ever updated. | BASE TABLE |
| public.suotar_api_calls | 18 | One row per HTTP call to Suotar, per batch rather than per item. Gives latency percentiles and error rate by endpoint without adding a metrics system. Retention is 90 days and the stored bodies are scrubbed; both, not either. | BASE TABLE |
The push path mirrors its registrations into the pre-existing pull-flow ledger under a registrar of its own, which keeps the existing teacher views and the pull API working while modules move over one at a time.
| Name | Columns | Comment | Type |
|---|---|---|---|
| public.course_module_completion_registered_to_study_registries | 10 | Completed course module completion registrations to study registries. | BASE TABLE |
| public.study_registry_registrars | 6 | Authorized third parties that can access course module completions for study registration purposes. | BASE TABLE |
| Name | Columns | Comment | Type |
|---|---|---|---|
| public.course_instances | 12 | Allows teachers to use a course multiple times with different points, submissions, and enrollments. | BASE TABLE |
| public.courses | 29 | Collection of course materials and exercises that define a online course. | BASE TABLE |
| public.users | 6 | Either students, teachers or staff. | BASE TABLE |
Generated by tbls