Creating database record insertion type of Flow Runs?

[MrChadMWood]

Hello,

I've been pondering an idea lately and would appreciate some input. As of now, Prefect doesn't seem to offer a native solution for this concept, but I believe it could be constructed using various tools.

The concept involves triggering a Prefect Flow Run whenever a new record is inserted into a database table. Additionally, it would be beneficial if certain fields within the inserted record could be utilized as parameters to customize the behavior of these Flow Runs.

In essence, I am thinking of setting up a SUBSCRIBE/NOTIFY mechanism on a Postgres table to alert a message queuing service upon any insertions, thereby initiating an automation process that translates the insertion into API commands for Prefect.

The plan involves utilizing Postgres → Redis → Celery → Prefect. The Postgres table could incorporate a data::JSON column, which Celery would interpret as a dictionary, thereby unpacking it into the input parameters for the Flows. Additionally, the table could include a field dedicated to the Flow ID, enabling Celery to identify which Flow to execute.

I'm curious to hear if Prefect recommends an alternative approach.

Thank you!"

[MrChadMWood]

Another approach could involve replacing the pub/sub model with polling. Setting up a Prefect Flow to periodically poll the designated table could effectively achieve the same outcome. If a new record is detected during polling, it could trigger a Flow Run just as Celery would have done.

In this case, it's probably more important to start thinking about how a task gets marked as complete on the db side.