tops-0001.sql (7947B)
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 my_now INT8; 99 BEGIN 100 -- legitimization_outcomes.decision_time and .expiration_time are stored in 101 -- microseconds (that is what GNUNET_PQ_query_param_timestamp() writes), so 102 -- the durations below have to be scaled accordingly. 103 my_now = ROUND(EXTRACT(epoch FROM CURRENT_TIMESTAMP(0)::TIMESTAMP))::INT8 104 * 1000000; 105 SELECT wt.h_normalized_payto 106 INTO my_h_payto 107 FROM wire_targets wt 108 WHERE wire_target_h_payto = NEW.wire_target_h_payto; 109 110 CALL exchange_do_bump_amount_stat 111 ('deposit-volume' 112 ,my_h_payto 113 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 114 ,NEW.total_amount); 115 116 -- FIXME: this is just for testing, I want to also check 117 -- the 'counter'-based functions. 118 CALL exchange_do_bump_number_stat 119 ('deposit-transactions' 120 ,my_h_payto 121 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 122 ,1); 123 124 -- Get historical deposit volumes and extract the yearly and monthly 125 -- interval statistic values from the result for the AML trigger check. 126 FOR my_rec IN 127 SELECT * 128 FROM exchange_statistic_interval_amount_get( 129 'deposit-volume' 130 ,my_h_payto 131 ) 132 LOOP 133 IF (my_rec.range = 60*60*24*7*52) 134 THEN 135 my_last_year = my_rec.rvalue; 136 END IF; 137 IF (my_rec.range = 60*60*24*7*4) 138 THEN 139 my_last_month = my_rec.rvalue; 140 END IF; 141 END LOOP; 142 -- Note: it is OK to ignore '.frac', as that cannot be significant. 143 -- Also, we effectively exclude the current month's revenue from 144 -- "last year" as otherwise the rule makes no sense. 145 -- Finally, we define the "current month" always as the last 4 weeks, 146 -- just like the "last year" is the last 52 weeks. 147 IF (my_last_year.val < my_last_month.val * 2) 148 THEN 149 -- This is suspicious. => Flag account for AML review! 150 -- 151 -- FIXME: we probably want to factor the code from 152 -- this branch out into a generic 153 -- function to trigger investigations at some point! 154 -- 155 -- First, get existing rules and clear an 'is_active' 156 -- flag, but ONLY if we are not _already_ investigating 157 -- the account (as in the latter case, we'll do no INSERT). 158 UPDATE legitimization_outcomes 159 SET is_active=NOT to_investigate 160 WHERE h_payto = my_h_payto 161 AND is_active 162 RETURNING jproperties 163 ,new_measure_name 164 ,jnew_rules 165 ,to_investigate 166 INTO my_old_rules; 167 168 -- Note that if we have no active legitimization_outcome 169 -- that means we are on default rules and the account 170 -- did not cross KYC thresholds and thus we have no 171 -- established business relationship. In this case, we 172 -- do not care as the overall volume is insignificant. 173 -- This also takes care of the case where a customer 174 -- is new (and obviously the first few months are 175 -- basically always above the inherently zero or near-zero 176 -- transactions from the previous year). 177 -- Thus, we only proceed IF FOUND. 178 IF FOUND 179 THEN 180 my_properties = my_old_rules.jproperties; 181 my_measure_name = my_old_rules.new_measure_name; 182 my_rules = my_old_rules.jnew_rules; 183 my_investigate_property = json_object(ARRAY['AML_INVESTIGATION_STATE', 184 'AML_INVESTIGATION_TRIGGER'], 185 ARRAY['INVESTIATION_PENDING', 186 'DEPOSIT_ANOMALY']); 187 IF my_properties IS NULL 188 THEN 189 my_properties = my_investigate_property::TEXT; 190 ELSE 191 my_properties = (my_properties::JSONB || my_investigate_property)::TEXT; 192 END IF; 193 194 -- Note: here we could in theory manipulate my_properties, 195 -- say to set a note as to why the investigation was started. 196 IF NOT my_old_rules.to_investigate 197 THEN 198 -- Only insert if 'to_investigate' was not already set. 199 INSERT INTO legitimization_outcomes ( 200 h_payto 201 ,decision_time 202 ,expiration_time 203 ,jproperties 204 ,new_measure_name 205 ,to_investigate 206 ,is_active 207 ,jnew_rules 208 ) VALUES ( 209 my_h_payto 210 ,my_now 211 ,my_now + 366*24*60*60*1000000::INT8 212 ,my_properties::JSONB 213 ,my_measure_name 214 ,TRUE 215 ,TRUE 216 ,my_rules::JSONB); 217 END IF; 218 END IF; 219 END IF; 220 RETURN NEW; 221 END $$; 222 COMMENT ON FUNCTION tops_deposit_statistics_trigger 223 IS 'creates deposit statistics'; 224 225 -- Whenever a deposit is made, call our trigger to bump statistics 226 CREATE TRIGGER tops_batch_deposits_on_insert 227 AFTER INSERT 228 ON batch_deposits 229 FOR EACH ROW EXECUTE FUNCTION tops_deposit_statistics_trigger(); 230 231 232 233 COMMIT;