depolymerization

depolymerizer-bitcoin-procedures.sql (11712B)

---

 --
 -- 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 depolymerizer_bitcoin;
 
 -- 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 = 'depolymerizer_bitcoin'::regnamespace;
 
   IF _sql IS NOT NULL THEN
     EXECUTE _sql;
   END IF;
 END
 $do$;
 
 CREATE FUNCTION taler_transfer(
   IN in_amount taler_amount,
   IN in_exchange_base_url TEXT,
   IN in_credit_acc TEXT,
   IN in_credit_name TEXT,
   IN in_request_uid BYTEA,
   IN in_wtid BYTEA,
   IN in_metadata TEXT,
   IN in_now INT8,
   -- Error status
   OUT out_request_uid_reuse BOOLEAN,
   OUT out_wtid_reuse BOOLEAN,
   -- Success return
   OUT out_transfer_row_id INT8,
   OUT out_created_at INT8
 )
 LANGUAGE plpgsql AS $$
 BEGIN
 -- Check for idempotence and conflict
 SELECT (amount != in_amount
           OR credit_acc != in_credit_acc
           OR credit_name != in_credit_name
           OR exchange_url != in_exchange_base_url
           OR wtid != in_wtid)
         ,transfer_id, created_at
   INTO out_request_uid_reuse, out_transfer_row_id, out_created_at
   FROM transfer
   WHERE request_uid = in_request_uid;
 IF FOUND THEN
   RETURN;
 END IF;
 
 -- Register a transfer operation
 INSERT INTO transfer (
   amount,
   exchange_url,
   credit_acc,
   credit_name,
   request_uid,
   wtid,
   metadata,
   created_at,
   status
 ) VALUES (
   in_amount,
   in_exchange_base_url,
   in_credit_acc,
   in_credit_name,
   in_request_uid,
   in_wtid,
   in_metadata,
   in_now,
   'requested'
 ) ON CONFLICT (wtid) DO NOTHING
   RETURNING transfer_id, created_at INTO out_transfer_row_id, out_created_at;
 out_wtid_reuse=NOT FOUND;
 IF out_wtid_reuse THEN
   RETURN;
 END IF;
 -- Notify new transaction
 PERFORM pg_notify('transfer', out_transfer_row_id || '');
 END $$;
 COMMENT ON FUNCTION taler_transfer IS 'Create an outgoing taler transaction and register it';
 
 CREATE FUNCTION register_tx_in(
   IN in_txid BYTEA,
   IN in_amount taler_amount,
   IN in_debit_acc TEXT,
   IN in_received_at INT8,
   IN in_type incoming_type,
   IN in_metadata BYTEA,
   -- 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;
 BEGIN
 out_pending=false;
 
 -- Check for idempotence, txid is a hash of the transaction data, if the txid match all info match
 SELECT tx_in_id, received_at INTO out_tx_row_id, out_valued_at FROM tx_in WHERE txid = in_txid;
 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
 INSERT INTO tx_in (
   txid,
   amount,
   debit_acc,
   received_at
 ) VALUES (
   in_txid,
   in_amount,
   in_debit_acc,
   in_received_at
 ) RETURNING tx_in_id, received_at INTO out_tx_row_id, out_valued_at;
 -- 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) OR authorization_pub = local_authorization_pub;
   -- Insert new incoming talerable tranreceived_atsaction
   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
   );
   -- Notify new incoming talerable transaction registration
   PERFORM pg_notify('taler_in', out_tx_row_id || '');
 END IF;
 END $$;
 COMMENT ON FUNCTION register_tx_in IS 'Register an incoming transaction idempotently';
 
 
 CREATE FUNCTION register_bounce_tx_in(
   IN in_txid BYTEA,
   IN in_amount taler_amount,
   IN in_debit_acc TEXT,
   IN in_received_at INT8,
   IN in_reason TEXT,
   IN in_now INT8,
   -- Success return
   OUT out_tx_row_id INT8,
   OUT out_tx_new BOOLEAN,
   OUT out_bounce_row_id INT8,
   OUT out_bounce_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_txid, in_amount, in_debit_acc, in_received_at, NULL, NULL);
 
 -- Register bounce
 INSERT INTO bounced(
   tx_in_id,
   reason,
   status
 ) VALUES (
   out_tx_row_id,
   in_reason,
   'requested'
 ) ON CONFLICT (tx_in_id) DO NOTHING;
 END $$;
 COMMENT ON FUNCTION register_bounce_tx_in IS 'Register an incoming transaction and bounce it idempotently';
 
 CREATE FUNCTION sync_out(
   IN in_txid BYTEA,
   IN in_replace_txid BYTEA,
   IN in_amount taler_amount,
   IN in_credit_acc TEXT,
   IN in_wtid BYTEA,
   IN in_exchange_base_url TEXT,
   IN in_metadata TEXT,
   IN in_bounced_txid BYTEA,
   IN in_created_at INT8,
   IN in_now INT8,
   -- Success return
   OUT out_tx_row_id INT8,
   OUT out_new BOOLEAN,
   OUT out_replaced BOOLEAN,
   OUT out_recovered BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 DECLARE
   local_id INT8;
 BEGIN
 UPDATE tx_out SET txid=in_txid WHERE txid=in_replace_txid;
 out_replaced=FOUND;
 out_new=NOT EXISTS(SELECT FROM tx_out WHERE txid = in_txid);
 IF NOT out_new THEN
     RETURN;
 END IF;
 
 -- Insert new outgoing transaction
 INSERT INTO tx_out (
   amount,
   credit_acc,
   txid,
   created_at
 ) VALUES (
   in_amount,
   in_credit_acc,
   in_txid,
   in_created_at
 ) RETURNING tx_out_id INTO out_tx_row_id;
 -- Notify new outgoing transaction registration
 PERFORM pg_notify('tx_out', out_tx_row_id || '');
 
 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_exchange_base_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;
   -- Update transfer state
   UPDATE transfer SET status='confirmed',txid=in_txid WHERE wtid=in_wtid;
   out_recovered=FOUND;
 ELSIF in_bounced_txid IS NOT NULL THEN
   -- Update bounce state
   IF NOT EXISTS(SELECT FROM bounced JOIN tx_in USING (tx_in_id) WHERE tx_in.txid=in_bounced_txid) THEN
     INSERT INTO bounced (
       tx_in_id,
       txid,
       status
     ) VALUES (
       (SELECT tx_in_id FROM tx_in WHERE txid=in_bounced_txid),
       in_txid,
       'confirmed'
     );
     out_recovered=TRUE;
   ELSE
     UPDATE bounced SET status='confirmed',txid=in_txid
     FROM tx_in
     WHERE bounced.tx_in_id=tx_in.tx_in_id AND tx_in.txid=in_bounced_txid;
     out_recovered=FALSE;
   END IF;
 END IF;
 END $$;
 COMMENT ON FUNCTION sync_out IS 'Sync a debit blockchain state with local state';
 
 
 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;
   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
   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
   WITH moved_tx AS (
     DELETE FROM pending_recurrent_in
     WHERE tx_in_id = (
       SELECT txid
       FROM tx_in
       JOIN tx_in USING (tx_in_id)
       WHERE authorization_pub = in_authorization_pub
       ORDER BY created_at ASC
       LIMIT 1
     )
     RETURNING tx_in_id
   )
   INSERT INTO taler_in (tx_in_id, type, metadata, authorization_pub, authorization_sig)
   SELECT moved_tx.tx_in_id, in_type, in_account_pub, in_authorization_pub, in_authorization_sig
   FROM moved_tx
   RETURNING tx_in_id INTO talerable_tx;
   IF talerable_tx IS NOT NULL THEN
     PERFORM pg_notify('taler_in', talerable_tx::text);
   END IF;
 ELSE
   -- Bounce all pending
   WITH bounced AS (
     DELETE FROM pending_recurrent_in
     WHERE authorization_pub = in_authorization_pub
     RETURNING tx_in_id
   )
   INSERT INTO bounced (tx_in_id, reason, status)
   SELECT tx_in_id, 'cancelled mapping', 'requested' FROM bounced;
 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,
   IN in_timestamp INT8,
   OUT out_found BOOLEAN
 )
 LANGUAGE plpgsql AS $$
 BEGIN
 
 -- Bounce all pending
 WITH bounced AS (
   DELETE FROM pending_recurrent_in
   WHERE authorization_pub = in_authorization_pub
   RETURNING tx_in_id
 )
 INSERT INTO bounced (tx_in_id, reason, status)
 SELECT tx_in_id, 'cancelled mapping', 'requested' FROM bounced;
 
 -- Delete registration
 DELETE FROM prepared_in
 WHERE authorization_pub = in_authorization_pub;
 out_found = FOUND;
 
 END $$;