npm package: @mbakgun/n8n-nodes-postgresql-event-channel
Description: n8n nodes for PostgreSQL-backed event channel communication between workflows
Author: Mehmet Burak Akgun
Latest version: 0.0.17

# PostgreSQL Event Channel Nodes

This project provides three n8n custom nodes that implement an event channel backed by PostgreSQL. Workflows can push commands into a shared table and wait for the results or listen for new entries, execute the requested work, and persist the response.

## Database Table Schema

Create the event table with the following SQL before using the nodes:

```
CREATE TYPE n8n_postgresql_event_state AS ENUM ('sent', 'processing', 'completed', 'error');

CREATE TABLE IF NOT EXISTS n8n_postgresql_event_channel (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  channel_key VARCHAR(255) NOT NULL,
  event_type VARCHAR(128) NOT NULL,
  command TEXT NOT NULL,
  state n8n_postgresql_event_state NOT NULL DEFAULT 'sent',
  result_data JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  timeout_seconds INTEGER NOT NULL DEFAULT 60
);

CREATE INDEX IF NOT EXISTS idx_n8n_postgresql_event_channel_state ON n8n_postgresql_event_channel (state);
CREATE INDEX IF NOT EXISTS idx_n8n_postgresql_event_channel_channel_event ON n8n_postgresql_event_channel (channel_key, event_type);
```

If `gen_random_uuid()` is unavailable replace it with `uuid_generate_v4()` and ensure the corresponding extension is enabled.

## Command Validation

Commands are validated before being sent to the database. The validator blocks patterns such as `rm -rf`, `dd if=`, `mkfs`, any reference to `/dev/sd*` or `/dev/hd*`, fork bombs, and commands using `--no-preserve-root`. Blocked commands throw an error and are logged to the console for monitoring.

## Nodes Overview

### PostgreSqlSendToChannel

Action node that writes a command into the event table, waits for the processing workflow to finish, and returns the stored result. It performs command validation, supports configurable timeouts between 10 and 500 seconds, and polls the database every second while waiting for results. If the timeout elapses, the node marks the event as `error` with an explanatory message.

### PostgreSqlEventTrigger

Trigger node that polls the event table for entries in the `sent` state matching the configured channel key and event type. When an entry is found the node moves it to `processing` and emits the payload to downstream workflow nodes. The workflow must end with a PostgreSqlWriteResult node to persist the execution result.

### PostgreSqlWriteResult

Action node that writes the workflow execution result back to the database. This node should be placed at the end of the triggered workflow. It automatically detects errors in the input data and sets the appropriate state (`completed` or `error`) along with the result data. The Event ID is automatically captured from the trigger node output.

## Configuration

All nodes share the same credential configuration and expect standard PostgreSQL connection parameters: host, port, database, user, password, and optional SSL settings. The event type dropdown currently exposes `httpExecutionCurl`, and additional types can be introduced by extending the shared constant list.

## Usage Flow

1. Workflow A uses **PostgreSqlSendToChannel** to push a curl command with channel key `curl-information-team-executor` and waits for the response.
2. Workflow B uses **PostgreSqlEventTrigger** with the same channel key and event type. When it detects the request it switches the state to `processing` and runs the downstream nodes.
3. Workflow B ends with **PostgreSqlWriteResult** which captures the execution result (success or error) and persists it to the database.
4. Workflow A resumes once `result_data` is populated and propagates the returned payload.

## Error Handling

* Invalid credentials or connectivity problems propagate descriptive errors.
* Dangerous commands are refused with a security error.
* Timeout updates the database row to `error` alongside a detailed message.
* PostgreSqlWriteResult automatically detects errors in the workflow execution and stores them with state `error`.
* If a workflow fails before reaching the WriteResult node, the timeout mechanism will mark the event as error.

## Building

Compile the project with bun:

```
bunx tsc --project tsconfig.json --noEmit false
```

This command emits the compiled output into the `dist` directory. Clean the directory between builds if required.

## Security Notes

Carefully scope channel keys and credentials. Because the trigger node locks rows with `SELECT FOR UPDATE SKIP LOCKED`, multiple trigger workflows can safely run in parallel without processing the same event more than once.