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.
| Name | Type | Default | Nullable | Children | Parents | Comment |
|---|---|---|---|---|---|---|
| claimed_by_user_id | uuid | true | public.users | The account that opened the link, written together with used_at. NULL until claimed. | ||
| course_id | uuid | true | public.courses | The course whose registration flow produced this token, for the mail copy and support context. | ||
| 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. | |||
| emailed_to | varchar(255) | false | The address the link was mailed to. | |||
| expires_at | timestamp with time zone | (now() + ‘14 days’::interval) | false | When the link stops working. Default 14 days: this link arrives in an email the student did not ask for and may read a week later. | ||
| first_names | varchar(255) | true | First names as Sisu reports them, shown on the confirmation page so the recipient can see what they are linking. | |||
| id | uuid | uuid_generate_v4() | false | public.credit_registration_account_linking_emails | A unique, stable identifier for the record. | |
| last_name | varchar(255) | true | Last name as Sisu reports it, shown on the confirmation page. | |||
| sisu_person_id | varchar(255) | false | Sisu person id this token identifies. Before the claim, the token identifies a Sisu person rather than one of our accounts. | |||
| student_number | varchar(32) | false | The student number this token would link. | |||
| token | varchar(255) | false | Long random string (at least 128 characters) that is the only proof of ownership. Not scoped to an account, so entropy matters more here than in the admin login flow. | |||
| 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. | ||
| used_at | timestamp with time zone | true | When the link was opened and the link established. Null if unused. |
| Name | Definition |
|---|---|
| Credit registration | 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. |
| Name | Type | Definition |
|---|---|---|
| student_number_verification_token_length | CHECK | CHECK ((length((token)::text) >= 128)) |
| student_number_verification_tokens_claimed_by_user_id_fkey | FOREIGN KEY | FOREIGN KEY (claimed_by_user_id) REFERENCES users(id) |
| student_number_verification_tokens_course_id_fkey | FOREIGN KEY | FOREIGN KEY (course_id) REFERENCES courses(id) |
| student_number_verification_tokens_created_at_not_null | n | NOT NULL created_at |
| student_number_verification_tokens_emailed_to_not_null | n | NOT NULL emailed_to |
| student_number_verification_tokens_expires_at_not_null | n | NOT NULL expires_at |
| student_number_verification_tokens_id_not_null | n | NOT NULL id |
| student_number_verification_tokens_pkey | PRIMARY KEY | PRIMARY KEY (id) |
| student_number_verification_tokens_sisu_person_id_not_null | n | NOT NULL sisu_person_id |
| student_number_verification_tokens_student_number_not_null | n | NOT NULL student_number |
| student_number_verification_tokens_token_not_null | n | NOT NULL token |
| student_number_verification_tokens_updated_at_not_null | n | NOT NULL updated_at |
| Name | Definition |
|---|---|
| idx_student_number_verification_tokens_claimed_by | CREATE INDEX idx_student_number_verification_tokens_claimed_by ON public.student_number_verification_tokens USING btree (claimed_by_user_id, created_at DESC) WHERE ((claimed_by_user_id IS NOT NULL) AND (deleted_at IS NULL)) |
| idx_student_number_verification_tokens_expires | CREATE INDEX idx_student_number_verification_tokens_expires ON public.student_number_verification_tokens USING btree (expires_at) WHERE ((used_at IS NULL) AND (deleted_at IS NULL)) |
| idx_student_number_verification_tokens_number | CREATE INDEX idx_student_number_verification_tokens_number ON public.student_number_verification_tokens USING btree (student_number, created_at DESC) WHERE (deleted_at IS NULL) |
| student_number_verification_tokens_pkey | CREATE UNIQUE INDEX student_number_verification_tokens_pkey ON public.student_number_verification_tokens USING btree (id) |
| uq_student_number_verification_token | CREATE UNIQUE INDEX uq_student_number_verification_token ON public.student_number_verification_tokens USING btree (token, deleted_at) NULLS NOT DISTINCT WHERE (used_at IS NULL) |
| Name | Definition |
|---|---|
| set_timestamp | CREATE TRIGGER set_timestamp BEFORE UPDATE ON public.student_number_verification_tokens FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp() |
Generated by tbls