attendx-api / app /models /models.py
iamudit02's picture
feat: production readiness, admin master password protection, teacher registration approval gate, and data integrity safeguards
544faf7
Raw History Blame Contribute Delete
6.7 kB
import datetime
from sqlalchemy import Column, Integer, String, Boolean, Float, DateTime, ForeignKey, Text, JSON
from sqlalchemy.orm import relationship
from app.database.session import Base
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(100), nullable=False)
email = Column(String(100), unique=True, index=True, nullable=False)
password_hash = Column(String(255), nullable=False)
role = Column(String(20), default="TEACHER") # TEACHER or ADMIN
is_approved = Column(Boolean, default=False, nullable=False) # Requires Admin approval for teachers
approved_by = Column(Integer, ForeignKey("users.id"), nullable=True)
approved_at = Column(DateTime, nullable=True)
created_at = Column(DateTime, default=datetime.datetime.utcnow)
sessions = relationship("AttendanceSession", back_populates="teacher")
class Class(Base):
__tablename__ = "classes"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(100), nullable=False) # e.g. CSE
section = Column(String(20), nullable=False) # e.g. Section A
academic_year = Column(String(20), nullable=False) # e.g. 2026-27
students = relationship("Student", back_populates="class_obj", cascade="all, delete-orphan")
subjects = relationship("Subject", back_populates="class_obj", cascade="all, delete-orphan")
sessions = relationship("AttendanceSession", back_populates="class_obj")
class Subject(Base):
__tablename__ = "subjects"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(100), nullable=False) # Database Management Systems
code = Column(String(20), nullable=False) # DBMS101
class_id = Column(Integer, ForeignKey("classes.id"), nullable=False)
class_obj = relationship("Class", back_populates="subjects")
enrollments = relationship("Enrollment", back_populates="subject", cascade="all, delete-orphan")
sessions = relationship("AttendanceSession", back_populates="subject")
class Student(Base):
__tablename__ = "students"
id = Column(Integer, primary_key=True, index=True)
student_id = Column(String(50), unique=True, index=True, nullable=False) # STU001
name = Column(String(100), nullable=False)
roll_number = Column(String(50), nullable=False)
class_id = Column(Integer, ForeignKey("classes.id"), nullable=False)
email = Column(String(100), nullable=True)
active = Column(Boolean, default=True)
# Student portal login credentials
password_hash = Column(String(255), nullable=True) # Set when student account is activated
face_registration_complete = Column(Boolean, default=False)
created_at = Column(DateTime, default=datetime.datetime.utcnow)
class_obj = relationship("Class", back_populates="students")
embeddings = relationship("FaceEmbedding", back_populates="student", cascade="all, delete-orphan")
enrollments = relationship("Enrollment", back_populates="student", cascade="all, delete-orphan")
attendance_records = relationship("AttendanceRecord", back_populates="student")
class Enrollment(Base):
__tablename__ = "enrollments"
id = Column(Integer, primary_key=True, index=True)
student_id = Column(Integer, ForeignKey("students.id"), nullable=False)
subject_id = Column(Integer, ForeignKey("subjects.id"), nullable=False)
student = relationship("Student", back_populates="enrollments")
subject = relationship("Subject", back_populates="enrollments")
class FaceEmbedding(Base):
__tablename__ = "face_embeddings"
id = Column(Integer, primary_key=True, index=True)
student_id = Column(Integer, ForeignKey("students.id"), nullable=False)
embedding = Column(JSON, nullable=False) # Array of floats representing 128-d embedding
source_image = Column(String(255), nullable=True)
angle_label = Column(String(50), nullable=True) # e.g. 'front', 'left', 'right', 'chin_down', 'smile'
source = Column(String(50), default="portal_scan") # 'portal_scan', 'teacher_verified', 'manual_upload'
created_at = Column(DateTime, default=datetime.datetime.utcnow)
student = relationship("Student", back_populates="embeddings")
class AttendanceSession(Base):
__tablename__ = "attendance_sessions"
id = Column(Integer, primary_key=True, index=True)
class_id = Column(Integer, ForeignKey("classes.id"), nullable=False)
subject_id = Column(Integer, ForeignKey("subjects.id"), nullable=False)
teacher_id = Column(Integer, ForeignKey("users.id"), nullable=False)
date = Column(String(20), nullable=False) # YYYY-MM-DD
start_time = Column(String(20), nullable=False) # HH:MM
image_path = Column(String(255), nullable=True)
created_at = Column(DateTime, default=datetime.datetime.utcnow)
class_obj = relationship("Class", back_populates="sessions")
subject = relationship("Subject", back_populates="sessions")
teacher = relationship("User", back_populates="sessions")
records = relationship("AttendanceRecord", back_populates="session", cascade="all, delete-orphan")
class AttendanceRecord(Base):
__tablename__ = "attendance_records"
id = Column(Integer, primary_key=True, index=True)
session_id = Column(Integer, ForeignKey("attendance_sessions.id"), nullable=False)
student_id = Column(Integer, ForeignKey("students.id"), nullable=False)
status = Column(String(20), nullable=False) # PRESENT, ABSENT
confidence = Column(Float, default=0.0)
verification_status = Column(String(30), default="AUTO") # AUTO, TEACHER_VERIFIED, MANUAL
created_at = Column(DateTime, default=datetime.datetime.utcnow)
session = relationship("AttendanceSession", back_populates="records")
student = relationship("Student", back_populates="attendance_records")
class AttendanceAuditLog(Base):
__tablename__ = "attendance_audit_logs"
id = Column(Integer, primary_key=True, index=True)
session_id = Column(Integer, ForeignKey("attendance_sessions.id"), nullable=False)
student_id = Column(Integer, ForeignKey("students.id"), nullable=False)
teacher_id = Column(Integer, ForeignKey("users.id"), nullable=False)
original_status = Column(String(20), nullable=True) # AI proposed status
original_score = Column(Float, nullable=True) # AI match score
final_status = Column(String(20), nullable=False) # Teacher confirmed status
reason = Column(String(255), nullable=True) # Reason for change / override
created_at = Column(DateTime, default=datetime.datetime.utcnow)
session = relationship("AttendanceSession")
student = relationship("Student")
teacher = relationship("User")