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