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