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>
Pure styling sweep — no behaviour changes, no new dependencies, all
data-tutorial-id selectors preserved. typecheck / lint / build clean.
Main entry 16.36 KB gz (was 12.85), recharts unchanged at 154.57 KB gz.
Foundations:
- body bg-slate-950 text-slate-100, color-scheme:dark, Apple-system font.
- .btn-primary indigo, .btn-secondary slate-700, .card slate-900 +
slate-800 border, .input slate-800/600, .label slate-400 uppercase.
Top chrome:
- Navbar rewritten as a two-row header: title row with brand + dynamic
subtitle (filename + row count once a timelog is loaded), then the
three coloured upload pills inline, then user identity + Sign-out text
link. Tab row hangs off `border-b border-slate-800` with the
original's rounded-top "tab" look — active `bg-slate-800 text-white
border-t border-x border-slate-700`, inactive `text-slate-400`.
- HeaderUploads now matches the original: indigo Time Log pill, emerald
Deliverable when loaded, violet Project Summary when loaded; neutral
slate-700 surfaces when empty. Re-uploading replaces (no Clear
button). Errors collapse to a small ⚠ marker so the header height
doesn't jump.
- StatsBar: bg-slate-900/50 strip below the navbar with five stacked
stats (label slate-400 / value white).
Filter rail:
- bg-slate-900/30 strip with stacked-label fields and slate-800/600
inputs. Native <select multiple> capped to h-20 for the brand/division/
hub/userRole multiselects (closer to the original's selects than our
prior chip pattern). Reset is a slate-400 text link, pushed right.
Filter blocks only render when their option list is populated.
Charts (all six):
- CartesianGrid stroke #334155, axis ticks #94a3b8 on #475569 axis lines.
- Tooltip contentStyle: slate-800 surface + slate-700 border + slate-200
text, indigo cursor highlight. Tooltip filterNull preserved on Project
Load.
- "Available"/"Idle" greys swapped to slate-600 so they're visible on
dark. Primary booking blue swapped to indigo so it harmonises with the
rest of the indigo accent set.
Side panel + FAB:
- ChatView panel slate-900 / slate-700 border, indigo user bubbles,
slate-800 assistant bubbles. Repositioned to bottom-24 right-6 so it
clears the FAB. ChatToggle now indigo, moved to bottom-6 right-6.
Pages:
- Login: slate-950 page, slate-900 card, indigo affordances.
- Department / Resourcing / Bookings / Forecast / ProjectTypeSummary /
TimeLogDetail / Tutorial all recoloured. Forecast's capacity-decision
banner is inlined rather than using .card so it can carry the
colour-coded tint. Bookings virtualised table re-skinned to slate-800
header / slate-300 rows.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Two bugs you pointed out (Time Log Detail / Project Type Summary had no
way to upload anything, yet were the pages that need an upload to show
data):
1. The big UploadButton lived only on Department. Moved a compact
HeaderUploads tray into the Navbar — three slots (Time Log /
Deliverable / Project Summary), each showing filename + row count
when loaded, "Upload" / "Replace" / clear actions, drag-and-drop
removed in favour of the simpler picker since the tray is small.
2. DataProvider was inside <ProtectedShell>, which is rendered per
Route. Every nav click remounted it and silently wiped the uploaded
timelog state, which is why Time Log Detail showed "Upload a time
log to see rows" even after a Department upload. Lifted DataProvider
to main.tsx, above <Routes>. Upload state now survives navigation.
3. Department page lost its duplicate big UploadButton (the tray
handles it now) — kept the parser-warning banner inline since it's
still useful context next to the filter bar.
typecheck / lint / build clean. No bundle-size regression.
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>
Clears the six npm deprecation warnings that fire on every deploy:
inflight, @humanwhocodes/config-array, rimraf, glob,
@humanwhocodes/object-schema, and eslint 8.57 itself — all transitive
deps of the eslint v8 chain.
- eslint 8.57 → 9.10
- @typescript-eslint/{parser,eslint-plugin} 7.x → typescript-eslint 8.7
(single combined package in v8)
- eslint-plugin-react-hooks 4 → 5 (v5 supports flat config)
- eslint-plugin-react 7.34 → 7.36
- Replace .eslintrc.cjs with eslint.config.js (flat config)
- `npm run lint` switches from --ext flag (legacy) to flat-config lookup
typecheck silent, lint silent, build identical bundle (45.93 KB raw,
15.76 KB gzip on the main entry).
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>
`b.totalHoursBooked.toFixed(1)` would crash if Airtable ever returned
null/undefined for the rollup field; coerce through Number() with 0
fallback. Also coerce nullable department/resourceName in the client-
side filter check to ''.
Flagged by the previous frontend audit as a follow-up risk.
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>
Real Airtable bookings have 100+ unique project names in a single
month. The previous chart rendered one <Bar>+legend entry per project,
which flooded the legend off-screen and broke the page layout entirely
(screenshot 2026-05-16).
- Aggregate sorts projects by total hours and shows top 10 only; the
remainder are collapsed into a single greyed "Other (N)" stack so
the totals still add up but the visual stays sane.
- Remove the <Legend> entirely. With long L'Oréal-style project names
even 10 entries dominate. The tooltip (capped to 360px wide, with
filterNull) handles per-segment lookup on hover.
- Wire FilterBar's department/name selections through to the chart on
the Resourcing page. The backend's /api/airtable/bookings endpoint
doesn't accept those params yet, so filter client-side for v1.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Resourcing page went blank in prod the first time a booking arrived
with a null resourceName (Airtable placeholder rows, or any booking
whose resource lookup didn't resolve). The Project Load per Person
chart's aggregate then crashed on `null.localeCompare(...)` during
sort, and with no error boundary in the tree, React unmounted the
entire route — visible to the user as "loads, then goes blank".
- ProjectLoadPerPerson.aggregate: coerce nullable lookup values to
scalars before keying/sorting; treat empty resourceName as
"Placeholder", missing project name as "Unknown", and NaN totals
as 0.
- New ErrorBoundary component (class — React still requires class
for boundaries) that renders a card with the error message instead
of unmounting the parent.
- Wrap each chart on both Resourcing and Department in its own
ErrorBoundary so a future chart-specific bug only blanks that one
card, not the whole page.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
The collision check filtered self-matches by /opt/${SLUG}/, which only
works when the on-disk directory matches the URL slug. When the repo is
cloned to a different directory (e.g. /opt/loreal-utilisation-dept/ to
match the Bitbucket repo name while keeping URL slug "utilisation-dept"),
the script flagged its own apache-*.conf as a foreign collision and
refused to redeploy. Filter against \$REPO_ROOT instead, which holds the
actual on-disk path and matches what grep -l emits.
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>
The repo can be cloned to any directory (e.g. /opt/loreal-utilisation-dept
to match the Bitbucket repo name) — the slug we use for the URL path and
compose project is independent of the on-disk path. The previously printed
Apache Include line assumed `/opt/<slug>/`, which broke when the clone
location didn't match the slug. Use $APACHE_CONF (already absolute and
correct, derived from $SCRIPT_DIR) instead.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Move `git pull --ff-only origin main` to the very top of the script so
every deploy reflects the latest committed code. Pull failure is now
fatal (was warn-and-continue). After the pull, hash-compare deploy.sh
against its pre-pull copy; if it changed, exec the new version with a
DEPLOY_RECURSE_GUARD env var to prevent any chance of infinite re-exec.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Fail fast if another app on optical-dev already claims /utilisation-dept/
in the shared Apache vhost or any sibling /opt/*/deploy/apache-*.conf,
and surface which 8200-8299 ports are already taken by sibling apps so
the port picker's choice is visible. Silently skips on dev laptops where
the vhost file isn't present.
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>
