Use Case: Config Snapshots

Scenario

BizFirst platform tenants operate with a two-layer configuration model. A master configuration layer defines system-wide defaults for every feature — pay frequencies, rounding rules, tax codes, workflow thresholds, and more. A tenant override layer stores per-tenant deviations from those defaults. When a tenant setting is absent from the override layer, the platform falls back to the master value.

Administrators regularly need to export, diff, and restore configuration states — especially before a major tenant migration, a platform upgrade, or a regulatory audit. Constructing this export manually requires joining SharedConfigurations (master) against TenantConfigOverrides (child) for every AppDomainID in scope, then grouping by configuration key. That is tedious, inconsistent across environments, and difficult to version.

A Multi-Query template collapses this into a single stored, auditable, reusable call. The result is a self-describing nested JSON tree that can be stored as a versioned snapshot file.

Template

The root query retrieves all master configuration rows for the specified application domain and tenant. The child query retrieves every tenant-level override keyed to each master config row via @parent.ConfigID.

{
  "name": "Tenant Configuration Snapshot",
  "sql": "SELECT ConfigID, Code, [Value], Category, AppDomainID,
                 DataDomainID, IsActive, CreatedOn, LastModifiedOn
          FROM   SharedConfigurations
          WHERE  AppDomainID = @AppDomainID
            AND  TenantID    = @TenantID
            AND  Deleted     = 0
            AND  Archived    = 0
          ORDER BY Category, Code",
  "recordType": "MasterConfig",
  "isTemplate": true,
  "parameters": [
    { "name": "AppDomainID", "type": "int", "defaultValue": null }
  ],
  "options": {
    "outputFormat": "json",
    "outputFormatTemplate": "fragment",
    "maxDepth": 3,
    "cacheSeconds": 300
  },
  "children": [
    {
      "collectionName": "overrides",
      "sql": "SELECT OverrideID, ConfigID, OverrideValue, EffectiveFrom,
                     EffectiveTo, OverrideReason, CreatedOn, CreatedBy
              FROM   TenantConfigOverrides
              WHERE  ConfigID  = @parent.ConfigID
                AND  TenantID  = @TenantID
                AND  Deleted   = 0
              ORDER BY EffectiveFrom DESC",
      "recordType": "TenantConfigOverride",
      "isTemplate": false,
      "children": []
    }
  ]
}

Store this template in dbo.Shared_Configurations with catalogue code MQ_TENANT_CONFIG_SNAPSHOT.

Exporting as JSON

Use the .json endpoint to retrieve the full snapshot in machine-readable form. This output can be piped directly to a file for archival or checked into a configuration version control repository.

# PowerShell — export snapshot for tenant 42, app domain 7
$token  = "Bearer eyJhbGciOiJSUzI1NiIsInR5..."
$url    = "https://api.bizfirstai.com/api/v1/expressions/multiquery" +
          "/MQ_TENANT_CONFIG_SNAPSHOT.json?AppDomainID=7"
$output = "snapshots\tenant42-appdomain7-$(Get-Date -Format 'yyyyMMdd').json"

Invoke-RestMethod -Uri $url `
  -Headers @{ Authorization = $token } `
  -Method GET |
  ConvertTo-Json -Depth 20 |
  Out-File -FilePath $output -Encoding utf8

Write-Host "Snapshot written to $output"

The resulting file contains the complete master-plus-override tree at the moment of export. Store snapshots in a dated folder hierarchy (snapshots\YYYY\MM\) so that any historical state can be retrieved by date.

Diff / Restore Pattern

Once two snapshots exist (e.g., pre-migration and post-migration), they can be diffed at the JSON level to identify configuration drift. A companion stored script — addressable via the run-stored endpoint — can accept a snapshot payload and apply it back to TenantConfigOverrides, effectively restoring a prior configuration state without manual data surgery.

The recommended pattern is:

1. Export snapshot before any configuration change using the .json endpoint.
2. Apply the change through the normal admin UI or configuration API.
3. Export snapshot after the change.
4. Use a JSON diff tool (or a BizFirst ProcessEngine comparison node) to verify that only the intended keys changed.
5. If a rollback is needed, invoke the restore script via POST /api/v1/expressions/multiquery/scripts/run-stored with the before-snapshot as input.

Security Notes

The TenantID is always injected from the caller's JWT — it is never accepted as a query parameter. This means a tenant administrator cannot snapshot another tenant's configuration even if they know the target AppDomainID. Restrict the TenantAdmin role to authorized personnel only, as snapshot exports include all active configuration values including sensitive overrides.