insert_transfer_details.sql (10581B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2024, 2025 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 18 DROP FUNCTION IF EXISTS merchant_do_insert_transfer_details; 19 CREATE FUNCTION merchant_do_insert_transfer_details ( 20 IN in_merchant_serial INT8, 21 IN in_exchange_url TEXT, 22 IN in_payto_uri TEXT, 23 IN in_wtid BYTEA, 24 IN in_execution_time INT8, 25 IN in_exchange_pub BYTEA, 26 IN in_exchange_sig BYTEA, 27 IN in_total_amount merchant.taler_amount_currency, 28 IN in_wire_fee merchant.taler_amount_currency, 29 IN ina_coin_values merchant.taler_amount_currency[], 30 IN ina_deposit_fees merchant.taler_amount_currency[], 31 IN ina_coin_pubs BYTEA[], 32 IN ina_contract_terms BYTEA[], 33 OUT out_no_account BOOL, 34 OUT out_no_exchange BOOL, 35 OUT out_duplicate BOOL, 36 OUT out_conflict BOOL, 37 -- IDs of ALL orders that became fully wired by this transfer; 38 -- one aggregated transfer can settle many orders. 39 OUT out_order_ids TEXT[]) 40 LANGUAGE plpgsql 41 AS $$ 42 DECLARE 43 my_signkey_serial INT8; 44 my_expected_credit_serial INT8; 45 my_account_serial INT8; 46 my_affected_orders RECORD; 47 my_decose INT8; 48 my_order_id TEXT; 49 i INT8; 50 curs CURSOR (arg_coin_pub BYTEA, arg_contract_term BYTEA) FOR 51 SELECT mcon.deposit_confirmation_serial, 52 mcon.order_serial 53 FROM merchant_deposits dep 54 JOIN merchant_deposit_confirmations mcon 55 USING (deposit_confirmation_serial) 56 JOIN merchant_contract_terms cterm 57 USING (order_serial) 58 WHERE dep.coin_pub=arg_coin_pub 59 AND cterm.h_contract_terms=arg_contract_term 60 AND mcon.exchange_url=in_exchange_url 61 AND mcon.account_serial=my_account_serial; 62 ini_coin_pub BYTEA; 63 ini_contract_term BYTEA; 64 ini_coin_value merchant.taler_amount_currency; 65 ini_deposit_fee merchant.taler_amount_currency; 66 BEGIN 67 68 out_order_ids=ARRAY[]::TEXT[]; 69 70 -- Determine account that was credited. 71 SELECT expected_credit_serial, account_serial 72 INTO my_expected_credit_serial, my_account_serial 73 FROM merchant_expected_transfers 74 WHERE exchange_url=in_exchange_url 75 AND wtid=in_wtid 76 AND account_serial= 77 (SELECT account_serial 78 FROM merchant_accounts 79 WHERE payto_uri=in_payto_uri); 80 81 IF NOT FOUND 82 THEN 83 out_no_account=TRUE; 84 out_no_exchange=FALSE; 85 out_duplicate=FALSE; 86 out_conflict=FALSE; 87 RETURN; 88 END IF; 89 out_no_account=FALSE; 90 91 -- Find exchange sign key 92 SELECT signkey_serial 93 INTO my_signkey_serial 94 FROM merchant.merchant_exchange_signing_keys 95 WHERE exchange_pub=in_exchange_pub 96 ORDER BY start_date DESC 97 LIMIT 1; 98 99 IF NOT FOUND 100 THEN 101 out_no_exchange=TRUE; 102 out_conflict=FALSE; 103 out_duplicate=FALSE; 104 RETURN; 105 END IF; 106 out_no_exchange=FALSE; 107 108 -- Add signature first, check for idempotent request 109 INSERT INTO merchant_transfer_signatures 110 (expected_credit_serial 111 ,signkey_serial 112 ,credit_amount 113 ,wire_fee 114 ,execution_time 115 ,exchange_sig) 116 VALUES 117 (my_expected_credit_serial 118 ,my_signkey_serial 119 ,in_total_amount 120 ,in_wire_fee 121 ,in_execution_time 122 ,in_exchange_sig) 123 ON CONFLICT DO NOTHING; 124 125 IF NOT FOUND 126 THEN 127 PERFORM 1 128 FROM merchant_transfer_signatures 129 WHERE expected_credit_serial=my_expected_credit_serial 130 AND signkey_serial=my_signkey_serial 131 AND credit_amount=in_total_amount 132 AND wire_fee=in_wire_fee 133 AND execution_time=in_execution_time 134 AND exchange_sig=in_exchange_sig; 135 IF FOUND 136 THEN 137 -- duplicate case 138 out_duplicate=TRUE; 139 out_conflict=FALSE; 140 RETURN; 141 END IF; 142 -- conflict case 143 out_duplicate=FALSE; 144 out_conflict=TRUE; 145 RETURN; 146 END IF; 147 148 out_duplicate=FALSE; 149 out_conflict=FALSE; 150 151 152 -- Note: the COALESCE is required, the exchange is allowed to 153 -- return an empty list of deposits for a wire transfer; without 154 -- it plpgsql raises 'upper bound of FOR loop cannot be null'. 155 FOR i IN 1..COALESCE(array_length(ina_coin_pubs,1),0) 156 LOOP 157 ini_coin_value=ina_coin_values[i]; 158 ini_deposit_fee=ina_deposit_fees[i]; 159 ini_coin_pub=ina_coin_pubs[i]; 160 ini_contract_term=ina_contract_terms[i]; 161 162 INSERT INTO merchant_expected_transfer_to_coin 163 (deposit_serial 164 ,expected_credit_serial 165 ,offset_in_exchange_list 166 ,exchange_deposit_value 167 ,exchange_deposit_fee) 168 SELECT 169 dep.deposit_serial 170 ,my_expected_credit_serial 171 ,i 172 ,ini_coin_value 173 ,ini_deposit_fee 174 FROM merchant_deposits dep 175 JOIN merchant_deposit_confirmations dcon 176 USING (deposit_confirmation_serial) 177 JOIN merchant_contract_terms cterm 178 USING (order_serial) 179 WHERE dep.coin_pub=ini_coin_pub 180 AND cterm.h_contract_terms=ini_contract_term 181 AND dcon.exchange_url=in_exchange_url 182 AND dcon.account_serial=my_account_serial 183 -- The exchange may list the same coin more than once in one 184 -- response, and a coin belongs to at most one wire transfer 185 -- (merchant_expected_transfer_to_coin is UNIQUE on 186 -- deposit_serial): keep the first association we saw instead of 187 -- failing the entire transaction. 188 ON CONFLICT (deposit_serial) DO NOTHING; 189 190 RAISE NOTICE 'iterating over affected orders'; 191 OPEN curs (arg_coin_pub:=ini_coin_pub, 192 arg_contract_term:=ini_contract_term); 193 LOOP 194 FETCH NEXT FROM curs INTO my_affected_orders; 195 EXIT WHEN NOT FOUND; 196 197 RAISE NOTICE 'checking affected order for completion'; 198 199 my_decose=my_affected_orders.deposit_confirmation_serial; 200 201 -- A deposit that failed permanently (say because the exchange 202 -- wired the money to an account we do not know, EC 2558) has 203 -- settlement_retry_needed=FALSE and a settlement_wtid, but is 204 -- NOT settled: it must not make the order count as wired. 205 -- The signed transfer list is also settlement evidence. Reconciliation 206 -- can learn about every deposit in an aggregate before depositcheck has 207 -- queried them individually. Keep the distinct per-deposit signature 208 -- fields untouched, and only accept transfers to this deposit's account 209 -- from its exchange. A permanent deposit error still prevents settlement. 210 PERFORM FROM merchant_deposits md 211 JOIN merchant_deposit_confirmations dcon 212 USING (deposit_confirmation_serial) 213 WHERE md.deposit_confirmation_serial=my_decose 214 AND ((NOT COALESCE(md.settlement_retry_needed, TRUE) 215 AND COALESCE(md.settlement_last_ec, 0) <> 0) 216 OR ((COALESCE(md.settlement_retry_needed, TRUE) 217 OR md.settlement_wtid IS NULL 218 OR COALESCE(md.settlement_last_ec, 0) <> 0) 219 AND NOT EXISTS 220 (SELECT 1 221 FROM merchant_expected_transfer_to_coin tc 222 JOIN merchant_expected_transfers et 223 USING (expected_credit_serial) 224 JOIN merchant_transfer_signatures ts 225 USING (expected_credit_serial) 226 WHERE tc.deposit_serial=md.deposit_serial 227 AND et.exchange_url=dcon.exchange_url 228 AND et.account_serial=dcon.account_serial))); 229 IF NOT FOUND 230 THEN 231 -- must be all done, clear flag 232 UPDATE merchant_deposit_confirmations 233 SET wire_pending=FALSE 234 WHERE deposit_confirmation_serial=my_decose 235 AND wire_pending; 236 237 IF FOUND 238 THEN 239 -- Also update contract terms, if all (other) associated 240 -- deposit_confirmations are also done. 241 242 RAISE NOTICE 'checking affected contract for completion'; 243 PERFORM FROM merchant_deposit_confirmations mdc 244 WHERE mdc.wire_pending 245 AND mdc.order_serial=my_affected_orders.order_serial; 246 IF NOT FOUND 247 THEN 248 249 UPDATE merchant_contract_terms 250 SET wired=TRUE 251 WHERE (order_serial=my_affected_orders.order_serial); 252 253 -- Select merchant_serial and order_id for webhook 254 SELECT order_id 255 INTO my_order_id 256 FROM merchant_contract_terms 257 WHERE order_serial=my_affected_orders.order_serial; 258 -- Remember ALL orders that completed, not just the last 259 -- one: an aggregated transfer settles many orders and 260 -- every one of them needs an ORDER_STATUS_CHANGED event. 261 IF NOT (my_order_id = ANY(out_order_ids)) 262 THEN 263 out_order_ids = array_append(out_order_ids, my_order_id); 264 END IF; 265 -- Insert pending webhook if it exists 266 INSERT INTO merchant.merchant_pending_webhooks 267 (merchant_serial 268 ,webhook_serial 269 ,url 270 ,http_method 271 ,header 272 ,body) 273 SELECT in_merchant_serial 274 ,mw.webhook_serial 275 ,mw.url 276 ,mw.http_method 277 ,merchant.replace_placeholder( 278 merchant.replace_placeholder(mw.header_template, 279 'order_id', 280 my_order_id), 281 'wtid', 282 encode(in_wtid, 'hex') 283 )::TEXT 284 ,merchant.replace_placeholder( 285 merchant.replace_placeholder(mw.body_template, 286 'order_id', 287 my_order_id), 288 'wtid', 289 encode(in_wtid, 'hex') 290 )::TEXT 291 FROM merchant_webhook mw 292 WHERE mw.event_type = 'order_settled'; 293 IF FOUND 294 THEN 295 NOTIFY XXJWF6C1DCS1255RJH7GQ1EK16J8DMRSQ6K9EDKNKCP7HRVWAJPKG; 296 END IF; -- found pending order_settled webhooks to deliver 297 END IF; -- no more merchant_deposits waiting for wire_pending 298 END IF; -- did clear wire_pending flag for deposit confirmation 299 END IF; -- no more merchant_deposits wait for settlement 300 301 END LOOP; -- END curs LOOP 302 CLOSE curs; 303 END LOOP; -- END FOR loop 304 305 END $$;