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