Late and missing assignments: where is the data in CD2 Postgres db?

Question

Does anyone know which Canvas Data 2 database table(s) have assignment submission late / missing / excused flags?

I'm querying CD2 Postgres database for students who have late or missing assignment submissions.  In the Canvas UI instructors can mark a submission late, missing or excused, or apply a late policy.  In the Canvas Submissions API there are Boolean fields in the return object for late, missing and excused, but I do not see these fields in submissions table in CD2.  Where is the submission status data in CD2?  Thank you

Doug9 · Answer

Thank you @reynlds and @KeithSmith_au for your replies.   I didn't see it, and my bad, you're right @KeithSmith_au a Boolean for submission excused is in the [submissions] table in the [excused] column.   However, simple Booleans for late and missing which are returned by the Submissions API are not in CD2.  We plan to add it as feature idea to the requested features list.  From what I see both [late_policy_status] and [seconds_late_override] can be null and [workflow_state] = 'graded' while in the Canvas UI in the gradebook the instructor has marked the submission as late.  As a workaround we'll also count lates in our early warning SIS module any assignment submission, if it has a submitted_at date, that is past its due date if the assignment has a due date.  We'll consider calling API per student to get a count of late submissions on the fly, but the workaround is Ok for now.

KeithSmith_au · Answer

As @reynlds said, there is far more to it than that - as there are a number of different factors interacting.

In the submissions table, you need to look at the "excused" boolean for that condition, and then consider looking at "late_policy_status" for manually applied overrides, as well as "seconds_late_override" and the actual "submitted_at" (which may not always have a value) compared to the assignment "due_at".

Some or all of these fields may have values depending on the path taken.

The answer to the question that you actually asked is that the submission status is held in the "workflow_state" - but that will not give you all the nuances you are after for a "submitted" state.  Likewise, "missing" (where no submission has been made, and no grading applied) may have to be inferred from the absence of any submission records for the student.

reynlds · Answer

I've used a date calculation between "due_at" and "submitted_at" to determine this. There is more to it than that, but at its simplest you're just checking to see if the submitted timestamp is greater than the due timestamp