Privacy & legal: data deletion, retention policy, and at-rest encryption for sensitive columns

[PianoNic]

Tracking issue for the four privacy/legal quick wins identified in the website-side audit. Splitting into one issue (this one) since they all serve the same goal: reducing the disclosure surface and risk in our privacy policy / DPIA.

Background

The backend processes special-category data under GDPR Art. 9 / besonders schützenswerte Personendaten under revDSG Art. 5 lit. c:

• Grades (Score, Weighting, Points, semester GPA, promotion decisions)
• Absence reasons (free-text up to 500 chars, can reveal health information)
• DOB, address, phone of school users
• Student documents (uploaded files in S3)

Audit also surfaced: no automatic retention / TTL, no column-level encryption, no self-service account deletion endpoint.

These have to be either fixed or disclosed in the privacy policy. Fixing is better than disclosing for sensitive-data minors.

Tasks

1. `DELETE /me` endpoint (GDPR Art. 17 / revDSG Art. 32)

Currently account deletion is admin-only (`DeleteApplicationUserCommand`, admin endpoint). Users have a right to erasure that doesn't depend on us being online to action it manually.

• Add `DELETE /api/me` (authenticated, current user only)
• Cascade-deletes the caller's `ApplicationUser` + all linked `SchoolUser` rows + their grades, absences, documents, semester reports
• Delete S3-stored student documents too (currently cascade is DB-only)
• Return 204 + invalidate the session
• Audit log the deletion (timestamp + user ID hash only, no PII)

2. Retention policy + cleanup job

Today: data lives forever until manually deleted. Needs an explicit, documented policy.

Proposed defaults (open to discussion):

• Account deleted by user: hard-delete after 30 days (grace period for accidental deletion)

• Inactive account (no login for 2 years): notify by email, hard-delete after another 30 days if no response

• OIDC sync metadata (last login, sync timestamps): retain only while account is active

• Add `LastLoginAt` to `ApplicationUser` if not present, populate on JWT validation

• Add a background job (Hangfire / hosted service / cron) that runs the retention sweeps

• Make thresholds configurable via `appsettings.json` (`Retention:InactiveLoginDays`, `Retention:DeletionGraceDays`)

• Document the policy in the repo README (so the website privacy policy can link to it)

3. Column-level encryption for sensitive fields

The DB host has disk encryption but no column-level encryption. For grades and absence reasons, that's not enough - a stolen backup or compromised DB dump exposes everything in plaintext.

• Encrypt at rest (EF Core value converters or `pgcrypto`):
  • `Grade.Score`, `Grade.Points`
  • `SemesterReport.GradeAverage`, `SemesterReport.PromotionDecision`
  • `SemesterSubjectGrade.Grade`
  • `Absence.Reason`
• Key managed via `user-secrets` in dev, env var / KMS in prod
• Migration plan: add encrypted columns, backfill, drop plaintext columns

4. (Documentation) Data Processing Record + DPIA

These are required internally under revDSG Art. 12 / GDPR Art. 30 + Art. 35 because we process sensitive data of minors.

• Add `docs/data-processing-record.md` (or similar) listing categories, purposes, legal basis, retention, recipients
• Add `docs/dpia.md` covering risks (no encryption, no TTL, plugin attack surface) + mitigations

Why now

The marketing site (schuly.dev) is getting privacy policy / Impressum / ToS pages, and Schuly is being released on the Play Store + App Store. Both stores require a published privacy policy that accurately describes what the backend does. We can either fix these and disclose less, or disclose them as-is and accept the friction with users and reviewers.

Recommended order: 1 → 2 → 3 → 4.