taler-rust

cyclos-procedures.sql (14399B)

---

 --
 -- This file is part of TALER
 -- Copyright (C) 2025-2026 Taler Systems SA
 --
 -- TALER is free software; you can redistribute it and/or modify it under the
 -- terms of the GNU General Public License as published by the Free Software
 -- Foundation; either version 3, or (at your option) any later version.
 --
 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 -- A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
 --
 -- You should have received a copy of the GNU General Public License along with
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 
 SET search_path TO cyclos;
 
 -- Remove all existing functions
 DO
 $do$
 DECLARE
   _sql text;
 BEGIN
   SELECT INTO _sql
         string_agg(format('DROP %s %s CASCADE;'
                         , CASE prokind
                             WHEN 'f' THEN 'FUNCTION'
                             WHEN 'p' THEN 'PROCEDURE'
                           END
                         , oid::regprocedure)
                   , E'\n')
   FROM   pg_proc
   WHERE  pronamespace = 'cyclos'::regnamespace;
 
   IF _sql IS NOT NULL THEN
     EXECUTE _sql;
   END IF;
 END
 $do$;
 
 CREATE FUNCTION register_tx_in(
   IN in_transfer_id INT8,
   IN in_tx_id INT8,
   IN in_amount taler_amount,
   IN in_subject TEXT,
   IN in_debit_account INT8,
   IN in_debit_name TEXT,
   IN in_valued_at INT8,
   IN in_type incoming_type,
   IN in_metadata BYTEA,
   IN in_now INT8,
   -- Error status
   OUT out_reserve_pub_reuse BOOLEAN,
   OUT out_mapping_reuse BOOLEAN,
   OUT out_unknown_mapping BOOLEAN,
   -- Success return
   OUT out_tx_row_id INT8,
   OUT out_valued_at INT8,
   OUT out_new BOOLEAN,
   OUT out_pending BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 local_authorization_pub BYTEA;
 local_authorization_sig BYTEA;
 local_taler_in_id INT8;
 BEGIN
 out_pending=false;
 -- Check for idempotence
 SELECT tx_in_id, valued_at
 INTO out_tx_row_id, out_valued_at
 FROM tx_in
 WHERE transfer_id = in_transfer_id;
 out_new = NOT found;
 IF NOT out_new THEN
   RETURN;
 END IF;
 
 -- Resolve mapping logic
 IF in_type = 'map' THEN
   SELECT type, account_pub, authorization_pub, authorization_sig,
       tx_in_id IS NOT NULL AND NOT recurrent,
       tx_in_id IS NOT NULL AND recurrent
     INTO in_type, in_metadata, local_authorization_pub, local_authorization_sig, out_mapping_reuse, out_pending
     FROM prepared_in
     WHERE authorization_pub = in_metadata;
   out_unknown_mapping = NOT FOUND;
   IF out_unknown_mapping OR out_mapping_reuse THEN
     RETURN;
   END IF;
 END IF;
 
 
 -- Check conflict
 out_reserve_pub_reuse=NOT out_pending AND in_type = 'reserve' AND EXISTS(SELECT FROM taler_in WHERE metadata = in_metadata AND type = 'reserve');
 IF out_reserve_pub_reuse THEN
   RETURN;
 END IF;
 
 -- Insert new incoming transaction
 out_valued_at = in_valued_at;
 -- Lock for revenue id commit ordering
 LOCK TABLE tx_in IN SHARE ROW EXCLUSIVE MODE;
 INSERT INTO tx_in (
   transfer_id,
   tx_id,
   amount,
   subject,
   debit_account,
   debit_name,
   valued_at,
   registered_at
 ) VALUES (
   in_transfer_id,
   in_tx_id,
   in_amount,
   in_subject,
   in_debit_account,
   in_debit_name,
   in_valued_at,
   in_now
 )
 RETURNING tx_in_id INTO out_tx_row_id;
 -- Notify new incoming transaction registration
 PERFORM pg_notify('tx_in', out_tx_row_id || '');
 
 IF out_pending THEN
   -- Delay talerable registration until mapping again
   INSERT INTO pending_recurrent_in (tx_in_id, authorization_pub)
     VALUES (out_tx_row_id, local_authorization_pub);
 ELSIF in_type IS NOT NULL THEN
   UPDATE prepared_in
   SET tx_in_id = out_tx_row_id
   WHERE (
     tx_in_id IS NULL AND account_pub = in_metadata AND in_type=type AND type='reserve'
   ) OR authorization_pub = local_authorization_pub;
   -- Lock for incoming history id commit ordering
   LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
   -- Insert new incoming talerable transaction
   INSERT INTO taler_in (
     tx_in_id,
     type,
     metadata,
     authorization_pub,
     authorization_sig
   ) VALUES (
     out_tx_row_id,
     in_type,
     in_metadata,
     local_authorization_pub,
     local_authorization_sig
   ) RETURNING taler_in_id INTO local_taler_in_id;
   -- Notify new incoming talerable transaction registration
   PERFORM pg_notify('taler_in', local_taler_in_id::text);
 END IF;
 END $$;
 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently';
 
 CREATE FUNCTION register_tx_out(
   IN in_transfer_id INT8,
   IN in_tx_id INT8,
   IN in_amount taler_amount,
   IN in_subject TEXT,
   IN in_credit_account INT8,
   IN in_credit_name TEXT,
   IN in_valued_at INT8,
   IN in_wtid BYTEA,
   IN in_origin_exchange_url TEXT,
   IN in_metadata TEXT,
   IN in_bounced INT8,
   IN in_now INT8,
   -- Success return
   OUT out_tx_row_id INT8,
   OUT out_result register_result
 )
 LANGUAGE plpgsql AS $$
 BEGIN
 -- Check for idempotence
 SELECT tx_out_id INTO out_tx_row_id
 FROM tx_out WHERE transfer_id = in_transfer_id;
 
 IF FOUND THEN
   out_result = 'idempotent';
   RETURN;
 END IF;
 
 -- Lock for revenue and incoming history id commit ordering
 LOCK TABLE tx_out IN SHARE ROW EXCLUSIVE MODE;
 -- Insert new outgoing transaction
 INSERT INTO tx_out (
   transfer_id,
   tx_id,
   amount,
   subject,
   credit_account,
   credit_name,
   valued_at,
   registered_at
 ) VALUES (
   in_transfer_id,
   in_tx_id,
   in_amount,
   in_subject,
   in_credit_account,
   in_credit_name,
   in_valued_at,
   in_now
 )
 RETURNING tx_out_id INTO out_tx_row_id;
 -- Notify new outgoing transaction registration
 PERFORM pg_notify('tx_out', out_tx_row_id || '');
 
 -- Update initiated status
 UPDATE initiated
 SET
   tx_out_id = out_tx_row_id,
   status = 'success',
   status_msg = NULL
 WHERE tx_id = in_tx_id;
 IF FOUND OR EXISTS(SELECT FROM bounced WHERE chargeback_id = in_transfer_id) THEN
   out_result = 'known';
 ELSE
   -- Make it idempotent using wtid matching TODO find a idempotent way with cyclos API
   UPDATE initiated
   SET
     tx_out_id = out_tx_row_id,
     status = 'success',
     status_msg = NULL
   FROM transfer
   WHERE transfer.initiated_id = initiated.initiated_id AND wtid = in_wtid;
   out_result = 'recovered';
 END IF;
 
 IF in_wtid IS NOT NULL THEN
   -- Insert new outgoing talerable transaction
   INSERT INTO taler_out (
     tx_out_id,
     wtid,
     exchange_base_url,
     metadata
   ) VALUES (
     out_tx_row_id,
     in_wtid,
     in_origin_exchange_url,
     in_metadata
   ) ON CONFLICT (wtid) DO NOTHING;
   IF FOUND THEN
     -- Notify new outgoing talerable transaction registration
     PERFORM pg_notify('taler_out', out_tx_row_id || '');
   END IF;
 ELSIF in_bounced IS NOT NULL THEN
   -- TODO Reconstruct bounces ?
 END IF;
 END $$;
 COMMENT ON FUNCTION register_tx_out IS 'Register an outgoing transaction idempotently';
 
 CREATE FUNCTION register_charge_back_failure(
   IN in_transfer_id INT8,
   -- Success return
   OUT out_initiated_id INT8,
   OUT out_new BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
 current_status transfer_status;
 BEGIN
 -- Found existing initiated transaction
 SELECT status, initiated_id
 INTO current_status, out_initiated_id
 FROM initiated
 JOIN tx_out USING (tx_out_id)
 WHERE transfer_id = in_transfer_id;
 IF NOT FOUND THEN
   out_initiated_id=0;
   RETURN;
 END IF;
 
 -- Update status if new
 out_new = current_status != 'late_failure';
 IF out_new THEN
   UPDATE initiated
   SET
     status = 'late_failure',
     status_msg = 'charged back'
   WHERE initiated_id = out_initiated_id;
 END IF;
 END $$;
 COMMENT ON FUNCTION register_charge_back_failure IS 'Register an outgoing transaction chargeback idempotently';
 
 CREATE FUNCTION taler_transfer(
   IN in_request_uid BYTEA,
   IN in_wtid BYTEA,
   IN in_subject TEXT,
   IN in_amount taler_amount,
   IN in_exchange_base_url TEXT,
   IN in_metadata TEXT,
   IN in_credit_account INT8,
   IN in_credit_name TEXT,
   IN in_now INT8,
   -- Error return
   OUT out_request_uid_reuse BOOLEAN,
   OUT out_wtid_reuse BOOLEAN,
   -- Success return
   OUT out_initiated_row_id INT8,
   OUT out_initiated_at INT8
 )
 LANGUAGE plpgsql AS $$
 BEGIN
 -- Check for idempotence and conflict
 SELECT (amount != in_amount
           OR credit_account != in_credit_account
           OR exchange_base_url != in_exchange_base_url
           OR wtid != in_wtid
           OR metadata IS DISTINCT FROM in_metadata)
         ,initiated_id, initiated_at
 INTO out_request_uid_reuse, out_initiated_row_id, out_initiated_at
 FROM transfer JOIN initiated USING (initiated_id)
 WHERE request_uid = in_request_uid;
 IF FOUND THEN
   RETURN;
 END IF;
 -- Check for wtid reuse
 out_wtid_reuse = EXISTS(SELECT FROM transfer WHERE wtid=in_wtid);
 IF out_wtid_reuse THEN
   RETURN;
 END IF;
 -- Insert an initiated outgoing transaction
 out_initiated_at = in_now;
 LOCK TABLE transfer IN SHARE ROW EXCLUSIVE MODE;
 INSERT INTO initiated (
   amount,
   subject,
   credit_account,
   credit_name,
   initiated_at
 ) VALUES (
   in_amount,
   in_subject,
   in_credit_account,
   in_credit_name,
   in_now
 ) RETURNING initiated_id
 INTO out_initiated_row_id;
 -- Insert a transfer operation
 INSERT INTO transfer (
   initiated_id,
   request_uid,
   wtid,
   exchange_base_url,
   metadata
 ) VALUES (
   out_initiated_row_id,
   in_request_uid,
   in_wtid,
   in_exchange_base_url,
   in_metadata
 );
 PERFORM pg_notify('transfer', out_initiated_row_id || '');
 END $$;
 
 CREATE FUNCTION initiated_status_update(
   IN in_initiated_id INT8,
   IN in_status transfer_status,
   IN in_status_msg TEXT
 )
 RETURNS void
 LANGUAGE plpgsql AS $$
 DECLARE
 current_status transfer_status;
 BEGIN
   -- Check current status
   SELECT status INTO current_status FROM initiated
     WHERE initiated_id = in_initiated_id;
   IF FOUND THEN
     -- Update unsettled transaction status
     IF current_status = 'success' AND in_status = 'permanent_failure' THEN
       UPDATE initiated
       SET status = 'late_failure', status_msg = in_status_msg
       WHERE initiated_id = in_initiated_id;
     ELSIF current_status NOT IN ('success', 'permanent_failure', 'late_failure') THEN
       UPDATE initiated
       SET status = in_status, status_msg = in_status_msg
       WHERE initiated_id = in_initiated_id;
     END IF;
   END IF;
 END $$;
 
 CREATE FUNCTION register_bounced_tx_in(
   IN in_transfer_id INT8,
   IN in_tx_id INT8,
   IN in_amount taler_amount,
   IN in_subject TEXT,
   IN in_debit_account INT8,
   IN in_debit_name TEXT,
   IN in_valued_at INT8,
   IN in_chargeback_id INT8,
   IN in_reason TEXT,
   IN in_now INT8,
   -- Success return
   OUT out_tx_row_id INT8,
   OUT out_tx_new BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 BEGIN
 -- Register incoming transaction idempotently
 SELECT register_tx_in.out_tx_row_id, register_tx_in.out_new
 INTO out_tx_row_id, out_tx_new
 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);
 
 -- Register the bounce
 INSERT INTO bounced (
   tx_in_id,
   chargeback_id,
   reason
 ) VALUES (
   out_tx_row_id,
   in_chargeback_id,
   in_reason
 ) ON CONFLICT (chargeback_id) DO NOTHING;
 END $$;
 COMMENT ON FUNCTION register_bounced_tx_in IS 'Register a bounced incoming transaction idempotently';
 
 CREATE FUNCTION register_prepared_transfers (
   IN in_type incoming_type,
   IN in_account_pub BYTEA,
   IN in_authorization_pub BYTEA,
   IN in_authorization_sig BYTEA,
   IN in_recurrent BOOLEAN,
   IN in_timestamp INT8,
   -- Error status
   OUT out_reserve_pub_reuse BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
   talerable_tx INT8;
   local_taler_in_id INT8;
   idempotent BOOLEAN;
 BEGIN
 
 -- Check idempotency
 SELECT type = in_type
     AND account_pub = in_account_pub
     AND recurrent = in_recurrent
 INTO idempotent
 FROM prepared_in
 WHERE authorization_pub = in_authorization_pub;
 
 -- Check idempotency and delay garbage collection
 IF FOUND AND idempotent THEN
   UPDATE prepared_in
   SET registered_at=in_timestamp,authorization_sig=in_authorization_sig
   WHERE authorization_pub=in_authorization_pub;
   RETURN;
 END IF;
 
 -- Check reserve pub reuse
 out_reserve_pub_reuse=in_type = 'reserve' AND (
   EXISTS(SELECT FROM taler_in WHERE metadata = in_account_pub AND type = 'reserve')
   OR EXISTS(SELECT FROM prepared_in WHERE account_pub = in_account_pub AND type = 'reserve' AND authorization_pub != in_authorization_pub)
 );
 IF out_reserve_pub_reuse THEN
   RETURN;
 END IF;
 
 IF in_recurrent THEN
   -- Finalize one pending right now. Lock history only if there is a row to publish.
   DELETE FROM pending_recurrent_in
   WHERE tx_in_id = (
     SELECT tx_in_id
     FROM pending_recurrent_in
     JOIN tx_in USING (tx_in_id)
     WHERE authorization_pub = in_authorization_pub
     ORDER BY registered_at ASC
     LIMIT 1
   )
   RETURNING tx_in_id INTO talerable_tx;
   IF talerable_tx IS NOT NULL THEN
     LOCK TABLE taler_in IN SHARE ROW EXCLUSIVE MODE;
     INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig)
     VALUES (talerable_tx, in_type, in_account_pub, in_authorization_pub, in_authorization_sig)
     RETURNING taler_in_id INTO local_taler_in_id;
     PERFORM pg_notify('taler_in', local_taler_in_id::text);
   END IF;
 ELSE
   -- Bounce all pending
   WITH pending AS (
     DELETE FROM pending_recurrent_in
     WHERE authorization_pub = in_authorization_pub
     RETURNING tx_in_id
   )
   INSERT INTO bounced (
     tx_in_id,
     chargeback_id,
     reason
   )
   SELECT
     tx_in_id,
     NULL,
     'cancelled mapping'
   FROM pending;
 END IF;
 
 -- Upsert registration
 INSERT INTO prepared_in (
   type,
   account_pub,
   authorization_pub,
   authorization_sig,
   recurrent,
   registered_at,
   tx_in_id
 ) VALUES (
   in_type,
   in_account_pub,
   in_authorization_pub,
   in_authorization_sig,
   in_recurrent,
   in_timestamp,
   talerable_tx
 ) ON CONFLICT (authorization_pub)
 DO UPDATE SET
   type = EXCLUDED.type,
   account_pub = EXCLUDED.account_pub,
   recurrent = EXCLUDED.recurrent,
   registered_at = EXCLUDED.registered_at,
   tx_in_id = EXCLUDED.tx_in_id,
   authorization_sig = EXCLUDED.authorization_sig;
 END $$;
 
 CREATE FUNCTION delete_prepared_transfers (
   IN in_authorization_pub BYTEA,
   OUT out_found BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 BEGIN
 
 -- Bounce all pending
 WITH pending AS (
   DELETE FROM pending_recurrent_in
   WHERE authorization_pub = in_authorization_pub
   RETURNING tx_in_id
 )
 INSERT INTO bounced (
   tx_in_id,
   chargeback_id,
   reason
 )
 SELECT
   tx_in_id,
   NULL,
   'cancelled mapping'
 FROM pending;
 
 -- Delete registration
 DELETE FROM prepared_in
 WHERE authorization_pub = in_authorization_pub;
 out_found = FOUND;
 
 END $$;