et-day-boundary-fixes

ET Day-Boundary Fix Pass

Shipped: 2026-08-05 (entirely-api 5e17480 + migration 032, deployed). Follow-up to date-parser-consolidation — the ~16 sites its sweep agents flagged, all fixed in one deliberate pass.

The bug class

A visit at/after 8 PM ET is the next calendar day in UTC. Any site that sliced a timed date to its UTC day and compared/displayed it in an Eastern-time context was off by one for evening work. Fix pattern: config.extract_date_et on the value side, ZoneInfo("America/New_York") on the “today” side.

What was actually affected (evening visits only)

• Field arrival/completion never matched “today” → visit status stuck.
• Visit-completion prompt and same-day approval alert never fired that day.
• inspection_date stamped a day late on status advancement.
• 3-business-day SLA clock started a day late; cert-queue legacy DATE_CUTOFF could misroute a Dec-31-evening inspection into the 2026 pipeline.
• ADM-MISS no-show invoice text, payment-recon aging (days_outstanding inflated by 1 between 8 PM and midnight ET), holding-pool cancel window / days_held, and invoice/cert “Inspection Date” rendering used the UTC day.

Migration 032 (the structural half)

site_visits.scheduled_date/requested_date still stored SmartSuite JSON blobs. Consequences found during the fix: the P9 create-visit dedup’s lexical range window could never match blob rows (dedup was broken, not just ET-wrong), and migration 008’s get_site_visits_in_range RPC + expression index were pinned to the blob format. 032 normalizes both columns (41 rows mislabeled include_time=true at exact midnight corrected to date-only — verified against the real time-of-day distribution, which clusters 8 AM–5 PM ET), drops the blob index (~900 rows, seq scan is fine), and rebuilds the RPC on sv_et_day(text) — a SQL mirror of extract_date_et (blob-tolerant for the deploy gap, unflagged midnight-Z = date-only). Callers fixed at the SQL layer: branch schedule previews, Friday cross-system, pier-log requests, two calendar feeds. The P9 dedup is now a Python-side ET-day compare.

Verification

19 regression tests (test_et_day_boundary.py 16, test_et_dedup.py 3 — including the exact 9:30-PM-ET-visit dedup scenario); suite 395 passed. RPC verified live post-migration. Exactly one stored row shifts ET day (a genuine 01:30Z evening visit) — matches expectation.

Fix inventory: memory project_et_day_boundary_fixes (resolved).