Generate SQL

Compiles an event filter to the chain of CTEs that represents it, from the body or from a saved filter.

Generate SQL

Compiles an event filter definition to the SQL that represents it. There are two routes with the same response: one compiles a definition sent in the body and the other an event filter that is already saved.

Route	Definition source	Success code
`POST /api/event-criterias/generate-sql`	The body	`201`
`GET /api/event-criterias/:id/sql`	The saved filter	`200`

Auth: Required — event-criteria:view-sql permission on both routes

The SQL is not executed: these routes compile and return. To validate it against BigQuery use Validate; to see real rows, Preview.

POST /api/event-criterias/generate-sql

Request Body

Field	Type	Required	Description
`queryBlocks`	`QueryBlockDto[]`	Yes	Minimum 1 element. The definition to compile.
`format`	string	No	`parameterized` \| `literal`. Default `parameterized`.

The structure of each block is in Definition.

This endpoint receives only the definition: name and description are not part of the body and sending them produces a 400 for unknown property.

Example

curl -X POST https://api.reten.ai/api/event-criterias/generate-sql \
  -H "Authorization: Bearer <token>" \
  -H "x-tenant-id: <tenant-id>" \
  -H "x-project-id: <project-id>" \
  -H "Content-Type: application/json" \
  -d '{
    "queryBlocks": [
      {
        "order": 0,
        "from": { "table": "event_transactions", "joins": [] },
        "select": {
          "distinct": true,
          "items": [
            { "kind": "column", "column": { "table": "event_transactions", "column": "event_id" } }
          ]
        },
        "where": {
          "kind": "group",
          "logicalOperator": "AND",
          "children": [
            {
              "kind": "predicate",
              "column": { "table": "event_transactions", "column": "status" },
              "comparisonOperator": "EQUALS",
              "value": "Facturada"
            },
            {
              "kind": "semiJoinPredicate",
              "table": "event_transactions",
              "semiJoin": "products",
              "subWhere": {
                "kind": "group",
                "logicalOperator": "AND",
                "children": [
                  {
                    "kind": "predicate",
                    "field": "categorization.category_name",
                    "comparisonOperator": "IN",
                    "value": ["Food Service", "HORECA"]
                  }
                ]
              }
            }
          ]
        }
      }
    ]
  }'

import axios from 'axios';

const body = {
  queryBlocks: [
    {
      order: 0,
      from: { table: 'event_transactions', joins: [] },
      select: {
        distinct: true,
        items: [
          { kind: 'column', column: { table: 'event_transactions', column: 'event_id' } },
        ],
      },
      where: {
        kind: 'group',
        logicalOperator: 'AND',
        children: [
          {
            kind: 'predicate',
            column: { table: 'event_transactions', column: 'status' },
            comparisonOperator: 'EQUALS',
            value: 'Facturada',
          },
          {
            kind: 'semiJoinPredicate',
            table: 'event_transactions',
            semiJoin: 'products',
            subWhere: {
              kind: 'group',
              logicalOperator: 'AND',
              children: [
                {
                  kind: 'predicate',
                  field: 'categorization.category_name',
                  comparisonOperator: 'IN',
                  value: ['Food Service', 'HORECA'],
                },
              ],
            },
          },
        ],
      },
    },
  ],
};

const response = await axios.post(
  'https://api.reten.ai/api/event-criterias/generate-sql',
  body,
  { headers: { Authorization: 'Bearer <token>', 'x-tenant-id': '<tenant-id>', 'x-project-id': '<project-id>' } },
);
const sql = response.data;

GET /api/event-criterias/:id/sql

Compiles an already saved filter. The definition is read from the filter and validated before compiling it.

Path Parameters

Parameter	Type	Description
`id`	UUID	Id of the event filter.

Query Parameters

Parameter	Type	Default	Description
`format`	string	`parameterized`	`parameterized` \| `literal`.

Example

curl "https://api.reten.ai/api/event-criterias/6d2a9c41-7e35-48b0-b1c9-0f8e7d6c5b4a/sql?format=literal" \
  -H "Authorization: Bearer <token>" \
  -H "x-tenant-id: <tenant-id>" \
  -H "x-project-id: <project-id>"

import axios from 'axios';

const id = '6d2a9c41-7e35-48b0-b1c9-0f8e7d6c5b4a';
const response = await axios.get(
  `https://api.reten.ai/api/event-criterias/${id}/sql`,
  {
    headers: { Authorization: 'Bearer <token>', 'x-tenant-id': '<tenant-id>', 'x-project-id': '<project-id>' },
    params: { format: 'literal' },
  },
);
const sql = response.data;

Output format — `format`

The format field decides how the SQL values are emitted and which fields the response carries. It is optional; if omitted, the effective value is parameterized.

`format`	How it emits values	Extra fields in the response
`parameterized`	Named `@pN` placeholders in the `query`; the binding goes separately.	`params`, `types`
`literal`	Values are inlined as safe literals inside the `query`.	(none)

In generate-sql (POST) the format goes in the body.
In :id/sql (GET) the format goes as a query param.

Only the strings "parameterized" and "literal" are accepted; any other value produces a 400.

Response — `IEventCriteriaSql`

It is a discriminated union on format. Both variants share a base and differ in the binding fields.

Base (common to both formats):

Field	Type	Description
`query`	string	A chain of CTEs (the clauses are separated by `\n` line breaks): without the leading `WITH` and without the trailing `SELECT`, to splice it into a `WITH … SELECT`.
`finalCteName`	string	Name of the final CTE of the chain; always `CTE_VALID_EVENTS`. It is the "handle" of the result: a consumer does `SELECT … FROM CTE_VALID_EVENTS`.
`subjectColumn`	string \| null	Identity column exposed by the final CTE. In an event filter it is always `null`.

format: "parameterized":

Field	Type	Description
`format`	`"parameterized"`	Discriminator.
`params`	`Record<string, ScalarValue>`	Binding of each `@pN` → its value. `ScalarValue` = `string` \| `number` \| `boolean`.
`types`	`Record<string, string>`	BigQuery parameter type of each `@pN`: `STRING`, `TIMESTAMP`, `BOOL`, `FLOAT64`, `INT64`, `NUMERIC`, `BIGNUMERIC`.

format: "literal" — only the base; no params or types.

In parameterized, params and types are part of the contract: the query carries @pN and is not executable without its binding and its types. In literal there is no binding —the values live inside the query— so it does not carry params or types. Always discriminate by format before reading params/types.

subjectColumn is null in every event filter: the final CTE projects events, not subjects, and the catalog does not declare an identity column. The columns the CTE exposes are those of the definition's select.

Example — `parameterized` (default)

201 response to the example POST above:

{
  "format": "parameterized",
  "query": "CTE_VALID_EVENTS AS (\nSELECT DISTINCT `event_transactions`.`event_id`\nFROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions\nWHERE `event_transactions`.`status` = @p0 AND EXISTS (SELECT 1 FROM UNNEST(`event_transactions`.`items`) AS `items_item`, `reten-prod.tenant_a1b2c3d4.product_properties` AS `product_properties` WHERE `product_properties`.`sku` = `items_item`.`item_id` AND (`product_properties`.`categorization`.`category_name` IN (@p1, @p2)))\n)",
  "finalCteName": "CTE_VALID_EVENTS",
  "subjectColumn": null,
  "params": { "p0": "Facturada", "p1": "Food Service", "p2": "HORECA" },
  "types": { "p0": "STRING", "p1": "STRING", "p2": "STRING" }
}

The query is a single string with \n line breaks. Formatted for readability:

CTE_VALID_EVENTS AS (
SELECT DISTINCT `event_transactions`.`event_id`
FROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions
WHERE `event_transactions`.`status` = @p0
  AND EXISTS (
    SELECT 1
    FROM UNNEST(`event_transactions`.`items`) AS `items_item`,
         `reten-prod.tenant_a1b2c3d4.product_properties` AS `product_properties`
    WHERE `product_properties`.`sku` = `items_item`.`item_id`
      AND (`product_properties`.`categorization`.`category_name` IN (@p1, @p2))
  )
)

Each @pN is bound with params[pN] (its value) and types[pN] (its BigQuery type); without that binding the query is not executable.

How the chain is consumed

The response is not an executable query: it is the chain of CTEs that has to be wrapped. In the snippets below, {query} is replaced by the value of the query field exactly as it comes: it already carries its own CTE_VALID_EVENTS AS ( … ), so the CTE header is not written again.

To run it stand-alone:

WITH {query}
SELECT * FROM CTE_VALID_EVENTS

To join it against another query, {query} is spliced into the caller's own WITH —followed by a comma— and you JOIN CTE_VALID_EVENTS on the event column that the select of the definition projects (for example event_id):

WITH
{query},
mis_metricas AS (
  SELECT t.event_id, t.pricing.final_amount
  FROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS t
)
SELECT SUM(m.final_amount) AS total
FROM mis_metricas AS m
JOIN CTE_VALID_EVENTS AS v ON v.event_id = m.event_id

The name CTE_VALID_EVENTS is stable and does not depend on the filter: it is the finalCteName of every event filter. The columns it exposes, on the other hand, are those of the select of each definition.

Example — `literal`

The same definition with format: "literal":

{
  "format": "literal",
  "query": "CTE_VALID_EVENTS AS (\nSELECT DISTINCT `event_transactions`.`event_id`\nFROM `reten-prod.tenant_a1b2c3d4.event_transactions` AS event_transactions\nWHERE `event_transactions`.`status` = 'Facturada' AND EXISTS (SELECT 1 FROM UNNEST(`event_transactions`.`items`) AS `items_item`, `reten-prod.tenant_a1b2c3d4.product_properties` AS `product_properties` WHERE `product_properties`.`sku` = `items_item`.`item_id` AND (`product_properties`.`categorization`.`category_name` IN ('Food Service', 'HORECA')))\n)",
  "finalCteName": "CTE_VALID_EVENTS",
  "subjectColumn": null
}

The values are inlined as safe literals (escaped strings in single quotes, TIMESTAMP('…') for instants, NUMERIC '…' for exact decimals); there are no params or types.

Validation Rules

Before compiling, the definition goes through the two layers of validation, and both return 400 Bad Request. Business validation runs on both endpoints: generate-sql validates the body definition, and :id/sql validates the saved filter before compiling it.

The full catalog of rules and their exact messages is in Definition → Validation Rules.

Error Responses

Status	Description
`400`	Invalid DTO (missing field, wrong type, enum out of range, invalid `format`, unknown property such as `name`/`description`) or a violated business rule. In `:id/sql`, also if the `id` is not a valid UUID.
`401`	Missing or invalid `Authorization: Bearer`.
`403`	The user does not have the `event-criteria:view-sql` permission, or the `x-tenant-id` is not authorized.
`404`	In `:id/sql`, no event filter with that `id` exists in the tenant (`Event criteria not found`), or the tenant does not have a dataset configured.