0002-legitimization_processes.sql (6770B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2014--2022 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_processes( 18 IN shard_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 '(legitimization_process_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 27 ',h_payto BYTEA NOT NULL CHECK (LENGTH(h_payto)=32)' 28 ',start_time INT8 NOT NULL' 29 ',expiration_time INT8 NOT NULL DEFAULT (0)' 30 ',provider_name TEXT NOT NULL' 31 ',provider_user_id TEXT DEFAULT NULL' 32 ',provider_legitimization_id TEXT DEFAULT NULL' 33 ',redirect_url TEXT DEFAULT NULL' 34 ',finished BOOLEAN DEFAULT (FALSE)' 35 ',legitimization_measure_serial_id BIGINT' 36 ',measure_index INT4 DEFAULT(0)' 37 ',error_code INT4 DEFAULT (0)' 38 ',error_message TEXT DEFAULT NULL' 39 ') %s ;' 40 ,'legitimization_processes' 41 ,'PARTITION BY HASH (h_payto)' 42 ,shard_suffix 43 ); 44 PERFORM comment_partitioned_table( 45 'List of legitimization processes (ongoing and completed) by account and provider' 46 ,'legitimization_processes' 47 ,shard_suffix 48 ); 49 PERFORM comment_partitioned_column( 50 'unique ID for this legitimization process at the exchange' 51 ,'legitimization_process_serial_id' 52 ,'legitimization_processes' 53 ,shard_suffix 54 ); 55 PERFORM comment_partitioned_column( 56 'hash over the normalized payto URI; foreign key linking the entry to the kyc_targets table, NOT a primary key (multiple legitimizations are possible per wire target)' 57 ,'h_payto' 58 ,'legitimization_processes' 59 ,shard_suffix 60 ); 61 PERFORM comment_partitioned_column( 62 'time when the KYC check was initiated, useful for garbage collection (absolute time, not rounded)' 63 ,'start_time' 64 ,'legitimization_processes' 65 ,shard_suffix 66 ); 67 PERFORM comment_partitioned_column( 68 'URL where the user should go to begin the KYC process' 69 ,'redirect_url' 70 ,'legitimization_processes' 71 ,shard_suffix 72 ); 73 PERFORM comment_partitioned_column( 74 'in the future if the respective KYC check was passed successfully; an absolute time (not rounded)' 75 ,'expiration_time' 76 ,'legitimization_processes' 77 ,shard_suffix 78 ); 79 PERFORM comment_partitioned_column( 80 'Configuration file section with details about this provider' 81 ,'provider_name' 82 ,'legitimization_processes' 83 ,shard_suffix 84 ); 85 PERFORM comment_partitioned_column( 86 'Identifier for the user at the provider that was used for the legitimization. NULL if provider is unaware.' 87 ,'provider_user_id' 88 ,'legitimization_processes' 89 ,shard_suffix 90 ); 91 PERFORM comment_partitioned_column( 92 'Identifier for the specific legitimization process at the provider. NULL if legitimization was not started.' 93 ,'provider_legitimization_id' 94 ,'legitimization_processes' 95 ,shard_suffix 96 ); 97 PERFORM comment_partitioned_column( 98 'Set to TRUE when the specific legitimization process is finished.' 99 ,'finished' 100 ,'legitimization_processes' 101 ,shard_suffix 102 ); 103 PERFORM comment_partitioned_column( 104 'measure that enabled this setup, NULL if client voluntarily initiated the process' 105 ,'legitimization_measure_serial_id' 106 ,'legitimization_processes' 107 ,shard_suffix 108 ); 109 PERFORM comment_partitioned_column( 110 '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 process' 111 ,'measure_index' 112 ,'legitimization_processes' 113 ,shard_suffix 114 ); 115 PERFORM comment_partitioned_column( 116 'TALER_ErrorCode set if the process failed, otherwise NULL' 117 ,'error_code' 118 ,'legitimization_processes' 119 ,shard_suffix 120 ); 121 PERFORM comment_partitioned_column( 122 'human-readable error details set if the process failed, otherwise NULL' 123 ,'error_message' 124 ,'legitimization_processes' 125 ,shard_suffix 126 ); 127 END 128 $$; 129 130 -- We need a separate function for this, as we call create_table only once but need to add 131 -- those constraints to each partition which gets created 132 CREATE FUNCTION constrain_table_legitimization_processes( 133 IN partition_suffix TEXT 134 ) 135 RETURNS void 136 LANGUAGE plpgsql 137 AS $$ 138 DECLARE 139 partition_name TEXT; 140 BEGIN 141 partition_name = concat_ws('_', 'legitimization_processes', partition_suffix); 142 143 EXECUTE FORMAT ( 144 'ALTER TABLE ' || partition_name 145 || ' ' 146 'ADD CONSTRAINT ' || partition_name || '_serial_key ' 147 'UNIQUE (legitimization_process_serial_id)'); 148 EXECUTE FORMAT ( 149 'CREATE INDEX IF NOT EXISTS ' || partition_name || '_by_provider_and_legi_index ' 150 'ON '|| partition_name || ' ' 151 '(provider_name,provider_legitimization_id)' 152 ); 153 EXECUTE FORMAT ( 154 'COMMENT ON INDEX ' || partition_name || '_by_provider_and_legi_index ' 155 'IS ' || quote_literal('used (rarely) in kyc_provider_account_lookup') || ';' 156 ); 157 END 158 $$; 159 160 161 -- We need a separate function for this, as we call create_table only once but need to add 162 -- those constraints to each partition which gets created 163 CREATE FUNCTION foreign_table_legitimization_processes() 164 RETURNS void 165 LANGUAGE plpgsql 166 AS $$ 167 DECLARE 168 table_name TEXT DEFAULT 'legitimization_processes'; 169 BEGIN 170 EXECUTE FORMAT ( 171 'ALTER TABLE ' || table_name || 172 ' ADD CONSTRAINT ' || table_name || '_foreign_key_legitimization_measure' 173 ' FOREIGN KEY (legitimization_measure_serial_id)' 174 ' REFERENCES legitimization_measures (legitimization_measure_serial_id)'); 175 176 EXECUTE FORMAT ( 177 'ALTER TABLE ' || table_name || 178 ' ADD CONSTRAINT ' || table_name || '_unique_measure_and_index' 179 ' UNIQUE (legitimization_measure_serial_id,measure_index)'); 180 END 181 $$; 182 183 184 185 INSERT INTO exchange_tables 186 (name 187 ,version 188 ,action 189 ,partitioned 190 ,by_range) 191 VALUES 192 ('legitimization_processes' 193 ,'exchange-0002' 194 ,'create' 195 ,TRUE 196 ,FALSE), 197 ('legitimization_processes' 198 ,'exchange-0002' 199 ,'constrain' 200 ,TRUE 201 ,FALSE), 202 ('legitimization_processes' 203 ,'exchange-0002' 204 ,'foreign' 205 ,TRUE 206 ,FALSE);