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.
auto_link_notice_dismissed_at	timestamp with time zone		true		When the student dismissed the notice telling them this link was made automatically. Only ever set for verified_via = email_match_fast_track; the notice and its one-click unlink are the compensating control for linking without asking.
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. 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.

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↩︎