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