ores.sql.schema.notify_trigger

{{! 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;
{{#has_party_column}}
    changed_party_id text;
{{/has_party_column}}
begin
    if TG_OP = 'DELETE' then
        change_action := 'deleted';
        {{primary_key.notify_assign_old}}
{{#has_audit_columns}}
        changed_version := OLD.version;
{{/has_audit_columns}}
{{#has_tenant_id}}
        changed_tenant_id := OLD.tenant_id::text;
{{/has_tenant_id}}
{{#has_party_column}}
        changed_party_id := OLD.party_id::text;
{{/has_party_column}}
    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;
{{#has_tenant_id}}
        changed_tenant_id := NEW.tenant_id::text;
{{/has_tenant_id}}
{{#has_party_column}}
        changed_party_id := NEW.party_id::text;
{{/has_party_column}}
{{/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}}
{{#has_tenant_id}}
        changed_tenant_id := NEW.tenant_id::text;
{{/has_tenant_id}}
{{#has_party_column}}
        changed_party_id := NEW.party_id::text;
{{/has_party_column}}
    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{{#has_party_column}},
        'party_id', changed_party_id{{/has_party_column}}
    );

    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}}
{{#junction}}
/*
 * AUTO-GENERATED FILE - DO NOT EDIT MANUALLY
 * Template: sql_schema_notify_trigger.mustache
 * To modify, update the template and regenerate.
 */

-- A link is closed by ending its validity and never deleted, so the close is an
-- update. A close that a newer version of the same link follows is a replacement,
-- which the insert announces; a close with no successor is the link going away.
-- The successor does not exist when the close runs, so the close is judged at
-- the end of the transaction.
create or replace function {{events_channel}}_notify_fn()
returns trigger as $$
declare
    notification_payload jsonb;
    change_action text;
    changed_version integer := 0;
{{#event_key_columns}}
    changed_{{column}} text;
{{/event_key_columns}}
    changed_tenant_id text;
    -- A party is a key column of the junction when there is one, so it is
    -- already declared and set with the key.
begin
    if TG_OP = 'UPDATE' then
        if OLD.valid_to <> ores_utility_infinity_timestamp_fn()
           or NEW.valid_to = ores_utility_infinity_timestamp_fn() then
            return null;
        end if;
        -- A newer version of the link, open or closed, answers for it: an open
        -- one replaced this version, and a closed one is announced when its
        -- own close is judged, so a link closed twice in one transaction is
        -- told once.
        if exists (
            select 1 from {{events_channel}}_tbl
            where tenant_id = OLD.tenant_id
{{#event_key_columns}}
              and {{column}} = OLD.{{column}}
{{/event_key_columns}}
              and valid_from > OLD.valid_from
        ) then
            return null;
        end if;
        change_action := 'deleted';
        changed_version := OLD.version;
{{#event_key_columns}}
        changed_{{column}} := OLD.{{column}}::text;
{{/event_key_columns}}
        changed_tenant_id := OLD.tenant_id::text;
    else
        if NEW.version <= 1 then
            change_action := 'created';
        else
            change_action := 'updated';
        end if;
        changed_version := NEW.version;
{{#event_key_columns}}
        changed_{{column}} := NEW.{{column}}::text;
{{/event_key_columns}}
        changed_tenant_id := NEW.tenant_id::text;
    end if;

    notification_payload := jsonb_build_object(
        'event_id', gen_random_uuid()::text,
        'entity', '{{product}}.{{component}}.{{name_singular}}',
        'key', jsonb_build_object({{#event_key_columns}}'{{column}}', changed_{{column}}{{^last}}, {{/last}}{{/event_key_columns}})::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{{#has_party_column}},
        'party_id', changed_party_id{{/has_party_column}}
    );

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

    return null;
end;
$$ language plpgsql;

create or replace trigger {{events_channel}}_notify_trg
after insert on {{events_channel}}_tbl
for each row execute function {{events_channel}}_notify_fn();

drop trigger if exists {{events_channel}}_notify_close_trg on {{events_channel}}_tbl;
create constraint trigger {{events_channel}}_notify_close_trg
after update on {{events_channel}}_tbl
deferrable initially deferred
for each row execute function {{events_channel}}_notify_fn();
{{/junction}}