Python 64.6%
TypeScript 33.7%
CSS 0.8%
1"""SQLAlchemy models (async). SQLite by default, PostgreSQL via DATABASE_URL."""23from __future__ import annotations45import uuid6from datetime import UTC, datetime78from sqlalchemy import (9 JSON,10 Boolean,11 DateTime,12 Float,13 ForeignKey,14 Index,15 Integer,16 String,17 Text,18)19from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship202122def new_id() -> str:23 return uuid.uuid4().hex242526def utcnow() -> datetime:27 return datetime.now(UTC).replace(tzinfo=None)282930class Base(DeclarativeBase):31 type_annotation_map = {dict: JSON, list: JSON}323334class User(Base):35 __tablename__ = "users"36 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)37 email: Mapped[str] = mapped_column(String(255), unique=True, index=True)38 role: Mapped[str] = mapped_column(String(16), default="student")39 display_name: Mapped[str | None] = mapped_column(String(120))40 locale: Mapped[str] = mapped_column(String(8), default="fr-CA")41 preferences: Mapped[dict] = mapped_column(JSON, default=dict)42 password_hash: Mapped[str | None] = mapped_column(String(255))43 consent_at: Mapped[datetime | None] = mapped_column(DateTime)44 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)45 last_seen_at: Mapped[datetime | None] = mapped_column(DateTime)46 deleted_at: Mapped[datetime | None] = mapped_column(DateTime)47 invited_at: Mapped[datetime | None] = mapped_column(DateTime) # last "choose password" e-mail484950class MagicLink(Base):51 """Single-use e-mailed tokens: login link, invitation (choose password), password reset."""5253 __tablename__ = "magic_links"54 token: Mapped[str] = mapped_column(String(64), primary_key=True)55 email: Mapped[str] = mapped_column(String(255), index=True)56 expires_at: Mapped[datetime] = mapped_column(DateTime)57 used_at: Mapped[datetime | None] = mapped_column(DateTime)58 purpose: Mapped[str | None] = mapped_column(String(16)) # None/"login" | "invite" | "reset"596061class Course(Base):62 __tablename__ = "courses"63 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)64 code: Mapped[str] = mapped_column(String(16), unique=True, index=True)65 title: Mapped[str] = mapped_column(String(255))66 term: Mapped[str] = mapped_column(String(64), default="")67 syllabus: Mapped[dict] = mapped_column(JSON, default=dict)68 extra_system_prompt: Mapped[str] = mapped_column(Text, default="")69 announcement: Mapped[str] = mapped_column(Text, default="")70 settings: Mapped[dict] = mapped_column(JSON, default=dict)71 active: Mapped[bool] = mapped_column(Boolean, default=True)727374class Conversation(Base):75 __tablename__ = "conversations"76 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)77 user_id: Mapped[str] = mapped_column(String(32), ForeignKey("users.id"), index=True)78 course_code: Mapped[str] = mapped_column(String(16), default="IMM1003")79 title: Mapped[str] = mapped_column(String(255), default="Nouvelle conversation")80 pinned: Mapped[bool] = mapped_column(Boolean, default=False)81 archived: Mapped[bool] = mapped_column(Boolean, default=False)82 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)83 updated_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, onupdate=utcnow)8485 messages: Mapped[list[Message]] = relationship(86 back_populates="conversation", cascade="all, delete-orphan", order_by="Message.created_at"87 )888990Index("ix_conversations_user_updated", Conversation.user_id, Conversation.updated_at.desc())919293class Message(Base):94 __tablename__ = "messages"95 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)96 conversation_id: Mapped[str] = mapped_column(97 String(32), ForeignKey("conversations.id"), index=True98 )99 role: Mapped[str] = mapped_column(String(16)) # user | assistant | system100 content: Mapped[str] = mapped_column(Text, default="")101 content_redacted_at: Mapped[datetime | None] = mapped_column(DateTime)102 model: Mapped[str | None] = mapped_column(String(120))103 tokens_in: Mapped[int] = mapped_column(Integer, default=0)104 tokens_out: Mapped[int] = mapped_column(Integer, default=0)105 cost_usd: Mapped[float] = mapped_column(Float, default=0.0)106 latency_ms: Mapped[int] = mapped_column(Integer, default=0)107 feedback: Mapped[str | None] = mapped_column(String(8)) # up | down108 attachments: Mapped[list] = mapped_column(JSON, default=list) # file ids109 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)110111 conversation: Mapped[Conversation] = relationship(back_populates="messages")112 tool_calls: Mapped[list[ToolCall]] = relationship(113 cascade="all, delete-orphan", order_by="ToolCall.created_at"114 )115116117Index("ix_messages_conv_created", Message.conversation_id, Message.created_at)118119120class ToolCall(Base):121 __tablename__ = "tool_calls"122 id: Mapped[str] = mapped_column(String(64), primary_key=True)123 message_id: Mapped[str] = mapped_column(String(32), ForeignKey("messages.id"), index=True)124 name: Mapped[str] = mapped_column(String(64))125 arguments: Mapped[dict] = mapped_column(JSON, default=dict)126 result_summary: Mapped[str] = mapped_column(Text, default="")127 result_payload: Mapped[dict] = mapped_column(JSON, default=dict) # for UI cards128 status: Mapped[str] = mapped_column(String(16), default="ok")129 duration_ms: Mapped[int] = mapped_column(Integer, default=0)130 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)131132133class StoredFile(Base):134 """Uploaded files and tool artifacts (single table, `kind` discriminates)."""135136 __tablename__ = "files"137 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)138 user_id: Mapped[str] = mapped_column(String(32), index=True)139 conversation_id: Mapped[str | None] = mapped_column(String(32), index=True)140 tool_call_id: Mapped[str | None] = mapped_column(String(64))141 kind: Mapped[str] = mapped_column(String(16), default="upload") # upload | artifact142 type: Mapped[str] = mapped_column(String(16), default="file") # xlsx | image | csv | pdf ...143 filename: Mapped[str] = mapped_column(String(255))144 mime: Mapped[str] = mapped_column(String(128), default="application/octet-stream")145 storage_key: Mapped[str] = mapped_column(String(255))146 size_bytes: Mapped[int] = mapped_column(Integer, default=0)147 expires_at: Mapped[datetime | None] = mapped_column(DateTime)148 pinned: Mapped[bool] = mapped_column(Boolean, default=False)149 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)150151152class Quiz(Base):153 __tablename__ = "quizzes"154 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)155 user_id: Mapped[str] = mapped_column(String(32), index=True)156 course_code: Mapped[str] = mapped_column(String(16))157 topic: Mapped[str] = mapped_column(String(255))158 payload: Mapped[dict] = mapped_column(JSON, default=dict)159 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)160161162class QuizAttempt(Base):163 __tablename__ = "quiz_attempts"164 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)165 quiz_id: Mapped[str] = mapped_column(String(32), ForeignKey("quizzes.id"), index=True)166 answers: Mapped[dict] = mapped_column(JSON, default=dict)167 score: Mapped[float] = mapped_column(Float, default=0.0)168 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)169170171class CourseDocument(Base):172 __tablename__ = "course_documents"173 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)174 course_code: Mapped[str] = mapped_column(String(16), index=True)175 filename: Mapped[str] = mapped_column(String(255))176 title: Mapped[str] = mapped_column(String(255), default="")177 checksum: Mapped[str] = mapped_column(String(64), index=True)178 visibility: Mapped[str] = mapped_column(String(16), default="students")179 n_chunks: Mapped[int] = mapped_column(Integer, default=0)180 ingested_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)181182183class CourseChunk(Base):184 __tablename__ = "course_chunks"185 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)186 document_id: Mapped[str] = mapped_column(187 String(32), ForeignKey("course_documents.id", ondelete="CASCADE"), index=True188 )189 course_code: Mapped[str] = mapped_column(String(16), index=True)190 module: Mapped[str] = mapped_column(String(255), default="")191 section: Mapped[str] = mapped_column(String(255), default="")192 page: Mapped[str] = mapped_column(String(32), default="")193 url: Mapped[str] = mapped_column(String(512), default="")194 content: Mapped[str] = mapped_column(Text)195 embedding: Mapped[list | None] = mapped_column(JSON)196 metadata_: Mapped[dict] = mapped_column("metadata", JSON, default=dict)197 visibility: Mapped[str] = mapped_column(String(16), default="students")198199200class AnalyticsEvent(Base):201 __tablename__ = "analytics_events"202 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)203 user_id_hash: Mapped[str] = mapped_column(String(64), index=True)204 course_code: Mapped[str] = mapped_column(String(16), default="")205 event_type: Mapped[str] = mapped_column(String(32))206 topic: Mapped[str] = mapped_column(String(128), default="")207 question_sample: Mapped[str] = mapped_column(Text, default="") # reformulated, anonymised208 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)209210211class LLMUsage(Base):212 __tablename__ = "llm_usage"213 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)214 generation_id: Mapped[str | None] = mapped_column(String(64))215 task: Mapped[str] = mapped_column(String(32), default="tutor")216 course_code: Mapped[str] = mapped_column(String(16), default="")217 model: Mapped[str] = mapped_column(String(120), default="")218 provider: Mapped[str] = mapped_column(String(64), default="")219 tokens_in: Mapped[int] = mapped_column(Integer, default=0)220 tokens_out: Mapped[int] = mapped_column(Integer, default=0)221 cost_usd: Mapped[float] = mapped_column(Float, default=0.0)222 latency_ms: Mapped[int] = mapped_column(Integer, default=0)223 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)224225226class AppSetting(Base):227 """Small key/value store for runtime settings editable by the professor (access code…)."""228229 __tablename__ = "app_settings"230 key: Mapped[str] = mapped_column(String(64), primary_key=True)231 value: Mapped[str] = mapped_column(Text, default="")232 updated_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, onupdate=utcnow)233