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 consent_at: Mapped[datetime | None] = mapped_column(DateTime)43 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)44 last_seen_at: Mapped[datetime | None] = mapped_column(DateTime)45 deleted_at: Mapped[datetime | None] = mapped_column(DateTime)464748class MagicLink(Base):49 __tablename__ = "magic_links"50 token: Mapped[str] = mapped_column(String(64), primary_key=True)51 email: Mapped[str] = mapped_column(String(255), index=True)52 expires_at: Mapped[datetime] = mapped_column(DateTime)53 used_at: Mapped[datetime | None] = mapped_column(DateTime)545556class Course(Base):57 __tablename__ = "courses"58 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)59 code: Mapped[str] = mapped_column(String(16), unique=True, index=True)60 title: Mapped[str] = mapped_column(String(255))61 term: Mapped[str] = mapped_column(String(64), default="")62 syllabus: Mapped[dict] = mapped_column(JSON, default=dict)63 extra_system_prompt: Mapped[str] = mapped_column(Text, default="")64 announcement: Mapped[str] = mapped_column(Text, default="")65 settings: Mapped[dict] = mapped_column(JSON, default=dict)66 active: Mapped[bool] = mapped_column(Boolean, default=True)676869class Conversation(Base):70 __tablename__ = "conversations"71 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)72 user_id: Mapped[str] = mapped_column(String(32), ForeignKey("users.id"), index=True)73 course_code: Mapped[str] = mapped_column(String(16), default="IMM1003")74 title: Mapped[str] = mapped_column(String(255), default="Nouvelle conversation")75 pinned: Mapped[bool] = mapped_column(Boolean, default=False)76 archived: Mapped[bool] = mapped_column(Boolean, default=False)77 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)78 updated_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, onupdate=utcnow)7980 messages: Mapped[list[Message]] = relationship(81 back_populates="conversation", cascade="all, delete-orphan", order_by="Message.created_at"82 )838485Index("ix_conversations_user_updated", Conversation.user_id, Conversation.updated_at.desc())868788class Message(Base):89 __tablename__ = "messages"90 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)91 conversation_id: Mapped[str] = mapped_column(92 String(32), ForeignKey("conversations.id"), index=True93 )94 role: Mapped[str] = mapped_column(String(16)) # user | assistant | system95 content: Mapped[str] = mapped_column(Text, default="")96 content_redacted_at: Mapped[datetime | None] = mapped_column(DateTime)97 model: Mapped[str | None] = mapped_column(String(120))98 tokens_in: Mapped[int] = mapped_column(Integer, default=0)99 tokens_out: Mapped[int] = mapped_column(Integer, default=0)100 cost_usd: Mapped[float] = mapped_column(Float, default=0.0)101 latency_ms: Mapped[int] = mapped_column(Integer, default=0)102 feedback: Mapped[str | None] = mapped_column(String(8)) # up | down103 attachments: Mapped[list] = mapped_column(JSON, default=list) # file ids104 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)105106 conversation: Mapped[Conversation] = relationship(back_populates="messages")107 tool_calls: Mapped[list[ToolCall]] = relationship(108 cascade="all, delete-orphan", order_by="ToolCall.created_at"109 )110111112Index("ix_messages_conv_created", Message.conversation_id, Message.created_at)113114115class ToolCall(Base):116 __tablename__ = "tool_calls"117 id: Mapped[str] = mapped_column(String(64), primary_key=True)118 message_id: Mapped[str] = mapped_column(String(32), ForeignKey("messages.id"), index=True)119 name: Mapped[str] = mapped_column(String(64))120 arguments: Mapped[dict] = mapped_column(JSON, default=dict)121 result_summary: Mapped[str] = mapped_column(Text, default="")122 result_payload: Mapped[dict] = mapped_column(JSON, default=dict) # for UI cards123 status: Mapped[str] = mapped_column(String(16), default="ok")124 duration_ms: Mapped[int] = mapped_column(Integer, default=0)125 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)126127128class StoredFile(Base):129 """Uploaded files and tool artifacts (single table, `kind` discriminates)."""130131 __tablename__ = "files"132 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)133 user_id: Mapped[str] = mapped_column(String(32), index=True)134 conversation_id: Mapped[str | None] = mapped_column(String(32), index=True)135 tool_call_id: Mapped[str | None] = mapped_column(String(64))136 kind: Mapped[str] = mapped_column(String(16), default="upload") # upload | artifact137 type: Mapped[str] = mapped_column(String(16), default="file") # xlsx | image | csv | pdf ...138 filename: Mapped[str] = mapped_column(String(255))139 mime: Mapped[str] = mapped_column(String(128), default="application/octet-stream")140 storage_key: Mapped[str] = mapped_column(String(255))141 size_bytes: Mapped[int] = mapped_column(Integer, default=0)142 expires_at: Mapped[datetime | None] = mapped_column(DateTime)143 pinned: Mapped[bool] = mapped_column(Boolean, default=False)144 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)145146147class Quiz(Base):148 __tablename__ = "quizzes"149 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)150 user_id: Mapped[str] = mapped_column(String(32), index=True)151 course_code: Mapped[str] = mapped_column(String(16))152 topic: Mapped[str] = mapped_column(String(255))153 payload: Mapped[dict] = mapped_column(JSON, default=dict)154 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)155156157class QuizAttempt(Base):158 __tablename__ = "quiz_attempts"159 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)160 quiz_id: Mapped[str] = mapped_column(String(32), ForeignKey("quizzes.id"), index=True)161 answers: Mapped[dict] = mapped_column(JSON, default=dict)162 score: Mapped[float] = mapped_column(Float, default=0.0)163 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)164165166class CourseDocument(Base):167 __tablename__ = "course_documents"168 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)169 course_code: Mapped[str] = mapped_column(String(16), index=True)170 filename: Mapped[str] = mapped_column(String(255))171 title: Mapped[str] = mapped_column(String(255), default="")172 checksum: Mapped[str] = mapped_column(String(64), index=True)173 visibility: Mapped[str] = mapped_column(String(16), default="students")174 n_chunks: Mapped[int] = mapped_column(Integer, default=0)175 ingested_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)176177178class CourseChunk(Base):179 __tablename__ = "course_chunks"180 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)181 document_id: Mapped[str] = mapped_column(182 String(32), ForeignKey("course_documents.id", ondelete="CASCADE"), index=True183 )184 course_code: Mapped[str] = mapped_column(String(16), index=True)185 module: Mapped[str] = mapped_column(String(255), default="")186 section: Mapped[str] = mapped_column(String(255), default="")187 page: Mapped[str] = mapped_column(String(32), default="")188 url: Mapped[str] = mapped_column(String(512), default="")189 content: Mapped[str] = mapped_column(Text)190 embedding: Mapped[list | None] = mapped_column(JSON)191 metadata_: Mapped[dict] = mapped_column("metadata", JSON, default=dict)192 visibility: Mapped[str] = mapped_column(String(16), default="students")193194195class AnalyticsEvent(Base):196 __tablename__ = "analytics_events"197 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)198 user_id_hash: Mapped[str] = mapped_column(String(64), index=True)199 course_code: Mapped[str] = mapped_column(String(16), default="")200 event_type: Mapped[str] = mapped_column(String(32))201 topic: Mapped[str] = mapped_column(String(128), default="")202 question_sample: Mapped[str] = mapped_column(Text, default="") # reformulated, anonymised203 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)204205206class LLMUsage(Base):207 __tablename__ = "llm_usage"208 id: Mapped[str] = mapped_column(String(32), primary_key=True, default=new_id)209 generation_id: Mapped[str | None] = mapped_column(String(64))210 task: Mapped[str] = mapped_column(String(32), default="tutor")211 course_code: Mapped[str] = mapped_column(String(16), default="")212 model: Mapped[str] = mapped_column(String(120), default="")213 provider: Mapped[str] = mapped_column(String(64), default="")214 tokens_in: Mapped[int] = mapped_column(Integer, default=0)215 tokens_out: Mapped[int] = mapped_column(Integer, default=0)216 cost_usd: Mapped[float] = mapped_column(Float, default=0.0)217 latency_ms: Mapped[int] = mapped_column(Integer, default=0)218 created_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)219