Convert assessment_instances_grade/points sprocs to TS

[jonatanschroeder]

Description

Part of the #8893 effort. Converts both assessment_instances_grade and assessment_instances_points to library functions.

Testing

Tested manually in example course, using homework/extraCredit. This assessment includes many of the kinks of this process (best questions, max points per zone, bonus points, etc.).

[codecov[bot]]

Codecov Report

:x: Patch coverage is 84.21053% with 6 lines in your changes missing coverage. Please review. :white_check_mark: Project coverage is 53.21%. Comparing base (e587a1b) to head (6dcc9d7).

Files with missing lines	Patch %	Lines
apps/prairielearn/src/lib/assessment-grading.ts	82.60%	1 Missing and 3 partials :warning:
...irielearn/src/ee/lib/ai-grading/ai-grading-util.ts	0.00%	1 Missing :warning:
apps/prairielearn/src/lib/assessment.ts	80.00%	1 Missing :warning:

Additional details and impacted files

@@                        Coverage Diff                         @@
##           sproc-instance-questions-grade   #13152      +/-   ##
==================================================================
+ Coverage                           53.19%   53.21%   +0.02%     
==================================================================
  Files                                 866      867       +1     
  Lines                               32809    32832      +23     
  Branches                             4911     4919       +8     
==================================================================
+ Hits                                17454    17473      +19     
- Misses                              14040    14041       +1     
- Partials                             1315     1318       +3     

Flag	Coverage Δ
javascript	50.90% <84.21%> (+0.02%)	:arrow_up:
python	70.33% <ø> (ø)

Flags with carried forward coverage won't be shown. Click here to find out more.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

:rocket: New features to boost your workflow:

• :snowflake: Test Analytics: Detect flaky tests, report on failures, and find test suite problems.

[github-actions[bot]]

All images

Image	Platform	Old Size	New Size	Change
prairielearn/executor:6dcc9d79026bd11cc5b610e241e14f6cd9531f2d	linux/arm64	1236.01 MB	1236.02 MB	0.00%
prairielearn/executor:6dcc9d79026bd11cc5b610e241e14f6cd9531f2d	linux/amd64	1288.35 MB	1288.36 MB	0.00%
prairielearn/prairielearn:6dcc9d79026bd11cc5b610e241e14f6cd9531f2d	linux/arm64	1236.01 MB	1236.01 MB	0.00%
prairielearn/prairielearn:6dcc9d79026bd11cc5b610e241e14f6cd9531f2d	linux/amd64	1288.35 MB	1288.36 MB	0.00%

[coderabbitai[bot]]

[!NOTE]

Other AI code review bot(s) detected

CodeRabbit has detected other AI code review bot(s) in this pull request and will avoid duplicating their findings in the review comments. This may lead to a less comprehensive review.

📝 Walkthrough

Walkthrough

This PR refactors assessment grading logic from PostgreSQL stored procedures to a TypeScript-based API. A new assessment-grading module provides updateAssessmentInstanceGrade and computeAssessmentInstanceScoreByZone functions that replace direct calls to assessment_instances_grade and assessment_instances_points sprocs. Function naming is standardized across the codebase (e.g., updateAssessmentInstanceScore → setAssessmentInstanceScore).

Changes

Cohort / File(s)	Summary
Core Grading Logic Migration apps/prairielearn/src/lib/assessment-grading.sql, apps/prairielearn/src/lib/assessment-grading.ts	New SQL blocks for assessment instance locking, credit retrieval, grade updates with logging, and per-zone point computation. New TypeScript functions orchestrate grading transactions with credit handling, score calculation, and non-decreasing score enforcement. Exports updateAssessmentInstanceGrade, computeAssessmentInstanceScoreByZone, and AssessmentInstanceZonePointsSchema.
Stored Procedures Removed apps/prairielearn/src/sprocs/assessment_instances_grade.sql, apps/prairielearn/src/sprocs/assessment_instances_points.sql, apps/prairielearn/src/sprocs/index.ts	Removed legacy stored functions and their registration during initialization. Grading and point computation logic migrated to TypeScript.
Assessment Module Updates apps/prairielearn/src/lib/assessment.ts, apps/prairielearn/src/lib/assessment.sql	Integrated computeAssessmentInstanceScoreByZone and updateAssessmentInstanceGrade. Replaced direct sproc calls with new API. Removed zones_total_max_points CTE, using computed parameter instead. Renamed exported functions: updateAssessmentInstanceScore → setAssessmentInstanceScore, updateAssessmentInstancePoints → setAssessmentInstancePoints.
Type Definitions apps/prairielearn/src/lib/db-types.ts	Removed exported SprocAssessmentInstancesGradeSchema.
Grading Call Site Updates apps/prairielearn/src/ee/lib/ai-grading/ai-grading-util.ts, apps/prairielearn/src/lib/manualGrading.ts, apps/prairielearn/src/lib/regrading.ts, apps/prairielearn/src/models/grading-job.ts	Replaced stored procedure invocations with calls to updateAssessmentInstanceGrade, removing schema dependencies and adapting to the new object-based parameter format.
Function Rename Updates apps/prairielearn/src/lib/score-upload.ts, apps/prairielearn/src/pages/instructorAssessmentInstance/instructorAssessmentInstance.ts	Updated import and call sites to use renamed functions (set* instead of update*), preserving control flow and error handling.

Estimated code review effort

🎯 4 (Complex) | ⏱️ ~60 minutes

• Score calculation logic: The new updateAssessmentInstanceGrade function implements intricate edge cases (credit <100, credit >100, non-decreasing score enforcement) that must be verified against the original sproc behavior to prevent logic divergence.
• Per-zone point computation: New SQL logic in compute_assessment_instance_points_by_zone with complex CTEs and filtering rules (deleted questions handling, exam vs. homework types) requires careful validation.
• Multiple interrelated changes: Function renames propagate across 7+ files; call site updates must be verified for consistency.
• API surface changes: Public function renames and schema removals affect multiple call sites.

Suggested reviewers

• mylesw27

Pre-merge checks and finishing touches

✅ Passed checks (2 passed)

Check name	Status	Explanation
Title check	✅ Passed	The title accurately describes the main change: converting two stored procedures (assessment_instances_grade and assessment_instances_points) from SQL sprocs to TypeScript library functions.
Description check	✅ Passed	The description is related to the changeset, referencing the #8893 effort and explaining that both stored procedures are being converted to library functions, with manual testing performed.

✨ Finishing touches

• [ ] 📝 Generate docstrings

🧪 Generate unit tests (beta)

• [ ] Create PR with unit tests
• [ ] Post copyable unit tests in a comment
• [ ] Commit unit tests in branch sproc-assessment-instance-grade

---

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

• X
• Mastodon
• Reddit
• LinkedIn

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

[reteps]

Taking this over from @mylesw27 . I'll review this once the coderabbit comments are dismissed/addressed for functionality on that assessment.

[jonatanschroeder]

Assessment type validation

It's still there: https://github.com/PrairieLearn/PrairieLearn/blob/c49d69958d31dac449aa72f3f9108c8c92676f11/apps/prairielearn/src/lib/question-points.ts#L75-L92 Besides, the type being Exam or Homework is a bit of a given throughout the code, and other things would fail if something were outside these two values.

Negative point handling

I don't think we properly handle negative points anywhere in the codebase. Problems would likely arise way before this point if we were to have them.

[jonatanschroeder]

This PR will create merge conflicts with #13175. I'm changing the base branch to that of #13175 so I can address them now instead of waiting for either of these PRs to be merged. The implication is that this PR can only be merged after #13175.