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>
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