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 Incident Limitations: Single security incidents often fan out into complex, multi-week investigations spanning multiple distinct systems and sub-incidents.
- 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.
- 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.prismaprisma/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
Labels:
enhancement,database,prisma,architecture,security-opsDifficulty: 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.Coupling forensic case tracking directly to incident alert records introduces significant operational and data integrity issues:
📁 Implementation Scope
prisma/schema.prismaprisma/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:
CaseModel:idUUIDv4, human-readable identifiercaseNumberindexed, e.g.,CASE-2026-0891).CaseStatus:DRAFT,ACTIVE,IN_REVIEW,SUSPENDED,CLOSED,ARCHIVED) and severity level (CaseSeverity:LOW,MEDIUM,HIGH,CRITICAL).createdAt,updatedAt,closedAt,nextReviewAt).leadInvestigatorIdreferencingUser).CaseAssigneejoin table for multi-user collaborator tracking with granular role attributes (INVESTIGATOR,LEGAL_OBSERVER,EXTERNAL_AUDITOR).M:N) explicit mapping table (CaseIncident) linking multipleIncidentrecords to a singleCase.onDelete: Restrict) on critical case history to prevent accidental data loss during incident cleanup.CaseActivityLogmodel capturing state mutations, actor ID, IP address, changed fields (JSON diffs), and timestamp for tamper-evident history.2. Migration & Seed Pipeline
prisma migrate dev).🎯 Acceptance Criteria
prisma validateexecutes cleanly with zero syntax errors or implicit relation ambiguity warnings.CaseIncidentandCaseActivityLogrelations enforce strict foreign key constraints; attempts to delete an activeCasecontaining locked forensic records revert gracefully.