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