One row per account-linking mail we queued, keyed on the Sisu person id plus the recipient address. While its link can still be used, a row blocks another mail to the same person, course and address; replaced rows still count against the per-person rate caps.
| Name | Type | Default | Nullable | Children | Parents | Comment |
|---|---|---|---|---|---|---|
| course_id | uuid | false | public.courses | The course whose registration flow triggered the mail. | ||
| 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. | |||
| email_delivery_id | uuid | true | public.email_deliveries | The email_deliveries row, which carries our send status. We report send status, never delivery. | ||
| emailed_to | varchar(255) | false | The address the mail was queued to. | |||
| id | uuid | uuid_generate_v4() | false | A unique, stable identifier for the record. | ||
| replaced_at | timestamp with time zone | true | When this row gave up its place in the dedup key: a later mail to the same person, course and address took it after this row’s link had expired or been used, or the person’s student number was unlinked with the link unused. NULL while the row holds that place. | |||
| sent_at | timestamp with time zone | now() | false | When the mail was queued for sending. Not a delivery confirmation. | ||
| sisu_person_id | varchar(255) | false | Sisu person id the mail was about. The dedup and rate cap key, because at send time there is no account of ours to key on. | |||
| student_number | varchar(32) | false | The student number the mail was about, for support and audit. Not a key: it changes when a student moves between programmes. | |||
| student_number_verification_token_id | uuid | true | public.student_number_verification_tokens | The token whose link the mail carried. | ||
| 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. |
| 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 |
|---|---|---|
| credit_registration_account_l_student_number_verification__fkey | FOREIGN KEY | FOREIGN KEY (student_number_verification_token_id) REFERENCES student_number_verification_tokens(id) |
| credit_registration_account_linking_ema_sisu_person_id_not_null | n | NOT NULL sisu_person_id |
| credit_registration_account_linking_ema_student_number_not_null | n | NOT NULL student_number |
| credit_registration_account_linking_emai_email_delivery_id_fkey | FOREIGN KEY | FOREIGN KEY (email_delivery_id) REFERENCES email_deliveries(id) |
| credit_registration_account_linking_emails_course_id_fkey | FOREIGN KEY | FOREIGN KEY (course_id) REFERENCES courses(id) |
| credit_registration_account_linking_emails_course_id_not_null | n | NOT NULL course_id |
| credit_registration_account_linking_emails_created_at_not_null | n | NOT NULL created_at |
| credit_registration_account_linking_emails_emailed_to_not_null | n | NOT NULL emailed_to |
| credit_registration_account_linking_emails_id_not_null | n | NOT NULL id |
| credit_registration_account_linking_emails_pkey | PRIMARY KEY | PRIMARY KEY (id) |
| credit_registration_account_linking_emails_sent_at_not_null | n | NOT NULL sent_at |
| credit_registration_account_linking_emails_updated_at_not_null | n | NOT NULL updated_at |
| Name | Definition |
|---|---|
| credit_registration_account_linking_emails_pkey | CREATE UNIQUE INDEX credit_registration_account_linking_emails_pkey ON public.credit_registration_account_linking_emails USING btree (id) |
| idx_account_linking_emails_course | CREATE INDEX idx_account_linking_emails_course ON public.credit_registration_account_linking_emails USING btree (course_id, sent_at DESC) WHERE (deleted_at IS NULL) |
| idx_account_linking_emails_person_sent | CREATE INDEX idx_account_linking_emails_person_sent ON public.credit_registration_account_linking_emails USING btree (sisu_person_id, sent_at DESC) WHERE (deleted_at IS NULL) |
| idx_account_linking_emails_token | CREATE INDEX idx_account_linking_emails_token ON public.credit_registration_account_linking_emails USING btree (student_number_verification_token_id) WHERE (student_number_verification_token_id IS NOT NULL) |
| uq_account_linking_email_person_course_address | CREATE UNIQUE INDEX uq_account_linking_email_person_course_address ON public.credit_registration_account_linking_emails USING btree (sisu_person_id, course_id, lower((emailed_to)::text)) WHERE ((deleted_at IS NULL) AND (replaced_at IS NULL)) |
| Name | Definition |
|---|---|
| set_timestamp | CREATE TRIGGER set_timestamp BEFORE UPDATE ON public.credit_registration_account_linking_emails FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp() |
Generated by tbls