Facial Recognition Logging

Objective

Create a system for logging facial recognition events, managing enrolled faces, and tracking recognition matches for security and access control applications.

Step 1: Create Recognition Systems Table

Define facial recognition system locations.

CREATE TABLE fr_systems (
    id INTEGER PRIMARY KEY,
    system_id VARCHAR(50) NOT NULL UNIQUE,
    system_name VARCHAR(200),
    location VARCHAR(200),
    purpose VARCHAR(100),
    camera_resolution VARCHAR(20),
    model_version VARCHAR(50),
    min_confidence DECIMAL(3, 2) DEFAULT 0.80,
    is_active BOOLEAN DEFAULT TRUE,
    installed_date DATE,
    last_calibration DATE
);

Step 2: Create Enrolled Faces Table

Store enrolled individuals.

CREATE TABLE enrolled_faces (
    id INTEGER PRIMARY KEY,
    person_id VARCHAR(50) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    person_type VARCHAR(50),
    department VARCHAR(100),
    employee_id VARCHAR(50),
    enrollment_photo IMAGE(JPEG),
    face_encoding TEXT,
    encoding_version VARCHAR(20),
    enrollment_quality DECIMAL(3, 2),
    enrolled_date TIMESTAMP,
    enrolled_by VARCHAR(100),
    status VARCHAR(50) DEFAULT 'active',
    last_recognized TIMESTAMP,
    recognition_count INTEGER DEFAULT 0,
    notes TEXT
);

Step 3: Create Recognition Events Table

Log face detection and matching.

CREATE TABLE recognition_events (
    id INTEGER PRIMARY KEY,
    system_id INTEGER NOT NULL,
    event_time TIMESTAMP NOT NULL,
    detected_face_image IMAGE(JPEG),
    scene_image IMAGE(JPEG),
    face_quality_score DECIMAL(3, 2),
    is_matched BOOLEAN DEFAULT FALSE,
    matched_person_id INTEGER,
    confidence_score DECIMAL(5, 4),
    match_rank INTEGER,
    processing_time_ms INTEGER,
    face_position TEXT,
    face_landmarks TEXT,
    liveness_score DECIMAL(3, 2),
    liveness_passed BOOLEAN,
    action_taken VARCHAR(100),
    FOREIGN KEY (system_id) REFERENCES fr_systems(id),
    FOREIGN KEY (matched_person_id) REFERENCES enrolled_faces(id)
);

Step 4: Create Access Decisions Table

Track access control outcomes.

CREATE TABLE fr_access_decisions (
    id INTEGER PRIMARY KEY,
    event_id INTEGER NOT NULL,
    access_point VARCHAR(100),
    access_requested VARCHAR(50),
    access_granted BOOLEAN,
    denial_reason VARCHAR(100),
    decision_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    override_by VARCHAR(100),
    override_reason TEXT,
    FOREIGN KEY (event_id) REFERENCES recognition_events(id)
);

Step 5: Create Match Candidates Table

Store top match results.

CREATE TABLE match_candidates (
    id INTEGER PRIMARY KEY,
    event_id INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    rank_position INTEGER,
    confidence_score DECIMAL(5, 4),
    is_selected BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (event_id) REFERENCES recognition_events(id),
    FOREIGN KEY (person_id) REFERENCES enrolled_faces(id)
);

Step 6: Insert FR Systems

Add recognition systems.

INSERT INTO fr_systems (id, system_id, system_name, location, purpose, camera_resolution, model_version, min_confidence, installed_date, last_calibration) VALUES
    (1, 'FR-MAIN-001', 'Main Entrance FR', 'Building A Main Entrance', 'access_control', '4K', 'FaceNet-v2.1', 0.85, '2023-06-01', '2024-01-01'),
    (2, 'FR-LOBBY-001', 'Lobby Kiosk FR', 'Building A Lobby', 'visitor_check_in', '1080p', 'FaceNet-v2.1', 0.80, '2023-06-15', '2024-01-01'),
    (3, 'FR-SERVER-001', 'Server Room FR', 'Building A Floor 2', 'high_security', '4K', 'FaceNet-v2.1', 0.92, '2023-07-01', '2024-01-05'),
    (4, 'FR-EXEC-001', 'Executive Suite FR', 'Building A Floor 3', 'access_control', '4K', 'FaceNet-v2.1', 0.90, '2023-07-15', '2024-01-05'),
    (5, 'FR-PARK-001', 'Parking Entry FR', 'Parking Garage A', 'access_control', '1080p', 'FaceNet-v2.0', 0.75, '2023-05-01', '2023-12-01'),
    (6, 'FR-SURV-001', 'Surveillance FR', 'Building A Various', 'monitoring', '1080p', 'FaceNet-v2.1', 0.70, '2023-08-01', '2024-01-01');

Step 7: Insert Enrolled Faces

Add authorized personnel.

INSERT INTO enrolled_faces (id, person_id, first_name, last_name, person_type, department, employee_id, encoding_version, enrollment_quality, enrolled_date, enrolled_by, status, recognition_count) VALUES
    (1, 'FACE-001', 'John', 'Smith', 'employee', 'IT', 'EMP-001', 'v2.1', 0.95, '2023-06-01 10:00:00', 'Security Admin', 'active', 245),
    (2, 'FACE-002', 'Sarah', 'Johnson', 'employee', 'Engineering', 'EMP-002', 'v2.1', 0.92, '2023-06-01 10:30:00', 'Security Admin', 'active', 312),
    (3, 'FACE-003', 'Michael', 'Williams', 'employee', 'Finance', 'EMP-003', 'v2.1', 0.88, '2023-06-02 09:00:00', 'Security Admin', 'active', 198),
    (4, 'FACE-004', 'Emily', 'Brown', 'employee', 'HR', 'EMP-004', 'v2.1', 0.94, '2023-06-02 09:30:00', 'Security Admin', 'active', 287),
    (5, 'FACE-005', 'David', 'Davis', 'employee', 'R&D', 'EMP-005', 'v2.1', 0.91, '2023-06-03 14:00:00', 'Security Admin', 'active', 156),
    (6, 'FACE-006', 'Lisa', 'Martinez', 'employee', 'Executive', 'EMP-006', 'v2.1', 0.96, '2023-06-01 08:00:00', 'Security Admin', 'active', 89),
    (7, 'FACE-007', 'Robert', 'Garcia', 'employee', 'Security', 'EMP-007', 'v2.1', 0.93, '2023-06-01 11:00:00', 'Security Admin', 'active', 425),
    (8, 'FACE-008', 'Jennifer', 'Lee', 'employee', 'IT', 'EMP-008', 'v2.1', 0.90, '2023-06-04 10:00:00', 'Security Admin', 'active', 178);

Step 8: Insert Recognition Events

Add recognition logs.

INSERT INTO recognition_events (id, system_id, event_time, face_quality_score, is_matched, matched_person_id, confidence_score, match_rank, processing_time_ms, face_position, liveness_score, liveness_passed, action_taken) VALUES
    (1, 1, '2024-01-15 08:15:32', 0.92, TRUE, 1, 0.9845, 1, 125, '{"x":320,"y":180,"w":150,"h":180}', 0.98, TRUE, 'access_granted'),
    (2, 1, '2024-01-15 08:22:18', 0.88, TRUE, 2, 0.9712, 1, 132, '{"x":310,"y":175,"w":155,"h":185}', 0.96, TRUE, 'access_granted'),
    (3, 3, '2024-01-15 08:30:45', 0.95, TRUE, 1, 0.9923, 1, 118, '{"x":325,"y":185,"w":145,"h":175}', 0.99, TRUE, 'access_granted'),
    (4, 1, '2024-01-15 09:00:12', 0.72, FALSE, NULL, 0.6234, 0, 145, '{"x":340,"y":190,"w":140,"h":170}', 0.45, FALSE, 'access_denied'),
    (5, 1, '2024-01-15 09:15:30', 0.89, TRUE, 3, 0.9456, 1, 128, '{"x":315,"y":178,"w":152,"h":182}', 0.94, TRUE, 'access_granted'),
    (6, 4, '2024-01-15 09:30:00', 0.94, TRUE, 6, 0.9867, 1, 115, '{"x":322,"y":182,"w":148,"h":178}', 0.97, TRUE, 'access_granted'),
    (7, 6, '2024-01-15 10:45:22', 0.78, TRUE, 5, 0.8234, 1, 142, '{"x":280,"y":160,"w":160,"h":190}', NULL, NULL, 'logged_only'),
    (8, 1, '2024-01-15 12:30:15', 0.91, TRUE, 4, 0.9678, 1, 121, '{"x":318,"y":180,"w":150,"h":180}', 0.95, TRUE, 'access_granted'),
    (9, 2, '2024-01-15 14:00:00', 0.85, FALSE, NULL, 0.4512, 0, 138, '{"x":300,"y":170,"w":155,"h":185}', 0.92, TRUE, 'visitor_flagged'),
    (10, 3, '2024-01-15 02:30:45', 0.88, TRUE, 7, 0.9534, 1, 125, '{"x":320,"y":178,"w":152,"h":182}', 0.96, TRUE, 'access_granted');

Step 9: Insert Access Decisions

Log access outcomes.

INSERT INTO fr_access_decisions (id, event_id, access_point, access_requested, access_granted, denial_reason, override_by, override_reason) VALUES
    (1, 1, 'Main Entrance', 'building_entry', TRUE, NULL, NULL, NULL),
    (2, 2, 'Main Entrance', 'building_entry', TRUE, NULL, NULL, NULL),
    (3, 3, 'Server Room', 'restricted_area', TRUE, NULL, NULL, NULL),
    (4, 4, 'Main Entrance', 'building_entry', FALSE, 'low_confidence_match', NULL, NULL),
    (5, 5, 'Main Entrance', 'building_entry', TRUE, NULL, NULL, NULL),
    (6, 6, 'Executive Suite', 'restricted_area', TRUE, NULL, NULL, NULL),
    (7, 8, 'Main Entrance', 'building_entry', TRUE, NULL, NULL, NULL),
    (8, 10, 'Server Room', 'restricted_area', TRUE, NULL, NULL, NULL);

Step 10: Insert Match Candidates

Store alternative matches.

INSERT INTO match_candidates (id, event_id, person_id, rank_position, confidence_score, is_selected) VALUES
    (1, 1, 1, 1, 0.9845, TRUE),
    (2, 1, 7, 2, 0.7234, FALSE),
    (3, 1, 2, 3, 0.6512, FALSE),
    (4, 4, 5, 1, 0.6234, FALSE),
    (5, 4, 3, 2, 0.5845, FALSE),
    (6, 4, 8, 3, 0.5123, FALSE),
    (7, 9, 4, 1, 0.4512, FALSE),
    (8, 9, 2, 2, 0.4234, FALSE);

Step 11: Get Recognition History for Person

View individual recognition log.

SELECT
    fs.system_name,
    fs.location,
    re.event_time,
    re.confidence_score,
    re.face_quality_score,
    re.liveness_passed,
    re.action_taken
FROM recognition_events re
INNER JOIN fr_systems fs ON re.system_id = fs.id
WHERE re.matched_person_id = 1
ORDER BY re.event_time DESC
LIMIT 20;

Step 12: Daily Recognition Summary

Analyze system activity.

SELECT
    fs.system_name,
    fs.purpose,
    COUNT(re.id) as total_events,
    COUNT(CASE WHEN re.is_matched THEN 1 END) as matches,
    COUNT(CASE WHEN NOT re.is_matched THEN 1 END) as non_matches,
    AVG(re.confidence_score) as avg_confidence,
    AVG(re.processing_time_ms) as avg_processing_ms
FROM fr_systems fs
LEFT JOIN recognition_events re ON fs.id = re.system_id
    AND re.event_time >= CURRENT_DATE
WHERE fs.is_active = TRUE
GROUP BY fs.id, fs.system_name, fs.purpose
ORDER BY total_events DESC;

Step 13: Failed Recognition Analysis

Review non-matches.

SELECT
    fs.system_name,
    re.event_time,
    re.face_quality_score,
    re.liveness_score,
    re.liveness_passed,
    re.action_taken,
    fad.denial_reason
FROM recognition_events re
INNER JOIN fr_systems fs ON re.system_id = fs.id
LEFT JOIN fr_access_decisions fad ON re.id = fad.event_id
WHERE re.is_matched = FALSE
  AND re.event_time >= CURRENT_DATE - 7
ORDER BY re.event_time DESC;

Step 14: High-Security Area Activity

Monitor restricted zones.

SELECT
    ef.first_name || ' ' || ef.last_name as person,
    ef.department,
    fs.system_name,
    re.event_time,
    re.confidence_score,
    fad.access_granted,
    fad.access_point
FROM recognition_events re
INNER JOIN fr_systems fs ON re.system_id = fs.id
INNER JOIN enrolled_faces ef ON re.matched_person_id = ef.id
LEFT JOIN fr_access_decisions fad ON re.id = fad.event_id
WHERE fs.purpose = 'high_security'
  AND re.event_time >= CURRENT_DATE
ORDER BY re.event_time DESC;

Step 15: Enrollment Quality Report

Check face enrollment health.

SELECT
    ef.first_name || ' ' || ef.last_name as person,
    ef.department,
    ef.enrollment_quality,
    ef.encoding_version,
    ef.enrolled_date,
    ef.recognition_count,
    ef.last_recognized,
    CASE
        WHEN ef.enrollment_quality >= 0.90 THEN 'Excellent'
        WHEN ef.enrollment_quality >= 0.80 THEN 'Good'
        WHEN ef.enrollment_quality >= 0.70 THEN 'Fair'
        ELSE 'Needs Re-enrollment'
    END as quality_status
FROM enrolled_faces ef
WHERE ef.status = 'active'
ORDER BY ef.enrollment_quality;

Cleanup (Optional)

DROP TABLE IF EXISTS match_candidates;
DROP TABLE IF EXISTS fr_access_decisions;
DROP TABLE IF EXISTS recognition_events;
DROP TABLE IF EXISTS enrolled_faces;
DROP TABLE IF EXISTS fr_systems;

Expected Outcomes

• Faces enrolled
• Recognition logged
• Matches tracked
• Access decisions recorded
• Quality monitored

Liveness Detection

Key Concepts Learned

• Face enrollment management
• Recognition event logging
• Confidence scoring
• Liveness detection
• Access decision tracking