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