Retain moderation and audit evidence with userId linkage and pseudonymization

[HunteRoi]

Summary

Implement evidence retention rules so moderation and audit/security evidence survive user hard-delete lifecycle while preserving userId linkage and pseudonymized data where required.

Problem

The specification requires that when user data is deleted or accounts are deactivated, some classes of evidence must still be retained:

• moderation evidence
• audit events
• security events

At the same time, the retained evidence must support:

• continued userId linkage
• pseudonymization

Without a dedicated evidence-retention model, hard-delete behavior can either remove required evidence or retain too much raw user data incorrectly.

Why This Matters

This protects:

• moderation continuity
• security incident investigation
• auditability after deletion requests
• privacy-aware retention behavior

It also prevents the deletion lifecycle from silently destroying required records.

Required Behavior

1. Moderation evidence survives normal user hard-delete flow.
2. Audit and security events survive normal user hard-delete flow.
3. Retained evidence keeps userId linkage.
4. Retained evidence also supports pseudonymized data handling.
5. Regular application reads still respect soft-delete / hard-delete rules for user-facing data.

Acceptance Criteria

1. Hard-delete does not remove required moderation evidence.
2. Hard-delete does not remove required audit/security event records.
3. Retained evidence still links to the userId.
4. Pseudonymized representation is preserved where required by the retention model.
5. Normal user-facing reads do not expose retained evidence as if it were active account data.

Suggested Implementation Targets

• prisma/schema.prisma
• new evidence retention service or repository layer
• backend API modules to be created
• src/services/PostgresDatabaseService.ts

Suggested Technical Direction

Separate retained evidence from normal user profile data.

At minimum, retained evidence records should distinguish:

• evidence type
• retained linkage key (userId)
• pseudonymized projection fields
• retention reason
• originating guild if applicable
• createdAt / retainedAt timestamps

Do not rely on ordinary user profile rows as the long-term storage location for retained evidence.

Validation

• unit test: hard-delete path preserves evidence records
• unit test: retained evidence still references userId
• integration test: pseudonymized fields remain available
• integration test: normal account reads exclude retained evidence from user-facing profile data
• regression test: deletion workflow does not break moderation investigation capability

Traceability

• Spec: docs/specs/issue-93-specification.md
• Matrix rule: TRC-022
• Related docs:
  • docs/specs/traceability-matrix.md
  • docs/specs/issue-drafts.md
  • docs/specs/non-mvp-roadmap.md

Related Issues

• Umbrella Epic - Feature List #93
• Add self-serve data deletion and account deactivation with rolling 24h login block #137