Index

public.credit_registrations

Description

The credit registration ledger: one row per attempt at registering one course module completion into the study registry via Suotar. Every state write goes through credit_registrations::transition, which stamps state_entered_at and appends an event row in the same transaction; nothing else may update state.

Columns

Name	Type	Default	Nullable	Children	Parents	Comment
action_needed_email_delivery_id	uuid		true		public.email_deliveries	The delivery carrying the “we could not register your credits, you have no enrolment” mail. Set once and never cleared: it is what stops the mail being sent again when the row re-enters no_usable_enrolment.
attained_at	timestamp with time zone		true			Frozen attainment time submitted. Its date in Helsinki is the attainment date Sisu records.
attainment_language	varchar(15)		true			Frozen attainment language submitted, in whatever form the client sends.
attempt_number	integer	1	false			Which attempt at this completion this row is. Grade improvements insert a new attempt rather than mutating the registered one, and the UIs render “attempt 2 of 3” from this.
course_id	uuid		false		public.courses	The course, denormalised from the completion so per-course reads need no join.
course_instance_id	uuid		false		public.course_instances	The instance the completion was earned on.
course_module_completion_id	uuid		false		public.course_module_completions	The completion being registered.
course_module_id	uuid		false		public.course_modules	The module, which is what carries the per-module configuration and pause.
created_at	timestamp with time zone	now()	false			Timestamp when the record was created.
credits	real		true			Frozen ECTS credits submitted. REAL to match course_modules.ects_credits.
deleted_at	timestamp with time zone		true			Timestamp when the record was deleted. If null, the record is not deleted.
enrolment_banner_dismissed_at	timestamp with time zone		true			When the student dismissed the persistent in-course-material re-enrol banner for this registration. Cleared on every fresh entry to no_usable_enrolment so a new problem is shown again.
enrolment_check_anchor_at	timestamp with time zone		true			What the group’s schedule offsets are counted from: the completion or the link, whichever is later, for completed; the latest restart for the other groups. NULL until the row first waits for an enrolment.
enrolment_check_claimed_until	timestamp with time zone		true			Set while resolve-enrolments has a lookup out for a row parked in no_usable_enrolment, which stays in that state for the call: no other lookup claims the row before this time. The answer clears it; a worker that died mid-call leaves it to expire.
enrolment_check_due_at	timestamp with time zone		true			The schedule time of enrolment_check_step, which next_attempt_at loses whenever a check is brought forward or waits out a failed lookup.
enrolment_check_group	enrolment_check_group	‘completed’::enrolment_check_group	false			Which enrolment check schedule the row follows. Kept for the row’s whole life, through every state, and inherited by a later attempt for the same student and module.
enrolment_check_requested_at	timestamp with time zone		true			When a student or teacher last asked for a check. With enrolment_checked_at, it is what the shared 30-minute limit on asking is counted from.
enrolment_check_restart_count	integer	0	false			How many check requests restarted the schedule in the current window. Past the daily cap a request still gets one check, without a restart.
enrolment_check_restart_window_started_at	timestamp with time zone		true			Start of the 24-hour window enrolment_check_restart_count counts in.
enrolment_check_source	enrolment_check_source	‘schedule’::enrolment_check_source	false			What made the next or running enrolment check due. Back to schedule once the check answers.
enrolment_check_step	integer		true			Zero-based index into the group’s schedule of the check the row waits for. Moves only when a check answers, never when one is claimed. NULL while no check is scheduled, including after the schedule has run out.
enrolment_checked_at	timestamp with time zone		true			When enrolments were last resolved for this row.
enrolment_checks_stopped_at	timestamp with time zone		true			When the schedule ran out without an enrolment. The row keeps waiting in no_usable_enrolment and nothing tells the student; a visit, a check request, a new completion or a roster listing can still wake it.
error_code	credit_registration_error_code		true			Why the row is where it is. NULL when nothing has gone wrong.
error_message	text		true			Human-readable detail for error_code, scrubbed of personal data before storage.
first_failed_at	timestamp with time zone		true			When the row first entered a failure state, the anchor of the retry window. Written only by credit_registrations::transition, so deferring a row cannot start the clock on one that has not failed.
grade_id	varchar(16)		true			Frozen Sisu grade id submitted, within grade_scale_id.
grade_scale_id	varchar(64)		true			Frozen Sisu grade scale id submitted.
id	uuid	uuid_generate_v4()	false	public.credit_registration_attention_dismissals public.credit_registration_enrolment_check_outcomes public.credit_registration_events public.credit_registrations		A unique, stable identifier for the record.
improvement_checked_completion_updated_at	timestamp with time zone		true			The completion’s updated_at as of the last time the grade-improvement scan judged this accepted attempt and found no improvement. A completion touched for any other reason keeps matching the scan’s cheap “changed since the attempt” pre-filter, so without this watermark those rows fill every capped batch and a real regrade further down the queue is never reached.
is_enrolment_check_batched	boolean	false	false			Whether the scheduled check is a slow one (a gap of a day or more): released only on a five-minute boundary, and pulled up to 15 minutes forward into a batch that is going out anyway.
last_attempt_at	timestamp with time zone		true			When the pipeline last acted on this row.
needs_admin_attention	boolean	false	false			Set when the pipeline has given up deciding and a human must look. Takes the row out of every stuck count without stopping the pipeline.
next_attempt_at	timestamp with time zone	now()	false			When the pipeline may next claim this row. Backoff, verify cadence and the states that wait on a human all write it; because claim_due orders by it under a LIMIT, a state that never defers starves the rest of its queue.
no_usable_enrolment_since	timestamp with time zone		true			When the row started waiting for an enrolment, kept through every check that finds none and through the ready_to_submit, resolving_enrolment and failed_retryable a first check or a retried lookup passes through. Cleared once the row stops waiting, for example when an enrolment is found.
not_registered_reimport_count	integer	0	false			How many times Suotar answered notRegistered for a submission of this row and it was sent back to import.
partially_registered_at	timestamp with time zone		true			When verify first saw only an assessment item attainment for the submission, which moved the row to partially_registered. Kept while the row polls on, so the wait for the course unit attainment is measured from the first sighting. Cleared on resubmission.
pending_superseded_by_id	uuid		true		public.credit_registrations	A later attempt, a regrade of the same completion or another of the student’s completions for the module, that is being sent to replace this registered row. Until the study registry holds it, this row stays the live credit and keeps its place in uq_credit_registrations_person_module. Becomes superseded_by_id when that attempt reaches registered or duplicate, and is cleared if it stops short.
phase_started_at	timestamp with time zone	now()	false			When the row last moved into a different timeline phase by a state change. All of pending counts as one phase here: the ledger does not record which precondition a pending row waits on.
registered_at	timestamp with time zone		true			When Sisu confirmed the attainment.
registered_email_delivery_id	uuid		true		public.email_deliveries	The delivery carrying the “your credits are in the study registry” mail. Set once and never cleared. A grade-improvement attempt is a separate row and gets its own mail, which is intended: the grade in the registry changed.
resubmit_not_before	timestamp with time zone		true			The retryAfter of Suotar’s last submissionPending answer: until then Suotar may still turn the pending submission into an attainment, so a second import could register the credits twice. Cleared when Suotar answers notRegistered.
seen_enrolment_ids	text[]		true			Every Sisu enrolment id the row’s checks have seen, plus those a roster listing has already woken it for. A listing naming any other id wakes the row. NULL until the first check, so any listing wakes a row never checked.
selected_enrolment_id	varchar(255)		true			The enrolment chosen by the selection policy and submitted.
selected_enrolment_kind	varchar(64)		true			Which kind of enrolment was chosen (degree or open university), for reporting on the selection policy.
selected_enrolment_realisation_id	varchar(255)		true			The course unit realisation the chosen enrolment belongs to.
selected_enrolment_realisation_name	jsonb		true			Localized name ({fi, sv, en}, each optional) of the course unit realisation the chosen enrolment belongs to, as Suotar reported it.
sisu_attainment_id	varchar(255)		true			The attainment id Sisu confirmed, learned from verify.
sisu_attainment_type	varchar(64)		true			The attainment type Sisu confirmed.
sisu_person_id	varchar(255)		true			Frozen snapshot of the Sisu person id submitted.
state	credit_registration_state	‘pending’::credit_registration_state	false			What the pipeline does next with this row.
state_changed_at	timestamp with time zone	now()	false			When the row last entered a different state. Unlike state_entered_at, a write that leaves the row in its state (every verify poll and enrolment check does) does not move it, so it measures how long the row has really been where it is.
state_entered_at	timestamp with time zone	now()	false			When this row entered the state it is in now. A deliberate denormalisation of the newest state_changed event: every stuck-item query the dashboard runs filters on it, and expressing that against the event table would mean a correlated subquery on the hottest admin query in the product.
student_number	varchar(32)		true			Frozen snapshot of the student number submitted. Filled while the enrolment is being checked and not changed afterwards, so a later regrade cannot silently alter a submitted row.
submit_retry_count	integer	0	false			How many times submission has been retried after a transient failure.
submitted_at	timestamp with time zone		true			When the import request was sent.
submitted_attainment_id	varchar(255)		true			The attainment id Suotar returned when it accepted the import. The only handle verify polling has.
submitted_attainment_type	varchar(64)		true			The attainment type Suotar returned alongside submitted_attainment_id.
superseded_at	timestamp with time zone		true			When this row was superseded by a newer attempt.
superseded_by_id	uuid		true		public.credit_registrations	The later attempt that replaced this row. A registered row is superseded only once the study registry holds its replacement (see pending_superseded_by_id), and keeps its state and terminal_at, because it really was registered.
terminal_at	timestamp with time zone		true			When the row reached a terminal state, and NULL whenever it is not in one: an admin retry out of a terminal state clears it. That is what lets terminal_at IS NULL serve as the non-terminal filter for the stuck detector and the pipeline funnel.
uh_course_code	varchar(255)		true			Frozen snapshot of the University of Helsinki course code submitted.
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 student whose completion this is.
verify_attempt_count	integer	0	false			How many verify polls have been made. Also part of the verify request item id.

Viewpoints

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.

Constraints

Name	Type	Definition
credit_registrations_action_needed_email_delivery_id_fkey	FOREIGN KEY	FOREIGN KEY (action_needed_email_delivery_id) REFERENCES email_deliveries(id)
credit_registrations_attempt_counts_nonnegative	CHECK	CHECK (((submit_retry_count >= 0) AND (verify_attempt_count >= 0) AND (attempt_number >= 1)))
credit_registrations_attempt_number_not_null	n	NOT NULL attempt_number
credit_registrations_course_id_fkey	FOREIGN KEY	FOREIGN KEY (course_id) REFERENCES courses(id)
credit_registrations_course_id_not_null	n	NOT NULL course_id
credit_registrations_course_instance_id_fkey	FOREIGN KEY	FOREIGN KEY (course_instance_id) REFERENCES course_instances(id)
credit_registrations_course_instance_id_not_null	n	NOT NULL course_instance_id
credit_registrations_course_module_completion_id_fkey	FOREIGN KEY	FOREIGN KEY (course_module_completion_id) REFERENCES course_module_completions(id)
credit_registrations_course_module_completion_id_not_null	n	NOT NULL course_module_completion_id
credit_registrations_course_module_id_fkey	FOREIGN KEY	FOREIGN KEY (course_module_id) REFERENCES course_modules(id)
credit_registrations_course_module_id_not_null	n	NOT NULL course_module_id
credit_registrations_created_at_not_null	n	NOT NULL created_at
credit_registrations_enrolment_check_claimed	CHECK	CHECK (((enrolment_check_claimed_until IS NULL) OR (state = ‘no_usable_enrolment’::credit_registration_state)))
credit_registrations_enrolment_check_group_not_null	n	NOT NULL enrolment_check_group
credit_registrations_enrolment_check_restart_count	CHECK	CHECK ((enrolment_check_restart_count >= 0))
credit_registrations_enrolment_check_restart_count_not_null	n	NOT NULL enrolment_check_restart_count
credit_registrations_enrolment_check_source_not_null	n	NOT NULL enrolment_check_source
credit_registrations_enrolment_check_step	CHECK	CHECK ((((enrolment_check_step IS NULL) = (enrolment_check_due_at IS NULL)) AND ((enrolment_check_step IS NULL) OR ((enrolment_check_step >= 0) AND (enrolment_check_anchor_at IS NOT NULL)))))
credit_registrations_enrolment_checks_stopped	CHECK	CHECK (((enrolment_checks_stopped_at IS NULL) OR ((enrolment_check_step IS NULL) AND (enrolment_check_anchor_at IS NOT NULL))))
credit_registrations_id_not_null	n	NOT NULL id
credit_registrations_is_enrolment_check_batched_not_null	n	NOT NULL is_enrolment_check_batched
credit_registrations_needs_admin_attention_not_null	n	NOT NULL needs_admin_attention
credit_registrations_next_attempt_at_not_null	n	NOT NULL next_attempt_at
credit_registrations_not_registered_reimport_count_not_null	n	NOT NULL not_registered_reimport_count
credit_registrations_pending_superseded_by_id_fkey	FOREIGN KEY	FOREIGN KEY (pending_superseded_by_id) REFERENCES credit_registrations(id)
credit_registrations_pending_superseded_by_not_self	CHECK	CHECK ((pending_superseded_by_id <> id))
credit_registrations_phase_started_at_not_null	n	NOT NULL phase_started_at
credit_registrations_pkey	PRIMARY KEY	PRIMARY KEY (id)
credit_registrations_registered_at_set	CHECK	CHECK (((state <> ‘registered’::credit_registration_state) OR (registered_at IS NOT NULL)))
credit_registrations_registered_email_delivery_id_fkey	FOREIGN KEY	FOREIGN KEY (registered_email_delivery_id) REFERENCES email_deliveries(id)
credit_registrations_reimport_count_nonnegative	CHECK	CHECK ((not_registered_reimport_count >= 0))
credit_registrations_state_changed_at_not_null	n	NOT NULL state_changed_at
credit_registrations_state_entered_at_not_null	n	NOT NULL state_entered_at
credit_registrations_state_not_null	n	NOT NULL state
credit_registrations_submit_retry_count_not_null	n	NOT NULL submit_retry_count
credit_registrations_superseded_by_id_fkey	FOREIGN KEY	FOREIGN KEY (superseded_by_id) REFERENCES credit_registrations(id) DEFERRABLE INITIALLY DEFERRED
credit_registrations_superseded_by_not_self	CHECK	CHECK ((superseded_by_id <> id))
credit_registrations_superseded_or_pending	CHECK	CHECK (((superseded_by_id IS NULL) OR (pending_superseded_by_id IS NULL)))
credit_registrations_superseded_pair	CHECK	CHECK (((superseded_by_id IS NULL) = (superseded_at IS NULL)))
credit_registrations_updated_at_not_null	n	NOT NULL updated_at
credit_registrations_user_id_fkey	FOREIGN KEY	FOREIGN KEY (user_id) REFERENCES users(id)
credit_registrations_user_id_not_null	n	NOT NULL user_id
credit_registrations_verify_attempt_count_not_null	n	NOT NULL verify_attempt_count

Indexes

Triggers

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

Relations

Generated by tbls

Name	Definition
credit_registrations_pkey	CREATE UNIQUE INDEX credit_registrations_pkey ON public.credit_registrations USING btree (id)
idx_credit_registrations_admin_attention	CREATE INDEX idx_credit_registrations_admin_attention ON public.credit_registrations USING btree (updated_at DESC) WHERE (needs_admin_attention AND (deleted_at IS NULL))
idx_credit_registrations_batched_enrolment_checks	CREATE INDEX idx_credit_registrations_batched_enrolment_checks ON public.credit_registrations USING btree (enrolment_check_due_at) WHERE ((state = ‘no_usable_enrolment’::credit_registration_state) AND is_enrolment_check_batched AND (deleted_at IS NULL))
idx_credit_registrations_course_module	CREATE INDEX idx_credit_registrations_course_module ON public.credit_registrations USING btree (course_module_id, state) WHERE (deleted_at IS NULL)
idx_credit_registrations_course_state	CREATE INDEX idx_credit_registrations_course_state ON public.credit_registrations USING btree (course_id, state) WHERE (deleted_at IS NULL)
idx_credit_registrations_due	CREATE INDEX idx_credit_registrations_due ON public.credit_registrations USING btree (state, next_attempt_at) WHERE (deleted_at IS NULL)
idx_credit_registrations_module_created	CREATE INDEX idx_credit_registrations_module_created ON public.credit_registrations USING btree (course_module_id, created_at DESC) WHERE (deleted_at IS NULL)
idx_credit_registrations_module_error_code	CREATE INDEX idx_credit_registrations_module_error_code ON public.credit_registrations USING btree (course_module_id, error_code) WHERE ((error_code IS NOT NULL) AND (deleted_at IS NULL))
idx_credit_registrations_pending_superseded_by	CREATE INDEX idx_credit_registrations_pending_superseded_by ON public.credit_registrations USING btree (pending_superseded_by_id) WHERE (pending_superseded_by_id IS NOT NULL)
idx_credit_registrations_state_entered	CREATE INDEX idx_credit_registrations_state_entered ON public.credit_registrations USING btree (state, state_entered_at) WHERE ((terminal_at IS NULL) AND (deleted_at IS NULL))
idx_credit_registrations_superseded_by	CREATE INDEX idx_credit_registrations_superseded_by ON public.credit_registrations USING btree (superseded_by_id) WHERE (superseded_by_id IS NOT NULL)
idx_credit_registrations_terminal_at	CREATE INDEX idx_credit_registrations_terminal_at ON public.credit_registrations USING btree (terminal_at) WHERE (deleted_at IS NULL)
idx_credit_registrations_unnotified	CREATE INDEX idx_credit_registrations_unnotified ON public.credit_registrations USING btree (state_entered_at) WHERE ((deleted_at IS NULL) AND (((state = ‘no_usable_enrolment’::credit_registration_state) AND (action_needed_email_delivery_id IS NULL)) OR ((state = ANY (ARRAY[‘registered’::credit_registration_state, ‘duplicate’::credit_registration_state, ‘not_improved’::credit_registration_state])) AND (registered_email_delivery_id IS NULL))))
idx_credit_registrations_user	CREATE INDEX idx_credit_registrations_user ON public.credit_registrations USING btree (user_id, created_at DESC) WHERE (deleted_at IS NULL)
uq_credit_registrations_completion	CREATE UNIQUE INDEX uq_credit_registrations_completion ON public.credit_registrations USING btree (course_module_completion_id) WHERE ((deleted_at IS NULL) AND (superseded_by_id IS NULL) AND (state <> ALL (ARRAY[‘registered’::credit_registration_state, ‘duplicate’::credit_registration_state, ‘not_improved’::credit_registration_state])))
uq_credit_registrations_completion_attempt	CREATE UNIQUE INDEX uq_credit_registrations_completion_attempt ON public.credit_registrations USING btree (course_module_completion_id, attempt_number) WHERE (deleted_at IS NULL)
uq_credit_registrations_person_module	CREATE UNIQUE INDEX uq_credit_registrations_person_module ON public.credit_registrations USING btree (sisu_person_id, course_module_id) WHERE ((sisu_person_id IS NOT NULL) AND (deleted_at IS NULL) AND (superseded_by_id IS NULL) AND (pending_superseded_by_id IS NULL) AND (state = ANY (ARRAY[‘submitting’::credit_registration_state, ‘submission_uncertain’::credit_registration_state, ‘awaiting_verification’::credit_registration_state, ‘partially_registered’::credit_registration_state, ‘registered’::credit_registration_state, ‘duplicate’::credit_registration_state, ‘not_improved’::credit_registration_state])))
uq_credit_registrations_sisu_attainment	CREATE UNIQUE INDEX uq_credit_registrations_sisu_attainment ON public.credit_registrations USING btree (sisu_attainment_id) WHERE ((sisu_attainment_id IS NOT NULL) AND (deleted_at IS NULL))