pg_notify & Event Outbox

Overview

The platform uses PostgreSQL as its first-tier event bus: triggers on key tables call pg_notify(...), and worker-go runs a pg-listener that consumes those channels in real time.

The catch is that pg_notify offers no delivery guarantee. If the listener loses its connection at the moment an event fires, that event is gone without a trace. To close the gap, the event_outbox table was introduced so every trigger also writes a durable copy of the event into the database — the transactional outbox pattern. A listener that has just come back online can then catch up by replaying from this table.

Core Data Structure

event_outbox

Created through manual SQL only; it has no Prisma definition.

• id BIGSERIAL, channel VARCHAR(100), payload JSONB
• created_at, processed (defaults to false), processed_at
• Two partial indexes with clearly separated jobs: (created_at) WHERE processed = FALSE for catch-up reads, and (processed_at) WHERE processed = TRUE for cleanup

Channels and the triggers that fire them

Channel	Source table	Trigger	Function
attribute_changed	line_user	trg_line_user_attribute_change (AFTER UPDATE)	notify_attribute_change()
form_submitted	form_submission	form_submission_notify (AFTER INSERT/UPDATE)	notify_form_submitted()
campaign_click	tracking_log	tracking_log_campaign_click_notify (AFTER INSERT)	notify_campaign_click()
friend_track_event_inserted	friend_track_event	friend_track_event_insert_notify (AFTER INSERT)	notify_friend_track_event_insert()
booking_event	appointment.booking	trg_booking_event_insert / trg_booking_event_update	appointment.notify_booking_event()

Behaviour worth knowing

• notify_attribute_change() watches both the fixed columns (display_name, firstname, lastname, email, mobile_no, language, follow) and the keys inside custom_attribute, which it reports in the form custom.<key>.
• Triggers can be suppressed for the duration of a transaction through current_setting('app.skip_triggers', true) = 'true'. This is used during bulk imports so a single run does not emit tens of thousands of events. It is safe in a multi-tenant setup because the setting is scoped to the session or transaction, not the database.
• appointment.notify_booking_event() writes to the outbox on a best-effort basis: it catches undefined_table and silently skips if the outbox table does not exist in that database.

Related Files

• manual-sql/5.sql — creates event_outbox, rewrites every notify function to also insert into the outbox, and adds the missing friend_track_event_insert_notify trigger (fixed 2026-07-24)
• manual-sql/1.notify_attribute_change.sql, manual-sql/3.bank.sql — how the attribute trigger evolved
• manual-sql/2.notify_attribute_change.sql — notify_form_submitted
• manual-sql/4.sql — notify_campaign_click
• apps/appointment/migrations/004_add_booking_event_trigger.sql — the booking_event channel
• manual-sql/0.select.sql — the query that lists every trigger from information_schema.triggers
• schema-dumps/2026-07-24/triggers-summary.txt, schema-dumps/2026-07-24/notify-functions.txt — what is actually deployed on preprod
• schema-dumps/2026-07-24/schema.sql:1681 — event_outbox

Connections to Other Services

• worker-go listens on all five channels and dispatches the events to Trigger Rules and Workflow.
• The source tables live across LINE Friends, Form Builder, Link Tracking, Friend Track and Appointment Booking.
• Caution: none of these triggers are visible to Prisma. prisma migrate will not manage them, so they must be applied manually with psql and verified with manual-sql/0.select.sql.