Index

public.user_details

Description

Contains the details of user accounts such as their email, names, etc. It is not possible to soft delete these records – only hard deletes are supported in this table.

Columns

Name Type Default Nullable Extra Definition Children Parents Comment
country varchar(255) true The country that the user has provided to the system.
created_at timestamp with time zone now() false Timestamp when the record was created.
email varchar(255) false User’s email
email_communication_consent boolean false false Whether user has given consent to receive emails related to MOOC.fi courses and the development of the learning environment
email_search_helper text true GENERATED ALWAYS AS lower((COALESCE(email, ’’::character varying))::text) STORED Generated helper column for typo-resistant full email search in lowercase.
email_verified_at timestamp with time zone true When the user last proved control of the address currently in email. NULL means unproven. Automatically reset to NULL by the clear_email_verification trigger whenever email changes, so a non-NULL value always refers to the current address. Never set this without a proof of mailbox control.
email_verified_method email_verification_method true How email_verified_at was obtained. The credit-registration email-match fast track accepts emailed_code, password_reset_backfill and tmc_confirmed; admin_asserted rests on no mailbox proof and is refused.
first_name varchar(255) true The first name that the user has provided to the system.
last_name varchar(255) true The last name that the user has provided to the system.
name_search_helper text true GENERATED ALWAYS AS lower(btrim((((((((COALESCE(first_name, ’‘::character varying))::text ||’ ‘::text) || (COALESCE(last_name,’‘::character varying))::text) ||’ ‘::text) || (COALESCE(last_name,’‘::character varying))::text) ||’ ‘::text) || (COALESCE(first_name,’’::character varying))::text))) STORED Generated helper column for typo-resistant user name search. Contains first-name last-name and last-name first-name in lowercase.
search_helper text true GENERATED ALWAYS AS lower(replace((((((((((((user_id)::text || ’ ‘::text) || (COALESCE(first_name,’‘::character varying))::text) ||’ ‘::text) || (COALESCE(last_name,’‘::character varying))::text) ||’ ‘::text) || (COALESCE(last_name,’‘::character varying))::text) ||’ ‘::text) || (COALESCE(first_name,’‘::character varying))::text) ||’ ‘::text) || (COALESCE(email,’‘::character varying))::text),’ ‘::text,’ ’::text)) STORED Helps us to search users with one trigram-indexed column. It contains user id, email, first-name last-name, and last-name first-name; both names are intentionally repeated so partial name searches work in either order.
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 user this record belongs to.

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
user_details_created_at_not_null n NOT NULL created_at
user_details_email_check CHECK CHECK (((email)::text ~~ ‘%@%’::text))
user_details_email_communication_consent_not_null n NOT NULL email_communication_consent
user_details_email_not_null n NOT NULL email
user_details_email_verification_consistent CHECK CHECK (((email_verified_at IS NULL) = (email_verified_method IS NULL))) NOT VALID
user_details_first_name_check CHECK CHECK ((TRIM(BOTH FROM first_name) <> ’’::text))
user_details_last_name_check CHECK CHECK ((TRIM(BOTH FROM last_name) <> ’’::text))
user_details_pkey PRIMARY KEY PRIMARY KEY (user_id)
user_details_updated_at_not_null n NOT NULL updated_at
user_details_user_id_fkey FOREIGN KEY FOREIGN KEY (user_id) REFERENCES users(id)
user_details_user_id_not_null n NOT NULL user_id

Indexes

Name Definition
user_details_email_search_helper_gist CREATE INDEX user_details_email_search_helper_gist ON public.user_details USING gist (email_search_helper gist_trgm_ops)
user_details_email_trgm_idx CREATE INDEX user_details_email_trgm_idx ON public.user_details USING gist (lower((email)::text) gist_trgm_ops)
user_details_name_search_helper_gist CREATE INDEX user_details_name_search_helper_gist ON public.user_details USING gist (name_search_helper gist_trgm_ops)
user_details_pkey CREATE UNIQUE INDEX user_details_pkey ON public.user_details USING btree (user_id)
users_email CREATE UNIQUE INDEX users_email ON public.user_details USING btree (lower((email)::text))

Triggers

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

Relations

er

Generated by tbls