PostgreSQL integration

Start workflows from new or changed rows and read and write a client's PostgreSQL database from inside a workflow.

What it does

The PostgreSQL integration lets your agency react to and change a client's PostgreSQL database from a workflow. Connect the database once with a connection string or discrete host, port, user, password, and database. Triggers start a workflow when a row is added or changed, when your own query returns a new row, or when the schema gains a table or column. Actions find rows without writing SQL, get one row by its primary key, run a parameterized query, insert a row, update or delete rows by an equality filter, and upsert a row on a conflict key. Connection details are encrypted at rest, every value you pass is bound as a parameter rather than spliced into the SQL, and results are capped so a large read cannot overwhelm a run.

Connect a PostgreSQL database

Open your workspace in TaskJuice and add a PostgreSQL connection on the node that uses it.
Paste a connection string, for example postgresql://user:password@db.example.com:5432/appdb?sslmode=verify-full. The TLS posture is read from the connection string; sslmode=verify-full validates the server certificate and hostname.
Or fill in the host, port (5432 by default), user, password, and database separately. Discrete connections default to verifying the full certificate chain.
Save the connection. The password and connection string are stored encrypted and are never shown again or written to run output.

Provision a least-privilege database user scoped to the tables the workflow touches, and expose the database on a public host the workflow can reach over TLS. Private and internal addresses are blocked. A user that only needs triggers and reads can be granted SELECT alone.

Network access. TaskJuice reaches the database from more than one network. Workflow steps in a published run connect from a fixed set of outbound addresses. Triggers, which check the database on an interval, and the editor's Test step connect from cloud infrastructure that has no fixed IP addresses. If the database only accepts listed addresses, triggers and editor tests time out, so a step that fails its editor test with a connection error can still work in a published run. Unless you have a reason to restrict it, allow access from anywhere (0.0.0.0/0) and let a long, random password and TLS protect the database.

Triggers

PostgreSQL has no way to call out to a workflow when data changes, so each trigger checks the database on an interval you choose (every 5 minutes by default, as often as every minute). Each check runs one small query. Rows, tables, and columns that already exist when you turn a trigger on never fire; only changes after that point do. Every check that finds something starts one run whose items array holds everything found, so add a Loop node to handle the items one at a time.

postgresql/new-row fires when a row is added to a table. Point it at a column whose value only grows for new rows, usually a serial or identity primary key.
postgresql/new-or-updated-row fires when a row is added or changed. Point it at a timestamp or timestamptz column your schema sets on every write, for example an updated_at timestamptz column that a BEFORE UPDATE trigger sets to now(), with a unique ID column as the tie-breaker.
postgresql/new-row-custom-query fires when your own SELECT returns a row it has not returned before. Name a unique column so a row fires once even if its other values change later.
postgresql/new-table fires when a table or view is created in the connection's current schema (usually public).
postgresql/new-column fires when a column is added to a table in that schema. Name a table to watch just that one, or leave it empty to watch them all.

Actions

postgresql/find finds rows in a table without writing SQL. Filter with conditions such as status = open or total >= 100 (operators =, !=, >, >=, <, <=, like, not-like, in, not-in, is-null, is-not-null), match all or any of them, pick the columns to return, sort, and cap the row count.
postgresql/get-row gets one row by its primary key, for example { "id": 42 }; for a composite key, give every key column. It returns found and the row, or found: false and row: null when no row has that key. A key that matches more than one row fails the step rather than picking one.
postgresql/query runs a SQL statement with $1, $2, … placeholders and a matching params array, returning the result rows and the row count.
postgresql/insert inserts a row from a column-to-value map and returns the inserted row (with its generated id and defaults) and the row count.
postgresql/update updates the rows matching an equality filter, setting new column values, and returns the affected row count.
postgresql/upsert inserts a row or, on a conflict with the columns you name, updates it (INSERT … ON CONFLICT DO UPDATE). It returns the inserted or updated row, including when the row already existed and nothing changed.
postgresql/delete deletes the rows matching an equality filter and returns the deleted row count.

Insert and Upsert return the whole written row, every column as stored, so a later step can use its id or any default the database filled in, for example {{ $steps.insert_1.data.rows[0].id }}. The rows come back empty when the database user has no SELECT on the table; the write itself still happens. When every value you upsert is one of the conflict columns, Postgres reads an existing row back through a no-op update, so the table's row-level UPDATE triggers run on that row, and its statement-level UPDATE triggers run on every such upsert. If that update is refused (the user has no UPDATE on the table, or an UPDATE trigger or a constraint rejects it), the upsert still runs as an insert-if-missing, and the rows come back empty, for a new key too.

Write a safe query

Always pass values through params, never by building the SQL string yourself:

sql:    SELECT id, email FROM customers WHERE status = $1 AND created_at > $2
params: ["active", "2026-01-01"]

Table and column names in the structured actions and triggers are validated against a strict allowlist and quoted for you, so a name containing a quote, semicolon, or whitespace is rejected before the statement runs.

Known limitations

Triggers check on an interval rather than reacting instantly. Keep the watched columns indexed; new-or-updated-row reads best with a composite index on the timestamp and ID columns.
new-or-updated-row tracks its position to the millisecond and reads rows that share a millisecond in ID order, so every row is delivered exactly once even when one bulk UPDATE stamps thousands of rows with the same now(). A backlog spread over time is read straight from the composite index, a few milliseconds per check however large the backlog. Rows that share a single millisecond are scanned together on every check, and keep being scanned after they have all been delivered, until a newer row moves the trigger's position past that millisecond: measured at about a third of a second per million such rows. A date column works as the timestamp column, at one-day resolution.
The ID column of new-row and the timestamp and ID columns of new-or-updated-row must never be NULL. A row with a NULL in one of them is never delivered, and it can stop the trigger with a configuration error. That happens when any row has a NULL timestamp at the moment you turn new-or-updated-row on, when the row with the newest timestamp has a NULL ID, when any row has a NULL ID at the moment you turn new-row on, or when new-row is turned on over an empty table and a row with a NULL ID then arrives. A NULL ID on an older row of new-or-updated-row does not stop the trigger; that row is simply never delivered. Declare the columns NOT NULL.
Timestamps without a time zone (timestamp, date) are handled as plain wall-clock values: the trigger and the actions read and compare them exactly as stored, with no time-zone conversion, so values in a daylight-saving changeover hour behave like any other.
The custom-query trigger re-runs your whole query on every check. Keep it selective with a WHERE and a LIMIT of a few hundred rows.
Find and query return at most 1,000 rows and around 5 MB per call; when a read is clipped the result sets truncated to true so a workflow can detect it. Add a LIMIT and a WHERE clause to keep reads bounded.
A statement that runs longer than 30 seconds is cancelled by the database. Keep queries indexed and selective.
Connections are short-lived: each action and each trigger check opens and closes its own connection. Point the connection at a pooler (such as PgBouncer) if your database has a low connection limit.