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.
| Name | Type | Default | Nullable | Children | Parents | Comment |
|---|---|---|---|---|---|---|
| created_at | timestamp with time zone | now() | false | Timestamp when the record was created. | ||
| deleted_at | timestamp with time zone | true | Timestamp when the record was deleted. If null, the record is not deleted. | |||
| first_names | varchar(255) | true | First names as Sisu reports them, shown on the link confirmation page and in support views. | |||
| id | uuid | uuid_generate_v4() | false | public.study_registry_student_number_conflicts | A unique, stable identifier for the record. | |
| last_name | varchar(255) | true | Last name as Sisu reports it, shown on the link confirmation page and in support views. | |||
| link_reason | text | true | The reason the acting admin typed for an admin_manual link. NULL for other methods. | |||
| linked_by_user_id | uuid | true | public.users | The admin who established an admin_manual link. NULL for other methods. Duplicates the audit log deliberately: “on what authority does this account hold this number” must be answerable from the row itself. | ||
| sisu_person_id | varchar(255) | true | Sisu person id reported alongside the student number. Stable across student number changes, live-unique, and the identity the double-registration guards key on. NULL only for study_registry rows, whose registrar reports a number and no person; nothing we send to Suotar needs it. | |||
| student_number | varchar(32) | false | The University of Helsinki student number, normalised (whitespace stripped, leading zeros preserved). | |||
| updated_at | timestamp with time zone | now() | false | Timestamp when the record was last updated. The field is updated automatically by the set_timestamp trigger. | ||
| user_id | uuid | false | public.users | The account that holds this student number. | ||
| verified_at | timestamp with time zone | now() | false | When the link was established. | ||
| verified_from_course_id | uuid | true | public.courses | The course whose registration flow produced this link, for support context. NULL when not attributable to one course. | ||
| verified_via | student_number_verification_method | ‘emailed_link’::student_number_verification_method | false | Which proof established the link. | ||
| verified_via_email | varchar(255) | true | The Sisu-held address the linking mail was sent to. NULL exactly for admin_manual and study_registry rows. |
| Name | Definition |
|---|---|
| Credit registration | How a completed course module becomes a credit in the University of Helsinki study registry. Enrolment on the course is itself the student’s consent to register the credit; once a student number is proven to be theirs, one ledger row per completion is pushed to Sisu through Suotar and polled until the registry confirms the attainment. |
| Name | Type | Definition |
|---|---|---|
| student_number_format | CHECK | CHECK (((student_number)::text ~ ‘1{6,12}$’::text)) |
| verified_student_numbers_admin_linker | CHECK | CHECK (((verified_via = ‘admin_manual’::student_number_verification_method) = (linked_by_user_id IS NOT NULL))) |
| verified_student_numbers_created_at_not_null | n | NOT NULL created_at |
| verified_student_numbers_id_not_null | n | NOT NULL id |
| verified_student_numbers_link_reason | CHECK | CHECK (((verified_via = ‘admin_manual’::student_number_verification_method) OR (link_reason IS NULL))) |
| verified_student_numbers_linked_by_user_id_fkey | FOREIGN KEY | FOREIGN KEY (linked_by_user_id) REFERENCES users(id) |
| verified_student_numbers_person_id | CHECK | CHECK (((verified_via = ‘study_registry’::student_number_verification_method) OR (sisu_person_id IS NOT NULL))) |
| verified_student_numbers_pkey | PRIMARY KEY | PRIMARY KEY (id) |
| verified_student_numbers_proof_address | CHECK | CHECK (((verified_via = ANY (ARRAY[‘admin_manual’::student_number_verification_method, ‘study_registry’::student_number_verification_method])) = (verified_via_email IS NULL))) |
| verified_student_numbers_student_number_not_null | n | NOT NULL student_number |
| verified_student_numbers_updated_at_not_null | n | NOT NULL updated_at |
| verified_student_numbers_user_id_fkey | FOREIGN KEY | FOREIGN KEY (user_id) REFERENCES users(id) |
| verified_student_numbers_user_id_not_null | n | NOT NULL user_id |
| verified_student_numbers_verified_at_not_null | n | NOT NULL verified_at |
| verified_student_numbers_verified_from_course_id_fkey | FOREIGN KEY | FOREIGN KEY (verified_from_course_id) REFERENCES courses(id) |
| verified_student_numbers_verified_via_not_null | n | NOT NULL verified_via |
| Name | Definition |
|---|---|
| uq_verified_student_numbers_number | CREATE UNIQUE INDEX uq_verified_student_numbers_number ON public.verified_student_numbers USING btree (student_number) WHERE (deleted_at IS NULL) |
| uq_verified_student_numbers_person | CREATE UNIQUE INDEX uq_verified_student_numbers_person ON public.verified_student_numbers USING btree (sisu_person_id) WHERE (deleted_at IS NULL) |
| uq_verified_student_numbers_user | CREATE UNIQUE INDEX uq_verified_student_numbers_user ON public.verified_student_numbers USING btree (user_id) WHERE (deleted_at IS NULL) |
| verified_student_numbers_pkey | CREATE UNIQUE INDEX verified_student_numbers_pkey ON public.verified_student_numbers USING btree (id) |
| Name | Definition |
|---|---|
| set_timestamp | CREATE TRIGGER set_timestamp BEFORE UPDATE ON public.verified_student_numbers FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp() |
Generated by tbls
0-9↩︎