Skip to main content

TSQL Templates User Guide

TSQL templates are an extension of our SQL query system that allows for the creation of reusable, parameterized queries. These templates enable users to create flexible monitoring agents that can be customized through a user interface without requiring direct SQL modifications.

Template Structure​

A TSQL template consists of three main parts:

  1. Metadata comments
  2. Network configuration
  3. Query body

The query should also be marked as a template from the setting menu on the top row

Default Network

Metadata Comments​

The metadata section is defined at the top of the query file using YAML-formatted comments. This section provides information about the template and defines its parameters.

/*
    Description: 
        A clear description of what the query does.
    Parameters:
        - name: parameterName
          type: parameterType
          description: 
              Description of what this parameter does.
        [Optional fields]:
          alternativeTo: anotherParameter
          alternativeButtonText: Text shown for alternative selection
*/

Parameter Types​

The following parameter types are supported:

Single Value Types:

  • string: A text string
  • number: A numeric value
  • address: An Ethereum address
  • address_with_signature: An Ethereum address with an associated function signature
  • address_with_log: An Ethereum address with an associated event log
  • function: A function selector (e.g., '0x23b872dd')
  • log: An event log topic (e.g., '0x8c5be1e5...')
  • eth_call_input: Input data for eth_call operations
  • protocol: A protocol name from our supported protocols list

Array Types (both notations are supported):

  • string[] or array<string>: An array of strings
  • number[] or array<number>: An array of numbers
  • address[] or array<address>: An array of Ethereum addresses
  • function[] or array<function>: An array of function selectors
  • log[] or array<log>: An array of event log topics
  • protocol[] or array<protocol>: An array of protocol names

Network Configuration​

The network configuration section uses the net_config function to define default values for parameters across different networks. This section must be placed after the metadata comments and before the query body.

You can configure multiple networks by adding them to the net_config:

{{
    net_config(
        ethereum={
            "parameter1": defaultValue1,
            "parameter2": defaultValue2
        },
        polygon={
            "parameter1": defaultValue1,
            "parameter2": defaultValue2
        },
        binance={
            "parameter1": defaultValue1,
            "parameter2": defaultValue2
        },
        arbitrum={
            "parameter1": defaultValue1,
            "parameter2": defaultValue2
        }
    )
}}

To exclude a network, simply remove it from the net_config. The networks that will be queried are specified in the networks() function call:

{% for network in networks('ethereum', 'polygon', 'binance', 'arbitrum') %}

Query Body​

The query body contains the actual TSQL query that will be executed. Parameters defined in the metadata can be accessed using the network.param() function.

Parameter Usage​

Parameters defined in the metadata can be accessed within the query using the network.param() function. Here are some common patterns:

Single Value Parameters​

select *
from {{ network.transaction_detail() }}
where to_a = {{ network.param("address") | to_address }}

Array Parameters​

Arrays can be handled in multiple ways depending on your needs:

  1. Using VALUES clause:
with addresses(address) as (
    values
        {% for addr in network.param("addresses") %}
        ({{ addr | to_address }}){% if not loop.last %}, {% endif %}
        {% endfor %}
)
  1. Using ARRAY constructor:
ARRAY[
    {% for address in network.param("addresses") %}
    ({{ address | to_address }})
    {% if not loop.last %}, {% endif %}
    {% endfor %}
]

Protocol Parameters​

When using a protocol parameter, you can use it with the contracts gadget:

select address
from {{ network.contracts(protocols=[network.param("protocol")]) }}

Alternative Parameters​

Templates can define alternative parameters that provide different ways to achieve the same goal. For example, you might want to monitor either a specific protocol or a set of contract addresses.

/*
    Parameters:
        - name: protocol
          type: protocol
          description: The protocol to monitor
          alternativeTo: contracts
          alternativeButtonText: Or select specific contracts
        - name: contracts
          type: array<address>
          description: The specific contracts to monitor
          alternativeTo: protocol
          alternativeButtonText: Or select a protocol
*/

In the query, handle alternative parameters using conditional logic:

{% if not network.param("protocol") %}
    -- Use specific contracts
    select * from unnest({{ network.param("contracts") }}) as contract
{% else %}
    -- Use protocol contracts
    select address as contract
    from {{ network.contracts(protocols=[network.param("protocol")]) }}
{% endif %}

Complete Examples​

Example 1: Basic Transaction Monitoring​

This example monitors transactions between specific addresses and contracts:

/*
    Description: 
        This query gathers the transactions that happened when a blacklisted address called one of the given contracts.
    Parameters:
        - name: addresses
          type: array<address>
          description: 
              The blacklisted addresses.
        - name: protocol
          type: protocol
          description: 
              The protocol that you want to monitor
          alternativeTo: contracts
          alternativeButtonText: Or select a set of addresses
        - name: contracts
          type: array<address>
          description: 
              The addresses that you want to monitor
          alternativeTo: protocol
          alternativeButtonText: Or select a protocol
 
    Usage:
        Replace <network1>, <protocol>, <address1> and <address2> with your desired ones.
*/

{{
    net_config(
        ethereum={
            "addresses": [],
            "contracts": [],
            "protocol": ""
        }
    )
}}

{% for network in networks('ethereum') %}

    select *
    from
        (
            with
                addresses(address) as (
                    values
                        {% for address in network.param("addresses") %}
                        ({{ address | to_address }}){% if not loop.last %}, {% endif %}
                        {% endfor %}
                ),
                contracts(contract) as (
                    {% if not network.param("protocol") %}
                        values
                            {% for address in network.param("contracts") %}
                            ({{ address | to_address }}){% if not loop.last %}, {% endif %}
                            {% endfor %}
                    {% else %}
                        select address
                        from
                            {{ network.contracts(protocols=[network.param("protocol")]) }} addresses
                    {% endif %}
                )
                 

            select {{network}}.get_chain_id() as chain_id, td.from_a as sender, td.to_a as receiver, {{network}}.tx_hash(td.block_number, td.tx_index) as tx_hash
            from {{ network.transaction_detail() }} td
            where
                td.to_a in (select contract from contracts)
                and td.from_a in (select address from addresses)
        ) _

    {% if not loop.last %}
    union all
    {% endif %}
{% endfor %}

Default Network

Best Practices​

  1. Clear Descriptions: Provide detailed descriptions for both the template and its parameters in the metadata section.

  2. Parameter Naming: Use descriptive names for parameters that clearly indicate their purpose.

  3. Default Values: Always provide sensible default values in the net_config section.

  4. Alternative Parameters: When offering alternative ways to achieve the same goal, use clear alternativeButtonText to explain the options.

  5. Network Support: Unless specifically targeting a single network, make your templates work across multiple networks using the networks() function.

  6. Example Values: Include example values in the metadata comments to help users understand the expected input format:

    /*
        Example values: 
            <network1>: ethereum
            <address1>: '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
            <function1>: '0x23b872dd'
    */

By following these guidelines and patterns, you can create robust and reusable TSQL templates that provide powerful monitoring capabilities while remaining easy to customize through the user interface. ,