Incident Video Evidence

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');

Step 9: Insert Evidence Files

Add file details.

INSERT INTO evidence_files (id, evidence_id, file_type, resolution, frame_rate, codec, has_audio, storage_location, is_encrypted, encryption_key_id) VALUES
    (1, 1, 'original', '1080p', 30, 'H.264', TRUE, '/evidence/2024/001/EVD-2024-001-A.mp4', TRUE, 'KEY-2024-001'),
    (2, 2, 'original', '1080p', 30, 'H.264', FALSE, '/evidence/2024/001/EVD-2024-001-B.mp4', TRUE, 'KEY-2024-001'),
    (3, 3, 'original', '4K', 30, 'H.265', TRUE, '/evidence/2024/002/EVD-2024-002-A.mp4', TRUE, 'KEY-2024-002'),
    (4, 4, 'original', '1080p', 25, 'H.264', FALSE, '/evidence/2024/003/EVD-2024-003-A.mp4', TRUE, 'KEY-2024-003'),
    (5, 5, 'original', '1080p', 30, 'H.264', TRUE, '/evidence/2024/004/EVD-2024-004-A.mp4', TRUE, 'KEY-2024-004'),
    (6, 6, 'original', '1080p', 30, 'H.264', TRUE, '/evidence/2024/004/EVD-2024-004-B.mp4', TRUE, 'KEY-2024-004'),
    (7, 7, 'processed', '1080p', 30, 'H.264', TRUE, '/evidence/2024/004/EVD-2024-004-C.mp4', TRUE, 'KEY-2024-004');

Step 10: Insert Chain of Custody Records

Track evidence handling.

INSERT INTO chain_of_custody (id, evidence_id, action, action_time, performed_by, received_by, location, purpose, notes) VALUES
    -- Evidence 1 chain
    (1, 1, 'collected', '2024-01-15 15:30:00', 'Security Officer Jones', NULL, 'Security Office', 'Initial collection', 'Exported from DVR system'),
    (2, 1, 'verified', '2024-01-15 15:45:00', 'Security Officer Jones', NULL, 'Security Office', 'Hash verification', 'SHA-256 hash computed and recorded'),
    (3, 1, 'transferred', '2024-01-15 16:00:00', 'Security Officer Jones', 'Evidence Custodian', 'Evidence Room', 'Secure storage', 'Transferred to evidence locker'),
    -- Evidence 5 chain (critical incident)
    (4, 5, 'collected', '2024-01-15 10:15:00', 'Security Manager Wilson', NULL, 'Security Office', 'Urgent collection', 'Priority collection for critical incident'),
    (5, 5, 'verified', '2024-01-15 10:20:00', 'Security Manager Wilson', NULL, 'Security Office', 'Hash verification', 'SHA-256 verified'),
    (6, 5, 'reviewed', '2024-01-15 11:00:00', 'Security Manager Wilson', NULL, 'Security Office', 'Initial review', 'Incident timeline identified'),
    (7, 5, 'copied', '2024-01-15 12:00:00', 'Security Manager Wilson', NULL, 'Security Office', 'Working copy', 'Created working copy for investigation'),
    (8, 5, 'transferred', '2024-01-15 14:00:00', 'Security Manager Wilson', 'Legal Department', 'Legal Office', 'Legal review', 'Provided to legal for review');

Step 11: Insert Evidence Annotations

Add timestamps and notes.

INSERT INTO evidence_annotations (id, evidence_id, timestamp_offset_sec, duration_sec, annotation_type, title, description, annotated_by) VALUES
    -- Incident 1 annotations
    (1, 1, 1820, 45, 'person_of_interest', 'Unknown Individual at Desk', 'Unidentified person observed near victims desk', 'Security Officer Jones'),
    (2, 1, 1865, 30, 'action', 'Item Removed', 'Individual appears to take object from desk', 'Security Officer Jones'),
    (3, 2, 1850, 20, 'movement', 'Hallway Exit', 'Same individual leaving via north hallway', 'Security Officer Jones'),
    -- Incident 4 annotations
    (4, 5, 240, 5, 'incident_start', 'Initial Confrontation', 'Verbal altercation begins between two individuals', 'Security Manager Wilson'),
    (5, 5, 280, 15, 'escalation', 'Physical Contact', 'Altercation becomes physical', 'Security Manager Wilson'),
    (6, 5, 320, 10, 'intervention', 'Security Response', 'Security personnel intervene', 'Security Manager Wilson'),
    (7, 5, 380, 30, 'resolution', 'Parties Separated', 'Individuals separated and contained', 'Security Manager Wilson');

Step 12: Insert Evidence Requests

Track access requests.

INSERT INTO evidence_requests (id, evidence_id, request_id, requester_name, requester_organization, request_type, purpose, legal_authority, requested_at, approved, approved_by, approved_at, provided_at) VALUES
    (1, 1, 'REQ-2024-001', 'Detective Johnson', 'City Police Department', 'law_enforcement', 'Criminal investigation into theft', 'Subpoena #2024-PD-1234', '2024-01-16 09:00:00', TRUE, 'Legal Counsel', '2024-01-16 14:00:00', '2024-01-16 15:00:00'),
    (2, 5, 'REQ-2024-002', 'Attorney Smith', 'Smith & Associates', 'legal', 'Civil litigation preparation', 'Client authorization', '2024-01-16 10:00:00', TRUE, 'Legal Counsel', '2024-01-16 16:00:00', '2024-01-17 09:00:00'),
    (3, 4, 'REQ-2024-003', 'Insurance Adjuster Brown', 'ABC Insurance', 'insurance', 'Vehicle damage claim investigation', 'Claim #INS-2024-5678', '2024-01-15 10:00:00', FALSE, 'Security Manager', '2024-01-15 11:00:00', NULL);

Step 13: Get Incident Evidence Package

Retrieve all evidence for incident.

SELECT
    si.incident_id,
    si.title as incident_title,
    ei.evidence_id,
    ei.evidence_type,
    ei.title as evidence_title,
    ei.source_camera,
    ei.start_time,
    ei.duration_seconds / 60 as duration_min,
    ei.file_size_mb,
    ei.status
FROM security_incidents si
INNER JOIN evidence_items ei ON si.id = ei.incident_id
WHERE si.id = 4
ORDER BY ei.start_time;

Step 14: View Chain of Custody

Track evidence handling history.

SELECT
    ei.evidence_id,
    coc.action,
    coc.action_time,
    coc.performed_by,
    coc.received_by,
    coc.location,
    coc.purpose
FROM chain_of_custody coc
INNER JOIN evidence_items ei ON coc.evidence_id = ei.id
WHERE ei.incident_id = 4
ORDER BY coc.action_time;

Step 15: Evidence Timeline with Annotations

View evidence with key moments.

SELECT
    ei.evidence_id,
    ei.title,
    ea.timestamp_offset_sec / 60.0 as minute_mark,
    ea.annotation_type,
    ea.title as annotation,
    ea.description,
    ea.annotated_by
FROM evidence_items ei
INNER JOIN evidence_annotations ea ON ei.id = ea.evidence_id
WHERE ei.incident_id = 4
ORDER BY ei.start_time, ea.timestamp_offset_sec;

Cleanup (Optional)

DROP TABLE IF EXISTS evidence_requests;
DROP TABLE IF EXISTS evidence_annotations;
DROP TABLE IF EXISTS chain_of_custody;
DROP TABLE IF EXISTS evidence_files;
DROP TABLE IF EXISTS evidence_items;
DROP TABLE IF EXISTS security_incidents;

Expected Outcomes

• Incidents documented
• Evidence collected
• Chain of custody maintained
• Annotations added
• Requests tracked

Evidence Status Values

Key Concepts Learned

• Evidence collection workflow
• Chain of custody tracking
• Hash verification
• Annotation timestamps
• Access request management