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
attainment_date	date		true			Frozen attainment date submitted.
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_checked_at	timestamp with time zone		true			When enrolments were last resolved for this row.
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_events public.credit_registrations		A unique, stable identifier for the record.
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.
registered_at	timestamp with time zone		true			When Sisu confirmed the attainment.
request_item_id	varchar(128)		false			The per-item id Suotar sees, generated once at row creation and stable for the row. Deterministic and greppable on both sides, so a Suotar log line maps to exactly one ledger row without an id allocation table.
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.
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_prerequisites’::credit_registration_state	false			What the pipeline does next with this row.
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 newer attempt that replaced this row. Set when a strictly better grade is resubmitted; the old row 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. 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
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_id_not_null	n	NOT NULL id
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_pkey	PRIMARY KEY	PRIMARY KEY (id)
credit_registrations_request_item_id_not_null	n	NOT NULL request_item_id
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)
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_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_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_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))
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 (state = ANY (ARRAY[‘submitting’::credit_registration_state, ‘submission_uncertain’::credit_registration_state, ‘awaiting_verification’::credit_registration_state, ‘registered’::credit_registration_state, ‘duplicate’::credit_registration_state, ‘not_improved’::credit_registration_state])))
uq_credit_registrations_request_item_id	CREATE UNIQUE INDEX uq_credit_registrations_request_item_id ON public.credit_registrations USING btree (request_item_id)
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))
uq_credit_registrations_submitted_attainment	CREATE UNIQUE INDEX uq_credit_registrations_submitted_attainment ON public.credit_registrations USING btree (submitted_attainment_id) WHERE ((submitted_attainment_id IS NOT NULL) AND (deleted_at IS NULL))