0002-legitimization_outcomes.sql (4847B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024 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 CREATE FUNCTION create_table_legitimization_outcomes( 18 IN partition_suffix TEXT DEFAULT NULL 19 ) 20 RETURNS VOID 21 LANGUAGE plpgsql 22 AS $$ 23 BEGIN 24 PERFORM create_partitioned_table( 25 'CREATE TABLE %I' 26 '(outcome_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 27 ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' 28 ',decision_time INT8 NOT NULL' 29 ',expiration_time INT8 NOT NULL' 30 ',jproperties TEXT' 31 ',new_measure_name TEXT' 32 ',to_investigate BOOL NOT NULL' 33 ',is_active BOOL NOT NULL DEFAULT(TRUE)' 34 ',jnew_rules TEXT' 35 ') %s ;' 36 ,'legitimization_outcomes' 37 ,'PARTITION BY HASH (h_payto)' 38 ,partition_suffix 39 ); 40 PERFORM comment_partitioned_table( 41 'Outcomes of legitimization processes by account' 42 ,'legitimization_outcomes' 43 ,partition_suffix 44 ); 45 PERFORM comment_partitioned_column( 46 'unique ID for this legitimization outcome at the exchange' 47 ,'outcome_serial_id' 48 ,'legitimization_outcomes' 49 ,partition_suffix 50 ); 51 PERFORM comment_partitioned_column( 52 'hash of the normalized payto://-URI this outcome is about; foreign key linking the entry to the kyc_targets table, NOT a primary key (multiple outcomes are possible per account over time)' 53 ,'h_payto' 54 ,'legitimization_outcomes' 55 ,partition_suffix 56 ); 57 PERFORM comment_partitioned_column( 58 'when was this outcome decided, rounded timestamp' 59 ,'decision_time' 60 ,'legitimization_outcomes' 61 ,partition_suffix 62 ); 63 PERFORM comment_partitioned_column( 64 'space-separated list of names of measures to trigger immediately, NULL for none, prefixed with a "+" to indicate AND combination for the measures' 65 ,'new_measure_name' 66 ,'legitimization_outcomes' 67 ,partition_suffix 68 ); 69 PERFORM comment_partitioned_column( 70 'time when the decision expires and the expiration jnew_rules should be applied' 71 ,'expiration_time' 72 ,'legitimization_outcomes' 73 ,partition_suffix 74 ); 75 PERFORM comment_partitioned_column( 76 'JSON object of type AccountProperties, such as PEP status, business domain, risk assessment, etc.' 77 ,'jproperties' 78 ,'legitimization_outcomes' 79 ,partition_suffix 80 ); 81 PERFORM comment_partitioned_column( 82 'AML staff should investigate the activity of this account' 83 ,'to_investigate' 84 ,'legitimization_outcomes' 85 ,partition_suffix 86 ); 87 PERFORM comment_partitioned_column( 88 'TRUE if this is the current authoritative legitimization outcome' 89 ,'is_active' 90 ,'legitimization_outcomes' 91 ,partition_suffix 92 ); 93 PERFORM comment_partitioned_column( 94 '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; NULL if the default rules apply' 95 ,'jnew_rules' 96 ,'legitimization_outcomes' 97 ,partition_suffix 98 ); 99 END 100 $$; 101 102 103 CREATE FUNCTION constrain_table_legitimization_outcomes( 104 IN partition_suffix TEXT 105 ) 106 RETURNS VOID 107 LANGUAGE plpgsql 108 AS $$ 109 DECLARE 110 table_name TEXT DEFAULT 'legitimization_outcomes'; 111 BEGIN 112 table_name = concat_ws('_', table_name, partition_suffix); 113 114 EXECUTE FORMAT ( 115 'CREATE INDEX ' || table_name || '_by_target_token' 116 ' ON ' || table_name || 117 ' (h_payto)' 118 ' WHERE is_active' || 119 ';' 120 ); 121 END 122 $$; 123 124 125 CREATE FUNCTION foreign_table_legitimization_outcomes() 126 RETURNS void 127 LANGUAGE plpgsql 128 AS $$ 129 DECLARE 130 table_name TEXT DEFAULT 'legitimization_outcomes'; 131 BEGIN 132 133 EXECUTE FORMAT ( 134 'ALTER TABLE ' || table_name || 135 ' ADD CONSTRAINT ' || table_name || '_serial_id_key' 136 ' UNIQUE (outcome_serial_id)'); 137 END 138 $$; 139 140 141 INSERT INTO exchange_tables 142 (name 143 ,version 144 ,action 145 ,partitioned 146 ,by_range) 147 VALUES 148 ('legitimization_outcomes' 149 ,'exchange-0002' 150 ,'create' 151 ,TRUE 152 ,FALSE), 153 ('legitimization_outcomes' 154 ,'exchange-0002' 155 ,'constrain' 156 ,TRUE 157 ,FALSE), 158 ('legitimization_outcomes' 159 ,'exchange-0002' 160 ,'foreign' 161 ,TRUE 162 ,FALSE);