0012-aggregation_deferrals.sql (6279B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 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 -- The exchange's own record of why it aggregated a payout and then did not 18 -- make it. 19 -- 20 -- aggregation_transient already holds that reason, but it cannot be the 21 -- answer for an auditor: it has no serial ID, it is UPDATEd in place and it 22 -- is DELETEd once the transfer goes out, so replication -- which walks each 23 -- table by its serial ID and only ever appends -- can neither carry it nor 24 -- notice that it changed. An external auditor could therefore see that a 25 -- payout was decided and not executed, but never why, and had to guess from 26 -- the KYC tables whether the exchange was withholding the money lawfully or 27 -- simply sitting on it. 28 -- 29 -- This table is that claim, append-only: one row per occasion on which the 30 -- aggregator decided not to pay out, stating what it was holding and why. 31 -- The exchange is not taken at its word -- the auditor still recomputes the 32 -- amount and checks a KYC claim against the legitimization tables -- but a 33 -- disagreement is now a finding against a statement the exchange signed up 34 -- to, rather than the auditor's own inference against nothing. 35 -- 36 -- Rows are not garbage collected on their own. batch_deposit_serial_id 37 -- names the last deposit of the aggregate to reach its wire deadline, so 38 -- exchange_do_main_gc() removing that deposit takes the deferral with it: 39 -- the claim lives exactly as long as the deposits it is a claim about. 40 41 CREATE FUNCTION create_table_aggregation_deferrals( 42 IN partition_suffix TEXT DEFAULT NULL 43 ) 44 RETURNS VOID 45 LANGUAGE plpgsql 46 AS $$ 47 DECLARE 48 table_name TEXT DEFAULT 'aggregation_deferrals'; 49 BEGIN 50 PERFORM create_partitioned_table( 51 'CREATE TABLE %I' 52 '(aggregation_deferral_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' 53 ',batch_deposit_serial_id INT8 NOT NULL' 54 ',wtid_raw BYTEA NOT NULL CHECK (LENGTH(wtid_raw)=32)' 55 ',wire_target_h_payto BYTEA NOT NULL CHECK (LENGTH(wire_target_h_payto)=32)' 56 ',amount taler_amount NOT NULL' 57 ',deferral_reason INT4 NOT NULL' 58 ',legitimization_requirement_serial_id INT8 NOT NULL DEFAULT(0)' 59 ',deferral_time INT8 NOT NULL' 60 ') %s ;' 61 ,table_name 62 ,'PARTITION BY HASH (batch_deposit_serial_id)' 63 ,partition_suffix 64 ); 65 PERFORM comment_partitioned_table( 66 'reasons the aggregator gave for not executing a wire transfer it had already decided on; append-only, and thus replicated to the auditor (unlike aggregation_transient)' 67 ,table_name 68 ,partition_suffix 69 ); 70 PERFORM comment_partitioned_column( 71 'the deposit of the aggregate that is the last to reach its wire deadline; only present to tie the lifetime of this row to that of the deposits it talks about, as garbage collecting the deposit cascades to here' 72 ,'batch_deposit_serial_id' 73 ,table_name 74 ,partition_suffix 75 ); 76 PERFORM comment_partitioned_column( 77 'identifier of the wire transfer that was not executed' 78 ,'wtid_raw' 79 ,table_name 80 ,partition_suffix 81 ); 82 PERFORM comment_partitioned_column( 83 'unsalted hash of the (full) payto URI of the account that was to receive the funds' 84 ,'wire_target_h_payto' 85 ,table_name 86 ,partition_suffix 87 ); 88 PERFORM comment_partitioned_column( 89 'sum of the aggregated deposits (minus refunds and deposit fees) that was withheld, matching aggregation_transient.amount at the time' 90 ,'amount' 91 ,table_name 92 ,partition_suffix 93 ); 94 PERFORM comment_partitioned_column( 95 'why the transfer was not made: 1 for an aggregate too small to cover the wire fee, 2 for an open KYC/AML requirement against the recipient' 96 ,'deferral_reason' 97 ,table_name 98 ,partition_suffix 99 ); 100 PERFORM comment_partitioned_column( 101 'legitimization measure that has to be satisfied before the transfer can be made, or 0 if the transfer was not deferred for KYC reasons' 102 ,'legitimization_requirement_serial_id' 103 ,table_name 104 ,partition_suffix 105 ); 106 PERFORM comment_partitioned_column( 107 'when the aggregator made this decision' 108 ,'deferral_time' 109 ,table_name 110 ,partition_suffix 111 ); 112 END 113 $$; 114 115 116 CREATE FUNCTION constrain_table_aggregation_deferrals( 117 IN partition_suffix TEXT 118 ) 119 RETURNS VOID 120 LANGUAGE plpgsql 121 AS $$ 122 DECLARE 123 table_name TEXT DEFAULT 'aggregation_deferrals'; 124 BEGIN 125 table_name = concat_ws('_', table_name, partition_suffix); 126 EXECUTE FORMAT ( 127 'ALTER TABLE ' || table_name || 128 ' ADD CONSTRAINT ' || table_name || '_aggregation_deferral_serial_id_key' 129 ' UNIQUE (aggregation_deferral_serial_id)' 130 ); 131 EXECUTE FORMAT ( 132 'CREATE INDEX ' || table_name || '_by_wtid_raw_index ' 133 'ON ' || table_name || ' ' 134 '(wtid_raw);' 135 ); 136 EXECUTE FORMAT ( 137 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' 138 'IS ' || quote_literal('for get_aggregation_deferral_by_wtid') || ';' 139 ); 140 END 141 $$; 142 143 144 CREATE FUNCTION foreign_table_aggregation_deferrals() 145 RETURNS VOID 146 LANGUAGE plpgsql 147 AS $$ 148 DECLARE 149 table_name TEXT DEFAULT 'aggregation_deferrals'; 150 BEGIN 151 EXECUTE FORMAT ( 152 'ALTER TABLE ' || table_name || 153 ' ADD CONSTRAINT ' || table_name || '_foreign_deposit' 154 ' FOREIGN KEY (batch_deposit_serial_id)' 155 ' REFERENCES batch_deposits (batch_deposit_serial_id)' 156 ' ON DELETE CASCADE' 157 ); 158 END 159 $$; 160 161 162 INSERT INTO exchange_tables 163 (name 164 ,version 165 ,action 166 ,partitioned 167 ,by_range) 168 VALUES 169 ('aggregation_deferrals' 170 ,'exchange-0012' 171 ,'create' 172 ,TRUE 173 ,FALSE), 174 ('aggregation_deferrals' 175 ,'exchange-0012' 176 ,'constrain' 177 ,TRUE 178 ,FALSE), 179 ('aggregation_deferrals' 180 ,'exchange-0012' 181 ,'foreign' 182 ,TRUE 183 ,FALSE);