Custom Queries

What are Custom Queries?

Custom Queries are a powerful backend feature that enables users and administrators to define, store, and execute dynamic SQL queries tailored to specific business needs. Unlike standard queries, which are fixed and limited to predefined logic, Custom Queries allow for flexible data retrieval, complex filtering, and advanced reporting directly from the database layer.

Definition and Purpose

Custom Queries are designed to address scenarios where the default API endpoints are insufficient. They provide:

• Dynamic query composition: Users can specify entities, joins, filters, and output fields.
• Parameterization: Queries can include variables, allowing runtime customization (e.g., filtering by user, date, or status).
• Advanced data access: Useful for analytics, integrations, and custom dashboards.

Structure of Custom Query metadata

The metadata for a Custom Query only contains a description field, which defines the purpose or context of the query.

{
  "description": "string"
}

Structure of a Custom Query entity

Field	Type	Description
id	string	The unique identifier for the entity.
name	string	The name of the entity.
fields	Array<Field>	Each field of entity used on the query with its type.

{
    "id": "00000000-0000-0000-0000-0123456789AB",
    "name": "Employees",
    "fields": [
        {
            "name": "emp_no",
            "type": "number"
        },
        {
            "name": "first_name",
            "type": "string"
        },
        {
            "name": "last_name",
            "type": "string"
        },
        {
            "name": "dept_no",
            "type": "string"
        }
    ]
}

Structure of a Custom Query subquery metadata

Field	Type	Description
type	string	The type of visualization (e.g., table, chart).
width	number	The width of the visualization in grid units.
widget	object	Configuration specific to the visualization type.
widget.columns	Array<string>	The columns to display in the visualization.
widget.rows	Array<string>	The rows to display in the visualization.

{
    "type": "table",
    "width": 3,
    "widget": {
        "columns": [
            "column1",
            "column2"
        ],
        "rows": [
            "267686b4-bc9c-436d-a7fa-b8fe3bf9dfa2.field1",
            "267686b4-bc9c-436d-a7fa-b8fe3bf9dfa2.field2"
        ]
    }
}

Structure of a Custom Query subquery join

Field	Type	Description
type	string	The type of join (e.g., INNER, LEFT, RIGHT).
entity	string	The ID of the first entity in the join.
field	string	The field from the first entity to join on.
entity2	string	The ID of the second entity in the join.
field2	string	The field from the second entity to join on.

{
    "type": "INNER",
    "entity": "267686b4-bc9c-436d-a7fa-b8fe3bf9dfa2",
    "field": "dept_no",
    "entity2": "1ab82e53-574c-4c5b-b24c-fc9e25809cc2",
    "field2": "dept_no"
}

Structure of a Custom Query subquery match

Field	Type	Description
combinator	string	Logical operator to combine rules (e.g., “and”, “or”).
id	string	Unique identifier for the match condition.
not	boolean	Negates the entire match condition if true.
rules	Array<Rule>	Array of individual rules for filtering.
rules[].id	string	Unique identifier for the rule.
rules[].field	string	The field to apply the rule on.
rules[].operator	string	The operator for the rule (e.g., ”=”, ”>”, ”<”, “IN”).
rules[].value	string	The value to compare the field against.
rules[].valueSource	string	Source of the value for the rule.

{
    "combinator": "and",
    "id": "00000000-ABCD-0000-0000-0123456789AB",
    "not": false,
    "rules": [
        {
            "operator": "=",
            "value": "2000",
            "valueSource": "value",
            "id": "00000000-0000-CDEF-0000-0123456789AB",
            "field": "00000000-0000-0000-0000-0123456789AB.emp_no"
        }
    ]
}

Structure of a Custom Query subquery output

An array of strings representing the fields to be included in the output of the query.

[
  "00000000-0000-0000-0000-0123456789AB.emp_no",
  "00000000-0000-0000-0000-0123456789AA.dept_no"
]

Structure of a Custom Query variable

Field	Type	Description
name	string	The name of the variable.
label	string	A human-readable label for the variable.
type	string	The data type of the variable (e.g., string, number).

{
  "name": "month",
  "label": "Month",
  "type": "string"
}

Structure of a Custom Query

A complete Custom Query object combines all the above components into a single definition.

{
    "name": "Employee Departments",
    "metadata": { <CustomQueryMetadata> },
    "entities": [ { <CustomQueryEntity> } ],
    "queries": [
        {
            "name": "Example Query",
            "metadata": { <CustomQuerySubQueryMetadata> },
            "jsonData": {
                "entity": "00000000-0000-0000-0000-0123456789AB",
                "joins": [ { <CustomQuerySubQueryJoin> } ],
                "match": { <CustomQuerySubqueryMatch> },
                "output": [ <CustomQuerySubqueryOutput> ]
            }
        }
    ],
    "variables": [ {<CustomQueryVariable> } ]
}

Typical Use Cases

• Business Intelligence: Generating custom reports, aggregations, and metrics for management.
• Integration: Supplying tailored data to external systems or partners.
• User-driven exploration: Allowing power users to create their own views and filters.
• Auditing and compliance: Extracting specific records for review or export.

How are Custom Queries Executed?

Storage and Internal Representation

• Custom queries are stored in the custom_queries table, with fields for name, metadata, variables, json_data (the query definition), and sql_data (the generated SQL statement).
• The json_data field is a structured object describing the query logic, including:
  • entity: The main table or view to query.
  • match: Filter conditions, supporting variable substitution (e.g., {{month}}).
  • joins: Array of join definitions, specifying relationships between entities.
  • output: List of fields to return in the result set.

Query Creation and SQL Generation

• When a query is created or updated, the backend parses the JSON definition and uses a secure query builder (SecureQueryBuilder) to generate the corresponding SQL.
• The SQL is stored alongside the JSON for efficient execution.
• Variables are validated and mapped to SQL parameters to prevent injection and ensure correctness.

Runtime Execution Flow

1. Request Handling: The controller receives an execution request, parses parameters (limit, page, sorting, variables, subQuery indexes).
2. Validation: Ensures the query exists, variables are supported, and indexes are valid.
3. Variable Substitution: Replaces placeholders in the SQL with provided values, including system variables (e.g., current_user_id).
4. SQL Execution: Runs the query against the database, applying pagination and sorting as needed.
5. Response Formatting: Returns results, count, and pagination metadata in a standardized format.

Example: Advanced Query with Joins and Variables

{
    "id": "3",
    "name": "Custom Queries Example",
    "metadata": {
        "description": "Please do not modify"
    },
    "queries": [
        {
            "name": "Names and IDs",
            "metadata": {
                "type": "table",
                "width": 6,
                "widget": [
                    {
                        "columnLabel": "ID",
                        "sourceField": "0b049cb4-80fc-4677-85f5-574c21278503.id"
                    },
                    {
                        "columnLabel": "Name",
                        "sourceField": "0b049cb4-80fc-4677-85f5-574c21278503.name"
                    }
                ]
            },
            "jsonData": {
                "joins": [],
                "match": {
                    "id": "60359d66-a5b9-48fd-ab0d-da6240cd9117",
                    "rules": []
                },
                "entity": "0b049cb4-80fc-4677-85f5-574c21278503",
                "output": [
                    "0b049cb4-80fc-4677-85f5-574c21278503.id",
                    "0b049cb4-80fc-4677-85f5-574c21278503.name"
                ]
            }
        },
        {
            "name": "Table debounce",
            "metadata": {
                "type": "table",
                "width": 6,
                "widget": [
                    {
                        "columnLabel": "ID",
                        "sourceField": "0b049cb4-80fc-4677-85f5-574c21278503.id"
                    },
                    {
                        "columnLabel": "Name",
                        "sourceField": "0b049cb4-80fc-4677-85f5-574c21278503.name"
                    }
                ]
            },
            "jsonData": {
                "joins": [],
                "match": {
                    "id": "1971d1c0-5492-4fc1-b080-219e050295eb",
                    "rules": []
                },
                "entity": "0b049cb4-80fc-4677-85f5-574c21278503",
                "output": [
                    "0b049cb4-80fc-4677-85f5-574c21278503.id",
                    "0b049cb4-80fc-4677-85f5-574c21278503.name"
                ]
            }
        },
        {
            "name": "Pie 1.2",
            "metadata": {
                "type": "pie",
                "width": 6,
                "widget": {
                    "fieldToAggregate": "5dde2695-c956-4c3a-a41a-b28520203853.user_id"
                }
            },
            "jsonData": {
                "joins": [],
                "match": {
                    "id": "fe439a0c-fff7-4b6b-855f-307f782905e4",
                    "rules": []
                },
                "entity": "5dde2695-c956-4c3a-a41a-b28520203853",
                "output": [
                    "5dde2695-c956-4c3a-a41a-b28520203853.user_id"
                ]
            }
        },
        {
            "name": "Bar",
            "metadata": {
                "type": "bar",
                "width": 6,
                "widget": {
                    "fieldToAggregate": "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8.id"
                }
            },
            "jsonData": {
                "joins": [],
                "match": {
                    "id": "88f1d21d-be31-4a22-9480-6aec7c2c6278",
                    "rules": []
                },
                "entity": "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8",
                "output": [
                    "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8.id",
                    "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8.item",
                    "52f9db9b-1b23-47a1-bdd6-b7b47deb69a8.log"
                ]
            }
        }
    ],
    "variables": [
        {
            "name": "7b268b3107d74a8e89a74ff728e1de33name",
            "type": "string",
            "label": "Address Name test"
        }
    ]
}

Limitations and Constraints

Performance

• Very complex queries or those returning large result sets may impact system performance.
• Pagination is enforced to mitigate excessive data loads.

Security

• All endpoints require authentication and proper permissions (checked via middleware).
• Variable substitution is strictly validated; only defined variables are accepted.

Supported Query Types

• Only SELECT queries are supported; updates, inserts, or deletes are not allowed.
• Joins, filters, and output fields must reference valid entities and fields.
• Subqueries are supported via index selection, but must be within bounds.

Error Handling and Edge Cases

• Invalid Query Definition: Malformed JSON or missing required fields returns 400 Bad Request.
• Unsupported Variables: Providing variables not defined in the query results in a descriptive error.
• Nonexistent Query: Requests for missing queries return 404 Not Found.
• SubQuery Index Out of Range: Attempting to execute a subquery index that does not exist returns a BadRequestError.
• Database Errors: Any SQL or connection errors are wrapped and returned as BadQueryError.

Example Error Response

{
  "status": 400,
  "error": "Unsupported variables provided: [foo, bar]"
}

Advanced Usage Tips

• Use variables to make queries reusable and context-aware (e.g., filter by current user).
• Combine multiple joins for complex data relationships.
• Leverage output field selection to optimize result payloads.
• Use sorting and pagination parameters to control data flow and performance.

Endpoints for managing Custom Queries

Custom Queries are managed through a set of RESTful endpoints, each serving a distinct purpose in the query lifecycle:

Route	Method	Description
/api/v1/queries	GET	List all custom queries
/api/v1/queries	POST	Create a new custom query
/api/v1/queries/{id}	GET	Retrieve a single custom query by ID
/api/v1/queries/{id}	PUT	Update an existing custom query
/api/v1/queries/{id}	DELETE	Delete a custom query
/api/v1/queries/{id}/execute	GET	Execute a stored custom query

Create a Custom Query

POST /api/v1/queries

<CustomQuery>

Response example:

{
  "status": 201,
    "error": false,
  "entityName": "string",
    "message": "Created successfully",
    "data": {
        "id": "1",
        "queries": [...],
        "variables": [...],
        "name": "Employee Departments",
        "metadata": { "description": "Employee and Department query" }
    }
}

Update a Custom Query

Parameter	Type	Description
id	string	The ID of the custom query to be deleted.

Request:

PUT /api/v1/queries/:id

<CustomQuery>

Response example:

{
  "status": 200,
  "error": false,
  "message": "string",
  "entityName": "string",
  "data": { <CustomQuery> }
}

List All Custom Queries

Parameter	Type	Description
limit	number	(Optional) Number of records to return per page. Default is 20.
page	number	(Optional) Page number to retrieve. Default is 1.
filter	Object	(Optional) JSON object to filter queries by queries.
ids	Array<string>	(Optional) Array of specific query IDs to retrieve.

GET /api/v1/queries?limit=:limit&page=:page&filter=:filter[Object]&ids[]=:id[]

GET /api/v1/queries?limit=20&page=1&filter={"name":"Employee%20Departments"}&ids[]=1&ids[]=2&ids[]=3

Response example:

{
  "status": 200,
  "error": false,
  "message": "string",
  "entityName": "string",
  "data": [
    {
      "id": "1",
      "queries": [...],
      "variables": [...],
      "name": "Employee Departments",
      "metadata": { "description": "Employee and Department query" }
    }
  ]
}

Delete a Custom Query

Parameter	Type	Description
id	string	The ID of the custom query to be deleted.

DELETE /api/v1/queries/:id

Response:

{
  "status": 200,
  "error": false,
  "message": "string",
  "data": [ { <CustomQuery> } ]
}

Execute a Custom Query

Parameter	Type	Description
id	string	The ID of the custom query to execute.
limit	number	(Optional) Number of records to return per page. Default is 10.
page	number	(Optional) Page number to retrieve. Default is 1.
sorting	string	(Optional) Field to sort by (e.g., fieldName)
subQuery	number	(Optional) Index of the subquery to execute. Default is 0 (first subquery).
variables	Object	(Optional) JSON object mapping variable names to their values, e.g., {"month":"2025-09","region":"West"}.

GET /api/v1/queries/:id/execute?limit=:limit&page=:page&sorting=:sorting&subQuery=:subQuery&variables=:variables[Object]

GET /api/v1/queries/42/execute?limit=10&page=1&variables={"month":"2025-09","region":"West"}

Response:

{
    "status": 200,
    "error": false,
    "errors": [ { } ],
    "data": [ [...] ],
    "message": "string",
    "pagination": { <Pagination> }
}