Writing Templates

Root Template Fields

The root object is the entry point of every template. It is identified by "isTemplate": true and is the only record directly addressable by the API via its catalogue code.

Parameter Object

Each entry in the parameters array declares one caller-supplied SQL parameter. Parameters are resolved from the API query string, the POST body, or the EvaluationContext (for expression directives).

Options Object

The options object controls how the engine formats and caches results. All fields are optional; the defaults shown below are applied when the field is absent.

ChildCollection Object

Each entry in the children array defines one nested collection. The engine executes the child SQL once for every row returned by the parent query, substituting @parent.* tokens with the current parent row's column values.

SQL Writing Guidelines

• Always filter by TenantID. Every SELECT at every level must include AND TenantID = @TenantID. The engine injects the tenant from the caller's JWT, but SQL must enforce it to prevent cross-tenant data leakage if the engine layer is ever bypassed.
• Reference the parent via @parent.ColumnName. Child SQL joins to the parent row using the @parent. prefix. The column name must exactly match the column returned in the parent SELECT (case-insensitive). For grandchildren, use @parent.parent.ColumnName.
• Use parameterized queries only. All dynamic values must come from declared parameters or @parent tokens. Never concatenate user-supplied strings into the SQL. The engine uses SqlCommand.Parameters for all bindings.
• Avoid ORDER BY on parent-level queries. Because the engine issues one child query per parent row, the order of parent rows affects execution order but not result structure. Apply ORDER BY at child levels where sort order is meaningful for display.
• Filter soft-deleted records. Always include AND Deleted = 0 AND Archived = 0 unless the use case explicitly requires deleted records (e.g., an audit trail). Omitting these filters returns logically deleted rows to callers.
• Qualify all column names. In queries that join multiple tables, prefix every column with its table alias to prevent ambiguity errors when schema changes add identically-named columns to joined tables.

Full Example — 3-Level Template

A realistic three-level template: Clients (root) → Projects (child) → Timesheets (grandchild). Each level uses explicit column lists, TenantID filtering, and @parent token joins.

{
  "name": "Client Project Timesheets",
  "sql": "SELECT c.ClientID,
                 c.ClientName,
                 c.IndustryCode,
                 c.AccountManagerID,
                 c.ContractStartDate,
                 c.ContractEndDate
          FROM   Clients c
          WHERE  c.TenantID  = @TenantID
            AND  c.IsActive  = 1
            AND  c.Deleted   = 0
            AND  c.Archived  = 0
          ORDER BY c.ClientName",
  "recordType": "Client",
  "isTemplate": true,
  "parameters": [
    { "name": "PeriodStart", "type": "datetime", "defaultValue": null },
    { "name": "PeriodEnd",   "type": "datetime", "defaultValue": null }
  ],
  "options": {
    "outputFormat": "json",
    "outputFormatTemplate": "fragment",
    "maxDepth": 5,
    "cacheSeconds": 120
  },
  "children": [
    {
      "collectionName": "projects",
      "sql": "SELECT p.ProjectID,
                     p.ClientID,
                     p.ProjectName,
                     p.ProjectCode,
                     p.Status,
                     p.BudgetHours,
                     p.BillableRate
              FROM   Projects p
              WHERE  p.ClientID  = @parent.ClientID
                AND  p.TenantID  = @TenantID
                AND  p.Deleted   = 0
              ORDER BY p.ProjectName",
      "recordType": "Project",
      "isTemplate": false,
      "children": [
        {
          "collectionName": "timesheets",
          "sql": "SELECT ts.TimesheetID,
                         ts.ProjectID,
                         ts.EmployeeID,
                         ts.WorkDate,
                         ts.HoursLogged,
                         ts.IsBillable,
                         ts.NarrativeNote,
                         ts.ApprovedBy,
                         ts.ApprovedOn
                  FROM   Timesheets ts
                  WHERE  ts.ProjectID  = @parent.ProjectID
                    AND  ts.TenantID   = @TenantID
                    AND  ts.WorkDate  >= @PeriodStart
                    AND  ts.WorkDate  <= @PeriodEnd
                    AND  ts.Deleted   = 0
                  ORDER BY ts.WorkDate DESC",
          "recordType": "Timesheet",
          "isTemplate": false,
          "children": []
        }
      ]
    }
  ]
}

Store this with catalogue code MQ_CLIENT_PROJECT_TIMESHEETS and call it as:

GET /api/v1/expressions/multiquery/MQ_CLIENT_PROJECT_TIMESHEETS.json
    ?PeriodStart=2025-11-01&PeriodEnd=2025-11-30
Authorization: Bearer <token>