model and routes for Submissions

[ngjunsiang]

Overview

Add a Submissions resource to Campus API for storing student assignment responses and teacher feedback. This is needed for campus-classroom (Google Classroom Add-On).

Data Model

Python Dataclass: campus/model/submission.py

@dataclass(eq=False, kw_only=True)
class Response:
    """A student's response to a single question."""
    question_id: str  # References Assignment.question.id
    response_text: str

@dataclass(eq=False, kw_only=True)
class Feedback:
    """Teacher feedback on a specific question response."""
    question_id: str
    feedback_text: str
    teacher_id: UserID
    created_at: DateTime = field(default_factory=DateTime.utcnow)

@dataclass(eq=False, kw_only=True)
class Submission(Model):
    """Dataclass representation of a student submission."""
    id: CampusID = field(default_factory=(
        lambda: uid.generate_category_uid("submission", length=8)
    ))
    assignment_id: CampusID  # References Assignment.id
    student_id: UserID
    course_id: str  # Google Classroom course ID (for querying)
    responses: list[Response] = field(default_factory=list)
    feedback: list[Feedback] = field(default_factory=list)
    submitted_at: DateTime | None = None
    updated_at: DateTime = field(default_factory=DateTime.utcnow)

PostgreSQL Schema: submissions table

CREATE TABLE IF NOT EXISTS "submissions" (
    "id" TEXT PRIMARY KEY,
    "created_at" TIMESTAMP NOT NULL,
    "assignment_id" TEXT NOT NULL,
    "student_id" TEXT NOT NULL,
    "course_id" TEXT NOT NULL,
    "responses" JSONB NOT NULL DEFAULT '[]',
    "feedback" JSONB NOT NULL DEFAULT '[]',
    "submitted_at" TIMESTAMP,
    "updated_at" TIMESTAMP NOT NULL,
    CONSTRAINT fk_assignment FOREIGN KEY ("assignment_id")
        REFERENCES "assignments"("id") ON DELETE CASCADE
);

CREATE INDEX idx_submissions_assignment ON "submissions"("assignment_id");
CREATE INDEX idx_submissions_student ON "submissions"("student_id");
CREATE INDEX idx_submissions_course ON "submissions"("course_id");
CREATE INDEX idx_submissions_responses ON "submissions" USING GIN("responses");
CREATE INDEX idx_submissions_feedback ON "submissions" USING GIN("feedback");

-- One submission per student per assignment per course
CREATE UNIQUE INDEX idx_submissions_unique
    ON "submissions"("assignment_id", "student_id", "course_id");

JSONB Structure

responses:

[
  {"question_id": "q1", "response_text": "The main theme is..."},
  {"question_id": "q1.a", "response_text": "1. Sunlight... 2. Plants..."},
  {"question_id": "q1.a.i", "response_text": "Chlorophyll captures..."}
]

feedback:

[
  {"question_id": "q1", "feedback_text": "Good overview...", "teacher_id": "teacher@example.com", "created_at": "2025-01-28T14:30:00Z"},
  {"question_id": "q1.a", "feedback_text": "Excellent details...", "teacher_id": "teacher@example.com", "created_at": "2025-01-28T14:30:00Z"}
]

API Routes

Method	Path	Description
POST	/api/v1/submissions	Create submission
GET	/api/v1/submissions	List (filtered)
GET	/api/v1/submissions/{id}	Get submission
GET	/api/v1/submissions/by-assignment/{assignment_id}	List by assignment
GET	/api/v1/submissions/by-student/{student_id}	List by student
PATCH	/api/v1/submissions/{id}	Update submission
POST	/api/v1/submissions/{id}/responses	Add/update response
POST	/api/v1/submissions/{id}/feedback	Add feedback
POST	/api/v1/submissions/{id}/submit	Finalize submission

Implementation Checklist

• Create campus/model/submission.py with dataclasses
• Create PostgreSQL table migration
• Create campus/api/resources/submission.py with storage functions
• Create campus/api/routes/submissions.py with HTTP routes
• Register routes in campus/api/__init__.py
• Add tests

Related

• Schema proposal: campus-classroom/docs/schema-proposal.md
• Prerequisite issue: model and routes for Assignments #309 (Assignments)