ores.sql.schema.notify_trigger

1. Template
2. See also

pg_notify trigger announcing entity changes (insert/update/delete with the primary-key value) on the entity's channel — the database end of the eventing pipeline. {{#domain_entity}} section. sql profile; output …/{component}_{entity_plural}_notify_trigger_create.sql. PostgreSQL `NOTIFY` trigger: fires a channel notification on every `INSERT`, `UPDATE`, or `DELETE` so the application layer can push real-time updates over NATS.

See the Template variable reference for the complete list of available variables and their semantics. See SQL naming conventions for the official object-name patterns all SQL templates follow.

1. Template

The full template source. Edit here and re-tangle with compass build --direct tangle_codegen_templates to regenerate library/templates/sql_schema_notify_trigger.mustache.

{{! GENERATED FILE — tangled from projects/ores.codegen/library/templates/ores.sql.schema.notify_trigger.org. Edit the org source. }}
{{! Template to generate the SQL notification trigger for entity changes }}
{{{sql_license}}}
{{#domain_entity}}
/*
 * AUTO-GENERATED FILE - DO NOT EDIT MANUALLY
 * Template: sql_schema_notify_trigger.mustache
 * To modify, update the template and regenerate.
 */

create or replace function {{sql_name_base}}_notify_fn()
returns trigger as $$
declare
    notification_payload jsonb;
    change_action text;
    changed_version integer := 0;
    {{primary_key.notify_declarations}}
    changed_key jsonb;
    changed_tenant_id text;
begin
    if TG_OP = 'DELETE' then
        change_action := 'deleted';
        {{primary_key.notify_assign_old}}
{{#has_audit_columns}}
        changed_version := OLD.version;
{{/has_audit_columns}}
        changed_tenant_id := OLD.tenant_id::text;
    elsif TG_OP = 'UPDATE' then
{{#has_audit_columns}}
        -- A versioned table's update is the internal close of the current
        -- row; the insert that follows it carries the change. Announcing
        -- both would report one change twice, so the close announces
        -- nothing.
        return null;
{{/has_audit_columns}}
{{^has_audit_columns}}
        change_action := 'updated';
        {{primary_key.notify_assign_new}}
        changed_version := 0;
        changed_tenant_id := NEW.tenant_id::text;
{{/has_audit_columns}}
    else
{{#has_audit_columns}}
        -- The first version of a row is a create; every later one is an
        -- update, because the row it replaces was already there.
        if NEW.version <= 1 then
            change_action := 'created';
        else
            change_action := 'updated';
        end if;
        changed_version := NEW.version;
{{/has_audit_columns}}
{{^has_audit_columns}}
        change_action := 'created';
        changed_version := 0;
{{/has_audit_columns}}
        {{primary_key.notify_assign_new}}
        changed_tenant_id := NEW.tenant_id::text;
    end if;

    changed_key := {{primary_key.notify_key_object}};

    notification_payload := jsonb_build_object(
        'event_id', gen_random_uuid()::text,
        'entity', '{{product}}.{{component}}.{{entity_singular}}',
        'key', changed_key::text,
        'action', change_action,
        'version', changed_version,
        'occurred_at', ores_utility_iso8601_timestamp_fn(clock_timestamp()),
        'correlation_id', nullif(current_setting('ores.request.correlation_id', true), ''),
        'tenant_id', changed_tenant_id
    );

    perform pg_notify('{{sql_name_base}}', notification_payload::text);

    return null;
end;
$$ language plpgsql;

create or replace trigger {{sql_name_base}}_notify_trg
after insert or update or delete on {{sql_name_base}}_tbl
for each row execute function {{sql_name_base}}_notify_fn();
{{/domain_entity}}

2. See also

Parent facet: ores.sql.schema
Template variable reference

ores.sql.schema.notify_trigger

Table of Contents

1. Template

2. See also