Releases subscriptions

1. README

First, I checked doc on https://www.graphile.org/postgraphile/subscriptions/


I installed the app_public.graphql_subscription()

I added the trigger on insert/update of each release

I added validate_subscription()

enable simple_subscription call validation_subscription but not from graphql, so I need to see how I can validate from graphql itself

_500_gql_new_release

CREATE TRIGGER _500_gql_new_release
  AFTER INSERT OR UPDATE ON app_public.releases
  FOR EACH ROW
  EXECUTE PROCEDURE app_public.graphql_subscription('releasesChanged', 'apps:$1:releases', 'app_uuid');

app_hidden.validate_subscription(topic text)

CREATE OR REPLACE FUNCTION app_hidden.validate_subscription(topic text)
RETURNS TEXT AS $$
DECLARE
    s_origin text;
    s_uuid text;
    -- s_table text;
BEGIN

  IF current_owner_uuid() is null THEN
      RAISE EXCEPTION 'Subscriptions are reserved for authenticated users';
  END IF;

  s_origin = SPLIT_PART(topic, ':', 1);
  s_uuid = SPLIT_PART(topic, ':', 2);
  -- s_table = SPLIT_PART(topic, ':', 3); -- DO WE CARE ?

  IF format('SELECT owner_uuid FROM app_public.%I WHERE UUID = %L', s_origin, s_uuid) is null THEN
      RAISE EXCEPTION 'Subscriptions are reserved for owners';
  END IF;

  RETURN current_owner_uuid()::text;

END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;


BEGIN;
SELECT set_config('jwt.claims.owner_uuid', '7b198e7d-347a-4283-be98-14a91298371d', true);
SELECT app_hidden.validate_subscription('apps:30d159cb-0e85-41d4-b894-56bd5b6a61d3:releases');
END;

app_public.graphql_subscription.sql

create function app_public.graphql_subscription() returns trigger as $$
declare
  v_process_new bool = (TG_OP = 'INSERT' OR TG_OP = 'UPDATE');
  v_process_old bool = (TG_OP = 'UPDATE' OR TG_OP = 'DELETE');
  v_event text = TG_ARGV[0];
  v_topic_template text = TG_ARGV[1];
  v_attribute text = TG_ARGV[2];
  v_record record;
  v_sub text;
  v_topic text;
  v_i int = 0;
  v_last_topic text;
begin
  -- On UPDATE sometimes topic may be changed for NEW record,
  -- so we need notify to both topics NEW and OLD.
  for v_i in 0..1 loop
    if (v_i = 0) and v_process_new is true then
      v_record = new;
    elsif (v_i = 1) and v_process_old is true then
      v_record = old;
    else
      continue;
    end if;
     if v_attribute is not null then
      execute 'select $1.' || quote_ident(v_attribute)
        using v_record
        into v_sub;
    end if;
    if v_sub is not null then
      v_topic = replace(v_topic_template, '$1', v_sub);
    else
      v_topic = v_topic_template;
    end if;
    if v_topic is distinct from v_last_topic then
      -- This if statement prevents us from triggering the same notification twice
      v_last_topic = v_topic;
      perform pg_notify(v_topic, json_build_object(
        'event', v_event,
        'subject', v_sub
      )::text);
    end if;
  end loop;
  return v_record;
end;
$$ language plpgsql volatile set search_path from current;