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:

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:

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

Compress-Archive -Path * -DestinationPath ../EmployeeSyncFunction.zip -Force

az functionapp deploy --resource-group <rg-name> --name <your-function-app-name> `
    --src-path ../EmployeeSyncFunction.zip --type zip

# if that errors:
az functionapp deployment source config-zip --resource-group <rg-name> `
    --name <your-function-app-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):

Invoke-RestMethod -Method Post -Uri "https://<your-function-app-name>.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.

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

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

Invoke-RestMethod -Method Post -Uri "https://<your-function-app-name>.azurewebsites.net/api/employees/sync" `
    -Headers @{ Authorization = 'Bearer anything' } -ContentType 'application/xml' -Body '<x/>'

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 <x/>, 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	<guid>
  Mapping	Brindabella Christian College	<guid>

  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:

$uri    = 'https://<your-function-app-name>.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:

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:

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:

traces
| where operation_Id == "<paste-operation-id-here>"
| project timestamp, message, severityLevel
| order by timestamp asc

Trend over time, to spot a run that suddenly failed more than usual:

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://<your-function-app-name>.azurewebsites.net/api/employees/sync

• Headers: Authorization: Bearer <ciaETLsecret value>, 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.