Index

public.verified_student_numbers

Description

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.

Columns

Name	Type	Default	Nullable	Parents	Comment
account_email_verified_at	timestamp with time zone		true		The account email verification timestamp as it stood at link time, frozen here on purpose: user_details.email_verified_at is cleared on the next address change, and an audit years later must still be able to answer how old the proof was.
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		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)		false		Sisu person id reported alongside the student number. Stable across student number changes, live-unique, and the identity the double-registration guards key on.
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 proof rests on: the address the link was mailed to, or the matched address for the fast track. NULL exactly for admin_manual rows.
verified_via_email_match_field	varchar(16)		true		Which Sisu address field matched for email_match_fast_track rows: primary (secondary is reserved and not currently accepted). NULL for other methods.

Viewpoints

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.

Constraints

Name	Type	Definition
student_number_format	CHECK	CHECK (((student_number)::text ~ ‘¹{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_match_field_method	CHECK	CHECK (((verified_via = ‘email_match_fast_track’::student_number_verification_method) OR (verified_via_email_match_field IS NULL)))
verified_student_numbers_pkey	PRIMARY KEY	PRIMARY KEY (id)
verified_student_numbers_proof_address	CHECK	CHECK (((verified_via = ‘admin_manual’::student_number_verification_method) = (verified_via_email IS NULL)))
verified_student_numbers_sisu_person_id_not_null	n	NOT NULL sisu_person_id
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

Indexes

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)

Triggers

Name	Definition
set_timestamp	CREATE TRIGGER set_timestamp BEFORE UPDATE ON public.verified_student_numbers FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp()

Relations

Generated by tbls

0-9↩︎