commit c22149d02a97c6d61eb5dfd1b0d1c7af3ed4cb4e
parent 9fbc75ddca21dedc3a1bc256a9f87ac19d4ed221
Author: Florian Dold <dold@taler.net>
Date: Mon, 7 Sep 2026 12:37:13 +0200
exchange TOPS: add SQL regression coverage for monitoring decisions
Install the customization in a scratch database and exercise the real
deposit trigger. Cover fractional thresholds, batch increments, account
status, preserved KYC and reporting decisions, and anomaly episodes
that clear and recur between deposits.
Issue: https://bugs.taler.net/n/9639
Diffstat:
3 files changed, 273 insertions(+), 1 deletion(-)
diff --git a/src/exchange-tools/meson.build b/src/exchange-tools/meson.build
@@ -55,7 +55,7 @@ executable(
)
-executable(
+exchange_dbinit = executable(
'taler-exchange-dbinit',
['taler-exchange-dbinit.c'],
dependencies: [
@@ -70,3 +70,18 @@ executable(
include_directories: [incdir, configuration_inc],
install: true,
)
+
+# Install the customization in a fresh database before exercising its triggers.
+test_tops_sh = configure_file(
+ input: '../exchangedb/test_tops.sh', output: 'test_tops.sh', copy: true,
+)
+configure_file(
+ input: '../exchangedb/test_tops.sql', output: 'test_tops.sql', copy: true,
+)
+test(
+ 'test_tops', test_tops_sh,
+ workdir: meson.current_build_dir(), suite: ['exchangedb'],
+ depends: [exchange_dbinit, exchangedb_sql_targets],
+ env: {'TALER_BUILD_ROOT': meson.project_build_root()},
+ is_parallel: false, timeout: 120,
+)
diff --git a/src/exchangedb/test_tops.sh b/src/exchangedb/test_tops.sh
@@ -0,0 +1,55 @@
+#!/bin/sh
+# This file is in the public domain.
+#
+# Driver for TOPS SQL customization tests. It provisions a scratch
+# database of its own, installs TOPS and runs test_tops.sql, then removes the
+# database again. No pre-existing database is ever touched: the name is
+# derived from the PID and is deliberately *not* taken from the environment.
+set -eu
+
+# Where the build tree lives; meson passes this, the fallback is for
+# running the script by hand from its build directory.
+BUILD_ROOT="${TALER_BUILD_ROOT:-$(cd ../../.. && pwd)}"
+SQL_DIR="${TALER_SQL_DIR:-${BUILD_ROOT}/src/exchangedb/sql-schema}"
+
+# Skip (77) rather than fail if there is no usable PostgreSQL around.
+command -v createdb > /dev/null 2>&1 || exit 77
+command -v dropdb > /dev/null 2>&1 || exit 77
+psql -l < /dev/null > /dev/null 2>&1 || exit 77
+
+DBNAME="taler_exdb_tops_$$"
+CONF="test_tops_$$.conf"
+
+# The freshly built libraries must outrank any installed copy, as
+# LD_LIBRARY_PATH beats the build tree's RUNPATH.
+for d in "${BUILD_ROOT}"/src/*/; do
+ LD_LIBRARY_PATH="${d%/}${LD_LIBRARY_PATH:+:}${LD_LIBRARY_PATH:-}"
+done
+export LD_LIBRARY_PATH
+
+cleanup ()
+{
+ dropdb --if-exists "$DBNAME" > /dev/null 2>&1 || true
+ rm -f "$CONF"
+}
+trap cleanup EXIT
+
+cat > "$CONF" <<EOF
+[exchange]
+CURRENCY = CHF
+BASE_URL = http://localhost/
+[exchangedb-postgres]
+CONFIG = postgres:///${DBNAME}
+SQL_DIR = ${SQL_DIR}/
+[exchangedb]
+MAX_AML_PROGRAM_RUNTIME = 1 minute
+IDLE_RESERVE_EXPIRATION_TIME = 4 weeks
+LEGAL_RESERVE_EXPIRATION_TIME = 7 years
+AGGREGATOR_SHIFT = 1s
+DEFAULT_PURSE_LIMIT = 1
+EOF
+
+createdb "$DBNAME" > /dev/null 2>&1 || exit 77
+
+"${BUILD_ROOT}/src/exchange-tools/taler-exchange-dbinit" -c "$CONF" -e tops
+psql -X -q -v ON_ERROR_STOP=1 -d "$DBNAME" -f test_tops.sql
diff --git a/src/exchangedb/test_tops.sql b/src/exchangedb/test_tops.sql
@@ -0,0 +1,202 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2026 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
+--
+
+-- The fixtures exercise the installed production trigger, without copying its
+-- implementation. Each scenario uses a separate account in a scratch database.
+SET search_path TO exchange, pg_temp;
+SET TIME ZONE 'Europe/Berlin';
+CREATE SCHEMA test_tops;
+
+CREATE FUNCTION test_tops.hash(id INT) RETURNS BYTEA LANGUAGE SQL IMMUTABLE AS $$
+ SELECT decode(lpad(to_hex(id),64,'0'),'hex');
+$$;
+CREATE FUNCTION test_tops.amount(n NUMERIC) RETURNS taler_amount LANGUAGE SQL IMMUTABLE AS $$
+ SELECT (trunc(n)::INT8, ((n-trunc(n))*100000000)::INT4)::taler_amount;
+$$;
+CREATE FUNCTION test_tops.account(id INT, opened BOOL DEFAULT TRUE,
+ expired BOOL DEFAULT FALSE, props JSONB DEFAULT '{}'::JSONB)
+RETURNS VOID LANGUAGE plpgsql AS $$
+DECLARE
+ now_us INT8 := EXTRACT(epoch FROM exchange_now())::INT8 * 1000000;
+ expiry INT8 := CASE WHEN expired THEN now_us-1000000 ELSE 9223372036854775807 END;
+BEGIN
+ INSERT INTO kyc_targets(h_normalized_payto,is_wallet,open_time)
+ VALUES (test_tops.hash(id),FALSE,CASE WHEN opened THEN now_us END);
+ INSERT INTO wire_targets(wire_target_h_payto,h_normalized_payto,payto_uri)
+ VALUES (test_tops.hash(id),test_tops.hash(id),'payto://x-taler-bank/localhost/test-'||id);
+ INSERT INTO legitimization_outcomes(h_payto,decision_time,expiration_time,jproperties,jnew_rules,to_investigate)
+ VALUES (test_tops.hash(id),now_us,expiry,props,
+ jsonb_build_object('rules','[]'::JSONB,'custom_measures','{}'::JSONB,
+ 'expiration_time',jsonb_build_object('t_s',CASE WHEN expired THEN to_jsonb(expiry/1000000) ELSE '"never"'::JSONB END)),
+ FALSE);
+END $$;
+CREATE FUNCTION test_tops.deposit(id INT, n NUMERIC) RETURNS INT8 LANGUAGE plpgsql AS $$
+DECLARE
+ batch INT8;
+ now_us INT8 := EXTRACT(epoch FROM exchange_now())::INT8 * 1000000;
+BEGIN
+ INSERT INTO batch_deposits(shard,merchant_pub,merchant_sig,wallet_timestamp,exchange_timestamp,
+ refund_deadline,wire_deadline,h_contract_terms,wire_salt,wire_target_h_payto,total_amount,total_without_fee)
+ VALUES (id,test_tops.hash(id),random_bytea(64),now_us,now_us,now_us,now_us,
+ random_bytea(64),random_bytea(16),test_tops.hash(id),test_tops.amount(n),test_tops.amount(n))
+ RETURNING batch_deposit_serial_id INTO batch;
+ RETURN batch;
+END $$;
+CREATE FUNCTION test_tops.active(id INT) RETURNS legitimization_outcomes LANGUAGE SQL AS $$
+ SELECT * FROM legitimization_outcomes WHERE h_payto=test_tops.hash(id) AND is_active;
+$$;
+CREATE FUNCTION test_tops.outcomes(id INT) RETURNS INT8 LANGUAGE SQL AS $$
+ SELECT count(*) FROM legitimization_outcomes WHERE h_payto=test_tops.hash(id);
+$$;
+CREATE FUNCTION test_tops.officer(id INT, investigating BOOL, props JSONB)
+RETURNS VOID LANGUAGE plpgsql AS $$
+DECLARE prev legitimization_outcomes;
+BEGIN
+ prev := test_tops.active(id);
+ UPDATE legitimization_outcomes SET is_active=FALSE WHERE h_payto=test_tops.hash(id);
+ INSERT INTO legitimization_outcomes(h_payto,decision_time,expiration_time,jproperties,jnew_rules,to_investigate)
+ VALUES (prev.h_payto,EXTRACT(epoch FROM exchange_now())::INT8*1000000,
+ prev.expiration_time,props,prev.jnew_rules,investigating);
+END $$;
+
+-- Closed/not-yet-open accounts are counted but not monitored. Opening starts
+-- monitoring immediately, even if the rules expired before the account opened.
+DO $$
+DECLARE before legitimization_outcomes; after legitimization_outcomes;
+BEGIN
+ PERFORM test_tops.account(1,FALSE,TRUE,'{"CUSTOMER_LABEL":"preserve me"}');
+ before := test_tops.active(1);
+ PERFORM test_tops.deposit(1,200000);
+ ASSERT NOT (test_tops.active(1)).to_investigate, 'unopened account was flagged';
+ UPDATE kyc_targets SET open_time=EXTRACT(epoch FROM exchange_now())::INT8*1000000
+ WHERE h_normalized_payto=test_tops.hash(1);
+ PERFORM test_tops.deposit(1,1);
+ after := test_tops.active(1);
+ ASSERT after.to_investigate, 'expired KYC suppressed monitoring';
+ ASSERT after.expiration_time=before.expiration_time, 'expired KYC was renewed';
+ ASSERT after.jnew_rules=before.jnew_rules, 'KYC rules changed';
+ ASSERT (after.jproperties::JSONB)->>'CUSTOMER_LABEL'='preserve me';
+ ASSERT (after.jproperties::JSONB)->>'INVESTIGATION_STATE'='INVESTIGATION_PENDING';
+ ASSERT (after.jproperties::JSONB)->>'INVESTIGATION_TRIGGER'='DEPOSIT_ANOMALY';
+ ASSERT NOT ((after.jproperties::JSONB) ? 'AML_INVESTIGATION_STATE');
+ ASSERT after.decision_time=EXTRACT(epoch FROM CURRENT_TIMESTAMP(0))::INT8*1000000,
+ 'protocol timestamp depends on session timezone';
+ ASSERT NOT EXISTS (SELECT FROM legitimization_outcomes WHERE h_payto=test_tops.hash(1)
+ AND is_active AND expiration_time >= after.decision_time), 'expired rules became valid';
+END $$;
+
+-- The floor is strict and fractional amounts matter. Batch extensions count
+-- only their increments and do not create extra deposit-transaction counts.
+DO $$
+DECLARE batch INT8; count_before INT8; total taler_amount;
+BEGIN
+ PERFORM test_tops.account(2);
+ batch := test_tops.deposit(2,100000);
+ ASSERT NOT (test_tops.active(2)).to_investigate, 'equal to the floor is not above it';
+ UPDATE batch_deposits SET total_amount=test_tops.amount(100000.00000001)
+ WHERE batch_deposit_serial_id=batch;
+ ASSERT (test_tops.active(2)).to_investigate, 'positive batch increment was ignored';
+ ASSERT (test_tops.active(2)).expiration_time=9223372036854775807, 'permanent approval shortened';
+ count_before := test_tops.outcomes(2);
+ UPDATE batch_deposits SET total_amount=total_amount WHERE batch_deposit_serial_id=batch;
+ UPDATE batch_deposits SET total_amount=test_tops.amount(100001.00000001)
+ WHERE batch_deposit_serial_id=batch;
+ ASSERT test_tops.outcomes(2)=count_before, 'repeated deposits duplicate an investigation';
+ SELECT (rvalue).* INTO total FROM exchange_statistic_interval_amount_get('deposit-volume',test_tops.hash(2))
+ WHERE range=31449600;
+ ASSERT total=test_tops.amount(100001.00000001), 'batch volume was duplicated or lost';
+ ASSERT (SELECT rvalue=1 FROM exchange_statistic_interval_number_get('deposit-transactions',test_tops.hash(2))
+ WHERE range=31449600), 'batch extension counted as another transaction';
+END $$;
+
+-- Clearing a case suppresses the current episode; a later threshold crossing
+-- reopens it after deposits have aged out of the monthly window.
+DO $$
+DECLARE n INT8;
+BEGIN
+ PERFORM test_tops.officer(2,FALSE,'{"INVESTIGATION_STATE":"INVESTIGATION_COMPLETED_WITHOUT_SUSPICION","INVESTIGATION_TRIGGER":"DEPOSIT_ANOMALY"}');
+ n := test_tops.outcomes(2);
+ PERFORM test_tops.deposit(2,1);
+ ASSERT NOT (test_tops.active(2)).to_investigate, 'clearance immediately undone';
+ ASSERT test_tops.outcomes(2)=n;
+ PERFORM set_config('taler.timetravel_us',(29::INT8*86400000000)::TEXT,FALSE);
+ PERFORM test_tops.deposit(2,200000);
+ ASSERT (test_tops.active(2)).to_investigate, 'new episode did not reopen investigation';
+ ASSERT test_tops.outcomes(2)=n+1;
+ ASSERT (test_tops.active(2)).jproperties::JSONB->>'INVESTIGATION_TRIGGER'='DEPOSIT_ANOMALY';
+END $$;
+
+-- Preserve reporting states, both for an existing case and when starting a
+-- new investigation after an earlier MROS report. Append the reason only once.
+DO $$
+DECLARE n INT8;
+BEGIN
+ PERFORM test_tops.account(3);
+ PERFORM test_tops.officer(3,TRUE,'{"INVESTIGATION_STATE":"REPORTED_SUSPICION_SIMPLE","INVESTIGATION_TRIGGER":"SANCTION_LIST_MATCH"}');
+ PERFORM test_tops.deposit(3,200000);
+ ASSERT (test_tops.active(3)).jproperties::JSONB->>'INVESTIGATION_STATE'='REPORTED_SUSPICION_SIMPLE';
+ ASSERT (test_tops.active(3)).jproperties::JSONB->>'INVESTIGATION_TRIGGER'='SANCTION_LIST_MATCH; DEPOSIT_ANOMALY';
+ n := test_tops.outcomes(3);
+ PERFORM test_tops.deposit(3,1);
+ ASSERT test_tops.outcomes(3)=n, 'reason was appended repeatedly';
+ PERFORM test_tops.account(4,TRUE,FALSE,'{"INVESTIGATION_STATE":"REPORTED_SUSPICION_SUBSTANTIATED"}');
+ PERFORM test_tops.deposit(4,200000);
+ ASSERT (test_tops.active(4)).to_investigate;
+ ASSERT (test_tops.active(4)).jproperties::JSONB->>'INVESTIGATION_STATE'='REPORTED_SUSPICION_SUBSTANTIATED';
+ PERFORM test_tops.account(5);
+ UPDATE kyc_targets SET close_time=EXTRACT(epoch FROM exchange_now())::INT8*1000000
+ WHERE h_normalized_payto=test_tops.hash(5);
+ PERFORM test_tops.deposit(5,200000);
+ ASSERT NOT (test_tops.active(5)).to_investigate, 'closed account was flagged';
+END $$;
+
+-- The comparison is strict too, and uses all fractional units.
+DO $$
+BEGIN
+ PERFORM set_config('taler.timetravel_us','0',FALSE);
+ PERFORM test_tops.account(6,FALSE);
+ PERFORM test_tops.deposit(6,200000);
+ UPDATE kyc_targets SET open_time=EXTRACT(epoch FROM exchange_now())::INT8*1000000
+ WHERE h_normalized_payto=test_tops.hash(6);
+ PERFORM set_config('taler.timetravel_us',(60::INT8*86400000000)::TEXT,FALSE);
+ PERFORM test_tops.deposit(6,200000);
+ ASSERT NOT (test_tops.active(6)).to_investigate, 'equal historical volume is not exceeded';
+ PERFORM test_tops.deposit(6,0.00000001);
+ ASSERT (test_tops.active(6)).to_investigate, 'fractional crossing was ignored';
+END $$;
+
+-- An episode can clear and become anomalous again solely through aging,
+-- entirely between two deposits. An officer's old clearance must not suppress
+-- this later episode just because both sampled endpoints are anomalous.
+DO $$
+BEGIN
+ PERFORM set_config('taler.timetravel_us',(2::INT8*86400000000)::TEXT,FALSE);
+ PERFORM test_tops.account(7,FALSE);
+ PERFORM test_tops.deposit(7,100000);
+ PERFORM set_config('taler.timetravel_us',(337::INT8*86400000000)::TEXT,FALSE);
+ PERFORM test_tops.deposit(7,60000);
+ UPDATE kyc_targets SET open_time=EXTRACT(epoch FROM exchange_now())::INT8*1000000
+ WHERE h_normalized_payto=test_tops.hash(7);
+ PERFORM set_config('taler.timetravel_us',(340::INT8*86400000000)::TEXT,FALSE);
+ PERFORM test_tops.deposit(7,110000);
+ ASSERT (test_tops.active(7)).to_investigate;
+ PERFORM test_tops.officer(7,FALSE,'{"INVESTIGATION_STATE":"INVESTIGATION_COMPLETED_WITHOUT_SUSPICION"}');
+ -- Day 365: 60,000 leaves the month, making 110,000 < 100,000 + 60,000.
+ -- Day 366: 100,000 leaves the year, making 110,000 > 60,000 again.
+ PERFORM set_config('taler.timetravel_us',(367::INT8*86400000000)::TEXT,FALSE);
+ PERFORM test_tops.deposit(7,1);
+ ASSERT (test_tops.active(7)).to_investigate, 'missed an episode between deposits';
+END $$;
