One row per account-linking mail we queued, keyed on the Sisu person id plus the recipient address. Prevents mailing the same Sisu person twice for the same course and backs 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. | ||
| 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), deleted_at) NULLS NOT DISTINCT |
| 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