pg_notify & Event Outbox

Overview

This domain has a single table, event_outbox, which is not declared in the Prisma schema but created by manual SQL (manual-sql/5.sql). It acts as a durable backup for the pg_notify mechanism — every time a trigger function emits pg_notify, it also inserts the same payload into this table, so consumers that were disconnected can catch up afterwards (pg_notify does not retain messages that were already sent)

Table event_outbox

Column	Type	Nullable	Default	Description
id	BIGSERIAL	NO	nextval(...)	Primary key
channel	VARCHAR(100)	NO	-	Name of the pg_notify channel the event was sent on
payload	JSONB	NO	-	Event payload (identical to what was sent via pg_notify)
created_at	TIMESTAMPTZ	NO	NOW()	Time the event was written to the outbox
processed	BOOLEAN	NO	FALSE	Whether a consumer has processed this event
processed_at	TIMESTAMPTZ	YES	-	Time the event finished being processed

Notes

All pg_notify channels in the system

Channel	Trigger function	Source table	Firing condition
attribute_changed	notify_attribute_change()	line_user	A fixed column changes (display_name, firstname, lastname, email, mobile_no, language, follow) or any key inside custom_attribute changes
friend_track_event_inserted	notify_friend_track_event_insert()	friend_track_event	A new row is INSERTed
form_submitted	notify_form_submitted()	form_submission	is_submitted changes from false to true
campaign_click	notify_campaign_click()	tracking_log	INSERT with action_type = 'click', content_type = 'campaign', and non-null campaign_id and line_uid

Triggers bound to these functions

• trg_line_user_attribute_change — AFTER UPDATE ON line_user
• friend_track_event_insert_notify — AFTER INSERT ON friend_track_event
• form_submission_notify — AFTER INSERT OR UPDATE ON form_submission
• tracking_log_campaign_click_notify — AFTER INSERT ON tracking_log

Other observations

• event_outbox does not exist in prisma/schema.prisma — when using Prisma migrate you must run manual-sql/5.sql separately
• Two partial indexes support the main access patterns:
  • idx_event_outbox_catchup on created_at filtered by WHERE processed = FALSE — used to fetch unprocessed events when a consumer reconnects
  • idx_event_outbox_cleanup on processed_at filtered by WHERE processed = TRUE — used to purge old, already-processed events
• notify_attribute_change() skips all work when the session sets app.skip_triggers = 'true' (used for bulk updates; it is transaction-scoped and therefore multi-tenant safe)
• notify_form_submitted() has to JOIN form_builder and line_oa to resolve lineOaId and organizationId before building the payload, because form_submission does not store them
• All payloads are built with json_build_object(...)::text and cast to jsonb on insert into the outbox, so the value in payload exactly matches what was sent through pg_notify