Federal Forensic Case Management Suite

1. Project Overview

Custody Suite is a secure web-based system designed for managing case dossiers and tracking physical evidence collected at offense scenes. It provides a tamper-evident, legally auditable Chain of Custody record.

By integrating advanced MySQL database View filters, transactional Stored Procedures, and automated auditing Triggers directly on the database engine, the platform enforces referential integrity independent of application-level routing.

2. Technology Stack

The architecture is selected to support high-performance relational operations, secure data isolation, and concurrent process server executions.

* Full justifications visible on larger screens.

3. System Architecture

The diagram below illustrates the request lifecycle for a custody handover transaction, showing how it is processed by the database.

[Evidence Handover Action]
   │
   ▼
👤 User Client ---> ⚛ React UI (NextJS tab synchronization)
                     │
                     ▼ (HTTP POST payload)
                  ⚡ FastAPI Controller (Pydantic validator)
                     │
                     ├─(Invoke Stored Procedure)──> 🔒 MySQL Database (sp_transfer_custody)
                     │                                   │
                     │<─(Transaction SUCCESS)────────────┤
                     │                                   ▼
                     │                       [Update status & insert log]
                     │
                     ├─(Fire Update Trigger)──────> ⚡ MySQL Database (trg_evidence_update)
                     │                                   │
                     │                                   ▼
                     │                       [Write to audit_logs table]
                     │
                     ▼
                  👤 Client UI Refresh (Real-time timeline update)

4. Workspace Folder Structure

5. Relational Database Design

The entity relationship diagram details key mapping dependencies, foreign keys, and audit log links.

users table
  ├── user_id (Primary Key, INT)
  ├── employee_id (Unique, VARCHAR)
  ├── role_id (Foreign Key referencing roles)
  └── department_id (Foreign Key referencing departments)
         │
         ├── (1:N Relationship)
         ▼
cases table
  ├── case_id (Primary Key, INT)
  ├── case_number (Unique, VARCHAR)
  ├── lead_investigator (Foreign Key referencing users.user_id ON DELETE SET NULL)
  └── crime_scene_id (Foreign Key referencing crime_scenes)
         │
         ├─ (1:N Relationship)
         ▼
evidence table
  ├── evidence_id (Primary Key, INT)
  ├── barcode (Unique, VARCHAR)
  ├── case_id (Foreign Key referencing cases.case_id ON DELETE CASCADE)
  └── collected_by (Foreign Key referencing users.user_id ON DELETE SET NULL)
         │
         ├─ (1:N Relationship)
         ▼
evidence_custody table
  ├── custody_id (Primary Key, INT)
  ├── evidence_id (Foreign Key referencing evidence.evidence_id ON DELETE CASCADE)
  ├── from_user (Foreign Key referencing users.user_id ON DELETE SET NULL)
  └── to_user (Foreign Key referencing users.user_id ON DELETE SET NULL)

Relational Table Schema Specifications

6. API Endpoint Reference

* Usage details visible on larger screens.

// Request Payload: application/json
                            {
                                "first_name": "Marcus",
                                "last_name": "Burnett",
                                "email": "marcus@forensics.gov",
                                "phone": "+1 (555) 012-3456",
                                "password": "SecurePassword123",
                                "role_id": 2,          // 2 = Investigator
                                "department_id": 1,    // 1 = Forensic Unit
                                "status": "Active"
                            }

                            // Response: 201 Created
                            {
                                "user_id": 4,
                                "employee_id": "EMP-004",
                                "first_name": "Marcus",
                                "last_name": "Burnett",
                                "email": "marcus@forensics.gov",
                                "status": "Active"
                            }

// Request Payload: application/json
                            {
                                "evidence_id": 4,
                                "from_user": 2,
                                "to_user": 3,
                                "purpose": "Autopsy Testing",
                                "location": "Medical Lab, Vault A",
                                "remarks": "Container seal verified"
                            }

                            // Response: 201 Created
                            {
                                "custody_id": 14,
                                "evidence_id": 4,
                                "from_user": 2,
                                "to_user": 3,
                                "transfer_date": "2026-07-10T09:20:00"
                            }

7. Database Procedural SQL Code

Stored Procedure Transaction (`sp_transfer_custody`)

Performs evidence custody handoffs within an atomic transaction. If any update fails, the entire transaction is rolled back:

CREATE PROCEDURE sp_transfer_custody(
  IN p_evidence_id INT, IN p_from_user INT, IN p_to_user INT,
  IN p_purpose VARCHAR(255), IN p_location VARCHAR(255),
  IN p_remarks TEXT, IN p_status VARCHAR(50), OUT p_success BOOLEAN
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_success = FALSE;
  END;

  START TRANSACTION;
    INSERT INTO evidence_custody (evidence_id, from_user, to_user, transfer_date, purpose, location, remarks)
    VALUES (p_evidence_id, p_from_user, p_to_user, NOW(), p_purpose, p_location, p_remarks);

    UPDATE evidence SET current_status = p_status, storage_location = p_location
    WHERE evidence_id = p_evidence_id;
  COMMIT;
  SET p_success = TRUE;
END;

Automated Database trigger (`trg_evidence_update`)

Fires automatically on the database server whenever an evidence status updates, logging the action in `audit_logs`:

CREATE TRIGGER trg_evidence_update
AFTER UPDATE ON evidence FOR EACH ROW
BEGIN
  IF OLD.current_status <> NEW.current_status OR OLD.storage_location <> NEW.storage_location THEN
    INSERT INTO audit_logs (user_id, action, table_name, record_id, timestamp, ip_address)
    VALUES (NEW.collected_by, CONCAT('EVIDENCE_UPDATE: status=', NEW.current_status, ', location=', NEW.storage_location), 'evidence', NEW.evidence_id, NOW(), '127.0.0.1');
  END IF;
END;

Aggregated Database view (`v_case_summaries`)

Combines multiple tables and calculates live totals, enabling the backend to fetch case summaries with a single query:

CREATE OR REPLACE VIEW v_case_summaries AS
SELECT 
    c.case_id, c.case_number, c.title, c.description, c.crime_type, c.priority, c.status,
    CONCAT(u.first_name, ' ', u.last_name) AS lead_investigator_name,
    (SELECT COUNT(*) FROM evidence e WHERE e.case_id = c.case_id) AS evidence_count,
    (SELECT COUNT(*) FROM suspects s WHERE s.case_id = c.case_id) AS suspect_count
FROM cases c
LEFT JOIN users u ON c.lead_investigator = u.user_id;

8. Security & Data Safeguards

• ✓
  Password Hashing via bcrypt

  User accounts created in the system have their passwords securely hashed using bcrypt prior to database storage.

• ✓
  Self-Deletion Protection

  The frontend hides the deletion controls on the logged-in user's profile card, protecting active sessions from accidental self-deletion.

• ✓
  Orphan Prevention Cascades

  MySQL schemas utilize ON DELETE SET NULL on creator references, preserving forensic logs even when user accounts are deactivated.

9. Technical Challenges

10. Configuration & Roadmap

.env.example

# MySQL Database Coordinates
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=forensic_management_system
DB_USER=root
DB_PASSWORD=your_mysql_password

Roadmap