diff options
author | Christian Grothoff <christian@grothoff.org> | 2024-04-22 21:07:40 +0200 |
---|---|---|
committer | Christian Grothoff <christian@grothoff.org> | 2024-07-29 12:18:38 +0200 |
commit | 19f98d1575261034b4fa7c634be9be0999a17533 (patch) | |
tree | bde825d809e8c669f4d30b523d3237de79510c62 /src | |
parent | 4f433353ebcb9d1bd64171e29402ac15c22dfacf (diff) | |
download | exchange-19f98d1575261034b4fa7c634be9be0999a17533.tar.xz |
DB schema change for DD23
Diffstat (limited to 'src')
-rw-r--r-- | src/exchangedb/.gitignore | 1 | ||||
-rw-r--r-- | src/exchangedb/0002-kyc_attributes.sql | 2 | ||||
-rw-r--r-- | src/exchangedb/0005-aml_history.sql | 80 | ||||
-rw-r--r-- | src/exchangedb/0005-aml_status.sql | 46 | ||||
-rw-r--r-- | src/exchangedb/0005-kyc_attributes.sql | 85 | ||||
-rw-r--r-- | src/exchangedb/0005-kyc_events.sql | 29 | ||||
-rw-r--r-- | src/exchangedb/0005-legitimization_measures.sql | 146 | ||||
-rw-r--r-- | src/exchangedb/0005-legitimization_outcomes.sql | 161 | ||||
-rw-r--r-- | src/exchangedb/0005-legitimization_processes.sql | 82 | ||||
-rw-r--r-- | src/exchangedb/0005-legitimization_requirements.sql | 45 | ||||
-rw-r--r-- | src/exchangedb/0005-wire_targets.sql | 70 | ||||
-rw-r--r-- | src/exchangedb/Makefile.am | 12 | ||||
-rw-r--r-- | src/exchangedb/exchange-0005.sql.in | 35 |
13 files changed, 792 insertions, 2 deletions
diff --git a/src/exchangedb/.gitignore b/src/exchangedb/.gitignore index 6e67fadb1..e4c628e66 100644 --- a/src/exchangedb/.gitignore +++ b/src/exchangedb/.gitignore @@ -14,3 +14,4 @@ test-exchangedb-populate-link-data-postgres test-exchangedb-populate-ready-deposit-postgres test-exchangedb-populate-select-refunds-by-coin-postgres exchange-0004.sql +exchange-0005.sql diff --git a/src/exchangedb/0002-kyc_attributes.sql b/src/exchangedb/0002-kyc_attributes.sql index 66f3fc315..0091a03d4 100644 --- a/src/exchangedb/0002-kyc_attributes.sql +++ b/src/exchangedb/0002-kyc_attributes.sql @@ -133,7 +133,7 @@ BEGIN 'ALTER TABLE ' || table_name || ' ADD CONSTRAINT ' || table_name || '_foreign_legitimization_processes' ' FOREIGN KEY (legitimization_serial) ' - ' REFERENCES legitimization_processes (legitimization_process_serial_id)' -- ON DELETE CASCADE + ' REFERENCES legitimization_processes (legitimization_process_serial_id)' -- ON DELETE SET NULL? ); END $$; diff --git a/src/exchangedb/0005-aml_history.sql b/src/exchangedb/0005-aml_history.sql new file mode 100644 index 000000000..3016967d3 --- /dev/null +++ b/src/exchangedb/0005-aml_history.sql @@ -0,0 +1,80 @@ +-- +-- 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_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' + ,'create' + ,TRUE + ,FALSE), + ('aml_history5' + ,'exchange-0005' + ,'foreign' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0005-aml_status.sql b/src/exchangedb/0005-aml_status.sql new file mode 100644 index 000000000..f3413c325 --- /dev/null +++ b/src/exchangedb/0005-aml_status.sql @@ -0,0 +1,46 @@ +-- +-- 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_aml_status5( + IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'aml_status'; +BEGIN + PERFORM create_partitioned_table( + 'DROP TABLE %I;' + ,table_name + ,'' + ,partition_suffix + ); +END $$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('aml_status5' + ,'exchange-0005' + ,'create' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0005-kyc_attributes.sql b/src/exchangedb/0005-kyc_attributes.sql new file mode 100644 index 000000000..c5a2b4046 --- /dev/null +++ b/src/exchangedb/0005-kyc_attributes.sql @@ -0,0 +1,85 @@ +-- +-- 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_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' + ',ADD COLUMN legitimization_process_serial_id INT8 DEFAULT NULL' + ',ADD COLUMN trigger_outcome_serial INT8 NOT NULL' + ';' + ,table_name + ,'' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'serial ID of the legitimization process that resulted in these attributes, NULL if the attributes are from a form directly supplied by the account owner via a form' + ,'legitimization_process_serial_id' + ,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 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' + ,'create' + ,TRUE + ,FALSE), + ('kyc_attributes5' + ,'exchange-0005' + ,'foreign' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0005-kyc_events.sql b/src/exchangedb/0005-kyc_events.sql new file mode 100644 index 000000000..2d49cd05f --- /dev/null +++ b/src/exchangedb/0005-kyc_events.sql @@ -0,0 +1,29 @@ +-- +-- 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 TABLE kyc_events ( + kyc_event_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY + ,event_timestamp INT8 NOT NULL + ,event_type TEXT NOT NULL +); + +COMMENT ON TABLE kyc_events + IS 'Records of key events for statistics. Populated via triggers.'; +COMMENT ON COLUMN kyc_events.event_type + IS 'Name of the event, such as account-open or sar-filed'; + +CREATE INDEX kyc_event_index + ON kyc_events(event_type,event_timestamp); diff --git a/src/exchangedb/0005-legitimization_measures.sql b/src/exchangedb/0005-legitimization_measures.sql new file mode 100644 index 000000000..3a94d68f5 --- /dev/null +++ b/src/exchangedb/0005-legitimization_measures.sql @@ -0,0 +1,146 @@ +-- +-- 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_legitimization_measures5( + IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE %I' + '(legitimization_measure_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',target_token BYTEA NOT NULL CHECK (LENGTH(target_token)=32)' + ',start_time INT8 NOT NULL' + ',jmeasures TEXT NOT NULL' + ',display_priority INT4 NOT NULL' + ',is_finished BOOL NOT NULL DEFAULT(FALSE)' + ') %s ;' + ,'legitimization_measures' + ,'PARTITION BY HASH (target_token)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'List of required legitimizations by account' + ,'legitimization_measures' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'unique ID for this legitimization process at the exchange' + ,'legitimization_measure_serial_id' + ,'legitimization_measures' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'foreign key linking the entry to the wire_targets table, NOT a primary key (multiple legitimizations are possible per account)' + ,'target_token' + ,'legitimization_measures' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Time when the measure was triggered (by decision or rule)' + ,'start_time' + ,'legitimization_measures' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'JSON object of type LegitimizationMeasures with KYC/AML measures for the account encoded' + ,'jmeasures' + ,'legitimization_measures' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Display priority of the rule that triggered this measure; if in the meantime another rule also triggers, the measure is only replaced if the new rule has a higher display priority' + ,'display_priority' + ,'legitimization_measures' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Set to TRUE if this set of measures was processed; used to avoid indexing measures that are done' + ,'is_finished' + ,'legitimization_measures' + ,partition_suffix + ); +END +$$; + + + +CREATE FUNCTION constrain_table_legitimization_measures5( + IN partition_suffix TEXT +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'legitimization_measures'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_serial_id_key' + ' UNIQUE (legitimization_measure_serial_id)'); + EXECUTE FORMAT ( + 'CREATE INDEX ' || table_name || '_by_target_token' + ' ON ' || table_name || + ' (target_token)' + ' WHERE NOT is_finished' || + ';' + ); +END +$$; + + +CREATE FUNCTION foreign_table_legitimization_measures5() +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'legitimization_measures'; +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_key_target_token' + ' FOREIGN KEY (target_token)' + ' REFERENCES wire_targets (target_token) ON DELETE CASCADE'); +END +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('legitimization_measures5' + ,'exchange-0005' + ,'create' + ,TRUE + ,FALSE), + ('legitimization_measures5' + ,'exchange-0005' + ,'constrain' + ,TRUE + ,FALSE), + ('legitimization_measures5' + ,'exchange-0005' + ,'foreign' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0005-legitimization_outcomes.sql b/src/exchangedb/0005-legitimization_outcomes.sql new file mode 100644 index 000000000..642e7441f --- /dev/null +++ b/src/exchangedb/0005-legitimization_outcomes.sql @@ -0,0 +1,161 @@ +-- +-- 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_legitimization_outcomes5( + IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'CREATE TABLE %I' + '(outcome_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' + ',decision_time INT8 NOT NULL' + ',expiration_time INT8 NOT NULL' + ',jproperties TEXT' + ',to_investigate BOOL NOT NULL' + ',is_active BOOL NOT NULL DEFAULT(TRUE)' + ',jnew_rules TEXT NOT NULL' + ') %s ;' + ,'legitimization_outcomes' + ,'PARTITION BY HASH (h_payto)' + ,partition_suffix + ); + PERFORM comment_partitioned_table( + 'Outcomes of legitimization processes by account' + ,'legitimization_outcomes' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'unique ID for this legitimization outcome at the exchange' + ,'outcome_serial_id' + ,'legitimization_outcomes' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'hash of the payto://-URI this outcome is about; foreign key linking the entry to the wire_targets table, NOT a primary key (multiple outcomes are possible per account over time)' + ,'h_payto' + ,'legitimization_outcomes' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'when was this outcome decided' + ,'decision_time' + ,'legitimization_outcomes' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'time when the decision expires and the expiration jnew_rules should be applied' + ,'expiration_time' + ,'legitimization_outcomes' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'JSON object of type AccountProperties, such as PEP status, business domain, risk assessment, etc.' + ,'jproperties' + ,'legitimization_outcomes' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'AML staff should investigate the activity of this account' + ,'to_investigate' + ,'legitimization_outcomes' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'TRUE if this is the current authoritative legitimization outcome' + ,'is_active' + ,'legitimization_outcomes' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'JSON object of type LegitimizationRuleSet with rules to apply to the various operation types for this account; all KYC checks should first check if active new rules for a given account exist in this table (and apply specified measures); if not, it should check the default rules to decide if a measure is required' + ,'jnew_rules' + ,'legitimization_outcomes' + ,partition_suffix + ); +END +$$; + + +CREATE FUNCTION constrain_table_legitimization_outcomes5( + IN partition_suffix TEXT +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'legitimization_outcomes'; +BEGIN + table_name = concat_ws('_', table_name, partition_suffix); + + EXECUTE FORMAT ( + 'CREATE INDEX ' || table_name || '_by_target_token' + ' ON ' || table_name || + ' (h_payto)' + ' WHERE is_active' || + ';' + ); +END +$$; + + +CREATE FUNCTION foreign_table_legitimization_outcomes5() +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'legitimization_outcomes'; +BEGIN + + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_serial_id_key' + ' UNIQUE (outcome_serial_id)'); + + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_key_h_payto' + ' FOREIGN KEY (h_payto)' + ' REFERENCES wire_targets (wire_target_h_payto) ON DELETE CASCADE'); +END +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('legitimization_outcomes5' + ,'exchange-0005' + ,'create' + ,TRUE + ,FALSE), + ('legitimization_outcomes5' + ,'exchange-0005' + ,'constrain' + ,TRUE + ,FALSE), + ('legitimization_outcomes5' + ,'exchange-0005' + ,'foreign' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0005-legitimization_processes.sql b/src/exchangedb/0005-legitimization_processes.sql new file mode 100644 index 000000000..49e9d68e8 --- /dev/null +++ b/src/exchangedb/0005-legitimization_processes.sql @@ -0,0 +1,82 @@ +-- +-- 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_legitimization_processes5( + IN shard_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'ALTER TABLE %I' + ' ADD COLUMN legitimization_measure_serial_id BIGINT' + ',ADD COLUMN measure_index INT4' + ';' + ,'legitimization_processes' + ,'' + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'measure that enabled this setup, NULL if client voluntarily initiated the process' + ,'legitimization_measure_serial_id' + ,'legitimization_processes' + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'index of the measure in legitimization_measures that was selected for this KYC setup; NULL if legitimization_measure_serial_id is NULL; enables determination of the context data provided to the external proces' + ,'measure_index' + ,'legitimization_processes' + ,shard_suffix + ); +END +$$; + +-- We need a separate function for this, as we call create_table only once but need to add +-- those constraints to each partition which gets created +CREATE FUNCTION foreign_table_legitimization_processes5() +RETURNS void +LANGUAGE plpgsql +AS $$ +DECLARE + table_name TEXT DEFAULT 'legitimization_processes'; +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_key_legitimization_measure' + ' FOREIGN KEY (legitimization_measure_serial_id)' + ' REFERENCES legitimization_measures (legitimization_measure_serial_id)'); +END +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('legitimization_processes5' + ,'exchange-0005' + ,'create' + ,TRUE + ,FALSE), + ('legitimization_processes5' + ,'exchange-0005' + ,'foreign' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0005-legitimization_requirements.sql b/src/exchangedb/0005-legitimization_requirements.sql new file mode 100644 index 000000000..3d3a238fa --- /dev/null +++ b/src/exchangedb/0005-legitimization_requirements.sql @@ -0,0 +1,45 @@ +-- +-- 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_legitimization_requirements5( + IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'DROP TABLE %I;' + ,'legitimization_requirements' + ,'' + ,partition_suffix + ); +END +$$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('legitimization_requirements5' + ,'exchange-0005' + ,'create' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/0005-wire_targets.sql b/src/exchangedb/0005-wire_targets.sql new file mode 100644 index 000000000..1b0f59dd1 --- /dev/null +++ b/src/exchangedb/0005-wire_targets.sql @@ -0,0 +1,70 @@ +-- +-- 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 OR REPLACE FUNCTION random_bytea( + bytea_length INT +) +RETURNS BYTEA + AS $body$ + SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex') + FROM generate_series(1, $1); + $body$ +LANGUAGE 'sql' +VOLATILE; + +CREATE FUNCTION create_table_wire_targets5( + IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + PERFORM create_partitioned_table( + 'ALTER TABLE %I' + ' ADD COLUMN target_token BYTEA UNIQUE CHECK(LENGTH(target_token)=32) DEFAULT random_bytea(32)' + ',ADD COLUMN target_pub BYTEA CHECK(LENGTH(target_pub)=32) DEFAULT NULL' + ';' + ,'wire_targets' + ,partition_suffix + ); + + PERFORM comment_partitioned_column( + 'high-entropy random value that is used as a bearer token used to authenticate access to the KYC SPA and its state (without requiring a signature)' + ,'target_token' + ,'wire_targets' + ,partition_suffix + ); + PERFORM comment_partitioned_column( + 'Public key of a merchant instance or reserve to authenticate access; NULL if KYC is not allowed for the account (if there was no incoming KYC wire transfer yet); updated, thus NOT available to the auditor' + ,'target_pub' + ,'wire_targets' + ,partition_suffix + ); +END $$; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('wire_targets5' + ,'exchange-0005' + ,'create' + ,TRUE + ,FALSE); diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index fd993f968..0618b9a3c 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -20,9 +20,11 @@ sqlinputs = \ 0002-*.sql \ 0003-*.sql \ 0004-*.sql \ + 0005-*.sql \ exchange-0002.sql.in \ exchange-0003.sql.in \ - exchange-0004.sql.in + exchange-0004.sql.in \ + exchange-0005.sql.in sql_DATA = \ benchmark-0001.sql \ @@ -32,6 +34,7 @@ sql_DATA = \ exchange-0002.sql \ exchange-0003.sql \ exchange-0004.sql \ + exchange-0005.sql \ drop.sql \ procedures.sql @@ -44,6 +47,8 @@ BUILT_SOURCES = \ CLEANFILES = \ exchange-0002.sql \ exchange-0003.sql \ + exchange-0004.sql \ + exchange-0005.sql \ procedures.sql procedures.sql: procedures.sql.in exchange_do_*.sql @@ -66,6 +71,11 @@ exchange-0004.sql: exchange-0004.sql.in 0004-*.sql gcc -E -P -undef - < exchange-0004.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ chmod ugo-w $@ +exchange-0005.sql: exchange-0005.sql.in 0005-*.sql + chmod +w $@ 2> /dev/null || true + gcc -E -P -undef - < exchange-0005.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ + chmod ugo-w $@ + check_SCRIPTS = \ test_idempotency.sh diff --git a/src/exchangedb/exchange-0005.sql.in b/src/exchangedb/exchange-0005.sql.in new file mode 100644 index 000000000..a7f0e2029 --- /dev/null +++ b/src/exchangedb/exchange-0005.sql.in @@ -0,0 +1,35 @@ +-- +-- 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/> +-- + +BEGIN; + +SELECT _v.register_patch('exchange-0005', NULL, NULL); +SET search_path TO exchange; + +#include "0005-wire_targets.sql" +#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" + +-- drops: +#include "0005-aml_status.sql" +#include "0005-legitimization_requirements.sql" + + +COMMIT; |