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.
| Name | Type | Default | Nullable | Children | Parents | Comment |
|---|---|---|---|---|---|---|
| attempt_count | integer | 0 | false | Wrong guesses recorded against this code. The checking handler retires the code once its own limit is reached, which is what stops a six digit code from being brute forced. | ||
| code | varchar(16) | false | The single-use code sent to the user. | |||
| created_at | timestamp with time zone | now() | false | Time when the code was created. | ||
| deleted_at | timestamp with time zone | true | Timestamp when the record was deleted. If null, the record is not deleted. | |||
| expires_at | timestamp with time zone | (now() + ‘01:00:00’::interval) | false | Time after which the code becomes invalid. | ||
| id | uuid | gen_random_uuid() | false | A unique identifier for this code record | ||
| purpose | user_email_code_purpose | false | Which action this code authorises. | |||
| updated_at | timestamp with time zone | now() | false | Time when the code was last updated. Automatically set by trigger. | ||
| used_at | timestamp with time zone | true | Time when the code was used. Null if unused. | |||
| user_id | uuid | false | public.users | References the user the code belongs to. |
| 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 |
|---|---|---|
| user_email_codes_attempt_count_not_null | n | NOT NULL attempt_count |
| user_email_codes_code_not_null | n | NOT NULL code |
| user_email_codes_created_at_not_null | n | NOT NULL created_at |
| user_email_codes_expires_at_not_null | n | NOT NULL expires_at |
| user_email_codes_id_not_null | n | NOT NULL id |
| user_email_codes_pkey | PRIMARY KEY | PRIMARY KEY (id) |
| user_email_codes_purpose_not_null | n | NOT NULL purpose |
| user_email_codes_updated_at_not_null | n | NOT NULL updated_at |
| user_email_codes_user_id_fkey | FOREIGN KEY | FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE |
| user_email_codes_user_id_not_null | n | NOT NULL user_id |
| Name | Definition |
|---|---|
| unique_active_user_email_codes_user | CREATE UNIQUE INDEX unique_active_user_email_codes_user ON public.user_email_codes USING btree (user_id, purpose) WHERE ((deleted_at IS NULL) AND (used_at IS NULL)) |
| user_email_codes_pkey | CREATE UNIQUE INDEX user_email_codes_pkey ON public.user_email_codes USING btree (id) |
| Name | Definition |
|---|---|
| set_timestamp | CREATE TRIGGER set_timestamp BEFORE UPDATE ON public.user_email_codes FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp() |
Generated by tbls