database-service is the platform's system of record — durable, structured storage for everything your playbooks need to keep. It is a thin, safe gateway in front of a real database — PostgreSQL by default, with SQLite, MySQL/MariaDB, MongoDB and Microsoft SQL Server available too — so the data your automations produce (invoices, deployments, customers, audit trails) survives restarts, is queryable later, and can be read back by other steps in the same workflow. You describe what to store and read in short, readable YAML; the daemon does the database work for you. You never write raw SQL. And when one database is not enough, you register more — each under a short alias — and address them all with the same verbs.
Safe by design. Because you express storage as structured YAML rather than SQL strings, an entire class of mistakes — SQL injection, accidental table-wide deletes — simply cannot happen. The daemon validates every identifier, binds every value as a parameter, and refuses an update or delete that has no filter. More on this below.
Binions is event-driven: daemons talk to each other over a fast internal event bus, and a playbook addresses a daemon by naming a lowercase verb. database-service is the daemon that owns persistent, structured data. When a playbook step asks it to write a row, run a query, or update a record, the daemon performs that operation safely against your database — parameterised SQL for the relational engines, the equivalent document operation for MongoDB — and returns the result as an event the rest of your workflow can use.
The mental model is a small, dependable set of table operations:
The big idea. Three primitives cover almost everything: declare a table, write rows to it, and query rows back. The other operations are conveniences built on the same foundation. If you can describe a row as a set of fields, database-service can store it — no schema migrations to hand-write, no SQL to learn.
| What it is | Durable, structured storage for playbooks — the platform's system of record |
| Databases | PostgreSQL (default), SQLite, MySQL/MariaDB, MongoDB, Microsoft SQL Server — chosen inline per operation with backend:, or registered once and used everywhere as connection: <alias> |
| Playbook prefix | database. |
| Operations | 11 — register_table, register_connection, write, query, update, upsert, delete, transaction, exists, count, aggregate |
| How you query | Structured YAML only — tables, columns, joins, 16 filter operators with or: alternatives, grouping, ordering, limit/offset, distinct, single-row reads — no raw SQL strings |
| Safety | Validated identifiers, parameterised values, mandatory filter on update/delete |
| Service | binions-database.service with a dedicated redis-binions-database.service |
| Health endpoint | 127.0.0.1:9102 — /health/live, /health/ready, /metrics |
| Package | binions-database — one of the 13 binions in a set |
One vocabulary, five engines. The same eleven operations work against any of them — you choose the engine per operation, and the daemon handles the dialect differences (placeholders, quoting, identity columns, the right form of "insert or update") for you. Leave the connection fields out entirely and you get PostgreSQL, the platform's default store, so any playbook written without them just works.
| Database | Select with | Good for |
|---|---|---|
| PostgreSQL | nothing — the default (or backend: postgres + dsn: for a second PostgreSQL server) | The platform's own durable store, installed with Binions. Your system of record. |
| SQLite | backend: sqlite + path: | A serverless, single-file database — zero extra installs. Ideal for small or edge deployments. |
| MySQL / MariaDB | backend: mysql + dsn: | Reading from or writing to an existing MySQL or MariaDB server you already run. |
| MongoDB | backend: mongo + dsn: | A document store. Tables become collections and rows become documents; the same verbs and filters apply, each row still gets an auto-assigned id, and columns is optional when registering a table — collections are schemaless. Joins and distinct: are refused with a clear error. |
| Microsoft SQL Server | backend: mssql + dsn: | Integrating with a Microsoft SQL Server estate — the daemon speaks T-SQL for you. |
All five engines ship in the standard package — there is nothing to rebuild or enable. There are two ways to point an operation at an engine. Inline: put backend: and a path: (SQLite) or dsn: (the server-based engines) right on the step — fine for a one-off. By name: register the connection once and refer to it everywhere else as connection: <alias> (next section). backend: itself is optional: with no connection fields at all you get the platform's own PostgreSQL, and backend: postgres with a dsn: is how you reach a second PostgreSQL server elsewhere — the built-in one needs no DSN.
# The same write, sent to a serverless SQLite file instead of PostgreSQL
- run: database.write
with:
backend: sqlite
path: edge.db
table: clicks
row: { source: "landing-page" }When several playbooks share the same reporting database, repeating a DSN — credentials included — on every step is exactly the kind of duplication provisioning playbooks exist to remove. database.register_connection declares a connection once, under a short alias:
alias — the name every other step will use: 1–64 characters of lowercase letters, digits, - and _.backend plus dsn (or path for SQLite) — the same fields you would have written inline. Secrets belong in ${secret.*} references, never in the YAML itself.The daemon connects eagerly, the moment the step runs — so a wrong DSN surfaces at provisioning time, not in the middle of a business workflow — and answers with Fact.Database.ConnectionRegistered carrying alias, backend and replaced (true when the alias was re-registered over an existing one). From then on, every database operation accepts connection: <alias>. Naming a connection and spelling one out are mutually exclusive: a step carries connection: or the inline backend/dsn/path fields, never both.
name: register-db-connection
description: Provisioning — named sqlite connection + audit table + smoke row.
trigger:
event: Fact.System.Boot
steps:
- id: conn
run: database.register_connection
with:
alias: local-audit
backend: sqlite
path: "audit.db"
- id: table
run: database.register_table
with:
connection: local-audit
name: audit_log
columns:
- { name: source, sql_type: text, nullable: false }
- { name: note, sql_type: text, nullable: true }
- id: smoke
run: database.write
with:
connection: local-audit
table: audit_log
row: { source: "provision", note: "named-connection smoke row" }
- id: check
run: database.query
with:
connection: local-audit
table: audit_log
single: first
order_by: id
order_dir: DESCThe alias registry lives in memory and is rebuilt by your provisioning playbooks on every boot — that is exactly what Fact.System.Boot provisioning is for — so connection credentials never persist anywhere except your secrets files. Re-registering an alias simply replaces it, which makes the playbook above safe to leave in place.
In a playbook you address the daemon with the lowercase verb form run: database.<operation>. There are eleven operations, grouped by what they are for: two provision — a table, and a named connection — and the rest read and write rows.
| Operation | What it does | Arguments |
|---|---|---|
database.register_table | Declare a table and its columns, creating it if it does not exist. Idempotent — safe to run on every boot (declared indexes are reconciled on every registration too). | name, columns (each { name, sql_type, nullable? }; optional on the schemaless MongoDB backend, at least one required elsewhere); optional indexes |
database.register_connection | Declare a named database connection under an alias, connecting eagerly. Re-registering an alias replaces it. | alias, backend, dsn or path |
database.write | Insert a single row. | table, row |
database.query | Read rows back, optionally joined to related tables, filtered, ordered, paged, and de-duplicated. Returns a rows array — or a single flat row when you ask for exactly one. | table (required); optional columns, join, where, order_by, order_dir, limit, offset, distinct, single |
database.update | Change the columns of matching rows. The filter is mandatory. | table, where (required), set |
database.upsert | Insert a row, or update it if a matching one already exists (“insert or update”). | table, row, conflict key |
database.delete | Remove matching rows. The filter is mandatory. | table, where (required) |
database.transaction | Perform several writes as one atomic unit — all succeed together, or none are applied. A later operation can reference a value an earlier write generated (below). | ops — a list of write/update/delete operations, each with a kind |
database.exists | Return whether any row matches a filter — a cheap yes/no check. | table, where |
database.count | Return how many rows match a filter. | table; optional where |
database.aggregate | Group rows and total them — sums, averages, and counts per category, computed by the database. | table, aggregates (required); optional group_by, having, where, order_by, limit, offset |
Every operation also accepts connection: <alias> (a connection registered as above) or the inline backend/dsn/path fields — one or the other, never both; with neither, the platform's own PostgreSQL is used. And queries are structured, not free-form SQL: a query is built entirely from the fields in the table — there is no raw sql: string field. Always use the structured form shown below.
A read is more than an exact match. A where filter is a map of column.operator keys, with the same grammar — and the same sixteen operators — on every operation that filters: query, exists, count and aggregate exactly as on update and delete. A bare column name (no operator suffix) means equals. The operators: comparisons (.eq .ne .gt .ge .lt .le), text tests (.contains .startswith .endswith), sets (.in .not_in), null checks (.is_null .is_not_null), and time windows (.within .before .after). database.query can join a related table with an inner or left equi-join and alias its columns; filter keys may then be table-qualified (orders.status.eq) to say which side you mean — and a qualified key requires an explicit operator, so orders.status.eq: paid is right where a bare orders.status: would be ambiguous. database.aggregate groups rows and totals them on the server — all still structured YAML, never raw SQL.
# Join, then filter and sort (inner keeps matched rows; left keeps all base rows)
- run: database.query
with:
table: orders
join:
- table: customers
type: left
on: { left: orders.customer_id, right: customers.id }
columns:
- { col: orders.id, as: order_id }
- orders.total
- { col: customers.name, as: customer }
where: { orders.status.eq: paid, created_at.within: 7d }
order_by: orders.total
order_dir: DESC
---
# Group and total on the server (revenue per category, biggest first)
- run: database.aggregate
with:
table: orders
group_by: [category]
aggregates:
- { fn: sum, col: amount, as: revenue }
- { fn: count, col: "*", as: order_count }
having: { revenue.gt: 1000 }
order_by: revenue
order_dir: DESCWhen the alternatives will not fit one AND-ed map, add an or: key — a list of branches, each branch itself a small AND-map. The whole filter then reads: every top-level condition must hold, and at least one branch must match. It is the same or: shape playbook trigger filters use, so you only learn it once.
# Open invoices that are either long overdue OR large EUR amounts
- run: database.query
with:
table: invoices
where:
status.eq: open # every top-level condition must hold...
or: # ...AND at least one branch must match
- overdue_days.gt: 30
- { amount_gross.gt: 10000, currency.eq: EUR }
limit: 200Reads page and shape cleanly, too. limit defaults to 100 and caps at 10,000; offset skips that many rows first (on MongoDB it becomes a cursor skip), and on aggregate the same limit/offset pair pages through grouped results. distinct: true on a query collapses duplicate result rows — a real SELECT DISTINCT on the SQL engines; MongoDB refuses it with a clear error, just as it refuses joins.
Very often you expect exactly one row — the latest setpoint, the customer record — and unpacking a one-element rows array is needless friction. Add single: to a query and the daemon hands the row back flat on the result fact, under row:
single: first — take the first row of the (ordered) result.single: one — assert there is exactly one match; more than one row fails the step.# "first" — the newest row wins: order it, then take the top one
- id: target
run: database.query
with:
table: setpoints
order_by: id
order_dir: DESC
limit: 1
single: first
# "one" — there must be exactly one matching row
- id: stock
run: database.query
with:
table: warehouse_stock
where:
item: widget-a
single: oneA later step then reads columns directly — ${steps.target.row.value}, ${steps.stock.row.qty}, or ${prev.row.qty} if it is the very next step — and feeds them straight into a mail, a webhook, or an industrial write.
The classic parent-and-children insert — an order and its lines, where each line needs the order's freshly generated id — is atomic by nature: either everything lands or nothing does. Inside database.transaction, a value in a later operation's row, set or where may reference an earlier write as "@tx:<op>.<column>", where <op> is the 0-based position of that write in the ops list. The reference is resolved inside the transaction, against the row that operation returned — so the child rows see the parent's real key, and a rollback takes them all away together.
name: order-with-lines-tx
description: |
Business — POST /in/orders {customer, sku, qty}: parent and child rows
land atomically; the child's order_id comes from op 0's RETURNING id.
trigger:
event: Fact.Http.Received
filter:
route.eq: orders
steps:
- id: tx
run: database.transaction
with:
ops:
- kind: write
table: orders
row:
customer: ${trigger.body.customer}
- kind: write
table: order_lines
row:
order_id: "@tx:0.id"
sku: ${trigger.body.sku}
qty: ${trigger.body.qty}The rules are strict on purpose. @tx: references work on PostgreSQL and SQLite; MySQL, SQL Server and MongoDB reject a transaction that contains them with a clear error rather than guessing. A reference that points forward, or at an operation that is not a write, is rejected up front — before the transaction touches the database. And if you ever need a literal value that begins with @tx:, escape it as "@@tx:".
Timestamps interpolate into playbooks as ISO-8601 strings — and that is fine. The daemon keeps track of which of your columns are timestamp columns, and whenever a string is bound against one, it is bound as a real timestamp in the engine's own dialect — on the values of every mutating operation (write, upsert, update, delete) and in every where comparison on the read side (query, exists, count, aggregate), bare and table-qualified keys alike. So created_at: ${trigger.received_at} lands as a proper timestamptz, a created_at.before: filter compares as time rather than text, and text columns still receive the literal string untouched.
When you register_table, the daemon adds two columns automatically so you never have to declare them:
id — an auto-incrementing primary key (a unique number per row), assigned for you on insert. MongoDB collections get the same numeric id, so your playbooks behave identically whichever engine is behind them.created_at — a timestamp set to the moment the row was written.Both creation of the table and its indexes use an “if it does not already exist” rule, so re-running the same provisioning step on a later boot is harmless — it confirms the table rather than failing or duplicating it.
The most common job: an upstream step has extracted an invoice, and you want to keep it. write takes the target table and the row to insert — the id and created_at columns are filled in for you.
name: store-extracted-invoice
trigger:
event: Fact.AI.Extracted
filter: { document_type.eq: invoice }
steps:
- run: database.write
with:
table: invoices
row:
supplier: "Acme Components Ltd"
invoice_number: "INV-2026-0481"
amount_gross: 1240.50
currency: GBP
due_date: "2026-06-30"That appends one row to the invoices table. Every value is bound as a parameter, so even a supplier name containing quotes or punctuation is stored exactly as given, with no risk of it being mistaken for part of a command.
To read data, describe the shape of the result rather than writing SQL: which table, which columns you want, how to order them, and how many to return. Here we fetch the twenty most recent deployments:
name: recent-deployments-report
trigger:
event: Fact.Schedule.Fired
filter: { name.eq: deploy-digest }
steps:
- run: database.query
id: recent
with:
table: deployments
columns: [service, version, deployed_at]
order_by: deployed_at
order_dir: DESC
limit: 20The query returns its result as a rows array on the event bus. A later step reads it straight out with ${steps.recent.rows} — for example, to format the list into a message and post it to your team.
Before you write to a table, declare it. A provisioning playbook does this once, triggered by the platform booting and filtered so it runs only as the workflow engine starts. Because register_table is idempotent, it is safe to leave this in place and let it run on every boot.
name: provision-invoices-table
trigger:
event: Fact.System.Boot
filter: { component.eq: playbook-service }
steps:
- run: database.register_table
with:
name: invoices
columns:
- { name: supplier, sql_type: text }
- { name: invoice_number, sql_type: text }
- { name: amount_gross, sql_type: numeric }
- { name: currency, sql_type: text }
- { name: due_date, sql_type: timestamptz }Column types are case-insensitive and accept the usual SQL synonyms — text/TEXT/varchar, int/integer, bigint, double/float, boolean, jsonb/json, timestamptz/timestamp, uuid, numeric/decimal — write whichever spelling reads best.
You declare only the columns specific to your data — the id primary key and the created_at timestamp are added for you. On the SQL engines a table needs at least one column of its own, and the daemon says so loudly if the list is empty; only MongoDB, being schemaless, lets you omit columns entirely. Keep table definitions in their own provisioning playbooks, separate from the workflows that use them, so your business logic never carries setup concerns and adding a column later is a one-line change in one place. For the difference between these two kinds of playbook, see Provisioning vs. business playbooks.
Letting a YAML file drive a database could be dangerous in the wrong design. database-service is built so that the obvious accidents are impossible by construction, not merely discouraged:
update or a delete without a non-empty where is refused outright. There is no way to accidentally rewrite or wipe an entire table with a single careless step.The payoff for you is the power of a real database without the foot-guns. The same guard rails apply whether a step was written by you, generated by an AI step, or filled in from incoming data — the daemon validates and binds every time, so a hostile or malformed input cannot turn into a destructive query. And when an operation cannot honestly do what you asked — a single: read that finds nothing, a distinct: or @tx: reference on an engine that does not support it — it fails loudly at the source rather than guessing.
Configuration lives in the daemon's application.toml. The default PostgreSQL connection and the daemon's dedicated Redis instance are set here; the other engines are addressed in your playbooks — inline with path: or dsn:, or registered once as a named connection — so they need no global configuration block.
# /opt/binions/database-service/config/application.toml
# This daemon's dedicated Redis instance (state, outbox, idempotency)
[redis]
host = "127.0.0.1"
port = 6392
password_file = "/etc/binions/secrets/database-redis.pass"
# The default PostgreSQL database where rows are stored
[postgres]
host = "127.0.0.1"
port = 5432
database = "binions"
user = "binions_app"
password_file = "/etc/binions/secrets/database-pg.pass"
# Local health and metrics HTTP endpoint
[healthcheck]
listen_addr = "127.0.0.1:9102"
# Optional: export traces to a collector for end-to-end visibility
# [otel]
# endpoint = "http://127.0.0.1:4317"path: or a MySQL / MongoDB / SQL Server dsn: can ride on the step that uses it — opened and pooled on demand — or be registered once with database.register_connection, which connects eagerly and keeps its alias registry in memory; your boot-time provisioning playbooks rebuild it on every start.password_file, and connection strings registered from playbooks keep their credentials in ${secret.*} references — in line with how every daemon handles credentials. See Secrets & credentials.Every operation reports its outcome as an event on the internal bus, so other playbooks and your monitoring can react to what the database did. The one your workflows read most is the query result, which carries the rows you asked for.
| Event | Meaning |
|---|---|
Fact.Database.QueryResult | A query completed. Carries a rows array — a later step reads it with ${steps.<id>.rows} — and a single: read adds the matched row flat under row. |
Fact.Database.Inserted | A row was inserted. |
Fact.Database.Updated / Fact.Database.Upserted | Matching rows were updated, or inserted-or-updated. |
Fact.Database.Deleted | Matching rows were removed. |
Fact.Database.CountResult / Fact.Database.ExistsResult | The answer to a count or an exists check. |
Fact.Database.Aggregated | An aggregate finished — the grouped totals ride back just as query rows do. |
Fact.Database.TableRegistered | A table was created or confirmed to exist. |
Fact.Database.ConnectionRegistered | A named connection was registered. Carries alias, backend, and replaced (true when an existing alias was replaced). |
Fact.Database.TransactionCompleted | A multi-step transaction committed as one unit. |
The defining trick is that a query's result is itself an event. Because the rows ride back on the bus under the step's id, the next step in the same playbook can consume them directly — no temporary files, no second round-trip. For the full anatomy of the envelope every event shares, see The event catalog.
Like every binion, database-service runs as its own hardened systemd service under a dedicated, unprivileged databasesvc user, alongside its own Redis instance. When it uses the default PostgreSQL backend it also depends on PostgreSQL being available — part of the platform's shared infrastructure, covered in Shared infrastructure. Install the package and bring the units up together:
# Install the package and start both units
sudo apt install binions-database
sudo systemctl enable --now redis-binions-database binions-database
# Check status
systemctl status binions-databaseThe service is a notify-type unit with a watchdog, so systemd knows when it is truly ready and restarts it on failure. Its readiness deliberately stays red until it can actually reach its default database — so a green /health/ready is a positive signal that the database connection is working, not just that the process is alive.
# Liveness, and readiness (ready turns green only once the database is reachable)
curl -s http://127.0.0.1:9102/health/live
curl -s http://127.0.0.1:9102/health/ready
# Prometheus-style metrics
curl -s http://127.0.0.1:9102/metricsIf readiness stays red. A daemon that is alive but never ready almost always means it cannot reach the database — a stopped PostgreSQL, the wrong host or port, or a password file it cannot read. Start there. The full checklist is in Database & Redis issues.