Create small Postgres NOTIFY/LISTEN webhook service for ODK Central

Is your feature request related to a problem? Please describe.

ODK Central has no webhook capabilities, and I imagine will not focus on this: https://forum.getodk.org/t/webhooks-in-odk-central/39917/2
We already have real time updates of task areas using Electric and our task_events table.
However, updating the status of Entities in real time is more difficult: i.e. making the buildings turn green when mapped!
This is because we can't suppose access to the ODK Database from FMTM in production, so can't monitor changes in the ODK Database as they happen.
The only way to make this work is to replicate a subset of the data in the FMTM database:
- A small table odk_entities with fields entity_id and status.
We can then track changes to the status in the FMTM database for realtime building updates.
HOWEVER, to achieve this we need to a trigger updates in the FMTM db record when a new submission is made in ODK (to update the Entity status=2, i.e. fully mapped).

To achieve notifications that the submission was made in ODK we have three options:

Pull: polling of the ODK Entities API at an interval (not ideal, resource inefficient).
Push via ODK: as mentioned, this won't be possible for the foreseeable future.
Push via middleware: a separate lightweight service we bundle into the compose stack of the ODK Central deployment.

I will describe the middleware based approach below.

Describe the solution you'd like

Short term solution:

At the start we won't do a long term solution immediately.
Instead I will simply have the Entities in our database update every time someone access the /statuses endpoint to retrieve the entities.
Here the statuses will be replicated in our database, triggering an update in the users browsers to replicate the database state (via Electric).

Long term solution:

A separate Golang microservice that sites alongside the ODK Central backend, using Postgres LISTEN / NOTIFY events.
First we create a trigger in the database to create a NOTIFY event on update of the entity_def table in the ODK Central database:

Example:

CREATE OR REPLACE FUNCTION data_change() RETURNS trigger AS
$$
    DECLARE
        js jsonb;
    BEGIN
        SELECT to_jsonb(NEW.*) INTO js;
        js := jsonb_set(js, '{dml_action}', to_jsonb(TG_OP));
        PERFORM (
            SELECT pg_notify('people', js::text)
        );
        RETURN NEW;
    END;
$$ LANGUAGE 'plpgsql';

DROP TRIGGER IF EXISTS data_change_trigger
  ON people;
CREATE TRIGGER data_change_trigger
    BEFORE INSERT OR UPDATE ON people
    FOR EACH ROW
        EXECUTE FUNCTION data_change();

This will send JSON data from the data column to our listening Golang service.
- Next we create a small Golang service that simply sits there with a connection open to Postgres, subscribes to the event queue under a certain name (named 'people' above), and then does something when it get notified.
- In our case the 'does something' is to pass on the Entity ID and status fields in a JSON, and POST them to a webhook endpoint.
- This webhook endpoint will be in our Python FastAPI server, simply as something like /projects/ID/update-entites. It will receive the Entity ID and status fields, then update the status in the FMTM database.
- This will trigger propagation via Electric to all subscribed users on the frontend.

Blogs about simple LISTEN / NOTIFY service in Golang:

https://brojonat.com/posts/go-postgres-listen-notify/ https://ds0nt.com/postgres-streaming-listen-notify-go https://brandur.org/notifier

It's also useful to take notes from the psycopg implementation of this: https://www.psycopg.org/psycopg3/docs/advanced/async.html#detecting-disconnections

While it could be nice to implement a more generic pub/sub server like this https://blog.geomusings.com/2023/07/13/a-simple-webhook-interface-for-notify/, I think it's overkill.

We can deploy the very small single Go binary alongside the ODK Central deployment in the compose stack & it will trigger a webhook call whenever an Entity is updated / on submission.

Note we obviously need to make sure this is only triggered on UPDATE not CREATE, as we don't want 1000's of API calls when 1000's of Entities are generated.

Note 2, we probably need to handle auth somehow? Perhaps we generate API tokens in FMTM for this.

Alternatives

We could simply call the webhook directly from the Postgres trigger using https://github.com/pramsey/pgsql-http, but doesn't this seem seriously hacky?

hotosm / fmtm

Create small Postgres NOTIFY/LISTEN webhook service for ODK Central #1841