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