Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Data stores

A data object (<bpmn:dataObject>) is a per-instance variable — private, transient, born and dying with one instance. A data store (<bpmn:dataStore> / <bpmn:dataStoreReference>) is the opposite axis: durable, keyed state that outlives and is shared across instances — an account ledger, a customer index, the covered/uncovered flags behind a compliance route.

Status note. Data stores are exercised end to end by the money-transfer example — a real sql-backed ledger under transactional, pessimistic-lock control. (Path coverage rides a data store of an unusual kind: its marks are persisted in a store the package declares — the reserved coverage name, whose connection picks the database they land in — but the engine owns that store’s schema and applies it itself, so it is neither a key→value business store nor one you write DDL for. See Coverage: declared routes as the compliance signal.) Some of the wider surface described in the design record — a file-backed store, an optimistic-concurrency compare-and-set on every provider — is still in motion; treat anything below not shown against the money-transfer example as directionally accurate rather than a finished contract, and check rust/crates/sutra-datastore for the current provider set before depending on specifics.

Choosing a store shape

A business store has two shapes. The choice between them is narrower than the volume of mechanism the rest of this chapter spends on the second one suggests, so it is worth settling first. (The reserved coverage store is neither — the engine owns its schema and applies it, so a package writes no coverage DDL and makes no shape choice at all; see Coverage.)

Key/value is the general case, and it is always sufficient. By default a store is a key→value store: one row per key, the value serialized whole as JSON text. Any structure fits it — nested, repeated, open-ended, a different shape next release — because nothing about the value’s shape is written down anywhere for it to disagree with. The table behind it is one fixed generic shape (store name, key, value, rev, updated_at) shared by every key/value store on that connection, and it does not change when your data does. From a standards point of view this is the only shape that carries every scenario, which is why it is the default and stays the default. A store with no structure: block is this store, and none of the projection machinery below applies to it.

Projection is an integration affordance, not an upgrade. Declaring a structure: does not make a store more correct, more durable or more capable, and it is not a tier you graduate to. It exists for one reason: so that everything else that already speaks SQL can read the data. A BI tool, a dashboard, a nightly reporting query, an analyst with read access to that database — none of them know what Sutra is, and against a key/value store the most any of them can see is an opaque document to parse. Against a projected table they see balance and opened_at as columns, with types. That is one more way to ease integration, not a better kind of store.

The trade, stated plainly. Projection costs two things and buys one:

  • It costs the flat-only constraint. The declared type must be entirely scalar, and a type that later grows a nested or repeated child stops projecting at all — see Flat only.
  • It costs ownership of the DDL and its evolution. You write the CREATE TABLE, and every change to the declared type is a migration you author and sequence. A key/value store needs a table too, but it is the same generic one every time — written once, never revisited. A projected table is your record’s own shape, so it has to move whenever that shape moves.
  • It buys direct SQL access to the data, for anything that speaks SQL, without going through the engine.

If nothing outside the process ever reads the store, that affordance buys nothing, and key/value is the right answer.

flowchart TD
    W["one write through q:store"]

    subgraph KV["Key/value — the default"]
        KROW[("a shared generic table<br/>name · key · value · rev")]
    end

    subgraph PJ["Projected — a structure: block"]
        PROW[("this store's own table<br/>one typed column per field")]
    end

    W --> KROW
    W --> PROW
    KROW -.->|"an opaque document<br/>to parse"| BI["any SQL tool that has<br/>never heard of Sutra"]
    PROW -->|"balance, opened_at:<br/>columns, with types"| BI

Both shapes hold the same record and answer the same <q:store> access with the same locking and rev semantics; the only thing projection moves is the arrow on the right — an opaque document versus columns a reader can use directly, bought with the flat-only constraint and ownership of the DDL.

The shape is a property of the store, not of the flow — within limits worth knowing. A store’s shape is declared in datastores.yaml and nowhere else. The BPMN is unchanged either way: the same <bpmn:dataStoreReference>, the same <q:store> with the same key / field / forUpdate / expect attributes, resolved by store name through the same registry. Pessimistic locking, <bpmn:transaction> enlistment and rollback, delete, and the rev bookkeeping behind expect="unchanged" — seeded at 1, bumped on every write, a conflict when the revision moved — carry the same semantics under both shapes. Adding or removing a structure: block is a package-level change, not a process-level one.

What does change is what the store accepts and what it hands back — and that is not nothing:

  • A projected store refuses a write carrying a field the structure does not declare (UNDECLARED_FIELD); a key/value store simply stores it. This is the difference most likely to bite, because a process that has quietly been writing a wider record than the declared type works against key/value and fails against projection — and nothing catches it beforehand: neither sutra lint nor the executor checks a <q:store field="…"> name against the declared structure, so the first evidence is the refused write.
  • It refuses a value that isn’t a record at all — a scalar, an array, an explicit null (VALUE_NOT_A_RECORD). A key/value store holds any of those happily.
  • It refuses every operation until the live table satisfies the declaration (PROJECTION_UNSATISFIABLE, checked on first use). A key/value store has no declaration to drift from, so it has no such failure mode.
  • A field-narrowed write is a read-modify-write of the whole value under both shapes, so one that creates a key writes a record containing only that field. On a key/value store that is simply a one-field document; on a projected store every other declared column is bound NULL, so unless they are all nullable or defaulted in your DDL that insert runs straight into your own constraints. Make the first write of a key a full record rather than a narrowed one.
  • Values that pass through a typed column come back in the column’s canonical form rather than the text that was written: an explicit null and an absent optional field become the same thing on read, and a dateTime comes back as your dialect’s rendering of that instant. The exact scope of what is and isn’t preserved is in A worked example.

None of those change how a process addresses the store; several of them change whether a given write succeeds. They are the checklist for a shape change, not a reason to avoid one.

Column names become a published interface. The moment a dashboard reads that table, its column names are a contract with someone who has never seen your schema — a report knows account_balance, not the XSD element it was derived from. Column names are derived by convention from the declared field names, so renaming a field renames a column, and silently breaks that report. That is what the columns: override exists for: pin the physical name, and the schema can be renamed underneath it while the published surface holds still. It is worth setting before you need it rather than after someone’s query starts returning nothing — the naming rules and the override syntax are under Column names.

Declaring a store

One flat datastores.yaml per package (see Deployment packages). The module owns its store — the connection is declared here, resolved from environment references, and its migrations ship inside the package:

# examples/money-transfer/.../datastores.yaml (abridged)
datastores:
  - name: accounts
    type: sql
    sql:
      url-ref: env:ACCOUNTS_DB_URL
      username-ref: env:ACCOUNTS_DB_USER
      password-ref: env:ACCOUNTS_DB_PASSWORD
      migrations: migrations/accounts     # V<n>__*.sql, each run once per database
    dataClass: financial                  # sensitivity tag — redacted in audit/logs/traces

A store’s migrations run the first time the store is used, under a database lock, so replicas never run them at the same moment. The lock is one per database (per server on MySQL and MariaDB), not one per store, because several stores often share one table and the same CREATE TABLE IF NOT EXISTS. Before 0.6.12 the lock was per store, and two stores sharing a table that were first used at the same moment could fail that first request with HTTP 500 (pg_type_typname_nsp_index); the next request succeeded.

Migrations run once

The engine records every script it applies in a history table, sutra_store_migrations, created in the store’s own database the first time a store there migrates. The table has one row per store and version: store_name, version, script (the file name), checksum (the SHA-256 of the script as it ran) and applied_at. On first use, under the lock described above, each script is checked against that table:

  • Recorded with the same checksum: skipped. A restart, a second replica or a redeploy of the same package runs nothing.
  • Not recorded: it runs, then its row is written. On PostgreSQL and SQL Server the script and its row commit in one transaction, so a script that fails leaves neither its changes nor a row behind. MySQL and MariaDB commit DDL implicitly, so there the script runs first and its row is written after it succeeds. Either way, a failed script records nothing and fails the store’s first use; the next use tries again. Because of that transaction, a statement PostgreSQL refuses inside one (CREATE INDEX CONCURRENTLY, for example) cannot be in a migration script; create such an index outside the package’s migrations.
  • Recorded with a different checksum: the store refuses to start with SUTRA.RUNTIME.DATASTORE.MIGRATION_CHECKSUM, naming the store, the file, the version and both checksums. A script that has been applied must not be edited. Restore it, and put the change in a new script with the next version number.

Rows for scripts the package no longer ships are ignored.

Versions and order. V<n>__<description>.sql has version <n> with leading zeros dropped, so V001__accounts.sql and V1__accounts.sql are the same version, and two scripts of one store with the same version are refused (SUTRA.CONFIG.DATASTORE.MIGRATION_VERSION_DUPLICATE). When every script is named this way they run in numeric order, so V2 runs before V10. A script with any other name still works: its version is its file name without .sql, and a folder that mixes the two styles runs in plain file-name order. sutra lint requires the V<n>__ form.

Databases migrated before the history table existed. Releases before 0.6.13 re-ran every script on every boot and kept no record, which is why those scripts had to be idempotent. Nothing special is needed to move such a database over: it has no rows for the store, so each script runs one more time, harmlessly, and is recorded. From then on it runs once.

Placeholders. A script can use five placeholders, replaced before it runs and before its checksum is taken:

PlaceholderReplaced with
${package.tenant}the package’s tenant label
${package.module}the package’s module label
${package.version}the package’s version label
${store.name}the store’s name in datastores.yaml
${store.table}the table the store writes: its sql.table (or its name) when it declares a structure:, otherwise data_store
-- migrations/accounts/V002__seed.sql
INSERT INTO data_store (store_name, store_key, store_value)
VALUES ('${store.name}', 'settings', '{"owner":"${package.tenant}"}');

Any other ${…} is an error, and so is a ${ with no } on the same line. Both are reported by sutra lint and again when the engine loads the package (SUTRA.CONFIG.DATASTORE.MIGRATION_PLACEHOLDER, naming the file, the line and the placeholder). Write $${ to get a literal ${. A lone $ or $$, such as PostgreSQL dollar quoting, is left alone. Because ${package.version} is part of the checksummed text, a script that uses it counts as edited when the version label changes. Use it only in scripts you add for a specific release.

The engine’s own datasource (instances, outbox, lease, audit, inbox) is never a store’s backing connection — a sql store always resolves its own connection, and fails closed if it declares none. This is what makes a generic engine image self-sufficient against any number of modules that each bring their own store: no baked default datasource, no baked migrations.

A store declared this way is the key/value shape: one row per key, the value serialized whole as JSON text, opaque to the database. Adding a structure: block projects a flat record onto real typed columns instead — see Typed columns: declaring the structure a store holds below.

Referencing a store from BPMN

<bpmn:dataStore id="accountsStore" name="accounts"/>   <!-- definitions scope -->

<bpmn:dataStoreReference id="dsrFrom" name="accounts" dataStoreRef="accountsStore">
  <bpmn:extensionElements>
    <q:store key="payload.fromId" forUpdate="true"/>
  </bpmn:extensionElements>
</bpmn:dataStoreReference>

<q:store> (see The q: namespace) is what turns a bare reference into a keyed access: key is a FEEL expression producing the row key, forUpdate="true" takes a pessimistic row lock so concurrent writers on the same key serialize, field narrows a write to one field of a stored map value, and expect="unchanged" is an optimistic compare-and-set alternative to a lock. expect="absent", delete and query are covered in Claiming a key once, Deleting a row and Querying rows by key. A data task — a serviceTask with no implementation — reads and writes through its data associations exactly like it would a data object, except the source/target is a store reference instead of a variable.

Typed columns: declaring the structure a store holds

An opaque store hands the database a string. Nothing inside the value is queryable, indexable or typed, and the only way to read a field is to fetch the whole document by key and pick it apart in the process. For a record whose fields are all scalars that is a blob tax on data that could perfectly well be ordinary columns.

So a store may declare the structure it stores, not just its key:

datastores:
  - name: accounts
    type: sql
    structure:
      schema: urn:accounts        # a schemas/<folder> codec of this package
      type: AccountRecord         # a complexType, a root element, or a JSON Schema definition
    sql:
      url-ref: env:ACCOUNTS_DB_URL
      migrations: migrations/accounts

schema + type resolve through the schemas the package already compiles for its codecs (the path-derived URN rule is in Deployment packages), so there is no second schema source to keep in step and no shape inferred from observed data — the declaration is the contract.

A store with no structure: block behaves exactly as it always did. Same opaque key→JSON row, same providers, same <q:store> semantics, and none of the diagnostics below ever fire for it. Declaring a structure is opt-in per store; a record too nested to project simply doesn’t declare one.

Flat only

The declared type must be entirely scalar. Every child is classified in declared order:

Declared shapeBecomes
Scalar leaf, at most once (a simple-type element or an attribute)a column
Scalar leaf, minOccurs="0"a nullable column
Scalar element inside a choicea nullable column (at most one branch is populated)
A complex child — a nested objectlint error
Anything that may occur more than oncelint error
Open content (xs:any, JSON Schema additionalProperties: true)lint error

The table is the declared fields. There is no residue column for the parts that didn’t fit, no column-versus-JSON merge on read, and no partial projection. That is the whole simplification, and it is what makes a projected row readable by anything that speaks SQL without knowing Sutra exists.

A type that can’t be expressed that way is rejected at package time with SUTRA.CONFIG.DATASTORE.STRUCTURE_NOT_FLAT, naming the offending child:

data store 'accounts': declared structure type 'AccountRecord' is not flat: 'owner' is not a
scalar leaf (nested content). Flatten the type, or remove the 'structure' block and keep the
opaque store.

Those two remedies are the whole menu, deliberately:

  • Flatten the type — pull the nested part up into scalar siblings (owner/name → ownerName), or move it to a store of its own keyed by the same business id.
  • Drop the structure: block — the store goes back to being the opaque key→JSON store it was, which is a perfectly good answer for a record that genuinely isn’t flat.

There is nothing in between, because anything in between would be a silent partial projection — some fields queryable, some hidden in a blob, and a merge rule nobody can see from the declaration.

Because the structure is closed and there is nowhere for an unexpected field to go, a write carrying a field the structure does not declare is a fail-closed runtime error (SUTRA.RUNTIME.DATASTORE.UNDECLARED_FIELD) naming the field. Silently dropping it is the one outcome this design refuses. Writing anything that isn’t a record at all — a scalar, an array, an explicit null for the whole value — is refused the same way, as SUTRA.RUNTIME.DATASTORE.VALUE_NOT_A_RECORD: a projected row is its declared fields, so there is nowhere for a bare value to land either.

Control columns

A projected table is never only the declared fields. Three columns belong to the engine, on every projected table, whether or not your own type ever mentions them:

ColumnRole
store_keythe store key — whatever the store’s <q:store key="…"> expression produces — and the table’s PRIMARY KEY
revthe optimistic-concurrency revision, bumped on every write; what put_if_revision keys on
updated_atthe write timestamp

There is no store_name column — a projected table is one store, unlike the opaque table that multiplexes many stores by name. The runtime binds and maintains all three itself, so a declared field may not claim one of their names: that’s a naming collision (SUTRA.CONFIG.DATASTORE.COLUMN_NAME_INVALID), resolved the same way as any other one — map the field to a different column under columns:. Your own migration has to create all three alongside your declared columns, or sutra lint raises COLUMN_MISSING naming whichever is absent — a projected table missing store_key, rev or updated_at cannot be served no matter how correct its declared columns are. The worked example below shows all three in the DDL.

Column names

A declared field’s column name is derived by convention: lowerCamel → snake_case, ASCII-folded, runs of anything else collapsed to a single _.

Declared fieldColumn
accountIdaccount_id
openedAtopened_at
iso4217Codeiso4217_code
IBANCodeiban_code

The convention is not always enough, and when it isn’t, sutra lint says so (SUTRA.CONFIG.DATASTORE.COLUMN_NAME_INVALID) rather than guessing: two fields folding to the same column, a fold that lands on a SQL reserved word, a name over the 63-character identifier cap (PostgreSQL’s — the narrowest of the three shipped dialects, so a name that clears it is portable), or a fold that isn’t a usable identifier at all. Every one of them is resolved by naming the column yourself:

    structure:
      schema: urn:accounts
      type: AccountRecord
      columns:
        openedAt: opened_on       # the DDL already calls it this
        order:    order_seq       # `order` is reserved

The mapping exists for two reasons. The first is that you write the DDL and may already have column names you can’t change. The second is that it pins the physical name against a later schema rename — which matters as soon as anything outside the engine reads the table, because at that point the column names are a published interface and the convention is no longer yours alone to change (see Choosing a store shape). The mapping is checked by the same rules — an override that is itself reserved or over-length is still an error, and an override naming a field the type doesn’t declare is an error rather than a silent no-op.

You own the DDL; the engine verifies it

The engine generates no DDL for a projected store and owns no part of its table shape. That table is created by the store’s own migrations, in your own dialect, shipped inside the package under migrations/<store>/ (see Deployment packages).

sutra lint derives the effective table shape statically — from the package’s own migrations/<store>/V*.sql, replayed in version order (CREATE TABLE plus ALTER TABLE ADD/ALTER/DROP COLUMN), with no database connection and no credentials — and compares it against the projection. The table it compares against is the one named after the store — or, if the migrations create exactly one table, that one — or the one you name explicitly:

    sql:
      url-ref: env:ACCOUNTS_DB_URL
      migrations: migrations/accounts
      table: account_ledger       # when the table isn't named after the store

What it checks the column types against is this mapping. It is advisory — the shape lint expects to find, not DDL anything emits:

DeclaredPostgreSQLMySQL / MariaDBSQL Server
xs:string + maxLength nVARCHAR(n)VARCHAR(n)NVARCHAR(n)
xs:string, unboundedTEXTLONGTEXTNVARCHAR(MAX)
xs:decimal + totalDigits p / fractionDigits sNUMERIC(p,s)DECIMAL(p,s)DECIMAL(p,s)
xs:int / xs:long / xs:shortINTEGER / BIGINT / SMALLINTsameINT / BIGINT / SMALLINT
xs:booleanBOOLEANTINYINT(1)BIT
xs:date / xs:dateTime / xs:timeDATE / TIMESTAMPTZ / TIMEDATE / DATETIME / TIMEDATE / DATETIME2 / TIME
xs:base64BinaryBYTEABLOBVARBINARY(MAX)
an enumeration facetthe base type (a CHECK or a lookup table is your choice)samesame

Two declared shapes this release refuses outright — not silently degraded, not weakly verified — and one honest limit on verification alone:

  • xs:base64Binary is refused, not projected. The advisory mapping above (BYTEA / BLOB / VARBINARY(MAX)) is what the column would need to be; the runtime doesn’t marshal binary values through a projected column yet. A store declaring one is refused when the engine resolves it — at deploy time, not at lint time — naming the field:

    data store 'accounts' cannot project structure type 'AccountRecord': field 'attachment' is
    declared 'base64Binary', which a projected column does not carry in this release — declare it
    as a string, or remove the 'structure' block and keep the opaque store.
    

    Lint doesn’t catch this on its own: its DDL parser treats BYTEA/BLOB/VARBINARY as an ordinary column type and matches it happily, so a package can lint clean and still refuse to deploy. Don’t read lint’s silence as proof a binary field works.

  • A JSON-Schema-declared structure is refused, not weakly verified. Only a package schemas/<folder> XSD codec carries the enumerable, facet-bearing field list a typed column is checked against — a JSON Schema definition has no such list at this phase. sutra lint reports that honestly as a DDL_UNVERIFIABLE warning (unprovable, not necessarily wrong) — but the engine that actually resolves the store at deploy time draws a harder line and refuses to load the deployment:

    data store 'accounts' declares structure schema 'urn:accounts', which this package provides no
    XSD codec for. A projected store's type must be declared by a schemas/<folder> XSD codec of the
    same package — only XSD carries the declared facets a typed column is checked against.
    

    So a lint-clean package with a JSON-Schema structure: still will not deploy. Declare the structure against an XSD if you want the store to exist at all, not just to have its column types checked.

  • DDL outside the parsed subset degrades to a warning, never to a false error. A migration that creates the table in a PL/pgSQL block, a T-SQL procedural guard, a table created outside the package entirely — lint reports that it could not derive the shape and raises no column diagnostic for that store. A linter that cries wolf on legitimate DDL is worse than no linter, because authors learn to ignore it. See Troubleshooting for how to read that warning.

Lint proves the package-time case. The deployed table is checked separately, but not on every operation: first use of a projected store rides the same once-per-store-instance gate that runs its migrations (the one already serialized across replicas by an advisory lock), and there the provider reads the live table’s actual columns and fails the store closed if the projection isn’t satisfiable, naming every offending column at once. That is the defence against a table that drifted from the package’s own migrations — a hand-applied ALTER — and it fails loudly because a silent partial write is far worse than a refusal. It costs one catalog round-trip the first time the store is used, and nothing on any operation after that.

flowchart TD
    L["<b>lint</b> — package time<br/>replays your V*.sql, no database"]
    L -.->|"COLUMN_MISSING<br/>KEY_MISMATCH"| LE["package rejected"]
    L --> D["<b>deploy</b> — the store resolves"]
    D -.->|"a field no codec<br/>of this package declares"| DE["deployment refused"]
    D --> F["<b>first use</b> — read the<br/>live table's columns, once"]
    F -.->|"the table does not<br/>satisfy the declaration"| FE["PROJECTION_UNSATISFIABLE<br/>fails closed, names every column"]
    F --> R["<b>every operation after</b><br/>no further catalog round-trip"]
    R -.->|"undeclared field,<br/>or not a record"| RE["UNDECLARED_FIELD<br/>VALUE_NOT_A_RECORD"]

Each gate sees something the one before it could not — lint has the migrations but no database, deploy has the resolved store but no table, first use has the live catalog — which is why a package can pass lint (or draw only a warning from it) and still refuse to deploy.

A worked example

A flat record, declared in the package’s own codec schema (schemas/accounts/accounts.xsd, the codec urn:accounts):

<xs:element name="AccountRecord">
  <xs:complexType>
    <xs:sequence>
      <xs:element name="accountId" type="AccountId"/>
      <xs:element name="balance"   type="Money"/>
      <xs:element name="openedAt"  type="xs:date"/>
      <xs:element name="active"    type="xs:boolean"/>
      <xs:element name="note"      type="Note" minOccurs="0"/>
    </xs:sequence>
  </xs:complexType>
</xs:element>

<xs:simpleType name="AccountId">
  <xs:restriction base="xs:string"><xs:maxLength value="35"/></xs:restriction>
</xs:simpleType>
<xs:simpleType name="Money">
  <xs:restriction base="xs:decimal">
    <xs:totalDigits value="18"/><xs:fractionDigits value="2"/>
  </xs:restriction>
</xs:simpleType>
<xs:simpleType name="Note">
  <xs:restriction base="xs:string"><xs:maxLength value="140"/></xs:restriction>
</xs:simpleType>

Its table, written by hand, in the dialect the store actually runs on (migrations/accounts/V001__accounts.sql) — store_key, rev and updated_at are the engine’s control columns, alongside one column per declared field:

CREATE TABLE IF NOT EXISTS accounts (
  store_key   VARCHAR(512)  NOT NULL,
  account_id  VARCHAR(35)   NOT NULL,
  balance     NUMERIC(18,2) NOT NULL DEFAULT 0,
  opened_at   DATE          NOT NULL,
  active      BOOLEAN       NOT NULL DEFAULT TRUE,
  note        VARCHAR(140),              -- the optional field's column admits NULL
  rev         BIGINT        NOT NULL DEFAULT 1,
  updated_at  TIMESTAMPTZ   NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (store_key)                -- the store key, never a declared field
);

CREATE INDEX IF NOT EXISTS accounts_opened_at ON accounts (opened_at);

And the declaration that ties them together — no columns: block, because every field folds to the name the DDL already uses:

datastores:
  - name: accounts
    type: sql
    structure:
      schema: urn:accounts
      type: AccountRecord
    sql:
      url-ref: env:ACCOUNTS_DB_URL
      migrations: migrations/accounts
    dataClass: financial

sutra lint raises nothing on this package: every declared field has a column, every column’s type holds the declared facet range (VARCHAR(35) for maxLength="35", NUMERIC(18,2) for 18 total / 2 fractional digits), the one optional field maps to a nullable column, the three control columns are all present, and the table’s primary key is store_key itself — never a declared field — which is what lint requires of every projected table (KEY_MISMATCH otherwise: a business key would let two store keys collide on one row).

What lands in the database is a table anything can read:

store_keyaccount_idbalanceopened_atactivenoterevupdated_at
alicealice100.002026-01-01trueNULL12026-01-01T00:00:00Z

Round-tripping is lexical, not merely typed: every value travels as text in both directions — bound with the dialect’s write-side cast, read back through its canonical text rendering — so a put followed by a get returns the same shape it wrote, including a decimal’s written scale, decided by your own NUMERIC(18,2) rather than by anything in between. That holds exactly for xs:string, xs:decimal/the integer family, xs:boolean, xs:date, and an absent optional field — and an explicit JSON null on an optional field stores and reads back exactly like an absent one: both are NULL in the column, and both come back with the key omitted, never an explicit null.

xs:dateTime and xs:time are the one exception to “lexical”: the column stores an instant, not the string you sent, so a get returns the dialect’s own rendering of that instant, normalised toward ISO-8601 (a T separator, an explicit +HH:MM offset) — not necessarily the offset or the sub-second digits you wrote. Write 2026-08-04T10:00:00.000-05:00 into a dateTime column and expect back whatever your dialect renders that same instant as (typically UTC, for a timezone-aware column type), not the literal string. forUpdate, expect="unchanged" and the revision bookkeeping behave exactly as they do for an opaque store.

If AccountRecord later grows an owner sub-record, the package stops linting with STRUCTURE_NOT_FLAT — an explicit, package-time decision (flatten it, or give up projection for this store) rather than a silent shape change. The evolution rules are in Deployment packages.

Querying rows by key

Since 0.6.10 (typed-column stores since 0.6.13). A data-task read can return every row of a store whose key starts with a prefix, or lies in a range, together with aggregates over all of those rows. It suits append-only data — one row per event, keyed so that the rows you report on together share a prefix — where a single summary row would be a write hot spot.

<bpmn:dataStoreReference id="refDay" dataStoreRef="readingsStore">
  <bpmn:extensionElements>
    <q:store key="payload.meter + '|' + payload.day + '|'" query="prefix"
             limit="2" after="payload.after">
      <q:aggregate fn="sum" field="kwh"/>
      <q:aggregate fn="sum" field="kwh" groupBy="kind"/>
    </q:store>
  </bpmn:extensionElements>
</bpmn:dataStoreReference>
Attribute / elementMeaning
query="prefix"keys that start with key (an empty prefix is refused: it would read the whole store)
query="range", tokeys from key (inclusive) to to (exclusive); both are FEEL strings
limitrows per page, default 1000, maximum 10 000
afterFEEL: only keys after this one — the previous page’s next, or null for the first page
rows="false"aggregates only
<q:aggregate fn="count">{count} over every matching row
<q:aggregate fn="sum" field="…">{count, sum}; field is a dotted path into the stored value
groupBy="…"{groups: {"<value>": {count, sum?}}}, one cell per value of that path

The read’s target variable receives:

{"rows": [{"key": "m-7|2026-10-30|0001", "value": {"kind": "import", "kwh": "1.25"}}, ...],
 "aggregates": [{"count": 5, "sum": 7.55},
                {"groups": {"export": {"count": 2, "sum": 1.15}, "import": {"count": 3, "sum": 6.40}}}],
 "truncated": true, "next": "m-7|2026-10-30|0002"}
  • Keys compare bytewise, so m-7|2026-10-01 … m-7|2026-10-31 covers a month and a | separator behaves predictably. Rows come back in ascending key order. truncated says more rows follow, and next is the key to pass as after.
  • Aggregates cover every matching row, whatever limit and after say, so every page reports the same totals.
  • Sums are exact decimals, never floats. A sum adds JSON numbers and strings that hold a decimal literal ("30.05"). A row whose field is missing or null is counted but not summed. Any other value — text, a boolean, an object — fails the task with SUTRA.RUNTIME.DATASTORE.QUERY_NOT_NUMERIC. A row without the groupBy field groups under "null". No matching row gives {count: 0, sum: 0} or {groups: {}}, never null.
  • It is a plain read. It takes no locks, reads committed rows, never uses the read cache, and does not see the uncommitted writes of an enclosing <bpmn:transaction>. It cannot be combined with forUpdate, cannot be a write, and runs on sql stores — key/value or typed-column (see Querying a typed-column store).

The index a key query needs

A key query is one range scan of an index whose first two columns are (store_name, store_key), with store_key in a bytewise collation. Declare both in the store’s migration:

CREATE TABLE IF NOT EXISTS data_store (
  tenant_id       VARCHAR(64)  NOT NULL,   -- filled by the engine on insert
  module_id       VARCHAR(256) NOT NULL,
  module_version  VARCHAR(64)  NOT NULL,
  store_name      VARCHAR(128) NOT NULL,
  store_key       VARCHAR(512) COLLATE "C" NOT NULL,
  store_value     TEXT         NOT NULL,
  rev             BIGINT       NOT NULL DEFAULT 1,
  updated_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (store_name, store_key)
);

On MySQL/MariaDB use a …_bin collation for store_key (ascii_bin, utf8mb4_bin); on SQL Server an NVARCHAR key with a …_BIN2 collation. Before a store’s first query the engine checks both and refuses rather than scanning: SUTRA.RUNTIME.DATASTORE.QUERY_KEY_ORDER names a key column that does not sort bytewise, SUTRA.RUNTIME.DATASTORE.QUERY_KEY_INDEX a table with no such index — for example one keyed (tenant_id, module_id, module_version, store_name, store_key). Each message carries the DDL that fixes it. Stores that never run a key query are unaffected.

On MySQL a JSON number is held as a double, exact to about 15 significant digits; store amounts with more digits as strings there. PostgreSQL keeps the inputs’ decimal scale (6.40); MySQL and SQL Server report the sum without trailing zeros.

Querying a typed-column store

Since 0.6.13. A store that declares a structure: is queried with the same <q:store query=…>, over its own table. Each row’s value is the typed record, exactly as a plain read of that key returns it, and the aggregates are SQL COUNT and SUM over the declared columns, with GROUP BY the grouping column. For the accounts store from Typed columns:

<q:store key="'branch-7|'" query="prefix" limit="100" after="payload.after">
  <q:aggregate fn="sum" field="balance" groupBy="active"/>
  <q:aggregate fn="count" groupBy="note"/>
</q:store>
{"rows": [{"key": "branch-7|alice",
           "value": {"accountId": "alice", "balance": 100.50, "openedAt": "2026-01-01", "active": true}}],
 "aggregates": [{"groups": {"true": {"count": 2, "sum": 250.50}, "false": {"count": 1, "sum": 0.00}}},
                {"groups": {"null": {"count": 3}}}],
 "truncated": false, "next": null}
  • field and groupBy name one declared field — balance, not balance.value, and not a field the structure does not declare. sutra lint refuses either, naming the structure and its fields; at run time the same mistake fails with SUTRA.RUNTIME.DATASTORE.QUERY_UNSUPPORTED.
  • A summed field must be numeric — declared xs:decimal or an integer type. sutra lint refuses a sum over a string, date or boolean field. (If one reaches the engine anyway, every present value counts as not numeric and the task fails with SUTRA.RUNTIME.DATASTORE.QUERY_NOT_NUMERIC, as on a key/value store.)
  • Sums are exact. They are computed on the column itself, so a NUMERIC(18,2) sums to two decimal places on PostgreSQL. MySQL and SQL Server report the sum without trailing zeros.
  • An absent optional field is a NULL column. A sum skips it (the row still counts), and it groups under "null", just as a missing field does on a key/value store. Group keys read the way the record does: "true"/"false" for a boolean, the decimal text for a number, yyyy-mm-dd for a date.

The index requirement is the same idea with one column: the table’s first index column must be store_key, in a bytewise collation. A typed table normally already has PRIMARY KEY (store_key); add the collation:

CREATE TABLE IF NOT EXISTS accounts (
  store_key   VARCHAR(512)  COLLATE "C" NOT NULL,   -- MySQL: … COLLATE utf8mb4_bin; SQL Server: NVARCHAR … COLLATE Latin1_General_100_BIN2
  account_id  VARCHAR(35)   NOT NULL,
  balance     NUMERIC(18,2) NOT NULL,
  opened_at   DATE          NOT NULL,
  active      BOOLEAN       NOT NULL,
  note        VARCHAR(140),
  rev         BIGINT        NOT NULL DEFAULT 1,
  updated_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (store_key)
);

The first query checks both and refuses with SUTRA.RUNTIME.DATASTORE.QUERY_KEY_ORDER or SUTRA.RUNTIME.DATASTORE.QUERY_KEY_INDEX, naming the table and the DDL that fixes it — for example a table whose primary key is (account_id, store_key).

Claiming a key once

Since 0.6.10. expect="absent" makes a write an insert that applies only when the key does not exist yet. The database decides, so of any number of instances — on any number of replicas — that write the same key, exactly one wins. (A forUpdate read of a missing key locks nothing, so “read, see nothing, write” cannot do this.) The losing write changes nothing and raises SUTRA.RUNTIME.DATASTORE.CONFLICT. Catch it with an error boundary on the data task to take a “someone else got there first” path; uncaught, it fails the instance:

<bpmn:error id="taken" errorCode="SUTRA.RUNTIME.DATASTORE.CONFLICT"/>

<bpmn:dataStoreReference id="refRun" dataStoreRef="runsStore">
  <bpmn:extensionElements><q:store key="'daily|' + day" expect="absent"/></bpmn:extensionElements>
</bpmn:dataStoreReference>

<bpmn:serviceTask id="Claim">           <!-- writes `claim` to refRun -->
  <bpmn:dataOutputAssociation>
    <bpmn:sourceRef>claim</bpmn:sourceRef><bpmn:targetRef>refRun</bpmn:targetRef>
  </bpmn:dataOutputAssociation>
</bpmn:serviceTask>
<bpmn:boundaryEvent id="AlreadyClaimed" attachedToRef="Claim">
  <bpmn:errorEventDefinition errorRef="taken"/>
</bpmn:boundaryEvent>

The same conflict from expect="unchanged" is catchable the same way.

Walkthrough: readings by meter and day

A package with two processes: record-reading writes one row per reading with expect="absent" (so a resent reading is refused), and day-report runs the key query above and answers with a template. The files:

readings/
├── package.yaml            # labels: default / readings / 1.0.0
├── datastores.yaml         # store `readings`, env-indirected connection, migrations/readings
├── migrations/readings/V001__data_store.sql   # the table above
├── channels.yaml           # reading-in, day-report: HTTP + json + apikey
├── bpmn/readings.bpmn
└── templates/day-report.hbs

record-reading keys the row by meter, day and sequence number:

<q:store key="payload.meter + '|' + payload.day + '|' + payload.seq" expect="absent"/>

day-report reads the day into report, then picks the parts the answer shows:

<bpmn:serviceTask id="Query" name="Read the day">
  <bpmn:dataInputAssociation>
    <bpmn:sourceRef>refDay</bpmn:sourceRef><bpmn:targetRef>report</bpmn:targetRef>
  </bpmn:dataInputAssociation>
  <bpmn:dataInputAssociation>
    <bpmn:assignment><bpmn:from>report.aggregates[1]</bpmn:from><bpmn:to>total</bpmn:to></bpmn:assignment>
  </bpmn:dataInputAssociation>
  <bpmn:dataInputAssociation>
    <bpmn:assignment><bpmn:from>report.aggregates[2].groups</bpmn:from><bpmn:to>byKind</bpmn:to></bpmn:assignment>
  </bpmn:dataInputAssociation>
  <bpmn:dataInputAssociation>
    <bpmn:assignment><bpmn:from>for row in report.rows return row.key</bpmn:from><bpmn:to>keys</bpmn:to></bpmn:assignment>
  </bpmn:dataInputAssociation>
</bpmn:serviceTask>
{"keys": [{{#each keys}}{{#if @index}}, {{/if}}"{{this}}"{{/each}}],
 "readings": {{total.count}}, "kwh": {{total.sum}},
 "importKwh": {{byKind.import.sum}}, "exportKwh": {{byKind.export.sum}},
 "next": {{#if report.next}}"{{report.next}}"{{else}}null{{/if}}}

Package it and start PostgreSQL and the engine (the engine refuses a superuser database role, so it gets its own):

sutra package readings --out deployments
docker network create readings-net
docker run -d --name readings-db --network readings-net -e POSTGRES_PASSWORD=dev-only postgres:16-alpine
docker exec readings-db psql -U postgres \
  -c "CREATE ROLE sutra_engine LOGIN PASSWORD 'dev-only' NOSUPERUSER NOBYPASSRLS" \
  -c "ALTER SCHEMA public OWNER TO sutra_engine"
docker run -d --name readings-engine --network readings-net -p 127.0.0.1::8080 \
  -v "$PWD/deployments:/deployments:ro" -e SUTRA_DEPLOYMENTS_DIR=/deployments \
  -e SUTRA_DATASOURCE_URL=postgres://readings-db:5432/postgres \
  -e SUTRA_DATASOURCE_USERNAME=sutra_engine -e SUTRA_DATASOURCE_PASSWORD=dev-only \
  -e SUTRA_ADMIN_OIDC_DEV_DISABLED=true \
  -e READINGS_DB_URL=postgres://readings-db:5432/postgres \
  -e READINGS_DB_USER=sutra_engine -e READINGS_DB_PASSWORD=dev-only \
  -e READINGS_API_KEY=dev-only-readings-key ghcr.io/startr-trade/sutra:0.6.13
PORT=$(docker port readings-engine 8080 | head -1 | cut -d: -f2)
H=(-H 'Content-Type: application/json' -H 'X-Api-Key: dev-only-readings-key')

Record five readings for meter m-7 on 2026-10-30 (amounts as decimal strings or numbers, both sum exactly) and one on the next day:

for r in '"0001","kwh":"1.25","kind":"import"' '"0002","kwh":0.75,"kind":"export"' \
         '"0003","kwh":"2.10","kind":"import"' '"0004","kwh":"0.40","kind":"export"' \
         '"0005","kwh":"3.05","kind":"import"'; do
  curl -s -o /dev/null -w "%{http_code}\n" -X POST "http://127.0.0.1:$PORT/channels/reading-in" "${H[@]}" \
    -d "{\"meter\":\"m-7\",\"day\":\"2026-10-30\",\"seq\":$r}"
done
curl -s -o /dev/null -w "%{http_code}\n" -X POST "http://127.0.0.1:$PORT/channels/reading-in" "${H[@]}" \
  -d '{"meter":"m-7","day":"2026-10-31","seq":"0001","kwh":"9.99","kind":"import"}'

Each answers 200. Resending reading 0003 is refused, because the key exists:

{"type":"urn:bpm:diag:SUTRA.RUNTIME.DATASTORE.CONFLICT", ... "detail":"Optimistic-concurrency
conflict writing data store 'readings' key 'm-7|2026-10-30|0003' at data task 'Record': the key
already exists (expect=\"absent\"). ..."}

Ask for the day, two rows per page, passing each page’s next as the next after:

curl -s -X POST "http://127.0.0.1:$PORT/channels/day-report" "${H[@]}" \
  -d '{"meter":"m-7","day":"2026-10-30","after":null}'
{"keys": ["m-7|2026-10-30|0001", "m-7|2026-10-30|0002"],
 "readings": 5, "kwh": 7.55,
 "importKwh": 6.40, "exportKwh": 1.15,
 "next": "m-7|2026-10-30|0002"}

With "after":"m-7|2026-10-30|0002" the keys are 0003 and 0004 (next …|0004); with "after":"m-7|2026-10-30|0004" only 0005, and "next": null. The totals are the same on every page, and the 2026-10-31 reading is never counted. Clean up with docker rm -f readings-engine readings-db && docker network rm readings-net.

Deleting a row

Since 0.7.0. delete="true" on a write removes the key’s row instead of writing a value. The reference is the targetRef of the data task’s dataOutputAssociation, as for any write. The association needs no <sourceRef>, and one that is given is ignored.

<bpmn:dataStoreReference id="refRemove" dataStoreRef="itemsStore">
  <bpmn:extensionElements><q:store key="payload.itemId" delete="true"/></bpmn:extensionElements>
</bpmn:dataStoreReference>

<bpmn:serviceTask id="Remove">
  <bpmn:dataOutputAssociation><bpmn:targetRef>refRemove</bpmn:targetRef></bpmn:dataOutputAssociation>
</bpmn:serviceTask>
FormRow presentRow missing
delete="true"deletedno-op
delete="true" expect="unchanged", key read earlier in the instancedeleted while its revision is still the one read, otherwise a conflicta conflict (the row is gone)
delete="true" expect="unchanged", key never read, or read as missinga conflict (the row appeared)no-op

The conflict is SUTRA.RUNTIME.DATASTORE.CONFLICT. An error boundary on the data task catches it, as it catches a lost claim.

Read, check, delete. A data task runs its reads before its writes. So one task can read a key through one reference and delete it through another with expect="unchanged". If anything changes the row between the read and the delete, the delete does not apply.

<bpmn:dataStoreReference id="refItem" dataStoreRef="itemsStore">
  <bpmn:extensionElements><q:store key="payload.itemId"/></bpmn:extensionElements>
</bpmn:dataStoreReference>
<bpmn:dataStoreReference id="refRemove" dataStoreRef="itemsStore">
  <bpmn:extensionElements><q:store key="payload.itemId" delete="true" expect="unchanged"/></bpmn:extensionElements>
</bpmn:dataStoreReference>

<bpmn:serviceTask id="Remove">
  <bpmn:dataInputAssociation><bpmn:sourceRef>refItem</bpmn:sourceRef><bpmn:targetRef>item</bpmn:targetRef></bpmn:dataInputAssociation>
  <bpmn:dataOutputAssociation><bpmn:targetRef>refRemove</bpmn:targetRef></bpmn:dataOutputAssociation>
</bpmn:serviceTask>

A delete follows the same rules as a write of the same key:

  • Transactions. Inside a <bpmn:transaction> the delete runs on the scope’s store transaction, so it commits or rolls back with the scope. Outside one it commits on its own.
  • Locks. The DELETE takes the row lock itself. It waits behind a forUpdate read of the same key in another instance until that instance commits.
  • The read cache. A delete through the engine invalidates the key at once. Other replicas see the row gone on their next read, because a deleted row no longer has the revision they cached.
  • Store shapes and databases. Deletes work on key/value and typed-column stores, on PostgreSQL, MySQL, MariaDB and SQL Server.

delete cannot be combined with field, forUpdate, expect="absent", query, to, rows, limit, after or a <q:aggregate>. A delete reference cannot be the source of a read either. sutra lint, sealing and deploy all refuse these with SUTRA.PARSE.STORE.DELETE_INVALID.

A re-created row never reuses a revision. A row’s revision starts at the database’s own clock (microseconds since the epoch, read in the insert itself, so all replicas share one clock) and goes up by one with each write. Revisions are strictly increasing per row but not dense. A key that is deleted and created again therefore starts above every revision the old row had. A replica that cached the old row reads the new one, and an expect="unchanged" write or delete that read the old row is refused. This relies on the database clock moving forward: if it is stepped backwards between the delete and the re-create, the guarantee can break.

Walkthrough: removing an item seen through two replicas

A package with an items store that keeps a read cache, a results store, and three processes, each started over HTTP: item-write writes a row, item-copy reads one row and copies it into results, and item-delete runs the read-check-delete above. Two engines share one database, so one can delete a row the other has cached. The files:

items/
├── package.yaml            # labels: default / items / 1.0.0
├── datastores.yaml         # stores `items` (cache: {enabled: true}) and `results`, env-indirected
├── migrations/items/V001__data_store.sql     # the key/value table, as in Querying rows by key
├── migrations/results/V001__data_store.sql   # the same table
├── channels.yaml           # item-in, copy-in, delete-in: HTTP + json + apikey
└── bpmn/items.bpmn

item-copy copies the row into results under copy|<key>|<tag>:

<bpmn:dataStoreReference id="refItem" dataStoreRef="itemsStore">
  <bpmn:extensionElements><q:store key="payload.key"/></bpmn:extensionElements>
</bpmn:dataStoreReference>
<bpmn:dataStoreReference id="refCopy" dataStoreRef="resultsStore">
  <bpmn:extensionElements><q:store key="'copy|' + payload.key + '|' + payload.tag"/></bpmn:extensionElements>
</bpmn:dataStoreReference>

<bpmn:serviceTask id="Copy" name="Copy the item">
  <bpmn:dataInputAssociation>
    <bpmn:sourceRef>refItem</bpmn:sourceRef><bpmn:targetRef>copied</bpmn:targetRef>
  </bpmn:dataInputAssociation>
  <bpmn:dataOutputAssociation>
    <bpmn:sourceRef>copied</bpmn:sourceRef><bpmn:targetRef>refCopy</bpmn:targetRef>
  </bpmn:dataOutputAssociation>
</bpmn:serviceTask>

Package it, then start PostgreSQL and two engines, items-a and items-b:

sutra package items --out deployments
docker network create items-net
docker run -d --name items-db --network items-net -e POSTGRES_PASSWORD=dev-only postgres:16-alpine
until docker exec items-db pg_isready -U postgres -h 127.0.0.1; do sleep 1; done; sleep 2
docker exec items-db psql -U postgres \
  -c "CREATE ROLE sutra_engine LOGIN PASSWORD 'dev-only' NOSUPERUSER NOBYPASSRLS" \
  -c "ALTER SCHEMA public OWNER TO sutra_engine"
for r in a b; do
  docker run -d --name items-$r --network items-net -p 127.0.0.1::8080 \
    -v "$PWD/deployments:/deployments:ro" -e SUTRA_DEPLOYMENTS_DIR=/deployments \
    -e SUTRA_DATASOURCE_URL=postgres://items-db:5432/postgres \
    -e SUTRA_DATASOURCE_USERNAME=sutra_engine -e SUTRA_DATASOURCE_PASSWORD=dev-only \
    -e SUTRA_ADMIN_OIDC_DEV_DISABLED=true \
    -e ITEMS_DB_URL=postgres://items-db:5432/postgres \
    -e ITEMS_DB_USER=sutra_engine -e ITEMS_DB_PASSWORD=dev-only \
    -e ITEMS_API_KEY=dev-only-items-key ghcr.io/startr-trade/sutra:0.8.2
  until curl -sf -o /dev/null "http://127.0.0.1:$(docker port items-$r 8080 | head -1 | cut -d: -f2)/sutra/health/ready"; do
    sleep 1
  done
done
A=http://127.0.0.1:$(docker port items-a 8080 | head -1 | cut -d: -f2)
B=http://127.0.0.1:$(docker port items-b 8080 | head -1 | cut -d: -f2)
C=(-s -o /dev/null -w "%{http_code}\n" -H 'Content-Type: application/json' -H 'X-Api-Key: dev-only-items-key')
sql() { docker exec items-db psql -U postgres -tAc "$1"; }
  1. Write an item through items-a. Its revision is the database clock at the insert:

    curl "${C[@]}" -X POST "$A/channels/item-in" -d '{"key":"item-0001","entry":{"amount":"12.50"}}'
    sql "SELECT store_value, rev FROM data_store WHERE store_name='items' AND store_key='item-0001'"
    
    200
    {"amount":"12.50"}|1795060962963065
    
  2. Read it through items-b, which now holds it in its cache:

    curl "${C[@]}" -X POST "$B/channels/copy-in" -d '{"key":"item-0001","tag":"before"}'
    sql "SELECT store_value FROM data_store WHERE store_name='results' AND store_key='copy|item-0001|before'"
    
    200
    {"amount":"12.50"}
    
  3. Delete it through items-a:

    curl "${C[@]}" -X POST "$A/channels/delete-in" -d '{"key":"item-0001"}'
    sql "SELECT count(*) FROM data_store WHERE store_name='items' AND store_key='item-0001'"
    
    200
    0
    
  4. Read it through items-b again. Its cached entry’s revision no longer matches (the row is gone), so it serves nothing stale:

    curl "${C[@]}" -X POST "$B/channels/copy-in" -d '{"key":"item-0001","tag":"after"}'
    sql "SELECT store_value FROM data_store WHERE store_name='results' AND store_key='copy|item-0001|after'"
    
    200
    null
    
  5. Create the item again with another value through items-a, and read it through items-b. The new row starts above every revision the old one had, so items-b reads the new value:

    curl "${C[@]}" -X POST "$A/channels/item-in" -d '{"key":"item-0001","entry":{"amount":"2.00"}}'
    sql "SELECT store_value, rev FROM data_store WHERE store_name='items' AND store_key='item-0001'"
    curl "${C[@]}" -X POST "$B/channels/copy-in" -d '{"key":"item-0001","tag":"again"}'
    sql "SELECT store_value FROM data_store WHERE store_name='results' AND store_key='copy|item-0001|again'"
    
    200
    {"amount":"2.00"}|1795060963446990
    200
    {"amount":"2.00"}
    
  6. Delete it twice, once through each engine. Both answer 200: the second delete is of a missing key, a no-op.

    curl "${C[@]}" -X POST "$A/channels/delete-in" -d '{"key":"item-0001"}'
    curl "${C[@]}" -X POST "$B/channels/delete-in" -d '{"key":"item-0001"}'
    
  7. Change the delete reference to <q:store key="payload.key" delete="true" forUpdate="true"/> and lint the package with sutra lint items. It is refused before anything runs:

    [ERROR] SUTRA.DEPLOY.PACKAGE.CONFIG_INVALID — BPMN bpmn/items.bpmn fails to parse: [SUTRA.PARSE.STORE.DELETE_INVALID] <serviceTask> 'Remove' declares a delete on data store 'items' that cannot run: @forUpdate locks a row for a read; a delete takes the row lock itself (lock it with a forUpdate read through another reference). (items/bpmn/items.bpmn)
    

Revisions differ on every run. Clean up with docker rm -f items-a items-b items-db && docker network rm items-net.

Caching reads

Since 0.6.5. A data task reads a value and its revision in one round trip. A store that is read far more often than it is written — settings, reference data — can also keep a read cache:

datastores:
  - name: refdata
    type: sql
    sql:
      url-ref: env:REF_DB_URL
      username-ref: env:REF_DB_USER
      password-ref: env:REF_DB_PASSWORD
    cache:
      enabled: true      # default false
      maxEntries: 1000   # keys kept; the least recently read is evicted first
      ttl: PT0S          # default: check the revision on every read
ttlA read of a cached keyA write made through any engineA write made by SQL outside the engine
PT0S (default)one revision check (SELECT rev) instead of a full readseen by the next read on every replica: each write bumps the row’s revisionseen by the next read if the SQL bumps rev, otherwise only once the key is evicted
positive, e.g. PT5Sno query at all while the key was checked less than ttl agothrough this engine: seen at once; through another replica: within ttlwithin ttl

The cache is shared by every lane of the engine process and is per deployment. A write or delete made through the engine — a data task’s, or one inside a <bpmn:transaction> — invalidates its key at once. Every other replica sees it on its next read, a key deleted and created again included: the re-created row never reuses a revision (see Deleting a row). Reads inside a transaction always go to the database, because they must see the transaction’s own snapshot and locks. Keep ttl at PT0S unless a few seconds of staleness after an operator’s edit is acceptable. When you change rows by hand, bump rev with them:

UPDATE data_store SET store_value = '{"maxAmount": 500}', rev = rev + 1
 WHERE store_name = 'refdata' AND store_key = 'default';

When the database cannot be reached

A data task whose database cannot be reached (a dropped connection, a pool timeout, a server restarting) fails with SUTRA.RUNTIME.DATASTORE.UNAVAILABLE. That is not a failed instance: the step is not committed and the delivery is refused for a retry (HTTP 503, a broker redelivery, the outbox’s backoff for a hop between processes, the timer poller’s backoff), whatever the process declares about idempotency. It is never dead-lettered. (Since 0.8.2.)

Isolation and atomicity

Two separate guarantees, worth keeping distinct:

  • Isolation (no lost update between concurrent writers) comes from serializing access to a key — either a pessimistic forUpdate="true" lock, or funneling all writers for that channel through a single active consumer (see the singleton channel property in Channels and transports).
  • Atomicity (all writes commit or none do) comes from a <bpmn:transaction> scope: writes inside it commit together on a normal end, or roll back together on a cancel end / error. An external store write cannot auto-rollback on its own — the transaction sub-process is what gives you the boundary.

The worked example combines both: a singleton channel serializes writers, and the debit/credit pair runs inside a <bpmn:transaction> so a rejected transfer touches no row.

Keeping a message as text

A flow can keep a message exactly as it arrived, as text in a typed store column, and work with it decoded again later. This walkthrough keeps notes in a TEXT column, reads one back through <q:decode>, and then masks them.

You need the engine and PostgreSQL; this walkthrough was run on PostgreSQL 16. xsd/q.xsd is the reference for the element.

1. A typed store with a text column

package.yaml labels the module notes, version 1.0.0. datastores.yaml gives the store its row type:

datastores:
  - name: notes
    type: sql
    structure:
      schema: urn:note-row
      type: NoteRow
    sql:
      url-ref: env:NOTES_DB_URL
      username-ref: env:NOTES_DB_USER
      password-ref: env:NOTES_DB_PASSWORD
      migrations: migrations/notes

schemas/note-row/codec-manifest.yaml is schemaKind: xsd and formats: [xml]. schemas/note-row/note-row.xsd declares the row. body has no length limit:

<xs:element name="NoteRow">
  <xs:complexType>
    <xs:sequence>
      <xs:element name="noteId"      type="NoteId"/>    <!-- maxLength 64 -->
      <xs:element name="contentType" type="MediaType" minOccurs="0"/>  <!-- maxLength 128 -->
      <xs:element name="body"        type="xs:string"/>
    </xs:sequence>
  </xs:complexType>
</xs:element>

migrations/notes/V001__notes.sql:

CREATE TABLE IF NOT EXISTS notes (
  store_key    VARCHAR(512) NOT NULL,
  note_id      VARCHAR(64)  NOT NULL,
  content_type VARCHAR(128),
  body         TEXT         NOT NULL,
  rev          BIGINT       NOT NULL DEFAULT 1,
  updated_at   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (store_key)
);

On MySQL, TEXT stops at 64 KiB, so use MEDIUMTEXT or LONGTEXT. On SQL Server, use NVARCHAR(MAX).

2. Capture the text

channels.yaml declares two HTTP channels with the usual API-key auth. note-in has no codec, so it takes any format, and its key comes from a header:

channels:
  - name: note-in
    transport: http
    bind: "POST /channels/note-in"
    idempotency-key-header: X-Note-Id
    cloudevents-mode: none
    auth: { scheme: apikey, apikey: { value: env:NOTES_API_KEY, header: X-Api-Key } }
  - name: note-read
    transport: http
    bind: "POST /channels/note-read"
    codec: json
    cloudevents-mode: none
    auth: { scheme: apikey, apikey: { value: env:NOTES_API_KEY, header: X-Api-Key } }

bpmn/note-keep.bpmn builds the row from event on the step right after the start. A later message would replace event, so capture here. The gateway is step 6:

<bpmn:dataStore id="notesStore" name="notes"/>
<bpmn:process id="note-keep" isExecutable="true">
  <bpmn:extensionElements>
    <q:variables><q:variable name="row" type="object"/></q:variables>
  </bpmn:extensionElements>
  <bpmn:dataStoreReference id="refRow" dataStoreRef="notesStore">
    <bpmn:extensionElements><q:store key="event.dedupKey"/></bpmn:extensionElements>
  </bpmn:dataStoreReference>
  <bpmn:startEvent id="Start">
    <bpmn:extensionElements><q:source channel="note-in"/></bpmn:extensionElements>
  </bpmn:startEvent>
  <bpmn:exclusiveGateway id="IsText" default="ToKeep"/>
  <bpmn:serviceTask id="Keep" name="Keep the text">
    <bpmn:dataInputAssociation>
      <bpmn:assignment>
        <bpmn:from>{noteId: event.dedupKey, contentType: event.contentType, body: event.body}</bpmn:from>
        <bpmn:to>row</bpmn:to>
      </bpmn:assignment>
    </bpmn:dataInputAssociation>
    <bpmn:dataOutputAssociation>
      <bpmn:sourceRef>row</bpmn:sourceRef><bpmn:targetRef>refRow</bpmn:targetRef>
    </bpmn:dataOutputAssociation>
  </bpmn:serviceTask>
  <bpmn:endEvent id="Kept"/>
  <bpmn:endEvent id="NotText"/>
  <bpmn:sequenceFlow id="F1" sourceRef="Start" targetRef="IsText"/>
  <bpmn:sequenceFlow id="ToKeep" sourceRef="IsText" targetRef="Keep"/>
  <bpmn:sequenceFlow id="ToRefuse" sourceRef="IsText" targetRef="NotText">
    <bpmn:conditionExpression>event.bodyLossy = true</bpmn:conditionExpression>
  </bpmn:sequenceFlow>
  <bpmn:sequenceFlow id="F2" sourceRef="Keep" targetRef="Kept"/>
</bpmn:process>

3. A retrieval flow

bpmn/note-read.bpmn reads the row, decodes its text by the row’s own content type, and answers from the decoded fields. The store read runs first, then <q:decode>, within one data task. Text that doesn’t parse raises SUTRA.RUNTIME.DECODE.FAILED, which the boundary event catches:

<bpmn:error id="undecodable" errorCode="SUTRA.RUNTIME.DECODE.FAILED"/>
<bpmn:dataStore id="notesStore" name="notes"/>
<bpmn:process id="note-read" isExecutable="true">
  <bpmn:extensionElements>
    <q:variables>
      <q:variable name="row" type="object"/>
      <q:variable name="note" type="object"/>
    </q:variables>
  </bpmn:extensionElements>
  <bpmn:dataStoreReference id="refRead" dataStoreRef="notesStore">
    <bpmn:extensionElements><q:store key="req.id"/></bpmn:extensionElements>
  </bpmn:dataStoreReference>
  <bpmn:startEvent id="Start">
    <bpmn:extensionElements><q:source channel="note-read" name="req"/></bpmn:extensionElements>
  </bpmn:startEvent>
  <bpmn:serviceTask id="Load" name="Read and decode">
    <bpmn:extensionElements>
      <q:decode name="note" expression="row.body" codec="json" contentType="row.contentType"/>
    </bpmn:extensionElements>
    <bpmn:dataInputAssociation>
      <bpmn:sourceRef>refRead</bpmn:sourceRef><bpmn:targetRef>row</bpmn:targetRef>
    </bpmn:dataInputAssociation>
  </bpmn:serviceTask>
  <bpmn:boundaryEvent id="BadText" attachedToRef="Load">
    <bpmn:errorEventDefinition errorRef="undecodable"/>
  </bpmn:boundaryEvent>
  <bpmn:serviceTask id="Answer" implementation="summary.hbs">
    <bpmn:extensionElements><q:reply mode="native" contentType="application/json"/></bpmn:extensionElements>
  </bpmn:serviceTask>
  <bpmn:serviceTask id="AnswerBad" implementation="undecodable.hbs">
    <bpmn:extensionElements><q:reply mode="native" contentType="application/json"/></bpmn:extensionElements>
  </bpmn:serviceTask>
  <bpmn:endEvent id="Done"/>
  <bpmn:endEvent id="DoneBad"/>
  <bpmn:sequenceFlow id="F1" sourceRef="Start" targetRef="Load"/>
  <bpmn:sequenceFlow id="F2" sourceRef="Load" targetRef="Answer"/>
  <bpmn:sequenceFlow id="F3" sourceRef="Answer" targetRef="Done"/>
  <bpmn:sequenceFlow id="F4" sourceRef="BadText" targetRef="AnswerBad"/>
  <bpmn:sequenceFlow id="F5" sourceRef="AnswerBad" targetRef="DoneBad"/>
</bpmn:process>

templates/summary.hbs:

{"decoded":true,"title":"{{note.title}}","pages":{{note.pages}}}

templates/undecodable.hbs:

{"decoded":false}

4. Package it and start the engine

sutra lint notes
sutra package notes -o deployments

Give the engine a role that can’t bypass row-level security, and a database that role owns:

CREATE ROLE sutra_app LOGIN NOSUPERUSER NOBYPASSRLS PASSWORD '<a password>';
CREATE DATABASE sutra OWNER sutra_app;

Start the engine with SUTRA_DATASOURCE_URL pointing at that database as sutra_app, SUTRA_DEPLOYMENTS_DIR pointing at deployments, NOTES_API_KEY, and NOTES_DB_URL, NOTES_DB_USER and NOTES_DB_PASSWORD for the store. Turn admin auth on with SUTRA_ADMIN_AUTH_SCHEME=apikey, SUTRA_ADMIN_AUTH_KEY_REF=env:SUTRA_ADMIN_API_KEY and SUTRA_ADMIN_API_KEY, because step 7 reads an instance. Set B to the engine’s address.

5. Keep a note and read it back

note.json has odd spacing, a non-ASCII title, and a number with a written scale:

{ "title" : "café notes",
  "pages": 3.50 }
curl -s -o /dev/null -w '%{http_code}\n' -X POST "$B/channels/note-in" \
  -H 'X-Api-Key: <your key>' -H 'Content-Type: application/json' -H 'X-Note-Id: n-1' \
  --data-binary @note.json
200

The column holds the exact text:

SELECT store_key, note_id, content_type, body FROM notes;
 store_key | note_id |   content_type   |           body
-----------+---------+------------------+---------------------------
 n-1       | n-1     | application/json | { "title" : "café notes",+
           |         |                  |   "pages": 3.50 }        +
           |         |                  |

Read it back:

curl -s -X POST "$B/channels/note-read" -H 'X-Api-Key: <your key>' \
  -H 'Content-Type: application/json' -d '{"id":"n-1"}'
{"decoded":true,"title":"café notes","pages":3.50}

The row’s content type picks the parser, so the same flow reads XML. Keep <note><title>second</title><pages>2</pages></note> as application/xml under n-2, and read it:

{"decoded":true,"title":"second","pages":2}

Keep { this is not json as application/json under n-3. It is stored as it came, and reading it takes the error boundary:

{"decoded":false}

6. A message that isn’t text

Send bytes that aren’t valid UTF-8:

printf 'ok\377\376' | curl -s -o /dev/null -w '%{http_code}\n' -X POST "$B/channels/note-in" \
  -H 'X-Api-Key: <your key>' -H 'Content-Type: application/octet-stream' -H 'X-Note-Id: n-4' \
  --data-binary @-
200

The invalid bytes became U+FFFD, so event.bodyLossy is true, and the gateway took ToRefuse. There is no n-4 row. A clean message has no bodyLossy key at all, which is why = true is the test. <q:decode> handles text only; there is no binary form.

7. Masking

Now suppose the titles are private. Add redactors/title.hbs, which holds the path to mask:

/title

Name it on the source. This walkthrough also adds a wait, so there is an instance to look at:

<bpmn:startEvent id="Start">
  <bpmn:extensionElements>
    <q:source channel="note-in"><q:redactors><q:redactor ref="title"/></q:redactors></q:source>
    <q:alias name="noteId" expression="event.dedupKey" unique="true"/>
  </bpmn:extensionElements>
</bpmn:startEvent>
<!-- after Keep -->
<bpmn:receiveTask id="Review" name="Wait for a review">
  <bpmn:extensionElements>
    <q:source channel="note-reviewed" name="review"/>
    <q:alias name="noteId" expression="review.id"/>
  </bpmn:extensionElements>
</bpmn:receiveTask>

sutra lint notes now finds the copies the engine can’t see through, including in note-read, which masks nothing itself but reads what a masking flow stored:

[WARN] SUTRA.CONFIG.BPMN.RAW_BODY_UNMASKED — ... process 'note-keep' declares masking, but variable 'row' holds raw event.body text and is not sensitive, so the raw message shows in audit and inspect and is stored unencrypted. Declare it <q:variable name="row" sensitive="true"/>. This becomes an error in a later release.
[WARN] SUTRA.CONFIG.BPMN.RAW_BODY_STORED — ... process 'note-keep' declares masking, but writes raw event.body text to data store 'notes' through variable 'row'. A store column has no at-rest encryption, and sensitive does not reach it.
[WARN] SUTRA.CONFIG.BPMN.RAW_BODY_UNMASKED — ... process 'note-read' reads text a masking process stored, and variable 'row' holds raw event.body text and is not sensitive, ...
[ERROR] SUTRA.CONFIG.DECODE.REDACTORS_MISSING — ... process 'note-read' reads text a masking process stored, and <q:decode name="note"> parses raw event.body text into a variable with no masking, so the message shows in audit and inspect. Declare it <q:variable name="note" sensitive="true"/>, or give the step <q:redactors>.

Declare row sensitive in both flows and note sensitive in note-read. Only RAW_BODY_STORED remains. It stays because storing the text is the point of this flow, and it is true: the column is cleartext.

Package it and restart the engine with SUTRA_CRYPTO_MASTER_KEY set, so that parked state is encrypted. Keep a note with a private title under n-5, and look at the parked instance. This route needs the admin key:

curl -s -H "X-API-Key: $SUTRA_ADMIN_API_KEY" "$B/sutra/instances?alias=noteId:n-5"
curl -s -H "X-API-Key: $SUTRA_ADMIN_API_KEY" "$B/sutra/instances/<instanceId>"
{"instanceId":"cb729345-dde3-46ab-94c6-0beaa6cbf5d3","deploymentId":"dep-a149aa3f6fbb7bc0a118c185","status":"SUSPENDED","completedNodes":["Keep"],"waitingNodes":["Review"],"sensitive":["row"],"variables":{"attr":"{}","event":"{body=[REDACTED], channel=note-in, contentType=application/json, dedupKey=[REDACTED], headers={accept=[REDACTED], content-length=[REDACTED], content-type=[REDACTED], host=[REDACTED], user-agent=[REDACTED], x-api-key=[REDACTED], x-note-id=[REDACTED]}, idempotencyKey=[REDACTED], receivedAt=2026-11-04T19:21:26.23034207Z}","header":"{accept=[REDACTED], content-length=[REDACTED], content-type=[REDACTED], host=[REDACTED], user-agent=[REDACTED], x-api-key=[REDACTED], x-note-id=[REDACTED]}","noteId":"n-5","payload":"{pages=1, title=[REDACTED]}","payload.validation":"[]","row":"***REDACTED***","validation":"{firstIssue=, firstReasonCode=, issues=[], outcome=OK, tier=n/a}"}}

The title appears nowhere in that answer, and the parked row in instance_state doesn’t contain it either. The notes column does, in the clear. To keep it out of the column, don’t store it in text.

The flow itself still reads the real values. Answer the wait on note-reviewed with {"id":"n-5"}, then read the note:

{"decoded":true,"title":"PRIVATE-TITLE-5","pages":1}

Answering with the title is this flow’s choice. A real one masking its titles would answer something else.

Limits

  • Size. <q:decode> refuses text over sutra.codec.max-payload-bytes with SUTRA.RUNTIME.DECODE.TOO_LARGE. Column limits are the database’s.
  • Encoding. A body whose prolog declares another encoding, such as ISO-8859-1 XML, can’t be kept faithfully. A NUL byte survives into event.body, but PostgreSQL TEXT refuses it, and that write fails the instance.
  • After a wait. A resuming message replaces event. Capture before the wait, as here.
  • Other packages. Lint follows a store by name within one package. Another package reading the same database isn’t seen.

Sensitivity

A store’s dataClass (pii / pci / phi / financial) marks its contents sensitive: values read from it are redacted wherever the engine emits observability data — audit events, structured logs, traces — while the flow itself still sees the real value. The tag travels with the variable a data association reads into, so it stays redacted downstream too.

Next