Index

public.email_deliveries

Description

An email delivery table, which contains info about if an email has been sent to an user or if it failed

Columns

Name Type Default Nullable Children Parents Comment
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_template_id uuid false public.email_templates The email template that is being sent in this email delivery.
first_failed_at timestamp with time zone true Timestamp of the first failed delivery attempt for this email, used as the retry window anchor.
id uuid uuid_generate_v4() false public.credit_registration_account_linking_emails public.email_delivery_errors A unique, stable identifier for the record.
last_attempt_at timestamp with time zone true Timestamp of the most recent delivery attempt for this email.
next_retry_at timestamp with time zone true Next scheduled time when this email delivery will be retried, or NULL when no retry is scheduled.
placeholders jsonb true Placeholder bag substituted into the template body at send time. Used when the recipient has no account, so the sender needs no user lookup. NULL means the template derives its placeholders from user_id.
recipient_email varchar(255) true Explicit recipient address, used when the email is not addressed to a known user. Never set together with user_id, and nulled by retention once the delivery is sent or retired, so an old row holds no address.
retry_count integer 0 false Number of failed delivery attempts recorded so far for this email.
retryable boolean true false Indicates whether this email delivery is still eligible for further retry attempts.
sent boolean false false Whether the email was sent or not. If false, a background daemon will try to send this email message.
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 true public.users The user to whom the email should be sent. NULL for emails addressed to a raw external address (see recipient_email), e.g. student-number linking mails sent to the address Sisu holds for a person who may not have an account here. Never set together with recipient_email.

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
email_deliveries_created_at_not_null n NOT NULL created_at
email_deliveries_email_template_id_not_null n NOT NULL email_template_id
email_deliveries_has_exactly_one_recipient CHECK CHECK (((NOT ((user_id IS NOT NULL) AND (recipient_email IS NOT NULL))) AND (sent OR (deleted_at IS NOT NULL) OR (user_id IS NOT NULL) OR (recipient_email IS NOT NULL)))) NOT VALID
email_deliveries_id_not_null n NOT NULL id
email_deliveries_pkey PRIMARY KEY PRIMARY KEY (id)
email_deliveries_retry_count_not_null n NOT NULL retry_count
email_deliveries_retryable_not_null n NOT NULL retryable
email_deliveries_sent_not_null n NOT NULL sent
email_deliveries_updated_at_not_null n NOT NULL updated_at
email_deliveries_user_id_fkey FOREIGN KEY FOREIGN KEY (user_id) REFERENCES users(id)
email_template_fk FOREIGN KEY FOREIGN KEY (email_template_id) REFERENCES email_templates(id)

Indexes

Name Definition
email_deliveries_pkey CREATE UNIQUE INDEX email_deliveries_pkey ON public.email_deliveries USING btree (id)
email_deliveries_retry_queue_idx CREATE INDEX email_deliveries_retry_queue_idx ON public.email_deliveries USING btree (next_retry_at) WHERE ((sent = false) AND (retryable = true) AND (deleted_at IS NULL))

Triggers

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

Relations

er

Generated by tbls