headless_lms_models/
course_module_suotar_realisations.rs

1use utoipa::ToSchema;
2
3use crate::course_modules::CourseModuleSuotarRealisationEdit;
4use crate::credit_registrations::CreditRegistrationErrorCode;
5use crate::prelude::*;
6
7#[derive(Debug, Serialize, Deserialize, PartialEq, Clone, ToSchema)]
8pub struct CourseModuleSuotarRealisation {
9    pub id: Uuid,
10    pub created_at: DateTime<Utc>,
11    pub updated_at: DateTime<Utc>,
12    pub deleted_at: Option<DateTime<Utc>>,
13    pub course_module_id: Uuid,
14    pub course_unit_realisation_id: String,
15    pub label: Option<String>,
16    pub active: bool,
17    pub last_listed_at: Option<DateTime<Utc>>,
18    pub last_listed_person_count: Option<i32>,
19    pub last_already_linked_count: Option<i32>,
20    pub last_mailed_count: Option<i32>,
21    pub last_suppressed_by_dedup_count: Option<i32>,
22    pub last_suppressed_by_rate_cap_count: Option<i32>,
23    pub last_no_address_count: Option<i32>,
24    pub last_fast_tracked_count: Option<i32>,
25    pub last_fast_track_skipped_no_account_count: Option<i32>,
26    pub last_fast_track_skipped_unverified_count: Option<i32>,
27    pub last_fast_track_skipped_stale_verification_count: Option<i32>,
28    pub last_fast_track_skipped_name_mismatch_count: Option<i32>,
29    pub last_fast_track_skipped_account_has_number_count: Option<i32>,
30    pub last_fast_track_skipped_unlinked_before_count: Option<i32>,
31    pub last_listing_attempted_at: Option<DateTime<Utc>>,
32    pub last_listing_error: Option<CreditRegistrationErrorCode>,
33    pub consecutive_listing_failures: i32,
34}
35
36/// Outcome counters for one enrolment-discovery run over one realisation. Written whole, so the
37/// dashboard never mixes two runs.
38#[derive(Debug, Clone, PartialEq, Default)]
39pub struct RealisationListingOutcome {
40    pub listed_person_count: i32,
41    pub already_linked_count: i32,
42    pub mailed_count: i32,
43    pub suppressed_by_dedup_count: i32,
44    pub suppressed_by_rate_cap_count: i32,
45    pub no_address_count: i32,
46    pub fast_tracked_count: i32,
47    pub fast_track_skipped_no_account_count: i32,
48    pub fast_track_skipped_unverified_count: i32,
49    pub fast_track_skipped_stale_verification_count: i32,
50    pub fast_track_skipped_name_mismatch_count: i32,
51    pub fast_track_skipped_account_has_number_count: i32,
52    pub fast_track_skipped_unlinked_before_count: i32,
53}
54
55pub async fn upsert(
56    conn: &mut PgConnection,
57    course_module_id: Uuid,
58    course_unit_realisation_id: &str,
59    label: Option<&str>,
60    active: bool,
61) -> ModelResult<Uuid> {
62    let res = sqlx::query!(
63        r#"
64INSERT INTO course_module_suotar_realisations (
65    course_module_id,
66    course_unit_realisation_id,
67    label,
68    active
69  )
70VALUES ($1, $2, $3, $4) ON CONFLICT (
71    course_module_id,
72    course_unit_realisation_id,
73    deleted_at
74  ) DO
75UPDATE
76SET label = $3,
77  active = $4
78RETURNING id
79        "#,
80        course_module_id,
81        course_unit_realisation_id,
82        label,
83        active,
84    )
85    .fetch_one(conn)
86    .await?;
87    Ok(res.id)
88}
89
90/// Makes the module's live realisations exactly `wanted`, soft-deleting the rest rather than setting
91/// `active = false`, which means "configured but not polled now".
92pub async fn replace_for_course_module(
93    conn: &mut PgConnection,
94    course_module_id: Uuid,
95    wanted: &[CourseModuleSuotarRealisationEdit],
96) -> ModelResult<()> {
97    let existing = get_by_course_module_id(conn, course_module_id).await?;
98    let kept: Vec<&str> = wanted
99        .iter()
100        .map(|row| row.course_unit_realisation_id.trim())
101        .filter(|id| !id.is_empty())
102        .collect();
103    for row in existing {
104        if !kept.contains(&row.course_unit_realisation_id.as_str()) {
105            soft_delete(conn, row.id).await?;
106        }
107    }
108    for row in wanted {
109        let realisation_id = row.course_unit_realisation_id.trim();
110        if realisation_id.is_empty() {
111            continue;
112        }
113        let label = row
114            .label
115            .as_deref()
116            .map(str::trim)
117            .filter(|l| !l.is_empty());
118        upsert(conn, course_module_id, realisation_id, label, row.active).await?;
119    }
120    Ok(())
121}
122
123/// Shared by [`get_by_course_module_id`] and [`get_by_course_id`], which differ only in which of
124/// these is `Some`.
125async fn realisations_for(
126    conn: &mut PgConnection,
127    course_module_id: Option<Uuid>,
128    course_id: Option<Uuid>,
129) -> ModelResult<Vec<CourseModuleSuotarRealisation>> {
130    let res = sqlx::query_as!(
131        CourseModuleSuotarRealisation,
132        r#"
133SELECT cmsr.id,
134  cmsr.created_at,
135  cmsr.updated_at,
136  cmsr.deleted_at,
137  cmsr.course_module_id,
138  cmsr.course_unit_realisation_id,
139  cmsr.label,
140  cmsr.active,
141  cmsr.last_listed_at,
142  cmsr.last_listed_person_count,
143  cmsr.last_already_linked_count,
144  cmsr.last_mailed_count,
145  cmsr.last_suppressed_by_dedup_count,
146  cmsr.last_suppressed_by_rate_cap_count,
147  cmsr.last_no_address_count,
148  cmsr.last_fast_tracked_count,
149  cmsr.last_fast_track_skipped_no_account_count,
150  cmsr.last_fast_track_skipped_unverified_count,
151  cmsr.last_fast_track_skipped_stale_verification_count,
152  cmsr.last_fast_track_skipped_name_mismatch_count,
153  cmsr.last_fast_track_skipped_account_has_number_count,
154  cmsr.last_fast_track_skipped_unlinked_before_count,
155  cmsr.last_listing_attempted_at,
156  cmsr.last_listing_error AS "last_listing_error?",
157  cmsr.consecutive_listing_failures
158FROM course_module_suotar_realisations cmsr
159  JOIN course_modules cm ON cm.id = cmsr.course_module_id
160WHERE ($1::uuid IS NULL OR cmsr.course_module_id = $1)
161  AND ($2::uuid IS NULL OR cm.course_id = $2)
162  AND cm.deleted_at IS NULL
163  AND cmsr.deleted_at IS NULL
164ORDER BY cm.order_number,
165  cmsr.created_at
166        "#,
167        course_module_id,
168        course_id,
169    )
170    .fetch_all(conn)
171    .await?;
172    Ok(res)
173}
174
175pub async fn get_by_course_module_id(
176    conn: &mut PgConnection,
177    course_module_id: Uuid,
178) -> ModelResult<Vec<CourseModuleSuotarRealisation>> {
179    realisations_for(conn, Some(course_module_id), None).await
180}
181
182pub async fn get_by_course_id(
183    conn: &mut PgConnection,
184    course_id: Uuid,
185) -> ModelResult<Vec<CourseModuleSuotarRealisation>> {
186    realisations_for(conn, None, Some(course_id)).await
187}
188
189/// The id `list-by-course` echoes back for one realisation. Derived rather than stored, so the
190/// realisation stays greppable in both call logs without a ledger row.
191pub fn listing_request_item_id(realisation_id: Uuid) -> String {
192    format!("cur-{realisation_id}")
193}
194
195/// A realisation and the module facts a `list-by-course` request for it needs.
196#[derive(Debug, Clone, PartialEq)]
197pub struct RealisationToList {
198    pub id: Uuid,
199    pub course_module_id: Uuid,
200    pub course_id: Uuid,
201    pub course_unit_realisation_id: String,
202    /// `None` means the module has no course code configured, so it cannot be listed.
203    pub uh_course_code: Option<String>,
204    /// The language the mails this listing sets off are written in.
205    pub course_language_code: String,
206}
207
208/// Claims the realisations one discovery iteration takes, stalest attempt first.
209///
210/// Locks the rows `FOR UPDATE SKIP LOCKED` and stamps `last_listing_attempted_at` in the same
211/// transaction the caller commits before dropping the connection: the stamp is what keeps a second
212/// concurrent run from reselecting the same realisations once the lock itself is released ahead of
213/// the (potentially slow) Suotar call.
214///
215/// Ordering only: due-ness comes from the phase's own interval, never from the timestamps. Attempts
216/// order the queue rather than successes, so a realisation that keeps failing cannot starve the rest.
217pub async fn claim_stalest_for_listing(
218    conn: &mut PgConnection,
219    limit: i64,
220    course_id: Option<Uuid>,
221) -> ModelResult<Vec<RealisationToList>> {
222    let res = sqlx::query_as!(
223        RealisationToList,
224        r#"
225SELECT cmsr.id AS "id!",
226  cmsr.course_module_id AS "course_module_id!",
227  cm.course_id AS "course_id!",
228  cmsr.course_unit_realisation_id AS "course_unit_realisation_id!",
229  cm.uh_course_code AS "uh_course_code?",
230  co.language_code AS "course_language_code!"
231FROM course_module_suotar_realisations cmsr
232  JOIN credit_registration_active_course_modules acm ON acm.course_module_id = cmsr.course_module_id
233  JOIN course_modules cm ON cm.id = acm.course_module_id
234  JOIN courses co ON co.id = acm.course_id
235WHERE cmsr.active
236  AND cmsr.deleted_at IS NULL
237  AND ($2::uuid IS NULL OR acm.course_id = $2)
238ORDER BY COALESCE(cmsr.last_listing_attempted_at, cmsr.last_listed_at) ASC NULLS FIRST,
239  cmsr.id
240LIMIT $1
241FOR UPDATE OF cmsr SKIP LOCKED
242        "#,
243        limit,
244        course_id,
245    )
246    .fetch_all(&mut *conn)
247    .await?;
248    let ids: Vec<Uuid> = res.iter().map(|row| row.id).collect();
249    if !ids.is_empty() {
250        sqlx::query!(
251            r#"
252UPDATE course_module_suotar_realisations
253SET last_listing_attempted_at = now()
254WHERE id = ANY($1)
255            "#,
256            &ids,
257        )
258        .execute(conn)
259        .await?;
260    }
261    Ok(res)
262}
263
264/// Every active realisation of one course, unpaginated: unlike [`claim_stalest_for_listing`], which
265/// pages by staleness for the scheduler, this one must not miss any of them.
266pub async fn get_active_for_course(
267    conn: &mut PgConnection,
268    course_id: Uuid,
269) -> ModelResult<Vec<RealisationToList>> {
270    let res = sqlx::query_as!(
271        RealisationToList,
272        r#"
273SELECT cmsr.id AS "id!",
274  cmsr.course_module_id AS "course_module_id!",
275  cm.course_id AS "course_id!",
276  cmsr.course_unit_realisation_id AS "course_unit_realisation_id!",
277  cm.uh_course_code AS "uh_course_code?",
278  co.language_code AS "course_language_code!"
279FROM course_module_suotar_realisations cmsr
280  JOIN credit_registration_active_course_modules acm ON acm.course_module_id = cmsr.course_module_id
281  JOIN course_modules cm ON cm.id = acm.course_module_id
282  JOIN courses co ON co.id = acm.course_id
283WHERE cmsr.active
284  AND cmsr.deleted_at IS NULL
285  AND acm.course_id = $1
286ORDER BY cmsr.id
287        "#,
288        course_id,
289    )
290    .fetch_all(conn)
291    .await?;
292    Ok(res)
293}
294
295/// One active realisation's counters from its last successful discovery run, and whether the attempts
296/// since then have been failing. Point-in-time, not a windowed sum: the phase overwrites the row
297/// whole, so every surface rendering them has to say so.
298#[derive(Debug, Clone, PartialEq)]
299pub struct RealisationDiscoveryReport {
300    pub id: Uuid,
301    pub course_id: Uuid,
302    pub course_name: String,
303    pub course_module_id: Uuid,
304    pub course_module_name: Option<String>,
305    pub course_unit_realisation_id: String,
306    pub label: Option<String>,
307    pub uh_course_code: Option<String>,
308    pub last_listed_at: Option<DateTime<Utc>>,
309    pub last_listed_person_count: Option<i32>,
310    pub last_already_linked_count: Option<i32>,
311    pub last_mailed_count: Option<i32>,
312    pub last_suppressed_by_dedup_count: Option<i32>,
313    pub last_suppressed_by_rate_cap_count: Option<i32>,
314    pub last_no_address_count: Option<i32>,
315    pub last_fast_tracked_count: Option<i32>,
316    pub last_fast_track_skipped_no_account_count: Option<i32>,
317    pub last_fast_track_skipped_unverified_count: Option<i32>,
318    pub last_fast_track_skipped_stale_verification_count: Option<i32>,
319    pub last_fast_track_skipped_name_mismatch_count: Option<i32>,
320    pub last_fast_track_skipped_account_has_number_count: Option<i32>,
321    pub last_fast_track_skipped_unlinked_before_count: Option<i32>,
322    pub last_listing_attempted_at: Option<DateTime<Utc>>,
323    pub last_listing_error: Option<CreditRegistrationErrorCode>,
324    pub consecutive_listing_failures: i32,
325}
326
327pub async fn get_active_discovery_reports(
328    conn: &mut PgConnection,
329) -> ModelResult<Vec<RealisationDiscoveryReport>> {
330    let res = sqlx::query_as!(
331        RealisationDiscoveryReport,
332        r#"
333SELECT cmsr.id,
334  cm.course_id,
335  c.name AS course_name,
336  cmsr.course_module_id,
337  cm.name AS course_module_name,
338  cmsr.course_unit_realisation_id,
339  cmsr.label,
340  cm.uh_course_code,
341  cmsr.last_listed_at,
342  cmsr.last_listed_person_count,
343  cmsr.last_already_linked_count,
344  cmsr.last_mailed_count,
345  cmsr.last_suppressed_by_dedup_count,
346  cmsr.last_suppressed_by_rate_cap_count,
347  cmsr.last_no_address_count,
348  cmsr.last_fast_tracked_count,
349  cmsr.last_fast_track_skipped_no_account_count,
350  cmsr.last_fast_track_skipped_unverified_count,
351  cmsr.last_fast_track_skipped_stale_verification_count,
352  cmsr.last_fast_track_skipped_name_mismatch_count,
353  cmsr.last_fast_track_skipped_account_has_number_count,
354  cmsr.last_fast_track_skipped_unlinked_before_count,
355  cmsr.last_listing_attempted_at,
356  cmsr.last_listing_error AS "last_listing_error?: CreditRegistrationErrorCode",
357  cmsr.consecutive_listing_failures
358FROM course_module_suotar_realisations cmsr
359  JOIN course_modules cm ON cm.id = cmsr.course_module_id
360  JOIN courses c ON c.id = cm.course_id
361WHERE cmsr.active
362  AND cmsr.deleted_at IS NULL
363  AND cm.deleted_at IS NULL
364  AND c.deleted_at IS NULL
365ORDER BY c.name,
366  cmsr.course_unit_realisation_id
367        "#,
368    )
369    .fetch_all(conn)
370    .await?;
371    Ok(res)
372}
373
374/// Records a listing attempt whose roster never arrived. `last_listed_at` and the counters keep
375/// describing the last roster that did, because zeroing them would make a failed listing read as an
376/// empty course.
377pub async fn mark_listing_failed(
378    conn: &mut PgConnection,
379    id: Uuid,
380    error: CreditRegistrationErrorCode,
381) -> ModelResult<()> {
382    sqlx::query!(
383        r#"
384UPDATE course_module_suotar_realisations
385SET last_listing_attempted_at = now(),
386  last_listing_error = $2,
387  consecutive_listing_failures = consecutive_listing_failures + 1
388WHERE id = $1
389  AND deleted_at IS NULL
390        "#,
391        id,
392        error as CreditRegistrationErrorCode,
393    )
394    .execute(conn)
395    .await?;
396    Ok(())
397}
398
399pub async fn record_listing_outcome(
400    conn: &mut PgConnection,
401    id: Uuid,
402    outcome: &RealisationListingOutcome,
403) -> ModelResult<()> {
404    sqlx::query!(
405        r#"
406UPDATE course_module_suotar_realisations
407SET last_listed_at = now(),
408  last_listing_attempted_at = now(),
409  last_listing_error = NULL,
410  consecutive_listing_failures = 0,
411  last_listed_person_count = $2,
412  last_already_linked_count = $3,
413  last_mailed_count = $4,
414  last_suppressed_by_dedup_count = $5,
415  last_suppressed_by_rate_cap_count = $6,
416  last_no_address_count = $7,
417  last_fast_tracked_count = $8,
418  last_fast_track_skipped_no_account_count = $9,
419  last_fast_track_skipped_unverified_count = $10,
420  last_fast_track_skipped_stale_verification_count = $11,
421  last_fast_track_skipped_name_mismatch_count = $12,
422  last_fast_track_skipped_account_has_number_count = $13,
423  last_fast_track_skipped_unlinked_before_count = $14
424WHERE id = $1
425  AND deleted_at IS NULL
426        "#,
427        id,
428        outcome.listed_person_count,
429        outcome.already_linked_count,
430        outcome.mailed_count,
431        outcome.suppressed_by_dedup_count,
432        outcome.suppressed_by_rate_cap_count,
433        outcome.no_address_count,
434        outcome.fast_tracked_count,
435        outcome.fast_track_skipped_no_account_count,
436        outcome.fast_track_skipped_unverified_count,
437        outcome.fast_track_skipped_stale_verification_count,
438        outcome.fast_track_skipped_name_mismatch_count,
439        outcome.fast_track_skipped_account_has_number_count,
440        outcome.fast_track_skipped_unlinked_before_count,
441    )
442    .execute(conn)
443    .await?;
444    Ok(())
445}
446
447/// How many persons the last discovery run refused to fast-track because the registry's name did
448/// not look like the matched account's, summed over the active realisations.
449///
450/// A last-run value, not a window: the counters are overwritten whole on every run.
451pub async fn sum_last_fast_track_name_mismatches(conn: &mut PgConnection) -> ModelResult<i64> {
452    let count = sqlx::query_scalar!(
453        r#"
454SELECT COALESCE(
455    SUM(last_fast_track_skipped_name_mismatch_count),
456    0
457  ) AS "count!"
458FROM course_module_suotar_realisations
459WHERE active
460  AND deleted_at IS NULL
461        "#,
462    )
463    .fetch_one(conn)
464    .await?;
465    Ok(count)
466}
467
468pub async fn soft_delete(conn: &mut PgConnection, id: Uuid) -> ModelResult<()> {
469    sqlx::query!(
470        r#"
471UPDATE course_module_suotar_realisations
472SET deleted_at = now()
473WHERE id = $1
474  AND deleted_at IS NULL
475        "#,
476        id
477    )
478    .execute(conn)
479    .await?;
480    Ok(())
481}
headless_lms_models/course_module_suotar_realisations.rs

headless_lms_models/
course_module_suotar_realisations.rs
