mars MARS-382

user soft-delete path does not cascade to userRoles (silent live-role orphans)

Done normal dmdb02-mars-cc

Live data (audit-mars, MARS-181 investigation): 6 soft-deleted users still carry LIVE userRoles (deletedAt IS NULL) + 34 orphaned soft-deleted role rows. Root cause: whatever code path soft-deletes a user does NOT cascade the soft-delete to that user's userRoles rows. Impact: (a) a resurrected/re-approved twin would inherit stale live roles (MARS-181 now normalizes on resurrect, so that specific surface is contained) but (b) any live-role-on-dead-user is a latent authz inconsistency — role-scoped queries that don't join users.deletedAt could grant access to a soft-deleted identity. FIX AT SOURCE (code-cascade, NOT a row-fix per No-Proactive-DB-Edits): the user-soft-delete action must soft-delete the user's live userRoles in the same txn (set archive context first per audited-table rules if applicable). db/coder owned. Backfill of the existing 6+34 rows is a SEPARATE decision once the cascade is fixed.

Sub-tickets — 0/1 done

restoreUser must regrant roles after MARS-382 soft-delete cascade (role-less restore gap)

MARS-479 Backlog

cmcoder-mars-cc

+ Add sub-ticket

Questions

No questions.

Activity

wi-cli-venus created · 6w ago
wi-cli-venus note · 6w ago

WIDEN SCOPE (audit-mars, MARS-181 diff-review tag mr4kti48hy6m): the same user-soft-delete no-cascade root affects studentAssignments AND teacherComisionMemberships, not just userRoles. Evidence path: MARS-181 resurrect reuses the twin's userId, so any leftover LIVE studentAssignments/teacherComisionMemberships carry forward — a resurrected student could sit in a stale OLD comisión + the newly-approved one. MARS-181 normalizes ROLES only (ratified scope); placements are deliberately NOT touched by it. The cascade fix here MUST cover all three child tables: userRoles + studentAssignments + teacherComisionMemberships. Confirm each has a deletedAt soft-delete col + whether archive-triggered (studentAssignments/teacherComisionMemberships may be audited — set archive context before their soft-delete if so, unlike userRoles). Backfill of existing orphans is a separate post-fix decision.
wi-cli-venus assigned · 4w ago

db02-mars-cc / db
wi-cli-venus commented · 4w ago

Assigned db02-mars-cc per Elazar go-dispatch 2026-07-16 (pmmaster-evolutiva-cc relay). No table/file overlap with db-mars-cc's MARS-472 (userRoles cascade vs vStudentAssignments/vTeacherComisionMemberships).
wi-cli-venus completed · 4w ago

db02-mars-cc mig 051 + coder-mars-cc ed881f5, audit-mars-ca PASS:ed881f5, live v2.31.0. userRoles cascade-soft-deletes on user soft-delete, at-source trigger. Follow-up MARS-479 (restoreUser regrant) + 6-row backfill remain separately tracked.