Security Camera Video Storage

Objective

Create a system for storing and managing surveillance video footage from security cameras with support for multiple locations, retention policies, and efficient retrieval.

Step 1: Create Camera Locations Table

Define camera installation sites.

CREATE TABLE camera_locations (
    id INTEGER PRIMARY KEY,
    location_code VARCHAR(50) NOT NULL UNIQUE,
    location_name VARCHAR(200),
    building VARCHAR(100),
    floor VARCHAR(20),
    zone VARCHAR(100),
    area_type VARCHAR(50),
    gps_latitude DECIMAL(10, 8),
    gps_longitude DECIMAL(11, 8),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Security Cameras Table

Store camera details.

CREATE TABLE security_cameras (
    id INTEGER PRIMARY KEY,
    camera_id VARCHAR(50) NOT NULL UNIQUE,
    camera_name VARCHAR(200),
    location_id INTEGER NOT NULL,
    camera_type VARCHAR(50),
    manufacturer VARCHAR(100),
    model VARCHAR(100),
    resolution VARCHAR(20),
    frame_rate INTEGER DEFAULT 30,
    has_audio BOOLEAN DEFAULT FALSE,
    has_ptz BOOLEAN DEFAULT FALSE,
    has_night_vision BOOLEAN DEFAULT TRUE,
    ip_address VARCHAR(50),
    status VARCHAR(50) DEFAULT 'online',
    install_date DATE,
    last_maintenance DATE,
    FOREIGN KEY (location_id) REFERENCES camera_locations(id)
);

Step 3: Create Video Recordings Table

Store recorded footage.

CREATE TABLE video_recordings (
    id INTEGER PRIMARY KEY,
    camera_id INTEGER NOT NULL,
    recording_uid VARCHAR(100) UNIQUE,
    video_file VIDEO(MP4),
    thumbnail IMAGE(JPEG),
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    duration_seconds INTEGER,
    file_size_mb DECIMAL(10, 2),
    resolution VARCHAR(20),
    frame_rate INTEGER,
    codec VARCHAR(50),
    has_motion BOOLEAN DEFAULT FALSE,
    motion_events INTEGER DEFAULT 0,
    quality_score DECIMAL(3, 2),
    is_archived BOOLEAN DEFAULT FALSE,
    retention_days INTEGER DEFAULT 30,
    expires_at TIMESTAMP,
    FOREIGN KEY (camera_id) REFERENCES security_cameras(id)
);

Step 4: Create Video Segments Table

Store chunked recordings for streaming.

CREATE TABLE video_segments (
    id INTEGER PRIMARY KEY,
    recording_id INTEGER NOT NULL,
    segment_number INTEGER,
    segment_file VIDEO(MP4),
    start_offset_sec INTEGER,
    duration_sec INTEGER,
    file_size_mb DECIMAL(8, 2),
    is_keyframe BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (recording_id) REFERENCES video_recordings(id)
);

Step 5: Create Recording Tags Table

Label recordings with metadata.

CREATE TABLE recording_tags (
    id INTEGER PRIMARY KEY,
    recording_id INTEGER NOT NULL,
    tag_name VARCHAR(100),
    tag_value VARCHAR(255),
    tagged_by VARCHAR(100),
    tagged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (recording_id) REFERENCES video_recordings(id)
);

Step 6: Insert Camera Locations

Add surveillance zones.

INSERT INTO camera_locations (id, location_code, location_name, building, floor, zone, area_type, gps_latitude, gps_longitude) VALUES
    (1, 'LOC-ENT-MAIN', 'Main Entrance', 'Building A', 'Ground', 'Entrance Zone', 'entrance', 37.78825000, -122.40730000),
    (2, 'LOC-LOBBY-A', 'Main Lobby', 'Building A', 'Ground', 'Lobby Zone', 'lobby', 37.78830000, -122.40735000),
    (3, 'LOC-PARK-A', 'Parking Garage Level 1', 'Parking A', 'P1', 'Parking Zone', 'parking', 37.78815000, -122.40745000),
    (4, 'LOC-PARK-B', 'Parking Garage Level 2', 'Parking A', 'P2', 'Parking Zone', 'parking', 37.78815000, -122.40745000),
    (5, 'LOC-SERV-A', 'Server Room', 'Building A', '2', 'Secure Zone', 'restricted', 37.78840000, -122.40720000),
    (6, 'LOC-ELEV-A1', 'Elevator Bank A', 'Building A', 'Ground', 'Common Area', 'elevator', 37.78832000, -122.40732000),
    (7, 'LOC-STAIR-A1', 'Stairwell A North', 'Building A', 'All', 'Emergency Exit', 'stairwell', 37.78835000, -122.40728000),
    (8, 'LOC-LOAD-A', 'Loading Dock', 'Building A', 'Ground', 'Service Zone', 'loading', 37.78820000, -122.40750000);

Step 7: Insert Security Cameras

Add camera inventory.

INSERT INTO security_cameras (id, camera_id, camera_name, location_id, camera_type, manufacturer, model, resolution, frame_rate, has_audio, has_ptz, has_night_vision, ip_address, status, install_date) VALUES
    (1, 'CAM-ENT-001', 'Main Entrance Camera 1', 1, 'dome', 'Axis', 'P3245-V', '1080p', 30, TRUE, TRUE, TRUE, '192.168.1.101', 'online', '2023-01-15'),
    (2, 'CAM-ENT-002', 'Main Entrance Camera 2', 1, 'bullet', 'Hikvision', 'DS-2CD2085', '4K', 25, FALSE, FALSE, TRUE, '192.168.1.102', 'online', '2023-01-15'),
    (3, 'CAM-LOBBY-001', 'Lobby Overview', 2, 'dome', 'Axis', 'P3245-V', '1080p', 30, TRUE, TRUE, TRUE, '192.168.1.103', 'online', '2023-01-15'),
    (4, 'CAM-PARK-P1-001', 'Parking P1 Entrance', 3, 'bullet', 'Hikvision', 'DS-2CD2085', '4K', 25, FALSE, FALSE, TRUE, '192.168.1.104', 'online', '2023-02-01'),
    (5, 'CAM-PARK-P1-002', 'Parking P1 Interior', 3, 'dome', 'Axis', 'M3057-PLVE', '1080p', 15, FALSE, FALSE, TRUE, '192.168.1.105', 'online', '2023-02-01'),
    (6, 'CAM-SERVER-001', 'Server Room Entry', 5, 'dome', 'Axis', 'P3245-V', '4K', 30, TRUE, FALSE, TRUE, '192.168.1.106', 'online', '2023-01-20'),
    (7, 'CAM-SERVER-002', 'Server Room Interior', 5, 'fisheye', 'Axis', 'M3057-PLVE', '1080p', 15, TRUE, FALSE, TRUE, '192.168.1.107', 'online', '2023-01-20'),
    (8, 'CAM-LOAD-001', 'Loading Dock', 8, 'bullet', 'Hikvision', 'DS-2CD2685', '4K', 25, TRUE, TRUE, TRUE, '192.168.1.108', 'online', '2023-03-01');

Step 8: Insert Video Recordings

Add sample recordings.

INSERT INTO video_recordings (id, camera_id, recording_uid, start_time, end_time, duration_seconds, file_size_mb, resolution, frame_rate, codec, has_motion, motion_events, quality_score, retention_days, expires_at) VALUES
    (1, 1, 'REC-20240115-CAM001-0800', '2024-01-15 08:00:00', '2024-01-15 09:00:00', 3600, 1250.50, '1080p', 30, 'H.264', TRUE, 45, 0.95, 30, '2024-02-14 09:00:00'),
    (2, 1, 'REC-20240115-CAM001-0900', '2024-01-15 09:00:00', '2024-01-15 10:00:00', 3600, 1320.75, '1080p', 30, 'H.264', TRUE, 62, 0.92, 30, '2024-02-14 10:00:00'),
    (3, 2, 'REC-20240115-CAM002-0800', '2024-01-15 08:00:00', '2024-01-15 09:00:00', 3600, 2450.00, '4K', 25, 'H.265', TRUE, 38, 0.98, 30, '2024-02-14 09:00:00'),
    (4, 4, 'REC-20240115-CAM004-0700', '2024-01-15 07:00:00', '2024-01-15 08:00:00', 3600, 2180.25, '4K', 25, 'H.265', TRUE, 28, 0.94, 30, '2024-02-14 08:00:00'),
    (5, 6, 'REC-20240115-CAM006-0000', '2024-01-15 00:00:00', '2024-01-15 01:00:00', 3600, 2890.00, '4K', 30, 'H.265', FALSE, 2, 0.97, 90, '2024-04-15 01:00:00'),
    (6, 6, 'REC-20240115-CAM006-0800', '2024-01-15 08:00:00', '2024-01-15 09:00:00', 3600, 2950.50, '4K', 30, 'H.265', TRUE, 15, 0.96, 90, '2024-04-15 09:00:00'),
    (7, 8, 'REC-20240115-CAM008-0600', '2024-01-15 06:00:00', '2024-01-15 07:00:00', 3600, 2100.00, '4K', 25, 'H.265', TRUE, 8, 0.93, 30, '2024-02-14 07:00:00'),
    (8, 3, 'REC-20240115-CAM003-1200', '2024-01-15 12:00:00', '2024-01-15 13:00:00', 3600, 1450.25, '1080p', 30, 'H.264', TRUE, 85, 0.91, 30, '2024-02-14 13:00:00');

Step 9: Insert Video Segments

Add streaming segments.

INSERT INTO video_segments (id, recording_id, segment_number, start_offset_sec, duration_sec, file_size_mb, is_keyframe) VALUES
    (1, 1, 1, 0, 300, 104.20, TRUE),
    (2, 1, 2, 300, 300, 108.50, FALSE),
    (3, 1, 3, 600, 300, 102.80, TRUE),
    (4, 1, 4, 900, 300, 105.30, FALSE),
    (5, 1, 5, 1200, 300, 110.20, TRUE),
    (6, 1, 6, 1500, 300, 106.40, FALSE),
    (7, 2, 1, 0, 300, 110.50, TRUE),
    (8, 2, 2, 300, 300, 112.30, FALSE),
    (9, 2, 3, 600, 300, 108.90, TRUE);

Step 10: Insert Recording Tags

Add metadata tags.

INSERT INTO recording_tags (id, recording_id, tag_name, tag_value, tagged_by) VALUES
    (1, 1, 'event_type', 'high_traffic', 'system'),
    (2, 1, 'time_of_day', 'morning_rush', 'system'),
    (3, 2, 'incident', 'suspicious_activity', 'security_operator'),
    (4, 2, 'reviewed', 'true', 'J. Smith'),
    (5, 5, 'area', 'restricted_access', 'system'),
    (6, 6, 'maintenance', 'technician_visit', 'security_operator'),
    (7, 7, 'delivery', 'scheduled', 'system'),
    (8, 8, 'crowd', 'lunch_rush', 'system');

Step 11: Get Camera Recordings

List recordings for a camera.

SELECT
    vr.recording_uid,
    vr.start_time,
    vr.end_time,
    vr.duration_seconds / 60 as duration_min,
    vr.file_size_mb,
    vr.resolution,
    vr.has_motion,
    vr.motion_events,
    vr.quality_score
FROM video_recordings vr
WHERE vr.camera_id = 1
ORDER BY vr.start_time DESC
LIMIT 10;

Step 12: Get Recordings by Location

Find footage from a zone.

SELECT
    cl.location_name,
    sc.camera_name,
    vr.start_time,
    vr.duration_seconds / 60 as duration_min,
    vr.motion_events,
    vr.file_size_mb
FROM video_recordings vr
INNER JOIN security_cameras sc ON vr.camera_id = sc.id
INNER JOIN camera_locations cl ON sc.location_id = cl.id
WHERE cl.area_type = 'entrance'
  AND vr.start_time >= '2024-01-15 08:00:00'
  AND vr.start_time < '2024-01-15 12:00:00'
ORDER BY vr.start_time;

Step 13: Storage Usage Report

Analyze video storage.

SELECT
    cl.area_type,
    COUNT(DISTINCT sc.id) as cameras,
    COUNT(vr.id) as recordings,
    SUM(vr.file_size_mb) / 1024 as total_gb,
    AVG(vr.quality_score) as avg_quality
FROM camera_locations cl
INNER JOIN security_cameras sc ON cl.id = sc.location_id
LEFT JOIN video_recordings vr ON sc.id = vr.camera_id
GROUP BY cl.area_type
ORDER BY total_gb DESC;

Step 14: Expiring Recordings

Find recordings nearing retention limit.

SELECT
    sc.camera_name,
    vr.recording_uid,
    vr.start_time,
    vr.retention_days,
    vr.expires_at,
    vr.file_size_mb
FROM video_recordings vr
INNER JOIN security_cameras sc ON vr.camera_id = sc.id
WHERE vr.expires_at <= CURRENT_TIMESTAMP + INTERVAL '7 days'
  AND vr.is_archived = FALSE
ORDER BY vr.expires_at;

Step 15: Camera Status Overview

Monitor camera health.

SELECT
    cl.building,
    cl.zone,
    sc.camera_name,
    sc.status,
    sc.resolution,
    COUNT(vr.id) as recent_recordings,
    MAX(vr.end_time) as last_recording
FROM security_cameras sc
INNER JOIN camera_locations cl ON sc.location_id = cl.id
LEFT JOIN video_recordings vr ON sc.id = vr.camera_id
    AND vr.start_time >= CURRENT_DATE - 1
GROUP BY sc.id, cl.building, cl.zone, sc.camera_name, sc.status, sc.resolution
ORDER BY cl.building, cl.zone;

Cleanup (Optional)

DROP TABLE IF EXISTS recording_tags;
DROP TABLE IF EXISTS video_segments;
DROP TABLE IF EXISTS video_recordings;
DROP TABLE IF EXISTS security_cameras;
DROP TABLE IF EXISTS camera_locations;

Expected Outcomes

• Cameras organized by location
• Recordings stored with metadata
• Segments available for streaming
• Tags for quick retrieval
• Storage tracked

Camera Types

Key Concepts Learned

• Surveillance video organization
• Multi-camera management
• Video segmentation
• Retention policies
• Storage monitoring