status-advancement

Status Advancement (project status reconciler)

services/status_advancement.py — daily 6:30 AM ET cron (worker) that reconciles project statuses against site-visit data. Rules: 0) Scheduled→Field Complete when all visits completed in the past; 1) Scheduled→In Progress on visit day; 2) In Progress→Field Complete when all visits completed; 3) In Progress→Scheduled reset on missed visits.

Connections

• connects to: Supabase
• feeds: auto-validation chain (Field Complete → run_auto_validation)
• hooked from: supabase_write_service.update_site_visit (Completed hook)

Notes

• 2026-07-15 (session 58): Fixed silent-truncation bug (entirely-api 240c0c9). The cron loaded an unfiltered limit=2000 window over a 7,126-row projects table — recently created projects (MAN-9105, MAN-9123, MAN-9124) were invisible; runs logged errors=0 with zero transitions. Scan now filters server-side to Scheduled/In Progress only (~14 rows) and warns if either scan hits its row cap.
• Same commit added the visit-completed hook: update_site_visit now fires advance_on_visit_completion(visit_id) when a visit is marked Completed (mirrors the No-Show hook). Advances the single project immediately — Field Complete + inspection_date + auto-validation — instead of waiting for the next cron. The cron is now the safety net, not the only path.
• History: unhashable type: 'list' list-shaped single-select bug fixed earlier (1ab50fc); Rule 0 added 11434fa; inspection_date write added 0735bfd. See HANDOFF_STATUS_ADVANCEMENT.md (now resolved).
• Field close-out design: Darius’s “YES completed” reply (WhatsApp /visit/process-reply) writes only site_visits.visit_status. Project advancement was event-driven only via Rob arrival photo (Scheduled→In Progress) or all-SOW-lines-inspected (F4 chain). The completed hook closes that gap.
• 2026-10-10: Field Complete now comes BACK when a visit re-opens. status_advancement.reset_on_visit_reopened (entirely-api 2eb63fb) fires from supabase_write_service.update_site_visit whenever a status write is anything but Completed, and from create_site_visit for a firm non-desk-review visit (reopen_check=True; the Week Board’s tentative hold passes False). A Field Complete project with a firm open visit (Scheduled / En Route / On Site, not tentative) goes to In Progress + inspection_date today when the visit is today, else Scheduled; Desk Review, cancelled, tentative-only skip. Trigger: MAN-9255 sat at Field Complete after its 10/05 visit was flipped Completed → No-Show and a 10/10 return visit booked. Same commit: the calendar card’s “visit N of M” counts No-Show trips (only Cancelled is excluded), so a return visit after a no-show reads 2 of 2.