Running a flow when a database row changes

A lot of city data never leaves a database. Work orders typed in by phone staff, inspection results from a desktop app, parcel changes from the assessor's system. Often the only sign that something happened is a row that changed.

If another system needs to know, the integration has to notice that change. There are three common ways to do it, and each has a place.

Option 1: poll with a watermark

The simplest approach: on a schedule, ask the database for rows that changed since the last good run.

That only works if the table has a column holding the change time, usually updated_at or modified_date. That column is the watermark. Each run reads rows where the watermark is later than the last good run, then remembers the new high point.

SELECT * FROM work_orders
WHERE updated_at > :last_success_at
ORDER BY updated_at

• Good: works on any database, needs no database changes, easy to understand.
• Watch for: the column must be updated on every change, including by other apps and bulk scripts. Deletes don't show up at all.
• Latency: as often as you schedule it, down to a minute.

Use only the last good run as the starting point. If a run fails, the next one covers the same window again, so nothing is skipped.

Option 2: SQL Server Change Tracking

SQL Server has a built-in feature called Change Tracking. Once a DBA turns it on for a database and table, SQL Server records which rows changed (by primary key) and whether each was inserted, updated or deleted. It doesn't keep the old values, just the fact of the change and a version number.

An integration asks: "what changed since version N?", gets a list of keys, then reads those rows.

• Good: catches every change, including ones that don't touch an updated_at column, and includes deletes.
• Watch for: a DBA has to enable it, and there's a retention window. If the integration is off longer than that, it has to fall back to a full read.
• Latency: still checked on a schedule, but each check is cheap.

Option 3: PostgreSQL LISTEN/NOTIFY

PostgreSQL can send a message to anyone listening on a named channel. A small trigger on the table calls pg_notify when a row changes, sending the row's ID. A listening integration hears it right away and starts the flow.

CREATE FUNCTION notify_work_order() RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('work_orders_changed', NEW.id::text);
  RETURN NEW;
END $$ LANGUAGE plpgsql;

• Good: near-instant, and the message carries only an ID.
• Watch for: if the listener is disconnected, messages sent in that moment are missed. Pair it with a periodic watermark check as a safety net.
• Latency: seconds.

Which one to choose

• The table has a reliable updated_at: start with polling. It's the least work for everyone.
• SQL Server, and you need deletes or the change column isn't trustworthy: Change Tracking.
• PostgreSQL, and minutes are too slow: LISTEN/NOTIFY, with polling as a backstop.

Don't forget the other side

Noticing a change is half the job. The flow also has to avoid echoing it back. If the 311 system writes to the database and the database triggers a flow into 311, you can create a loop. Filter out rows that came from the destination system, for example with a source column, before sending.

Notice by ID, then read

All three approaches share one good habit: the trigger only needs to say which rows changed. The flow then reads those rows, by ID, as they are now. That keeps the change mechanism small and avoids passing stale copies around.

A worked example: database to 311

Phone and field staff often log work in a city database that 311 never sees. The reverse sync example reads work orders created or changed in the database every 15 minutes and creates matching Sentra 311 requests, so that work shows up in 311 too.

In SentraBridge

In SentraBridge, a flow can start when database rows change, using polling with a changed-column watermark, SQL Server Change Tracking, or PostgreSQL LISTEN/NOTIFY. Read steps also support "only rows changed since the last good run" directly. Rows are read into memory and not stored. See triggers and databases.