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.
| 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. | ||
| 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. | |
| 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. | |||
| 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. | |||
| 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. | ||
| 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’::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. |
| 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_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_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_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_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) DEFERRABLE INITIALLY DEFERRED |
| credit_registrations_superseded_by_not_self | CHECK | CHECK ((superseded_by_id <> id)) |
| 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 |
| 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_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_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)) |
| 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)) |
| Name | Definition |
|---|---|
| set_timestamp | CREATE TRIGGER set_timestamp BEFORE UPDATE ON public.credit_registrations FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp() |
Generated by tbls