# Admin Hard-Delete User — Design **Status:** Draft **Date:** 2026-04-28 **Owner:** Backend (procleanlakes-api) **Related:** Frontend popup-modal work lives in the frontend repo and is out of scope here. ## Problem Admins need a way to permanently remove a user account and every record attached to it. The existing `DELETE /users/:id` is a soft-delete (sets `isActive=false`) and is intended for users to deactivate their own account. There is no admin-driven hard-delete today. ## Goals - Admin-only endpoint that permanently deletes a user and every dependent record. - Two-step confirmation: admin re-enters their password, then redeems a 6-digit code emailed to the admin. - Atomic cascade: all DB deletions run inside one Prisma `$transaction` so any failure rolls back cleanly. - Best-effort cleanup of physical files (avatars, attachments) after the transaction commits. - Live sessions of the deleted user are destroyed so they cannot keep using the app on a stale cookie. - Rate-limit failed password/code attempts to slow brute-force attacks. ## Non-goals - Frontend popup modals/warnings (lives in the frontend repo, separate PR). - Audit log of admin actions (project has no audit-log infrastructure today; out of scope). - Reassignment of authored content. Per requirements, the cascade deletes everything. - Soft-delete behavior changes (the existing `DELETE /users/:id` soft-delete remains, with one bonus authorization fix — see below). ## High-level flow ``` Step 1: POST /users/:id/delete-confirmation body: { password } ── verify admin password ── compute dependency summary ── generate 6-digit code, hash, store in AdminDeleteConfirmation ── email plaintext code to admin's address (post-commit) ── return summary + expiresAt (NOT the code) Step 2: POST /users/:id/hard-delete body: { password, confirmationCode } ── verify admin password ── verify code matches (adminId, targetUserId), unconsumed, unexpired ── transaction: ── mark confirmation consumed ── collect file paths ── cascade-delete all dependents in correct order ── delete the user row ── post-commit: ── unlinkSync collected file paths (best-effort) ── destroy user's sessions in `sessions` table ── log AdminDeleteAttempt(success: true) ── return success ``` ## Architecture The feature lives entirely inside `src/users/`: - New service methods on `UserService`: `initiateAdminDelete`, `confirmAdminDelete`, plus private helpers (`assertNotSelf`, `assertNotLastAdmin`, `enforceRateLimit`, `cascadeDeleteUser`). - Two new routes on `UserController`: `POST /users/:id/delete-confirmation` and `POST /users/:id/hard-delete`. Both `@Roles(Role.ADMIN)`. - New Handlebars template at `src/templates/admin-delete-confirmation.hbs` (mirrors the structure of the existing `link-email.hbs`). - New scheduled cron in `UserService` (uses existing `@nestjs/schedule` already wired in `app.module.ts`) for daily cleanup of expired confirmation rows and old attempt rows. Dependencies already injected on `UserService`: `PrismaService`, `FilesService`. We add `MailerService` and `ConfigService` to that list. The actual `unlinkSync` for cascaded files happens directly in `UserService` (post-commit) rather than via `FilesService.deleteFile`, because that method is per-row, requires `userId` ownership match, and runs its own transaction — not what we need here. ## Data model Two new Prisma models added to `prisma/schema.prisma`: ```prisma model AdminDeleteConfirmation { id Int @id @default(autoincrement()) code String @db.VarChar(255) // hashed 6-digit code adminId Int @map("admin_id") admin User @relation("AdminDeleteConfirmationAdmin", fields: [adminId], references: [id]) targetUserId Int @map("target_user_id") targetUser User @relation("AdminDeleteConfirmationTarget", fields: [targetUserId], references: [id]) expiresAt DateTime @map("expires_at") consumedAt DateTime? @map("consumed_at") createdAt DateTime @default(now()) @map("created_at") @@index([adminId, targetUserId]) @@index([expiresAt]) @@map("admin_delete_confirmations") } model AdminDeleteAttempt { id Int @id @default(autoincrement()) adminId Int @map("admin_id") admin User @relation("AdminDeleteAttemptAdmin", fields: [adminId], references: [id]) success Boolean @default(false) createdAt DateTime @default(now()) @map("created_at") @@index([adminId, createdAt]) @@map("admin_delete_attempts") } ``` Three back-relations added to `User`: ```prisma adminDeleteConfirmationsInitiated AdminDeleteConfirmation[] @relation("AdminDeleteConfirmationAdmin") adminDeleteConfirmationsTargeting AdminDeleteConfirmation[] @relation("AdminDeleteConfirmationTarget") adminDeleteAttempts AdminDeleteAttempt[] @relation("AdminDeleteAttemptAdmin") ``` Migration name: `add-admin-delete-confirmation-and-attempts`. **Why hash the code:** a DB read should not reveal active codes. Plaintext exists only between generation and email send. **Single-in-flight constraint:** when a new step-1 request arrives for the same `(adminId, targetUserId)`, any prior unconsumed `AdminDeleteConfirmation` for that pair is deleted. Prevents code reuse and limits clutter. ## API contract ### `POST /users/:id/delete-confirmation` **Auth:** `AuthenticatedGuard` + `RolesGuard` (`@Roles(Role.ADMIN)`). **Request body:** ```json { "password": "string" } ``` **Service flow:** 1. Rate-limit check (see below). If exceeded → `429 TOO_MANY_ATTEMPTS`. 2. If `id === req.user.id` → `400 CANNOT_DELETE_SELF`. 3. Verify `comparePasswords(body.password, admin.password)`. On miss → write `AdminDeleteAttempt(success: false)` → `401 INVALID_PASSWORD`. 4. Look up target user. Not found → `404 USER_NOT_FOUND`. 5. If target's role is `ADMIN` and they are the last `ADMIN` → `409 LAST_ADMIN`. 6. Compute dependency summary via parallel `count` queries against each related table. 7. Inside a transaction: delete prior unconsumed confirmations for `(adminId, targetUserId)`, generate a 6-digit code via `crypto.randomInt(100000, 1000000)`, hash it, insert new `AdminDeleteConfirmation` with `expiresAt = now + 10 min`. 8. After commit: send email via `MailerService.sendMail` to `admin.email` containing the plaintext code, the dependency summary, and the target's name+email+role. Wrap in try/catch — on failure return `503 MAIL_SEND_FAILED` (the row will simply expire unused). **Success response (200):** ```json { "success": true, "data": { "message": "Confirmation code sent to your email.", "expiresAt": "2026-04-28T14:35:00.000Z", "dependencies": { "trainingSessionsOwned": 3, "trainingModuleTrainerships": 2, "discussionsAuthored": 5, "discussionsCoaching": 1, "comments": 12, "files": 4, "blogPostsAuthored": 1, "blogPostsModerating": 0, "blogComments": 7, "trainingSessionEnrollments": 6, "favoriteTrainingModules": 9, "notifications": 23 } } } ``` The frontend uses `dependencies` to populate the warning modal. ### `POST /users/:id/hard-delete` **Auth:** `AuthenticatedGuard` + `RolesGuard` (`@Roles(Role.ADMIN)`). **Request body:** ```json { "password": "string", "confirmationCode": "string" } ``` **Service flow:** 1. Rate-limit check. 2. If `id === req.user.id` → `400 CANNOT_DELETE_SELF`. 3. If target's role is `ADMIN` and they are the last `ADMIN` → `409 LAST_ADMIN`. 4. Verify password. Miss → log failed attempt → `401 INVALID_PASSWORD`. 5. Look up `AdminDeleteConfirmation` where `adminId=req.user.id` AND `targetUserId=id` AND `consumedAt IS NULL`. Hash submitted code and compare. No match → log failed attempt → `401 INVALID_CODE`. Expired → `410 CODE_EXPIRED` (does not count toward rate limit). 6. Open `prisma.$transaction`: - Pre-read paths for post-commit cleanup. Two queries: - `SELECT path FROM files WHERE userId = X` — files directly owned by the user being deleted. - `SELECT f.path FROM files f JOIN comments c ON c.id = f.commentId WHERE c.discussionId IN (SELECT id FROM discussions WHERE userId = X)` — files attached to comments under the user's discussions, regardless of file owner. Their DB rows are deleted in step 11.3 of the cascade, so their physical bytes also need to go. - Collect into a single `paths: string[]` for post-commit `unlinkSync`. - Mark confirmation `consumedAt = now()`. - Run the cascade-delete sequence (next section). 7. Post-commit, best-effort: - For each collected path, derive disk filename via `path.split('/').pop()`, build absolute path against the `FilesService` upload dir, `unlinkSync` with try/catch — log on failure. - Destroy sessions: `SELECT id, data FROM sessions`, JSON-parse `data`, find rows whose `passport.user` (or equivalent serializer field — see `src/utils/serializer/session.serializer.ts`) matches the deleted user id, `DELETE FROM sessions WHERE id IN (...)`. - Insert `AdminDeleteAttempt(success: true)`. **Success response (200):** ```json { "success": true, "data": { "message": "User deleted successfully." } } ``` ### Error responses (consolidated) | Code | HTTP | When | |---|---|---| | `CANNOT_DELETE_SELF` | 400 | Admin tries to delete their own account | | `INVALID_PASSWORD` | 401 | Wrong password (counts toward rate limit) | | `INVALID_CODE` | 401 | Wrong confirmation code (counts toward rate limit) | | `CODE_EXPIRED` | 410 | Code older than 10 minutes (does not count toward rate limit) | | `USER_NOT_FOUND` | 404 | Target id doesn't exist | | `LAST_ADMIN` | 409 | Target is the last remaining ADMIN | | `TOO_MANY_ATTEMPTS` | 429 | Rate-limit triggered (response body includes `retryAfter` seconds) | | `MAIL_SEND_FAILED` | 503 | Step 1 only — mailer threw | ## Cascade deletion order Runs inside `prisma.$transaction(async (tx) => { ... })`. Strict order — wrong order causes FK constraint errors and rollback. 1. `AdminDeleteConfirmation` where `adminId = userId` and where `targetUserId = userId` (`deleteMany` × 2). 2. `AdminDeleteAttempt` where `adminId = userId` (`deleteMany`). 3. `VerificationToken` where `userId = userId` (`deleteMany`). 4. `Notification` where `userId = userId` (`deleteMany`). 5. `NotificationSettings` where `userId = userId` (`deleteMany`). 6. `UserFavoriteTrainingModule` where `userId = userId` (`deleteMany`). Schema has `onDelete: Cascade` here, but we delete explicitly so the order is auditable. 7. Discussions where user is **coach**: `updateMany` setting `coachId = null` (preserves discussions authored by other users). 8. Blog comment subtree on user-authored posts: 1. `SELECT id FROM blog_posts WHERE author_id = userId`. 2. `BlogComment` `deleteMany` where `blogPostId IN (...) AND repliedToId IS NOT NULL` (replies first — handles self-reference). 3. `BlogComment` `deleteMany` where `blogPostId IN (...)` (root comments). 4. `BlogPostModerator` `deleteMany` where `blogPostId IN (...)`. 5. `BlogPost` `deleteMany` where `authorId = userId`. 9. `BlogComment` authored by user on other people's posts: same two-pass to handle replies, both filtered by `userId = userId`. 10. `BlogPostModerator` where `userId = userId`. 11. Comment-and-file subtree under user's discussions: 1. `SELECT id FROM discussions WHERE userId = userId`. 2. `SELECT id FROM comments WHERE discussionId IN (...)`. 3. `File` `deleteMany` where `commentId IN (...)`. 4. `Comment` `deleteMany` where `discussionId IN (...)`. 5. `CoachingSession` `deleteMany` where `discussionId IN (...)`. 6. `Discussion` `deleteMany` where `userId = userId`. 12. Comments authored by user on other people's discussions: 1. `SELECT id FROM comments WHERE userId = userId`. 2. `File` `deleteMany` where `commentId IN (...)`. 3. `Comment` `deleteMany` where `userId = userId`. 13. `File` `deleteMany` where `userId = userId` (avatars, standalone uploads — what's left after steps 11/12). 14. Training session subtree (sessions the user owns): 1. `SELECT id FROM training_sessions WHERE owner_id = userId`. 2. `TrainingSessionStakeholders` `deleteMany` where `trainingSessionId IN (...)`. 3. `TrainingSession` `deleteMany` where `ownerId = userId`. 15. `TrainingSessionStakeholders` `deleteMany` where `stakeholderId = userId` (user's enrollments in others' sessions). 16. `TrainingModuleTrainer` `deleteMany` where `userId = userId` (modules themselves stay). 17. `User` `delete` where `id = userId`. A code comment at the top of `cascadeDeleteUser` lists this sequence as the authoritative reference, so future schema additions get reviewed against it. ## Security & rate limiting **Rate-limit window:** ``` COUNT(AdminDeleteAttempt) WHERE adminId = req.user.id AND success = false AND createdAt > now() - 15 minutes if count >= 5 → 429 TOO_MANY_ATTEMPTS, retryAfter = seconds until the oldest failure ages out ``` **What counts as a failure** (logged with `success: false`): - Wrong password on either endpoint → `INVALID_PASSWORD`. - Wrong code on step 2 → `INVALID_CODE`. **What does NOT count:** - Expired code (`CODE_EXPIRED`). - Self-delete, last-admin, not-found (validation, not auth attempts). **Other security properties:** - The 6-digit code is stored hashed via the project's existing password hashing helper (`~/utils/encryption.hashPassword`, bcrypt). Plaintext exists only in memory between generation and email send. Verification on step 2 uses `comparePasswords`. - The code is bound to `(adminId, targetUserId)` — a code issued for user A cannot be redeemed against user B even by the same admin. - The plaintext password is never logged. - Step 1 returns the dependency summary unconditionally — this is not a leak because admins can already enumerate users via `GET /users`. - The code is delivered out-of-band via email and is **not** included in the HTTP response of step 1. Possessing admin password + admin session cookie is insufficient — the attacker also needs access to the admin's mailbox. ## Email template New file: `src/templates/admin-delete-confirmation.hbs`. Style matches `link-email.hbs`. Renders: - Logo + brand header. - Greeting to admin's first name. - Target user's full name, email, role. - Dependency summary as a bulleted list (formatted for readability — only non-zero rows shown). - The 6-digit code in a large, copy-friendly block. - Expiry note: "expires in 10 minutes". - Footer text: "If you didn't request this, you can ignore this email — but please change your password immediately." The template context is passed by `initiateAdminDelete`; subject line uses the target's name+email so admins can tell concurrent confirmations apart in their inbox. ## Maintenance cron Daily at 03:00 UTC, `UserService.cleanupAdminDeleteRows()`: - `AdminDeleteConfirmation`: `deleteMany` where `expiresAt < now() - 7 days`. - `AdminDeleteAttempt`: `deleteMany` where `createdAt < now() - 30 days`. Wired with `@Cron(CronExpression.EVERY_DAY_AT_3AM)`. ## Bonus fix included in this PR `DELETE /users/:id` (existing soft-delete) currently has no check that the requesting user is the user being deactivated, which means any authenticated user can soft-delete any other user. We add: ```ts if (id !== req.user.id) { throw new ForbiddenException( createErrorResponse('You can only deactivate your own account', 'FORBIDDEN_NOT_SELF'), ); } ``` Inside `UserController.deleteProfile` before delegating to the service. No other change to the soft-delete behavior. Returns `403 FORBIDDEN_NOT_SELF` on violation. ## Testing The project has no service-level tests for `AuthService` or `UserService` today. Recommended: integration tests against a real MySQL test database. The cascade has 17 ordered steps with FK constraints and self-references — mocking Prisma would test the mocks, not the code. **Coverage:** *Step 1 — initiation:* - Happy path: confirmation row + email + dependency summary. - Self-deletion blocked → 400, no row, no email. - Last-admin blocked → 409, no row, no email. - Wrong password → 401, attempt logged, no confirmation, no email. - Target not found → 404, no attempt logged. - Two consecutive step-1 calls for same `(admin, target)` → second supersedes first. - Mailer throws → 503, confirmation row remains (will expire), no half-state. - Six rapid wrong passwords → fifth or sixth call returns 429 with `retryAfter`. *Step 2 — confirmation:* - Happy path: user and every dependent row deleted across all 17 cascade steps; sessions destroyed; physical files removed; `consumedAt` set; `success: true` logged. - Wrong password → 401, attempt logged, confirmation untouched. - Wrong code → 401, attempt logged, confirmation untouched. - Expired code → 410, attempt **not** logged, confirmation untouched (cron will clean). - Code reuse: redeem successfully, then resubmit same code → 401 (excluded by `consumedAt IS NULL` filter). - Code issued for user A submitted with `:id=B` → 401. - Cascade correctness: fixture user with at least one of every relation type — verify all gone post-delete and no other users' data touched. - Failure rollback: simulate DB error mid-transaction — verify user still exists, no partial deletion. - Post-commit failure tolerance: file `unlinkSync` throws → user still deleted, error logged, response still 200. *Bonus fix:* - `DELETE /users/:id` called by user A targeting user B → 403 `FORBIDDEN_NOT_SELF`, target unchanged. - Same call with `id === req.user.id` → still soft-deletes (existing behavior preserved). **Manual end-to-end verification before merge:** register two test users in dev, log in as admin, call step 1, check email arrives with code, copy code into step 2, verify deletion succeeded, verify deleted user's session is gone (cannot continue using the app on their cookie). ## Files touched - `prisma/schema.prisma` — add two models, three back-relations on User. - New migration: `add-admin-delete-confirmation-and-attempts`. - `src/users/users.service.ts` — add methods, imports for `MailerService`/`ConfigService`/`crypto`. - `src/users/users.module.ts` — verify `MailerService` is available (it's app-wide global from `MailerModule.forRoot`, so likely no change). - `src/users/users.controller.ts` — add two routes; add `req.user.id !== id` guard to existing `deleteProfile`. - `src/users/dtos/initiate-admin-delete.dto.ts` — new DTO with `password`. - `src/users/dtos/confirm-admin-delete.dto.ts` — new DTO with `password` + `confirmationCode`. - `src/templates/admin-delete-confirmation.hbs` — new template. - `dist/templates/admin-delete-confirmation.hbs` — included by the build (Nest copies templates). ## Open questions / risks - **Session destruction relies on parsing the `data` blob** in the `sessions` table. Format depends on the passport serializer (`src/utils/serializer/session.serializer.ts`). The plan task should verify the exact key path before writing the cleanup query — this is the most likely place to silently fail. - **`MailerService` availability**: `MailerModule.forRoot` is registered globally in `app.module.ts`, so `UserService` should be able to inject it. If there's any DI scoping surprise, it surfaces immediately at startup; not a runtime risk. - **DB session count**: if the deployment has thousands of active sessions, scanning them all post-delete is fine for now, but worth noting as a future concern if scale grows. (Acceptable for current size.)