Use Case: Audit Trails

Scenario

A compliance officer needs to reconstruct the complete state of a payroll record at any point in time. In a traditional approach, this requires manual joins across three separate tables — PayrollRecords, AuditEvents, and AuditEventFields — plus a fourth join to ApprovalRecords to understand what triggered each change. Writing, maintaining, and securing that query across multiple consumer systems is complex, slow to execute at scale, and difficult to audit in itself.

Multi-Query solves this by defining the entire tree once in a stored template. Compliance reports, API calls, and embedded expressions all share the same vetted SQL, ensuring that every audit query is consistent, read-only, and tenant-scoped.

Template Design

The template has three levels: the root PayrollRecord, its child AuditEvents, and each event's grandchild AuditEventFields (the field-level before/after values). A fourth sibling child collects the ApprovalRecords that authorized each audit event.

{
  "name": "Payroll Record Audit Trail",
  "sql": "SELECT RecordID, EmployeeID, PeriodStart, PeriodEnd, GrossPay, NetPay,
                 Status, CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy
          FROM   PayrollRecords
          WHERE  RecordID  = @RecordID
            AND  TenantID  = @TenantID",
  "recordType": "PayrollRecord",
  "isTemplate": true,
  "parameters": [
    { "name": "RecordID", "type": "int", "defaultValue": null }
  ],
  "options": {
    "outputFormat": "json",
    "outputFormatTemplate": "fragment",
    "maxDepth": 5,
    "cacheSeconds": 0
  },
  "children": [
    {
      "collectionName": "auditEvents",
      "sql": "SELECT AuditEventID, EntityID, EntityType, EventType,
                     EventTimestamp, InitiatedBy, SourceSystem
              FROM   AuditEvents
              WHERE  EntityID   = @parent.RecordID
                AND  EntityType = 'PayrollRecord'
                AND  TenantID   = @TenantID
              ORDER BY EventTimestamp DESC",
      "recordType": "AuditEvent",
      "isTemplate": false,
      "children": [
        {
          "collectionName": "fieldChanges",
          "sql": "SELECT FieldChangeID, AuditEventID, FieldName,
                         OldValue, NewValue, ChangeReason
                  FROM   AuditEventFields
                  WHERE  AuditEventID = @parent.AuditEventID
                    AND  TenantID     = @TenantID",
          "recordType": "AuditEventField",
          "isTemplate": false,
          "children": []
        },
        {
          "collectionName": "approvals",
          "sql": "SELECT ApprovalID, AuditEventID, ApproverID, ApproverName,
                         DecisionStatus, DecisionTimestamp, Comments
                  FROM   ApprovalRecords
                  WHERE  AuditEventID = @parent.AuditEventID
                    AND  TenantID     = @TenantID",
          "recordType": "ApprovalRecord",
          "isTemplate": false,
          "children": []
        }
      ]
    }
  ]
}

Store this JSON in dbo.Shared_Configurations with a catalogue code such as MQ_PAYROLL_AUDIT_TRAIL.

Sample Output

Calling GET /api/v1/expressions/multiquery/MQ_PAYROLL_AUDIT_TRAIL.json?RecordID=4821 returns a compact nested JsonArray tree:

[
  {
    "RecordID": 4821,
    "EmployeeID": 1109,
    "PeriodStart": "2025-11-01",
    "PeriodEnd": "2025-11-30",
    "GrossPay": 6800.00,
    "NetPay": 4956.50,
    "Status": "Posted",
    "LastModifiedOn": "2025-12-02T10:14:22Z",
    "LastModifiedBy": 44,
    "auditEvents": [
      {
        "AuditEventID": 90021,
        "EntityID": 4821,
        "EntityType": "PayrollRecord",
        "EventType": "StatusChange",
        "EventTimestamp": "2025-12-02T10:14:22Z",
        "InitiatedBy": 44,
        "SourceSystem": "PayrollEngine",
        "fieldChanges": [
          {
            "FieldName": "Status",
            "OldValue": "PendingApproval",
            "NewValue": "Posted",
            "ChangeReason": "Manager approval received"
          }
        ],
        "approvals": [
          {
            "ApprovalID": 5501,
            "ApproverName": "Sarah Thompson",
            "DecisionStatus": "Approved",
            "DecisionTimestamp": "2025-12-02T10:12:05Z",
            "Comments": "Verified against timesheet data"
          }
        ]
      },
      {
        "AuditEventID": 89944,
        "EntityType": "PayrollRecord",
        "EventType": "FieldUpdate",
        "EventTimestamp": "2025-12-01T16:30:01Z",
        "InitiatedBy": 29,
        "SourceSystem": "BizFirstPayroll",
        "fieldChanges": [
          {
            "FieldName": "GrossPay",
            "OldValue": "6500.00",
            "NewValue": "6800.00",
            "ChangeReason": "Overtime hours added"
          }
        ],
        "approvals": []
      }
    ]
  }
]

Compliance Considerations

Data Retention

Audit trail templates query live tables. Your organization's data retention policy governs how long AuditEvents and AuditEventFields rows are preserved. Do not rely on the Multi-Query engine for archival — use dedicated retention jobs and confirm that the underlying tables comply with your regulatory requirements (SOC 2, GDPR, HIPAA, etc.).

Immutability

Audit tables should be protected by database-level permissions so that only the audit service account can INSERT. The Multi-Query template service account must have SELECT only on AuditEvents, AuditEventFields, and ApprovalRecords. Grant the minimum required privilege and audit access to the template storage table (dbo.Shared_Configurations) itself.

Cache Setting

Audit endpoints must set "cacheSeconds": 0. Caching an audit trail response would mean a compliance officer could receive stale data — potentially hiding a recent change event. The zero-cache setting ensures every call reflects the current database state in real time.

TenantID Injection

The TenantID parameter is injected automatically from the caller's JWT. It is never accepted from the query string or request body. This guarantees that tenant A cannot retrieve audit records belonging to tenant B, even if they know the RecordID.