PostgreSQL Trigger

The PostgreSQL trigger allows you to initiate workflows based on database changes using PostgreSQL's LISTEN/NOTIFY mechanism. It can automatically set up database triggers to watch for INSERT, UPDATE, and DELETE operations on specific tables.

Installation

pnpm add @stepflowjs/trigger-postgres

Usage

import { PostgresChangeTrigger } from "@stepflowjs/trigger-postgres";

const trigger = new PostgresChangeTrigger({
  connectionString: process.env.DATABASE_URL,
  channel: "order_changes",
  table: "orders",
  operations: ["INSERT", "UPDATE"],
});

await trigger.start(async (event) => {
  console.log("Database change detected:", event.data);
  await stepflow.trigger("process-order", event.data);
});

How it Works

When you specify a table in the configuration, the trigger will:

1. Create a PostgreSQL function that sends a NOTIFY event with the row data as JSON.
2. Create a database trigger on the specified table that calls this function after the configured operations.
3. Start a dedicated connection to LISTEN on the specified channel.

If you don't specify a table, the trigger will simply LISTEN on the channel for any manual NOTIFY events sent from your database or other applications.

Configuration

Event Data

The event data contains the operation type and the row data:

{
  operation: "INSERT" | "UPDATE" | "DELETE";
  table: string;
  old?: Record<string, any>; // Previous row data (for UPDATE and DELETE)
  new?: Record<string, any>; // New row data (for INSERT and UPDATE)
}