Skip to Content

Data Backend

IronFlock provisions a private database for each project, powered by TimescaleDB. Your app defines the data schema; IronFlock creates the tables and starts collecting data the moment a device is added to the app.

How It Works

  1. Define your data schema in .ironflock/data-template.yml.
  2. Use the IronFlock SDK to publish data from your edge code.
  3. IronFlock automatically sets up the database tables in each project where the app is installed.
  4. Data flows from devices through the messaging system into the project database.

Each project gets its own physical database — there is no data sharing between projects.

The user has full control over the data collected by your app in their project. As a developer, you do not have access to this data.

Defining the Data Schema

Create a data-template.yml file in the .ironflock/ directory:

data: tables: - tablename: sensordata columns: - id: tsp name: Timestamp description: Timestamp of measurement path: args[0].timestamp dataType: timestamp - id: temperature name: Temperature description: Temperature reading in Celsius path: args[0].temperature dataType: numeric - id: humidity name: Humidity description: Relative humidity percentage path: args[0].humidity dataType: numeric - id: device_id name: Device ID description: Source device identifier path: args[0].device_id dataType: string

Column Options

FieldDescription
idInternal column identifier (use tsp for timestamp columns)
nameHuman-readable column name shown in boards
descriptionOptional description
pathPath to the value in the published data object (e.g., args[0].temperature)
dataTypeOne of: timestamp, numeric, string, boolean
secretEncrypt this column at rest and never return it in clear on a normal read — see Secret Columns below

Secret Columns

Some values have to be stored but must never be shown: an API token, a device password, a licence key. Mark the column secret: true and the data backend encrypts it on insert:

- tablename: credentials columns: - id: tsp dataType: timestamp - id: device_id dataType: string - id: api_token dataType: string secret: true

From then on no ordinary read returns the plaintext — not even to the app that wrote it:

Read pathWhat you get back
History reads and realtime rows: what boards, widgets, the SDK and other apps reading the table getthe placeholder __secret__
SQL (the SQL query console, data exports and the FleetDB Access Postgres login) and database backupsthe stored ciphertext, ifsec:1:…
The SDK’s reveal functionthe decrypted value

NULL stays NULL everywhere, so “set but hidden” remains distinguishable from “never written”.

Reading a secret back in clear is only possible from the app’s own containers, through the SDK — see Secret Columns in the SDK reference. Boards and other apps cannot obtain it at all, which is what makes “never displayed in clear” true rather than merely cosmetic.

Updating a row without losing its secret. On an entity table, a client that edits a row can never send the real secret back — reads only ever gave it the placeholder. The data backend therefore treats the placeholder __secret__ (and the •••••••• mask boards display) as keep the previous value on write, and an omitted secret column keeps it too. Sending an explicit null clears the secret. Editing a machine’s description in a board form therefore never wipes its access code.

Two consequences follow: the literal strings __secret__ and •••••••• cannot themselves be stored as secret values (and raw ifsec:… ciphertext is rejected as input), and on a table without an entity key a placeholder write is rejected — there is no previous row to keep.

Three consequences are worth planning around, because they are hard limits rather than guidance:

  • Secret columns are strings only. Encryption produces text, so numeric, boolean and timestamp columns cannot be secret. The mandatory tsp column cannot be secret either.
  • You cannot filter, group or sort by a secret column. Every row is encrypted under its own random value, so two rows holding the same secret store different ciphertext. Equality filters, GROUP BY, ORDER BY and DISTINCT over a secret column simply cannot work. To answer “does this value match?”, use the SDK’s verify function, which compares inside the data backend instead of returning anything.
  • A secret column cannot be an entity key. Because every row’s ciphertext differs, latest-per-entity reads would treat each row as its own entity, so naming one in maintainLatestFlagFor is rejected outright.

The last two restrictions are checked when your data template is validated, so a table that breaks them fails on release rather than misbehaving later.

Table Options

Besides columns, a table takes a few optional keys that control how it is described and how its data ages:

data: tables: - tablename: sensordata description: Environmental readings from the shop floor chunkTimeInterval: 1 hour dropAfter: 30 days columns: # ...
FieldDescription
tablenameName of the table
descriptionOptional description, shown in the UI and used by AI agents to understand the table
chunkTimeIntervalSize of the time partitions the table is split into. Defaults to 7 days
dropAfterRetention window — partitions older than this are dropped automatically
downsampleMaintain a pre-aggregated copy for fast long-window charts — see Continuous Downsampling below
maintainLatestFlagForColumns identifying a unique entity — see Tracking the Latest State of an Entity below
privateHide this table from other apps — see Sharing Data with Other Apps below

chunkTimeInterval controls how the time-series data is partitioned on disk. Pick it so that one partition roughly matches what you query at once: high-frequency data collected every second benefits from small chunks (minutes to hours), slow-moving data from large ones (weeks). This is only the app’s default — the project owner can adjust it later on their own data backend.

dropAfter turns the table into a rolling window. Whole partitions older than the given interval are dropped, which is far cheaper than deleting individual rows. The cleanup job runs on a dropAfter / 4 schedule, so a record can outlive its expiry by up to a quarter of the interval before its partition goes. Omit dropAfter to keep data indefinitely.

Both take PostgreSQL interval strings — 30 minutes, 1 hour, 7 days, 6 months.

Continuous Downsampling

Dashboards can ask the database to aggregate data — hourly averages, daily totals, counts per machine. Computing those from raw records is fine for a day and expensive for a year. Add downsample to a table and the platform maintains a continuously updated, pre-aggregated copy of it, then answers long-window queries from that copy instead:

data: tables: - tablename: sensordata dropAfter: 30 days downsample: bucket: 1 minute keepFor: 2 years paths: - payload.temperature columns: # ...
FieldDescription
bucketGranularity of the pre-aggregated copy. Defaults to 1 minute
keepForHow long to keep the downsampled history. Omit to keep it indefinitely
pathsJSON field paths to include, in the same notation dashboards use

bucket is the finest resolution a chart can be served from — a chart asking for buckets much finer than this reads the raw table instead. It accepts fixed-width intervals from 1 second to 1 day that divide a day evenly (1 minute, 5 minutes, 1 hour). The default of 1 minute suits practically every dashboard; a coarser bucket costs less storage and less write throughput.

keepFor is what makes long histories possible at all. Raw records disappear with dropAfter, but the downsampled copy has its own retention: keep raw data for 30 days and downsampled data for 2 years, and a board can still chart two years of hourly averages on a fraction of the storage. Set it longer than dropAfter — the platform rejects the opposite as a misconfiguration.

paths extends downsampling to values inside JSON columns. Numeric columns are included automatically; JSON fields must be named explicitly, since a JSON column has no fixed set of keys. Undeclared fields still work in dashboards — they are simply computed from the raw table.

Everything else about it is automatic. Every numeric column gets its statistics maintained (average, sum, minimum, maximum, first, last and a record count), grouped by the table’s entity key (maintainLatestFlagFor, or the publishing device). Dashboards need no configuration and no awareness of it: a widget queries as usual, and the platform decides per query whether the pre-aggregated copy can answer it — falling back to the raw table transparently when it cannot, for example when a filter references a column the copy does not group by.

Schema changes rebuild the copy. Adding, removing or retyping a column of a downsampled table — or editing the downsample block itself — rebuilds the pre-aggregated copy from the raw table. Anything older than dropAfter cannot be reconstructed and is lost. Set the block up together with the table where you can, and treat later schema changes on long-lived tables as a deliberate decision.

Publishing Data from Edge Code

Use the IronFlock SDK to send data from your app:

from ironflock import IronFlock flock = IronFlock() await flock.start() await flock.publish_to_table("sensordata", { "timestamp": "2025-01-15T10:30:00Z", "temperature": 23.5, "humidity": 62.1, "device_id": "sensor-001" })

Every row needs a value for the mandatory tsp column: the data backend refuses a row without one. The SDK’s table write fails with an error; a row a board publishes is dropped, leaving only an entry in the app’s error-logs table. The sensordata definition above maps that column with path: args[0].timestamp, so tsp comes from the row’s timestamp field. A column without a path reads the row’s field of the same name (args[0].<id>): without that mapping, each row would need a tsp field of its own.

For high-frequency data, send many rows in a single message instead of one round-trip per row using publish_rows_to_table / publishRowsToTable / PublishRowsToTable or append_rows_to_table / appendRowsToTable / AppendRowsToTable — the two are the same and report the insert outcome. Each batch is inserted atomically — all-or-nothing. See the SDK reference for details.

Transform Tables

You can define SQL transforms that automatically aggregate or process your raw data:

data: tables: - tablename: sensordata columns: # ... raw data columns ... transforms: - tablename: hourly_averages materialize: true schedule: "0 * * * *" sql: > SELECT time_bucket('1 hour', tsp) AS hour, avg(temperature) AS avg_temp, avg(humidity) AS avg_humidity FROM sensordata GROUP BY hour columns: - id: hour name: Hour dataType: timestamp - id: avg_temp name: Average Temperature dataType: numeric - id: avg_humidity name: Average Humidity dataType: numeric
FieldDescription
tablenameName of the derived table
materializeIf true, the result is stored and refreshed on the schedule. If false, the transform is a view, computed on every read
scheduleCron expression for the transform’s runs: five fields (minute, hour, day of month, month, day of week), or six with a leading seconds field. */5 * * * * runs every 5 minutes, */10 * * * * * every 10 seconds. Each run refreshes a materialized result and sends the current result to the boards bound to the transform. The app’s code can also run such a tick on demand, see Refreshing on Demand
sqlSQL query that computes the transform
columnsColumn definitions for the output

Transform tables are accessible in boards and via the SDK, just like regular tables.

A transform is read exactly as its own SQL returns it: widget time windows, calendar filters and the latest mode apply to tables and are ignored for a transform. Three rules follow from that. Bound the time range inside the query (WHERE tsp > now() - interval '7 days'), order a time series newest first (ORDER BY <time column> DESC) so a row limit keeps the newest rows, and select every column a board should plot or filter by — a transform has no implicit timestamp or device columns. A single read returns at most 3000 rows, so aggregate in the query.

A transform is for what a table read cannot express: a join, a ratio, an aggregation over the current rows of several tables. It is not the way to show the current state of entities or to hide deleted records. Those are read modes of the table itself, the latest toggle and a filter on a deleted column (see below), and they work live: a widget bound to the table follows every new row within a second. A transform reaches a board only on its schedule (or when the app’s code refreshes it after its own writes, which a board’s save never triggers), so a view that picks the newest row per entity or drops deleted rows shows the same result late. When a transform does need the current state of an entity table, for example to join it with another table, select it in its SQL with DISTINCT ON and apply the deleted check there, in the order shown under Soft Deleting Records.

Shipping a transform with the app is not the only way to get one: a project member with the Data Access privilege can save the same kind of transform from the project’s data view, without any app, and so can the AI assistant. See Custom Transforms.

When a Materialized Transform Refreshes

A scheduled run refreshes a materialized transform only when a table it reads has been written since its last refresh, and at least every 10 minutes regardless. A run with nothing new to read leaves the stored result as it is: a transform scheduled every minute over a table that receives one row an hour refreshes after that row, and otherwise every 10 minutes.

A materialized transform refreshes on every run when its result can change without a write:

  • its SQL uses the current time or randomness, such as now(), CURRENT_DATE or random();
  • it calls a function that can return a different result for the same input: a volatile function, or a stable function that is not built into PostgreSQL;
  • it reads something other than app tables and materialized transforms, for example a non-materialized transform, a downsampled copy or one of the platform’s own tables.

When many transforms are due at the same moment, the platform refreshes a few at a time and the others wait their turn, so a refresh can finish later than its scheduled time. A run that comes due while the previous refresh of the same transform is still running or waiting is skipped.

Refreshing on Demand

The app’s own code can run a transform’s tick without waiting for its schedule: the SDK’s refresh_transform / refreshTransform / RefreshTransform refreshes the materialized result (a plain view is only read) and sends the current result to the boards bound to the transform, so a board shows a batch the app has just written at once. The same rules apply as to a scheduled run: the refresh is skipped — and reported as skipped — when no table the transform reads was written since the last one, a call that arrives while a refresh of the same transform runs waits for the one queued behind it, and at most one refresh a second starts per transform. The SDK’s table writes are committed before they return, so a write followed by a refresh never misses the write. Only the app’s own containers can refresh its transforms; a board receives the rows, and an app that consumes another app’s data reads its transforms but cannot refresh them. Transforms saved from the project’s data view belong to no app and run on their schedule only. See refreshTransform / refresh_transform / RefreshTransform.

Tracking the Latest State of an Entity

For tables that represent the current state of real-world entities — machines, assets, production orders — IronFlock supports a pattern called latest-state tracking.

Instead of overwriting a row when something changes, you always append a new row. You declare which columns identify a unique entity, and IronFlock derives the most recent row per entity whenever the table is read. This gives you a full history of every change while also making it easy to query only the current state.

Enable it on a table with maintainLatestFlagFor:

- tablename: machineform maintainLatestFlagFor: ['machinename'] columns: - id: tsp dataType: timestamp - id: machinename dataType: string - id: machinetype dataType: string - id: active dataType: boolean - id: description dataType: string

maintainLatestFlagFor takes a list of columns that together identify a unique entity. Nothing is written into the row itself: IronFlock indexes the table by that entity key plus the timestamp, and picks the newest row per entity at query time. A row that arrives late or out of order can therefore never leave a stale marker behind.

An entity table holds one row per entity and timestamp. A row that repeats both the entity key and the tsp of a stored row is rejected, and so is a batch in which two rows do: the SDK’s write fails with URI_ENTITY_KEY_CONFLICT, and the app’s error-logs table records the refusal. Give every version of an entity its own tsp. A row whose entity key column is empty (NULL) belongs to no entity: it is never rejected as a repeat, and latest reads leave it out.

Updating an Entity with a Partial Row

A row appended to an entity table is a new version of that entity — and it does not have to be complete. Columns the row does not provide are inherited from the entity’s previous latest row, so updating a single field means publishing just the entity key, a timestamp and that field:

The new row’s column isThe stored version holds
provided (0, false and "" count as provided)the provided value
explicitly nullNULL — the column is cleared
absentthe value from the previous latest row

To empty a column, send it as null (None in Python, nil in Go, null in C#). Leaving it out keeps the previous value. Watch for code that leaves a field out without meaning to: a JavaScript property set to undefined, for example, is dropped from the payload. The SDK reference lists these cases per language.

Two details are worth knowing:

  • A row that provides every column skips the previous-row lookup entirely, so complete rows stay exactly as cheap as they have always been — keep sending complete rows on high-frequency paths.
  • Inheritance respects time: a row that arrives with an older timestamp (a backfill) inherits only from rows at or before its own tsp, never from newer ones.

Tables without maintainLatestFlagFor keep plain append semantics: a column the row does not provide is stored as NULL.

To query only current machine states:

SELECT DISTINCT ON (machinename) * FROM machineform ORDER BY machinename, tsp DESC

To view the full history of a specific machine:

SELECT * FROM machineform WHERE machinename = 'Assembly-Line-01' ORDER BY tsp

You rarely write that query by hand, and you should not store it as a transform. Widgets on a board that connect to this table have a latest toggle in their filter settings, so users always see current values without any extra work. The toggle is live: the widget receives every new row of the table as it arrives and replaces the entity’s previous row with it, so a change shows within a second of being written, while the table keeps every version. Nothing is recomputed on a schedule, which is all a transform would add. From the SDK, request the same mode by adding {"latest": true} to filterAnd — see getHistory.

Upgrading from latest_flag: earlier versions of IronFlock stored a physical boolean column called latest_flag. That column no longer exists — the current state is derived in SQL instead, which keeps it correct when rows arrive out of order. Existing boards and SDK calls that filter on latest_flag = true keep working: IronFlock recognises them and applies the latest-state mode. New code should use the latest toggle or the {"latest": true} filter entry.

Changing the Entity Key

Once an app version is released, the entity key of each of its tables is fixed. Update Data Structure in the Cloud IDE’s side panel and the build of a new release refuse a data-template.yml that changes the entity key of a table the app’s newest release has:

  • adding a column to maintainLatestFlagFor, removing one, replacing one or changing their order;
  • adding maintainLatestFlagFor to a table that had none, or removing it.

Such a change would silently change what the table returns in every project that installed the app. A column new to the key is empty in every existing row, and latest reads leave out rows with an empty key column, so all existing entities would disappear. A column removed from the key can merge entities whose rows share a timestamp, while the table can hold only one row per entity and timestamp.

To use a different entity key, create a new table with the new key and move the data into it, for example from your app’s code. Moving the data is up to the app. A table that no release has yet can still change its entity key while you develop. An app version released before this rule existed can still change an entity key: a project that updates to it refuses the data structure update and keeps the previous one.

Rows That Repeat an Entity and Timestamp

Tables written before IronFlock enforced one row per entity and timestamp can still hold repeats, and so can a development table whose entity key you changed. When the data service sets up such a table, at its start or after a data structure update, it moves every row of each repeated group to a table named <table>__entitykey_dupes in the same data backend and puts one row of each group back. From then on, the table rejects repeats. An entry in the app’s error-logs table says how many rows were moved.

The new table keeps each moved row as it was, plus two columns: quarantined_at, the time of the move, and restored, which marks the row that went back. Which row goes back is arbitrary, as it already was for latest reads. Query it in the query console of the project’s data view as databackend_<key>.<table>__entitykey_dupes. If the original table is private, the new table is private too. If the rows cannot be moved, the error-logs entry says why, and the data service tries again at its next start.

Soft Deleting Records

IronFlock’s append-only model means records are never physically deleted. Instead, use a deleted boolean column to mark a record as removed. This preserves the full audit trail while hiding deleted records from dashboards.

Add a deleted column to any entity table:

- id: deleted name: Deleted dataType: boolean

When a user deletes a record (for example via a form on the board), your app publishes a new row for that entity with deleted: true. Combined with maintainLatestFlagFor, this new row becomes the latest state. Later partial rows inherit the deleted marker like any other column, so an update that does not mention deleted neither resurrects the entity nor hides it.

To query only active (non-deleted) current records:

SELECT * FROM ( SELECT DISTINCT ON (machinename) * FROM machineform ORDER BY machinename, tsp DESC ) latest WHERE deleted IS NULL OR deleted = false

The deleted check runs after the latest row per machine has been picked. That order matters: filtering deleted rows out first would make the previous, non-deleted row resurface as the current state.

Board widgets and the SDK apply the same order automatically — combine the latest toggle (or {"latest": true}) with a deleted filter and you get exactly this behaviour. Deleted records disappear from the dashboard immediately after the form is submitted, but remain in the database for history and audit purposes.

On the board, a delete form deletes a record: a form whose submit button says Delete and whose hidden checkbox field writes true into the deleted column, next to the record’s key (see Deleting Data). The widgets that list the records filter the table itself. No view or transform is needed to clean the list, and one would show a deletion only on its next scheduled run.

Sharing Data with Other Apps

Your data backend is private to your app: no other app installed in the project can see your tables. Two optional keys in data-template.yml change that.

To read another app’s data, list the apps you want to read from in a top-level consumes: section — next to data:, not inside it:

consumes: - app: machine-monitor reason: "Computes OEE from the monitor's machine state and counter streams" data: tables: - tablename: oee_results columns: # ... your app's own tables, as usual

app is the technical name of the providing app, or "*" (quotes required) for every app in the project. reason is shown to the user in the consent dialog — the declaration alone grants nothing until they approve it.

To keep individual tables back, mark them private: true. Everything you define is shareable by default; a private table or transform never appears in the catalog other apps see.

data: tables: - tablename: measurements # shared (default) columns: [ ... ] - tablename: calibration_state # internal — never visible to other apps private: true columns: [ ... ]

Access is read-only, granted per project by the user, and revocable at any time. See Consuming Data from Other Apps for the full model and the SDK calls that read a provider’s history and live streams.

Privileged Access

An app’s containers run isolated from the device, with Docker’s default rights. Some apps need more: a serial port or USB adapter, GPIO, a CAN or Bluetooth interface, the host network. That is privileged access. An app with privileged access can control the whole device, including other apps and their data, so your app has to request it, and the user decides whether it gets it.

To request it, add a top-level privileged: section with a reason — next to data:, not inside it:

privileged: reason: "Reads the PLC over the serial port /dev/ttyUSB0" data: tables: # ... your app's own tables, as usual

reason is required. It is shown to the user in the consent dialog, so write it for a person deciding whether to trust your app.

An app without tables writes data: {} next to privileged: a data-template.yml without the data: key fails to publish.

What the user grants depends on how the app is built:

  • A single-image app (a Dockerfile only) runs privileged when the user grants it: with all Linux capabilities, the device’s /dev and the host’s D-Bus. Without the declaration it runs without host devices, extra capabilities and the host D-Bus; the release still publishes, and its build log warns about this.
  • A Docker Compose app gets exactly the settings its docker-compose.yml asks for, and the consent dialog lists them. If the compose file uses any of the settings below, the app must declare privileged, or publishing the release fails with an error that names the service and the setting. Declaring privileged when the compose file uses none of them fails as well.

These compose settings count as privileged:

SettingPrivileged when
privilegedany value except false
cap_add, devices, device_cgroup_rulesit has an entry
security_optit has an entry other than no-new-privileges
network_mode, pid, ipc, uts, userns_mode, cgroupset to host or container:…
cgroup_parentit is set
volumes of a serviceit mounts a host path (/…, ~/…, C:\…), a path outside the app’s folder (../…) or a named pipe. Read-only /etc/localtime and /etc/timezone are fine.
volumes_fromit names container:…
top-level volumesa volume has driver_opts, external or a fixed name
top-level networksa network sets a driver other than bridge (such as macvlan, ipvlan or host) or driver_opts, is external or has a fixed name
top-level secrets, configsan entry reads a file outside the app’s folder

Named volumes, networks with the default bridge driver, bind mounts inside the app’s folder (./config:/config), published ports and GPU reservations (gpus, deploy.resources.reservations.devices) are not privileged.

A ${VAR} placeholder in one of these settings is allowed and counts as privileged, so it needs the declaration too. This is how an app parameter can let users choose the serial port:

services: plc: build: . devices: - "${SERIAL_PORT}:/dev/ttyS0"

A compose file that uses include, or extends with a file, fails to publish, with or without the declaration: a release carries only docker-compose.yml. Merge the other files into it.

Request no more than the app needs. If it needs one serial port, write a compose file with a single service and devices: [/dev/ttyUSB0] instead of a single image or privileged: true. The user sees exactly what the app gets, and the app cannot do more than that.

  • At install, the user sees your reason and, for a compose app, the list of settings, and switches privileged access on or off. Declining never blocks the install.
  • At an update, if the new version requests privileged access and the project has not granted it, the update dialog asks. Declining still updates the app; the new version then runs without privileged access.
  • Afterwards, privileged access is a switch in the project’s app settings, where users can revoke or grant it at any time. Running devices restart the app with the new setting.

A development run from the Cloud IDE follows the declaration and has no switch: the development build is privileged when .ironflock/data-template.yml declares privileged, and runs without privileged access when it does not. If the compose file uses privileged settings without the declaration, the development build fails with the same error as a publish.

Without the Grant

The app starts anyway. A single-image app runs with Docker’s defaults: no /dev, no extra capabilities, no host D-Bus. For a compose app, the device agent removes the privileged settings from the compose file before it starts the app, and writes to the app’s log which settings it removed from which service. Make sure your code reports what it cannot reach, such as a serial port it cannot open, instead of crashing.

Older Releases

Releases published before apps had to declare privileged access count as requesting it, and existing installs keep it, so these apps run as before. Users can revoke it in the project’s app settings as for any other app. From your next release on, declare privileged if the app needs it.

Last updated on