Design an Attendance System

Problem Design an attendance system for an institution with role-based views (teacher, dean, student), per-course attendance tracking, and a projection of how many more classes a student must attend to reach 75%. Include the login/authorization design, DB structure, APIs and core logic.

Functional requirements

• Roles (student, teacher, dean, admin) with a different view and permission set per role.
• Teachers and students both opt into courses; a teacher marks attendance for a session.
• Display per-course attendance to the student, their teachers, and the dean.
• Show a student how many more consecutive classes they must attend to reach 75%.
• Login and session management, authorizing each role into its own view.
• Attendance correction/regularization with an audit trail.

Non-functional requirements

• ~30,000 students, ~2,000 teachers, ~4,000 course offerings per term.
• Attendance volume: 30,000 students x 5 courses x ~60 sessions ≈ 9M records/term, ~90M over a 10-year retention.
• Load is extremely bursty: marking clusters in the 10 minutes after each class slot → ~2,000 writes/min at peak; ~500 concurrent users.
• Attendance-percentage read p95 < 200 ms; dean's institution-wide dashboard p95 < 2 s.
• Attendance is a consequential record (it gates exam eligibility): durable, auditable, no silent edits.

Key components

• Schema: Users (with role), Courses, CourseOfferings (course + term + section + teacher), Enrollments (student x offering), Sessions (offering + date + slot), AttendanceRecords (session x student x status), plus AttendanceAudit for corrections.
• APIs: POST /sessions/:id/attendance (bulk mark), GET /students/:id/attendance?course=, GET /offerings/:id/attendance, GET /students/:id/attendance/projection.
• Auth: password hashing with bcrypt/argon2id, session tokens or short-lived JWTs in HttpOnly+Secure+SameSite cookies, role claims resolved server-side on every request.
• Authorization middleware: RBAC checks enforced server-side per resource — a student may read only their own records, a teacher only their offerings, a dean their department.
• Percentage computation: a cached/materialized (student, offering) → (attended, held) counter maintained on write, since recomputing from 9M rows per page load will not hold.

Deep dives / trade-offs

• The 75% projection logic: given attended A of H held sessions with R remaining, solve (A + x) / (H + x) >= 0.75 for the smallest integer x. Note the trap — if the required x exceeds R, the target is already unreachable, and the UI must say so rather than showing an impossible number. Also decide whether the denominator is sessions held so far or total planned for the term; the two give very different answers mid-semester and this is exactly the ambiguity to surface.
• Computed vs stored percentage: computing on read is always correct but scans the attendance table per request. A maintained counter is fast but drifts when corrections are backdated, so it needs a reconciliation job. Given corrections are common in this domain, discuss why an append-only record plus a projection beats an in-place mutable count.
• RBAC modelling: roles are not a single column for long — a dean is also a teacher, a teacher may be a student in another program. Discuss a user_roles join with scope (role + department/offering) versus a flat enum, and why the flat enum breaks on the first real edge case.
• Authorization at the query, not the handler: filtering by role in the API layer while the query fetches everything is the classic IDOR bug. Scope must be pushed into the query predicate.
• Bulk marking concurrency: two teachers (or a teacher and a TA) marking the same session must not double-write — a unique constraint on (session_id, student_id) with upsert semantics, not a check-then-insert.
• Idempotency on bulk submit: mobile clients on campus wifi retry; a resubmitted roster must not duplicate records.