Tracker Workbook Upload (2026-07)

Tracker: Stephaney’s Consolidated Workbook Upload

JES’s system update (~2026-07) removed Stephaney’s access to the Closed Permits report. She now maintains a single all-branches Excel workbook: six branch sheets (BALT/MAN/RICH/ROA/VA BEACH/WVA), a color-legend sheet, an OVERDUES sheet (all branches merged), and a CLOSED sheet (full population, closures marked by green row fill or Notes starting “CLOSED” — convention confirmed with Stephaney 2026-07-07).

Changes

• unlikely-api routes/tracker.py (commits f4b57db, 4c83663):
  • POST /tracker/upload-closures gains workbook mode — any file with a CLOSED sheet. Dual-shape: if the CLOSED sheet mirrors the full OVERDUES population (≥50% of its Project Ids), closures are the green-filled (FF92D050/FF00B050) or “CLOSED”-noted rows (marked mode, today’s shape); a substantially smaller sheet is treated as a curated closed-only roster where every row is a closure (roster mode). Final Date used as close date when present. The sheet’s trailing CLOSED True/False column is her team’s internal recon flag (“verified it’s in Unlikely’s system” — Stephaney 2026-07-07) and is ignored. New response fields: closure_mode, missing_from_report_records (tracker-open permits absent from the OVERDUES sheet; report-only). Legacy single-sheet format unchanged.
  • POST /tracker/upload-overdue prefers the OVERDUES sheet by name, maps the Final Date header, returns workbook_mode.
• entirely-portal TrackerPage.tsx (commit 8d80a0d): “Upload Steph’s Workbook” button — one file, runs overdue sync then closures pass, combined summary.

Dry-run vs 2026-07-01 workbook

118 marked rows → 64 close, 54 already closed, 0 review, 0 unmatched; 706 missing-from-report flagged for review.

Live run 2026-07-07 (commit 36c336c)

First live run hung and exposed two bugs, both fixed:

• Junk text in date cells: Final Date “RTS” (project 188636) → Postgres 400. _fmt_date/_fmt_datetime/workbook close_date now emit only date-like values; per-row error capture (write_errors in response) so one bad row can’t abort a batch.
• rate_limiter middleware hang: its catch-all wrapped call_next, so any app exception was mislabelled “Redis error” and call_next re-invoked on a consumed request → client hung with no response. call_next moved outside the try. (The session-55 IndexError is this middleware’s other, separate bug.)

Result: closures moved 64 (46 dated 2026-07-07 + 18 from Final Date), tracker closed 637→701, open 5,645→5,581. Verified in Supabase. The 706 missing-from-report list is pending review with Stephaney.

Auto-CLOSED workbook handoff (2026-07-08)

Built /home/claude/STEPHS WORKBOOK - AUTO CLOSED.xlsx from her Jul 8 file: branch-sheet CLOSED column (col O) = TRUE now auto-populates the CLOSED sheet via classic formulas (hidden rank helper col P per branch, hidden Q–X helpers on CLOSED; INDEX/MATCH/CHOOSE — works in any Excel version, no macros). Backfill seeded O=TRUE for her 131-row roster ∪ existing branch TRUE flags = 140 rows. Verified via LibreOffice Calc recalc (had to apt install libreoffice-calc — recalc.py’s silent no-op/hang was the missing module; her Export sheet’s 1M-row dims also slow LO): zero formula errors, exact 140-row content match, tracker dry-run on generated CLOSED = roster mode, 27 move / 113 already / 0 review. Capacity: branch rows to 3000, CLOSED 2000. LO round-trip strips her color fills, so the deliverable is the openpyxl build (values compute on her first open in Excel — must be opened+saved once before its first tracker upload).

RTS — Ready To Schedule (commits 7d3e9a4 api / 7dcb33e portal, 2026-07-08)

tracker_permits.rts (bool) + rts_date (date), added via dashboard SQL editor. Tracker table gets a green-check RTS toggle (on = stamps today’s date; off clears both; green when flag or date present) + sortable RTS Date column. upload-overdue ingests three signals — “RTS DATE” column, “RTS” flag column, “RTS” text in Final Date cells (their original convention, previously discarded) — and sweeps branch tabs in workbook mode; uploads set-only, never clear. Workbook v2 (STEPHS WORKBOOK - AUTO CLOSED v2.xlsx, emailed 19f424be9ec5ea75) adds a visible RTS DATE column P per branch tab (closure helper moved to hidden Q); formulas re-verified, 140-row parity.

Workbook v3 (2026-07-08, emailed 19f42a4c6fb4a87c — supersedes v1/v2)

Per Stephaney: closed rows must LEAVE the branch tabs. True auto-move needs .xlsm macros (Groundworks M365 blocks emailed macros), so v3 uses saved AutoFilters instead — branch tabs filter CLOSED=FALSE+blanks; marking TRUE + Data→Reapply hides the row in place while the formula CLOSED sheet picks it up. 140 backfilled closures pre-hidden. OVERDUES + CLOSED sheets password-protected (pw UPpermits26, Steph only; team read-only). Fixed v2’s ###### End Date bug (explicit CLOSED column widths; J/L=12, yyyy-mm-dd). Formulas re-verified: 140-row parity, zero errors.

Workbook v4 (2026-07-12, emailed 19f59294584108cb — supersedes v1–v3)

Rebuilt on Stephaney’s July 12 cloud copy (pulled via Dustin’s SharePoint link → his Gmail). Her team’s adoption artifacts handled: 13 hand-deleted MAN closed rows restored (hidden, CLOSED=TRUE); 14 branch-flagged closures never copy-pasted to the roster captured; stray unlabeled MAN col-P note folded into 191812’s Notes. Portal synced from her file + baked v4: 554 recon notes updated, 34 permits closed (21 roster + 13 flag-only), 0 errors — tracker closed_total 701→735. API commit d3a3114: workbook-mode branch sweep now merges fresh branch-tab notes (825 were newer than the stale OVERDUES copies). Seed rule for rebuilds: roster ∪ branch O=TRUE ∪ restore roster rows missing from branch tabs.

Access control (commits 153161f api / 5960a21 portal, 2026-07-07)

Upload endpoints now require portal JWT or API key. Allowed: up_owner/up_admin, API-key callers, and config.TRACKER_UPLOAD_USERS (= Stephaney only). Her triage-role teammates (Madelyn Bennett, Olivia McGraw, Corbin Washington) keep the tracker page but get 403 on uploads and never see the upload bar (GET /tracker/can-upload drives visibility). Verified: anonymous 401, teammate 403, Stephaney + API key pass.

Related: Tracker, tracker_permits, JES Branch Expansion