tops-0001.sql (7594B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 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 17 -- @file tops-0001.sql 18 -- @brief special TOPS-specific (AML) rules to inject into an exchange 19 -- @author Christian Grothoff 20 21 -- Everything in one big transaction 22 BEGIN; 23 24 -- Check patch versioning is in place. 25 SELECT _v.register_patch('tops-0001', NULL, NULL); 26 27 -- Note: this NOT an accident: the schema MUST be named 28 -- using the filename prefix (and the name under --enable-custom of taler-exchange-dbinit). 29 CREATE SCHEMA IF NOT EXISTS tops; 30 31 SET search_path TO tops,exchange; 32 33 INSERT INTO exchange_statistic_interval_meta 34 (origin 35 ,slug 36 ,description 37 ,stype 38 ,ranges 39 ,precisions) 40 VALUES 41 -- this first one is just for testing right now 42 ('tops' -- must match schema! 43 ,'deposit-transactions' 44 ,'number of (batch) deposits performed by this merchant, used to detect sudden increase in number of transactions' 45 ,'number' 46 ,ARRAY(SELECT generate_series (60*60*24*7, 60*60*24*7*52, 60*60*24*7)) -- weekly volume over the last year 47 ,array_fill (60*60*24, ARRAY[52]) -- precision is per day 48 ), 49 ('tops' -- must match schema! 50 ,'deposit-volume' 51 ,'total amount deposited by this merchant in (batch) deposits including deposit fees, used to detect sudden increase in transaction volume' 52 ,'amount' 53 ,ARRAY(SELECT generate_series (60*60*24*7, 60*60*24*7*52, 60*60*24*7)) -- weekly volume over the last year 54 ,array_fill (60*60*24, ARRAY[52]) -- precision is per day 55 ) 56 ON CONFLICT DO NOTHING; 57 58 INSERT INTO exchange_statistic_bucket_meta 59 (origin 60 ,slug 61 ,description 62 ,stype 63 ,ranges 64 ,ages) 65 VALUES 66 -- this first one is just for testing right now 67 ('tops' -- must match schema! 68 ,'deposit-transactions' 69 ,'number of (batch) deposits performed by this merchant, used to detect sudden increase in number of transactions' 70 ,'number' 71 ,ARRAY['day'::statistic_range,'week'] 72 ,ARRAY[5,5] 73 ), 74 ('tops' -- must match schema! 75 ,'deposit-volume' 76 ,'total amount deposited by this merchant in (batch) deposits including deposit fees, used to detect sudden increase in transaction volume' 77 ,'amount' 78 ,ARRAY['day'::statistic_range,'week'] 79 ,ARRAY[5,5] 80 ) 81 ON CONFLICT DO NOTHING; 82 83 DROP FUNCTION IF EXISTS tops_deposit_statistics_trigger CASCADE; 84 CREATE FUNCTION tops_deposit_statistics_trigger() 85 RETURNS trigger 86 LANGUAGE plpgsql 87 AS $$ 88 DECLARE 89 my_h_payto BYTEA; -- normalized h_payto of target account 90 my_rec RECORD; 91 my_last_year taler_amount; -- sum of deposits this year 92 my_last_month taler_amount; -- sum of deposits this month 93 my_old_rules RECORD; 94 my_properties TEXT; 95 my_investigate_property JSONB; 96 my_measure_name TEXT; 97 my_rules TEXT; 98 BEGIN 99 SELECT wt.h_normalized_payto 100 INTO my_h_payto 101 FROM wire_targets wt 102 WHERE wire_target_h_payto = NEW.wire_target_h_payto; 103 104 CALL exchange_do_bump_amount_stat 105 ('deposit-volume' 106 ,my_h_payto 107 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 108 ,NEW.total_amount); 109 110 -- FIXME: this is just for testing, I want to also check 111 -- the 'counter'-based functions. 112 CALL exchange_do_bump_number_stat 113 ('deposit-transactions' 114 ,my_h_payto 115 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 116 ,1); 117 118 -- Get historical deposit volumes and extract the yearly and monthly 119 -- interval statistic values from the result for the AML trigger check. 120 FOR my_rec IN 121 SELECT * 122 FROM exchange_statistic_interval_amount_get( 123 'deposit-volume' 124 ,my_h_payto 125 ) 126 LOOP 127 IF (my_rec.range = 60*60*24*7*52) 128 THEN 129 my_last_year = my_rec.rvalue; 130 END IF; 131 IF (my_rec.range = 60*60*24*7*4) 132 THEN 133 my_last_month = my_rec.rvalue; 134 END IF; 135 END LOOP; 136 -- Note: it is OK to ignore '.frac', as that cannot be significant. 137 -- Also, we effectively exclude the current month's revenue from 138 -- "last year" as otherwise the rule makes no sense. 139 -- Finally, we define the "current month" always as the last 4 weeks, 140 -- just like the "last year" is the last 52 weeks. 141 IF (my_last_year.val < my_last_month.val * 2) 142 THEN 143 -- This is suspicious. => Flag account for AML review! 144 -- 145 -- FIXME: we probably want to factor the code from 146 -- this branch out into a generic 147 -- function to trigger investigations at some point! 148 -- 149 -- First, get existing rules and clear an 'is_active' 150 -- flag, but ONLY if we are not _already_ investigating 151 -- the account (as in the latter case, we'll do no INSERT). 152 UPDATE legitimization_outcomes 153 SET is_active=NOT to_investigate 154 WHERE h_payto = my_h_payto 155 AND is_active 156 RETURNING jproperties 157 ,new_measure_name 158 ,jnew_rules 159 ,to_investigate 160 INTO my_old_rules; 161 162 -- Note that if we have no active legitimization_outcome 163 -- that means we are on default rules and the account 164 -- did not cross KYC thresholds and thus we have no 165 -- established business relationship. In this case, we 166 -- do not care as the overall volume is insignificant. 167 -- This also takes care of the case where a customer 168 -- is new (and obviously the first few months are 169 -- basically always above the inherently zero or near-zero 170 -- transactions from the previous year). 171 -- Thus, we only proceed IF FOUND. 172 IF FOUND 173 THEN 174 my_properties = my_old_rules.jproperties; 175 my_measure_name = my_old_rules.new_measure_name; 176 my_rules = my_old_rules.jnew_rules; 177 my_investigate_property = json_object(ARRAY['AML_INVESTIGATION_STATE', 178 'AML_INVESTIGATION_TRIGGER'], 179 ARRAY['INVESTIATION_PENDING', 180 'DEPOSIT_ANOMALY']); 181 IF my_properties IS NULL 182 THEN 183 my_properties = my_investigate_property::TEXT; 184 ELSE 185 my_properties = (my_properties::JSONB || my_investigate_property)::TEXT; 186 END IF; 187 188 -- Note: here we could in theory manipulate my_properties, 189 -- say to set a note as to why the investigation was started. 190 IF NOT my_old_rules.to_investigate 191 THEN 192 -- Only insert if 'to_investigate' was not already set. 193 INSERT INTO legitimization_outcomes ( 194 h_payto 195 ,decision_time 196 ,expiration_time 197 ,jproperties 198 ,new_measure_name 199 ,to_investigate 200 ,is_active 201 ,jnew_rules 202 ) VALUES ( 203 my_h_payto 204 ,my_now 205 ,my_now + 366*24*60*60 206 ,my_properties 207 ,my_measure_name 208 ,TRUE 209 ,TRUE 210 ,my_rules); 211 END IF; 212 END IF; 213 END IF; 214 RETURN NEW; 215 END $$; 216 COMMENT ON FUNCTION tops_deposit_statistics_trigger 217 IS 'creates deposit statistics'; 218 219 -- Whenever a deposit is made, call our trigger to bump statistics 220 CREATE TRIGGER tops_batch_deposits_on_insert 221 AFTER INSERT 222 ON batch_deposits 223 FOR EACH ROW EXECUTE FUNCTION tops_deposit_statistics_trigger(); 224 225 226 227 COMMIT;