# CiAnywhere → Azure Table Storage Employee Sync — Implementation Guide

## What this builds

CiAnywhere's ETL builds an XML document of employee rows and POSTs it, HMAC-signed, to an
Azure Function. The Function verifies the signature, stores the raw payload, and queues it for
processing. A second Function picks the message off the queue, parses the XML, and upserts each
row into Azure Table Storage — which then becomes a data source for your other automations.

```
CiAnywhere ETL
     │  HTTP POST (XML, HMAC-signed)
     ▼
[Receiver Function]  ──▶ Blob (raw payload, audit/replay trail)
     │  enqueue message
     ▼
[Storage Queue]
     │
     ▼
[Processor Function] ──▶ Azure Table Storage (upserted rows)
     │
     ▼
Application Insights / Log Analytics ──▶ Alerts (silence, write failures)
```

The implementation is divided into milestones, each ending with a **validate before continuing**
step. Two details need to be confirmed in the target environment: CiAnywhere's exact HMAC
signature format and Az PowerShell module behavior, which can vary across versions. The relevant
checks are noted in the steps below.

---

## Table Storage authentication options

An earlier design considered using Managed Identity for Table Storage row-level operations via an
`Az.Tables` module. **There is no official Microsoft PowerShell module for Table *entity*
operations.** `Az.Storage` manages tables themselves (create/list/delete), not rows. The
community module for row-level work (`AzTable`) depends on an older SDK that only supports
**Shared Key authorization**, rather than Managed Identity.

The implementation therefore uses:
- **Blob and Queue** access from the Functions: Managed Identity, via `Az.Storage`'s
  `-UseConnectedAccount` context (fully supported, no secrets needed).
- **Table** row operations: the `AzTable` module, authenticated with the storage account's
  **connection string, stored in Key Vault** (not a code secret you manage by hand — just not
  Managed Identity for this one piece).

The storage key remains in Key Vault. A later hardening option is to call the Table REST API
directly with a Managed-Identity-issued bearer token and build batch (`$batch`) requests. This is
listed as a Phase 2 option in Milestone 4, not part of the initial implementation.

---

## Prerequisites

- Az PowerShell modules installed locally (`Install-Module Az -Scope CurrentUser`), PowerShell 7.
- Azure Functions Core Tools, if you want to develop/test locally before publishing
  (`winget install Microsoft.Azure.FunctionsCoreTools`).
- Contributor access on the target subscription/resource group.
- Access to CiAnywhere's ETL Designer to configure the web call step.
- A resource-naming convention — the script below uses placeholders; **storage account, Key
  Vault, and Function App names must be globally unique**, so adjust them.

---

## Milestone 0 — Provision the core Azure resources

```powershell
# --- Adjust these to your naming convention; storage/KV/FunctionApp names must be globally unique ---
$resourceGroup   = "rg-cia-employee-sync"
$location        = "australiaeast"
$storageAccount  = "stciaemployeesync01"     # lowercase, no hyphens, globally unique
$keyVaultName    = "kv-cia-employee-sync"    # globally unique
$functionAppName = "func-cia-employee-sync"  # globally unique
$appInsightsName = "$functionAppName-ai"

New-AzResourceGroup -Name $resourceGroup -Location $location

New-AzStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount `
    -Location $location -SkuName Standard_LRS -Kind StorageV2 -MinimumTlsVersion TLS1_2

# RBAC-authorization Key Vault (not the legacy access-policy model)
New-AzKeyVault -ResourceGroupName $resourceGroup -VaultName $keyVaultName `
    -Location $location -EnableRbacAuthorization $true

# Function App — PowerShell worker, Consumption plan to start.
# Check the current supported PowerShell version for the Functions runtime when you run this —
# it changes over time; use the latest 7.x offered.
New-AzFunctionApp -ResourceGroupName $resourceGroup -Name $functionAppName `
    -StorageAccountName $storageAccount -Location $location `
    -Runtime PowerShell -RuntimeVersion 7.4 -FunctionsVersion 4 `
    -OSType Windows -PlanType Consumption

# System-assigned managed identity
Update-AzFunctionApp -ResourceGroupName $resourceGroup -Name $functionAppName -IdentityType SystemAssigned

# Confirm/link Application Insights — recent Az.Functions versions auto-provision this;
# if it's not already linked (check the portal), create and wire it explicitly:
$ai = New-AzApplicationInsights -ResourceGroupName $resourceGroup -Name $appInsightsName -Location $location
Update-AzFunctionAppSetting -ResourceGroupName $resourceGroup -Name $functionAppName -AppSetting @{
    "APPLICATIONINSIGHTS_CONNECTION_STRING" = $ai.ConnectionString
}

# Create the blob container, queue, and table (run this as an identity with Storage Data
# Contributor roles on this account — e.g. your own, if you provisioned it)
$adminCtx = New-AzStorageContext -StorageAccountName $storageAccount -UseConnectedAccount
New-AzStorageContainer -Name "cia-raw-payloads" -Context $adminCtx -Permission Off
New-AzStorageQueue -Name "cia-employee-ingest" -Context $adminCtx
New-AzStorageTable -Name "Employees" -Context $adminCtx

# Role assignments for the Function App's managed identity
$principalId = (Get-AzFunctionApp -ResourceGroupName $resourceGroup -Name $functionAppName).IdentityPrincipalId
$storageId   = (Get-AzStorageAccount -ResourceGroupName $resourceGroup -Name $storageAccount).Id
$vaultId     = (Get-AzKeyVault -VaultName $keyVaultName).ResourceId

New-AzRoleAssignment -ObjectId $principalId -RoleDefinitionName "Key Vault Secrets User"       -Scope $vaultId
New-AzRoleAssignment -ObjectId $principalId -RoleDefinitionName "Storage Blob Data Contributor"  -Scope $storageId
New-AzRoleAssignment -ObjectId $principalId -RoleDefinitionName "Storage Queue Data Contributor" -Scope $storageId
```

**✅ Validate before continuing:** in the portal, confirm the Function App shows "Running," has
an Identity blade with a populated Object ID, and Application Insights appears under its
Overview. Confirm the container, queue, and table all exist in Storage Explorer.

---

## Milestone 1 — Generate and store the HMAC secret

```powershell
# Cryptographically random 256-bit secret
$bytes = New-Object 'System.Byte[]' 32
[System.Security.Cryptography.RandomNumberGenerator]::Fill($bytes)
$hmacSecret = [Convert]::ToBase64String($bytes)

Set-AzKeyVaultSecret -VaultName $keyVaultName -Name "CiAnywhereHmacSecret" `
    -SecretValue (ConvertTo-SecureString $hmacSecret -AsPlainText -Force) `
    -Expires (Get-Date).AddDays(365) `
    -ContentType "HMAC shared secret — CiAnywhere webhook integration"

Write-Host "COPY THIS INTO A CiAnywhere VARIABLE NOW (it will not be shown again): $hmacSecret"

# Also store the storage account connection string, for the AzTable-based writes in Milestone 4
$storageKey = (Get-AzStorageAccountKey -ResourceGroupName $resourceGroup -Name $storageAccount)[0].Value
$connString = "DefaultEndpointsProtocol=https;AccountName=$storageAccount;AccountKey=$storageKey;EndpointSuffix=core.windows.net"
Set-AzKeyVaultSecret -VaultName $keyVaultName -Name "StorageAccountConnectionString" `
    -SecretValue (ConvertTo-SecureString $connString -AsPlainText -Force)

# Wire both into the Function App as Key Vault references (resolved automatically at runtime)
$kvUri = (Get-AzKeyVault -VaultName $keyVaultName).VaultUri
Update-AzFunctionAppSetting -ResourceGroupName $resourceGroup -Name $functionAppName -AppSetting @{
    "HmacSharedSecret"        = "@Microsoft.KeyVault(SecretUri=$($kvUri)secrets/CiAnywhereHmacSecret/)"
    "StorageConnectionString" = "@Microsoft.KeyVault(SecretUri=$($kvUri)secrets/StorageAccountConnectionString/)"
    "StorageAccountName"      = $storageAccount
}
```

**✅ Validate before continuing:** in the portal, open the Function App's Configuration →
Application Settings — `HmacSharedSecret` and `StorageConnectionString` should show a green
"Key Vault Reference" resolution status, not an error icon. If it shows an error, the role
assignment from Milestone 0 likely hasn't propagated yet (can take a few minutes) or the
`SecretUri` is malformed.

---

## Milestone 2 — Deploy a logging-only receiver (to observe CiAnywhere's real HMAC format)

We don't yet know exactly how CiAnywhere's "HMAC Authentication" type signs a request — which
header it uses, and whether the signature is hex or base64. Rather than guess, deploy a version
that logs everything first.

**`Receiver/function.json`**
```json
{
  "bindings": [
    { "authLevel": "function", "type": "httpTrigger", "direction": "in", "name": "Request", "methods": ["post"] },
    { "type": "http", "direction": "out", "name": "Response" }
  ]
}
```

**`Receiver/run.ps1`**
```powershell
using namespace System.Net
param($Request, $TriggerMetadata)

Write-Host "=== Headers ==="
$Request.Headers.GetEnumerator() | ForEach-Object { Write-Host "$($_.Key): $($_.Value)" }

Write-Host "=== Body (first 2000 chars) ==="
$bodyText = [string]$Request.Body
Write-Host $bodyText.Substring(0, [Math]::Min(2000, $bodyText.Length))

Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::OK
    Body       = "Logged."
})
```

Note: exact property shapes on `$Request` can vary slightly by Functions host version — if
`.Headers.GetEnumerator()` doesn't behave as expected, run `$Request.Headers | Get-Member` in a
quick test to see what you're actually working with.

**Configure CiAnywhere:**
1. Create a variable holding the HMAC secret value (the one generated in Milestone 1).
2. On the `Call Web Resource` step: Authentication → Type = `HMAC Authentication`, Credentials →
   point at that variable.
3. Set the URL to the Function's endpoint, including its function key
   (`https://<app>.azurewebsites.net/api/Receiver?code=<function-key>`) — found under the
   function's "Function Keys" blade after first deploy.
4. Run the ETL process.

**✅ Validate before continuing:** pull the invocation in Application Insights (Live Metrics or
Logs → search for "Headers") and identify:
- The exact header name carrying the signature (e.g. `X-Signature`, `Authorization`, something
  else entirely).
- Whether the value looks like hex (`a3f9...`) or base64 (`o/n5...==`).
- Whether there's a timestamp or nonce header alongside it (relevant for replay protection).

Write these down — Milestone 3 depends on them.

---

## Milestone 3 — Harden the receiver: real signature verification, blob write, queue enqueue

**`Receiver/run.ps1`** (replace the logging-only version)
```powershell
using namespace System.Net
param($Request, $TriggerMetadata)

# --- 1. Verify signature --------------------------------------------------
# Adjust the header name and comparison format below to match what Milestone 2 revealed.
$signatureHeader = $Request.Headers["X-Signature"]     # <-- confirm real header name
$secret          = $env:HmacSharedSecret
$rawBody         = [string]$Request.Body

$hmac = [System.Security.Cryptography.HMACSHA256]::new([Text.Encoding]::UTF8.GetBytes($secret))
$computedBytes = $hmac.ComputeHash([Text.Encoding]::UTF8.GetBytes($rawBody))
$computedHex   = ($computedBytes | ForEach-Object { $_.ToString("x2") }) -join ""
$computedB64   = [Convert]::ToBase64String($computedBytes)

$isValid = ($signatureHeader -eq $computedHex) -or ($signatureHeader -eq $computedB64)

if (-not $isValid) {
    Write-Host "IngestRejected: signature mismatch"
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = [HttpStatusCode]::Unauthorized
        Body       = "Invalid signature."
    })
    return
}

# --- 2. Persist raw payload to blob (audit / replay trail) ---------------
$context  = New-AzStorageContext -StorageAccountName $env:StorageAccountName -UseConnectedAccount
$blobName = "raw/{0:yyyy/MM/dd}/{1}.xml" -f (Get-Date), [Guid]::NewGuid()
$tempFile = Join-Path $env:TEMP "$([Guid]::NewGuid()).xml"
Set-Content -Path $tempFile -Value $rawBody -Encoding UTF8
Set-AzStorageBlobContent -Context $context -Container "cia-raw-payloads" -File $tempFile -Blob $blobName -Force | Out-Null

# --- 3. Enqueue for processing ---------------------------------------------
$queue   = Get-AzStorageQueue -Name "cia-employee-ingest" -Context $context
$message = (@{ blob = $blobName } | ConvertTo-Json -Compress)
# If AddMessageAsync isn't found on your installed Az.Storage version, run
# `$queue.CloudQueue | Get-Member` to find the equivalent — the underlying SDK type has shifted
# across module versions.
$queue.CloudQueue.AddMessageAsync([Microsoft.Azure.Storage.Queue.CloudQueueMessage]::new($message)).GetAwaiter().GetResult() | Out-Null

# --- 4. Heartbeat telemetry --------------------------------------------------
Write-Host "IngestSuccess: blob=$blobName"

Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::Accepted
    Body       = "Accepted for processing."
})
```

**✅ Validate before continuing, two tests:**
1. Send a request with a deliberately wrong secret (e.g. `Invoke-RestMethod` from your own
   machine with a bogus signature) → expect `401`, and an `IngestRejected` trace in App Insights.
2. Run the real CiAnywhere ETL → expect `202`, a new blob under `cia-raw-payloads`, and a new
   message in `cia-employee-ingest`.

---

## Milestone 4 — Processor function (queue-triggered): parse XML, upsert to Table Storage

**`Processor/function.json`**
```json
{
  "bindings": [
    { "name": "QueueItem", "type": "queueTrigger", "direction": "in", "queueName": "cia-employee-ingest", "connection": "AzureWebJobsStorage" }
  ]
}
```
`AzureWebJobsStorage` is the Function App's own required storage connection — already present
for the runtime to function at all, so it's the path of least resistance here. Converting this
specific binding to a fully identity-based connection is possible (extension bundles support it)
but not necessary for this build.

**`Processor/run.ps1`**
```powershell
param($QueueItem, $TriggerMetadata)

$context  = New-AzStorageContext -StorageAccountName $env:StorageAccountName -UseConnectedAccount
$blobName = $QueueItem.blob

$tempFile = Join-Path $env:TEMP "$([Guid]::NewGuid()).xml"
Get-AzStorageBlobContent -Context $context -Container "cia-raw-payloads" -Blob $blobName -Destination $tempFile -Force | Out-Null
[xml]$xml = Get-Content $tempFile -Raw

$tableCtx = New-AzStorageContext -ConnectionString $env:StorageConnectionString
$table    = Get-AzStorageTable -Name "Employees" -Context $tableCtx -ErrorAction Stop

# PartitionKey/RowKey design — confirm this fits how you'll actually query this table later.
# Fixed partition keeps batching simple at this scale; shard by e.g. school/department if you'll
# need partition-scoped queries. RowKey must be unique per row — employeeId alone isn't, given
# the duplicate employeeIds seen in test data, so it's combined with jobTitle here as a stand-in.
# Swap in whatever field combination is actually guaranteed unique per row (e.g. position code).
$partitionKey = "Employees"
$rows = $xml.Employees.Employee
$failures = 0

foreach ($row in $rows) {
    $rowKey = "{0}_{1}" -f $row.employeeId, ($row.jobTitle -replace '\s+', '')
    try {
        # Confirm -UpdateExisting is the correct current parameter for upsert behaviour via
        # `Get-Help Add-AzTableRow -Full` — this has been known to shift across module versions.
        Add-AzTableRow -Table $table.CloudTable -PartitionKey $partitionKey -RowKey $rowKey `
            -Property @{ EmployeeId = $row.employeeId; JobTitle = $row.jobTitle } `
            -UpdateExisting
    }
    catch {
        $failures++
        Write-Host "TableWriteFailed: employeeId=$($row.employeeId) rowKey=$rowKey error=$($_.Exception.Message)"
    }
}

if ($failures -gt 0) {
    # Throwing lets the queue trigger's built-in retry (default 5 attempts) and poison-queue
    # handling take over, rather than silently swallowing a partial failure.
    throw "ProcessorPartialFailure: $failures of $($rows.Count) rows failed for blob $blobName"
}

Write-Host "ProcessSuccess: blob=$blobName rows=$($rows.Count)"
```

**`requirements.psd1`** (shared across both functions)
```powershell
@{
    'Az.Accounts' = '3.*'
    'Az.Storage'  = '7.*'
    'AzTable'     = '2.*'
}
```

**`profile.ps1`** (enables Managed Identity auth at cold start)
```powershell
if ($env:MSI_SECRET) {
    Disable-AzContextAutosave -Scope Process | Out-Null
    Connect-AzAccount -Identity | Out-Null
}
```

**`host.json`** (extend the timeout if 10,000-row batches run long on Consumption; 10 min is the
Consumption-plan ceiling — move to a Premium plan if you outgrow it)
```json
{
    "version": "2.0",
    "functionTimeout": "00:10:00"
}
```

**Phase 2 hardening (optional, not needed to go live):** replace the `AzTable`/connection-string
path with direct Table REST API calls authenticated by a Managed-Identity bearer token
(`https://storage.azure.com/` resource), using `$batch` entity-group transactions (up to 100
entities sharing a partition per batch) for real throughput at 1,000–10,000 rows. This removes
the storage-key secret entirely but is meaningfully more code to get right — worth doing once the
simpler version is stable and proven, not before.

**✅ Validate before continuing:**
- Confirm rows appear correctly in Table Storage (Storage Explorer).
- Re-run the same queue message a second time — confirm rows update in place rather than
  duplicate (proves the upsert is working).
- Deliberately corrupt one row's data in a test payload and confirm it shows up as a
  `TableWriteFailed` trace, and that the message ends up retried and eventually in
  `cia-employee-ingest-poison` if it keeps failing.

---

## Milestone 5 — Logging and alerting

Application Insights is already wired via `APPLICATIONINSIGHTS_CONNECTION_STRING`; `Write-Host`
calls above land there as traces automatically.

**KQL — silence detection ("no data received"):**
```kql
traces
| where message startswith "IngestSuccess"
| where timestamp > ago(10h)   // set to comfortably cover one missed run once you know the real schedule
| count
```
Alert condition: fire when this returns `0`.

**KQL — write failures:**
```kql
traces
| where message startswith "TableWriteFailed"
| where timestamp > ago(15m)
```
Alert condition: fire when count > 0.

**Poison-queue alert:** a metric alert on the Storage Account, metric "Queue Message Count,"
dimension filtered to `cia-employee-ingest-poison`, threshold "greater than 0."

**Action group:**
```powershell
New-AzActionGroup -ResourceGroupName $resourceGroup -Name "ag-cia-sync-alerts" -ShortName "ciasync" `
    -Receiver @(New-AzActionGroupReceiver -Name "PrimaryEmail" -EmailReceiver -EmailAddress "you@yourdomain.edu.au")
```

For the scheduled-query (log search) alert rules themselves: the PowerShell cmdlet for these
(`New-AzScheduledQueryRule`) has changed its parameter shape across Az.Monitor versions enough
that I'd rather not hand you a snippet likely to be stale. Build these two via the Portal wizard
(Monitor → Alerts → Create → Alert rule → scope to the Function App / Log Analytics workspace →
paste the KQL above) the first time — it's a guided flow and hard to get subtly wrong. Once
you've got a working rule, you can export it and script future ones from that as a template.

**✅ Validate before continuing:** trigger each alert condition once deliberately — pause the
CiAnywhere schedule briefly to trip the silence alert, and force a row failure to trip the
write-failure alert — and confirm the email actually arrives.

---

## Milestone 6 — CiAnywhere-side configuration recap

- Variable holding the HMAC secret (Milestone 1), pointed to by the `Call Web Resource` step's
  Authentication → Credentials.
- Authentication Type = `HMAC Authentication`.
- URL = Function endpoint including the function-level key (`?code=...`) — a second layer on top
  of the HMAC check, per the earlier "stack both layers" reasoning.
- Content Type = `text/xml`.
- `Save Xml` step feeding the built XML into the variable referenced by `Call Web Resource`.

---

## Milestone 7 — Rotation and ongoing maintenance

- The HMAC secret has no forced expiry (unlike App Registration secrets) — it's stored in Key
  Vault with a self-imposed 365-day expiry as a reminder, not a hard cutoff.
- Optional: set up a Key Vault "near expiry" Event Grid subscription to email you ~30 days before
  the secret's `Expires` date, so rotation doesn't rely on remembering.
- To rotate: generate a new secret, update the Key Vault entry, update the CiAnywhere variable,
  confirm one successful run, then let the old value lapse.
- Revisit the silence-alert threshold once CiAnywhere's real run schedule is confirmed in
  production — the 10-hour placeholder above was chosen for a "several times a day" cadence and
  should be tightened once you know the actual interval.