| """Normalized, ownership-scoped student learning state. |
| |
| These tables are the durable truth behind planning, teaching, assessment, and |
| progress. The versioned student workspace remains a fast UI aggregate/cache; |
| it must not be the only place a learning event exists. |
| """ |
| from __future__ import annotations |
|
|
| from datetime import date, datetime |
| from typing import Any |
|
|
| from sqlalchemy import ( |
| Boolean, |
| Date, |
| DateTime, |
| Float, |
| ForeignKey, |
| Index, |
| Integer, |
| JSON, |
| String, |
| Text, |
| UniqueConstraint, |
| func, |
| text, |
| ) |
| from sqlalchemy.orm import Mapped, mapped_column |
|
|
| from app.core.database import Base |
| from app.utils.ids import prefixed_id |
|
|
|
|
| class StudentProfileState(Base): |
| __tablename__ = "student_profiles" |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("stp")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), unique=True, index=True, nullable=False) |
| class_level: Mapped[str] = mapped_column(String(80), nullable=False) |
| board: Mapped[str] = mapped_column(String(120), nullable=False) |
| |
| exam_date: Mapped[date | None] = mapped_column(Date, nullable=True) |
| goal: Mapped[str] = mapped_column(String(80), nullable=False) |
| daily_minutes: Mapped[int] = mapped_column(Integer, nullable=False) |
| preferred_time: Mapped[str | None] = mapped_column(String(40), nullable=True) |
| available_study_days: Mapped[int] = mapped_column(Integer, nullable=False) |
| current_subject_id: Mapped[str | None] = mapped_column(String(40), nullable=True) |
| current_chapter_id: Mapped[str | None] = mapped_column(String(40), nullable=True) |
| current_mission_id: Mapped[str | None] = mapped_column(String(120), nullable=True) |
| onboarding_completed: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False) |
| preferences: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class Subject(Base): |
| __tablename__ = "subjects" |
| __table_args__ = ( |
| UniqueConstraint("user_id", "name", "board", "class_level", name="uq_subjects_user_context"), |
| Index("ix_subjects_user_priority", "user_id", "priority"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("sub")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| name: Mapped[str] = mapped_column(String(120), nullable=False) |
| board: Mapped[str] = mapped_column(String(120), nullable=False) |
| class_level: Mapped[str] = mapped_column(String(80), nullable=False) |
| priority: Mapped[int] = mapped_column(Integer, default=0, nullable=False) |
| status: Mapped[str] = mapped_column(String(32), default="active", nullable=False) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class Chapter(Base): |
| __tablename__ = "chapters" |
| __table_args__ = ( |
| UniqueConstraint("user_id", "subject_id", "catalog_id", name="uq_chapters_user_subject_catalog"), |
| Index("ix_chapters_user_subject_order", "user_id", "subject_id", "order_index"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("chp")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| subject_id: Mapped[str] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="CASCADE"), index=True, nullable=False) |
| catalog_id: Mapped[str] = mapped_column(String(120), nullable=False) |
| title: Mapped[str] = mapped_column(String(180), nullable=False) |
| order_index: Mapped[int] = mapped_column(Integer, default=0, nullable=False) |
| importance: Mapped[float] = mapped_column(Float, default=1.0, nullable=False) |
| estimated_minutes: Mapped[int] = mapped_column(Integer, default=0, nullable=False) |
| status: Mapped[str] = mapped_column(String(32), default="available", nullable=False) |
| curated: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class StudyPlan(Base): |
| __tablename__ = "study_plans" |
| __table_args__ = (Index("ix_study_plans_user_status_start", "user_id", "status", "start_date"),) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("spl")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| name: Mapped[str] = mapped_column(String(180), nullable=False) |
| status: Mapped[str] = mapped_column(String(32), default="active", nullable=False) |
| start_date: Mapped[date] = mapped_column(Date, nullable=False) |
| end_date: Mapped[date] = mapped_column(Date, nullable=False) |
| daily_minutes: Mapped[int] = mapped_column(Integer, nullable=False) |
| algorithm_version: Mapped[str] = mapped_column(String(40), default="adaptive-v1", nullable=False) |
| planning_factors: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class DailyPlan(Base): |
| """One persisted, explainable plan for one student and local study date.""" |
|
|
| __tablename__ = "daily_plans" |
| __table_args__ = ( |
| UniqueConstraint("user_id", "date", name="uq_daily_plans_user_date"), |
| Index("ix_daily_plans_user_status_date", "user_id", "status", "date"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("dpl")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| plan_date: Mapped[date] = mapped_column("date", Date, nullable=False) |
| generation: Mapped[int] = mapped_column(Integer, default=1, nullable=False) |
| version: Mapped[str] = mapped_column(String(40), default="learning-engine-v1", nullable=False) |
| status: Mapped[str] = mapped_column(String(24), default="active", nullable=False) |
| available_minutes: Mapped[int] = mapped_column(Integer, nullable=False) |
| planned_minutes: Mapped[int] = mapped_column(Integer, default=0, nullable=False) |
| completed_minutes: Mapped[int] = mapped_column(Integer, default=0, nullable=False) |
| input_summary: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| reason_snapshot: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| generated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| regenerated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class DailyTask(Base): |
| __tablename__ = "daily_tasks" |
| __table_args__ = ( |
| UniqueConstraint("daily_plan_id", "logical_key", name="uq_daily_tasks_plan_logical_key"), |
| Index("ix_daily_tasks_user_status_due", "user_id", "status", "scheduled_for"), |
| Index("ix_daily_tasks_plan_due", "study_plan_id", "scheduled_for"), |
| Index("ix_daily_tasks_daily_plan_status", "daily_plan_id", "status"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("tsk")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| study_plan_id: Mapped[str] = mapped_column(String(40), ForeignKey("study_plans.id", ondelete="CASCADE"), index=True, nullable=False) |
| daily_plan_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("daily_plans.id", ondelete="CASCADE"), index=True, nullable=True) |
| logical_key: Mapped[str | None] = mapped_column(String(220), nullable=True) |
| subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True) |
| chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True) |
| task_type: Mapped[str] = mapped_column(String(32), nullable=False) |
| title: Mapped[str] = mapped_column(String(220), nullable=False) |
| status: Mapped[str] = mapped_column(String(32), default="pending", nullable=False) |
| scheduled_for: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False) |
| duration_minutes: Mapped[int] = mapped_column(Integer, nullable=False) |
| priority: Mapped[float] = mapped_column(Float, default=1.0, nullable=False) |
| href: Mapped[str | None] = mapped_column(String(500), nullable=True) |
| mission_id: Mapped[str | None] = mapped_column(String(120), nullable=True) |
| rescheduled_from_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("daily_tasks.id", ondelete="SET NULL"), index=True, nullable=True) |
| task_metadata: Mapped[dict[str, Any]] = mapped_column("metadata", JSON, default=dict, nullable=False) |
| completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class StudySession(Base): |
| __tablename__ = "study_sessions" |
| __table_args__ = (Index("ix_study_sessions_user_started", "user_id", "started_at"),) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("ses")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| daily_task_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("daily_tasks.id", ondelete="SET NULL"), index=True, nullable=True) |
| subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True) |
| chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True) |
| status: Mapped[str] = mapped_column(String(32), default="started", nullable=False) |
| started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False) |
| ended_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| duration_minutes: Mapped[int | None] = mapped_column(Integer, nullable=True) |
| session_data: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
|
|
|
|
| class LessonProgress(Base): |
| __tablename__ = "lesson_progress" |
| __table_args__ = ( |
| UniqueConstraint("user_id", "chapter_id", "mission_id", name="uq_lesson_progress_user_mission"), |
| Index("ix_lesson_progress_user_status", "user_id", "status"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("lpr")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| chapter_id: Mapped[str] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="CASCADE"), index=True, nullable=False) |
| mission_id: Mapped[str] = mapped_column(String(120), nullable=False) |
| status: Mapped[str] = mapped_column(String(32), default="not_started", nullable=False) |
| progress_percent: Mapped[int] = mapped_column(Integer, default=0, nullable=False) |
| current_step: Mapped[int] = mapped_column(Integer, default=0, nullable=False) |
| completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
|
|
|
|
| class RevisionItem(Base): |
| """One intentional item the student asked DocDoe to bring back for revision. |
| |
| RepairItem is deliberately narrower: it models an unresolved wrong answer. |
| RevisionItem covers manual saves and lesson recaps as well, with an |
| idempotency key so returning to a lesson or double-clicking Save cannot |
| manufacture duplicate revision work. |
| """ |
|
|
| __tablename__ = "revision_items" |
| __table_args__ = ( |
| UniqueConstraint("user_id", "client_item_id", name="uq_revision_items_user_client"), |
| Index("ix_revision_items_user_status_due", "user_id", "status", "due_at"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("rev")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| client_item_id: Mapped[str] = mapped_column(String(180), nullable=False) |
| subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True) |
| chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True) |
| mission_id: Mapped[str | None] = mapped_column(String(120), nullable=True) |
| topic_key: Mapped[str | None] = mapped_column(String(180), nullable=True) |
| title: Mapped[str] = mapped_column(String(220), nullable=False) |
| source_kind: Mapped[str] = mapped_column(String(40), nullable=False) |
| source_ref: Mapped[str | None] = mapped_column(String(120), nullable=True) |
| status: Mapped[str] = mapped_column(String(24), default="pending", nullable=False) |
| due_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| item_data: Mapped[dict[str, Any]] = mapped_column("metadata", JSON, default=dict, nullable=False) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class LearningEvent(Base): |
| """Append-only, ownership-scoped audit record for meaningful learning actions. |
| |
| It is intentionally a transactional application ledger, not a second |
| source of truth. Domain rows remain authoritative; this links together the |
| action, its owning student, and the record that caused it. |
| """ |
|
|
| __tablename__ = "learning_events" |
| __table_args__ = ( |
| UniqueConstraint("user_id", "idempotency_key", name="uq_learning_events_user_key"), |
| Index("ix_learning_events_user_occurred", "user_id", "occurred_at"), |
| Index("ix_learning_events_user_type_occurred", "user_id", "event_type", "occurred_at"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("lev")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| event_type: Mapped[str] = mapped_column(String(64), nullable=False) |
| entity_type: Mapped[str] = mapped_column(String(64), nullable=False) |
| entity_id: Mapped[str] = mapped_column(String(100), nullable=False) |
| idempotency_key: Mapped[str] = mapped_column(String(220), nullable=False) |
| subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True) |
| chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True) |
| topic_key: Mapped[str | None] = mapped_column(String(180), nullable=True) |
| event_data: Mapped[dict[str, Any]] = mapped_column("metadata", JSON, default=dict, nullable=False) |
| occurred_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
|
|
|
|
| class StudyFollowUp(Base): |
| """A concrete unresolved action promoted from Study Chat into planning.""" |
|
|
| __tablename__ = "study_followups" |
| __table_args__ = ( |
| UniqueConstraint("user_id", "client_followup_id", name="uq_study_followups_user_client"), |
| Index("ix_study_followups_user_status_due", "user_id", "status", "due_at"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("sfu")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| chat_session_id: Mapped[str] = mapped_column(String(40), ForeignKey("chat_sessions.id", ondelete="CASCADE"), index=True, nullable=False) |
| source_message_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chat_messages.id", ondelete="SET NULL"), nullable=True) |
| client_followup_id: Mapped[str] = mapped_column(String(160), nullable=False) |
| kind: Mapped[str] = mapped_column(String(40), nullable=False) |
| title: Mapped[str] = mapped_column(String(220), nullable=False) |
| subject: Mapped[str | None] = mapped_column(String(120), nullable=True) |
| chapter: Mapped[str | None] = mapped_column(String(180), nullable=True) |
| topic: Mapped[str | None] = mapped_column(String(180), nullable=True) |
| status: Mapped[str] = mapped_column(String(24), default="open", nullable=False) |
| due_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| estimated_minutes: Mapped[int] = mapped_column(Integer, default=10, nullable=False) |
| source_ref: Mapped[str | None] = mapped_column(String(180), nullable=True) |
| target_data: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class QuizAttempt(Base): |
| __tablename__ = "quiz_attempts" |
| __table_args__ = ( |
| UniqueConstraint( |
| "user_id", |
| "client_attempt_id", |
| name="uq_quiz_attempts_user_client", |
| ), |
| Index("ix_quiz_attempts_user_completed", "user_id", "completed_at"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("qat")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| client_attempt_id: Mapped[str | None] = mapped_column(String(180), nullable=True) |
| quiz_id: Mapped[str | None] = mapped_column(String(40), nullable=True) |
| daily_task_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("daily_tasks.id", ondelete="SET NULL"), index=True, nullable=True) |
| subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True) |
| chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True) |
| score: Mapped[float] = mapped_column(Float, nullable=False) |
| max_score: Mapped[float] = mapped_column(Float, nullable=False) |
| answers: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list, nullable=False) |
| corrections: Mapped[list[dict[str, Any]]] = mapped_column(JSON, default=list, nullable=False) |
| missing_keywords: Mapped[list[str]] = mapped_column(JSON, default=list, nullable=False) |
| misconceptions: Mapped[list[str]] = mapped_column(JSON, default=list, nullable=False) |
| completed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False) |
|
|
|
|
| class TopicMastery(Base): |
| __tablename__ = "topic_mastery" |
| __table_args__ = ( |
| UniqueConstraint("user_id", "topic_key", name="uq_topic_mastery_user_topic"), |
| Index("ix_topic_mastery_user_score_review", "user_id", "score", "next_review_at"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("mst")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True) |
| chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True) |
| topic_key: Mapped[str] = mapped_column(String(180), nullable=False) |
| topic_label: Mapped[str] = mapped_column(String(180), nullable=False) |
| score: Mapped[float] = mapped_column(Float, default=0.0, nullable=False) |
| confidence: Mapped[float] = mapped_column(Float, default=0.0, nullable=False) |
| attempts_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False) |
| last_result: Mapped[str | None] = mapped_column(String(32), nullable=True) |
| next_review_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| evidence: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class CheckpointEvent(Base): |
| """One student answer to one checkpoint question — the atomic evidence unit. |
| |
| ``(user_id, client_event_id)`` is unique so double-clicks, retries after |
| network errors, and two open tabs cannot double-count evidence: replays |
| return the stored consequence instead of re-applying it. |
| """ |
|
|
| __tablename__ = "checkpoint_events" |
| __table_args__ = ( |
| UniqueConstraint("user_id", "client_event_id", name="uq_checkpoint_events_user_client"), |
| Index("ix_checkpoint_events_user_concept", "user_id", "concept_key", "created_at"), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("ckp")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| client_event_id: Mapped[str] = mapped_column(String(80), nullable=False) |
| concept_key: Mapped[str] = mapped_column(String(180), nullable=False) |
| question_id: Mapped[str] = mapped_column(String(120), nullable=False) |
| attempt_index: Mapped[int] = mapped_column(Integer, default=1, nullable=False) |
| kind: Mapped[str] = mapped_column(String(40), nullable=False) |
| correct: Mapped[bool] = mapped_column(Boolean, nullable=False) |
| error_category: Mapped[str | None] = mapped_column(String(48), nullable=True) |
| hint_used: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) |
| payload: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| consequence: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
|
|
|
|
| class RepairItem(Base): |
| """A visible consequence of a wrong answer. |
| |
| One OPEN row per (user, concept_key, error_category) — repeated failures |
| escalate the same item instead of duplicating it; a successful retry |
| resolves it idempotently. DailyTasks link back via |
| ``task_metadata["repair_item_id"]`` so the plan and the queue stay one story. |
| """ |
|
|
| __tablename__ = "repair_items" |
| __table_args__ = ( |
| Index("ix_repair_items_user_status", "user_id", "status"), |
| Index("ix_repair_items_user_concept", "user_id", "concept_key"), |
| Index( |
| "uq_repair_items_open_concept_error", |
| "user_id", |
| "concept_key", |
| "error_category", |
| unique=True, |
| postgresql_where=text("status IN ('open', 'escalated')"), |
| sqlite_where=text("status IN ('open', 'escalated')"), |
| ), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("rep")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), nullable=True) |
| chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), nullable=True) |
| concept_key: Mapped[str] = mapped_column(String(180), nullable=False) |
| concept_label: Mapped[str] = mapped_column(String(180), nullable=False) |
| mission_id: Mapped[str | None] = mapped_column(String(120), nullable=True) |
| error_category: Mapped[str] = mapped_column(String(48), nullable=False) |
| diagnosis: Mapped[str] = mapped_column(Text, nullable=False) |
| recommended_activity: Mapped[str] = mapped_column(String(48), nullable=False) |
| activity_prompt: Mapped[str] = mapped_column(Text, nullable=False) |
| priority: Mapped[float] = mapped_column(Float, default=1.5, nullable=False) |
| estimated_minutes: Mapped[int] = mapped_column(Integer, default=10, nullable=False) |
| status: Mapped[str] = mapped_column(String(24), default="open", nullable=False) |
| support_level: Mapped[int] = mapped_column(Integer, default=1, nullable=False) |
| failed_attempts: Mapped[int] = mapped_column(Integer, default=1, nullable=False) |
| retry_result: Mapped[str | None] = mapped_column(String(24), nullable=True) |
| mastery_recovered: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) |
| source_kind: Mapped[str] = mapped_column(String(40), default="checkpoint", nullable=False) |
| source_ref: Mapped[str | None] = mapped_column(String(80), nullable=True) |
| evidence: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
| resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
|
|
|
|
| class GeneratedResource(Base): |
| __tablename__ = "generated_resources" |
| __table_args__ = (Index("ix_generated_resources_user_type_created", "user_id", "resource_type", "created_at"),) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("res")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| subject_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("subjects.id", ondelete="SET NULL"), index=True, nullable=True) |
| chapter_id: Mapped[str | None] = mapped_column(String(40), ForeignKey("chapters.id", ondelete="SET NULL"), index=True, nullable=True) |
| source_id: Mapped[str | None] = mapped_column(String(80), nullable=True) |
| resource_type: Mapped[str] = mapped_column(String(40), nullable=False) |
| title: Mapped[str] = mapped_column(String(220), nullable=False) |
| status: Mapped[str] = mapped_column(String(32), default="ready", nullable=False) |
| storage_url: Mapped[str | None] = mapped_column(Text, nullable=True) |
| resource_data: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
|
|
|
|
| class Subscription(Base): |
| __tablename__ = "subscriptions" |
| __table_args__ = ( |
| Index("ix_subscriptions_provider_customer", "provider_customer_id", unique=True), |
| Index("ix_subscriptions_provider_subscription", "provider_subscription_id", unique=True), |
| ) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("subn")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), unique=True, index=True, nullable=False) |
| plan_key: Mapped[str] = mapped_column(String(80), default="free", nullable=False) |
| status: Mapped[str] = mapped_column(String(32), default="active", nullable=False) |
| usage_limits: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict, nullable=False) |
| provider_customer_id: Mapped[str | None] = mapped_column(String(180), nullable=True) |
| provider_subscription_id: Mapped[str | None] = mapped_column(String(180), nullable=True) |
| provider_price_id: Mapped[str | None] = mapped_column(String(180), nullable=True) |
| cancel_at_period_end: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False) |
| current_period_start: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| current_period_end: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True) |
| created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
| updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False) |
|
|
|
|
| class StripeWebhookEvent(Base): |
| """Minimal idempotency ledger for Stripe event delivery. |
| |
| The raw webhook payload is deliberately not stored because it can contain |
| customer and payment data. The event ID is enough to prevent duplicate |
| entitlement changes while still allowing failed transactions to retry. |
| """ |
|
|
| __tablename__ = "stripe_webhook_events" |
|
|
| event_id: Mapped[str] = mapped_column(String(180), primary_key=True) |
| event_type: Mapped[str] = mapped_column(String(100), nullable=False) |
| processed_at: Mapped[datetime] = mapped_column( |
| DateTime(timezone=True), |
| server_default=func.now(), |
| nullable=False, |
| ) |
|
|
|
|
| class UsageEvent(Base): |
| __tablename__ = "usage_events" |
| __table_args__ = (Index("ix_usage_events_user_type_occurred", "user_id", "event_type", "occurred_at"),) |
|
|
| id: Mapped[str] = mapped_column(String(40), primary_key=True, default=lambda: prefixed_id("uev")) |
| user_id: Mapped[str] = mapped_column(String(40), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False) |
| event_type: Mapped[str] = mapped_column(String(80), nullable=False) |
| resource_type: Mapped[str | None] = mapped_column(String(80), nullable=True) |
| units: Mapped[float] = mapped_column(Float, default=1.0, nullable=False) |
| event_data: Mapped[dict[str, Any]] = mapped_column("metadata", JSON, default=dict, nullable=False) |
| occurred_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), nullable=False) |
|
|
