pg_merchant_send_kyc_notification.sql (3133B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 16 -- @file pg_merchant_send_kyc_notification.sql 17 -- @brief Queue a report to alert the merchant about a KYC status change 18 -- @author Christian Grothoff 19 20 21 22 DROP PROCEDURE IF EXISTS merchant_send_kyc_notification; 23 CREATE PROCEDURE merchant_send_kyc_notification( 24 in_account_serial INT8 25 ,in_exchange_url TEXT 26 ) 27 LANGUAGE plpgsql 28 AS $$ 29 DECLARE 30 my_instance_serial INT8; 31 my_report_token BYTEA; 32 my_h_wire BYTEA; 33 my_email TEXT; 34 my_notification_language TEXT; 35 BEGIN 36 SELECT SUBSTRING(current_schema()::TEXT 37 FROM 'merchant_instance_([0-9]+)')::INT8 38 INTO my_instance_serial; 39 SELECT h_wire 40 INTO my_h_wire 41 FROM merchant_accounts 42 WHERE account_serial=in_account_serial; 43 IF NOT FOUND 44 THEN 45 RAISE WARNING 'Account not found, KYC change notification not triggered'; 46 RETURN; 47 END IF; 48 SELECT email 49 ,notification_language 50 INTO my_email 51 ,my_notification_language 52 FROM merchant.merchant_instances 53 WHERE merchant_serial=my_instance_serial; 54 IF NOT FOUND 55 THEN 56 RAISE WARNING 'Instance not found, KYC change notification not triggered'; 57 RETURN; 58 END IF; 59 IF my_notification_language IS NULL 60 THEN 61 -- Disabled 62 RETURN; 63 END IF; 64 IF my_email IS NULL 65 THEN 66 -- Note: we MAY want to consider sending an SMS instead... 67 RETURN; 68 END IF; 69 70 -- Note: random_bytea(), uri_escape() and base32_crockford() live in the 71 -- 'merchant' schema, while this procedure runs with a search_path that 72 -- contains only the per-instance schema (see set_instance.c), so they 73 -- MUST be schema-qualified here. 74 my_report_token = merchant.random_bytea(32); 75 -- Note: merchant_reports is the per-instance table; it lost its 76 -- 'merchant_serial' column when the tables were moved into per-instance 77 -- schemata (merchant-0036). 78 INSERT INTO merchant_reports ( 79 report_program_section 80 ,report_description 81 ,mime_type 82 ,report_token 83 ,data_source 84 ,target_address 85 ,frequency 86 ,frequency_shift 87 ,next_transmission 88 ,one_shot_hidden 89 ) VALUES ( 90 'email' 91 ,'automatically triggered KYC alert' 92 ,'text/plain' 93 ,my_report_token 94 ,'/private/kyc?exchange_url=' || merchant.uri_escape(in_exchange_url) 95 || '&h_wire=' || merchant.base32_crockford (my_h_wire) 96 ,my_email 97 ,0 98 ,0 99 ,0 100 ,TRUE 101 ); 102 -- Notify taler-merchant-report-generator 103 NOTIFY XSSAB8NCBQR1K2VK7H2M6SMY3V5TNJT1C3BW0SN4F2QV0KHR3PRB0; 104 END $$;