test_tops.sql (10961B)
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 17 -- The fixtures exercise the installed production trigger, without copying its 18 -- implementation. Each scenario uses a separate account in a scratch database. 19 SET search_path TO exchange, pg_temp; 20 SET TIME ZONE 'Europe/Berlin'; 21 CREATE SCHEMA test_tops; 22 23 CREATE FUNCTION test_tops.hash(id INT) RETURNS BYTEA LANGUAGE SQL IMMUTABLE AS $$ 24 SELECT decode(lpad(to_hex(id),64,'0'),'hex'); 25 $$; 26 CREATE FUNCTION test_tops.amount(n NUMERIC) RETURNS taler_amount LANGUAGE SQL IMMUTABLE AS $$ 27 SELECT (trunc(n)::INT8, ((n-trunc(n))*100000000)::INT4)::taler_amount; 28 $$; 29 CREATE FUNCTION test_tops.account(id INT, opened BOOL DEFAULT TRUE, 30 expired BOOL DEFAULT FALSE, props JSONB DEFAULT '{}'::JSONB) 31 RETURNS VOID LANGUAGE plpgsql AS $$ 32 DECLARE 33 now_us INT8 := EXTRACT(epoch FROM exchange_now())::INT8 * 1000000; 34 expiry INT8 := CASE WHEN expired THEN now_us-1000000 ELSE 9223372036854775807 END; 35 BEGIN 36 INSERT INTO kyc_targets(h_normalized_payto,is_wallet,open_time) 37 VALUES (test_tops.hash(id),FALSE,CASE WHEN opened THEN now_us END); 38 INSERT INTO wire_targets(wire_target_h_payto,h_normalized_payto,payto_uri) 39 VALUES (test_tops.hash(id),test_tops.hash(id),'payto://x-taler-bank/localhost/test-'||id); 40 INSERT INTO legitimization_outcomes(h_payto,decision_time,expiration_time,jproperties,jnew_rules,to_investigate) 41 VALUES (test_tops.hash(id),now_us,expiry,props, 42 jsonb_build_object('rules','[]'::JSONB,'custom_measures','{}'::JSONB, 43 'expiration_time',jsonb_build_object('t_s',CASE WHEN expired THEN to_jsonb(expiry/1000000) ELSE '"never"'::JSONB END)), 44 FALSE); 45 END $$; 46 CREATE FUNCTION test_tops.deposit(id INT, n NUMERIC) RETURNS INT8 LANGUAGE plpgsql AS $$ 47 DECLARE 48 batch INT8; 49 now_us INT8 := EXTRACT(epoch FROM exchange_now())::INT8 * 1000000; 50 BEGIN 51 INSERT INTO batch_deposits(shard,merchant_pub,merchant_sig,wallet_timestamp,exchange_timestamp, 52 refund_deadline,wire_deadline,h_contract_terms,wire_salt,wire_target_h_payto,total_amount,total_without_fee) 53 VALUES (id,test_tops.hash(id),random_bytea(64),now_us,now_us,now_us,now_us, 54 random_bytea(64),random_bytea(16),test_tops.hash(id),test_tops.amount(n),test_tops.amount(n)) 55 RETURNING batch_deposit_serial_id INTO batch; 56 RETURN batch; 57 END $$; 58 CREATE FUNCTION test_tops.active(id INT) RETURNS legitimization_outcomes LANGUAGE SQL AS $$ 59 SELECT * FROM legitimization_outcomes WHERE h_payto=test_tops.hash(id) AND is_active; 60 $$; 61 CREATE FUNCTION test_tops.outcomes(id INT) RETURNS INT8 LANGUAGE SQL AS $$ 62 SELECT count(*) FROM legitimization_outcomes WHERE h_payto=test_tops.hash(id); 63 $$; 64 CREATE FUNCTION test_tops.officer(id INT, investigating BOOL, props JSONB) 65 RETURNS VOID LANGUAGE plpgsql AS $$ 66 DECLARE prev legitimization_outcomes; 67 BEGIN 68 prev := test_tops.active(id); 69 UPDATE legitimization_outcomes SET is_active=FALSE WHERE h_payto=test_tops.hash(id); 70 INSERT INTO legitimization_outcomes(h_payto,decision_time,expiration_time,jproperties,jnew_rules,to_investigate) 71 VALUES (prev.h_payto,EXTRACT(epoch FROM exchange_now())::INT8*1000000, 72 prev.expiration_time,props,prev.jnew_rules,investigating); 73 END $$; 74 75 -- Closed/not-yet-open accounts are counted but not monitored. Opening starts 76 -- monitoring immediately, even if the rules expired before the account opened. 77 DO $$ 78 DECLARE before legitimization_outcomes; after legitimization_outcomes; 79 BEGIN 80 PERFORM test_tops.account(1,FALSE,TRUE,'{"CUSTOMER_LABEL":"preserve me"}'); 81 before := test_tops.active(1); 82 PERFORM test_tops.deposit(1,200000); 83 ASSERT NOT (test_tops.active(1)).to_investigate, 'unopened account was flagged'; 84 UPDATE kyc_targets SET open_time=EXTRACT(epoch FROM exchange_now())::INT8*1000000 85 WHERE h_normalized_payto=test_tops.hash(1); 86 PERFORM test_tops.deposit(1,1); 87 after := test_tops.active(1); 88 ASSERT after.to_investigate, 'expired KYC suppressed monitoring'; 89 ASSERT after.expiration_time=before.expiration_time, 'expired KYC was renewed'; 90 ASSERT after.jnew_rules=before.jnew_rules, 'KYC rules changed'; 91 ASSERT (after.jproperties::JSONB)->>'CUSTOMER_LABEL'='preserve me'; 92 ASSERT (after.jproperties::JSONB)->>'INVESTIGATION_STATE'='INVESTIGATION_PENDING'; 93 ASSERT (after.jproperties::JSONB)->>'INVESTIGATION_TRIGGER'='DEPOSIT_ANOMALY'; 94 ASSERT NOT ((after.jproperties::JSONB) ? 'AML_INVESTIGATION_STATE'); 95 ASSERT after.decision_time=EXTRACT(epoch FROM CURRENT_TIMESTAMP(0))::INT8*1000000, 96 'protocol timestamp depends on session timezone'; 97 ASSERT NOT EXISTS (SELECT FROM legitimization_outcomes WHERE h_payto=test_tops.hash(1) 98 AND is_active AND expiration_time >= after.decision_time), 'expired rules became valid'; 99 END $$; 100 101 -- The floor is strict and fractional amounts matter. Batch extensions count 102 -- only their increments and do not create extra deposit-transaction counts. 103 DO $$ 104 DECLARE batch INT8; count_before INT8; total taler_amount; 105 BEGIN 106 PERFORM test_tops.account(2); 107 batch := test_tops.deposit(2,100000); 108 ASSERT NOT (test_tops.active(2)).to_investigate, 'equal to the floor is not above it'; 109 UPDATE batch_deposits SET total_amount=test_tops.amount(100000.00000001) 110 WHERE batch_deposit_serial_id=batch; 111 ASSERT (test_tops.active(2)).to_investigate, 'positive batch increment was ignored'; 112 ASSERT (test_tops.active(2)).expiration_time=9223372036854775807, 'permanent approval shortened'; 113 count_before := test_tops.outcomes(2); 114 UPDATE batch_deposits SET total_amount=total_amount WHERE batch_deposit_serial_id=batch; 115 UPDATE batch_deposits SET total_amount=test_tops.amount(100001.00000001) 116 WHERE batch_deposit_serial_id=batch; 117 ASSERT test_tops.outcomes(2)=count_before, 'repeated deposits duplicate an investigation'; 118 SELECT (rvalue).* INTO total FROM exchange_statistic_interval_amount_get('deposit-volume',test_tops.hash(2)) 119 WHERE range=31449600; 120 ASSERT total=test_tops.amount(100001.00000001), 'batch volume was duplicated or lost'; 121 ASSERT (SELECT rvalue=1 FROM exchange_statistic_interval_number_get('deposit-transactions',test_tops.hash(2)) 122 WHERE range=31449600), 'batch extension counted as another transaction'; 123 END $$; 124 125 -- Clearing a case suppresses the current episode; a later threshold crossing 126 -- reopens it after deposits have aged out of the monthly window. 127 DO $$ 128 DECLARE n INT8; 129 BEGIN 130 PERFORM test_tops.officer(2,FALSE,'{"INVESTIGATION_STATE":"INVESTIGATION_COMPLETED_WITHOUT_SUSPICION","INVESTIGATION_TRIGGER":"DEPOSIT_ANOMALY"}'); 131 n := test_tops.outcomes(2); 132 PERFORM test_tops.deposit(2,1); 133 ASSERT NOT (test_tops.active(2)).to_investigate, 'clearance immediately undone'; 134 ASSERT test_tops.outcomes(2)=n; 135 PERFORM set_config('taler.timetravel_us',(29::INT8*86400000000)::TEXT,FALSE); 136 PERFORM test_tops.deposit(2,200000); 137 ASSERT (test_tops.active(2)).to_investigate, 'new episode did not reopen investigation'; 138 ASSERT test_tops.outcomes(2)=n+1; 139 ASSERT (test_tops.active(2)).jproperties::JSONB->>'INVESTIGATION_TRIGGER'='DEPOSIT_ANOMALY'; 140 END $$; 141 142 -- Preserve reporting states, both for an existing case and when starting a 143 -- new investigation after an earlier MROS report. Append the reason only once. 144 DO $$ 145 DECLARE n INT8; 146 BEGIN 147 PERFORM test_tops.account(3); 148 PERFORM test_tops.officer(3,TRUE,'{"INVESTIGATION_STATE":"REPORTED_SUSPICION_SIMPLE","INVESTIGATION_TRIGGER":"SANCTION_LIST_MATCH"}'); 149 PERFORM test_tops.deposit(3,200000); 150 ASSERT (test_tops.active(3)).jproperties::JSONB->>'INVESTIGATION_STATE'='REPORTED_SUSPICION_SIMPLE'; 151 ASSERT (test_tops.active(3)).jproperties::JSONB->>'INVESTIGATION_TRIGGER'='SANCTION_LIST_MATCH; DEPOSIT_ANOMALY'; 152 n := test_tops.outcomes(3); 153 PERFORM test_tops.deposit(3,1); 154 ASSERT test_tops.outcomes(3)=n, 'reason was appended repeatedly'; 155 PERFORM test_tops.account(4,TRUE,FALSE,'{"INVESTIGATION_STATE":"REPORTED_SUSPICION_SUBSTANTIATED"}'); 156 PERFORM test_tops.deposit(4,200000); 157 ASSERT (test_tops.active(4)).to_investigate; 158 ASSERT (test_tops.active(4)).jproperties::JSONB->>'INVESTIGATION_STATE'='REPORTED_SUSPICION_SUBSTANTIATED'; 159 PERFORM test_tops.account(5); 160 UPDATE kyc_targets SET close_time=EXTRACT(epoch FROM exchange_now())::INT8*1000000 161 WHERE h_normalized_payto=test_tops.hash(5); 162 PERFORM test_tops.deposit(5,200000); 163 ASSERT NOT (test_tops.active(5)).to_investigate, 'closed account was flagged'; 164 END $$; 165 166 -- The comparison is strict too, and uses all fractional units. 167 DO $$ 168 BEGIN 169 PERFORM set_config('taler.timetravel_us','0',FALSE); 170 PERFORM test_tops.account(6,FALSE); 171 PERFORM test_tops.deposit(6,200000); 172 UPDATE kyc_targets SET open_time=EXTRACT(epoch FROM exchange_now())::INT8*1000000 173 WHERE h_normalized_payto=test_tops.hash(6); 174 PERFORM set_config('taler.timetravel_us',(60::INT8*86400000000)::TEXT,FALSE); 175 PERFORM test_tops.deposit(6,200000); 176 ASSERT NOT (test_tops.active(6)).to_investigate, 'equal historical volume is not exceeded'; 177 PERFORM test_tops.deposit(6,0.00000001); 178 ASSERT (test_tops.active(6)).to_investigate, 'fractional crossing was ignored'; 179 END $$; 180 181 -- An episode can clear and become anomalous again solely through aging, 182 -- entirely between two deposits. An officer's old clearance must not suppress 183 -- this later episode just because both sampled endpoints are anomalous. 184 DO $$ 185 BEGIN 186 PERFORM set_config('taler.timetravel_us',(2::INT8*86400000000)::TEXT,FALSE); 187 PERFORM test_tops.account(7,FALSE); 188 PERFORM test_tops.deposit(7,100000); 189 PERFORM set_config('taler.timetravel_us',(337::INT8*86400000000)::TEXT,FALSE); 190 PERFORM test_tops.deposit(7,60000); 191 UPDATE kyc_targets SET open_time=EXTRACT(epoch FROM exchange_now())::INT8*1000000 192 WHERE h_normalized_payto=test_tops.hash(7); 193 PERFORM set_config('taler.timetravel_us',(340::INT8*86400000000)::TEXT,FALSE); 194 PERFORM test_tops.deposit(7,110000); 195 ASSERT (test_tops.active(7)).to_investigate; 196 PERFORM test_tops.officer(7,FALSE,'{"INVESTIGATION_STATE":"INVESTIGATION_COMPLETED_WITHOUT_SUSPICION"}'); 197 -- Day 365: 60,000 leaves the month, making 110,000 < 100,000 + 60,000. 198 -- Day 366: 100,000 leaves the year, making 110,000 > 60,000 again. 199 PERFORM set_config('taler.timetravel_us',(367::INT8*86400000000)::TEXT,FALSE); 200 PERFORM test_tops.deposit(7,1); 201 ASSERT (test_tops.active(7)).to_investigate, 'missed an episode between deposits'; 202 END $$;