# Employee Sync Function — Windows setup guide This assumes you're starting with **only VS Code installed** on Windows 11, admin rights, and nothing else — no PowerShell 7, no Azure CLI, no Functions tooling. It walks through installing everything needed, then getting this code into your existing (empty) Flex Consumption Function App, wiring up the trigger, identity, secrets, and the two Table Storage tables, and testing the whole path before pointing the real ETL step at it. **Assumed starting point:** a resource group already containing a Storage Account, Key Vault (`kv-cia-employee-sync`), an empty Function App (Flex Consumption, Linux, PowerShell runtime), Application Insights, a Smart Detector alert rule, and the App Service Plan. The `EmployeeSyncFunction.zip` deployment package should also be available on your machine. --- ## 0. Install the tools you'll need (one-time) **0.1 — Install PowerShell 7, and the Functions tooling.** Open a terminal — Windows Terminal from the Start menu is fine, or use VS Code's own terminal (View → Terminal) — and run: ```powershell winget install --id Microsoft.PowerShell --source winget winget install --id Microsoft.Azure.FunctionsCoreTools ``` If a User Account Control prompt appears asking to allow changes, click Yes — you have the rights to approve it. If the second command can't find that exact package, run `winget search "functions core tools"` first and use the ID it shows you instead. **0.2 — Fully close VS Code and any open terminals, then reopen VS Code.** This matters — PATH changes from step 0.1 won't be picked up by anything already running, including a VS Code window that was open during the install. **0.3 — Verify both installed correctly.** Open VS Code's terminal (View → Terminal), and in the dropdown next to the `+` in the terminal panel, choose **"PowerShell"** — not "Windows PowerShell," which is the older 5.1 version Windows ships with by default and which this whole project intentionally avoids. Then run: ```powershell $PSVersionTable.PSVersion # should show 7.4.x or similar — if it shows 5.1.x, you're in the wrong profile func --version # should show 4.x ``` **0.4 — Set PowerShell 7 as VS Code's default terminal**, so you don't have to pick the profile manually every time: Command Palette (`Ctrl+Shift+P`) → "Terminal: Select Default Profile" → choose **"PowerShell"** (again, not "Windows PowerShell"). **0.5 — Install two VS Code extensions.** Open the Extensions view (`Ctrl+Shift+X`), search for and install: - **Azure Functions** (publisher: Microsoft) — this is what deploys code to Azure from inside VS Code, and pulls in the Azure Resources/Account extensions it depends on - **PowerShell** (publisher: Microsoft) — syntax highlighting and IntelliSense for `run.ps1` **0.6 — Sign in to Azure inside VS Code.** Click the Azure icon that's now in the left-hand Activity Bar. You should see a "Sign in to Azure..." link — click it, and complete sign-in in the browser window that opens, using the account that has access to the resource group. Once signed in, expanding your subscription in that panel should show your existing resources, including the empty Function App. ## 1. Confirm the runtime version of the Function App In a browser, go to [portal.azure.com](https://portal.azure.com) and sign in. Use the search bar at the top to find your resource group, click into it, then click the Function App resource. On its **Overview** page (or **Settings → Environment variables** in the left-hand menu), confirm the runtime version. Flex Consumption's generally-available PowerShell version is **7.4** — this code has nothing 7.6-specific in it, but worth confirming there are no surprises. ## 2. Get the code onto your machine and open it in VS Code Extract `EmployeeSyncFunction.zip` to a folder — e.g. `C:\Projects\EmployeeSyncFunction`. In VS Code: **File → Open Folder...** and select that folder. VS Code may show a notification asking if you trust the authors of this folder, or offering to "initialize for VS Code" — both are safe to accept; it just adds some editor settings, it doesn't change your code. ## 3. Deploy the code to the existing Function App Command Palette (`Ctrl+Shift+P`) → type and select **"Azure Functions: Deploy to Function App..."**. You'll be prompted to pick your subscription, then pick the target Function App from a list — select your existing (currently empty) one. It'll warn that this overwrites any existing content in the app and ask you to confirm — since it's empty, there's nothing to lose. Wait for the "Deployment completed" notification in the bottom right. *Optional fallback, only if that fails:* Azure CLI can do the same thing from a terminal, though it needs installing first (`winget install --id Microsoft.AzureCLI`, then `az login`). Microsoft's own docs are inconsistent about which command Flex Consumption expects, so try the first and fall back to the second if it errors: ```powershell Compress-Archive -Path * -DestinationPath ../EmployeeSyncFunction.zip -Force az functionapp deploy --resource-group --name ` --src-path ../EmployeeSyncFunction.zip --type zip # if that errors: az functionapp deployment source config-zip --resource-group ` --name --src ../EmployeeSyncFunction.zip ``` ## 4. Confirm the trigger registered In the VS Code Azure panel, expand your Function App node — it should now list `HttpTrigger` underneath it. Or check the **Functions** blade on the Function App in the portal. Either way, a quick reachability check from a PowerShell 7 terminal (expect a **401**, not a 404 — that confirms routing works even before auth is fully configured): ```powershell Invoke-RestMethod -Method Post -Uri "https://.azurewebsites.net/api/employees/sync" ``` ## 5. Enable a system-assigned managed identity In the portal, on the Function App: left-hand menu → **Identity** → System assigned tab → switch Status to **On** → **Save**. This identity is what lets the function read the Key Vault secret and read/write Table Storage — no keys or connection strings anywhere in this design. ## 6. Grant that identity two roles First check `kv-cia-employee-sync` → **Access configuration** (left-hand menu) to see whether it's set to **Azure RBAC** or **Vault access policy** — the two grant permissions differently. | Resource | If RBAC | If Access Policy | |---|---|---| | `kv-cia-employee-sync` | Access control (IAM) → Add role assignment → **Key Vault Secrets User** → assign to the Function App's identity | Access policies → Add access policy → Secret permissions: **Get** → the Function App's identity | | Storage Account | Access control (IAM) → Add role assignment → **Storage Table Data Contributor** → assign to the Function App's identity | *(Storage accounts only use RBAC)* | For the "assign to the Function App's identity" part of a role assignment: on the Members step of the wizard, choose "Managed identity" → "+ Select members" → Function App → pick yours from the list. ## 7. Add the app settings Function App → **Settings → Environment variables** (left-hand menu) → **App settings** tab → **+ Add**, one at a time, then **Save** at the top once all five are added (this restarts the app — expected): | Name | Value | |---|---| | `CiaEtlSecret` | `@Microsoft.KeyVault(VaultName=kv-cia-employee-sync;SecretName=ciaETLsecret)` | | `TableStorageAccountName` | your storage account's name | | `EmployeeTableName` | `Employees` | | `PayLocationMappingTableName` | `PayLocationMappings` (optional — this is the default if unset) | *Copy-paste trap:* the backticks around each value above are markdown code-formatting, not part of the actual value — copy only the text between them (starting with `@` for the Key Vault reference), not the backtick characters themselves. A stray leading/trailing backtick in the pasted value is a genuine cause of Key Vault references silently resolving to the wrong (or garbage) string. *`EmployeeSyncThrottleLimit` is no longer used* and can be deleted if you added it earlier. Writes now go out as batched entity group transactions rather than one call per employee, so there's no parallelism left to throttle — see "How large runs are handled" at the bottom. After saving, click into `CiaEtlSecret`'s row — the detail panel should show its resolution status. Where exactly this appears has moved around across recent portal updates, so rather than relying on finding the right icon, the definitive test is calling the function itself (this reuses the request from step 9, just earlier): ```powershell Invoke-RestMethod -Method Post -Uri "https://.azurewebsites.net/api/employees/sync" ` -Headers @{ Authorization = 'Bearer anything' } -ContentType 'application/xml' -Body '' ``` The specific error tells you exactly what's wrong: - **500 "Server misconfigured"** → `$env:CiaEtlSecret` is empty. The reference isn't resolving at all. Most likely causes, roughly in order of likelihood: the RBAC role from step 6 hasn't propagated yet (can take a few minutes — just wait and retry); a typo in the vault or secret name in the reference string; or, if `kv-cia-employee-sync` has network restrictions configured (Networking blade shows anything other than "Allow public access from all networks"), Key Vault reference resolution can be blocked by that firewall unless trusted Microsoft services are allowed through — worth checking if the vault was locked down for other reasons. - **401 "Invalid token"** → the reference *did* resolve to something, just not a match for `Bearer anything` (expected, since that's not the real secret) — meaning the Key Vault piece is actually fine. Retry with the real secret to confirm properly. - **400 "Body is not valid XML"** → also a good sign here, oddly — it means auth passed and it got as far as parsing ``, so both the token and the Key Vault resolution are working. (400 is expected for this deliberately minimal placeholder body — a real payload wouldn't hit this.) If you land on 500 and a few minutes' wait doesn't fix it, deleting and re-adding the `CiaEtlSecret` setting from scratch (rather than editing it in place) has resolved this for other people in similar situations — worth trying if the syntax and RBAC both check out. *Rotation note:* the platform caches this resolved value and refreshes it periodically (roughly daily, or on restart/scale) — not on every request. If you rotate the secret in Key Vault and need the Function to pick it up immediately, restart the Function App. ## 8. Create the tables No separate tool needed — the portal has a built-in table editor. On the Storage Account: left-hand menu → **Storage browser** → **Tables** → **+ Add table**. - **`Employees`** — create it, no need to pre-define columns; each upsert just writes whatever properties are present on the primary-position record for that employee. There's exactly **one row per employee**, not one per position: `PartitionKey` = the GUID resolved from that employee's primary-position `paylocation` (via `PayLocationMappings`, below), `RowKey` = `employeeid`. An employee's secondary positions don't currently affect the row at all — see the note below. Every write also adds/preserves `SyncCreatedUtc` (set once, on first insert, and carried forward unchanged on every later update). There's no separate "last updated" field to maintain — Table Storage automatically stamps every entity with a built-in `Timestamp` property on every write, so that's already available for free. Query it like any other property, e.g. to find everything touched since a given point: `$filter=Timestamp gt datetime'2026-07-29T00:00:00Z'`. - **`PayLocationMappings`** — create it, then add rows via the Storage browser's row editor, one per **full** pay-location string (exact match, not a prefix): | PartitionKey | RowKey | MappedGuid | |---|---|---| | `Mapping` | `CCM Central Office` | `` | | `Mapping` | `Brindabella Christian College` | `` | `PartitionKey` is always the literal string `Mapping` (keeps the function's lookup a cheap single-partition query). Any employee whose primary-position `paylocation` doesn't match a row here gets skipped entirely (logged as a warning) since there'd be nothing valid to use as `PartitionKey` on their `Employees` row. - **`OrganisationUnitMappings`** — **on hold, not currently read by the function.** The original plan was for every position (primary and secondary) to contribute its org unit's shortcode into an `organisationUnitCodes` array on the employee's row, feeding an Entra extension attribute for dynamic group membership. That's paused because the ETL side will only send *updated* records rather than an employee's full position set each run — so a delta batch touching just one position could silently blank out previously- known secondary org units for that employee if the array were rebuilt from the batch alone. Fixing that needs a policy for how position *removals* get represented (an end date on the position, vs. simply not being resent), which needs input from the data source administrator before this is safe to re-enable. The table structure, if/when it comes back, was: `PartitionKey` = `Mapping`, `RowKey` = the uppercase 3-character org-unit prefix, `OrgUnitCode` = the fixed shortname (e.g. `BRI` → `bricc`). Feel free to keep it populated in the meantime — nothing currently reads it, but nothing breaks if it exists either. ## 9. Test end to end In a PowerShell 7 terminal: ```powershell $uri = 'https://.azurewebsites.net/api/employees/sync' $secret = 'the-actual-secret-value' # from Key Vault, local testing only $body = Get-Content ./sample-payload.xml -Raw # Expect 200 with a JSON summary: totalRecords / upserted / skipped / failed Invoke-RestMethod -Method Post -Uri $uri -Headers @{ Authorization = "Bearer $secret" } ` -ContentType 'application/xml' -Body $body # Expect a 401 — confirms bad tokens are actually rejected, not just accepted try { Invoke-RestMethod -Method Post -Uri $uri -Headers @{ Authorization = 'Bearer wrong-secret' } ` -ContentType 'application/xml' -Body $body } catch { $_.Exception.Response.StatusCode $_.ErrorDetails.Message } ``` Check the `Employees` table afterwards (Storage browser again) to confirm rows landed with the expected `PartitionKey`/`RowKey`/`organisationUnitId`. ## 10. Monitoring runs in Application Insights The function logs a single `SYNC_SUMMARY` line per invocation carrying the same counts it returns in the HTTP response, so past runs can be reviewed without having captured the response body at the time. Open the Application Insights resource → **Logs**, and run: **Every run's summary counts:** ```kusto traces | where timestamp > ago(7d) | where message startswith "SYNC_SUMMARY" | parse message with * "totalRecords=" totalRecords:int " employeesProcessed=" employeesProcessed:int " upserted=" upserted:int " skipped=" skipped:int " failed=" failed:int | project timestamp, totalRecords, employeesProcessed, upserted, skipped, failed, operation_Id | order by timestamp desc ``` **Why things failed, grouped by cause:** ```kusto traces | where timestamp > ago(7d) | where severityLevel >= 2 // 2 = Warning, 3 = Error | extend reason = case( message has "no 'Primary Position'", "No primary position in batch", message has "no pay location mapping", "Unmapped pay location", message has "missing paylocation", "Missing paylocation field", message has "missing employeeid", "Missing employeeid field", message has "Upsert failed", "Upsert error (write)", message has "Lookup failed", "Lookup error (read)", "Other") | summarize count() by reason | order by count_ desc ``` **Everything from one specific run** — take an `operation_Id` from the first query: ```kusto traces | where operation_Id == "" | project timestamp, message, severityLevel | order by timestamp asc ``` **Trend over time**, to spot a run that suddenly failed more than usual: ```kusto traces | where timestamp > ago(30d) | where message startswith "SYNC_SUMMARY" | parse message with * "totalRecords=" totalRecords:int " employeesProcessed=" employeesProcessed:int " upserted=" upserted:int " skipped=" skipped:int " failed=" failed:int | summarize Upserted=sum(upserted), Skipped=sum(skipped), Failed=sum(failed) by bin(timestamp, 1d) | render timechart ``` *Note on sampling:* `host.json` excludes `Request`, `Exception`, and `Trace` from adaptive sampling, so summary lines and warnings are never silently dropped — without that, a large batch generating lots of log entries could have telemetry sampled away exactly when it's most worth reading. If log volume ever becomes a cost concern, drop `Trace` from `excludedTypes`, accepting that individual per-employee warnings may then be sampled. ## 11. Point the real ETL step at it - **Method:** POST - **URL:** `https://.azurewebsites.net/api/employees/sync` - **Headers:** `Authorization: Bearer `, `Content-Type: application/xml` (or `text/xml`) - **Body:** the XML array, unmodified No function key needed — the bearer token check is the actual gate. (Consider also setting `authLevel` + a function key as defense-in-depth if you want belt-and-suspenders, though it's not required for this to work.) --- ## How large runs are handled Azure enforces a hard **230-second ceiling** on HTTP-triggered function responses, regardless of plan or `functionTimeout`. An earlier version of this function did one GET plus one PUT per employee, run in parallel — roughly 3,600 outbound calls for an 1800-record run. That both exceeded the 230s limit and exhausted the instance's outbound SNAT ports, producing multi-minute stalls and "connection timed out" errors, with the ETL client timing out before any response came back. The write path now works in two phases: 1. **One query per partition, not per employee.** All existing rows for a pay-location partition are read in a single query (following continuation tokens if the partition holds more than 1,000 rows), giving the `whenmodified` and `SyncCreatedUtc` values needed to skip unchanged rows and preserve creation timestamps. 2. **Batched writes.** Entities are grouped by `PartitionKey` and written as Table Storage *entity group transactions* — up to 100 entities per HTTP call. A full 1800-record run across ~20 pay locations drops from ~3,600 calls to roughly 40, so it completes in seconds and no longer needs parallelism at all. Removing parallelism also removed the connection pressure that caused SNAT exhaustion. **One behavioural note worth knowing:** entity group transactions are *atomic* — if Table Storage rejects any single entity, the whole 100-entity transaction rolls back. To stop one malformed row from blocking 99 good ones, a failed batch automatically falls back to individual writes for that chunk, so only the genuinely bad row is reported as failed. Batches are also capped by Table Storage at 4MB per transaction. At ~20 fields per employee that's nowhere near binding, but very wide future records could hit it — the fallback path handles that safely if it ever happens. This code intentionally uses no PowerShell modules at all (`requirements.psd1` is just `@{}`). Where you might expect `Az.Accounts` (e.g. `Connect-AzAccount -Identity` / `Get-AzAccessToken`) to get a Table Storage token via managed identity, `run.ps1` instead calls the Functions platform's local token endpoint directly (`$env:IDENTITY_ENDPOINT` / `$env:IDENTITY_HEADER`, both set automatically once step 5 is done) via plain `Invoke-RestMethod` — same result, but nothing to vendor and nothing pinned to a module version. ## Why no Az module This code intentionally uses no PowerShell modules at all (`requirements.psd1` is just `@{}`). Where you might expect `Az.Accounts` (e.g. `Connect-AzAccount -Identity` / `Get-AzAccessToken`) to get a Table Storage token via managed identity, `run.ps1` instead calls the Functions platform's local token endpoint directly (`$env:IDENTITY_ENDPOINT` / `$env:IDENTITY_HEADER`, both set automatically once step 5 is done) via plain `Invoke-RestMethod` — same result, but nothing to vendor and nothing pinned to a module version.