depolymerization

depolymerizer-bitcoin-procedures.sql (12835B)

---

 --
 -- 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
           OR metadata IS DISTINCT FROM in_metadata)
         ,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;
 local_taler_in_id INT8;
 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;
 
 -- Lock for revenue id commit ordering
 LOCK TABLE tx_in IN SHARE ROW EXCLUSIVE MODE;
 -- 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 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_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_replaces_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_confirmed BOOLEAN,
   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;
   local_status debit_status;
   local_update BOOLEAN;
 BEGIN
 IF in_confirmed THEN
   local_status='confirmed';
 ELSE
   local_status='sent';
 END IF;
 IF in_wtid IS NOT NULL THEN
   -- Sync transfer status
   SELECT
     txid=in_replaces_txid,
     txid IS NULL,
     status!=local_status OR txid!=in_txid
   INTO
     out_replaced,
     out_recovered,
     local_update
   FROM transfer
   WHERE wtid=in_wtid;
   IF local_update THEN
     UPDATE transfer SET status=local_status,txid=in_txid
     WHERE wtid=in_wtid;
   END IF;
 ELSIF in_bounced_txid IS NOT NULL THEN
   -- Sync bounce status
   SELECT
     bounced.txid=in_replaces_txid,
     bounced.txid IS NULL,
     status!=local_status OR bounced.txid!=in_txid
   INTO
     out_replaced,
     out_recovered,
     local_update
   FROM bounced JOIN tx_in USING (tx_in_id)
   WHERE tx_in.txid=in_bounced_txid;
   IF local_update THEN
     UPDATE bounced SET status=local_status,txid=in_txid
     FROM tx_in
     WHERE bounced.tx_in_id=tx_in.tx_in_id AND tx_in.txid=in_bounced_txid;
   END IF;
 END IF;
 
 IF in_confirmed THEN
   -- Sync tx_out status
   UPDATE tx_out SET txid=in_txid WHERE txid=in_replaces_txid;
   out_replaced=out_replaced OR FOUND;
   SELECT tx_out_id INTO out_tx_row_id
     FROM tx_out WHERE txid=in_txid;
   IF FOUND THEN
     RETURN;
   END IF;
   out_new = TRUE;
 
   -- Lock for outgoing history id commit ordering
   LOCK TABLE tx_out IN SHARE ROW EXCLUSIVE MODE;
   -- 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;
   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;
   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.
   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 received_at ASC
     LIMIT 1
   )
   RETURNING tx_in_id INTO talerable_tx;
   IF talerable_tx IS NOT NULL THEN
     -- Lock for incoming history id commit ordering
     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 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 $$;