MarkUs: Scheduled Visibility (Backend)

PR #7697 — Time-based assignment visibility with section-level overrides.

Requirements

Instructors needed to schedule assignment visibility windows: “visible October 1 through December 31” with per-section overrides (“Section A sees the assignment Monday, everyone else sees it Wednesday”). This required changes across the database, model, authorization, and test layers.

Database Design

Added two nullable datetime columns to assessments:

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

Mirrored in assessment_section_properties for per-section overrides. The nullable design provides backwards compatibility: existing assessments with NULL values retain their current behavior (always visible if is_hidden = false).

Precedence rule: Section-specific properties override global assessment properties. If a section has visible_on set, it takes precedence over the assessment’s visible_on. If the section property is NULL, fall back to the assessment-level setting.

Constraint: visible_on < visible_until enforced at the model level via validation. The database permits NULL for either field independently: NULL visible_on means “visible immediately,” NULL visible_until means “visible indefinitely.”

Query Design

The visibility query must evaluate a three-level precedence chain (section override → assessment default → fallback) within a single ActiveRecord scope:

def visible_assessments
  Assessment
    .where(is_hidden: false)
    .where(visible_on_condition)
    .where(visible_until_condition)
    .with_section_overrides(section)
end

The visible_on_condition produces SQL of the form:

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

COALESCE implements the precedence chain in SQL: use the section override if present, fall back to the assessment default, fall back to epoch (always visible). This keeps the query as a single pass without subqueries or application-level filtering.

The corresponding check_repo_permissions SQL function (used for Git repository access control) was updated to apply the same visibility logic, ensuring that students cannot clone assignment repositories outside the visibility window.

Validation

validate :visibility_dates_consistent

def visibility_dates_consistent
  if visible_on.present? && visible_until.present? && visible_on >= visible_until
    errors.add(:visible_until, "must be after visible_on")
  end
end

Edge cases handled:

• Both NULL: always visible (backwards compatible)
• Only visible_on set: visible from date, no end
• Only visible_until set: visible until date, no start restriction
• Both set: standard window, visible_on < visible_until enforced

Test Coverage

27 tests covering the visibility matrix:

Key edge cases: midnight boundaries (visible_on at exactly NOW()), section override with NULL fallback, assessment visible to one section but not another simultaneously.

Architecture Decision

Why database-level visibility rather than a background job? A cron-based approach (flip is_hidden at scheduled times) introduces race conditions, requires job infrastructure, and fails silently if the job doesn’t run. The query-based approach evaluates visibility at read time using the database clock, making it stateless, atomic, and correct by construction. The tradeoff is a slightly more complex query, but the query planner handles the COALESCE + comparison efficiently with a single index scan.