Skip to content
Thermite SCADA
Docs · Build

Transactions & queries

View as Markdown

Two ways the gateway talks SQL beyond the historian: transaction groups write rows when something happens, and named queries read rows on demand. Both are TOML files in the project, executing against the PostgreSQL connections declared in server.toml.

transactions/

Log tag values on a trigger.

One [transaction_group] per file. A write group inserts the mapped tag values when its trigger fires. Create the destination table first; groups and queries load from production snapshots.

# projects/press-line/transactions/cycle-log.toml
[transaction_group]
name = "Press Cycle Log"
type = "write"
database = "historian"      # a [[databases]] name
enabled = true

[transaction_group.trigger]
mode = "tag_change"
tag = "press/status/Cycle_Complete"
trigger_on = "rising_edge"

[transaction_group.operation]
mode = "insert"
table = "press_cycle_log"

[[transaction_group.mappings]]
tag = "press/data/Peak_Pressure"
column = "peak_pressure"
type = "f64"

[[transaction_group.mappings]]
tag = "press/data/Recipe_Id"
column = "recipe_id"
type = "i64"

[[transaction_group.mappings]]
column = "recorded_at"
value = "now()"       # allowed static expression
type = "timestamp"
  • • A mapping samples a tag or uses an allowed static value: now(), current_timestamp, true, false, or null.
  • • The project's [project.scope].databases allow-list controls which connections its groups may use.
  • • The transactions feature flag (default on) gates the whole subsystem.
type = "read"

Read groups: SQL rows back into tags.

A read group copies selected SQL columns into gateway tag values. Handshake tags expose execution state to screens and other gateway consumers. These updates do not write PLC memory; polling can overwrite device-backed values.

# projects/press-line/transactions/recipe-load.toml
[transaction_group]
name = "Recipe Load"
type = "read"
database = "historian"

[transaction_group.trigger]
mode = "tag_change"
tag = "press/controls/Load_Recipe_Trigger"
trigger_on = "rising_edge"

[transaction_group.handshake]
in_progress_tag = "press/status/Recipe_Loading"
complete_tag = "press/status/Recipe_Load_Complete"
error_tag = "press/status/Recipe_Load_Error"

[[transaction_group.mappings]]
tag = "press/recipe/Target_Pressure"   # column value lands in this tag
column = "target_pressure"
type = "f64"

[transaction_group.query]
table = "recipes"
where = "recipe_id = {press/recipe/Selected_Recipe_ID}"
queries/

Named, parameterized reads.

One [query] per file. Parameters use :name placeholders. Parameter values are bound to SQL rather than interpolated into query text. Use queries for recipe lists and shift summaries.

# projects/press-line/queries/active-recipes.toml
[query]
name = "active-recipes"
database = "historian"
cache_ttl_sec = 60   # accepted in the format; not used for result caching

[query.sql]
text = "SELECT recipe_id, recipe_name FROM recipes WHERE active = true AND line = :line"

[[query.parameters]]
name = "line"
type = "string"     # string | i64 | f64 | bool
required = true
Execution

Running them.

Queries execute over the gateway's admin API. The namespace is global today — query names are bare names across the site, and the endpoints live on the gateway port rather than under a project mount — so keep names unique across projects. With authentication enabled, use an administrator session and its CSRF token for execution requests.

# list queries
$ curl http://localhost:8400/api/queries

# execute with parameters
$ curl -X POST http://localhost:8400/api/queries/active-recipes/execute \
    -H "Content-Type: application/json" \
    -d '{"parameters": {"line": "A"}}'
{
  "query": "active-recipes",
  "row_count": 2,
  "rows": [
    { "recipe_id": 12, "recipe_name": "Deep Draw 3mm" },
    { "recipe_id": 14, "recipe_name": "Shallow Form 1mm" }
  ]
}

# transaction group status
$ curl http://localhost:8400/api/transactions

(cache_ttl_sec is part of the format but is not used for result caching. Each execution queries the database.) Endpoint details: HTTP & WebSocket API.