Spaces:
Paused
Paused
| from datetime import date, datetime | |
| from sqlalchemy import Boolean, Column, Date, DateTime, Float, ForeignKey, Index, Integer, String, Text, JSON, UniqueConstraint | |
| from sqlalchemy.orm import relationship | |
| from app.db import Base | |
| class User(Base): | |
| __tablename__ = "users" | |
| id = Column(Integer, primary_key=True, index=True) | |
| email = Column(String(255), unique=True, index=True, nullable=False) | |
| hashed_password = Column(String(255), nullable=False) | |
| is_admin = Column(Boolean, default=False, nullable=False) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| sessions = relationship("Session", back_populates="user", cascade="all, delete-orphan") | |
| refresh_tokens = relationship( | |
| "RefreshToken", back_populates="user", cascade="all, delete-orphan" | |
| ) | |
| session_metrics = relationship("SessionMetrics", back_populates="user", cascade="all, delete-orphan") | |
| daily_aggregates = relationship("DailyAggregate", back_populates="user", cascade="all, delete-orphan") | |
| coaching_feedback = relationship("CoachingFeedback", back_populates="user", cascade="all, delete-orphan") | |
| goals = relationship("UserGoal", back_populates="user", cascade="all, delete-orphan") | |
| presenter_profile = relationship( | |
| "PresenterProfile", back_populates="user", uselist=False, cascade="all, delete-orphan" | |
| ) | |
| curriculum_completions = relationship( | |
| "CurriculumDayCompletion", back_populates="user", cascade="all, delete-orphan" | |
| ) | |
| weekly_reflections = relationship( | |
| "WeeklyReflection", back_populates="user", cascade="all, delete-orphan" | |
| ) | |
| class RefreshToken(Base): | |
| __tablename__ = "refresh_tokens" | |
| id = Column(Integer, primary_key=True, index=True) | |
| user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True) | |
| jti_hash = Column(String(64), unique=True, index=True, nullable=False) | |
| expires_at = Column(DateTime, nullable=False) | |
| revoked_at = Column(DateTime, nullable=True) | |
| replaced_by_jti_hash = Column(String(64), nullable=True) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| user = relationship("User", back_populates="refresh_tokens") | |
| class Session(Base): | |
| __tablename__ = "sessions" | |
| id = Column(Integer, primary_key=True, index=True) | |
| user_id = Column(Integer, ForeignKey("users.id"), nullable=False) | |
| title = Column(String(255), nullable=True) | |
| mode = Column(String(80), nullable=True) | |
| prompt = Column(Text, nullable=True) | |
| goal = Column(String(255), nullable=True) | |
| curriculum_day = Column(Integer, nullable=True) | |
| exercise_id = Column(String(100), nullable=True) | |
| story_framework = Column(String(80), nullable=True) | |
| target_keywords_json = Column(Text, nullable=True) | |
| teleprompter_script = Column(Text, nullable=True) | |
| started_at = Column(DateTime, nullable=True) | |
| ended_at = Column(DateTime, nullable=True) | |
| duration_ms = Column(Integer, default=0) | |
| wpm_avg = Column(Float, default=0) | |
| filler_count = Column(Integer, default=0) | |
| eye_contact_pct = Column(Float, default=0) | |
| video_path = Column(String(1024), nullable=True) | |
| document_path = Column(String(1024), nullable=True) | |
| document_name = Column(String(255), nullable=True) | |
| document_mime_type = Column(String(255), nullable=True) | |
| document_blocks_json = Column(Text, nullable=True) | |
| transcription_status = Column(String(32), default="idle") | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| user = relationship("User", back_populates="sessions") | |
| transcript_segments = relationship( | |
| "TranscriptSegment", back_populates="session", cascade="all, delete-orphan" | |
| ) | |
| metrics = relationship( | |
| "MetricsTimeseries", back_populates="session", cascade="all, delete-orphan" | |
| ) | |
| score = relationship("Score", back_populates="session", uselist=False, cascade="all, delete-orphan") | |
| tags = relationship("Tag", back_populates="session", cascade="all, delete-orphan") | |
| metrics_snapshot = relationship( | |
| "SessionMetrics", back_populates="session", uselist=False, cascade="all, delete-orphan" | |
| ) | |
| coaching_feedback = relationship( | |
| "CoachingFeedback", back_populates="session", uselist=False, cascade="all, delete-orphan" | |
| ) | |
| class PresenterProfile(Base): | |
| __tablename__ = "presenter_profiles" | |
| id = Column(Integer, primary_key=True, index=True) | |
| user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, unique=True, index=True) | |
| display_name = Column(String(120), default="Tech Presenter", nullable=False) | |
| primary_role = Column(String(120), default="Software Engineer & Tech Presenter", nullable=False) | |
| focus_track = Column(String(80), default="tech_storytelling", nullable=False) | |
| experience_level = Column(String(40), default="intermediate", nullable=False) | |
| target_wpm_min = Column(Integer, default=125, nullable=False) | |
| target_wpm_max = Column(Integer, default=155, nullable=False) | |
| max_filler_rate = Column(Float, default=2.0, nullable=False) | |
| min_eye_contact = Column(Float, default=0.7, nullable=False) | |
| daily_minutes_goal = Column(Integer, default=15, nullable=False) | |
| weaknesses = Column(JSON, nullable=True) | |
| speaking_goal_statement = Column( | |
| Text, | |
| default="Become a confident, articulate, engaging technology presenter on camera.", | |
| nullable=True, | |
| ) | |
| curriculum_start_date = Column(Date, default=date.today, nullable=False) | |
| created_at = Column(DateTime, default=datetime.utcnow, nullable=False) | |
| updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False) | |
| user = relationship("User", back_populates="presenter_profile") | |
| class CurriculumDayCompletion(Base): | |
| __tablename__ = "curriculum_day_completions" | |
| __table_args__ = ( | |
| UniqueConstraint("user_id", "day_number", name="uq_curriculum_user_day"), | |
| Index("ix_curriculum_user_day", "user_id", "day_number"), | |
| ) | |
| id = Column(Integer, primary_key=True, index=True) | |
| user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) | |
| day_number = Column(Integer, nullable=False) | |
| completed_session_id = Column(Integer, ForeignKey("sessions.id", ondelete="SET NULL"), nullable=True) | |
| best_score = Column(Float, default=0, nullable=False) | |
| attempts_count = Column(Integer, default=1, nullable=False) | |
| reflection_notes = Column(Text, nullable=True) | |
| completed_at = Column(DateTime, default=datetime.utcnow, nullable=False) | |
| updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False) | |
| user = relationship("User", back_populates="curriculum_completions") | |
| class WeeklyReflection(Base): | |
| __tablename__ = "weekly_reflections" | |
| __table_args__ = ( | |
| UniqueConstraint("user_id", "week_start_date", name="uq_weekly_reflections_user_week"), | |
| Index("ix_weekly_reflections_user_week", "user_id", "week_start_date"), | |
| ) | |
| id = Column(Integer, primary_key=True, index=True) | |
| user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) | |
| week_start_date = Column(Date, nullable=False) | |
| wins_note = Column(Text, nullable=True) | |
| focus_next_week = Column(Text, nullable=True) | |
| updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False) | |
| user = relationship("User", back_populates="weekly_reflections") | |
| class SessionMetrics(Base): | |
| __tablename__ = "session_metrics" | |
| __table_args__ = ( | |
| Index("ix_session_metrics_user_date", "user_id", "recorded_at"), | |
| ) | |
| id = Column(Integer, primary_key=True, index=True) | |
| session_id = Column(Integer, ForeignKey("sessions.id", ondelete="CASCADE"), nullable=False, unique=True, index=True) | |
| user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) | |
| recorded_at = Column(DateTime, nullable=False, default=datetime.utcnow) | |
| duration_seconds = Column(Float, default=0, nullable=False) | |
| word_count = Column(Integer, default=0, nullable=False) | |
| wpm = Column(Float, default=0, nullable=False) | |
| filler_count = Column(Integer, default=0, nullable=False) | |
| filler_rate = Column(Float, default=0, nullable=False) | |
| pause_count = Column(Integer, default=0, nullable=False) | |
| unique_word_count = Column(Integer, default=0, nullable=False) | |
| vocabulary_density = Column(Float, default=0, nullable=False) | |
| eye_contact_score = Column(Float, nullable=True) | |
| smile_score = Column(Float, nullable=True) | |
| head_stability_score = Column(Float, nullable=True) | |
| loudness_score = Column(Float, nullable=True) | |
| speech_score = Column(Float, default=0, nullable=False) | |
| delivery_score = Column(Float, default=0, nullable=False) | |
| overall_score = Column(Float, default=0, nullable=False) | |
| filler_breakdown = Column(JSON, nullable=True) | |
| wpm_timeline = Column(JSON, nullable=True) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| session = relationship("Session", back_populates="metrics_snapshot") | |
| user = relationship("User", back_populates="session_metrics") | |
| class DailyAggregate(Base): | |
| __tablename__ = "daily_aggregates" | |
| __table_args__ = ( | |
| UniqueConstraint("user_id", "practice_date", name="uq_daily_aggregates_user_date"), | |
| Index("ix_daily_aggregates_user_date", "user_id", "practice_date"), | |
| ) | |
| id = Column(Integer, primary_key=True, index=True) | |
| user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) | |
| practice_date = Column(Date, nullable=False) | |
| session_count = Column(Integer, default=0, nullable=False) | |
| total_duration_seconds = Column(Float, default=0, nullable=False) | |
| avg_wpm = Column(Float, default=0, nullable=False) | |
| avg_filler_rate = Column(Float, default=0, nullable=False) | |
| avg_overall_score = Column(Float, default=0, nullable=False) | |
| avg_speech_score = Column(Float, default=0, nullable=False) | |
| avg_delivery_score = Column(Float, default=0, nullable=False) | |
| best_overall_score = Column(Float, default=0, nullable=False) | |
| user = relationship("User", back_populates="daily_aggregates") | |
| class CoachingFeedback(Base): | |
| __tablename__ = "coaching_feedback" | |
| __table_args__ = ( | |
| Index("ix_coaching_feedback_user_created", "user_id", "created_at"), | |
| ) | |
| id = Column(Integer, primary_key=True, index=True) | |
| session_id = Column(Integer, ForeignKey("sessions.id", ondelete="CASCADE"), nullable=False, unique=True, index=True) | |
| user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) | |
| status = Column(String(20), default="pending", nullable=False) | |
| model_used = Column(String(100), nullable=True) | |
| generation_time_ms = Column(Integer, nullable=True) | |
| summary = Column(Text, nullable=True) | |
| strengths = Column(JSON, nullable=True) | |
| improvements = Column(JSON, nullable=True) | |
| drills = Column(JSON, nullable=True) | |
| practice_plan = Column(JSON, nullable=True) | |
| raw_response = Column(Text, nullable=True) | |
| compared_to_session_id = Column(Integer, nullable=True) | |
| trend_context = Column(JSON, nullable=True) | |
| created_at = Column(DateTime, default=datetime.utcnow) | |
| error_message = Column(Text, nullable=True) | |
| session = relationship("Session", back_populates="coaching_feedback") | |
| user = relationship("User", back_populates="coaching_feedback") | |
| class UserGoal(Base): | |
| __tablename__ = "user_goals" | |
| __table_args__ = ( | |
| UniqueConstraint("user_id", "metric_name", name="uq_user_goals_user_metric"), | |
| Index("ix_user_goals_user_active", "user_id", "is_active"), | |
| ) | |
| id = Column(Integer, primary_key=True, index=True) | |
| user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True) | |
| metric_name = Column(String(50), nullable=False) | |
| comparison = Column(String(10), nullable=False) | |
| target_value = Column(Float, nullable=False) | |
| target_upper = Column(Float, nullable=True) | |
| is_active = Column(Boolean, default=True, nullable=False) | |
| created_at = Column(DateTime, default=datetime.utcnow, nullable=False) | |
| updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False) | |
| user = relationship("User", back_populates="goals") | |
| class TranscriptSegment(Base): | |
| __tablename__ = "transcript_segments" | |
| id = Column(Integer, primary_key=True, index=True) | |
| session_id = Column(Integer, ForeignKey("sessions.id"), nullable=False) | |
| start_ms = Column(Integer, nullable=False) | |
| end_ms = Column(Integer, nullable=False) | |
| text = Column(Text, nullable=False) | |
| session = relationship("Session", back_populates="transcript_segments") | |
| class MetricsTimeseries(Base): | |
| __tablename__ = "metrics_timeseries" | |
| id = Column(Integer, primary_key=True, index=True) | |
| session_id = Column(Integer, ForeignKey("sessions.id"), nullable=False) | |
| t = Column(Integer, nullable=False) | |
| wpm = Column(Float, default=0) | |
| rms = Column(Float, default=0) | |
| eye_contact = Column(Float, default=0) | |
| smile = Column(Float, default=0) | |
| yaw = Column(Float, default=0) | |
| pitch = Column(Float, default=0) | |
| roll = Column(Float, default=0) | |
| filler_count = Column(Integer, default=0) | |
| pause_ms = Column(Integer, default=0) | |
| session = relationship("Session", back_populates="metrics") | |
| class Score(Base): | |
| __tablename__ = "scores" | |
| id = Column(Integer, primary_key=True, index=True) | |
| session_id = Column(Integer, ForeignKey("sessions.id"), nullable=False, unique=True) | |
| data = Column(JSON, nullable=False) | |
| session = relationship("Session", back_populates="score") | |
| class Tag(Base): | |
| __tablename__ = "tags" | |
| id = Column(Integer, primary_key=True, index=True) | |
| session_id = Column(Integer, ForeignKey("sessions.id"), nullable=False) | |
| name = Column(String(80), nullable=False) | |
| session = relationship("Session", back_populates="tags") | |