libeufin-nexus-procedures.sql (26932B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2023-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 -- Lock for outgoing history id commit ordering 177 LOCK TABLE outgoing_transactions IN SHARE ROW EXCLUSIVE MODE; 178 -- Store the transaction in the database 179 INSERT INTO outgoing_transactions ( 180 amount 181 ,debit_fee 182 ,subject 183 ,execution_time 184 ,credit_payto 185 ,end_to_end_id 186 ,acct_svcr_ref 187 ) VALUES ( 188 in_amount 189 ,in_debit_fee 190 ,in_subject 191 ,in_execution_time 192 ,in_credit_payto 193 ,in_end_to_end_id 194 ,in_acct_svcr_ref 195 ) 196 RETURNING outgoing_transaction_id 197 INTO out_tx_id; 198 199 -- Register as talerable if contains wtid 200 IF in_wtid IS NOT NULL THEN 201 SELECT end_to_end_id INTO local_end_to_end_id 202 FROM talerable_outgoing_transactions 203 JOIN outgoing_transactions USING (outgoing_transaction_id) 204 WHERE wtid=in_wtid; 205 IF FOUND THEN 206 IF local_end_to_end_id != in_end_to_end_id THEN 207 RAISE NOTICE 'wtid reuse: tx % and tx % have the same wtid %', in_end_to_end_id, local_end_to_end_id, in_wtid; 208 END IF; 209 ELSE 210 INSERT INTO talerable_outgoing_transactions( 211 outgoing_transaction_id, 212 wtid, 213 exchange_base_url, 214 metadata 215 ) VALUES ( 216 out_tx_id, 217 in_wtid, 218 in_exchange_url, 219 in_metadata 220 ); 221 PERFORM pg_notify('nexus_outgoing_tx', out_tx_id::text); 222 END IF; 223 END IF; 224 225 IF out_initiated THEN 226 -- Reconciles the related initiated transaction 227 UPDATE initiated_outgoing_transactions 228 SET 229 outgoing_transaction_id = out_tx_id 230 ,status = 'success' 231 ,status_msg = null 232 WHERE initiated_outgoing_transaction_id = init_id 233 AND status != 'late_failure'; 234 235 -- Reconciles the related initiated batch 236 UPDATE initiated_outgoing_batches 237 SET status = 'success', status_msg = null 238 WHERE message_id = in_msg_id AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 239 END IF; 240 END IF; 241 END $$; 242 COMMENT ON FUNCTION register_outgoing 243 IS 'Register an outgoing transaction and optionally reconciles the related initiated transaction with it'; 244 245 CREATE FUNCTION register_incoming( 246 IN in_amount taler_amount 247 ,IN in_credit_fee taler_amount 248 ,IN in_subject TEXT 249 ,IN in_execution_time INT8 250 ,IN in_debit_payto TEXT 251 ,IN in_uetr UUID 252 ,IN in_tx_id TEXT 253 ,IN in_acct_svcr_ref TEXT 254 ,IN in_type taler_incoming_type 255 ,IN in_metadata BYTEA 256 ,IN in_qr_reference_number TEXT 257 -- Error status 258 ,OUT out_reserve_pub_reuse BOOLEAN 259 ,OUT out_mapping_reuse BOOLEAN 260 ,OUT out_unknown_mapping BOOLEAN 261 -- Success return 262 ,OUT out_found BOOLEAN 263 ,OUT out_completed BOOLEAN 264 ,OUT out_talerable BOOLEAN 265 ,OUT out_pending BOOLEAN 266 ,OUT out_tx_id INT8 267 ,OUT out_bounce_id TEXT 268 ) 269 LANGUAGE plpgsql AS $$ 270 DECLARE 271 local_ref TEXT; 272 local_amount taler_amount; 273 local_subject TEXT; 274 local_debit_payto TEXT; 275 local_authorization_pub BYTEA; 276 local_authorization_sig BYTEA; 277 local_taler_in_id INT8; 278 BEGIN 279 IF in_credit_fee = (0, 0)::taler_amount THEN 280 in_credit_fee = NULL; 281 END IF; 282 out_pending=FALSE; 283 284 -- Check if already registered 285 SELECT incoming_transaction_id, tx.subject, debit_payto, (tx.amount).val, (tx.amount).frac, metadata IS NOT NULL, end_to_end_id 286 INTO out_tx_id, local_subject, local_debit_payto, local_amount.val, local_amount.frac, out_talerable, out_bounce_id 287 FROM incoming_transactions AS tx 288 LEFT JOIN talerable_incoming_transactions USING (incoming_transaction_id) 289 LEFT JOIN bounced_transactions USING (incoming_transaction_id) 290 LEFT JOIN initiated_outgoing_transactions USING (initiated_outgoing_transaction_id) 291 WHERE uetr = in_uetr OR tx_id = in_tx_id OR acct_svcr_ref = in_acct_svcr_ref; 292 out_found=FOUND; 293 294 IF NOT out_found OR NOT out_talerable THEN 295 -- Resolve mapping logic 296 IF in_type = 'map' OR in_qr_reference_number IS NOT NULL THEN 297 SELECT type, account_pub, authorization_pub, authorization_sig, 298 incoming_transaction_id IS NOT NULL AND NOT recurrent, 299 incoming_transaction_id IS NOT NULL AND recurrent 300 INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending 301 FROM prepared_transfers 302 WHERE authorization_pub = in_metadata OR reference_number = in_qr_reference_number; 303 out_unknown_mapping = NOT FOUND; 304 IF out_unknown_mapping OR out_mapping_reuse THEN 305 RETURN; 306 END IF; 307 END IF; 308 309 -- Check reserve pub reuse 310 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'); 311 IF out_reserve_pub_reuse THEN 312 RETURN; 313 END IF; 314 END IF; 315 316 IF out_found THEN 317 local_ref=COALESCE(in_uetr::text, in_tx_id, in_acct_svcr_ref); 318 -- Check metadata 319 IF in_subject != local_subject THEN 320 RAISE NOTICE 'incoming tx %: stored subject is ''%'' got ''%''', local_ref, local_subject, in_subject; 321 END IF; 322 IF in_debit_payto != local_debit_payto THEN 323 RAISE NOTICE 'incoming tx %: stored subject debit payto is % got %', local_ref, local_debit_payto, in_debit_payto; 324 END IF; 325 IF local_amount != in_amount THEN 326 RAISE NOTICE 'incoming tx %: stored amount is % got %', local_ref, local_amount, in_amount; 327 END IF; 328 UPDATE incoming_transactions 329 SET subject=COALESCE(subject, in_subject), 330 debit_payto=COALESCE(debit_payto, in_debit_payto), 331 uetr=COALESCE(uetr, in_uetr), 332 tx_id=COALESCE(tx_id, in_tx_id), 333 acct_svcr_ref=COALESCE(acct_svcr_ref, in_acct_svcr_ref) 334 WHERE incoming_transaction_id = out_tx_id; 335 out_completed=local_debit_payto IS NULL AND in_debit_payto IS NOT NULL; 336 IF out_completed THEN 337 PERFORM pg_notify('nexus_revenue_tx', out_tx_id::text); 338 END IF; 339 ELSE 340 -- Lock for revenue id commit ordering 341 LOCK TABLE incoming_transactions IN SHARE ROW EXCLUSIVE MODE; 342 -- Store the transaction in the database 343 INSERT INTO incoming_transactions ( 344 amount 345 ,credit_fee 346 ,subject 347 ,execution_time 348 ,debit_payto 349 ,uetr 350 ,tx_id 351 ,acct_svcr_ref 352 ) VALUES ( 353 in_amount 354 ,in_credit_fee 355 ,in_subject 356 ,in_execution_time 357 ,in_debit_payto 358 ,in_uetr 359 ,in_tx_id 360 ,in_acct_svcr_ref 361 ) RETURNING incoming_transaction_id INTO out_tx_id; 362 IF in_subject IS NOT NULL AND in_debit_payto IS NOT NULL THEN 363 PERFORM pg_notify('nexus_revenue_tx', out_tx_id::text); 364 END IF; 365 out_talerable=FALSE; 366 END IF; 367 368 -- Register as talerable if not already registered as such and not already bounced 369 IF in_type IS NOT NULL AND NOT out_talerable AND out_bounce_id IS NULL THEN 370 If out_pending THEN 371 -- Delay talerable registration until mapping again 372 INSERT INTO pending_recurrent_incoming_transactions (incoming_transaction_id, authorization_pub) 373 VALUES (out_tx_id, local_authorization_pub); 374 ELSE 375 UPDATE prepared_transfers 376 SET incoming_transaction_id = out_tx_id 377 WHERE ( 378 incoming_transaction_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve' 379 ) OR authorization_pub = local_authorization_pub; 380 -- Lock for incoming history id commit ordering 381 LOCK TABLE talerable_incoming_transactions IN SHARE ROW EXCLUSIVE MODE; 382 -- We cannot use ON CONFLICT here because conversion use a trigger before insertion that isn't idempotent 383 INSERT INTO talerable_incoming_transactions ( 384 incoming_transaction_id 385 ,type 386 ,metadata 387 ,authorization_pub 388 ,authorization_sig 389 ) VALUES ( 390 out_tx_id 391 ,in_type 392 ,in_metadata 393 ,local_authorization_pub 394 ,local_authorization_sig 395 ) RETURNING taler_in_id INTO local_taler_in_id; 396 PERFORM pg_notify('nexus_incoming_tx', local_taler_in_id::text); 397 out_talerable=TRUE; 398 END IF; 399 END IF; 400 END $$; 401 402 CREATE FUNCTION register_and_bounce_incoming( 403 IN in_amount taler_amount 404 ,IN in_credit_fee taler_amount 405 ,IN in_subject TEXT 406 ,IN in_execution_time INT8 407 ,IN in_debit_payto TEXT 408 ,IN in_uetr UUID 409 ,IN in_tx_id TEXT 410 ,IN in_acct_svcr_ref TEXT 411 ,IN in_bounce_amount taler_amount 412 ,IN in_now_date INT8 413 ,IN in_bounce_id TEXT 414 ,IN in_cause TEXT 415 -- Error status 416 ,OUT out_talerable BOOLEAN 417 -- Success return 418 ,OUT out_found BOOLEAN 419 ,OUT out_completed BOOLEAN 420 ,OUT out_tx_id INT8 421 ,OUT out_bounce_id TEXT 422 ) 423 LANGUAGE plpgsql AS $$ 424 DECLARE 425 init_id INT8; 426 bounce_amount taler_amount; 427 BEGIN 428 -- Register incoming transaction 429 SELECT reg.out_found, reg.out_completed, reg.out_tx_id, reg.out_talerable 430 INTO out_found, out_completed, out_tx_id, out_talerable 431 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; 432 -- Cannot bounce a transaction registered as talerable 433 IF out_talerable THEN 434 RETURN; 435 END IF; 436 -- Bounce incoming transaction 437 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; 438 END $$; 439 440 CREATE FUNCTION bounce_incoming( 441 IN in_tx_id INT8 442 ,IN in_bounce_amount taler_amount 443 ,IN in_bounce_id TEXT 444 ,IN in_now_date INT8 445 ,IN in_cause TEXT 446 ,OUT out_bounce_id TEXT 447 ) 448 LANGUAGE plpgsql AS $$ 449 DECLARE 450 local_bank_id TEXT; 451 payto_uri TEXT; 452 init_id INT8; 453 BEGIN 454 -- Check if already bounced 455 SELECT end_to_end_id INTO out_bounce_id 456 FROM libeufin_nexus.initiated_outgoing_transactions 457 JOIN libeufin_nexus.bounced_transactions USING (initiated_outgoing_transaction_id) 458 WHERE incoming_transaction_id = in_tx_id; 459 460 -- Else initiate the bounce transaction 461 IF NOT FOUND THEN 462 out_bounce_id = in_bounce_id; 463 -- Get incoming transaction bank ID and creditor 464 SELECT COALESCE(uetr::text, tx_id, acct_svcr_ref), debit_payto 465 INTO local_bank_id, payto_uri 466 FROM libeufin_nexus.incoming_transactions 467 WHERE incoming_transaction_id = in_tx_id; 468 -- Initiate the bounce transaction 469 INSERT INTO libeufin_nexus.initiated_outgoing_transactions ( 470 amount 471 ,subject 472 ,credit_payto 473 ,initiation_time 474 ,end_to_end_id 475 ) VALUES ( 476 in_bounce_amount 477 ,'bounce ' || local_bank_id || ': ' || in_cause 478 ,payto_uri 479 ,in_now_date 480 ,in_bounce_id 481 ) 482 RETURNING initiated_outgoing_transaction_id INTO init_id; 483 -- Register the bounce 484 INSERT INTO libeufin_nexus.bounced_transactions (incoming_transaction_id, initiated_outgoing_transaction_id) 485 VALUES (in_tx_id, init_id); 486 END IF; 487 488 -- Delete from pending if any 489 DELETE FROM libeufin_nexus.pending_recurrent_incoming_transactions WHERE incoming_transaction_id = in_tx_id; 490 END$$; 491 492 CREATE FUNCTION taler_transfer( 493 IN in_request_uid BYTEA, 494 IN in_wtid BYTEA, 495 IN in_subject TEXT, 496 IN in_amount taler_amount, 497 IN in_exchange_base_url TEXT, 498 IN in_metadata TEXT, 499 IN in_credit_account_payto TEXT, 500 IN in_end_to_end_id TEXT, 501 IN in_timestamp INT8, 502 -- Error status 503 OUT out_request_uid_reuse BOOLEAN, 504 OUT out_wtid_reuse BOOLEAN, 505 -- Success return 506 OUT out_tx_row_id INT8, 507 OUT out_timestamp INT8 508 ) 509 LANGUAGE plpgsql AS $$ 510 BEGIN 511 -- Check for idempotence and conflict 512 SELECT (amount != in_amount 513 OR credit_payto != in_credit_account_payto 514 OR exchange_base_url != in_exchange_base_url 515 OR metadata IS DISTINCT FROM in_metadata 516 OR wtid != in_wtid) 517 ,transfer_operations.initiated_outgoing_transaction_id, initiation_time 518 INTO out_request_uid_reuse, out_tx_row_id, out_timestamp 519 FROM transfer_operations 520 JOIN initiated_outgoing_transactions 521 ON transfer_operations.initiated_outgoing_transaction_id=initiated_outgoing_transactions.initiated_outgoing_transaction_id 522 WHERE transfer_operations.request_uid = in_request_uid; 523 IF FOUND THEN 524 RETURN; 525 END IF; 526 out_wtid_reuse = EXISTS(SELECT FROM transfer_operations WHERE wtid = in_wtid); 527 IF out_wtid_reuse THEN 528 RETURN; 529 END IF; 530 out_timestamp=in_timestamp; 531 -- Initiate bank transfer 532 INSERT INTO initiated_outgoing_transactions ( 533 amount 534 ,subject 535 ,credit_payto 536 ,initiation_time 537 ,end_to_end_id 538 ) VALUES ( 539 in_amount 540 ,in_subject 541 ,in_credit_account_payto 542 ,in_timestamp 543 ,in_end_to_end_id 544 ) RETURNING initiated_outgoing_transaction_id INTO out_tx_row_id; 545 -- Register outgoing transaction 546 INSERT INTO transfer_operations( 547 initiated_outgoing_transaction_id 548 ,request_uid 549 ,wtid 550 ,exchange_base_url 551 ,metadata 552 ) VALUES ( 553 out_tx_row_id 554 ,in_request_uid 555 ,in_wtid 556 ,in_exchange_base_url 557 ,in_metadata 558 ); 559 out_timestamp = in_timestamp; 560 PERFORM pg_notify('nexus_outgoing_tx', out_tx_row_id::text); 561 END $$; 562 563 CREATE FUNCTION batch_outgoing_transactions( 564 IN in_timestamp INT8, 565 IN batch_ebics_id TEXT, 566 IN require_ack BOOLEAN 567 ) 568 RETURNS void 569 LANGUAGE plpgsql AS $$ 570 DECLARE 571 batch_id INT8; 572 local_sum taler_amount DEFAULT (0, 0)::taler_amount; 573 tx record; 574 BEGIN 575 -- Create a new batch only if some transactions are not batched 576 IF EXISTS(SELECT FROM initiated_outgoing_transactions WHERE initiated_outgoing_batch_id IS NULL AND (NOT require_ack OR NOT awaiting_ack)) THEN 577 -- Create batch 578 INSERT INTO initiated_outgoing_batches (creation_date, message_id) 579 VALUES (in_timestamp, batch_ebics_id) 580 RETURNING initiated_outgoing_batch_id INTO batch_id; 581 -- Link batched payment while computing the sum of amounts 582 FOR tx IN UPDATE initiated_outgoing_transactions 583 SET initiated_outgoing_batch_id=batch_id 584 WHERE initiated_outgoing_batch_id IS NULL 585 AND (NOT require_ack OR NOT awaiting_ack) 586 RETURNING amount 587 LOOP 588 SELECT sum.val, sum.frac 589 INTO local_sum.val, local_sum.frac 590 FROM amount_add(local_sum, tx.amount) AS sum; 591 END LOOP; 592 -- Update the batch with the sum of amounts 593 UPDATE initiated_outgoing_batches SET sum=local_sum WHERE initiated_outgoing_batch_id=batch_id; 594 END IF; 595 END $$; 596 597 CREATE FUNCTION batch_status_update( 598 IN in_message_id text, 599 IN in_status submission_state, 600 IN in_status_msg text, 601 OUT out_ok BOOLEAN 602 ) 603 LANGUAGE plpgsql AS $$ 604 DECLARE 605 local_batch_id INT8; 606 BEGIN 607 -- Check if there is a batch for this message id 608 SELECT initiated_outgoing_batch_id INTO local_batch_id 609 FROM initiated_outgoing_batches 610 WHERE message_id = in_message_id; 611 out_ok=FOUND; 612 IF FOUND THEN 613 -- Update unsettled batch status 614 UPDATE initiated_outgoing_batches 615 SET status = in_status, status_msg = in_status_msg 616 WHERE initiated_outgoing_batch_id = local_batch_id 617 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 618 619 -- When a batch succeed it doesn't mean that individual transaction also succeed 620 IF in_status = 'success' THEN 621 in_status = 'pending'; 622 END IF; 623 624 -- Update unsettled batch's transaction status 625 UPDATE initiated_outgoing_transactions 626 SET status = in_status, status_msg = in_status_msg 627 WHERE initiated_outgoing_batch_id = local_batch_id 628 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 629 END IF; 630 END $$; 631 632 CREATE FUNCTION tx_status_update( 633 IN in_end_to_end_id text, 634 IN in_message_id text, 635 IN in_status submission_state, 636 IN in_status_msg text, 637 OUT out_ok BOOLEAN 638 ) 639 LANGUAGE plpgsql AS $$ 640 DECLARE 641 local_status submission_state; 642 local_tx_id INT8; 643 BEGIN 644 -- Check current tx status 645 SELECT initiated_outgoing_transaction_id, status INTO local_tx_id, local_status 646 FROM initiated_outgoing_transactions 647 WHERE end_to_end_id = in_end_to_end_id; 648 out_ok=FOUND; 649 IF FOUND THEN 650 -- Update unsettled transaction status 651 IF in_status = 'permanent_failure' OR local_status NOT IN ('success', 'permanent_failure', 'late_failure') THEN 652 IF in_status = 'permanent_failure' AND local_status = 'success' THEN 653 in_status = 'late_failure'; 654 END IF; 655 UPDATE initiated_outgoing_transactions 656 SET status = in_status, status_msg = in_status_msg 657 WHERE initiated_outgoing_transaction_id = local_tx_id; 658 END IF; 659 660 -- Update unsettled batch status 661 UPDATE initiated_outgoing_batches 662 SET status = 'success', status_msg = NULL 663 WHERE message_id = in_message_id 664 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 665 END IF; 666 END $$; 667 668 CREATE FUNCTION register_prepared_transfers ( 669 IN in_type taler_incoming_type, 670 IN in_account_pub BYTEA, 671 IN in_authorization_pub BYTEA, 672 IN in_authorization_sig BYTEA, 673 IN in_recurrent BOOLEAN, 674 IN in_reference_number TEXT, 675 IN in_timestamp INT8, 676 -- Error status 677 OUT out_subject_reuse BOOLEAN, 678 OUT out_reserve_pub_reuse BOOLEAN 679 ) 680 LANGUAGE plpgsql AS $$ 681 DECLARE 682 talerable_tx INT8; 683 local_taler_in_id INT8; 684 idempotent BOOLEAN; 685 BEGIN 686 687 -- Check idempotency 688 SELECT type = in_type 689 AND account_pub = in_account_pub 690 AND recurrent = in_recurrent 691 AND reference_number = in_reference_number 692 INTO idempotent 693 FROM prepared_transfers 694 WHERE authorization_pub = in_authorization_pub; 695 696 -- Check idempotency and delay garbage collection 697 IF idempotent THEN 698 UPDATE prepared_transfers 699 SET registered_at=in_timestamp, authorization_sig=in_authorization_sig 700 WHERE authorization_pub=in_authorization_pub; 701 RETURN; 702 END IF; 703 704 -- Check reserve pub reuse and reference_number clash 705 out_reserve_pub_reuse=in_type = 'reserve' AND ( 706 EXISTS(SELECT FROM talerable_incoming_transactions WHERE metadata=in_account_pub AND type='reserve') 707 OR EXISTS(SELECT FROM prepared_transfers WHERE account_pub=in_account_pub AND type='reserve' AND authorization_pub != in_authorization_pub) 708 ); 709 out_subject_reuse=EXISTS(SELECT FROM prepared_transfers WHERE authorization_pub != in_authorization_pub AND reference_number = in_reference_number); 710 IF out_reserve_pub_reuse OR out_subject_reuse THEN 711 RETURN; 712 END IF; 713 714 IF in_recurrent THEN 715 -- Finalize one pending right now. 716 DELETE FROM pending_recurrent_incoming_transactions 717 WHERE incoming_transaction_id = ( 718 SELECT incoming_transaction_id 719 FROM pending_recurrent_incoming_transactions 720 JOIN incoming_transactions USING (incoming_transaction_id) 721 WHERE authorization_pub = in_authorization_pub 722 ORDER BY execution_time ASC 723 LIMIT 1 724 ) 725 RETURNING incoming_transaction_id INTO talerable_tx; 726 IF talerable_tx IS NOT NULL THEN 727 -- Lock for incoming history id commit ordering 728 LOCK TABLE talerable_incoming_transactions IN SHARE ROW EXCLUSIVE MODE; 729 INSERT INTO talerable_incoming_transactions (incoming_transaction_id, type, metadata, authorization_pub, authorization_sig) 730 VALUES (talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig) 731 RETURNING incoming_transaction_id, taler_in_id INTO talerable_tx, local_taler_in_id; 732 IF talerable_tx IS NOT NULL THEN 733 PERFORM pg_notify('nexus_incoming_tx', local_taler_in_id::text); 734 END IF; 735 END IF; 736 ELSE 737 -- Bounce all pending 738 PERFORM bounce_incoming(incoming_transaction_id, amount, ebics_id_gen(), in_timestamp, 'cancelled mapping') 739 FROM incoming_transactions 740 JOIN pending_recurrent_incoming_transactions USING (incoming_transaction_id) 741 WHERE authorization_pub = in_authorization_pub; 742 END IF; 743 744 -- Upsert registration 745 INSERT INTO prepared_transfers ( 746 type, 747 account_pub, 748 authorization_pub, 749 authorization_sig, 750 recurrent, 751 reference_number, 752 registered_at, 753 incoming_transaction_id 754 ) VALUES ( 755 in_type, 756 in_account_pub, 757 in_authorization_pub, 758 in_authorization_sig, 759 in_recurrent, 760 in_reference_number, 761 in_timestamp, 762 talerable_tx 763 ) ON CONFLICT (authorization_pub) 764 DO UPDATE SET 765 type = EXCLUDED.type, 766 account_pub = EXCLUDED.account_pub, 767 recurrent = EXCLUDED.recurrent, 768 reference_number = EXCLUDED.reference_number, 769 registered_at = EXCLUDED.registered_at, 770 incoming_transaction_id = EXCLUDED.incoming_transaction_id, 771 authorization_sig = EXCLUDED.authorization_sig; 772 END $$; 773 774 CREATE FUNCTION delete_prepared_transfers ( 775 IN in_authorization_pub BYTEA, 776 IN in_timestamp INT8, 777 OUT out_found BOOLEAN 778 ) 779 LANGUAGE plpgsql AS $$ 780 BEGIN 781 782 -- Bounce all pending 783 PERFORM bounce_incoming(incoming_transaction_id, amount, ebics_id_gen(), in_timestamp, 'cancelled mapping') 784 FROM incoming_transactions 785 JOIN pending_recurrent_incoming_transactions USING (incoming_transaction_id) 786 WHERE authorization_pub = in_authorization_pub; 787 788 -- Delete registration 789 DELETE FROM prepared_transfers 790 WHERE authorization_pub = in_authorization_pub; 791 out_found = FOUND; 792 793 END $$;