Hugging Face's logo

Spaces:
Devashishraghav
/
gujarat-reading-programv2
Sleeping

App Files Community
gujarat-reading-programv2 / backend /models.py
Devashishraghav's picture
Devashishraghav
Upload folder using huggingface_hub
4cd7b8f verified
Raw History Blame Contribute Delete
15.4 kB
"""
DRP Backend — SQLAlchemy Models
All 12+ tables as defined in the PRD and implementation plan.
"""
import uuid
from datetime import datetime
from sqlalchemy import (
Column, String, Integer, Float, Boolean, DateTime, Text,
ForeignKey, JSON
)
from sqlalchemy.orm import relationship
from database import Base
def generate_uuid():
return str(uuid.uuid4())
# ---------------------------------------------------------------------------
# Geographic / Organisational Hierarchy
# ---------------------------------------------------------------------------
class Programme(Base):
__tablename__ = "programme"
programme_id = Column(String, primary_key=True, default=generate_uuid)
name = Column(String, nullable=False)
state_code = Column(String, nullable=False)
year = Column(Integer, nullable=False)
created_at = Column(DateTime, default=datetime.utcnow)
schools = relationship("School", back_populates="programme")
class District(Base):
__tablename__ = "district"
district_id = Column(String, primary_key=True, default=generate_uuid)
name = Column(String, nullable=False)
state_code = Column(String, nullable=False)
schools = relationship("School", back_populates="district")
class School(Base):
__tablename__ = "school"
school_id = Column(String, primary_key=True, default=generate_uuid)
udise_code = Column(String, unique=True, nullable=False)
name = Column(String, nullable=False)
district_id = Column(String, ForeignKey("district.district_id"))
block = Column(String)
programme_id = Column(String, ForeignKey("programme.programme_id"))
whatsapp_group_link = Column(String, nullable=True) # For teacher onboarding
created_at = Column(DateTime, default=datetime.utcnow)
programme = relationship("Programme", back_populates="schools")
district = relationship("District", back_populates="schools")
classes = relationship("ClassAccount", back_populates="school")
teachers = relationship("TeacherAccount", back_populates="school")
# ---------------------------------------------------------------------------
# Class & Teacher
# ---------------------------------------------------------------------------
class ClassAccount(Base):
__tablename__ = "class_account"
class_id = Column(String, primary_key=True, default=generate_uuid)
school_id = Column(String, ForeignKey("school.school_id"), nullable=False)
grade = Column(Integer, nullable=False)
section = Column(String, nullable=False)
year = Column(Integer, nullable=False)
state = Column(String, default="not_started") # not_started | in_progress | completed
current_cluster_id = Column(String, ForeignKey("cluster.cluster_id"), nullable=True)
reading_level = Column(String, nullable=True) # Set during level selection
whatsapp_group_link = Column(String, nullable=True) # Class-specific link
created_at = Column(DateTime, default=datetime.utcnow)
school = relationship("School", back_populates="classes")
journey = relationship("ClassJourney", back_populates="class_account", uselist=False)
sessions = relationship("SessionLog", back_populates="class_account")
achievements = relationship("Achievement", back_populates="class_account")
teacher_mappings = relationship("TeacherClassMapping", back_populates="class_account")
class TeacherAccount(Base):
__tablename__ = "teacher_account"
teacher_id = Column(String, primary_key=True, default=generate_uuid)
name = Column(String, nullable=False)
mobile_hash = Column(String, nullable=True)
phone = Column(String, nullable=True) # For prototype display
email = Column(String, unique=True, nullable=True) # For email login
password_hash = Column(String, nullable=True) # For email login
school_id = Column(String, ForeignKey("school.school_id"), nullable=True)
role = Column(String, default="teacher") # teacher | substitute
activation_token = Column(String, unique=True, nullable=True)
is_active = Column(Boolean, default=False)
has_seen_onboarding = Column(Boolean, default=False) # Onboarding wizard flag
created_at = Column(DateTime, default=datetime.utcnow)
school = relationship("School", back_populates="teachers")
class_mappings = relationship("TeacherClassMapping", back_populates="teacher")
sessions = relationship("SessionLog", back_populates="teacher")
class TeacherClassMapping(Base):
__tablename__ = "teacher_class_mapping"
id = Column(String, primary_key=True, default=generate_uuid)
teacher_id = Column(String, ForeignKey("teacher_account.teacher_id"), nullable=False)
class_id = Column(String, ForeignKey("class_account.class_id"), nullable=False)
role = Column(String, default="primary") # primary | substitute
is_default = Column(Boolean, default=False)
teacher = relationship("TeacherAccount", back_populates="class_mappings")
class_account = relationship("ClassAccount", back_populates="teacher_mappings")
# ---------------------------------------------------------------------------
# Content: Books & Clusters
# ---------------------------------------------------------------------------
class Book(Base):
__tablename__ = "book"
book_id = Column(String, primary_key=True, default=generate_uuid)
title = Column(String, nullable=False)
author = Column(String)
illustrator = Column(String)
level = Column(Integer, nullable=False) # 1, 2, 3, 4
theme = Column(String, nullable=False) # Animals, Nature, Friendship, etc.
language = Column(String, default="English")
cover_url = Column(String)
has_readalong = Column(Boolean, default=False)
pages = Column(JSON, default=list) # List of {page_num, text, image_url}
activity_lets_talk = Column(Text, nullable=True) # Discussion prompt (not scored)
activity_lets_understand = Column(JSON, nullable=True) # MCQ: {question, options[], correct}
activity_lets_play = Column(JSON, nullable=True) # MCQ/rhyme/SEL: {question, options[], correct}
read_count = Column(Integer, default=0)
class Cluster(Base):
__tablename__ = "cluster"
cluster_id = Column(String, primary_key=True, default=generate_uuid)
grade = Column(Integer, nullable=False)
level = Column(Integer, nullable=False)
theme = Column(String, nullable=False)
book_ids = Column(JSON, nullable=False) # List of 3 book_ids
connecting_thread = Column(String, nullable=True) # Narrative thread tying the 3 books
secondary_theme = Column(String, nullable=True) # Fallback if insufficient books
# ---------------------------------------------------------------------------
# Journey & Progress
# ---------------------------------------------------------------------------
class ClassJourney(Base):
__tablename__ = "class_journey"
journey_id = Column(String, primary_key=True, default=generate_uuid)
class_id = Column(String, ForeignKey("class_account.class_id"), unique=True, nullable=False)
cluster_sequence = Column(JSON, default=list) # Ordered list of cluster_ids
completed_cluster_ids = Column(JSON, default=list) # Subset of cluster_sequence
current_book_index = Column(Integer, default=0) # 0-2 within current cluster
total_books_read = Column(Integer, default=0)
class_account = relationship("ClassAccount", back_populates="journey")
class SessionLog(Base):
__tablename__ = "session_log"
session_id = Column(String, primary_key=True, default=generate_uuid)
class_id = Column(String, ForeignKey("class_account.class_id"), nullable=False)
teacher_id = Column(String, ForeignKey("teacher_account.teacher_id"), nullable=True)
substitute_name = Column(String, nullable=True)
book_id = Column(String, ForeignKey("book.book_id"), nullable=False)
cluster_id = Column(String, ForeignKey("cluster.cluster_id"), nullable=False)
timestamp = Column(DateTime, default=datetime.utcnow)
device_type = Column(String, default="web")
readalong_done = Column(Boolean, default=False)
activity_submitted = Column(Boolean, default=False)
completed = Column(Boolean, default=False)
class_account = relationship("ClassAccount", back_populates="sessions")
teacher = relationship("TeacherAccount", back_populates="sessions")
activity_submissions = relationship("ActivitySubmission", back_populates="session")
feedback = relationship("Feedback", back_populates="session", uselist=False)
class ActivitySubmission(Base):
__tablename__ = "activity_submission"
submission_id = Column(String, primary_key=True, default=generate_uuid)
session_id = Column(String, ForeignKey("session_log.session_id"), nullable=False)
activity_type = Column(String, nullable=False) # lets_understand | lets_play
selected_option = Column(String, nullable=False)
is_correct = Column(Boolean, nullable=False)
score = Column(Float, default=0.0)
submitted_at = Column(DateTime, default=datetime.utcnow)
session = relationship("SessionLog", back_populates="activity_submissions")
class Feedback(Base):
__tablename__ = "feedback"
feedback_id = Column(String, primary_key=True, default=generate_uuid)
session_id = Column(String, ForeignKey("session_log.session_id"), unique=True, nullable=False)
feedback_emoji = Column(String, nullable=True) # e.g., 😊, 😐, 😟
feedback_quality = Column(String, nullable=True) # e.g., excellent, good, needs_work
session = relationship("SessionLog", back_populates="feedback")
class Achievement(Base):
__tablename__ = "achievement"
achievement_id = Column(String, primary_key=True, default=generate_uuid)
class_id = Column(String, ForeignKey("class_account.class_id"), nullable=False)
type = Column(String, nullable=False) # cluster_complete | programme_complete
badge_key = Column(String, nullable=True)
earned_at = Column(DateTime, default=datetime.utcnow)
class_account = relationship("ClassAccount", back_populates="achievements")
# ---------------------------------------------------------------------------
# Admin & Programme Team
# ---------------------------------------------------------------------------
class AdminAccount(Base):
__tablename__ = "admin_account"
admin_id = Column(String, primary_key=True, default=generate_uuid)
name = Column(String, nullable=False)
email = Column(String, unique=True, nullable=False)
password_hash = Column(String, nullable=False)
role = Column(String, nullable=False) # school_admin | district_admin | state_admin | programme_team
school_id = Column(String, ForeignKey("school.school_id"), nullable=True)
district_id = Column(String, ForeignKey("district.district_id"), nullable=True)
state_code = Column(String, nullable=True)
created_at = Column(DateTime, default=datetime.utcnow)
class EscalationTicket(Base):
__tablename__ = "escalation_ticket"
ticket_id = Column(String, primary_key=True, default=generate_uuid)
type = Column(String, nullable=False) # content | technical | access | other
severity = Column(String, default="medium") # low | medium | high | critical
school_id = Column(String, ForeignKey("school.school_id"), nullable=True)
class_id = Column(String, ForeignKey("class_account.class_id"), nullable=True)
description = Column(Text, nullable=False)
status = Column(String, default="open") # open | in_progress | resolved | closed
created_by = Column(String, nullable=True)
assigned_to = Column(String, nullable=True)
created_at = Column(DateTime, default=datetime.utcnow)
resolved_at = Column(DateTime, nullable=True)
class SystemHealthLog(Base):
__tablename__ = "system_health_log"
log_id = Column(String, primary_key=True, default=generate_uuid)
timestamp = Column(DateTime, default=datetime.utcnow)
api_latency_ms = Column(Float, default=0.0)
error_count = Column(Integer, default=0)
active_sessions = Column(Integer, default=0)
sync_queue_depth = Column(Integer, default=0)
uptime_percent = Column(Float, default=100.0)
class EventLog(Base):
__tablename__ = "event_log"
event_id = Column(String, primary_key=True, default=generate_uuid)
event_type = Column(String, nullable=False) # page_view | book_open | activity_submit | etc.
actor_type = Column(String, nullable=True) # teacher | admin | system
actor_id = Column(String, nullable=True)
metadata_json = Column(JSON, nullable=True)
timestamp = Column(DateTime, default=datetime.utcnow)
class Notification(Base):
__tablename__ = "notification"
notification_id = Column(String, primary_key=True, default=generate_uuid)
teacher_id = Column(String, ForeignKey("teacher_account.teacher_id"), nullable=True)
class_id = Column(String, ForeignKey("class_account.class_id"), nullable=True)
type = Column(String, nullable=False) # reminder | nudge | alert
message = Column(Text)
status = Column(String, default="pending") # pending | sent | read
created_at = Column(DateTime, default=datetime.utcnow)
# ---------------------------------------------------------------------------
# Field Staff
# ---------------------------------------------------------------------------
class FieldStaff(Base):
__tablename__ = "field_staff"
staff_id = Column(String, primary_key=True, default=generate_uuid)
name = Column(String, nullable=False)
phone = Column(String, nullable=True)
email = Column(String, unique=True, nullable=False)
password_hash = Column(String, nullable=False)
district_id = Column(String, ForeignKey("district.district_id"), nullable=False)
assigned_blocks = Column(JSON, default=list) # List of block names
is_active = Column(Boolean, default=True)
created_at = Column(DateTime, default=datetime.utcnow)
school_mappings = relationship("SchoolStaffMapping", back_populates="staff")
class SchoolStaffMapping(Base):
__tablename__ = "school_staff_mapping"
id = Column(String, primary_key=True, default=generate_uuid)
staff_id = Column(String, ForeignKey("field_staff.staff_id"), nullable=False)
school_id = Column(String, ForeignKey("school.school_id"), nullable=False)
is_primary = Column(Boolean, default=True)
staff = relationship("FieldStaff", back_populates="school_mappings")
class TrainingLog(Base):
__tablename__ = "training_log"
log_id = Column(String, primary_key=True, default=generate_uuid)
staff_id = Column(String, ForeignKey("field_staff.staff_id"), nullable=False)
school_id = Column(String, ForeignKey("school.school_id"), nullable=False)
date = Column(DateTime, default=datetime.utcnow)
teachers_trained_count = Column(Integer, default=0)
notes = Column(Text, nullable=True)
class SupportLog(Base):
__tablename__ = "support_log"
log_id = Column(String, primary_key=True, default=generate_uuid)
staff_id = Column(String, ForeignKey("field_staff.staff_id"), nullable=False)
teacher_id = Column(String, ForeignKey("teacher_account.teacher_id"), nullable=True)
issue_type = Column(String, nullable=False) # login | technical | content | process | device
description = Column(Text, nullable=False)
resolution = Column(Text, nullable=True)
timestamp = Column(DateTime, default=datetime.utcnow)