The 11:30 PM kickoff that broke NFL week assignments

The late Sunday kickoff that landed in the wrong week

An NFL game kicks off at 11:30 PM Eastern on Sunday night. In the local timezone that is Sunday. In UTC it is already Monday. If you sort games into weekly buckets by their game_date column, which one of those two values are you using?

This was the bug. Statpro's NFL release validator compared each game's date against the release week's start and end boundaries, but it used a game_date column that had already been cast to a local date somewhere upstream. An 11:30 PM Sunday kickoff rolled over to Monday in UTC, and the validator quietly filed it under the wrong week. The release completeness check passed because the game existed, it just existed in the wrong bucket.

What the old validator checked

The original completeness function compared every game in a release against the release week boundaries. If a game's date fell outside the week, the function flagged it. The problem was the date it compared:

-- Old: compared the local game_date directly
AND (game.game_date < week.start_date
  OR game.game_date > week.end_date)

That game_date value had already been normalized to a local timezone. A game that started at 11:30 PM ET on Sunday got a game_date of Sunday, when in UTC the kickoff was Monday. The week boundary check saw Sunday and happily slotted it into the earlier week. Nobody noticed because most games kick off at sensible afternoon times and local date and UTC date agree.

The fix: compare UTC kickoff timestamps

The new validator replaces the local game_date comparison with an explicit UTC cast on the kickoff_at timestamp:

-- New: cast kickoff_at to UTC date before comparing
AND ((game.kickoff_at AT TIME ZONE 'UTC')::date
    < week.start_date
  OR (game.kickoff_at AT TIME ZONE 'UTC')::date
    > week.end_date)

AT TIME ZONE 'UTC' converts the timestamptz to a plain timestamp in UTC, then ::date extracts the date portion. The game that kicked off at 11:30 PM ET on Sunday is now correctly seen as a Monday kickoff in UTC, and it lands in the right week bucket.

The migration discipline around it

This fix landed as migrations 0074 and 0075 in a reviewed migration pipeline. Each migration is assertion-only and replay-safe: it replaces the previous completeness validator with a corrected version, and a companion assertions file verifies the SQL is read-only with no CREATE, INSERT, or GRANT statements. The migration policy file was updated to record the new ordinal range, and a release-contract test checks that the old local-date range was removed and the new UTC range is present.

The whole sequence follows the same pattern Statpro has been using for every reviewed migration: generate the SQL, byte-attest the lineage through the snapshot, assert the migration is assertion-only, and pin the policy file. The fix itself was one line. The discipline around it was 60.

The broader hardening

Today was a full day of NFL v2 pipeline hardening beyond the week fix. Nine commits across the afternoon:

• Publisher TLS: the publisher database pool now requires rejectUnauthorized: true on its SSL config and pins the host, port, database name, and user role. No more passing a bare connection string and hoping TLS gets negotiated.
• Profile nullable facts: NFL team profiles can now carry omitted optional facts without the release validator rejecting them. Some teams do not publish a full fact sheet and the validator was treating the absence as a contract violation.
• Stat category privilege: the publisher role's stat-category access was restored after a prior migration narrowed it too far. The assertion file confirms the privilege grant is present and replay-safe.
• Team code aliases: projection input now preserves source team codes and normalizes aliases, so teams with multiple code variants across sources project consistently.
• Record expectations: a new phase-expectation function counts expected team record rows by deduplicating official team UUIDs across games in a phase, rather than counting games.
• Media contract ordering: the publisher repository now pins media contract ordering deterministically, so test fixtures do not flip on insertion order.

The pattern across all of it: each fix is one small behavioral change, wrapped in a reviewed migration with assertion-only companions, pinned in the policy file, and verified by a release-contract test that checks the exact SQL text. The ratio of discipline to fix is high on purpose. The NFL data pipeline is the part of Statpro where a silent wrong answer is worse than a loud crash, so every change earns its own audit trail.