# [FEAT] Design Independent Forensic Investigation & Case Management Domain Model in Prisma #126

Labels: enhancement, database, prisma, architecture, security-ops
Difficulty: High
Module: prisma/schema.prisma

🧠 Concept

Architect and integrate a dedicated, independent Investigation & Case Management data model within the system's core database domain (prisma/schema.prisma). The model must decouple long-running forensic case tracking from transient, short-lived alert incidents while supporting multi-party investigation ownership, granular audit trail history, and dynamic relationships to associated system evidence.

⚠️ Problem

Coupling forensic case tracking directly to incident alert records introduces significant operational and data integrity issues:

1. 1:1 Incident Limitations: Single security incidents often fan out into complex, multi-week investigations spanning multiple distinct systems and sub-incidents.
2. Missing Forensic Provenance: Closing or re-opening a temporary incident ticket corrupts or resets evidence chains, hindering long-term regulatory compliance and post-mortem reporting.
3. Inflexible RBAC & Ownership: Incident responders, external threat intelligence partners, and legal audit teams require distinct access levels across active cases that cannot be safely mapped to generic incident responder roles.

📁 Implementation Scope

• prisma/schema.prisma
• prisma/migrations/
• src/domain/cases/types/
• src/infrastructure/database/seeds/caseSeed.ts

🛠️ Requirements

1. Database Schema Extensions (prisma/schema.prisma)

Define a robust data model with strict relational constraints:

• Case Model:
  • Primary identifiers (id UUIDv4, human-readable identifier caseNumber indexed, e.g., CASE-2026-0891).
  • Enums for state lifecycle (CaseStatus: DRAFT, ACTIVE, IN_REVIEW, SUSPENDED, CLOSED, ARCHIVED) and severity level (CaseSeverity: LOW, MEDIUM, HIGH, CRITICAL).
  • Timestamps for record lifecycle (createdAt, updatedAt, closedAt, nextReviewAt).
• Ownership & Access Control Models:
  • Support lead investigator designation (leadInvestigatorId referencing User).
  • CaseAssignee join table for multi-user collaborator tracking with granular role attributes (INVESTIGATOR, LEGAL_OBSERVER, EXTERNAL_AUDITOR).
• Incident & Artifact Relational Mapping:
  • Many-to-Many (M:N) explicit mapping table (CaseIncident) linking multiple Incident records to a single Case.
  • Cascading delete/nullify behavior (onDelete: Restrict) on critical case history to prevent accidental data loss during incident cleanup.
• Audit & Timeline Ledger:
  • CaseActivityLog model capturing state mutations, actor ID, IP address, changed fields (JSON diffs), and timestamp for tamper-evident history.

2. Migration & Seed Pipeline

• Generate a deterministic, production-ready SQL migration (prisma migrate dev).
• Build a comprehensive database seed script populating mock cases, multi-incident attachments, and history logs for local integration testing.

🎯 Acceptance Criteria

• Schema Validation: prisma validate executes cleanly with zero syntax errors or implicit relation ambiguity warnings.
• Migration Safety: Migration applies seamlessly against a populated database without destructive table rewrites or lockup risks.
• Referential Integrity: CaseIncident and CaseActivityLog relations enforce strict foreign key constraints; attempts to delete an active Case containing locked forensic records revert gracefully.
• Audit Trail Completeness: Model schema inherently supports capturing delta diffs on every status transition without relying on application-level memory state.