Portal Community
Administration · Operations

Maintenance

Day-to-day management of templates, troubleshooting common problems, and monitoring Multi-Query performance.

Managing Templates

Templates live in the dbo.Shared_Configurations table. Each template is a row with a Code (the catalogue code) and a Value (the JSON template definition). No application restart is required after updating a template — the engine loads the template JSON per-request.

Locate the template

Query dbo.Shared_Configurations by Code and TenantID. The Code must exactly match the catalogue code used in the directive or REST endpoint path.

SELECT ID, Code, Value, IsActive, Deleted, LastModifiedOn
FROM dbo.Shared_Configurations
WHERE Code = 'PAYROLL_DEPT_SUMMARY'
  AND TenantID = @TenantID
  AND Deleted = 0;
Update the JSON value

Modify the Value column with the new template JSON. If you maintain a version field inside the JSON object (e.g., "version": 2), increment it to support change tracking and rollback.

UPDATE dbo.Shared_Configurations
SET Value = @newTemplateJson,
    LastModifiedOn = GETDATE(),
    LastModifiedBy = @editorUserID
WHERE Code = 'PAYROLL_DEPT_SUMMARY'
  AND TenantID = @TenantID;
Set audit columns

Always update LastModifiedOn (DATETIME) and LastModifiedBy (INT user ID) when saving changes. This supports audit trail queries and rollback identification.

Verify — no restart needed

Templates are resolved per-request from the database. The change takes effect immediately on the next API call or expression evaluation. Confirm by calling the execute endpoint and inspecting the response.

Disabling a Template

To temporarily block execution of a template without deleting it, set the IsActive flag to 0. The engine will return an HTTP 404 Not Found for direct API calls, and the expression directive will return an empty string.

UPDATE dbo.Shared_Configurations
SET IsActive = 0,
    LastModifiedOn = GETDATE(),
    LastModifiedBy = @editorUserID
WHERE Code = 'PAYROLL_DEPT_SUMMARY'
  AND TenantID = @TenantID;

Re-enable the template by setting IsActive = 1. This is the preferred approach for rollbacks during incident response — it is faster than restoring JSON content.

Soft-Deleting a Template

For permanent removal, set Deleted = 1. This has the same operational effect as IsActive = 0 (callers receive 404 / empty string), but additionally signals that the record is logically deleted and should be excluded from administrative listings and exports.

UPDATE dbo.Shared_Configurations
SET Deleted = 1,
    LastModifiedOn = GETDATE(),
    LastModifiedBy = @editorUserID
WHERE Code = 'PAYROLL_DEPT_SUMMARY'
  AND TenantID = @TenantID;

Soft-deleted records are never physically removed. This preserves the audit trail and allows recovery if a deletion was made in error.

Troubleshooting

The following accordion sections cover the most common failure modes. Expand each section for diagnostic steps.

404 Template Not Found

The engine could not locate a matching row in dbo.Shared_Configurations. Check the following in order:

  1. The Code column value exactly matches the code used in the URL or directive (case-sensitive comparison depending on database collation)
  2. The TenantID column matches the TenantID in the caller's JWT — use the JWT debugger to confirm the CurrentTenantIDWithCheck_Int claim
  3. IsActive is 1 — a value of 0 causes the engine to treat the template as absent
  4. Deleted is 0 — a value of 1 soft-deletes the template and produces the same result
  5. The row exists for the correct environment (dev/staging/prod may have separate databases)
Empty Child Collection

The parent query returns rows but a child query returns an empty array, even though data should exist.

  1. Verify that the @parent.ColumnName token in the child SQL exactly matches the column name returned by the parent query. SQL Server column names are case-insensitive at the DB level but the @parent token parser may be case-sensitive — use the exact alias as written in the parent SELECT list.
  2. Add a @parent.id reference to the child query and run it manually using a known parent ID to confirm the JOIN condition returns rows.
  3. Check that the parent column referenced is not aliased differently in the SELECT clause vs the @parent.ColumnName reference.
  4. Confirm the child query also includes WHERE TenantID = @TenantID — missing tenant filter can cause zero rows when data exists under a different tenant.
Slow Queries

Multi-Query executes one SQL statement per node in the template tree, multiplied by the number of parent rows. Performance compounds quickly without proper indexing and filtering.

  1. Enable cacheSeconds on the template root — for reference data that changes infrequently, caching eliminates repeated SQL round-trips
  2. Add database indexes on all foreign key columns used in @parent joins
  3. Tighten the parent row filter — the fewer rows the root query returns, the fewer child executions are required
  4. Reduce maxDepth in the template JSON to limit recursion depth for self-referential trees
  5. Replace SELECT * with explicit column lists — wide rows increase network transfer cost
  6. Use SQL Server Query Store to identify the slowest individual statements within a Multi-Query execution
401 Unauthorized

The JWT failed validation. Common causes:

  • Token has expired — check the exp claim. Obtain a fresh token and retry.
  • Issuer mismatch — the iss claim does not match JWT:Issuer in app settings. Confirm the token was issued by the correct BizFirst identity server.
  • Audience mismatch — the aud claim does not match JWT:Audience. Ensure the token was requested for the correct API audience.
  • Clock skew exceeded — server and client clocks are more than 5 minutes apart. Synchronize server time via NTP.
  • Signing key mismatch — the token was signed with a different key than what is configured in JWT:Key.
403 Forbidden

The token is valid but the authenticated user does not hold an authorized role. The [AuthorizeTenantAdminAttribute] requires the Admin or TenantAdmin role. Verify that the user's account has been granted one of these roles for the relevant tenant in BizFirst Identity administration.

Expression Directive Returns Empty

When {{multi-query:sqlserver.CODE}} evaluates to an empty string inside a workflow or template:

  1. Check the platform log for a warning message referencing the directive key — this will identify whether the failure is a missing template, a missing parameter, or an engine resolution failure
  2. Confirm that all @paramName parameters referenced in the template SQL are present in the EvaluationContext before the node that evaluates the directive
  3. Add a Set Expression Context node earlier in the workflow to explicitly populate required parameters
  4. Verify the template code is correct — a typo in the directive token produces a 404 internally which is silently converted to an empty string
  5. Check IsActive and Deleted on the template row, as described in the 404 section above

Performance Tips

Monitoring

BizFirst Observe integration
Rate limit counters, per-template execution time, SQL row counts, and error rates are emitted as BizFirst Observe metrics under the MultiQuery.* metric namespace. Set up Grafana alerts on the MultiQuery.Execute error rate and P95 latency metrics to detect template regressions before they impact users. The templateCode label is included on all metrics, enabling per-template dashboards.
Security & Permissions Evolution & Versioning