libeufin-nexus-procedures.sql (26271B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2023, 2024, 2025, 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 BEGIN; 17 SET search_path TO public; 18 CREATE EXTENSION IF NOT EXISTS pgcrypto; 19 20 SET search_path TO libeufin_nexus; 21 22 -- Remove all existing functions 23 DO 24 $do$ 25 DECLARE 26 _sql text; 27 BEGIN 28 SELECT INTO _sql 29 string_agg(format('DROP %s %s CASCADE;' 30 , CASE prokind 31 WHEN 'f' THEN 'FUNCTION' 32 WHEN 'p' THEN 'PROCEDURE' 33 END 34 , oid::regprocedure) 35 , E'\n') 36 FROM pg_proc 37 WHERE pronamespace = 'libeufin_nexus'::regnamespace; 38 39 IF _sql IS NOT NULL THEN 40 EXECUTE _sql; 41 END IF; 42 END 43 $do$; 44 45 CREATE FUNCTION ebics_id_gen() 46 RETURNS TEXT 47 LANGUAGE sql AS $$ 48 -- use gen_random_uuid to get some randomness 49 -- remove all - characters as they are not random 50 -- capitalise the UUID as some bank may still be case sensitive 51 -- end with 34 random chars which is valid for EBICS (max 35 chars) 52 SELECT upper(replace(gen_random_uuid()::text, '-', '')); 53 $$; 54 55 56 CREATE FUNCTION amount_normalize( 57 IN amount taler_amount 58 ,OUT normalized taler_amount 59 ) 60 LANGUAGE plpgsql IMMUTABLE AS $$ 61 BEGIN 62 normalized.val = amount.val + amount.frac / 100000000; 63 IF (normalized.val > 1::INT8<<52) THEN 64 RAISE EXCEPTION 'amount value overflowed'; 65 END IF; 66 normalized.frac = amount.frac % 100000000; 67 68 END $$; 69 COMMENT ON FUNCTION amount_normalize 70 IS 'Returns the normalized amount by adding to the .val the value of (.frac / 100000000) and removing the modulus 100000000 from .frac.' 71 'It raises an exception when the resulting .val is larger than 2^52'; 72 73 CREATE FUNCTION amount_add( 74 IN l taler_amount 75 ,IN r taler_amount 76 ,OUT sum taler_amount 77 ) 78 LANGUAGE plpgsql IMMUTABLE AS $$ 79 BEGIN 80 sum = (l.val + r.val, l.frac + r.frac); 81 SELECT normalized.val, normalized.frac INTO sum.val, sum.frac FROM amount_normalize(sum) as normalized; 82 END $$; 83 COMMENT ON FUNCTION amount_add 84 IS 'Returns the normalized sum of two amounts. It raises an exception when the resulting .val is larger than 2^52'; 85 86 CREATE FUNCTION register_outgoing( 87 IN in_amount taler_amount 88 ,IN in_debit_fee taler_amount 89 ,IN in_subject TEXT 90 ,IN in_execution_time INT8 91 ,IN in_credit_payto TEXT 92 ,IN in_end_to_end_id TEXT 93 ,IN in_msg_id TEXT 94 ,IN in_acct_svcr_ref TEXT 95 ,IN in_wtid BYTEA 96 ,IN in_exchange_url TEXT 97 ,IN in_metadata TEXT 98 ,OUT out_tx_id INT8 99 ,OUT out_found BOOLEAN 100 ,OUT out_initiated BOOLEAN 101 ) 102 LANGUAGE plpgsql AS $$ 103 DECLARE 104 init_id INT8; 105 local_amount taler_amount; 106 local_subject TEXT; 107 local_credit_payto TEXT; 108 local_wtid BYTEA; 109 local_exchange_base_url TEXT; 110 local_metadata TEXT; 111 local_end_to_end_id TEXT; 112 BEGIN 113 -- Check if already registered 114 SELECT outgoing_transaction_id, subject, credit_payto, (amount).val, (amount).frac, 115 wtid, exchange_base_url, metadata 116 INTO out_tx_id, local_subject, local_credit_payto, local_amount.val, local_amount.frac, 117 local_wtid, local_exchange_base_url, local_metadata 118 FROM outgoing_transactions LEFT JOIN talerable_outgoing_transactions USING (outgoing_transaction_id) 119 WHERE end_to_end_id = in_end_to_end_id OR acct_svcr_ref = in_acct_svcr_ref; 120 out_found=FOUND; 121 IF out_found THEN 122 -- Check metadata 123 -- TODO take subject if missing and more detailed credit payto 124 IF in_subject IS NOT NULL AND local_subject != in_subject THEN 125 RAISE NOTICE 'outgoing tx %: stored subject is ''%'' got ''%''', in_end_to_end_id, local_subject, in_subject; 126 END IF; 127 IF in_credit_payto IS NOT NULL AND local_credit_payto != in_credit_payto THEN 128 RAISE NOTICE 'outgoing tx %: stored subject credit payto is % got %', in_end_to_end_id, local_credit_payto, in_credit_payto; 129 END IF; 130 IF local_amount IS DISTINCT FROM in_amount THEN 131 RAISE NOTICE 'outgoing tx %: stored amount is % got %', in_end_to_end_id, local_amount, in_amount; 132 END IF; 133 IF local_wtid IS DISTINCT FROM in_wtid THEN 134 RAISE NOTICE 'outgoing tx %: stored wtid is % got %', in_end_to_end_id, local_wtid, in_wtid; 135 END IF; 136 IF local_exchange_base_url IS DISTINCT FROM in_exchange_url THEN 137 RAISE NOTICE 'outgoing tx %: stored exchange base url is % got %', in_end_to_end_id, local_exchange_base_url, in_exchange_url; 138 END IF; 139 IF local_metadata IS DISTINCT FROM in_metadata THEN 140 RAISE NOTICE 'outgoing tx %: stored metadata is % got %', in_end_to_end_id, local_metadata, in_metadata; 141 END IF; 142 END IF; 143 144 -- Check if initiated 145 SELECT initiated_outgoing_transaction_id, subject, credit_payto, (amount).val, (amount).frac, 146 wtid, exchange_base_url, metadata 147 INTO init_id, local_subject, local_credit_payto, local_amount.val, local_amount.frac, 148 local_wtid, local_exchange_base_url, local_metadata 149 FROM initiated_outgoing_transactions LEFT JOIN transfer_operations USING (initiated_outgoing_transaction_id) 150 WHERE end_to_end_id = in_end_to_end_id; 151 out_initiated=FOUND; 152 IF out_initiated AND NOT out_found THEN 153 -- Check metadata 154 -- TODO take subject if missing and more detailed credit payto 155 IF in_subject IS NOT NULL AND local_subject != in_subject THEN 156 RAISE NOTICE 'outgoing tx %: initiated subject is ''%'' got ''%''', in_end_to_end_id, local_subject, in_subject; 157 END IF; 158 IF local_credit_payto IS DISTINCT FROM in_credit_payto THEN 159 RAISE NOTICE 'outgoing tx %: initiated subject credit payto is % got %', in_end_to_end_id, local_credit_payto, in_credit_payto; 160 END IF; 161 IF local_amount IS DISTINCT FROM in_amount THEN 162 RAISE NOTICE 'outgoing tx %: initiated amount is % got %', in_end_to_end_id, local_amount, in_amount; 163 END IF; 164 IF in_wtid IS NOT NULL AND local_wtid != in_wtid THEN 165 RAISE NOTICE 'outgoing tx %: initiated wtid is % got %', in_end_to_end_id, local_wtid, in_wtid; 166 END IF; 167 IF in_exchange_url IS NOT NULL AND local_exchange_base_url != in_exchange_url THEN 168 RAISE NOTICE 'outgoing tx %: initiated exchange base url is % got %', in_end_to_end_id, local_exchange_base_url, in_exchange_url; 169 END IF; 170 IF in_metadata IS NOT NULL AND local_metadata != in_metadata THEN 171 RAISE NOTICE 'outgoing tx %: initiated metadata is % got %', in_end_to_end_id, local_metadata, in_metadata; 172 END IF; 173 END IF; 174 175 IF NOT out_found THEN 176 -- Store the transaction in the database 177 INSERT INTO outgoing_transactions ( 178 amount 179 ,debit_fee 180 ,subject 181 ,execution_time 182 ,credit_payto 183 ,end_to_end_id 184 ,acct_svcr_ref 185 ) VALUES ( 186 in_amount 187 ,in_debit_fee 188 ,in_subject 189 ,in_execution_time 190 ,in_credit_payto 191 ,in_end_to_end_id 192 ,in_acct_svcr_ref 193 ) 194 RETURNING outgoing_transaction_id 195 INTO out_tx_id; 196 197 -- Register as talerable if contains wtid 198 IF in_wtid IS NOT NULL THEN 199 SELECT end_to_end_id INTO local_end_to_end_id 200 FROM talerable_outgoing_transactions 201 JOIN outgoing_transactions USING (outgoing_transaction_id) 202 WHERE wtid=in_wtid; 203 IF FOUND THEN 204 IF local_end_to_end_id != in_end_to_end_id THEN 205 RAISE NOTICE 'wtid reuse: tx % and tx % have the same wtid %', in_end_to_end_id, local_end_to_end_id, in_wtid; 206 END IF; 207 ELSE 208 INSERT INTO talerable_outgoing_transactions( 209 outgoing_transaction_id, 210 wtid, 211 exchange_base_url, 212 metadata 213 ) VALUES ( 214 out_tx_id, 215 in_wtid, 216 in_exchange_url, 217 in_metadata 218 ); 219 PERFORM pg_notify('nexus_outgoing_tx', out_tx_id::text); 220 END IF; 221 END IF; 222 223 IF out_initiated THEN 224 -- Reconciles the related initiated transaction 225 UPDATE initiated_outgoing_transactions 226 SET 227 outgoing_transaction_id = out_tx_id 228 ,status = 'success' 229 ,status_msg = null 230 WHERE initiated_outgoing_transaction_id = init_id 231 AND status != 'late_failure'; 232 233 -- Reconciles the related initiated batch 234 UPDATE initiated_outgoing_batches 235 SET status = 'success', status_msg = null 236 WHERE message_id = in_msg_id AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 237 END IF; 238 END IF; 239 END $$; 240 COMMENT ON FUNCTION register_outgoing 241 IS 'Register an outgoing transaction and optionally reconciles the related initiated transaction with it'; 242 243 CREATE FUNCTION register_incoming( 244 IN in_amount taler_amount 245 ,IN in_credit_fee taler_amount 246 ,IN in_subject TEXT 247 ,IN in_execution_time INT8 248 ,IN in_debit_payto TEXT 249 ,IN in_uetr UUID 250 ,IN in_tx_id TEXT 251 ,IN in_acct_svcr_ref TEXT 252 ,IN in_type taler_incoming_type 253 ,IN in_metadata BYTEA 254 ,IN in_qr_reference_number TEXT 255 -- Error status 256 ,OUT out_reserve_pub_reuse BOOLEAN 257 ,OUT out_mapping_reuse BOOLEAN 258 ,OUT out_unknown_mapping BOOLEAN 259 -- Success return 260 ,OUT out_found BOOLEAN 261 ,OUT out_completed BOOLEAN 262 ,OUT out_talerable BOOLEAN 263 ,OUT out_pending BOOLEAN 264 ,OUT out_tx_id INT8 265 ,OUT out_bounce_id TEXT 266 ) 267 LANGUAGE plpgsql AS $$ 268 DECLARE 269 local_ref TEXT; 270 local_amount taler_amount; 271 local_subject TEXT; 272 local_debit_payto TEXT; 273 local_authorization_pub BYTEA; 274 local_authorization_sig BYTEA; 275 BEGIN 276 IF in_credit_fee = (0, 0)::taler_amount THEN 277 in_credit_fee = NULL; 278 END IF; 279 out_pending=FALSE; 280 281 -- Check if already registered 282 SELECT incoming_transaction_id, tx.subject, debit_payto, (tx.amount).val, (tx.amount).frac, metadata IS NOT NULL, end_to_end_id 283 INTO out_tx_id, local_subject, local_debit_payto, local_amount.val, local_amount.frac, out_talerable, out_bounce_id 284 FROM incoming_transactions AS tx 285 LEFT JOIN talerable_incoming_transactions USING (incoming_transaction_id) 286 LEFT JOIN bounced_transactions USING (incoming_transaction_id) 287 LEFT JOIN initiated_outgoing_transactions USING (initiated_outgoing_transaction_id) 288 WHERE uetr = in_uetr OR tx_id = in_tx_id OR acct_svcr_ref = in_acct_svcr_ref; 289 out_found=FOUND; 290 291 IF NOT out_found OR NOT out_talerable THEN 292 -- Resolve mapping logic 293 IF in_type = 'map' OR in_qr_reference_number IS NOT NULL THEN 294 SELECT type, account_pub, authorization_pub, authorization_sig, 295 incoming_transaction_id IS NOT NULL AND NOT recurrent, 296 incoming_transaction_id IS NOT NULL AND recurrent 297 INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending 298 FROM prepared_transfers 299 WHERE authorization_pub = in_metadata OR reference_number = in_qr_reference_number; 300 out_unknown_mapping = NOT FOUND; 301 IF out_unknown_mapping OR out_mapping_reuse THEN 302 RETURN; 303 END IF; 304 END IF; 305 306 -- Check reserve pub reuse 307 out_reserve_pub_reuse=NOT out_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM talerable_incoming_transactions WHERE metadata = in_metadata AND type = 'reserve'); 308 IF out_reserve_pub_reuse THEN 309 RETURN; 310 END IF; 311 END IF; 312 313 IF out_found THEN 314 local_ref=COALESCE(in_uetr::text, in_tx_id, in_acct_svcr_ref); 315 -- Check metadata 316 IF in_subject != local_subject THEN 317 RAISE NOTICE 'incoming tx %: stored subject is ''%'' got ''%''', local_ref, local_subject, in_subject; 318 END IF; 319 IF in_debit_payto != local_debit_payto THEN 320 RAISE NOTICE 'incoming tx %: stored subject debit payto is % got %', local_ref, local_debit_payto, in_debit_payto; 321 END IF; 322 IF local_amount != in_amount THEN 323 RAISE NOTICE 'incoming tx %: stored amount is % got %', local_ref, local_amount, in_amount; 324 END IF; 325 UPDATE incoming_transactions 326 SET subject=COALESCE(subject, in_subject), 327 debit_payto=COALESCE(debit_payto, in_debit_payto), 328 uetr=COALESCE(uetr, in_uetr), 329 tx_id=COALESCE(tx_id, in_tx_id), 330 acct_svcr_ref=COALESCE(acct_svcr_ref, in_acct_svcr_ref) 331 WHERE incoming_transaction_id = out_tx_id; 332 out_completed=local_debit_payto IS NULL AND in_debit_payto IS NOT NULL; 333 IF out_completed THEN 334 PERFORM pg_notify('nexus_revenue_tx', out_tx_id::text); 335 END IF; 336 ELSE 337 -- Store the transaction in the database 338 INSERT INTO incoming_transactions ( 339 amount 340 ,credit_fee 341 ,subject 342 ,execution_time 343 ,debit_payto 344 ,uetr 345 ,tx_id 346 ,acct_svcr_ref 347 ) VALUES ( 348 in_amount 349 ,in_credit_fee 350 ,in_subject 351 ,in_execution_time 352 ,in_debit_payto 353 ,in_uetr 354 ,in_tx_id 355 ,in_acct_svcr_ref 356 ) RETURNING incoming_transaction_id INTO out_tx_id; 357 IF in_subject IS NOT NULL AND in_debit_payto IS NOT NULL THEN 358 PERFORM pg_notify('nexus_revenue_tx', out_tx_id::text); 359 END IF; 360 out_talerable=FALSE; 361 END IF; 362 363 -- Register as talerable if not already registered as such and not already bounced 364 IF in_type IS NOT NULL AND NOT out_talerable AND out_bounce_id IS NULL THEN 365 If out_pending THEN 366 -- Delay talerable registration until mapping again 367 INSERT INTO pending_recurrent_incoming_transactions (incoming_transaction_id, authorization_pub) 368 VALUES (out_tx_id, local_authorization_pub); 369 ELSE 370 UPDATE prepared_transfers 371 SET incoming_transaction_id = out_tx_id 372 WHERE (incoming_transaction_id IS NULL AND account_pub = in_metadata) OR authorization_pub = local_authorization_pub; 373 -- We cannot use ON CONFLICT here because conversion use a trigger before insertion that isn't idempotent 374 INSERT INTO talerable_incoming_transactions ( 375 incoming_transaction_id 376 ,type 377 ,metadata 378 ,authorization_pub 379 ,authorization_sig 380 ) VALUES ( 381 out_tx_id 382 ,in_type 383 ,in_metadata 384 ,local_authorization_pub 385 ,local_authorization_sig 386 ); 387 PERFORM pg_notify('nexus_incoming_tx', out_tx_id::text); 388 out_talerable=TRUE; 389 END IF; 390 END IF; 391 END $$; 392 393 CREATE FUNCTION register_and_bounce_incoming( 394 IN in_amount taler_amount 395 ,IN in_credit_fee taler_amount 396 ,IN in_subject TEXT 397 ,IN in_execution_time INT8 398 ,IN in_debit_payto TEXT 399 ,IN in_uetr UUID 400 ,IN in_tx_id TEXT 401 ,IN in_acct_svcr_ref TEXT 402 ,IN in_bounce_amount taler_amount 403 ,IN in_now_date INT8 404 ,IN in_bounce_id TEXT 405 ,IN in_cause TEXT 406 -- Error status 407 ,OUT out_talerable BOOLEAN 408 -- Success return 409 ,OUT out_found BOOLEAN 410 ,OUT out_completed BOOLEAN 411 ,OUT out_tx_id INT8 412 ,OUT out_bounce_id TEXT 413 ) 414 LANGUAGE plpgsql AS $$ 415 DECLARE 416 init_id INT8; 417 bounce_amount taler_amount; 418 BEGIN 419 -- Register incoming transaction 420 SELECT reg.out_found, reg.out_completed, reg.out_tx_id, reg.out_talerable 421 INTO out_found, out_completed, out_tx_id, out_talerable 422 FROM register_incoming(in_amount, in_credit_fee, in_subject, in_execution_time, in_debit_payto, in_uetr, in_tx_id, in_acct_svcr_ref, NULL, NULL, NULL) as reg; 423 -- Cannot bounce a transaction registered as talerable 424 IF out_talerable THEN 425 RETURN; 426 END IF; 427 -- Bounce incoming transaction 428 SELECT bounce.out_bounce_id INTO out_bounce_id FROM bounce_incoming(out_tx_id, in_bounce_amount, in_bounce_id, in_now_date, in_cause) AS bounce; 429 END $$; 430 431 CREATE FUNCTION bounce_incoming( 432 IN in_tx_id INT8 433 ,IN in_bounce_amount taler_amount 434 ,IN in_bounce_id TEXT 435 ,IN in_now_date INT8 436 ,IN in_cause TEXT 437 ,OUT out_bounce_id TEXT 438 ) 439 LANGUAGE plpgsql AS $$ 440 DECLARE 441 local_bank_id TEXT; 442 payto_uri TEXT; 443 init_id INT8; 444 BEGIN 445 -- Check if already bounced 446 SELECT end_to_end_id INTO out_bounce_id 447 FROM libeufin_nexus.initiated_outgoing_transactions 448 JOIN libeufin_nexus.bounced_transactions USING (initiated_outgoing_transaction_id) 449 WHERE incoming_transaction_id = in_tx_id; 450 451 -- Else initiate the bounce transaction 452 IF NOT FOUND THEN 453 out_bounce_id = in_bounce_id; 454 -- Get incoming transaction bank ID and creditor 455 SELECT COALESCE(uetr::text, tx_id, acct_svcr_ref), debit_payto 456 INTO local_bank_id, payto_uri 457 FROM libeufin_nexus.incoming_transactions 458 WHERE incoming_transaction_id = in_tx_id; 459 -- Initiate the bounce transaction 460 INSERT INTO libeufin_nexus.initiated_outgoing_transactions ( 461 amount 462 ,subject 463 ,credit_payto 464 ,initiation_time 465 ,end_to_end_id 466 ) VALUES ( 467 in_bounce_amount 468 ,'bounce ' || local_bank_id || ': ' || in_cause 469 ,payto_uri 470 ,in_now_date 471 ,in_bounce_id 472 ) 473 RETURNING initiated_outgoing_transaction_id INTO init_id; 474 -- Register the bounce 475 INSERT INTO libeufin_nexus.bounced_transactions (incoming_transaction_id, initiated_outgoing_transaction_id) 476 VALUES (in_tx_id, init_id); 477 END IF; 478 479 -- Delete from pending if any 480 DELETE FROM libeufin_nexus.pending_recurrent_incoming_transactions WHERE incoming_transaction_id = in_tx_id; 481 END$$; 482 483 CREATE FUNCTION taler_transfer( 484 IN in_request_uid BYTEA, 485 IN in_wtid BYTEA, 486 IN in_subject TEXT, 487 IN in_amount taler_amount, 488 IN in_exchange_base_url TEXT, 489 IN in_metadata TEXT, 490 IN in_credit_account_payto TEXT, 491 IN in_end_to_end_id TEXT, 492 IN in_timestamp INT8, 493 -- Error status 494 OUT out_request_uid_reuse BOOLEAN, 495 OUT out_wtid_reuse BOOLEAN, 496 -- Success return 497 OUT out_tx_row_id INT8, 498 OUT out_timestamp INT8 499 ) 500 LANGUAGE plpgsql AS $$ 501 BEGIN 502 -- Check for idempotence and conflict 503 SELECT (amount != in_amount 504 OR credit_payto != in_credit_account_payto 505 OR exchange_base_url != in_exchange_base_url 506 OR metadata != in_metadata 507 OR wtid != in_wtid) 508 ,transfer_operations.initiated_outgoing_transaction_id, initiation_time 509 INTO out_request_uid_reuse, out_tx_row_id, out_timestamp 510 FROM transfer_operations 511 JOIN initiated_outgoing_transactions 512 ON transfer_operations.initiated_outgoing_transaction_id=initiated_outgoing_transactions.initiated_outgoing_transaction_id 513 WHERE transfer_operations.request_uid = in_request_uid; 514 IF FOUND THEN 515 RETURN; 516 END IF; 517 out_wtid_reuse = EXISTS(SELECT FROM transfer_operations WHERE wtid = in_wtid); 518 IF out_wtid_reuse THEN 519 RETURN; 520 END IF; 521 out_timestamp=in_timestamp; 522 -- Initiate bank transfer 523 INSERT INTO initiated_outgoing_transactions ( 524 amount 525 ,subject 526 ,credit_payto 527 ,initiation_time 528 ,end_to_end_id 529 ) VALUES ( 530 in_amount 531 ,in_subject 532 ,in_credit_account_payto 533 ,in_timestamp 534 ,in_end_to_end_id 535 ) RETURNING initiated_outgoing_transaction_id INTO out_tx_row_id; 536 -- Register outgoing transaction 537 INSERT INTO transfer_operations( 538 initiated_outgoing_transaction_id 539 ,request_uid 540 ,wtid 541 ,exchange_base_url 542 ,metadata 543 ) VALUES ( 544 out_tx_row_id 545 ,in_request_uid 546 ,in_wtid 547 ,in_exchange_base_url 548 ,in_metadata 549 ); 550 out_timestamp = in_timestamp; 551 PERFORM pg_notify('nexus_outgoing_tx', out_tx_row_id::text); 552 END $$; 553 554 CREATE FUNCTION batch_outgoing_transactions( 555 IN in_timestamp INT8, 556 IN batch_ebics_id TEXT, 557 IN require_ack BOOLEAN 558 ) 559 RETURNS void 560 LANGUAGE plpgsql AS $$ 561 DECLARE 562 batch_id INT8; 563 local_sum taler_amount DEFAULT (0, 0)::taler_amount; 564 tx record; 565 BEGIN 566 -- Create a new batch only if some transactions are not batched 567 IF EXISTS(SELECT FROM initiated_outgoing_transactions WHERE initiated_outgoing_batch_id IS NULL AND (NOT require_ack OR NOT awaiting_ack)) THEN 568 -- Create batch 569 INSERT INTO initiated_outgoing_batches (creation_date, message_id) 570 VALUES (in_timestamp, batch_ebics_id) 571 RETURNING initiated_outgoing_batch_id INTO batch_id; 572 -- Link batched payment while computing the sum of amounts 573 FOR tx IN UPDATE initiated_outgoing_transactions 574 SET initiated_outgoing_batch_id=batch_id 575 WHERE initiated_outgoing_batch_id IS NULL 576 AND (NOT require_ack OR NOT awaiting_ack) 577 RETURNING amount 578 LOOP 579 SELECT sum.val, sum.frac 580 INTO local_sum.val, local_sum.frac 581 FROM amount_add(local_sum, tx.amount) AS sum; 582 END LOOP; 583 -- Update the batch with the sum of amounts 584 UPDATE initiated_outgoing_batches SET sum=local_sum WHERE initiated_outgoing_batch_id=batch_id; 585 END IF; 586 END $$; 587 588 CREATE FUNCTION batch_status_update( 589 IN in_message_id text, 590 IN in_status submission_state, 591 IN in_status_msg text, 592 OUT out_ok BOOLEAN 593 ) 594 LANGUAGE plpgsql AS $$ 595 DECLARE 596 local_batch_id INT8; 597 BEGIN 598 -- Check if there is a batch for this message id 599 SELECT initiated_outgoing_batch_id INTO local_batch_id 600 FROM initiated_outgoing_batches 601 WHERE message_id = in_message_id; 602 out_ok=FOUND; 603 IF FOUND THEN 604 -- Update unsettled batch status 605 UPDATE initiated_outgoing_batches 606 SET status = in_status, status_msg = in_status_msg 607 WHERE initiated_outgoing_batch_id = local_batch_id 608 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 609 610 -- When a batch succeed it doesn't mean that individual transaction also succeed 611 IF in_status = 'success' THEN 612 in_status = 'pending'; 613 END IF; 614 615 -- Update unsettled batch's transaction status 616 UPDATE initiated_outgoing_transactions 617 SET status = in_status, status_msg = in_status_msg 618 WHERE initiated_outgoing_batch_id = local_batch_id 619 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 620 END IF; 621 END $$; 622 623 CREATE FUNCTION tx_status_update( 624 IN in_end_to_end_id text, 625 IN in_message_id text, 626 IN in_status submission_state, 627 IN in_status_msg text, 628 OUT out_ok BOOLEAN 629 ) 630 LANGUAGE plpgsql AS $$ 631 DECLARE 632 local_status submission_state; 633 local_tx_id INT8; 634 BEGIN 635 -- Check current tx status 636 SELECT initiated_outgoing_transaction_id, status INTO local_tx_id, local_status 637 FROM initiated_outgoing_transactions 638 WHERE end_to_end_id = in_end_to_end_id; 639 out_ok=FOUND; 640 IF FOUND THEN 641 -- Update unsettled transaction status 642 IF in_status = 'permanent_failure' OR local_status NOT IN ('success', 'permanent_failure', 'late_failure') THEN 643 IF in_status = 'permanent_failure' AND local_status = 'success' THEN 644 in_status = 'late_failure'; 645 END IF; 646 UPDATE initiated_outgoing_transactions 647 SET status = in_status, status_msg = in_status_msg 648 WHERE initiated_outgoing_transaction_id = local_tx_id; 649 END IF; 650 651 -- Update unsettled batch status 652 UPDATE initiated_outgoing_batches 653 SET status = 'success', status_msg = NULL 654 WHERE message_id = in_message_id 655 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 656 END IF; 657 END $$; 658 659 CREATE FUNCTION register_prepared_transfers ( 660 IN in_type taler_incoming_type, 661 IN in_account_pub BYTEA, 662 IN in_authorization_pub BYTEA, 663 IN in_authorization_sig BYTEA, 664 IN in_recurrent BOOLEAN, 665 IN in_reference_number TEXT, 666 IN in_timestamp INT8, 667 -- Error status 668 OUT out_subject_reuse BOOLEAN, 669 OUT out_reserve_pub_reuse BOOLEAN 670 ) 671 LANGUAGE plpgsql AS $$ 672 DECLARE 673 talerable_tx INT8; 674 idempotent BOOLEAN; 675 BEGIN 676 677 -- Check idempotency 678 SELECT type = in_type 679 AND account_pub = in_account_pub 680 AND recurrent = in_recurrent 681 AND reference_number = in_reference_number 682 INTO idempotent 683 FROM prepared_transfers 684 WHERE authorization_pub = in_authorization_pub; 685 686 -- Check idempotency and delay garbage collection 687 IF FOUND AND idempotent THEN 688 UPDATE prepared_transfers 689 SET registered_at=in_timestamp 690 WHERE authorization_pub=in_authorization_pub; 691 RETURN; 692 END IF; 693 694 -- Check reserve pub reuse and reference_number clash 695 out_reserve_pub_reuse=in_type = 'reserve' AND ( 696 EXISTS(SELECT FROM talerable_incoming_transactions WHERE metadata = in_account_pub AND type = 'reserve') 697 OR EXISTS(SELECT FROM prepared_transfers WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 698 ); 699 out_subject_reuse=EXISTS(SELECT FROM prepared_transfers WHERE authorization_pub != in_authorization_pub AND reference_number = in_reference_number); 700 IF out_reserve_pub_reuse OR out_subject_reuse THEN 701 RETURN; 702 END IF; 703 704 IF in_recurrent THEN 705 -- Finalize one pending right now 706 WITH moved_tx AS ( 707 DELETE FROM pending_recurrent_incoming_transactions 708 WHERE incoming_transaction_id = ( 709 SELECT incoming_transaction_id 710 FROM pending_recurrent_incoming_transactions 711 JOIN incoming_transactions USING (incoming_transaction_id) 712 WHERE authorization_pub = in_authorization_pub 713 ORDER BY execution_time ASC 714 LIMIT 1 715 ) 716 RETURNING incoming_transaction_id 717 ) 718 INSERT INTO talerable_incoming_transactions (incoming_transaction_id, type, metadata, authorization_pub, authorization_sig) 719 SELECT moved_tx.incoming_transaction_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 720 FROM moved_tx 721 RETURNING incoming_transaction_id INTO talerable_tx; 722 IF talerable_tx IS NOT NULL THEN 723 PERFORM pg_notify('nexus_incoming_tx', talerable_tx::text); 724 END IF; 725 ELSE 726 -- Bounce all pending 727 PERFORM bounce_incoming(incoming_transaction_id, amount, ebics_id_gen(), in_timestamp, 'cancelled mapping') 728 FROM incoming_transactions 729 JOIN pending_recurrent_incoming_transactions USING (incoming_transaction_id) 730 WHERE authorization_pub = in_authorization_pub; 731 END IF; 732 733 -- Upsert registration 734 INSERT INTO prepared_transfers ( 735 type, 736 account_pub, 737 authorization_pub, 738 authorization_sig, 739 recurrent, 740 reference_number, 741 registered_at, 742 incoming_transaction_id 743 ) VALUES ( 744 in_type, 745 in_account_pub, 746 in_authorization_pub, 747 in_authorization_sig, 748 in_recurrent, 749 in_reference_number, 750 in_timestamp, 751 talerable_tx 752 ) ON CONFLICT (authorization_pub) 753 DO UPDATE SET 754 type = EXCLUDED.type, 755 account_pub = EXCLUDED.account_pub, 756 recurrent = EXCLUDED.recurrent, 757 reference_number = EXCLUDED.reference_number, 758 registered_at = EXCLUDED.registered_at, 759 incoming_transaction_id = EXCLUDED.incoming_transaction_id, 760 authorization_sig = EXCLUDED.authorization_sig; 761 END $$; 762 763 CREATE FUNCTION delete_prepared_transfers ( 764 IN in_authorization_pub BYTEA, 765 IN in_timestamp INT8, 766 OUT out_found BOOLEAN 767 ) 768 LANGUAGE plpgsql AS $$ 769 BEGIN 770 771 -- Bounce all pending 772 PERFORM bounce_incoming(incoming_transaction_id, amount, ebics_id_gen(), in_timestamp, 'cancelled mapping') 773 FROM incoming_transactions 774 JOIN pending_recurrent_incoming_transactions USING (incoming_transaction_id) 775 WHERE authorization_pub = in_authorization_pub; 776 777 -- Delete registration 778 DELETE FROM prepared_transfers 779 WHERE authorization_pub = in_authorization_pub; 780 out_found = FOUND; 781 782 END $$;