Full-Stack Software Engineering · Database Design

Federal Forensic Case Management Suit

Federal Forensic Case Management Suit is an enterprise-grade case dossier and physical evidence tracking platform. Engineered with advanced MySQL Views, Transactions, Stored Procedures, and Automated Triggers to preserve data integrity and maintain a legally auditable Chain of Custody.

Federal Forensic Case Management Suit Dashboard – live metrics, recent activity feed, and security logs

❌ The Problem

• • Manual spreadsheets for physical evidence logs lead to broken Chain of Custody errors in court.
• • Relational database constraints and log consistency are often neglected at the database server level.
• • Deleting user accounts or cases can orphan records, ruining forensic accountability audit trails.

✅ The Solution

• • Database transactions bundle handover logs and evidence statuses atomically to guarantee consistency.
• • Custom SQL Views offload complex multi-table queries and count aggregations to the MySQL engine.
• • Database triggers automatically log user actions, maintaining an immutable security audit history.

📊 Database & Project Metrics

📋 Case Dossier Printable Briefing

Printed Case briefing - optimized format for legal case portfolios

Enables officers to export case details into a printable file. The stylesheet strips out interactive buttons, background panels, and search bars, creating a clean document with case metadata, crime scenes, evidence registries, and suspect lists.

• ✅ Strips out interactive components automatically
• ✅ Structured tables for evidence grids and personnel listings
• ✅ Formatted case history logs

🛡️ Global Evidence Vault & Timeline

Chain of custody logs - interactive handover timeline and tracking

A centralized list of all physical evidence items logged in the system. Selecting an item loads its complete Chain of Custody handoff log history, tracking officers, coordinates, dates, and transfer remarks.

• ✅ Live Chain of Custody vertical timelines
• ✅ One-click locker seal status toggling
• ✅ Transaction-backed custody handover forms

👥 User Accounts Administration Panel

User Management – search, register credentials, and delete profiles

Allows administrators to manage profiles, assign roles, and allocate departments. New profiles receive hashed passwords and auto-generated employee IDs, while deleted accounts safely update referencing tables.

• ✅ Search and filter profiles by name or employee ID
• ✅ Add User modal with secure credentials creation
• ✅ Safety checks to prevent self-deletion

🔒 Stored Procedure with ACID Transaction

Bundles evidence custody handoffs into an atomic database operation, rolling back updates if either logging or status changes fail.

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;

⚡ Automatic Audit Triggers

Triggers automatically write state changes on cases and evidence tables to the `audit_logs` table, maintaining an independent audit history.

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_STATUS: status=', NEW.current_status), 'evidence', NEW.evidence_id, NOW(), '127.0.0.1');
  END IF;
END;

👁️ Aggregated SQL Reporting Views

Calculates total counts for cases, scenes, investigators, and evidence on the database side to optimize query performance.

CREATE OR REPLACE VIEW v_case_summaries AS
SELECT 
  c.case_id, c.case_number, c.title, 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;

📁 VS Code Workspace Explorer

☁️ Runtime Architecture Stack

⏱️ Development Timeline

MySQL Semicolon Procedural Parsing

Python splits schema files using semicolon separators, breaking stored procedures and triggers. I bypassed this by executing advanced features as unified raw SQL blocks directly during bootstrap.

Session Reactivity & Page Refreshes

Browser refreshes reset tab selections. I solved this by synchronizing tab states with URL parameters (`?tab=...&caseId=...`), preserving coordinates across browser reloads.

Cascade Integrity Safeguards

Prevented orphaned records when deleting user profiles. Configured `ON DELETE SET NULL` for user-creator attributes while protecting the active session user profile from self-deletion.

✓ Legally Consistent Auditing

Enforces a clean transaction timeline for all evidence handovers, eliminating inconsistent database updates.

✓ Optimized Relational Queries

Utilizes database-level views to aggregate statistics, reducing case overview query loading times.

✓ Independent Security Logs

Records case and evidence state modifications automatically using triggers, ensuring auditing is independent of application routes.

Case Study Key Takeaways

• ✅ Engineered relational case timelines and evidence directories
• ✅ Programmed MySQL transactions and stored procedures
• ✅ Structured automated triggers and auditing logs
• ✅ Migrated components to Next.js with state parameter synchronization
• ✅ Designed print-ready CSS briefing dossier layouts

Lighthouse Metrics