Scripting Patterns and Idempotency

Idempotency: the central problem#

Re-running a script must not produce duplicates. Otherwise every failure becomes a manual cleanup, and people stop re-running scripts — which defeats the purpose of having them.

Three strategies, in order of preference.

1. Rebuild from scratch#

The cleanest approach for development and demonstration models: the script drops and recreates its own database.

& $exe @conn --create --name "Demo Plant"

Nothing to reconcile, because nothing pre-exists. This is what Infohub's own demo-model scripts do — each drops and rebuilds its own database, which makes every run identical and every result reproducible.

Obviously unavailable against production.

2. Create-if-absent#

Check before creating:

function Get-OrCreateFolder {
    param([string]$Name, $ParentId)

    $existing = & $exe @conn --list-metric-folders --parent-id $ParentId --output-format json | ConvertFrom-Json |
        Where-Object { $_.Name -eq $Name }

    if ($existing) { return $existing.MetricFolderID }

    & $exe @conn --create-metric-folder --name $Name --parent-id $ParentId | Out-Null
    if ($LASTEXITCODE -ne 0) { throw "create folder '$Name' failed" }

    # resolve the new id
    (& $exe @conn --list-metric-folders --parent-id $ParentId --output-format json | ConvertFrom-Json |
        Where-Object { $_.Name -eq $Name }).MetricFolderID
}

More code, and it makes the script safe to re-run against an instance that partially exists. This is the right shape for anything touching production.

A few verbs are already idempotent, so check the option reference before writing a guard you do not need:

Where a verb is not idempotent, --dry-run is the next best thing for the steps that have it — --delete-entity, --gap-skip-to, --import-model, --update-all-out-of-sync, --deploy and --undeploy all report what they would change without changing it.

3. Explicit phases#

Split the script into phases that can be run independently:

.\build-plant.ps1 -Phase Foundations
.\build-plant.ps1 -Phase Model
.\build-plant.ps1 -Phase Charts

Then a failure in Charts does not require re-running Foundations. Useful for long builds and for genuinely non-idempotent steps.

Structuring a long script#

A four-hundred-step script written as four hundred literal invocations is unmaintainable. Wrap the CLI in helpers named for what they mean.

# --- once, at the top ---
$exe  = "C:\Program Files\Flow Software\Flow\Config\Flow Config.exe"
$conn = @('--host', $Host, '--database', $Database)
if ($env:FLOW_SQL_USER) { $conn += @('--user', $env:FLOW_SQL_USER, '--password', $env:FLOW_SQL_PASSWORD) }

# --- one helper per concept ---
function New-Aggregation {
    param($MetricId, [string]$Name, $SourceId, [string]$Interval, [string]$Aggregation, [string]$Uom)
    Invoke-FlowStep "measure: $Name" @(
        '--create-measure', '--metric-id', $MetricId, '--name', $Name,
        '--retrieval-type', 'aggregation', '--source-measure-id', $SourceId,
        '--interval-type', $Interval, '--aggregation-type', $Aggregation, '--uom', $Uom
    )
}

The body of the script then reads like a description of the model rather than a wall of options:

$hourly = New-Aggregation $mSteam "Steam Flow Hourly" $tag    "hourly" "Average" "kg/s"
$daily  = New-Aggregation $mSteam "Steam Flow Daily"  $hourly "daily"  "Average" "kg/s"

That is the difference between a script someone can review and one nobody will touch.

Credentials from the environment#

Never hard-code a SQL password in a script:

$env:FLOW_SQL_USER     = "flow_build"
$env:FLOW_SQL_PASSWORD = "<from your secret store>"

Leaving both unset should fall back to integrated security, with a note saying so. Supplying a username with no password should be refused up front rather than surfacing later as an opaque login failure.

This is the convention Infohub's own QA and demo scripts follow — no script in that set contains a credential.

Portability rules#

Two rules that keep a script working on a second instance.

Never hard-code an ID. Chart type IDs, connection type IDs, and package IDs are auto-increment rows and differ between instances. Look them up by name at the start of the run.

Resolve by name, not position. --list-* output order is not a contract. Filter by name, and on the field the listing actually emits — MetricFolderID, MetricID, MeasureID, ParentID — rather than an Id you assume is there.

Name the engine and platform. A fresh instance has one of each and the template verbs use them. The moment a script creates a second, --instantiate, --derive and --create-template refuse until --engine-id and --platform-id say which. Resolve both by name at the start, as you do for chart types.

Keep it PowerShell 5.1-safe#

Windows PowerShell 5.1 is what is present on most Windows Server installations, and it lacks several conveniences:

• No ternary operator — use if/else
• No ?? null-coalescing
• No && / || pipeline chaining — use ; and if ($?)
• ConvertFrom-Json returns a PSCustomObject, not a hashtable

Writing to 5.1 costs little and means the script runs wherever Infohub does. Infohub's own QA scripts hold to this deliberately, along with ASCII-only content to avoid encoding surprises.

Quoting the model attribute token#

The attribute token contains real double quotes:

$tok = '[ModelAttribute("Unit Number")]'
$tag = "$tok-FT-001.PV"

Single-quoted so PowerShell does not interpolate, and the double quotes reach the CLI intact. This is a recurring stumble when building templated tags by script.

Read stdout and stderr concurrently#

If you capture process output, read both streams concurrently. Reading stdout to completion and then stderr can deadlock when a verb writes enough to stderr to fill the pipe buffer — the child blocks writing while the parent blocks reading the other stream.

The simplest safe approach is to let --logfile capture the detail and only inspect the exit code in the script.

Log everything, one log per step#

& $exe @conn --logfile "$logDir\$('{0:d4}' -f $stepNo).log" @Arguments

When a run fails at step 340, that log is the evidence. Keep them as build artefacts.

Report a summary and an honest exit code#

The end of a build script should say what happened and exit accordingly:

if ($failures.Count -gt 0) {
    Write-Host "`n$($failures.Count) of $stepNo steps failed:"
    $failures | ForEach-Object { Write-Host "  $_" }
    exit 1
}
Write-Host "`nAll $stepNo steps succeeded."

A build script that exits 0 after failures is worse than one that never ran, because it produces a model everyone believes is complete.

Record what you could not do#

When a script cannot express something, record it in place rather than silently omitting it:

# GAP: the doc wants a rolling 28-day window on this measure;
#      leaving as 7-day until the requirement is confirmed

Infohub's own model-authoring convention uses an explicit gap comment for exactly this. It means the next person reads the limitation instead of assuming the model is complete, and a review can see what was deliberately left out.

Verify at the end#

A build script's last phase should assert rather than assume:

$measures = & $exe @conn --list-measures --output-format json | ConvertFrom-Json

if ($measures.Count -lt $expected)            { throw "only $($measures.Count) measures" }
if ($measures | Where-Object { -not $_.UOM }) { throw "measures without a unit" }

foreach ($c in $charts) {
    $tree = & $exe @conn --list-chart-tree --chart-id $c --output-format json | ConvertFrom-Json
    if (-not $tree.Items) { throw "chart $c has no content" }
}

# every instance at its template's version
& $exe @conn --list-out-of-sync --entity-type metric

Counts, units, chart content, template sync. Cheap to write, and it converts "the script finished" into "the model is right".

Do not deploy from a build script#

Build, verify, then deploy as a separate, deliberate step. Deploying mid-build starts engines against half-finished configuration, and a build script that also deploys cannot be safely re-run.

Infohub's own demo-model scripts build entirely without deploying and without starting anything.

Checklist#

1. Decide the idempotency strategy before writing.
2. Credentials from the environment.
3. Helpers named for concepts, not raw invocations.
4. No hard-coded IDs; resolve by name.
5. PowerShell 5.1-safe.
6. Exit code checked after every step.
7. Outcomes asserted where "did nothing" is possible.
8. One log per step, kept.
9. Honest summary and exit code.
10. Gaps recorded in comments.
11. Deployment as a separate step.

See also#

• Exit Codes and Error Handling
• Building a Model from the CLI
• CLI Recipes for CI
• Getting Started with the Config CLI