QR Attendance System: Architecture & Implementation

Architecture & Data Flow

textCopy
[Camera Sensor]
       ↓ (OpenCV VideoCapture)
[Frame BGR Matrix]
       ↓
[pyzbar.decode()] ─── (Find QR Position & Read Payload)
       ↓
[Payload Parser & Schema Validation]
       ↓
[Duplicate Check Query (SQLite)] ── (Already recorded for current date/session?)
       ├─ If Duplicate ──> Render Amber Alert Overlay (Skip Write)
       └─ If New Valid  ──> Begin SQL Transaction
                                 ↓
                            INSERT INTO attendance (student_id, session_id, scan_time)
                                 ↓
                            Render Green Success Overlay & Audio Beep

Database Schema Design

The SQLite database maintains relational integrity with strict constraints:

sqlCopy
CREATE TABLE IF NOT EXISTS students (
    student_id TEXT PRIMARY KEY,
    full_name TEXT NOT NULL,
    department TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS attendance_logs (
    log_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id TEXT NOT NULL,
    session_date DATE NOT NULL,
    scan_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status TEXT NOT NULL CHECK(status IN ('PRESENT', 'LATE')),
    FOREIGN KEY(student_id) REFERENCES students(student_id),
    UNIQUE(student_id, session_date)
);

Verification & Key Takeaways

• Performance: OpenCV frame ingestion runs cleanly on modest commodity hardware.
• Integrity: Enforcing UNIQUE(student_id, session_date) at the SQLite schema level guarantees that concurrent or repeated rapid scans cannot corrupt attendance counts.
• Future Work: Implementing HMAC-SHA256 time-based rolling tokens in generated QR codes to prevent unauthorized screenshot sharing.