Access Control Logs with Images

Objective

Create a system for logging access control events with associated badge photos and entry camera images, enabling visual verification and audit trails.

Step 1: Create Access Points Table

Define controlled entry points.

CREATE TABLE access_points (
    id INTEGER PRIMARY KEY,
    point_id VARCHAR(50) NOT NULL UNIQUE,
    point_name VARCHAR(200),
    building VARCHAR(100),
    floor VARCHAR(20),
    area_type VARCHAR(50),
    security_level INTEGER DEFAULT 1,
    has_camera BOOLEAN DEFAULT TRUE,
    has_intercom BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Personnel Table

Store authorized personnel.

CREATE TABLE access_personnel (
    id INTEGER PRIMARY KEY,
    employee_id VARCHAR(50) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    department VARCHAR(100),
    title VARCHAR(100),
    badge_number VARCHAR(50) UNIQUE,
    badge_photo IMAGE(JPEG),
    access_level INTEGER DEFAULT 1,
    status VARCHAR(50) DEFAULT 'active',
    hire_date DATE,
    badge_issued DATE,
    badge_expires DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create Access Permissions Table

Define who can access what.

CREATE TABLE access_permissions (
    id INTEGER PRIMARY KEY,
    personnel_id INTEGER NOT NULL,
    access_point_id INTEGER NOT NULL,
    permission_type VARCHAR(50) DEFAULT 'standard',
    valid_from TIME,
    valid_to TIME,
    valid_days VARCHAR(20) DEFAULT '1234567',
    granted_by VARCHAR(100),
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (personnel_id) REFERENCES access_personnel(id),
    FOREIGN KEY (access_point_id) REFERENCES access_points(id)
);

Step 4: Create Access Events Table

Log access attempts.

CREATE TABLE access_events (
    id INTEGER PRIMARY KEY,
    access_point_id INTEGER NOT NULL,
    personnel_id INTEGER,
    badge_number VARCHAR(50),
    event_time TIMESTAMP NOT NULL,
    event_type VARCHAR(50),
    direction VARCHAR(20),
    access_granted BOOLEAN,
    denial_reason VARCHAR(100),
    entry_image IMAGE(JPEG),
    verification_method VARCHAR(50),
    reader_id VARCHAR(50),
    FOREIGN KEY (access_point_id) REFERENCES access_points(id),
    FOREIGN KEY (personnel_id) REFERENCES access_personnel(id)
);

Step 5: Create Visitor Logs Table

Track visitor access.

CREATE TABLE visitor_logs (
    id INTEGER PRIMARY KEY,
    visitor_id VARCHAR(50) UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    company VARCHAR(200),
    visitor_photo IMAGE(JPEG),
    host_employee_id INTEGER,
    purpose VARCHAR(255),
    badge_number VARCHAR(50),
    check_in_time TIMESTAMP,
    check_out_time TIMESTAMP,
    access_areas TEXT,
    id_verified BOOLEAN DEFAULT FALSE,
    nda_signed BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (host_employee_id) REFERENCES access_personnel(id)
);

Step 6: Insert Access Points

Add controlled doors.

INSERT INTO access_points (id, point_id, point_name, building, floor, area_type, security_level, has_camera, has_intercom) VALUES
    (1, 'AP-MAIN-001', 'Main Entrance', 'Building A', 'Ground', 'entrance', 1, TRUE, TRUE),
    (2, 'AP-LOBBY-001', 'Lobby to Office', 'Building A', 'Ground', 'interior', 2, TRUE, FALSE),
    (3, 'AP-SERVER-001', 'Server Room Entry', 'Building A', '2', 'restricted', 5, TRUE, TRUE),
    (4, 'AP-EXEC-001', 'Executive Suite', 'Building A', '3', 'restricted', 4, TRUE, TRUE),
    (5, 'AP-LAB-001', 'R&D Laboratory', 'Building B', '1', 'restricted', 4, TRUE, TRUE),
    (6, 'AP-PARK-001', 'Parking Garage Entry', 'Parking', 'P1', 'vehicle', 1, TRUE, TRUE),
    (7, 'AP-LOAD-001', 'Loading Dock', 'Building A', 'Ground', 'delivery', 2, TRUE, TRUE),
    (8, 'AP-EMRG-001', 'Emergency Exit North', 'Building A', 'Ground', 'emergency', 1, TRUE, FALSE);

Step 7: Insert Personnel

Add authorized employees.

INSERT INTO access_personnel (id, employee_id, first_name, last_name, department, title, badge_number, access_level, status, hire_date, badge_issued, badge_expires) VALUES
    (1, 'EMP-001', 'John', 'Smith', 'IT', 'System Administrator', 'B-10001', 5, 'active', '2020-03-15', '2023-01-01', '2025-01-01'),
    (2, 'EMP-002', 'Sarah', 'Johnson', 'Engineering', 'Senior Engineer', 'B-10002', 4, 'active', '2019-06-01', '2023-01-01', '2025-01-01'),
    (3, 'EMP-003', 'Michael', 'Williams', 'Finance', 'Accountant', 'B-10003', 2, 'active', '2021-09-15', '2023-01-01', '2025-01-01'),
    (4, 'EMP-004', 'Emily', 'Brown', 'HR', 'HR Manager', 'B-10004', 3, 'active', '2018-04-01', '2023-01-01', '2025-01-01'),
    (5, 'EMP-005', 'David', 'Davis', 'R&D', 'Research Scientist', 'B-10005', 4, 'active', '2022-01-10', '2023-01-01', '2025-01-01'),
    (6, 'EMP-006', 'Lisa', 'Martinez', 'Executive', 'CEO', 'B-10006', 5, 'active', '2015-01-01', '2023-01-01', '2025-01-01'),
    (7, 'EMP-007', 'Robert', 'Garcia', 'Security', 'Security Officer', 'B-10007', 3, 'active', '2020-08-01', '2023-01-01', '2025-01-01'),
    (8, 'EMP-008', 'Jennifer', 'Lee', 'IT', 'Network Engineer', 'B-10008', 4, 'active', '2021-03-20', '2023-01-01', '2025-01-01');

Step 8: Insert Access Permissions

Grant access rights.

INSERT INTO access_permissions (id, personnel_id, access_point_id, permission_type, valid_from, valid_to, valid_days, granted_by) VALUES
    -- Main entrance - everyone
    (1, 1, 1, 'standard', '06:00:00', '22:00:00', '1234567', 'Security Admin'),
    (2, 2, 1, 'standard', '06:00:00', '22:00:00', '1234567', 'Security Admin'),
    (3, 3, 1, 'standard', '07:00:00', '19:00:00', '12345', 'Security Admin'),
    (4, 4, 1, 'standard', '06:00:00', '22:00:00', '1234567', 'Security Admin'),
    (5, 5, 1, 'standard', '06:00:00', '22:00:00', '1234567', 'Security Admin'),
    (6, 6, 1, '24_7', NULL, NULL, '1234567', 'Security Admin'),
    (7, 7, 1, '24_7', NULL, NULL, '1234567', 'Security Admin'),
    (8, 8, 1, 'standard', '06:00:00', '22:00:00', '1234567', 'Security Admin'),
    -- Server room - IT only
    (9, 1, 3, '24_7', NULL, NULL, '1234567', 'IT Director'),
    (10, 8, 3, 'standard', '08:00:00', '18:00:00', '12345', 'IT Director'),
    -- Executive suite
    (11, 6, 4, '24_7', NULL, NULL, '1234567', 'Security Admin'),
    (12, 4, 4, 'standard', '08:00:00', '18:00:00', '12345', 'HR Director'),
    -- R&D Lab
    (13, 5, 5, 'standard', '06:00:00', '22:00:00', '1234567', 'R&D Director'),
    (14, 2, 5, 'standard', '06:00:00', '22:00:00', '1234567', 'R&D Director');

Step 9: Insert Access Events

Log entry attempts.

INSERT INTO access_events (id, access_point_id, personnel_id, badge_number, event_time, event_type, direction, access_granted, denial_reason, verification_method, reader_id) VALUES
    (1, 1, 1, 'B-10001', '2024-01-15 08:15:32', 'badge_tap', 'entry', TRUE, NULL, 'badge', 'RDR-MAIN-001'),
    (2, 1, 2, 'B-10002', '2024-01-15 08:22:45', 'badge_tap', 'entry', TRUE, NULL, 'badge', 'RDR-MAIN-001'),
    (3, 3, 1, 'B-10001', '2024-01-15 08:30:10', 'badge_tap', 'entry', TRUE, NULL, 'badge_pin', 'RDR-SERVER-001'),
    (4, 3, 3, 'B-10003', '2024-01-15 09:15:00', 'badge_tap', 'entry', FALSE, 'insufficient_access_level', 'badge', 'RDR-SERVER-001'),
    (5, 1, 3, 'B-10003', '2024-01-15 09:00:22', 'badge_tap', 'entry', TRUE, NULL, 'badge', 'RDR-MAIN-001'),
    (6, 5, 5, 'B-10005', '2024-01-15 08:45:18', 'badge_tap', 'entry', TRUE, NULL, 'badge_biometric', 'RDR-LAB-001'),
    (7, 4, 6, 'B-10006', '2024-01-15 09:30:00', 'badge_tap', 'entry', TRUE, NULL, 'badge_pin', 'RDR-EXEC-001'),
    (8, 1, NULL, 'B-UNKNOWN', '2024-01-15 10:15:45', 'badge_tap', 'entry', FALSE, 'invalid_badge', 'badge', 'RDR-MAIN-001'),
    (9, 3, 1, 'B-10001', '2024-01-15 12:30:00', 'badge_tap', 'exit', TRUE, NULL, 'badge', 'RDR-SERVER-002'),
    (10, 1, 1, 'B-10001', '2024-01-15 17:45:30', 'badge_tap', 'exit', TRUE, NULL, 'badge', 'RDR-MAIN-002');

Step 10: Insert Visitor Logs

Track visitors.

INSERT INTO visitor_logs (id, visitor_id, first_name, last_name, company, host_employee_id, purpose, badge_number, check_in_time, check_out_time, access_areas, id_verified, nda_signed) VALUES
    (1, 'VIS-20240115-001', 'Thomas', 'Anderson', 'Vendor Corp', 1, 'Server maintenance', 'V-001', '2024-01-15 10:00:00', '2024-01-15 14:30:00', 'Lobby,Server Room', TRUE, TRUE),
    (2, 'VIS-20240115-002', 'Amanda', 'White', 'Consulting Inc', 4, 'HR consultation', 'V-002', '2024-01-15 09:30:00', '2024-01-15 12:00:00', 'Lobby,HR Office', TRUE, FALSE),
    (3, 'VIS-20240115-003', 'James', 'Wilson', 'Audit Partners', 3, 'Financial audit', 'V-003', '2024-01-15 08:00:00', NULL, 'Lobby,Finance,Records', TRUE, TRUE);

Step 11: Get Access History for Person

View individual access log.

SELECT
    ap.point_name,
    ae.event_time,
    ae.event_type,
    ae.direction,
    ae.access_granted,
    ae.denial_reason,
    ae.verification_method
FROM access_events ae
INNER JOIN access_points ap ON ae.access_point_id = ap.id
WHERE ae.personnel_id = 1
ORDER BY ae.event_time DESC
LIMIT 20;

Step 12: Get Access Point Activity

View door activity.

SELECT
    COALESCE(apers.first_name || ' ' || apers.last_name, 'Unknown') as person,
    ae.badge_number,
    ae.event_time,
    ae.direction,
    ae.access_granted,
    ae.denial_reason
FROM access_events ae
LEFT JOIN access_personnel apers ON ae.personnel_id = apers.id
WHERE ae.access_point_id = 1
  AND ae.event_time >= CURRENT_DATE
ORDER BY ae.event_time DESC;

Step 13: Access Denials Report

Review failed attempts.

SELECT
    ap.point_name,
    ap.security_level,
    ae.badge_number,
    COALESCE(apers.first_name || ' ' || apers.last_name, 'Unknown Badge') as person,
    ae.event_time,
    ae.denial_reason
FROM access_events ae
INNER JOIN access_points ap ON ae.access_point_id = ap.id
LEFT JOIN access_personnel apers ON ae.personnel_id = apers.id
WHERE ae.access_granted = FALSE
ORDER BY ae.event_time DESC;

Step 14: Current Building Occupancy

Track who is inside.

SELECT
    apers.first_name || ' ' || apers.last_name as person,
    apers.department,
    MAX(CASE WHEN ae.direction = 'entry' THEN ae.event_time END) as last_entry,
    MAX(ae.event_time) as last_event,
    CASE
        WHEN MAX(CASE WHEN ae.direction = 'entry' THEN ae.event_time END) >
             COALESCE(MAX(CASE WHEN ae.direction = 'exit' THEN ae.event_time END), '1970-01-01')
        THEN 'Inside'
        ELSE 'Outside'
    END as status
FROM access_personnel apers
LEFT JOIN access_events ae ON apers.id = ae.personnel_id
    AND ae.event_time >= CURRENT_DATE
    AND ae.access_granted = TRUE
WHERE apers.status = 'active'
GROUP BY apers.id, apers.first_name, apers.last_name, apers.department
ORDER BY apers.department, apers.last_name;

Step 15: Visitor Status

Track active visitors.

SELECT
    vl.first_name || ' ' || vl.last_name as visitor,
    vl.company,
    apers.first_name || ' ' || apers.last_name as host,
    vl.purpose,
    vl.check_in_time,
    vl.check_out_time,
    vl.access_areas,
    CASE
        WHEN vl.check_out_time IS NULL THEN 'On-site'
        ELSE 'Checked out'
    END as status
FROM visitor_logs vl
LEFT JOIN access_personnel apers ON vl.host_employee_id = apers.id
WHERE vl.check_in_time >= CURRENT_DATE
ORDER BY vl.check_in_time DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS visitor_logs;
DROP TABLE IF EXISTS access_events;
DROP TABLE IF EXISTS access_permissions;
DROP TABLE IF EXISTS access_personnel;
DROP TABLE IF EXISTS access_points;

Expected Outcomes

• Access points defined
• Personnel tracked
• Permissions managed
• Events logged
• Visitors monitored

Security Levels

Key Concepts Learned

• Access control modeling
• Permission management
• Event logging with images
• Occupancy tracking
• Visitor management