tops-0001.sql (10268B)
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 -- One record per monitored account, independent of officer decisions. An 84 -- officer clearing a case must not cause the same anomaly to reopen it. 85 CREATE TABLE deposit_monitor_state 86 (h_payto BYTEA PRIMARY KEY REFERENCES exchange.kyc_targets(h_normalized_payto) 87 ON DELETE CASCADE 88 ,open_time INT8 NOT NULL 89 ,clears_at INT8 -- UTC seconds; NULL if the volume condition is false 90 ); 91 COMMENT ON TABLE deposit_monitor_state 92 IS 'tracks deposit anomaly episodes independently of KYC rule validity'; 93 94 -- When would the current anomaly first clear if no further deposits arrived? 95 -- Only expiration from the 28-day window can clear it. Expiration from the 96 -- 52-week window can make it true again, so remember the FIRST clearing time, 97 -- even if that whole false interval falls between two deposits. Events and 98 -- interval statistics have the same daily precision and inclusive lower bound. 99 CREATE FUNCTION deposit_anomaly_clears_at(in_h_payto BYTEA, in_now INT8) 100 RETURNS INT8 101 LANGUAGE SQL 102 VOLATILE 103 SET search_path TO exchange, pg_temp 104 AS $$ 105 WITH events AS MATERIALIZED ( 106 SELECT e.slot, (e.delta).val::NUMERIC * 100000000 + (e.delta).frac AS units 107 FROM exchange_statistic_amount_event e 108 JOIN exchange_statistic_interval_meta m USING (imeta_serial_id) 109 WHERE m.slug = 'deposit-volume' 110 AND m.stype = 'amount' 111 AND e.h_payto = in_h_payto 112 AND e.slot >= in_now - 31449600 113 ), boundaries AS ( 114 SELECT slot + 2419200 + 1 AS at_time 115 FROM events 116 WHERE slot >= in_now - 2419200 117 ) 118 SELECT MIN(b.at_time) 119 FROM boundaries b 120 CROSS JOIN LATERAL ( 121 SELECT COALESCE(SUM(units) FILTER (WHERE slot >= b.at_time - 2419200), 0) AS month, 122 COALESCE(SUM(units) FILTER (WHERE slot >= b.at_time - 31449600), 0) AS year 123 FROM events 124 ) totals 125 WHERE totals.month <= 100000::NUMERIC * 100000000 126 OR totals.year >= 2 * totals.month; 127 $$; 128 129 DROP FUNCTION IF EXISTS tops_deposit_statistics_trigger CASCADE; 130 CREATE FUNCTION tops_deposit_statistics_trigger() 131 RETURNS trigger 132 LANGUAGE plpgsql 133 SET search_path TO exchange, pg_temp 134 AS $$ 135 DECLARE 136 my_h_payto BYTEA; 137 my_delta taler_amount; 138 my_rec RECORD; 139 my_month NUMERIC := 0; 140 my_year NUMERIC := 0; 141 my_old_rules RECORD; 142 my_properties JSONB; 143 my_reason TEXT; 144 my_state TEXT; 145 my_now INT8; 146 my_seconds INT8; 147 my_open_time INT8; 148 my_close_time INT8; 149 my_previous_open INT8; 150 my_previous_clear INT8; 151 my_anomaly BOOL; 152 my_new_episode BOOL; 153 BEGIN 154 IF TG_OP = 'UPDATE' 155 THEN 156 -- Retries and unrelated updates contribute nothing. Gross deposits only 157 -- grow; refunds are recorded separately and do not reduce this statistic. 158 IF NEW.total_amount <= OLD.total_amount 159 THEN 160 RETURN NEW; 161 END IF; 162 SELECT (diff).* INTO my_delta 163 FROM amount_left_minus_right(NEW.total_amount, OLD.total_amount); 164 ELSE 165 my_delta = NEW.total_amount; 166 END IF; 167 168 my_seconds = EXTRACT(epoch FROM exchange_now())::INT8; 169 my_now = my_seconds * 1000000; 170 SELECT h_normalized_payto INTO STRICT my_h_payto 171 FROM wire_targets WHERE wire_target_h_payto = NEW.wire_target_h_payto; 172 173 -- Serialize monitoring for this account, also with account open/close 174 -- updates. The exchange runs these operations in retryable transactions. 175 SELECT open_time, close_time INTO my_open_time, my_close_time 176 FROM kyc_targets WHERE h_normalized_payto = my_h_payto FOR UPDATE; 177 178 CALL exchange_do_bump_amount_stat 179 ('deposit-volume', my_h_payto, exchange_now(), my_delta); 180 IF TG_OP = 'INSERT' 181 THEN 182 -- Count batches once, even when more coins arrive in a later request. 183 CALL exchange_do_bump_number_stat 184 ('deposit-transactions', my_h_payto, exchange_now(), 1); 185 END IF; 186 187 -- Keep statistics for all deposits, but monitor only open bank accounts. 188 IF my_open_time IS NULL OR my_close_time IS NOT NULL 189 THEN 190 DELETE FROM tops.deposit_monitor_state WHERE h_payto = my_h_payto; 191 RETURN NEW; 192 END IF; 193 194 FOR my_rec IN 195 SELECT * FROM exchange_statistic_interval_amount_get('deposit-volume', my_h_payto) 196 LOOP 197 IF my_rec.range = 2419200 198 THEN 199 my_month = (my_rec.rvalue).val::NUMERIC * 100000000 + (my_rec.rvalue).frac; 200 ELSIF my_rec.range = 31449600 201 THEN 202 my_year = (my_rec.rvalue).val::NUMERIC * 100000000 + (my_rec.rvalue).frac; 203 END IF; 204 END LOOP; 205 -- Latest 28 days exceed both CHF 100,000 and the preceding 336 days. 206 -- Amounts in the exchange DB have no currency tag; TOPS operates in CHF. 207 -- Compare all fractional units, including at the strict CHF 100,000 floor. 208 my_anomaly = my_month > 100000::NUMERIC * 100000000 AND my_year < 2 * my_month; 209 210 SELECT open_time, clears_at INTO my_previous_open, my_previous_clear 211 FROM tops.deposit_monitor_state WHERE h_payto = my_h_payto; 212 my_new_episode = NOT FOUND OR my_previous_open IS DISTINCT FROM my_open_time 213 OR my_previous_clear IS NULL OR my_previous_clear <= my_seconds; 214 INSERT INTO tops.deposit_monitor_state (h_payto, open_time, clears_at) 215 VALUES (my_h_payto, my_open_time, 216 CASE WHEN my_anomaly THEN tops.deposit_anomaly_clears_at(my_h_payto, my_seconds) END) 217 ON CONFLICT (h_payto) DO UPDATE SET 218 open_time = EXCLUDED.open_time, clears_at = EXCLUDED.clears_at; 219 IF NOT my_anomaly 220 THEN 221 RETURN NEW; 222 END IF; 223 224 -- An open account can have expired rules. Carry their deadline unchanged: 225 -- monitoring must neither renew KYC nor shorten a permanent approval. 226 SELECT * INTO my_old_rules 227 FROM legitimization_outcomes WHERE h_payto = my_h_payto AND is_active 228 ORDER BY outcome_serial_id DESC LIMIT 1 FOR UPDATE; 229 IF NOT my_new_episode AND NOT COALESCE(my_old_rules.to_investigate, FALSE) 230 THEN 231 RETURN NEW; 232 END IF; 233 my_properties = COALESCE(my_old_rules.jproperties::JSONB, '{}'::JSONB); 234 my_reason = my_properties ->> 'INVESTIGATION_TRIGGER'; 235 IF NOT ('DEPOSIT_ANOMALY' = ANY(regexp_split_to_array(COALESCE(my_reason, ''), ';[[:space:]]*'))) 236 THEN 237 my_properties = my_properties || jsonb_build_object('INVESTIGATION_TRIGGER', 238 CASE WHEN COALESCE(my_reason, '') = '' THEN 'DEPOSIT_ANOMALY' 239 ELSE my_reason || '; DEPOSIT_ANOMALY' END); 240 END IF; 241 my_state = my_properties ->> 'INVESTIGATION_STATE'; 242 IF my_state IS NULL OR 243 (NOT COALESCE(my_old_rules.to_investigate, FALSE) AND 244 my_state NOT IN ('REPORTED_SUSPICION_SIMPLE', 'REPORTED_SUSPICION_SUBSTANTIATED')) 245 THEN 246 my_properties = my_properties || jsonb_build_object('INVESTIGATION_STATE', 'INVESTIGATION_PENDING'); 247 END IF; 248 IF COALESCE(my_old_rules.to_investigate, FALSE) AND 249 my_properties = my_old_rules.jproperties::JSONB 250 THEN 251 RETURN NEW; 252 END IF; 253 254 UPDATE legitimization_outcomes SET is_active = FALSE 255 WHERE h_payto = my_h_payto AND is_active; 256 INSERT INTO legitimization_outcomes 257 (h_payto, decision_time, expiration_time, jproperties, new_measure_name, 258 to_investigate, is_active, jnew_rules) 259 VALUES 260 (my_h_payto, my_now, COALESCE(my_old_rules.expiration_time, 9223372036854775807), 261 my_properties, my_old_rules.new_measure_name, TRUE, TRUE, my_old_rules.jnew_rules); 262 RETURN NEW; 263 END $$; 264 COMMENT ON FUNCTION tops_deposit_statistics_trigger 265 IS 'counts deposit increments and flags new TOPS deposit anomaly episodes'; 266 267 CREATE TRIGGER tops_batch_deposits_on_insert 268 AFTER INSERT OR UPDATE OF total_amount ON batch_deposits 269 FOR EACH ROW EXECUTE FUNCTION tops_deposit_statistics_trigger(); 270 271 COMMIT;