# ALTENOVA: Engineering Portfolio Document

## User-confirmed context — 2026-10-18

> Source: direct user answers in the portfolio review. These are user-confirmed statements, not independently rerun repository or production measurements.

- **Ownership:** independently built both the Power Apps application and the Power BI report; deployed the platform and supports adoption and bug fixes.
- **Operational adoption:** deployed to 3 teams. More than 50 people in the original team, including managers, use it daily. Total users across all teams are estimated at more than 80; this estimate is not an audited active-user count.
- **Previous workflow:** multiple annual Excel trackers with many columns, no emails or notifications, and manual transcription and formula-based KPI consolidation taking hours each month. Users also spent more time entering data in Excel than in Power Apps; that saving is unmeasured.
- **Added capabilities:** centralized data; email and in-app notifications for submissions, deadlines, status changes and deliverable rejections; Power BI analysis and visualization.
- **Refresh:** Power BI refreshes every hour. Describe hourly KPI updates rather than immediate real-time refresh.
- **Unmeasured:** exact time saved, error reduction, adoption percentages and validation turnaround. The notifications are user-confirmed functionality; their implementation was not independently inspected during this review.

> Existing draft variants and unanswered placeholders are retained. This confirmed context governs personal ownership, adoption and outcomes; unresolved draft claims are not newly verified.


> Sources of truth:
> - **Operational app:** the unpacked canvas app in [ALTENOVA_v2_1_src/](./ALTENOVA_v2_1_src) (packaged as [ALTENOVA_v2_1_fixed.msapp](./ALTENOVA_v2_1_fixed.msapp)). It is the maintained version of the original export [ALTENOVA.msapp](./ALTENOVA.msapp) / [ALTENOVA_src/](./ALTENOVA_src) from the **ALTEN Group** Power Platform environment (App ID `3c7544db-e217-41a3-8006-135c38ec0700`). Section 0 lists what changed between the two.
> - **Analytics layer:** the Power BI report [KPI_Deliverables_Sharepoint.pbix](./KPI_Deliverables_Sharepoint.pbix) (PBIR report definition + tabular semantic model). It is published to workspace `6ecc219b-…`, which is the same workspace the app's `PowerBI1` tile points to.
>
> Every claim below points to a specific file, formula, DAX measure, or Power Query step.

## 0. Changes since the original export (`ALTENOVA.msapp` → `ALTENOVA_v2_1_fixed.msapp`)

- Latest editable app: [ALTENOVA_v2_1_src/](./ALTENOVA_v2_1_src).
- Repaired import package: [ALTENOVA_v2_1_fixed.msapp](./ALTENOVA_v2_1_fixed.msapp). The original exports are retained.
- Data sources, connections, and runtime feature flags are unchanged (14 data sources, 6 connections).

### Correctness and data integrity
- **Identity:** `App.OnStart` lower-cases the user's mailbox and falls back to `User().Email` (UPN) when the Graph profile call fails or has no mail. This one value (`gblUserEmail`) is used everywhere, including the Responsable person record. `gblUserFound` marks unregistered users, and a missing `isArchive` value is treated as active.
- **Hard-coded role override removed:** the original `OnStart` forced one named user to `TL`. Roles now come only from `user_lookup_new`.
- **Welcome gate:** "Open workspace" blocks unregistered users ("Your account is not registered…") as well as deactivated ones before preloading data.
- **Week generation (create):** weeks are built date-by-date from the Monday of the reception week to the Monday of the realized week, using `ISOWeekNum`. This replaces `WeekNum` with a `StartWeek…EndWeek` sequence, which broke across New Year. Going back from the weeks page keeps hours already typed, and deliverables longer than 52 weeks are rejected because `week_lookup_new` stores only the week number.
- **Create transaction:** the deliverable is written first. All weeks are then created in one bulk `Collect`. If that fails, the weeks and the deliverable are removed again, so no deliverable is left without its weeks. The submit label reads "Submitting...".
- **Catalog check on create:** if the selected deliverable ID does not belong to the user's team (role-aware `LookUp`), the user is stopped before page 2. The deliverable-name lookup now also matches on Scope.
- **Responsable on create:** TL/CP can assign the deliverable to the people they manage (`colResponsableOfDeliverables`); consultants are still limited to themselves.
- **Week reconciliation (edit):** weeks are rebuilt only for a valid date range and only in edit mode. Unsaved hours on screen are kept for weeks still in range. Weeks crossing New Year sort correctly through a `SortKey`. More than 52 weeks is rejected. On save, existing weeks are updated in one bulk `Patch`, new weeks are added in one bulk `Collect`, and removed weeks are deleted from the local `colDbWeeks` snapshot (not limited by the delegation row cap). Each step reports partial failures. Cancel restores the saved weeks and the form. Workload minimum is now 1 hour.
- **Deliverable data (catalog):**
  - Navigation and the edit form use the SharePoint `ID` instead of `Title`, because IDs such as `CRO5` are not unique across teams/scopes.
  - New IDs take the next number after the highest ID with the same prefix across the **whole list**, and are checked again for duplicates at submit time.
  - Success messages and navigation moved into `OnSuccess`; failures show the form error. Previously the success toast and navigation ran even when validation failed.
  - Edit reads "Edit"/"Cancel"; Cancel discards changes. Saving keeps existing Team, Global Scope, and Scope values. Team is editable only on records without one, with role-scoped choices (CP: every team; others: `colUserTeam`).
  - The catalog list shows only the user's own global scopes under "All" (CP sees everything), and Refresh now reloads the catalog instead of the deliverable log.
- **Deliverables list:** a new "All" status (now the default), a blank date falls back to the current month, and a TL with no person selected sees everyone in their teams (one delegable query per person). Opening a deliverable no longer pre-loads weeks; the edit screen does this in `OnVisible`.
- **Deletes:** deliverable and user deletes require a second click to confirm. Flow errors and non-`200` responses are reported with the response value.
- **RFT display** on the edit screen parses decimals independently of locale.
- **Removed:** the two `Debug` analytics prototype screens (charts, filters), the original `NextIcon` on Welcome, and the user-form diagnostics label. Leftover French messages are now in English.

### User management
- A searchable directory (name, email, employee ID) beside a wider editor, selected-person highlighting, explicit account status ("Active"/"Deactivated"), and a persistent titled save toolbar. Identity is first, related fields share columns where space permits, and panels stack on narrow screens. Search preserves CP/TL team scoping and existing edit/delete restrictions.
- User editing flow: selecting a row (or its Edit button) loads it into the editor; non-CP users cannot open CP accounts. While a save is in flight (`locIsSavingUser`), every action is disabled to prevent double submits. After a successful save, the saved record stays selected in Edit mode. Form errors also appear inline below the save button. Refresh re-reads the selected user and discards unsaved changes. Deleting the user currently in the editor resets it to a new-user form.
- User email/identity: the directory identity is chosen only when creating a user. In Edit mode the email card is hidden and the existing person record is resubmitted unchanged. Saving fails for a blank email, and the duplicate check excludes the record being edited. Directory users without Mail use their UPN.
- Role gating: User management and Power BI no longer redirect from `OnVisible`. Their content (user directory/editor and report tile) is visible only to TL/CP.

### UI and layout
- Responsive minimum size: `MinScreenWidth`/`MinScreenHeight` are fixed at 320 × 480 instead of the design size. Button labels collapse to icons below 760 px, and secondary gallery columns hide on narrow templates.
- Header component: shows the "AltenOva" title instead of the screen name, and hides the user name below 640 px.
- Screen titles replace the generic "Please complete all required fields" (e.g. "Add deliverable", "Weekly workload", "Deliverable data").
- Home: rebuilt as grouped navigation cards (Deliverables, Deliverable data, Administration for TL/CP) with a time-of-day greeting, role/team line, and a "Report a bug" link. "Add deliverable" sets `gblResetAddForm` so the form starts clean only when opened from Home.
- Home dashboard: titles and descriptions use automatic text height; cards grow with their content from a 96-pixel minimum, and section/wrapped-row heights follow their cards. This prevents both clipped descriptions and heading-only sections.
- Welcome: an explicit "Open workspace" button replaces the arrow icon, with explicit action sizing and ordering and scroll access on short screens. The greeting uses the signed-in name without an extra profile request, the role line shows "Account not registered" when applicable, and the same "Report a bug" destination as Home sits below "Open workspace".
- Power BI: a page title was added that reserves space for the Home button on small screens.

### Validation and maintenance notes
- Local validation covers package generation and Power Fx syntax across all screens/components. Live connector calls, SharePoint permissions, flow responses, and actual control rendering must also be checked in Power Apps Studio; the bundled App Checker report is from an earlier export, not a fresh validation of this build.
- Editor metadata: controls added in source need complete `EditorState` entries with auto-layout `DynamicProperties`, or the packer turns layout settings into unsupported properties and places the controls first in their containers. Layout minimums must not reference the control's own `Height`/`Width`.

---

## 1. High-Level Project Overview

**One-Line Pitch**
ALTENOVA is an end-to-end delivery-performance platform built on Microsoft Power Platform for ALTEN's consulting teams. It has two halves:
- **Data capture:** a role-based Power Apps front end where consultants, Team Leads, and the PMO log deliverables and week-by-week workload.
- **Analytics:** a Power BI semantic model and 8-page report that turns the data into delivery KPIs: On-Time Delivery, Right-First-Time, allocated days, work hours, and efficiency against actual team presence.

**Problem & Solution**

| Problem | How ALTENOVA's architecture addresses it |
|---|---|
| Deliverables were tracked inconsistently across teams. There was no standard catalog of deliverable types, review modes, or quality criteria. | A **standardized deliverable catalog** (`deliverable_lookup_new`) with Team → Global Scope → Scope hierarchy, Standard Review Mode, Control Criteria, Counting Unit and IQP. Only CP/TL can maintain it ([AddDelivrableDataPage.fx.yaml](./ALTENOVA_v2_1_src/Src/AddDelivrableDataPage.fx.yaml), [ModifyDeliverableDataPage.fx.yaml](./ALTENOVA_v2_1_src/Src/ModifyDeliverableDataPage.fx.yaml)). |
| Workload was reported as one lump sum, so nobody could see weekly capacity. | Every deliverable is split into **per-week workload rows** (`week_lookup_new`, 1:N). The rows are generated automatically from the request and delivery dates ([AddDelivrablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/AddDelivrablePage.fx.yaml) → [NextPageAddDelivrablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/NextPageAddDelivrablePage.fx.yaml)). |
| Different people need different views: a consultant sees only their own work, a Team Lead sees their team, the PMO sees everything. | **Identity-driven RBAC with row-level data scoping**. The app resolves the user's Microsoft 365 profile when it starts and maps it to a role, teams, and scopes ([App.fx.yaml](./ALTENOVA_v2_1_src/Src/App.fx.yaml)). |
| Delivery-quality KPIs (OTD, RFT, delay) were computed by hand in spreadsheets. | The app **previews OTD/RFT/delay as the user enters data** ([NextPageAddDelivrablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/NextPageAddDelivrablePage.fx.yaml)). A **Power BI star-schema model with 23 DAX measures** then computes them consistently across every slice: team, consultant, scope, and month. |
| Managers couldn't tell whether the effort spent on deliverables matched consultants' actual availability. | Power Query **merges the delivery data with separate staffing-plan Excel workbooks from two sites (Morocco, India)**. The data is unpivoted into a presence fact table, and DAX computes **Efficiency, Utilization, and Productivity**. |
| Analysis and action lived in separate tools. | **Two-way integration:** the Power BI report is embedded in the app (TL/CP only), and a **Power Apps visual is embedded in the report**. On the "Monthly Deliverable Validation" page, managers validate deliverables from inside the dashboard. |
| Bad data got in through free-text entry: wrong dates, missing fields, negative hours. | A **33-rule client-side validation layer** with per-field error messages and date-ordering constraints. |

---

## 2. Comprehensive Tech Stack & Tooling

> This is a **low-code / pro-code hybrid** project on Microsoft Power Platform. There is no `package.json`, Dockerfile, or CI pipeline. The categories below reflect what the repository actually contains.

### Languages & Runtimes
- **Power Fx**: Microsoft's declarative, Excel-inspired functional language. About **11,300 lines** of formula YAML across 11 screens, the `App` object, and 1 component (`Src/*.fx.yaml`). The original export had about 10,700 lines across 13 screens.
- **DAX** (Data Analysis Expressions): 23 measures, 3 calculated columns, 1 calculated date table, and 1 visual calculation in the Power BI semantic model.
- **Power Query M**: 9 ETL queries covering SharePoint lists, the SharePoint OData REST API, and Excel workbooks.
- **YAML** (`.fx.yaml` / `.pa.yaml`): source-controlled representation of the canvas app.
- **JSON**: data-source metadata, connection manifests, theme (`Src/Themes.json`), and the canvas manifest.
- **Power Apps canvas runtime** (web/Teams). The app theme is `TeamsTheme`, so it is built to be embedded in Microsoft Teams.

### Frontend / UI
- **Component library:** Fluent UI v9 "modern controls". **260 modern control instances**: `modernButton`, `modernCombobox`, `modernDropdown`, `modernDatePicker`, `modernNumberInput`, `modernTextInput`, `modernToggle`, `modernText`, `modernLink` (versions pinned in [pkgs/](./ALTENOVA_v2_1_src/pkgs)).
- **PCF (Power Apps Component Framework) controls:** `Button`, `Dropdown`, `Header`, `Text` ([pkgs/PcfControlTemplates](./ALTENOVA_v2_1_src/pkgs/PcfControlTemplates)).
- **Layout:** responsive **auto-layout containers** (`groupContainer.verticalAutoLayoutContainer` / `horizontalAutoLayoutContainer`) and the `autoLayout_HeaderFooter_ver1.0` screen template. `MinScreenWidth`/`MinScreenHeight` are fixed at 320 × 480, and button labels collapse to icons below 760 px.
- **Reusable component:** `Component1` (an app header showing the signed-in user), **reused on 10 screens** ([Src/Components/Component1.fx.yaml](./ALTENOVA_v2_1_src/Src/Components/Component1.fx.yaml)).
- **State management:**
  - **17 global variables** (unique `Set` targets), e.g. `gbRole`, `gblUserEmail`, `gblUserGlobalScopes`, `SelectedDelivrable`, `DonneesLivrableTemp`
  - **Screen-scoped context variables** (`UpdateContext`), e.g. the 33 `locValidation*` flags, `locIsSubmitting`, `locIsSaving`, `locIsSavingUser`
  - **10 named in-memory collections** (`ClearCollect`), e.g. `colWeeks`, `colFinalWeeks`, `colDbWeeks`, `colUserTeam`, `colResponsableOfDeliverables`
- **Routing:** imperative `Navigate()` with `ScreenTransition.Fade` / `CoverRight` (26 navigation calls). `StartScreen: =WelcomePage`, `BackEnabled: =false`.
- **Data visualization:** Power BI embedded tile (`powerbi` control bound to a workspace ID). The native `barChart`/`legend` prototype screens of the original export were removed.

### Analytics & Business Intelligence (Power BI)
- **Power BI Desktop / Service.** The report uses the **PBIR** (enhanced, JSON-per-visual) format under `Report/definition/`, and the dataset and report are published (`Connections` → `RemoteArtifacts`).
- **Tabular semantic model** (VertiPaq, Import mode):
  - 7 business tables + 1 calculated date table + 1 dedicated `Measure` table
  - 5 relationships, one of them bidirectional
- **ETL (Power Query M):**
  - Connectors: `SharePoint.Tables` (Implementation 2.0), `OData.Feed` against the SharePoint REST `_api/Web/Lists(guid)/items` endpoint, `SharePoint.Files` + `Excel.Workbook`, and `Web.Contents`
  - Techniques: list/record expansion, `Table.Unpivot`, `Table.Combine`, text cleaning, and a type-mismatch diagnostics query
- **Visuals (8 published pages):**
  - KPI cards and new card visuals
  - Line + clustered-column combo charts (dual axis)
  - Matrices, tables, treemap, pie, and bar/column charts
  - Classic and advanced slicers, a list slicer, and date-hierarchy drill-down
- **Embedded Power Apps visual** (`PowerApps_PBI_CV_…`) that hosts the *Deliverable Validation App* and passes the filtered `DeliverableLog` rows to it as context.

### Backend / API
- **Microsoft Power Automate (Logic Flows):** 2 HTTP-style instant flows called from the app with a typed request/response contract (WADL in [pkgs/Wadl](./ALTENOVA_v2_1_src/pkgs/Wadl)):
  - `DeleteDeliverableFlow.Run(DeliverableID, ID)` → `{ response }`
  - `DeleteItemFromPowerApps.Run(Email)` → `{ response }`
- **Microsoft Graph via Office 365 Users connector:** `UserProfileV2` and `SearchUserV2` (people picker). The original Welcome screen's `MyProfile` call was replaced by `User().FullName`.
- **Other connectors registered** ([Connections/Connections.json](./ALTENOVA_v2_1_src/Connections/Connections.json)): SharePoint Online, Office 365 Groups, Power Apps for Makers, Logic Flows ×2, Office 365 Users. **6 connections in total.**
- **Protocol:** OData/REST under the hood (SharePoint and connector APIs), with **delegation** to push filters to the server.

### Database & Storage
- **SharePoint Online lists** as the relational store (site `LYS_ReportingLivrable`). **5 lists**:
  - `deliverable_log_lookup`: transactional deliverable records (19 business columns)
  - `deliverable_lookup_new`: deliverable catalog / master data (9 columns)
  - `week_lookup_new`: per-week workload rows (child table)
  - `user_lookup_new`: user directory, RBAC, and team/scope assignments (multi-select columns)
  - `scope_lookup_new`: organizational hierarchy reference data (Team → Global Scope → Scope)
- **Column types used:** Person/Group (claims-based), single- and multi-select Choice, Number, Date, Boolean, and Attachments.
- **Client-side caching:** collections hold user context and lookup sets so they aren't fetched again on every screen.
- **Excel workbooks on SharePoint used as analytics sources:**
  - `LIvrable_Coff.xlsx`: deliverable coefficient / allocated-time reference
  - Two staffing-plan ("Planning de production") workbooks, one for the Morocco site and one for the India site, read from their *Month* sheets for monthly presence days
- **Power BI VertiPaq columnar store:** about **10,400 rows** imported at snapshot time (see §4).

### DevOps, Testing & Tooling
- **Power Platform CLI (`pac`)**: `pac canvas download` / `pac canvas unpack` turn the binary `.msapp` into diff-able, source-controllable YAML.
- **Power Apps App Checker (static analysis):** results are kept in [Entropy/AppCheckerResult.sarif](./ALTENOVA_v2_1_src/Entropy/AppCheckerResult.sarif) (SARIF format). There are **0 parser errors and 0 binding errors** (`CanvasManifest.json`).
- **Power Apps Test Studio:** a test suite is scaffolded ([Src/Tests/](./ALTENOVA_v2_1_src/Src/Tests)) with 1 test case.
- **Runtime feature flags enabled** (`CanvasManifest.json`): `errorhandling`, `delayloadscreens`, `formuladataprefetch`, `keeprecentscreensloaded`, `usenonblockingonstartrule`, `enableupdateifdelegation`, `enablepcfmoderndatasets`.
- **Not present:** Docker, GitHub Actions/Azure DevOps pipelines, linters beyond App Checker.

---

## 3. Architecture & Technical Highlights

### 3.1 Identity-driven RBAC with row-level data scoping
**Where:** [App.fx.yaml](./ALTENOVA_v2_1_src/Src/App.fx.yaml), [WelcomePage.fx.yaml](./ALTENOVA_v2_1_src/Src/WelcomePage.fx.yaml), [ShowDeliverablesPage.fx.yaml](./ALTENOVA_v2_1_src/Src/ShowDeliverablesPage.fx.yaml), [UsersManagement.fx.yaml](./ALTENOVA_v2_1_src/Src/UsersManagement.fx.yaml), [HomePage.fx.yaml](./ALTENOVA_v2_1_src/Src/HomePage.fx.yaml)

- **Authentication** is delegated to Microsoft Entra ID (SSO through Power Apps). On `App.OnStart` the app calls `Office365Users.UserProfileV2(User().Email).mail` to get the canonical mailbox, lower-cased, and falls back to the UPN if that call fails. It then joins that against `user_lookup_new` to hydrate the session: `gbRole`, `gblUserGlobalScopes`, `colUserTeam`, `IsAccountArchive`, and `gblUserFound`.
- **Three roles:** `CP` (central PMO), `TL` (Team Lead), `Consultant`. Enforcement happens on three levels:
  1. **Navigation/feature gating:** User Management and Power BI tiles are only `Visible` for `TL`/`CP`. In the current build, those screens also hide their content (user directory/editor, report tile) from other roles.
  2. **Field-level authorization:** `DisplayMode` switches on role. Consultants can only create deliverables in `"Work in Progress"` status, the status picker is read-only for them, and deliverable IDs in the catalog are editable only by `CP`/`TL`.
  3. **Row-level filtering:**
     - Consultants: `Filter(deliverable_log_lookup, …, Responsable.Email = gblUserEmail)`
     - Team Leads: users filtered by team intersection, `CountRows(Filter(Team, Value in colUserTeam.Value)) > 0`. With no person selected, the deliverables list runs one delegable query per team member and merges them with `Ungroup`.
     - CP: unfiltered
- **Account lifecycle:** an `isArchive` flag on the user record blocks entry at the Welcome screen ("Your account is deactivated!"). Users missing from `user_lookup_new` are blocked as well ("Your account is not registered…").
- **Ownership integrity:** consultants can only set themselves as "Responsable". The app builds a SharePoint claims identity (`"i:0#.f|membership|" & Lower(User().Email)`), so consultants cannot log work on someone else's behalf. TL/CP can assign deliverables to the people they manage (`colResponsableOfDeliverables`).

### 3.2 Two-phase wizard with staged state and parent→child transactional write
**Where:** [AddDelivrablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/AddDelivrablePage.fx.yaml) (`GoToNextFromBtn`), [NextPageAddDelivrablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/NextPageAddDelivrablePage.fx.yaml) (`SubmitBtn_1`)

- **Phase 1** runs 12 validation rules. It also checks that the selected catalog entry belongs to the user's team. It then **stages** the whole deliverable in an immutable record (`DonneesLivrableTemp`) instead of writing to the database. This avoids orphan header rows if the user leaves the flow before finishing.
- In the same step it **derives** preview fields, which are shown to the user on page 2 for review:
  - `DelayValue = DateDiff(expected, realized)`
  - OTD: `"100%"` if realized ≤ expected, otherwise `"0%"`
  - RFT, mapped from status with `Switch`: Accepted→1, Rejected→0, Delivered→"Waiting"
  - `DlvTrueID`, a foreign key into the catalog resolved by a role-aware `LookUp`. It is persisted as `deliverable_data_id` and becomes the model relationship to `DeliverableLookup` in Power BI.

  The stored `OTD` / `RFT` / `Days Delayed` columns that Power BI reads are **not written by ALTENOVA**. They are calculated columns in SharePoint (per the code comment in `GoToNextFromBtn.OnSelect`).
- It then **generates** the child week grid date by date: from the Monday of the reception week to the Monday of the realized week, each row is `{WeekStart, NumeroSemaine: ISOWeekNum(WeekStart), HeuresCharge}`. Hours already entered are carried over when the user goes back and forth. More than 52 weeks is rejected, because `week_lookup_new` stores only the week number.
- **Phase 2** validates every week (blank or `< 0` is rejected; 0 is allowed). It then performs the write inside `IfError(...)`:
  1. `Patch(deliverable_log_lookup, Defaults(...))` creates the header and captures the returned record.
  2. One bulk `Collect(week_lookup_new, ForAll(colWeeks, {Title: NewDeliverableRecord.Title, …}))` creates the children, linked by the server-confirmed key.
  3. If the weeks fail, a **compensating rollback** removes any weeks written and the header itself, so nothing is left half-saved. Failures surface `FirstError.Message`.
- **Double-submit guard:** `locIsSubmitting` disables the button (`DisplayMode.Disabled`) and swaps the label to "Submitting..." during the write.

### 3.3 Week-reconciliation algorithm (upsert + orphan delete)
**Where:** [ModifyDeliverablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/ModifyDeliverablePage.fx.yaml) (`OnVisible`, `EditFormBtn_5.OnSelect`, `DataCardValue27_2.OnChange`, `DataCardValue30_2.OnChange`, `UpdateForm.OnSuccess`)

When a user edits the request or delivery dates of an existing deliverable, the set of weeks changes. The app reconciles the old and new week lists instead of wiping and recreating them:
1. **Snapshot:** `OnVisible` loads the existing DB weeks into `colDbWeeks` and copies them to `colFinalWeeks` with a `SortKey`. Week numbers lower than the reception week get `+100`, so deliverables crossing New Year are listed in order.
2. **Recompute on date change:** only in edit mode and only for a valid range (realized ≥ reception). Weeks are generated Monday by Monday from the reception week to the realized week with `ISOWeekNum`. Saved `ID`s are merged from `colDbWeeks`, and the hours come from what is currently on screen (`colWeeksBeforeDateChange`), so unsaved edits survive a date change.
3. **Inline editing:** each week's number input patches the local collection only (`Patch(colFinalWeeks, ThisItem, …)`), with no server round-trip per keystroke. Cancel resets the form and reloads the saved weeks.
4. **Ordered commit:** more than 52 weeks is rejected, then the header is saved with `SubmitForm(UpdateForm)` while `locIsSaving` disables the button. Only in `OnSuccess`, using the server-confirmed `UpdateForm.LastSubmit.Title`, does the app:
   - **Update** existing weeks in one bulk `Patch(week_lookup_new, <base records from colDbWeeks>, <changes>)`.
   - **Insert** new weeks in one bulk `Collect`.
   - **Delete orphans:** `Remove(week_lookup_new, Filter(colDbWeeks, !(WeekNumber in colFinalWeeks.WeekNumber)))`. The orphans are computed from the local snapshot, so the delete is not limited by the delegation row cap.
   - Each step is wrapped in `IfError` and reports "The deliverable was saved but some weeks could not be saved". `OnFailure` short-circuits, so child rows are never written against a header that failed to save.

### 3.4 Cascading hierarchical pickers and deterministic ID generation
**Where:** [AddDelivrableDataPage.fx.yaml](./ALTENOVA_v2_1_src/Src/AddDelivrableDataPage.fx.yaml), [AddDelivrablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/AddDelivrablePage.fx.yaml)

- **Four-level cascade:** Team → Global Scope → Scope → Deliverable ID. Each level is `Distinct(Filter(scope_lookup_new | deliverable_lookup_new, parent = upstream.Selected.Value), column)`, so it is impossible to pick an invalid combination.
- **Role-aware roots:** CP sees every team. A TL gets a team selector seeded from `FirstN(colUserTeam, 1)`. Other roles are constrained to `gblUserGlobalScopes`.
- **Business-key generator** for new catalog entries:
  ```
  scopeAbbrev & (Coalesce(Max(ForAll(Filter(catalog, StartsWith(Title, scopeAbbrev)),
                 numeric suffix after the prefix, else 0)), 0) + 1)
  ```
  It takes the 3-letter upper-cased scope prefix, with a domain override (`OMOL` → `CRO`), and appends the next sequential number. The maximum is taken over **every ID with the same prefix in the whole list**, not just the same Team/Global Scope/Scope, so the new ID is unique list-wide. It is re-checked against the list at submit time to catch IDs taken by another user in the meantime. Catalog entries are opened by SharePoint `ID`, because older IDs such as `CRO5` can repeat across teams.
- **Collision-resistant transaction IDs:** `"DLV-" & Text(Now(), "yyMMddHHmmss") & "-" & Left(GUID(), 8)`.

### 3.5 Declarative validation framework
**Where:** all form screens. There are 33 distinct `locValidation*` flags.

- Each rule is a named boolean in screen context. On submit, all rules are evaluated at once in one `UpdateContext`, and the submit is gated by `And(...)` of all of them.
- **Cross-field temporal constraints:**
  - reception date ≤ `Today()`
  - expected delivery ≥ reception
  - realized delivery ≥ reception
  - workload ≥ 1 hour
- **Per-field error UX:** each field's `ErrorMessage*` label shows a specific message (e.g. *"Please select a date that is on or after the reception date"*) bound to `ValidationState`. Reset buttons put every flag back to `true` to clear the errors.
- **Server error surfacing:** on the user form, `OnFailure` shows `Coalesce(First(Errors(user_lookup_new)).Message, userForm.Error, "The user could not be saved.")`, and `userForm.Error` is also shown inline below the save button. The catalog forms report `Form.Error` in `OnFailure`, and success messages are only shown from `OnSuccess`.

### 3.6 Enterprise integrations: Graph people-picker, Power Automate, and Power BI
**Where:** [UsersManagement.fx.yaml](./ALTENOVA_v2_1_src/Src/UsersManagement.fx.yaml), [ShowDeliverablesPage.fx.yaml](./ALTENOVA_v2_1_src/Src/ShowDeliverablesPage.fx.yaml), [PowerBiRepport.fx.yaml](./ALTENOVA_v2_1_src/Src/PowerBiRepport.fx.yaml)

- **Directory search:** the people picker calls `Office365Users.SearchUserV2({searchTerm, top: 10, isSearchTermRequired: true})` and projects the results to `{DisplayName, Mail: Coalesce(Mail, UserPrincipalName)}`. When a new user is created, it **serializes the selection into a SharePoint `SPListExpandedUser` entity** (`'@odata.type'`, `Claims`, `Email`, …) so the Person column can be written directly. Existing users keep their identity unchanged.
- **Privileged deletes go through Power Automate:** the app does not delete rows directly. It calls flows with a response contract and branches on `response = "200"` for success or error toasts. Each delete needs a second click to confirm, and flow failures are reported with the response value. This keeps destructive operations (and any cascade logic) server-side and auditable.
- **Embedded analytics:** a Power BI tile bound to workspace `6ecc219b-…` gives TL/CP an in-app reporting view. This is the same workspace the `KPI_Deliverables_Sharepoint` report is published to (see §3.8–3.11).
- **Status visualization:** a `Switch` maps the **7 deliverable states** (Not Started, Waiting for client input, Work in Progress, Delivered, Accepted, Rejected, Submission Error) to a color palette. A further rule allows edit and delete only in `Work in Progress` / `Submission Error`, which acts as a **lightweight state machine**.

### 3.7 Performance considerations
- **Delegation-aware queries:** the gallery search uses `StartsWith` (the code is commented `// delegable`) inside a `With({BaseData: …})` scope, so filtering runs on SharePoint rather than being capped by the client row limit. Where the filter is a set membership that SharePoint cannot delegate (a TL's team members, a user's global scopes), the app runs one delegable query per value and merges the results with `Ungroup(ForAll(...))`.
- **Bulk writes:** week rows are created with one `Collect` and updated with one `Patch(table, baseRecords, changes)` instead of a `Patch` per row inside `ForAll`.
- **Half-open month windows:** `[Date(y, m, 1), DateAdd(…, 1, Months))` for month filtering. This is correct across 28/30/31-day months.
- **Parallel preloading:** `Concurrent(...)` on the Welcome screen loads deliverable-status choices and the role-specific list of responsible users at the same time.
- **Session-level caching:** user profile, teams, and scopes are resolved once in `OnStart` and reused from collections.
- **Runtime flags:** `delayloadscreens`, `formuladataprefetch`, `keeprecentscreensloaded`, `usenonblockingonstartrule`.

---

### Power BI Analytics Layer: `KPI_Deliverables_Sharepoint.pbix`

The app is the **system of record**. The Power BI report is the **system of insight**: it reads the same SharePoint lists the app writes to, joins them with staffing data from outside the app, and turns the result into team, consultant, and portfolio KPIs.

```
 ┌──────────── Power Apps (ALTENOVA) ────────────┐        ┌──────── Excel on SharePoint ────────┐
 │ writes deliverable_log / week / user / scope  │        │ Planning de production (MA + IN)    │
 └──────────────────────┬────────────────────────┘        │ LIvrable_Coff.xlsx                  │
                        ▼                                 └──────────────────┬──────────────────┘
        SharePoint Online lists (LYS_ReportingLivrable)                      │
                        │  SharePoint.Tables / OData.Feed                    │ SharePoint.Files / Web.Contents
                        ▼                                                    ▼
                 ┌────────────── Power Query (M) ETL ─────────────────────────────┐
                 │ expand Person columns · filter archived · unpivot 12 months    │
                 │ Table.Combine(Maroc, India) · clean/trim/lowercase keys        │
                 └──────────────────────────┬─────────────────────────────────────┘
                                            ▼
                     Tabular star schema (VertiPaq) + 23 DAX measures
                                            ▼
          8-page report  ──▶ embedded back into ALTENOVA (TL/CP)  ◀── Power Apps visual (validation)
```

### 3.8 Star-schema semantic model over operational SharePoint data
**Where:** `KPI_Deliverables_Sharepoint.pbix` → DataModel (tables, relationships)

- **Fact tables:**
  - `DeliverableLog`: 4,006 rows, one per logged deliverable
  - `WeekLookup`: 4,628 rows, per-week workload
  - `Full Presence Lookup`: 1,121 rows, consultant × month presence days
- **Dimensions:**
  - `UserLookup` (81 rows)
  - `DeliverableLookup` (95-entry catalog)
  - `ScopeLookup` (14 rows)
  - `Date`: a calculated table with **274 days**
- **Relationships** (5, all many-to-one):

  | From | To | Purpose |
  |---|---|---|
  | `DeliverableLog[Responsable_Email]` | `UserLookup[email]` | person, joined on the Entra ID email expanded from the SharePoint Person column |
  | `DeliverableLog[deliverable_data_id]` | `DeliverableLookup[ID]` | catalog, using the foreign key ALTENOVA writes during the wizard |
  | `DeliverableLog[Request Received Date]` | `Date[Date]` | time |
  | `WeekLookup[Title]` | `DeliverableLog[Title]` | **bidirectional**, so team and consultant slicers also filter weekly hours |
  | `Full Presence Lookup[Consultant_id]` | `UserLookup[Titre]` | presence, joined on the employee ID (*matricule*) |

- **Dedicated `Measure` table:** an empty `Table.FromRows` query that only holds measures. This is a modeling best practice that keeps measures separate from data.
- **Custom calendar in DAX:** `CALENDAR` is bounded by the min/max `Request Received Date`. `ADDCOLUMNS` adds Year, Quarter, Month Name/Number, `Year-Month` (sortable), Day of Week, **ISO week (`WEEKNUM(..., 21)`)**, and Start/End of Week. The auto date/time table is not used.

### 3.9 DAX KPI engine: 23 measures across delivery quality, capacity and productivity
**Where:** `Measure` table in the semantic model

| Family | Measures | Notable DAX technique |
|---|---|---|
| Volume | `Total Deliverables`, `Total Quantity`, `Avg Quantity per Deliverable`, `Deliverables WIP`, `Deliverables Accepted`, `Consultant Count`, `Deliverables per Consultant` | `COALESCE(DISTINCTCOUNT(...), 0)` so empty states show 0 instead of blank; `DIVIDE` for safe division |
| Quality | `Total Deliverables OTD`, `% OTD`, `Total Quantity Otd`, `Total Deliverables RFT`, `% RFT`, `Total Quantity RFT`, `Late Deliveries`, `Completion Rate` | **Quantity-weighted KPIs** via `SUMX(row, OTD × Quantity)`; RFT normalized across mixed encodings with `SWITCH(TRUE(), "1", "Accepted", UPPER(...))` |
| Capacity | `Days Allocated`, `Total Work Hours`, `Days Present`, `Days Variance` | `SUMX` + `RELATED(DeliverableLookup[Counting Unit])` converts quantity × unit into days; **`TREATAS`** creates a virtual relationship from `Date[Month Number]` to presence data |
| Efficiency | `Efficiency`, `Utilization Rate`, `Productivity Index` | `Efficiency = (Allocated − Present) / Allocated` with `VAR`s and blank-guarding |
| Trend | `Deliverables MoM Growth` | Time intelligence with `PREVIOUSMONTH('Date'[Date])` |
| UX | `Consultant Image URL` | `SELECTEDVALUE` returns a Base64 `data:image/jpeg` URI built in Power Query, which shows a consultant photo only when exactly one consultant is selected |

Plus **3 calculated columns** (`Work Time` via `DATEDIFF`, `Validation Data` composite label, `Year-Month`) and a **visual calculation** (`[Deliverable Quantity] * [Counting Unit]`) on the QA page.

### 3.10 Multi-source Power Query ETL with unpivoting and data cleansing
**Where:** Power Query queries `DeliverableLog`, `UserLookup`, `WeekLookup`, `PresenceLookup Maroc`, `PresenceLookup India`, `Full Presence Lookup`, `DeliverableCoefficient`, `Errors in Full Presence Lookup`

- **SharePoint Person/multi-choice flattening:**
  - `Table.ExpandListColumn` + `Table.ExpandRecordColumn` turn `Responsable` into `Responsable_Name` / `Responsable_Email`.
  - `Text.Combine(List.Transform(...), ",")` collapses multi-value `Team` and `Global Scope`.
- **Business filtering at ingestion:**
  - Archived deliverables are excluded (`[IsArchive] = false`). This is a soft-delete pattern that matches the app.
  - CP (PMO) users are removed from the people dimension, so KPIs only count delivery staff.
- **REST fallback:** `WeekLookup` reads the list through `OData.Feed(".../_api/Web/Lists(guid'…')/items", [Implementation="2.0"])` and renames the internal `field_1/field_2` columns. This is a direct REST pull instead of the list connector.
- **Staffing-plan integration:**
  - Two independently maintained Excel workbooks (Morocco through `SharePoint.Files`, filtered by `.xlsx` and creation year; India through `Web.Contents`) are read from their *Month* sheets.
  - The steps: promote multi-row headers, keep `Tâche = "Présence équipe"`, apply `Text.Trim` / `Text.Clean` / `Text.Lower` to the join keys, and remove blank rows.
  - **`Table.Unpivot` turns the 12 month columns into `(Consultant_id, MonthNumber, Value)` rows.** `Table.Combine` then merges both sites into one fact table.
- **Data-quality diagnostics:** a generated `Errors in Full Presence Lookup` query detects type mismatches row by row, so bad cells in the source spreadsheets can be traced.

### 3.11 Closed-loop analytics: Power BI ⇄ Power Apps two-way embedding
**Where:** [PowerBiRepport.fx.yaml](./ALTENOVA_v2_1_src/Src/PowerBiRepport.fx.yaml) (`PowerBI1`, `WorkspaceId`) and the report page *Monthly Deliverable Validation* (`PowerApps_PBI_CV_…` visual, `appId = …/apps/0fac6143-…`)

- **App → BI:** ALTENOVA embeds the Power BI report for TL/CP roles, so managers can go from data entry to dashboards without leaving the app.
- **BI → App:** the report embeds the **Deliverable Validation App** as a Power Apps visual. The visual receives the *currently filtered* rows (`ID`, `Responsable_Email`, `Deliverable ID`, `Deliverable Status`, `Deliverable Name`, `Deliverable Quantity`) from the report's slicers: status, date, team/scope/consultant, and consultant list. Managers filter in Power BI and then **approve or reject deliverables in place**. That status change flows back to SharePoint and appears in the RFT/acceptance KPIs after the next refresh.

---

## 4. Quantifiable Metrics & Scope (From the Codebase)

| Metric | Value | Source |
|---|---|---|
| Screens | **11** production screens (the original export also had 2 analytics/debug prototypes, now removed) | `Src/*.fx.yaml` |
| UI controls | **~480** control instances, **260** Fluent v9 modern controls (original: ~485 / 275) | count of `As <control>` declarations |
| Reusable components | **1** header component, reused on **10** screens | `Components/Component1.fx.yaml` |
| Forms / data cards / galleries | **5** forms, **51** typed data cards, **5** galleries | screen YAML |
| Power Fx source | **~11,300 lines** (original: ~10,700) | `Src/*.fx.yaml` |
| Data tables (SharePoint lists) | **5** lists, **~45** business columns | `pkgs/TableDefinitions/*.json` |
| Key relationships | `week_lookup_new.Title` → `deliverable_log_lookup.Title` (1:N); `deliverable_log_lookup.deliverable_data_id` → `deliverable_lookup_new.ID` (N:1); `scope_lookup_new` hierarchy Team → Global Scope → Scope; `Responsable` / `Email` → Entra ID Person | formulas in §3 |
| "Endpoints" / server operations | **2** Power Automate flows (HTTP request/response), **2** Office 365 Users operations (`UserProfileV2`, `SearchUserV2`), **4** `Patch`, **2** bulk `Collect`, **4** `SubmitForm`, **2** `Remove` + **1** `RemoveIf` (orphan cleanup / rollback), **10** `Refresh` | formula counts |
| Connectors | **6** connections (SharePoint, Office 365 Users, Office 365 Groups, Logic Flows ×2, Power Apps for Makers) | `Connections/Connections.json` |
| Roles | **3** (CP, TL, Consultant) | `App.fx.yaml` |
| Deliverable lifecycle states | **7** | `ShowDeliverablesPage.fx.yaml` |
| Validation rules | **33** named validation flags | `locValidation*` |
| State | **17** global variables, **10** named collections | `Set` / `ClearCollect` |
| Computed KPIs (app) | **3** previewed at entry: OTD %, RFT, Days Delayed | `AddDelivrablePage.fx.yaml` |
| Tunable constants | people-search `top: 10`; toast durations `5000`, `10000` ms; max **52** weeks per deliverable; week grid from the Monday of the reception week to the Monday of the realized week | formulas |
| Static analysis | **0** parser / **0** binding errors; App Checker (from the original export, not re-run on this build): **259** findings (219 accessibility-label, 13 tab-index, 8 delegation hints, 2 `ForAll`-with-mutation) | `CanvasManifest.json`, `AppCheckerResult.sarif` |
| Automated tests | Test Studio suite scaffolded: **1** suite / **1** case (no assertions yet) | `Src/Tests/` |

**Power BI analytics layer** (`KPI_Deliverables_Sharepoint.pbix`)

| Metric | Value | Source |
|---|---|---|
| Report pages | **8** published + 2 hidden (QA / scratch) | `Report/definition/pages` |
| Visuals | **140** visual objects; **~70** data visuals (cards, combo charts, matrices, treemap, pies, bars, slicers, Power Apps visual) | `visual.json` files |
| Page themes | OTD + RFT · OTD/RFT Analysis · Allocated Days KPI · Work Hours KPI · Deliverables KPI · Efficiency KPI · Deliverable Revision · Monthly Deliverable Validation | `pages.json` |
| Semantic model | **9** tables (7 data + `Measure` + `Date`), **5** relationships (1 bidirectional) | model DMVs |
| DAX | **23** measures, **3** calculated columns, **1** calculated table, **1** visual calculation | model DMVs |
| Power Query | **9** M queries; **4** source types (SharePoint lists, SharePoint REST/OData, Excel on SharePoint ×3, Web) | partitions / expressions |
| Data volume (snapshot) | **4,006** deliverables · **4,628** weekly workload rows · **1,121** presence rows · **95** catalog items · **81** users · **14** scopes · **274** calendar days (≈ **10.2k** rows) | `COUNTROWS` per table |
| Unpivot fan-out | 12 month columns → 12 rows per consultant per site | `Table.Unpivot` |
| RLS roles | **0** (see Known gaps) | `TMSCHEMA_ROLES` |

---

## 5. Ready-to-Use Resume Bullet Points (XYZ Format)

> Replace `[bracketed]` placeholders with real numbers from production (e.g. Power Platform analytics, Power BI usage, team feedback).

### Variation 1: Full-Stack / General Software Engineering
- **Independently built and deployed** ALTENOVA across **3 teams**, including both the Power Apps application and Power BI analytics. It serves **50+ daily users in the original team**, including managers, with **more than 80 users estimated overall**. It replaces fragmented annual Excel trackers with centralized deliverable tracking and email/in-app notifications for submissions, deadlines, status changes and rejections; the original percentage-reduction placeholder **[X%]** remains unmeasured.
- **Engineered** identity-driven role-based access control for 3 roles (Consultant / Team Lead / PMO), with **zero cross-team data exposure [verify]**. It resolves Entra ID profiles at startup and enforces navigation, field-level and row-level authorization in Power Fx.
- **Built** a two-step deliverable wizard that **eliminated orphaned records [and reduced data-entry errors by X%]**. It stages input in memory, validates 12 business rules, writes the header and then all weekly rows in one bulk call, and rolls the header back if the weekly rows fail.
- **Automated** delivery-quality analytics (On-Time Delivery, Right-First-Time, allocated days, efficiency) over **4,000+ deliverables and 4,600+ weekly workload entries**. I built a Power BI star-schema model with 23 DAX measures fed directly from the app's SharePoint lists, replacing **hours of manual transcription and formula-based KPI preparation each month**. Exact savings have not been measured.
- **Closed the loop between analytics and action** by embedding Power BI in the app and a Power Apps validation app inside the Power BI report. Managers approve deliverables straight from filtered dashboards, cutting validation cycle time by **[X%]**.
- **Standardized** a deliverable catalog of **95 entries in the documented snapshot** across a 3-level Team → Global Scope → Scope hierarchy. It uses cascading pickers and a regex-based sequential ID generator, so invalid combinations cannot be selected.

### Variation 2: Backend / Systems Architecture
- **Designed** a normalized 5-list SharePoint data model with 1:N (deliverable → weekly workload) and N:1 (deliverable → catalog) relationships, supporting **[N] records** with **[X ms] median query latency**. Filters are delegation-aware, so the work runs on the server instead of being capped by the client row limit.
- **Implemented** a reconciliation algorithm for weekly workload rows. It bulk-updates existing rows, bulk-inserts new weeks, and deletes orphaned weeks computed from a local snapshot. ISO weeks are generated Monday by Monday, so ranges crossing New Year stay correct, keeping **100% referential consistency [verify]** when delivery dates change.
- **Enforced** write-ordering guarantees: child rows are committed only in the parent form's `OnSuccess`, keyed on the server-confirmed `LastSubmit` record. On create, a failed child write triggers a compensating delete of the parent. This prevented **[N] partial-write incidents** without a native transaction engine.
- **Integrated** Microsoft Graph (Office 365 Users `UserProfileV2`, `SearchUserV2`) and serialized the results into SharePoint `SPListExpandedUser` claims entities. This enabled directory-backed user provisioning for **[N] users** without manual email entry.
- **Moved** destructive operations into Power Automate HTTP flows with a typed request/response contract (`response = "200"`). This centralized delete logic and gave an auditable server-side path for **[N] deletions/month**.

### Variation 3: Frontend / Product Engineering
- **Delivered** a responsive, Teams-embedded UI of **~480 controls (260 Fluent UI v9)** that works down to 320 × 480, using auto-layout containers and a reusable header component shared across 10 screens, used daily by **50+ original-team users** and deployed across **3 teams**; adoption percentage **[X%]** and rollout time **[N weeks]** remain unmeasured.
- **Built** a 33-rule validation system with cross-field date constraints and inline per-field error messages, reducing invalid submissions by **[X%]**.
- **Optimized** perceived performance by preloading reference data in parallel with `Concurrent()`, caching session context in collections, and enabling delayed screen loading. This brought time-to-interactive down to **[X s]**.
- **Prevented** duplicate submissions with disabled-state and progress-label submit patterns, two-click delete confirmation, and color-coded status chips for 7 lifecycle states, improving **[task-completion / CSAT metric by X]**.
- **Designed** role-adaptive screens: Consultant, Team Lead, and PMO users see different navigation, editable fields, and data slices from a single codebase, avoiding **[N] separate app variants** (the environment also contains per-unit forks, ALTENOVA - OS and ALTENOVA - SCEE).

### Variation 4: Data / BI Engineering (Power BI)
- **Built** an 8-page Power BI KPI suite (about 70 data visuals) on a star-schema semantic model of **~10k rows across 9 tables**. It gives Team Leads and the PMO self-service OTD, RFT, capacity, and efficiency tracking, used by managers within the **50+ daily users in the original team**, with **hourly Power BI refresh**. It replaces hours of monthly manual KPI preparation; the number of reports replaced remains **[N] manual Excel reports/month** (unmeasured).
- **Engineered** 23 DAX measures, including quantity-weighted OTD/RFT (`SUMX`), day allocation via `RELATED` catalog coefficients, month-over-month growth with `PREVIOUSMONTH`, and an Efficiency KPI joined to staffing data through a `TREATAS` virtual relationship. This standardized KPI definitions across **3 adopting teams**.
- **Developed** Power Query ETL that unifies 4 source types: SharePoint lists, the SharePoint REST/OData API, and staffing-plan Excel workbooks from 2 sites (Morocco and India). It unpivots 12 monthly columns into a normalized presence fact table, so allocated-vs-present efficiency is visible for the first time **[for N consultants]**.
- **Improved** data quality by cleaning join keys (`Trim` / `Clean` / `Lower`), filtering archived records and PMO accounts at ingestion, and adding a row-level type-mismatch diagnostics query, reducing KPI discrepancies by **[X%]**.
- **Integrated** a Power Apps visual into the report that passes the slicer-filtered deliverables into a validation app. This turned a read-only dashboard into an approve/reject workflow and cut validation turnaround from **[X days] to [Y days]**.

---

## 6. Interview Prep: Deep-Dive Talking Points

### Problem 1: Keeping weekly workload rows consistent when delivery dates change
**Files:** [ModifyDeliverablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/ModifyDeliverablePage.fx.yaml) (`OnVisible`, date-picker `OnChange`, `UpdateForm.OnSuccess`)

- **Situation:** Each deliverable owns N weekly workload rows in a separate list. When a Team Lead moved a delivery date, the set of weeks had to grow or shrink. SharePoint has no transactions or cascading foreign keys.
- **Task:** Let users edit dates and hours freely, keep the hours they had already entered for weeks that still apply, and never leave stale or duplicate weeks behind.
- **Action:**
  - Snapshot the DB rows into `colDbWeeks`, with a New-Year-aware sort key. Recompute the week list Monday by Monday with `ISOWeekNum`, re-attaching saved `ID`s and keeping the hours currently on screen.
  - Edits go to a local collection only. Cancel restores the saved state.
  - On save, reject more than 52 weeks and commit the header first. In `OnSuccess`, using the confirmed key, bulk-update existing weeks with one `Patch(table, baseRecords, changes)`, bulk-insert new weeks with one `Collect`, and `Remove` the weeks that are no longer in range, computed from the local snapshot.
- **Result:** An idempotent diff-and-apply sync with no orphaned rows and no lost hours. Child writes never happen against a parent that failed to save. *Trade-offs to discuss:* the original version did one `Patch` per row inside `ForAll` (App Checker flagged `ForAllWithMutation`) and computed week counts with `RoundUp(days/7)+1`, which could add an extra week (e.g. Monday to the following Sunday gave 2 weeks). Batching fixed the first; Monday-to-Monday generation fixed the second. If a week write fails after the header saved, the user is told, but the header is not rolled back on edit. A Power Automate flow could make the whole update atomic.

### Problem 2: Multi-step create without orphan records
**Files:** [AddDelivrablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/AddDelivrablePage.fx.yaml) (`GoToNextFromBtn.OnSelect`), [NextPageAddDelivrablePage.fx.yaml](./ALTENOVA_v2_1_src/Src/NextPageAddDelivrablePage.fx.yaml) (`SubmitBtn_1.OnSelect`)

- **Situation:** Creating a deliverable needs header data and a per-week hours breakdown, which is too much for one screen. A naive design would save the header on page 1, leaving orphans whenever users abandoned page 2.
- **Task:** Split the UX into two steps while keeping the write atomic from the user's point of view.
- **Action:**
  - Page 1 validates 12 rules and checks that the chosen catalog entry belongs to the user's team. It then stages everything in one record variable (`DonneesLivrableTemp`), including derived KPIs (OTD, RFT, delay) and a role-aware foreign-key lookup to the catalog. It also generates the week grid date by date (ISO weeks, max 52) and keeps hours already typed if the user comes back.
  - Page 2 validates the hours (0 allowed; blank and negative rejected). Inside `IfError` it Patches the header, captures the returned record, and creates all children in one bulk `Collect` against `NewDeliverableRecord.Title`. If that fails, it removes the children and the header again.
  - A `locIsSubmitting` flag disables the button during the write.
- **Result:** No database writes until the user confirms, no double submits, no header left without its weeks, and readable error messages from `FirstError.Message`. *Trade-off to discuss:* the rollback runs on the client, so a lost connection mid-write can still leave a partial record. A server-side flow that writes both atomically would close that gap.

### Problem 3: Role-based access control on a low-code stack
**Files:** [App.fx.yaml](./ALTENOVA_v2_1_src/Src/App.fx.yaml), [WelcomePage.fx.yaml](./ALTENOVA_v2_1_src/Src/WelcomePage.fx.yaml), [ShowDeliverablesPage.fx.yaml](./ALTENOVA_v2_1_src/Src/ShowDeliverablesPage.fx.yaml), [UsersManagement.fx.yaml](./ALTENOVA_v2_1_src/Src/UsersManagement.fx.yaml)

- **Situation:** Three user types share one app. Consultants must only see and own their own deliverables, Team Leads manage their teams (users can belong to several teams), and the PMO sees everything.
- **Task:** Implement authorization without a custom backend, using Entra ID identity and a SharePoint user registry.
- **Action:**
  - At startup, resolve the canonical mailbox via Graph (`UserProfileV2`, falling back to the UPN), join it to `user_lookup_new`, and cache role, global scopes, and teams.
  - Layer the enforcement:
    - **Feature gating:** `Visible` on admin and BI entry points and on the content of those screens.
    - **Field-level locks:** `DisplayMode` switches on `gbRole`. Consultants are pinned to "Work in Progress" status and to themselves as Responsable, using a claims-formatted identity.
    - **Row-level filters:** owner email for Consultants, multi-team set intersection for TLs.
  - Add `isArchive` and "not registered" gates at the Welcome screen, and route deletes through Power Automate.
- **Result:** One codebase serves all roles. *Trade-off to discuss honestly:* Power Fx checks run on the client. The real security boundary must be **SharePoint item/list permissions** (or a move to Dataverse row-level security with business units and teams). The original export also had a hard-coded per-user role override in `App.OnStart`; it has been removed, so roles now come only from data.

### Problem 4: Measuring efficiency by joining delivery data with an external staffing plan
**Files:** `KPI_Deliverables_Sharepoint.pbix`: Power Query `PresenceLookup Maroc`, `PresenceLookup India`, `Full Presence Lookup`; DAX `[Days Allocated]`, `[Days Present]`, `[Efficiency]`; page *Efficiency KPI*

- **Situation:** Management wanted to know whether the effort logged on deliverables matched how many days each consultant was actually present. Presence data lived in **separately maintained Excel staffing plans per site**:
  - wide format, with one column per month
  - several header rows
  - inconsistent naming and casing

  None of that data was linked to the deliverable list.
- **Task:** Produce a trustworthy monthly **Efficiency** KPI per consultant and team, without asking the sites to change their spreadsheets.
- **Action:**
  - **Power Query:** for each site, read the *Month* sheet, promote the stacked headers, keep only `Présence équipe` rows, trim/clean/lowercase the keys, and **unpivot 12 month columns into `(Consultant_id, MonthNumber, Value)`**. `Table.Combine` merges the sites, and a type-mismatch diagnostics query catches bad cells.
  - **Model:** relate presence to `UserLookup` on employee ID (`Titre` = *matricule*).
  - **DAX:**
    - `[Days Allocated]` = `SUMX(DeliverableLog, Quantity × RELATED(DeliverableLookup[Counting Unit]))`
    - `[Days Present]` uses **`TREATAS(VALUES('Date'[Month Number]), 'Full Presence Lookup'[MonthNumber])`**. This is a virtual relationship that avoids a second physical date relationship and the ambiguous filter paths that would come with it.
    - `[Efficiency]` = `DIVIDE(Allocated − Present, Allocated)`, with blank guards.
- **Result:** A combo chart and a Team → Consultant → Month matrix comparing allocated and present days. This was the first view that tied delivery output to real capacity across both sites **[used for N staffing decisions]**. *Trade-offs to discuss:*
  - `TREATAS` on month number alone ignores the year. It is fine for single-year plans but needs a `(Year, Month)` key across years.
  - The Morocco query selects a workbook by a fixed 2026 file name and year filter. Moving to a parameterized path, or a SharePoint list for presence, would remove the yearly maintenance.

### Known gaps to mention proactively (they show you understand the system)
- **Week-number consistency (fixed):** create and edit now both generate ISO weeks Monday by Monday from the dates (`ISOWeekNum`). The original create path used `WeekNum` with a single-year `Sequence(EndWeek − StartWeek + 1)`, which broke for deliverables spanning New Year. Remaining limitation: `week_lookup_new` stores only the week number, not the year, so deliverables are capped at 52 weeks.
- **Accessibility debt:** the bundled App Checker report (219 of 259 findings are missing `AccessibleLabel`s) dates from the original export. The current build adds accessible labels to the key action buttons, but App Checker has not been re-run.
- **Testing:** Test Studio is scaffolded but has no assertions. The next step is Power Apps Test Engine (YAML test plans) in a pipeline that runs `pac` (Power Platform Build Tools).
- **ALM:** the source is now unpacked with `pac canvas unpack`. Moving the app and flows into a Dataverse **solution** with environment variables would allow Dev → Test → Prod deployment and replace the per-unit app forks (OS, SCEE) with configuration.
- **Power BI: no Row-Level Security.** The model has **0 roles**, so the app's Consultant/TL/CP scoping is **not** enforced in the report. Adding dynamic RLS (`UserLookup[email] = USERPRINCIPALNAME()` plus a team-based rule) would bring the two layers in line.
- **Power BI: percentage formatting.** `Completion Rate`, `Utilization Rate`, and `Deliverables MoM Growth` multiply by 100 **and** use a `0.00%` format string, so they would display 100× too high (e.g. 5000%). Drop the `* 100` or switch to a plain number format. `% OTD` / `% RFT` already use `0.00` and are fine.
- **Power BI: KPI encoding drift.** RFT is matched on both `"1"` and `"Accepted"`, and OTD is cast with `VALUE()`. Storing OTD/RFT as typed 0/1 numbers at the source, ideally written by ALTENOVA itself, would simplify the DAX and remove string matching.
- **Power BI: hard-coded exclusions.** The presence queries filter out specific people by name in M. Moving this into an `isArchive` / exclusion flag in `UserLookup` would make it data-driven.
- **Power BI: ISO weeks.** The model's `Date` table uses ISO weeks (`WEEKNUM(…, 21)`), which now matches both the app's create and edit paths (`ISOWeekNum`).