Objective
Create a system for managing video evidence related to security incidents, maintaining chain of custody, and supporting investigation workflows.
Step 1: Create Incidents Table
Record security incidents.
CREATE TABLE security_incidents (
id INTEGER PRIMARY KEY,
incident_id VARCHAR(50) NOT NULL UNIQUE,
incident_type VARCHAR(100),
severity VARCHAR(20),
title VARCHAR(255),
description TEXT,
incident_time TIMESTAMP,
reported_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
location VARCHAR(200),
zone VARCHAR(100),
reported_by VARCHAR(100),
assigned_to VARCHAR(100),
status VARCHAR(50) DEFAULT 'open',
priority VARCHAR(20) DEFAULT 'medium',
resolution TEXT,
closed_at TIMESTAMP,
closed_by VARCHAR(100)
);
Step 2: Create Evidence Items Table
Store evidence metadata.
CREATE TABLE evidence_items (
id INTEGER PRIMARY KEY,
incident_id INTEGER NOT NULL,
evidence_id VARCHAR(50) NOT NULL UNIQUE,
evidence_type VARCHAR(50),
title VARCHAR(255),
description TEXT,
source_camera VARCHAR(100),
source_location VARCHAR(200),
start_time TIMESTAMP,
end_time TIMESTAMP,
duration_seconds INTEGER,
file_hash_sha256 VARCHAR(64),
file_size_mb DECIMAL(10, 2),
is_original BOOLEAN DEFAULT TRUE,
parent_evidence_id INTEGER,
collected_by VARCHAR(100),
collected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(50) DEFAULT 'collected',
FOREIGN KEY (incident_id) REFERENCES security_incidents(id),
FOREIGN KEY (parent_evidence_id) REFERENCES evidence_items(id)
);
Step 3: Create Evidence Files Table
Store actual video files.
CREATE TABLE evidence_files (
id INTEGER PRIMARY KEY,
evidence_id INTEGER NOT NULL,
file_type VARCHAR(50),
video_file VIDEO(MP4),
thumbnail IMAGE(JPEG),
resolution VARCHAR(20),
frame_rate INTEGER,
codec VARCHAR(50),
has_audio BOOLEAN DEFAULT FALSE,
storage_location VARCHAR(255),
is_encrypted BOOLEAN DEFAULT TRUE,
encryption_key_id VARCHAR(100),
FOREIGN KEY (evidence_id) REFERENCES evidence_items(id)
);
Step 4: Create Chain of Custody Table
Track evidence handling.
CREATE TABLE chain_of_custody (
id INTEGER PRIMARY KEY,
evidence_id INTEGER NOT NULL,
action VARCHAR(100),
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
performed_by VARCHAR(100),
received_by VARCHAR(100),
location VARCHAR(200),
purpose TEXT,
notes TEXT,
signature_hash VARCHAR(64),
FOREIGN KEY (evidence_id) REFERENCES evidence_items(id)
);
Step 5: Create Evidence Annotations Table
Store timestamps and notes.
CREATE TABLE evidence_annotations (
id INTEGER PRIMARY KEY,
evidence_id INTEGER NOT NULL,
timestamp_offset_sec DECIMAL(10, 2),
duration_sec DECIMAL(10, 2),
annotation_type VARCHAR(50),
title VARCHAR(255),
description TEXT,
frame_capture IMAGE(JPEG),
annotated_by VARCHAR(100),
annotated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (evidence_id) REFERENCES evidence_items(id)
);
Step 6: Create Evidence Requests Table
Track evidence access requests.
CREATE TABLE evidence_requests (
id INTEGER PRIMARY KEY,
evidence_id INTEGER NOT NULL,
request_id VARCHAR(50) UNIQUE,
requester_name VARCHAR(200),
requester_organization VARCHAR(200),
request_type VARCHAR(50),
purpose TEXT,
legal_authority TEXT,
requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
approved BOOLEAN,
approved_by VARCHAR(100),
approved_at TIMESTAMP,
provided_at TIMESTAMP,
denial_reason TEXT,
FOREIGN KEY (evidence_id) REFERENCES evidence_items(id)
);
Step 7: Insert Security Incidents
Add sample incidents.
INSERT INTO security_incidents (id, incident_id, incident_type, severity, title, description, incident_time, location, zone, reported_by, assigned_to, status, priority) VALUES
(1, 'INC-2024-001', 'theft', 'high', 'Laptop Theft from Office', 'Employee reported laptop missing from desk after lunch break', '2024-01-15 14:30:00', 'Building A Floor 2', 'Office Area', 'Jane Doe', 'Security Team Lead', 'investigating', 'high'),
(2, 'INC-2024-002', 'unauthorized_access', 'medium', 'Tailgating Attempt at Server Room', 'Unknown individual attempted to follow employee into server room', '2024-01-15 10:15:00', 'Building A Floor 2', 'Restricted Zone', 'Access Control System', 'Security Officer', 'closed', 'medium'),
(3, 'INC-2024-003', 'vandalism', 'low', 'Parking Lot Vehicle Damage', 'Vehicle reported with scratched paint in parking lot P1', '2024-01-14 18:00:00', 'Parking Lot P1', 'Parking Zone', 'Vehicle Owner', 'Security Officer', 'pending_review', 'low'),
(4, 'INC-2024-004', 'assault', 'critical', 'Physical Altercation in Lobby', 'Two individuals involved in physical confrontation in main lobby', '2024-01-15 09:45:00', 'Building A Lobby', 'Common Area', 'Receptionist', 'Security Manager', 'investigating', 'critical'),
(5, 'INC-2024-005', 'suspicious_activity', 'medium', 'After-Hours Access Alert', 'Unusual access patterns detected in R&D area after hours', '2024-01-15 23:30:00', 'Building B Floor 1', 'R&D Zone', 'Access Control System', 'Security Team Lead', 'open', 'medium');
Step 8: Insert Evidence Items
Add evidence records.
INSERT INTO evidence_items (id, incident_id, evidence_id, evidence_type, title, description, source_camera, source_location, start_time, end_time, duration_seconds, file_hash_sha256, file_size_mb, collected_by, status) VALUES
-- Incident 1: Laptop theft
(1, 1, 'EVD-2024-001-A', 'surveillance_video', 'Office Area Camera 14:00-15:00', 'Surveillance footage covering theft timeframe', 'CAM-OFF-201', 'Building A Floor 2 Office', '2024-01-15 14:00:00', '2024-01-15 15:00:00', 3600, 'a1b2c3d4e5f6...', 1850.50, 'Security Officer Jones', 'preserved'),
(2, 1, 'EVD-2024-001-B', 'surveillance_video', 'Hallway Camera 14:00-15:00', 'Hallway footage showing movement', 'CAM-HALL-202', 'Building A Floor 2 Hallway', '2024-01-15 14:00:00', '2024-01-15 15:00:00', 3600, 'b2c3d4e5f6g7...', 1620.25, 'Security Officer Jones', 'preserved'),
-- Incident 2: Tailgating
(3, 2, 'EVD-2024-002-A', 'surveillance_video', 'Server Room Entry Camera', 'Video showing tailgating attempt', 'CAM-SRV-101', 'Building A Floor 2 Server Room', '2024-01-15 10:10:00', '2024-01-15 10:20:00', 600, 'c3d4e5f6g7h8...', 425.80, 'Security Officer Smith', 'preserved'),
-- Incident 3: Vehicle damage
(4, 3, 'EVD-2024-003-A', 'surveillance_video', 'Parking Lot P1 Camera', '6-hour footage of parking area', 'CAM-PARK-P1', 'Parking Lot P1', '2024-01-14 12:00:00', '2024-01-14 18:00:00', 21600, 'd4e5f6g7h8i9...', 8250.00, 'Security Officer Davis', 'under_review'),
-- Incident 4: Lobby altercation
(5, 4, 'EVD-2024-004-A', 'surveillance_video', 'Lobby Camera 1', 'Main lobby footage of incident', 'CAM-LOBBY-01', 'Building A Lobby', '2024-01-15 09:40:00', '2024-01-15 10:00:00', 1200, 'e5f6g7h8i9j0...', 950.00, 'Security Manager Wilson', 'preserved'),
(6, 4, 'EVD-2024-004-B', 'surveillance_video', 'Lobby Camera 2', 'Alternative angle footage', 'CAM-LOBBY-02', 'Building A Lobby', '2024-01-15 09:40:00', '2024-01-15 10:00:00', 1200, 'f6g7h8i9j0k1...', 920.50, 'Security Manager Wilson', 'preserved'),
(7, 4, 'EVD-2024-004-C', 'clip', 'Edited Incident Clip', 'Key moments extracted', 'CAM-LOBBY-01', 'Building A Lobby', '2024-01-15 09:44:00', '2024-01-15 09:48:00', 240, 'g7h8i9j0k1l2...', 185.25, 'Security Manager Wilson', 'processed');