feat: Async PostgreSQL LISTEN connector

[mcavaciocchi]

First, I'd like to say that Orion is a fantastic project !

Feature Request

Add an asynchronous PostgreSQL LISTEN connector to Orion, enabling event-driven pipelines triggered by Postgres NOTIFY signals.

Motivation

Currently, Orion lacks a native way to react to database events in real time. A LISTEN connector would allow pipelines to be driven by Postgres notifications without polling.

Proposed Behavior

• Connect to a Postgres instance and issue LISTEN <channel>
• Emit a message into the Orion pipeline whenever a NOTIFY is received on the configured channel
• Support configuring one or more channels
• Handle reconnection automatically on connection loss

Example config_json

{"type":"db_listen","connection_string":"******","driver":"postgres","channels":["my_channel"],"listen_channel":"my_channel","max_connections":10}

[shankar-gpio]

Thanks for the kind words and the thoughtful proposal! Event-driven ingestion is definitely on our radar.

For the near term, our recommendation is to route Postgres NOTIFY events through Kafka rather than adding a direct LISTEN connector to Orion. Orion already has a first-class Kafka consumer ingestion path (src/kafka/consumer.rs) that maps topics -> channels -> workflows, with reconnect handling, DLQ, graceful shutdown, and engine-reload integration. Bridging Postgres into that pipeline gets you the event-driven behavior you're after today, without Orion having to grow a second long-lived ingestion subsystem.

A typical setup looks like:
Postgres NOTIFY -> small bridge (Debezium / pg_kafka / your own) -> Kafka topic -> Orion channel -> workflow

This also sidesteps a few real limitations of raw LISTEN/NOTIFY:

• notifications sent while a listener is disconnected are lost (no replay)
• the payload is capped at 8000 bytes
• there's no built-in ordering or delivery guarantee across reconnects

Going through Kafka gives you durability, replay, and backpressure for free, and several CDC-style bridges (Debezium being the most common) already solve the Postgres -> Kafka leg.

For a later version, we'd like to add direct connectors for these kinds of push sources (Postgres LISTEN, NATS, Redis pub/sub, etc.) as a unified "ingestion source" abstraction sitting alongside the Kafka consumer, but we want to design that abstraction once we have a couple of real use cases to shape it.

Would the Kafka-routing approach work for your use case? If so, we'll keep this issue open as a tracking ticket for the future direct-connector work. If you have a constraint that makes the Kafka hop a non-starter (e.g., you don't run Kafka in your stack), let us know and we can revisit.

[mcavaciocchi]

Thanks for the detailed explanation and for the suggestion — really appreciate the thoughtfulness.

For now, there’s no need to rush on this. This is still an initial project on my side, and in the short term I’ll handle it with a rust connector that simply calls an Orion endpoint.

The Kafka approach makes sense and I can see the advantages, but for the current stage I’ll keep things simpler and evolve as needed.