Transactions & queries
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.
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.
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}"
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
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.