Round 3 of parity fixes after the user shared side-by-side screen
recordings of our build vs. the original SPA. 72/72 tests, frontend
typecheck/lint/build clean, main entry 16.95 KB gz.
Airtable booking → person linkage (root cause of empty Resource
Availability + Daily Breakdown):
- normalise_booking now tries Resource / Booking Resource / Booked
Resource / Resource (from Booking) / Resource Name (from Resource)
as fallbacks for resourceRecordIds — first non-empty wins. Only
values matching `rec` + 17 chars are kept.
- One-shot LOG_AIRTABLE_SCHEMA_ONCE info log on the first booking
response so we can see what fields the live base actually returns.
Flip to False once we've confirmed the field name.
- Name-based fallback in department.py already in place.
Charts:
- DeptWeeklyChart: two bars per entity. Bar 1 stacks Soft Booked + Active
Booked. Bar 2 stacks Allocated + per-billing-category. Red Avg %
utilisation line crosses both. Legend gains Active/Soft Booked +
Avg %.
- DailyBreakdownModal: two bars per weekday (allocated + billing),
Active Booked + Soft Booked pills at the top, full two-row legend
matching frame f020.
Filters:
- New GlobalFilterBar (Division/Brand/Hub/Role/From/To/Reset) lives
above the tab nav in ProtectedShell, visible on every page.
- New DepartmentFilterBar (Name/Division/Brand/Employment) lives inside
Department.tsx only.
- Resourcing / Bookings lose their redundant inline FilterBar — global
one covers them.
Forecast:
- Pipeline chart bars now stacked by project type (PIPELINE_TYPES
palette). Legend below the chart includes the type colours +
Exiting + Forecast avg.
- New WeeklyComparisonTable below the pipeline: This Week / Next Week
/ Week +2 / Week +3 × project type, Active / Exit counts per cell,
totals row.
- "Last Week" subtitle now reads "Full week actual hours (Mon–Fri)" —
matches the original SPA's semantic.
- Backend: ForecastWeek gains activeAssetsByType + exitingByType maps.
Project Type Summary:
- Selected-type detail panel below the table: avg h/asset + avg
duration tiles (with min–max range), totals line, dept hours
segment bar with colour legend, Insights & Recommended Actions
panel, Panel 1 chart (avg h/asset by completion month, stacked by
division).
- Backend: ProjectTypeStatExtended gains deptHoursBreakdown,
monthlyAvgHoursPerAsset (with byDivision), minDurationDays.
Adhoc People:
- Department page now surfaces a small warning card next to the dept
pills listing the top 6 unmatched Zoho submitter emails + a "+N
more" count.
Header subtitle:
- Reads "<filename> · last updated <localised dd/mm/yyyy hh:mm:ss>"
when parsed_at is present in the parse response. Backend's
/api/timelog/parse now emits parsed_at (ISO 8601). Falls back to
row count if missing.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Three big pages rebuilt against the original's screen recording — addresses
the "the chart is not dynamic" + "see booking and timesheet together"
feedback. 72/72 backend tests (+12 new), frontend typecheck/lint/build
clean, main entry 16.43 KB gzipped.
Department page:
- Department pill subnav (Creative / PM Team / Syndication / Transcreation /
Opera Upload / Operations / Adhoc People). Replaces dropdown.
- Last Week / This Week / Next Week pills with date-range labels +
month-preset pills derived from the uploaded timelog.
- HOURS & UTILISATION strip: 6 tiles — Allocated (net of leave), Time
Logged (net of leave), Active People, Actual %, Forecast % (amber
highlight), Leave Hours.
- BILLABILITY BREAKDOWN strip: Fee Related, Client Related, Non-Billable,
Total Billable %.
- FTE VS FREELANCER section: two large composite cards, each with nested
Actual + Forecast utilisation sub-cards.
- DeptWeeklyChart (new): per-person stacked bars with billing-category
colours and red Forecast % line overlay. Click a name → DailyBreakdownModal
with per-weekday stacked bars + red avg-% line. Click a bar segment →
project-logs panel for that segment.
- DeptBookingVsActual (new): grouped per-person bars (Active / Soft /
Actual).
- ResourceAvailability (new): sortable two-section table (FTE / Freelancer)
showing each person's Active Booked, Soft Booked, Booked %, Logged h,
and Status — the "booking + timesheet together" view.
Forecast page:
- 12 KPI tiles in two rows (Weekly Team Capacity → Active Next Week).
- Last Week / This Week toggle + "Project Summary loaded" badge.
- Weekly Pipeline chart: active-count bars + red exit-rate line + dashed
forecast-avg baseline. Click a bar → drill into that week.
- Right-hand "How this forecast is built" sidebar with prose explanations.
Project Type Summary page:
- "Project Type Benchmark" header + coverage callouts (months in timelog,
warning about projects starting before coverage, recommended export
range).
- Month-preset pills.
- Sortable Summary by Project Type table — 10 columns including avg
assets/week and avg projects/month.
- Per-type detail panel below: monthly assets trend chart + project list.
- Bottom: Avg H/Asset and Avg Duration totals + Insights & Recommended
Actions section with auto-generated outlier callouts.
Backend additions:
- /api/utilisation/department, /api/utilisation/daily-breakdown
- /api/forecast extended with thisWeek + nextWeek capacity blocks
- /api/project-types extended with monthly trends, project lists,
longest-project tracking, coverage section, insights
- services/department.py + tests/test_department.py
- Booking model gains resourceRecordIds so daily breakdowns can match
bookings by Airtable record ID, not just flattened name
Tutorial selectors preserved + new ones added: dept-pills,
resource-availability, forecast-sidebar, forecast-canvas.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Real Zoho time-log exports carry ~120 columns; we only consume ~20. The
parser was reporting every unused header (Project Billing Client, Task
Stage, Project Owner Email, … ~90 of them) under "Unrecognised columns",
which surfaced a multi-line warning banner on every upload even though
nothing was wrong.
New semantics — `unrecognised_columns` now lists only REQUIRED canonical
fields we COULDN'T locate (date / submitter / hoursLogged). Empty list
on every clean export. Surfaces the actual signal: "Zoho renamed
something you depend on" — buried before, prominent now.
- zoho_parse.py: extras silently ignored; only missing requireds reported.
- UploadButton banner copy: "Couldn't find expected columns: …" with a
hint that charts will be incomplete.
- Tests updated: extras don't trigger, missing requireds do.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Brings the new app to full parity with the original L'Oréal SPA and
beyond. Backend 59/59 tests (was 40, +19). Frontend typecheck/lint/build
clean. Main entry chunk 15.76 KB gz (budget 30 KB).
Backend — new endpoints + services:
- POST /api/deliverable/parse — parse Deliverable Summary CSV/XLSX
- POST /api/projectsummary/parse — parse Project Summary CSV/XLSX
- GET /api/timelog/rows — paginated, searchable, sortable view
over the parsed Zoho upload
- GET /api/forecast — 4-week pipeline + capacity decision
- GET /api/project-types — hours/asset, duration, concentration
per project type + auto-insights
- POST /api/chat — Claude API proxy. 503s gracefully
when ANTHROPIC_API_KEY is unset.
Prompt-cached system prompt;
rate-limited 20/min/IP.
- GET /api/auth/me now returns role.
Backend — services:
- zoho_parse.py: extracts ~20 fields (brand, division, hub, userRole,
projectType, assetCount, projectStatus, project start/end dates,
userAgency, employingCompany, sageJobProfile, …) with back-compat
aliases so existing callers keep working.
- parse_store.py: in-process TTL-cached registry of parsed uploads keyed
by content hash. Lets endpoints reference an upload without re-sending it.
- forecast.py: working-day overlap math, exit-rate, weekly throughput
baseline, capacity decision string mirroring the original wording.
- project_types.py: per-type aggregation + concentration-risk insights.
- timelog_filters.py: server-side filter by brands/divisions/hubs/roles.
- ai_context.py: builds the dashboard context block fed to Claude.
Frontend — new pages + components:
- pages/Forecast.tsx — ComposedChart (stacked bars + line)
+ capacity-decision banner + table
- pages/ProjectTypeSummary.tsx — sortable table + small trend chart
- pages/TimeLogDetail.tsx — virtualised, searchable, sortable
view over all parsed timelog rows
- components/ChatView.tsx — floating side panel with Claude.
6 preset prompts mirroring the
original. Visible only for roles
with chat access.
- components/ChatToggle.tsx — bottom-right FAB.
- components/StatsBar.tsx — always-visible: Time Entries /
People / Projects / Total Hours /
Date Range.
- hooks/useDataContext.tsx — single source of truth for filter
state + parsed upload + filter
dimensions (brands/divs/hubs/
roles derived from uploads).
Frontend — modified:
- App.tsx, Navbar.tsx — 7 tabs + role gating per the
original TAB_ACCESS matrix.
- hooks/useAuth.tsx — role + canAccess(tab).
- lib/filters.ts, FilterBar.tsx — Brand / Division / Hub / Role
multiselects added (additive — keep
Department / Name / Billing).
- pages/Department, Resourcing,
Bookings, Tutorial.tsx — wired into DataContext; tutorial
is now a single 9-step global tour
mirroring the original's narrative.
Config:
- backend/.env.example: ADMIN_ROLE, ANTHROPIC_API_KEY, ANTHROPIC_MODEL.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Major parity push against the original SPA's bundle-level feature set
(non-architectural items — separate Forecast / ProjectType / TimeLog
views and AI Chat remain TBD).
Backend (40/40 tests, +7):
- merge.py splits booked hours by booking status: active vs soft.
Active set: Active, Active Booked, Fully Booked, Partially Booked.
Soft set: Soft Booking, Soft Booked, Soft-Booked. Unknown statuses
default to active so they're not silently dropped. Existing
`bookedHours` field is preserved as the sum for back-compat.
- compute_totals(): rolls KPIs across the filtered summary —
totalBooked, activeBooked, softBooked, totalLogged, totalBillable,
totalLeave, totalProjects (distinct projectName/projectNumber),
activePeople (distinct employees with logged>0), allocated,
allocatedNetOfLeave.
- breakdown_by_project(): drills into a single period+employee (or
whole-period) and returns per-project logged + booked hours.
- New /api/utilisation/breakdown endpoint. /api/utilisation/summary
response gains `totals` and accepts `period=week|month`.
- New schemas: UtilisationTotals, BreakdownResponse, plus
activeBookedHours / softBookedHours on UtilisationSummaryRow.
Frontend (typecheck/lint/build clean):
- KpiTiles component shows Total Booked / Logged / Billable, Total
projects, Active People (logged), Active bookings, Avg/person/week
or /month, Allocated (net of leave), Period covered.
- PeriodToggle (Per day / Per week / Per month). Day is rendered
disabled with an explanatory tooltip — backend only accepts week/
month.
- HourBreakdown drill-down panel: per-project logged + booked rows,
shown when a chart bar is clicked; "Upload a time log to see logged
breakdown" empty-state when no upload yet.
- MonthlyUtilisation: ComposedChart with stacked Active/Soft booked
bars + forecast Line overlay driven by the existing showForecast
toggle. onPeriodClick wired into HourBreakdown.
- WeeklyUtilisation, BookingVsActual: same Active/Soft stack treatment.
- Resourcing now passes the timelog hash through to summary so
loggedHours actually populates there too (was 0 before).
- Sync Airtable button on both Department and Resourcing — force-
refreshes bookings cache, re-derives summary.
- Tutorial steps re-mapped to the original SPA's chapter titles:
"Reading the Utilisation Chart", "Hours & Utilisation", "Drill-In",
"Forecast Line & Filters", "Spotting Resource Issues", "Sync
Airtable Bookings". Tutorial page heading is now "Interactive
Walkthrough" with the original copy.
- Defensive coercion in Bookings table totalHoursBooked rendering.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Backend (33/33 tests, +5 new):
- Split Zoho parser's canonical "billable" into "billable" (bool column)
and "billingType" (string column with values like "Client Related" /
"Leave Hours" / "Idle Time"). Each parsed row now carries both, and
billable is cross-filled from billingType when only the latter is
present.
- Merge service computes leaveHours separately from non_billable_h: any
row with billingType "leave hours"/"leave" lands in the leave bucket
and is no longer double-counted as non-billable.
- UtilisationSummaryRow gains leaveHours: float; TimelogRow gains
billingType: str | None.
- /api/airtable/bookings accepts ?department=&name= (comma-separated
multi-value), folded into the filterByFormula alongside the date
overlap. Apostrophes in names are escaped. Cache key now includes
the filter values so different selections don't collide.
- /api/airtable/meta computes departments + employmentTypes from a
live fetch_resources call (sorted distinct), falls back to the
hardcoded lists on any exception. billingTypes/bookingStatuses
stay static.
- Logout cookie now mirrors the login cookie's HttpOnly / Secure /
SameSite / Path attributes with max_age=0 and empty value, for
consistency.
Frontend (typecheck/lint/build clean):
- types.ts: UtilisationSummaryRow.leaveHours: number.
- BillabilityBreakdown uses r.leaveHours directly; idle becomes
max(0, available - billable - nonBillable - leave). Capped to top
20 employees by (available + billable) with "Other (N)" rollup;
Legend replaced with compact inline swatches.
- BookingVsActual and FTEvsFreelancer: same top-20 + Other treatment
to prevent the ProjectLoad-style x-axis explosion at scale.
- Defensive sweep on WeeklyUtilisation, MonthlyUtilisation,
BookingVsActual, FTEvsFreelancer: null-coerce sort keys, Number()-
guard arithmetic, skip rows with no usable period/employee.
- getBookings signature gains department + name; Resourcing passes
them through. Client-side visibleBookings filter retained as
belt-and-braces since linked-lookup filterByFormula on Airtable
can be flaky.
- Tutorial steps.ts restructured to cover the new chart and CSV
export tags; existing TutorialOverlay defensive selector check
preserved.
- ErrorBoundary: removed dead eslint-disable directive flagged by
--report-unused-disable-directives.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
A full-year Zoho time-log export can run 30-60 MB and was getting
rejected with {"detail":"Payload too large"} on the Department tab
upload. 20 MB was an under-cautious default; 100 MB matches what
Apache will pass through without a LimitRequestBody override.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
`Resource Name`, `Project Number (from Master)`, `Project Name (from
Master)`, `Department (from Resource Name)` on Booking Resource are
linked-record / lookup fields — Airtable always returns them as lists,
even when cardinality is 1. The previous booking normaliser only
handled one specific list case (`Resource Name (from Resource)`) and
let the other field aliases pass through as raw lists. The merge
service then crashed with `AttributeError: 'list' object has no
attribute 'strip'` on first call to /api/utilisation/summary in prod.
Introduce a `_flatten()` helper and apply it to every linked/lookup
field in `normalise_booking`. Also make `_name_key` in the merge
service defensive against raw lists so a missed flatten elsewhere
degrades gracefully instead of 500ing.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Replaces a static SPA that shipped an Airtable PAT in the JS bundle.
The new architecture holds all secrets server-side, fronts the app
behind Apache on optical-dev with the shared-vhost split-build pattern,
and is designed for a later Azure AD/MSAL swap-in.
- backend/ FastAPI + uvicorn, local auth (Azure AD stub), Airtable
proxy with TTL cache, Zoho .xlsx/.csv parser, merge
service for utilisation summaries. 28 pytest tests.
- frontend/ React + Vite + TS + Tailwind + Recharts SPA. Login entry
chunk 12.83 KB gzipped; Recharts lazy-loaded. No tokens
or Airtable URLs in the built bundle.
- deploy/ Idempotent deploy.sh (port auto-pick 8200-8299,
.env-persisted) + split-build Apache include template.
- docker-compose.yml pins name: utilisation-dept and binds 127.0.0.1.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
DJP