commit 48fc0adf616a531e8b106f06533ff720a71d7b6d
parent 5cd47837025ee5aac39accb5fc36882f4b7fc7b9
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Wed, 7 May 2025 17:55:03 +0200
merge exchange 5,6
Diffstat:
10 files changed, 164 insertions(+), 348 deletions(-)
diff --git a/src/exchangedb/0002-aml_history.sql b/src/exchangedb/0002-aml_history.sql
@@ -27,14 +27,10 @@ BEGIN
'CREATE TABLE IF NOT EXISTS %I'
'(aml_history_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
',h_payto BYTEA CHECK (LENGTH(h_payto)=32)'
- ',new_threshold taler_amount NOT NULL DEFAULT(0,0)'
- ',new_status INT4 NOT NULL DEFAULT(0)'
- ',decision_time INT8 NOT NULL DEFAULT(0)'
',justification TEXT NOT NULL'
- ',kyc_requirements TEXT'
- ',kyc_req_row INT8 NOT NULL DEFAULT(0)'
',decider_pub BYTEA CHECK (LENGTH(decider_pub)=32)'
',decider_sig BYTEA CHECK (LENGTH(decider_sig)=64)'
+ ',outcome_serial_id INT8 NOT NULL'
') %s ;'
,table_name
,'PARTITION BY HASH (h_payto)'
@@ -52,24 +48,6 @@ BEGIN
,partition_suffix
);
PERFORM comment_partitioned_column(
- 'new monthly inbound transaction limit below which we are OK'
- ,'new_threshold'
- ,table_name
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- '0 for all OK, 1 for AML decision required, 2 for account is frozen (prevents further transactions)'
- ,'new_status'
- ,table_name
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'when was the status changed'
- ,'decision_time'
- ,table_name
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
'human-readable justification for the status change'
,'justification'
,table_name
@@ -99,6 +77,12 @@ BEGIN
,table_name
,partition_suffix
);
+ PERFORM comment_partitioned_column(
+ 'Actual outcome for the account (included in what decider_sig signs over)'
+ ,'outcome_serial_id'
+ ,table_name
+ ,partition_suffix
+ );
END $$;
COMMENT ON FUNCTION create_table_aml_history
@@ -127,6 +111,21 @@ BEGIN
);
END $$;
+CREATE FUNCTION foreign_table_aml_history()
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name TEXT DEFAULT 'aml_history';
+BEGIN
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_legitimization_outcome'
+ ' FOREIGN KEY (outcome_serial_id)'
+ ' REFERENCES legitimization_outcomes (outcome_serial_id)'
+ );
+END $$;
+
INSERT INTO exchange_tables
(name
@@ -144,4 +143,10 @@ INSERT INTO exchange_tables
,'exchange-0002'
,'constrain'
,TRUE
+ ,FALSE),
+ ('aml_history'
+ ,'exchange-0005'
+ ,'foreign'
+ ,TRUE
,FALSE);
+
diff --git a/src/exchangedb/0002-kyc_attributes.sql b/src/exchangedb/0002-kyc_attributes.sql
@@ -27,13 +27,11 @@ BEGIN
'CREATE TABLE IF NOT EXISTS %I'
'(kyc_attributes_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)'
- ',kyc_prox BYTEA NOT NULL CHECK (LENGTH(kyc_prox)=32)'
- ',provider TEXT NOT NULL'
- ',satisfied_checks TEXT[] NOT NULL'
',collection_time INT8 NOT NULL'
',expiration_time INT8 NOT NULL'
',encrypted_attributes BYTEA NOT NULL'
',legitimization_serial INT8 NOT NULL'
+ ',trigger_outcome_serial INT8 NOT NULL'
') %s ;'
,table_name
,'PARTITION BY HASH (h_payto)'
@@ -51,12 +49,6 @@ BEGIN
,partition_suffix
);
PERFORM comment_partitioned_column(
- 'short hash of normalized full name and birthdate; used to efficiently find likely duplicate users'
- ,'kyc_prox'
- ,table_name
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
'time when the attributes were collected by the provider'
,'collection_time'
,table_name
@@ -69,12 +61,6 @@ BEGIN
,partition_suffix
);
PERFORM comment_partitioned_column(
- 'configuration section name of the provider that affirmed the attributes'
- ,'provider'
- ,table_name
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
'(encrypted) JSON object (as string) with the attributes'
,'encrypted_attributes'
,table_name
@@ -86,6 +72,12 @@ BEGIN
,table_name
,partition_suffix
);
+ PERFORM comment_partitioned_column(
+ 'ID of the outcome that was returned by the AML program based on the KYC data collected'
+ ,'trigger_outcome_serial'
+ ,table_name
+ ,partition_suffix
+ );
END $$;
COMMENT ON FUNCTION create_table_kyc_attributes
@@ -107,11 +99,11 @@ BEGIN
' ADD CONSTRAINT ' || table_name || '_serial_key '
'UNIQUE (kyc_attributes_serial_id)'
);
- -- To search similar users (e.g. during AML checks)
+ -- To search accounts
EXECUTE FORMAT (
- 'CREATE INDEX ' || table_name || '_similarity_index '
+ 'CREATE INDEX ' || table_name || '_h_payto_index '
'ON ' || table_name || ' '
- '(kyc_prox);'
+ '(h_payto);'
);
-- For garbage collection
EXECUTE FORMAT (
@@ -135,6 +127,12 @@ BEGIN
' FOREIGN KEY (legitimization_serial) '
' REFERENCES legitimization_processes (legitimization_process_serial_id)' -- ON DELETE SET NULL?
);
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_foreign_legitimization_outcomes'
+ ' FOREIGN KEY (trigger_outcome_serial)'
+ ' REFERENCES legitimization_outcomes (outcome_serial_id) ON DELETE CASCADE'
+ );
END $$;
diff --git a/src/exchangedb/0005-kyc_events.sql b/src/exchangedb/0002-kyc_events.sql
diff --git a/src/exchangedb/0002-kycauths_in.sql b/src/exchangedb/0002-kycauths_in.sql
@@ -0,0 +1,104 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2024 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/>
+--
+
+CREATE FUNCTION create_table_kycauths_in(
+ IN partition_suffix TEXT DEFAULT NULL
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name TEXT default 'kycauths_in';
+BEGIN
+ PERFORM create_partitioned_table(
+ 'CREATE TABLE %I'
+ '(kycauth_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
+ ',account_pub BYTEA CHECK (LENGTH(account_pub)=32)'
+ ',wire_reference INT8 NOT NULL'
+ ',credit taler_amount NOT NULL'
+ ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)'
+ ',exchange_account_section TEXT NOT NULL'
+ ',execution_date INT8 NOT NULL'
+ ',PRIMARY KEY(wire_source_h_payto, wire_reference)'
+ ') %s ;'
+ ,table_name
+ ,'PARTITION BY HASH (wire_source_h_payto)'
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_table(
+ 'list of transfers to register a key for KYC authentication, one per incoming wire transfer'
+ ,table_name
+ ,partition_suffix
+ );
+ -- FIXME: check that the *full* payto URI is indeed the best choice here,
+ -- given that this is mostly used for KYC, we may prefer the normalized
+ -- payto URI instead! Not sure, to be checked!
+ PERFORM comment_partitioned_column(
+ 'Identifies the debited bank account and KYC status by the hash over the full payto URI'
+ ,'wire_source_h_payto'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Public key to be associated with the account.'
+ ,'account_pub'
+ ,table_name
+ ,partition_suffix
+ );
+ PERFORM comment_partitioned_column(
+ 'Amount that was transferred into the account'
+ ,'credit'
+ ,table_name
+ ,partition_suffix
+ );
+END $$;
+
+
+CREATE FUNCTION constrain_table_kycauths_in(
+ IN partition_suffix TEXT
+)
+RETURNS void
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ table_name TEXT default 'kycauths_in';
+BEGIN
+ table_name = concat_ws('_', table_name, partition_suffix);
+ EXECUTE FORMAT (
+ 'ALTER TABLE ' || table_name ||
+ ' ADD CONSTRAINT ' || table_name || '_kycauth_in_serial_id_key'
+ ' UNIQUE (kycauth_in_serial_id)'
+ );
+END
+$$;
+
+INSERT INTO exchange_tables
+ (name
+ ,version
+ ,action
+ ,partitioned
+ ,by_range)
+ VALUES
+ ('kycauths_in'
+ ,'exchange-0002'
+ ,'create'
+ ,TRUE
+ ,FALSE),
+ ('kycauths_in'
+ ,'exchange-0002'
+ ,'constrain'
+ ,TRUE
+ ,FALSE);
diff --git a/src/exchangedb/0005-aml_history.sql b/src/exchangedb/0005-aml_history.sql
@@ -1,80 +0,0 @@
---
--- This file is part of TALER
--- Copyright (C) 2024 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/>
---
-
-CREATE FUNCTION alter_table_aml_history5(
- IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name TEXT DEFAULT 'aml_history';
-BEGIN
- PERFORM create_partitioned_table(
- 'ALTER TABLE %I'
- ' DROP COLUMN new_threshold'
- ',DROP COLUMN new_status'
- ',DROP COLUMN decision_time'
- ',DROP COLUMN kyc_requirements'
- ',DROP COLUMN kyc_req_row'
- ',ADD COLUMN outcome_serial_id INT8 NOT NULL'
- ';'
- ,table_name
- ,''
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'Actual outcome for the account (included in what decider_sig signs over)'
- ,'outcome_serial_id'
- ,table_name
- ,partition_suffix
- );
-END $$;
-
-
-CREATE FUNCTION foreign_table_aml_history5()
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name TEXT DEFAULT 'aml_history';
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE ' || table_name ||
- ' ADD CONSTRAINT ' || table_name || '_legitimization_outcome'
- ' FOREIGN KEY (outcome_serial_id)'
- ' REFERENCES legitimization_outcomes (outcome_serial_id)'
- );
-END $$;
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('aml_history5'
- ,'exchange-0005'
- ,'alter'
- ,TRUE
- ,FALSE),
- ('aml_history5'
- ,'exchange-0005'
- ,'foreign'
- ,TRUE
- ,FALSE);
diff --git a/src/exchangedb/0005-kyc_attributes.sql b/src/exchangedb/0005-kyc_attributes.sql
@@ -1,104 +0,0 @@
---
--- This file is part of TALER
--- Copyright (C) 2024 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/>
---
-
-CREATE FUNCTION alter_table_kyc_attributes5(
- IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name TEXT DEFAULT 'kyc_attributes';
-BEGIN
- PERFORM create_partitioned_table(
- 'ALTER TABLE %I'
- ' DROP COLUMN kyc_prox'
- ',DROP COLUMN provider'
- ',DROP COLUMN satisfied_checks'
- ',DROP CONSTRAINT kyc_attributes_pkey'
- ',ADD COLUMN trigger_outcome_serial INT8 NOT NULL'
- ';'
- ,table_name
- ,''
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'ID of the outcome that was returned by the AML program based on the KYC data collected'
- ,'trigger_outcome_serial'
- ,table_name
- ,partition_suffix
- );
-END $$;
-
-
-
-CREATE OR REPLACE FUNCTION constrain_table_kyc_attributes5(
- IN partition_suffix TEXT
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name TEXT DEFAULT 'kyc_attributes';
-BEGIN
- table_name = concat_ws('_', table_name, partition_suffix);
- -- To search accounts
- EXECUTE FORMAT (
- 'CREATE INDEX ' || table_name || '_h_payto_index '
- 'ON ' || table_name || ' '
- '(h_payto);'
- );
-END $$;
-
-
-CREATE FUNCTION foreign_table_kyc_attributes5()
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name TEXT DEFAULT 'kyc_attributes';
-BEGIN
- EXECUTE FORMAT (
- 'ALTER TABLE ' || table_name ||
- ' ADD CONSTRAINT ' || table_name || '_foreign_legitimization_outcomes'
- ' FOREIGN KEY (trigger_outcome_serial)'
- ' REFERENCES legitimization_outcomes (outcome_serial_id) ON DELETE CASCADE'
- );
-END $$;
-
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('kyc_attributes5'
- ,'exchange-0005'
- ,'alter'
- ,TRUE
- ,FALSE),
- ('kyc_attributes5'
- ,'exchange-0005'
- ,'constrain'
- ,TRUE
- ,FALSE),
- ('kyc_attributes5'
- ,'exchange-0005'
- ,'foreign'
- ,TRUE
- ,FALSE);
diff --git a/src/exchangedb/0005-kycauths_in.sql b/src/exchangedb/0005-kycauths_in.sql
@@ -1,104 +0,0 @@
---
--- This file is part of TALER
--- Copyright (C) 2024 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/>
---
-
-CREATE FUNCTION create_table_kycauths_in(
- IN partition_suffix TEXT DEFAULT NULL
-)
-RETURNS VOID
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name TEXT default 'kycauths_in';
-BEGIN
- PERFORM create_partitioned_table(
- 'CREATE TABLE %I'
- '(kycauth_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
- ',account_pub BYTEA CHECK (LENGTH(account_pub)=32)'
- ',wire_reference INT8 NOT NULL'
- ',credit taler_amount NOT NULL'
- ',wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32)'
- ',exchange_account_section TEXT NOT NULL'
- ',execution_date INT8 NOT NULL'
- ',PRIMARY KEY(wire_source_h_payto, wire_reference)'
- ') %s ;'
- ,table_name
- ,'PARTITION BY HASH (wire_source_h_payto)'
- ,partition_suffix
- );
- PERFORM comment_partitioned_table(
- 'list of transfers to register a key for KYC authentication, one per incoming wire transfer'
- ,table_name
- ,partition_suffix
- );
- -- FIXME: check that the *full* payto URI is indeed the best choice here,
- -- given that this is mostly used for KYC, we may prefer the normalized
- -- payto URI instead! Not sure, to be checked!
- PERFORM comment_partitioned_column(
- 'Identifies the debited bank account and KYC status by the hash over the full payto URI'
- ,'wire_source_h_payto'
- ,table_name
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'Public key to be associated with the account.'
- ,'account_pub'
- ,table_name
- ,partition_suffix
- );
- PERFORM comment_partitioned_column(
- 'Amount that was transferred into the account'
- ,'credit'
- ,table_name
- ,partition_suffix
- );
-END $$;
-
-
-CREATE FUNCTION constrain_table_kycauths_in(
- IN partition_suffix TEXT
-)
-RETURNS void
-LANGUAGE plpgsql
-AS $$
-DECLARE
- table_name TEXT default 'kycauths_in';
-BEGIN
- table_name = concat_ws('_', table_name, partition_suffix);
- EXECUTE FORMAT (
- 'ALTER TABLE ' || table_name ||
- ' ADD CONSTRAINT ' || table_name || '_kycauth_in_serial_id_key'
- ' UNIQUE (kycauth_in_serial_id)'
- );
-END
-$$;
-
-INSERT INTO exchange_tables
- (name
- ,version
- ,action
- ,partitioned
- ,by_range)
- VALUES
- ('kycauths_in'
- ,'exchange-0005'
- ,'create'
- ,TRUE
- ,FALSE),
- ('kycauths_in'
- ,'exchange-0005'
- ,'constrain'
- ,TRUE
- ,FALSE);
diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in
@@ -50,6 +50,16 @@ CREATE TYPE exchange_do_select_deposits_missing_wire_return_type
COMMENT ON TYPE exchange_do_select_deposits_missing_wire_return_type
IS 'Return type for exchange_do_select_deposits_missing_wire';
+CREATE TYPE exchange_do_select_aggregations_above_serial_return_type
+ AS
+ (
+ batch_deposit_serial_id INT8,
+ aggregation_serial_id INT8,
+ total_amount taler_amount
+ );
+COMMENT ON TYPE exchange_do_select_aggregations_above_serial_return_type
+ IS 'Return type for exchange_do_select_aggregations_above_serial';
+
#include "0002-denominations.sql"
#include "0002-denomination_revocations.sql"
@@ -69,6 +79,8 @@ COMMENT ON TYPE exchange_do_select_deposits_missing_wire_return_type
#include "0002-legitimization_measures.sql"
#include "0002-legitimization_outcomes.sql"
#include "0002-legitimization_processes.sql"
+
+
#include "0002-reserves.sql"
#include "0002-reserve_history.sql"
#include "0002-reserves_in.sql"
@@ -110,7 +122,11 @@ COMMENT ON TYPE exchange_do_select_deposits_missing_wire_return_type
#include "0002-partner_accounts.sql"
#include "0002-purse_actions.sql"
#include "0002-purse_deletion.sql"
+
#include "0002-kyc_attributes.sql"
+
+#include "0002-kycauths_in.sql"
+#include "0002-kyc_events.sql"
#include "0002-aml_staff.sql"
#include "0002-aml_history.sql"
diff --git a/src/exchangedb/exchange-0005.sql.in b/src/exchangedb/exchange-0005.sql.in
@@ -19,13 +19,4 @@ BEGIN;
SELECT _v.register_patch('exchange-0005', NULL, NULL);
SET search_path TO exchange;
-#include "0005-legitimization_measures.sql"
-#include "0005-legitimization_outcomes.sql"
-#include "0005-legitimization_processes.sql"
-#include "0005-kyc_attributes.sql"
-#include "0005-aml_history.sql"
-#include "0005-kyc_events.sql"
-#include "0005-kycauths_in.sql"
-
-
COMMIT;
diff --git a/src/exchangedb/exchange-0006.sql.in b/src/exchangedb/exchange-0006.sql.in
@@ -19,14 +19,4 @@ BEGIN;
SELECT _v.register_patch('exchange-0006', NULL, NULL);
SET search_path TO exchange;
-CREATE TYPE exchange_do_select_aggregations_above_serial_return_type
- AS
- (
- batch_deposit_serial_id INT8,
- aggregation_serial_id INT8,
- total_amount taler_amount
- );
-COMMENT ON TYPE exchange_do_select_aggregations_above_serial_return_type
- IS 'Return type for exchange_do_select_aggregations_above_serial';
-
COMMIT;
