Scheduled Assignment Visibility, End-to-End

PR #7697 (backend) and PR #7717 (frontend) — a feature that lets instructors schedule when assignments become visible to students, with per-section overrides. Two PRs, one feature, every layer touched.

What it had to do

Instructors needed to say things like: “this assignment is visible from October 1 through December 31, but Section A sees it starting Monday and everyone else sees it Wednesday.” Three behaviors compressed into one control: a hard window, an optional per-section override of either end of the window, and backwards compatibility with the existing is_hidden flag that every assignment in the database already had.

The constraint that shaped the rest of the design was the second one. Section overrides aren’t replacing the assignment-level setting — they’re shadowing it, but only where set. A NULL on a section’s visible_on means “fall back to the assessment default.” A NULL on the assessment’s visible_on means “fall back to always visible.” That’s a three-level precedence chain, and how it gets evaluated turns out to be the most interesting decision in the project.

Database

Two nullable datetime columns on assessments, mirrored on assessment_section_properties:

ALTER TABLE assessments
  ADD COLUMN visible_on    TIMESTAMP,
  ADD COLUMN visible_until TIMESTAMP;

Nullable on purpose. The existing rows in production are all NULL after this migration, and the system reads NULL as “no constraint at this level, defer to the next one down.” Backwards compatibility falls out of the design rather than being bolted on.

A model-level validation enforces visible_on < visible_until when both are present. The database allows either to be NULL independently: NULL visible_on means “visible immediately,” NULL visible_until means “visible indefinitely.”

The visibility query

This is the part where you decide whether the feature is going to be a recurring source of bugs or not. The naive approach is a cron job that flips is_hidden at the scheduled time. It’s wrong for three reasons. It introduces a race window between the scheduled time and the job actually running. It requires job infrastructure to be healthy or visibility silently breaks. And it makes the system stateful in a way that’s hard to audit — the truth of “is this visible right now” lives in the interaction between the database state and whether a job has fired, not in the data itself.

The right approach is to evaluate visibility at read time, from the database clock, in the query that fetches the assignments. Stateless, atomic, correct by construction. The cost is a slightly more complex query.

The precedence chain falls out cleanly with COALESCE:

COALESCE(
  assessment_section_properties.visible_on,
  assessments.visible_on,
  '1970-01-01'   -- ultimate fallback: always visible
) <= NOW()

COALESCE returns its first non-NULL argument. So if the section has a visible_on set, it wins. Otherwise the assessment’s visible_on is used. Otherwise the epoch, which is always before NOW. The same pattern for visible_until, except the fallback is the far future.

The whole visibility check is one pass over the join, no subqueries, no application-level filtering. Index scan on the assessment’s date columns, and the query planner handles it without any special hinting.

The same logic lives in a Postgres function called check_repo_permissions, which gates Git repository access for students. This was the part of the project that worried me most — if the SQL function and the Ruby query disagreed about visibility, students could see an assignment in the UI but be unable to clone its repo (or vice versa). I kept the two implementations adjacent in the diff and verified by hand that they produced identical truth tables across the edge cases.

Frontend

The instructor-facing UI is a three-state control: Hidden, Visible, Scheduled. Scheduled reveals two datetime pickers. Underneath, the three states map cleanly to the database columns:

The state machine is the load-bearing piece. Transitions actively clear dependent fields: switching from Scheduled back to Visible nullifies both datetimes, even if the form still has values in them. Without this, a previous Scheduled configuration would silently persist in the database after the instructor changed their mind, and the next student page load would honor a window the instructor thought they’d cancelled.

For the section overrides, a table where each row is a section and the columns are visible_on / visible_until inputs. Empty inputs persist as NULL, which the backend COALESCE chain reads as “fall back to assessment defaults” — the precedence chain is enforced symmetrically on both sides.

Datetime selection uses Flatpickr. One non-obvious bit: an instructor setting “visible until December 31” expects visibility through the end of December 31, not expiration at midnight. So the picker defaults to 23:59 for visible_until and 00:00 for visible_on. Small thing, but it’s the kind of detail that turns into a support ticket if you don’t think about it.

What I learned

Two things, neither of them about Rails.

The first is that the right place for business logic is the layer that can guarantee it gets enforced. The visibility rule isn’t a UI concern, it’s a data concern: any code path that reads assignments — controllers, background jobs, the Git permission function, future code I haven’t written — needs to honor it. Encoding it in the query means the rule is one place, evaluated at the right time, with no possibility of drift between callers.

The second is that the hardest part of a full-stack feature isn’t any single layer. It’s the contract between layers. The database columns, the query, the validation, the form submission, the controller’s normalization step, the section override table, the Postgres function for Git access — six places where the truth of “is this visible” has to agree. Most of the bug surface in a feature like this is in the seams.