magnet-bank-procedures.sql (14758B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 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 SET search_path TO magnet_bank; 17 18 -- Remove all existing functions 19 DO 20 $do$ 21 DECLARE 22 _sql text; 23 BEGIN 24 SELECT INTO _sql 25 string_agg(format('DROP %s %s CASCADE;' 26 , CASE prokind 27 WHEN 'f' THEN 'FUNCTION' 28 WHEN 'p' THEN 'PROCEDURE' 29 END 30 , oid::regprocedure) 31 , E'\n') 32 FROM pg_proc 33 WHERE pronamespace = 'magnet_bank'::regnamespace; 34 35 IF _sql IS NOT NULL THEN 36 EXECUTE _sql; 37 END IF; 38 END 39 $do$; 40 41 CREATE FUNCTION register_tx_in( 42 IN in_code INT8, 43 IN in_amount taler_amount, 44 IN in_subject TEXT, 45 IN in_debit_account TEXT, 46 IN in_debit_name TEXT, 47 IN in_valued_at INT8, 48 IN in_type incoming_type, 49 IN in_metadata BYTEA, 50 IN in_now INT8, 51 -- Error status 52 OUT out_reserve_pub_reuse BOOLEAN, 53 OUT out_mapping_reuse BOOLEAN, 54 OUT out_unknown_mapping BOOLEAN, 55 -- Success return 56 OUT out_tx_row_id INT8, 57 OUT out_valued_at INT8, 58 OUT out_new BOOLEAN, 59 OUT out_pending BOOLEAN 60 ) 61 LANGUAGE plpgsql AS $$ 62 DECLARE 63 local_authorization_pub BYTEA; 64 local_authorization_sig BYTEA; 65 local_taler_in_id INT8; 66 BEGIN 67 out_pending=false; 68 -- Check for idempotence 69 SELECT tx_in_id, valued_at 70 INTO out_tx_row_id, out_valued_at 71 FROM tx_in 72 WHERE magnet_code = in_code; 73 out_new = NOT found; 74 IF NOT out_new THEN 75 RETURN; 76 END IF; 77 78 -- Resolve mapping logic 79 IF in_type = 'map' THEN 80 SELECT type, account_pub, authorization_pub, authorization_sig, 81 tx_in_id IS NOT NULL AND NOT recurrent, 82 tx_in_id IS NOT NULL AND recurrent 83 INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending 84 FROM prepared_in 85 WHERE authorization_pub = in_metadata; 86 out_unknown_mapping = NOT FOUND; 87 IF out_unknown_mapping OR out_mapping_reuse THEN 88 RETURN; 89 END IF; 90 END IF; 91 92 -- Check conflict 93 out_reserve_pub_reuse=NOT out_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM taler_in WHERE metadata = in_metadata AND type = 'reserve'); 94 IF out_reserve_pub_reuse THEN 95 RETURN; 96 END IF; 97 98 -- Insert new incoming transaction 99 out_valued_at = in_valued_at; 100 INSERT INTO tx_in ( 101 magnet_code, 102 amount, 103 subject, 104 debit_account, 105 debit_name, 106 valued_at, 107 registered_at 108 ) VALUES ( 109 in_code, 110 in_amount, 111 in_subject, 112 in_debit_account, 113 in_debit_name, 114 in_valued_at, 115 in_now 116 ) 117 RETURNING tx_in_id INTO out_tx_row_id; 118 -- Notify new incoming transaction registration 119 PERFORM pg_notify('tx_in', out_tx_row_id || ''); 120 121 IF out_pending THEN 122 -- Delay talerable registration until mapping again 123 INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub) 124 VALUES (out_tx_row_id, local_authorization_pub); 125 ELSIF in_type IS NOT NULL THEN 126 UPDATE prepared_in 127 SET tx_in_id = out_tx_row_id 128 WHERE ( 129 tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve' 130 ) OR authorization_pub = local_authorization_pub; 131 -- Insert new incoming talerable transaction 132 INSERT INTO taler_in ( 133 tx_in_id, 134 type, 135 metadata, 136 authorization_pub, 137 authorization_sig 138 ) VALUES ( 139 out_tx_row_id, 140 in_type, 141 in_metadata, 142 local_authorization_pub, 143 local_authorization_sig 144 ) RETURNING taler_in_id INTO local_taler_in_id; 145 -- Notify new incoming talerable transaction registration 146 PERFORM pg_notify('taler_in', local_taler_in_id::text); 147 END IF; 148 END $$; 149 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently'; 150 151 CREATE FUNCTION register_tx_out( 152 IN in_code INT8, 153 IN in_amount taler_amount, 154 IN in_subject TEXT, 155 IN in_credit_account TEXT, 156 IN in_credit_name TEXT, 157 IN in_valued_at INT8, 158 IN in_wtid BYTEA, 159 IN in_origin_exchange_url TEXT, 160 IN in_metadata TEXT, 161 IN in_bounced INT8, 162 IN in_now INT8, 163 -- Success return 164 OUT out_tx_row_id INT8, 165 OUT out_result register_result 166 ) 167 LANGUAGE plpgsql AS $$ 168 BEGIN 169 -- Check for idempotence 170 SELECT tx_out_id INTO out_tx_row_id 171 FROM tx_out WHERE magnet_code = in_code; 172 173 IF FOUND THEN 174 out_result = 'idempotent'; 175 RETURN; 176 END IF; 177 178 -- Insert new outgoing transaction 179 INSERT INTO tx_out ( 180 magnet_code, 181 amount, 182 subject, 183 credit_account, 184 credit_name, 185 valued_at, 186 registered_at 187 ) VALUES ( 188 in_code, 189 in_amount, 190 in_subject, 191 in_credit_account, 192 in_credit_name, 193 in_valued_at, 194 in_now 195 ) 196 RETURNING tx_out_id INTO out_tx_row_id; 197 -- Notify new outgoing transaction registration 198 PERFORM pg_notify('tx_out', out_tx_row_id || ''); 199 200 -- Update initiated status 201 UPDATE initiated 202 SET 203 tx_out_id = out_tx_row_id, 204 status = 'success', 205 status_msg = NULL 206 WHERE magnet_code = in_code; 207 IF FOUND THEN 208 out_result = 'known'; 209 ELSE 210 out_result = 'recovered'; 211 END IF; 212 213 IF in_wtid IS NOT NULL THEN 214 -- Insert new outgoing talerable transaction 215 INSERT INTO taler_out ( 216 tx_out_id, 217 wtid, 218 exchange_base_url, 219 metadata 220 ) VALUES ( 221 out_tx_row_id, 222 in_wtid, 223 in_origin_exchange_url, 224 in_metadata 225 ) ON CONFLICT (wtid) DO NOTHING; 226 IF FOUND THEN 227 -- Notify new outgoing talerable transaction registration 228 PERFORM pg_notify('taler_out', out_tx_row_id || ''); 229 END IF; 230 ELSIF in_bounced IS NOT NULL THEN 231 UPDATE initiated 232 SET 233 tx_out_id = out_tx_row_id, 234 status = 'success', 235 status_msg = NULL 236 FROM bounced JOIN tx_in USING (tx_in_id) 237 WHERE initiated.initiated_id = bounced.initiated_id AND tx_in.magnet_code = in_bounced; 238 END IF; 239 END $$; 240 COMMENT ON FUNCTION register_tx_out IS 'Register an outgoing transaction idempotently'; 241 242 CREATE FUNCTION register_tx_out_failure( 243 IN in_code INT8, 244 IN in_bounced INT8, 245 IN in_now INT8, 246 -- Success return 247 OUT out_initiated_id INT8, 248 OUT out_new BOOLEAN 249 ) 250 LANGUAGE plpgsql AS $$ 251 DECLARE 252 current_status transfer_status; 253 BEGIN 254 -- Found existing initiated transaction or bounced transaction 255 SELECT status, initiated_id 256 INTO current_status, out_initiated_id 257 FROM initiated 258 LEFT JOIN bounced USING (initiated_id) 259 LEFT JOIN tx_in USING (tx_in_id) 260 WHERE initiated.magnet_code = in_code OR tx_in.magnet_code = in_bounced; 261 262 -- Update status if new 263 out_new = FOUND AND current_status != 'permanent_failure'; 264 IF out_new THEN 265 UPDATE initiated 266 SET 267 status = 'permanent_failure', 268 status_msg = NULL 269 WHERE initiated_id = out_initiated_id; 270 END IF; 271 END $$; 272 COMMENT ON FUNCTION register_tx_out_failure IS 'Register an outgoing transaction failure idempotently'; 273 274 CREATE FUNCTION taler_transfer( 275 IN in_request_uid BYTEA, 276 IN in_wtid BYTEA, 277 IN in_subject TEXT, 278 IN in_amount taler_amount, 279 IN in_exchange_base_url TEXT, 280 IN in_metadata TEXT, 281 IN in_credit_account TEXT, 282 IN in_credit_name TEXT, 283 IN in_now INT8, 284 -- Error return 285 OUT out_request_uid_reuse BOOLEAN, 286 OUT out_wtid_reuse BOOLEAN, 287 -- Success return 288 OUT out_initiated_row_id INT8, 289 OUT out_initiated_at INT8 290 ) 291 LANGUAGE plpgsql AS $$ 292 BEGIN 293 -- Check for idempotence and conflict 294 SELECT (amount != in_amount 295 OR credit_account != in_credit_account 296 OR exchange_base_url != in_exchange_base_url 297 OR wtid != in_wtid 298 OR metadata IS DISTINCT FROM in_metadata) 299 ,initiated_id, initiated_at 300 INTO out_request_uid_reuse, out_initiated_row_id, out_initiated_at 301 FROM transfer JOIN initiated USING (initiated_id) 302 WHERE request_uid = in_request_uid; 303 IF FOUND THEN 304 RETURN; 305 END IF; 306 -- Check for wtid reuse 307 out_wtid_reuse = EXISTS(SELECT FROM transfer WHERE wtid=in_wtid); 308 IF out_wtid_reuse THEN 309 RETURN; 310 END IF; 311 -- Insert an initiated outgoing transaction 312 out_initiated_at = in_now; 313 INSERT INTO initiated ( 314 amount, 315 subject, 316 credit_account, 317 credit_name, 318 initiated_at 319 ) VALUES ( 320 in_amount, 321 in_subject, 322 in_credit_account, 323 in_credit_name, 324 in_now 325 ) RETURNING initiated_id 326 INTO out_initiated_row_id; 327 -- Insert a transfer operation 328 INSERT INTO transfer ( 329 initiated_id, 330 request_uid, 331 wtid, 332 exchange_base_url, 333 metadata 334 ) VALUES ( 335 out_initiated_row_id, 336 in_request_uid, 337 in_wtid, 338 in_exchange_base_url, 339 in_metadata 340 ); 341 PERFORM pg_notify('transfer', out_initiated_row_id || ''); 342 END $$; 343 344 CREATE FUNCTION initiated_status_update( 345 IN in_initiated_id INT8, 346 IN in_status transfer_status, 347 IN in_status_msg TEXT 348 ) 349 RETURNS void 350 LANGUAGE plpgsql AS $$ 351 DECLARE 352 current_status transfer_status; 353 BEGIN 354 -- Check current status 355 SELECT status INTO current_status FROM initiated 356 WHERE initiated_id = in_initiated_id; 357 IF FOUND THEN 358 -- Update unsettled transaction status 359 IF current_status = 'success' AND in_status = 'permanent_failure' THEN 360 UPDATE initiated 361 SET status = 'late_failure', status_msg = in_status_msg 362 WHERE initiated_id = in_initiated_id; 363 ELSIF current_status NOT IN ('success', 'permanent_failure', 'late_failure') THEN 364 UPDATE initiated 365 SET status = in_status, status_msg = in_status_msg 366 WHERE initiated_id = in_initiated_id; 367 END IF; 368 END IF; 369 END $$; 370 371 CREATE FUNCTION register_bounce_tx_in( 372 IN in_code INT8, 373 IN in_amount taler_amount, 374 IN in_subject TEXT, 375 IN in_debit_account TEXT, 376 IN in_debit_name TEXT, 377 IN in_valued_at INT8, 378 IN in_reason TEXT, 379 IN in_now INT8, 380 -- Success return 381 OUT out_tx_row_id INT8, 382 OUT out_tx_new BOOLEAN, 383 OUT out_bounce_row_id INT8, 384 OUT out_bounce_new BOOLEAN 385 ) 386 LANGUAGE plpgsql AS $$ 387 BEGIN 388 -- Register incoming transaction idempotently 389 SELECT register_tx_in.out_tx_row_id, register_tx_in.out_new 390 INTO out_tx_row_id, out_tx_new 391 FROM register_tx_in(in_code, in_amount, in_subject, in_debit_account, in_debit_name, in_valued_at, NULL, NULL, in_now); 392 393 -- Check if already bounce 394 SELECT initiated_id 395 INTO out_bounce_row_id 396 FROM bounced JOIN initiated USING (initiated_id) 397 WHERE tx_in_id = out_tx_row_id; 398 out_bounce_new=NOT FOUND; 399 -- Else initiate the bounce transaction 400 IF out_bounce_new THEN 401 -- Initiate the bounce transaction 402 INSERT INTO initiated ( 403 amount, 404 subject, 405 credit_account, 406 credit_name, 407 initiated_at 408 ) VALUES ( 409 in_amount, 410 'bounce: ' || in_code, 411 in_debit_account, 412 in_debit_name, 413 in_now 414 ) 415 RETURNING initiated_id INTO out_bounce_row_id; 416 -- Register the bounce 417 INSERT INTO bounced ( 418 tx_in_id, 419 initiated_id, 420 reason 421 ) VALUES ( 422 out_tx_row_id, 423 out_bounce_row_id, 424 in_reason 425 ); 426 END IF; 427 END $$; 428 COMMENT ON FUNCTION register_bounce_tx_in IS 'Register an incoming transaction and bounce it idempotently'; 429 430 CREATE FUNCTION bounce_pending( 431 in_authorization_pub BYTEA, 432 in_timestamp INT8 433 ) 434 RETURNS void 435 LANGUAGE plpgsql AS $$ 436 DECLARE 437 local_tx_id INT8; 438 local_initiated_id INTEGER; 439 BEGIN 440 FOR local_tx_id IN 441 DELETE FROM pending_recurrent_in 442 WHERE authorization_pub = in_authorization_pub 443 RETURNING tx_in_id 444 LOOP 445 INSERT INTO initiated ( 446 amount, 447 subject, 448 credit_account, 449 credit_name, 450 initiated_at 451 ) 452 SELECT 453 amount, 454 CONCAT('bounce: ', magnet_code), 455 debit_account, 456 debit_name, 457 in_timestamp 458 FROM tx_in 459 WHERE tx_in_id = local_tx_id 460 RETURNING initiated_id INTO local_initiated_id; 461 462 INSERT INTO bounced (tx_in_id, initiated_id, reason) 463 VALUES (local_tx_id, local_initiated_id, 'cancelled mapping'); 464 END LOOP; 465 END; 466 $$; 467 468 CREATE FUNCTION register_prepared_transfers ( 469 IN in_type incoming_type, 470 IN in_account_pub BYTEA, 471 IN in_authorization_pub BYTEA, 472 IN in_authorization_sig BYTEA, 473 IN in_recurrent BOOLEAN, 474 IN in_timestamp INT8, 475 -- Error status 476 OUT out_reserve_pub_reuse BOOLEAN 477 ) 478 LANGUAGE plpgsql AS $$ 479 DECLARE 480 talerable_tx INT8; 481 local_taler_in_id INT8; 482 idempotent BOOLEAN; 483 BEGIN 484 485 -- Check idempotency 486 SELECT type = in_type 487 AND account_pub = in_account_pub 488 AND recurrent = in_recurrent 489 INTO idempotent 490 FROM prepared_in 491 WHERE authorization_pub = in_authorization_pub; 492 493 -- Check idempotency and delay garbage collection 494 IF FOUND AND idempotent THEN 495 UPDATE prepared_in 496 SET registered_at=in_timestamp,authorization_sig=in_authorization_sig 497 WHERE authorization_pub=in_authorization_pub; 498 RETURN; 499 END IF; 500 501 -- Check reserve pub reuse 502 out_reserve_pub_reuse=in_type = 'reserve' AND ( 503 EXISTS(SELECT FROM taler_in WHERE metadata = in_account_pub AND type = 'reserve') 504 OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub) 505 ); 506 IF out_reserve_pub_reuse THEN 507 RETURN; 508 END IF; 509 510 IF in_recurrent THEN 511 -- Finalize one pending right now 512 WITH moved_tx AS ( 513 DELETE FROM pending_recurrent_in 514 WHERE tx_in_id = ( 515 SELECT tx_in_id 516 FROM pending_recurrent_in 517 JOIN tx_in USING (tx_in_id) 518 WHERE authorization_pub = in_authorization_pub 519 ORDER BY registered_at ASC 520 LIMIT 1 521 ) 522 RETURNING tx_in_id 523 ) 524 INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig) 525 SELECT moved_tx.tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig 526 FROM moved_tx 527 RETURNING tx_in_id, taler_in_id INTO talerable_tx, local_taler_in_id; 528 IF talerable_tx IS NOT NULL THEN 529 PERFORM pg_notify('taler_in', local_taler_in_id::text); 530 END IF; 531 ELSE 532 -- Bounce all pending 533 PERFORM bounce_pending(in_authorization_pub, in_timestamp); 534 END IF; 535 536 -- Upsert registration 537 INSERT INTO prepared_in ( 538 type, 539 account_pub, 540 authorization_pub, 541 authorization_sig, 542 recurrent, 543 registered_at, 544 tx_in_id 545 ) VALUES ( 546 in_type, 547 in_account_pub, 548 in_authorization_pub, 549 in_authorization_sig, 550 in_recurrent, 551 in_timestamp, 552 talerable_tx 553 ) ON CONFLICT (authorization_pub) 554 DO UPDATE SET 555 type = EXCLUDED.type, 556 account_pub = EXCLUDED.account_pub, 557 recurrent = EXCLUDED.recurrent, 558 registered_at = EXCLUDED.registered_at, 559 tx_in_id = EXCLUDED.tx_in_id, 560 authorization_sig = EXCLUDED.authorization_sig; 561 END $$; 562 563 CREATE FUNCTION delete_prepared_transfers ( 564 IN in_authorization_pub BYTEA, 565 IN in_timestamp INT8, 566 OUT out_found BOOLEAN 567 ) 568 LANGUAGE plpgsql AS $$ 569 BEGIN 570 571 -- Bounce all pending 572 PERFORM bounce_pending(in_authorization_pub, in_timestamp); 573 574 -- Delete registration 575 DELETE FROM prepared_in 576 WHERE authorization_pub = in_authorization_pub; 577 out_found = FOUND; 578 579 END $$;