Backend: Data retention (Assessments and BTC)

Backend task for the 2-year data retention rule – a daily timer function that deletes or anonymises Assessments, BTC školovanja and BTC certificiranja whose end date is more than 2 years ago (soft-deleted records included), with their child rows and signature files. Check the end date per record type, decide delete vs anonymise, implement, and verify.

Docs: Assessments – Data retention, BTC – Data retention Spec: none. This is a background job with no API endpoint. DB: all Assessments and BTC tables in api/database/create_schema.sql

Data of Assessments, BTC školovanje and BTC certificiranje is kept for 2 years after the record’s end date. Deleting a record in the app is only a soft delete (DeletedAt), so this job is what finally removes the data.

1. Double check API spec and DB structure

• End date per record type (the 2 years count from this date):

  • Assessment → Assessments.Date
  • BTC školovanje → Modul 4 BtcTrainingModules.EndDate
  • BTC certificiranje → BtcCertifications.EndDate

• Školovanje without a Modul 4 end date: module dates are optional, so some školovanja have no end date (they stay Aktivni) and would never expire.

  Not specified: which date counts for a školovanje without a Modul 4 end date. Options: the latest date entered on any module; CreatedAt; or never expire. A školovanje with no module dates at all needs the same decision.

• Soft-deleted records (DeletedAt set) follow the same rule, by their end date, not by the deletion date. They could only be deleted while upcoming, so they are always kept for at least 2 years.

• Delete or anonymise:

  Not specified: should expired records be deleted or anonymised? Delete: remove the record and all its child rows, plus the signature files in blob storage. Anonymise: keep the records and grades (e.g. for statistics), but clear the personal data: candidate FullName and Email, the free-text notes (AssessmentNotes), the komisija FullName / UserId, and the signature files (they show signatures). Please decide.

• Scope: only Assessments and BTC (školovanje and certificiranje).

  Not covered: the other sections (Events with invitees, consents and signed documents; Vozni park with reservations, zapisnici and photos). Do they need a retention rule too? If yes, it gets its own task.

• Boundary: a record expires when its end date is before today (Europe/Zagreb) minus 2 years. A record that ended exactly 2 years ago is kept until the next day. Confirm.

• DB: check the indexes support the daily query: IX_Assessments_Date exists; there is no index on BtcTrainingModules.EndDate or BtcCertifications.EndDate. Add them if needed.

2. Implementation

• Timer function: a new TimerTrigger function, once a day at night (e.g. 0 0 2 * * *), following EventInviteeEmailTimerFunction (try/catch, log errors, the next run picks up whatever failed).
• Cut-off date: today in Europe/Zagreb (the same helper as the BTC status) minus 2 years.
• Per section, one repository method that finds the expired records and processes them in small batches, one transaction per record, so one failure doesn’t block the rest.
• Delete (if chosen): child rows in FK order, since there is no ON DELETE CASCADE:
  • Assessments: AssessmentGrades, AssessmentNotes, AssessmentWrittenTestGrades → AssessmentBoardMembers, AssessmentCandidates → Assessments
  • BTC školovanje: BtcTrainingAttendance, BtcTrainingSignatureFiles → BtcTrainingModules, BtcTrainingCandidates → BtcTrainings
  • BTC certificiranje: BtcCertificationGrades, BtcCertificationWrittenExamScores → BtcCertificationBoardMembers, BtcCertificationCandidates → BtcCertifications
• Anonymise (if chosen): clear the fields agreed in step 1 and mark the record as anonymised so it isn’t processed again (e.g. an AnonymisedAt column).
• Signature files: delete the blobs in btc-signatures after the DB transaction commits. A missing blob doesn’t fail the run. Uses the DeleteAsync added in Backend: Potpisi page.
• Logging: log how many records of each type were processed, and every failure with the record id (no personal data in logs).

3. Verify implementation

• An assessment, a školovanje and a certificiranje that ended 2 years and 1 day ago are processed. Ones that ended exactly 2 years ago, or later, are untouched.
• A soft-deleted record past the cut-off is processed. One before it is untouched.
• After a delete run: no orphan child rows, and the signature blobs are gone.
• Running the job twice in a row changes nothing the second time.
• A failure on one record (e.g. a blob delete error) doesn’t stop the other records, and the record is processed on the next run.
• The školovanje-without-end-date case behaves as decided in step 1.

Done when

Every night, Assessments and BTC data older than 2 years after its end date is deleted or anonymised, as decided, without manual work.