exchange

Base system with REST service to issue digital coins, run by the payment service provider
Log | Files | Refs | Submodules | README | LICENSE

0002-wire_targets.sql (4470B)

      1 --
      2 -- This file is part of TALER
      3 -- Copyright (C) 2014--2025 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 
     17 CREATE OR REPLACE FUNCTION random_bytea(
     18   bytea_length INT
     19 )
     20 RETURNS BYTEA
     21   AS $body$
     22   SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
     23     FROM generate_series(1, $1);
     24   $body$
     25 LANGUAGE 'sql'
     26 VOLATILE;
     27 
     28 
     29 CREATE FUNCTION create_table_wire_targets(
     30   IN partition_suffix TEXT DEFAULT NULL
     31 )
     32 RETURNS VOID
     33 LANGUAGE plpgsql
     34 AS $$
     35 BEGIN
     36   PERFORM create_partitioned_table(
     37     'CREATE TABLE %I'
     38       '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY'
     39       ',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)'
     40       ',payto_uri TEXT NOT NULL'
     41       ',access_token BYTEA CHECK(LENGTH(access_token)=32)'
     42       ' DEFAULT random_bytea(32)'
     43       ',target_pub BYTEA CHECK(LENGTH(target_pub)=32) DEFAULT NULL'
     44       ',h_normalized_payto BYTEA CHECK(LENGTH(h_normalized_payto)=32) DEFAULT NULL'
     45       ',aml_program_lock_timeout INT8 DEFAULT NULL'
     46     ') %s ;'
     47     ,'wire_targets'
     48     ,'PARTITION BY HASH (wire_target_h_payto)'
     49     ,partition_suffix
     50 
     51 );
     52   PERFORM comment_partitioned_table(
     53      'All senders and recipients of money via the exchange'
     54     ,'wire_targets'
     55     ,partition_suffix
     56   );
     57   PERFORM comment_partitioned_column(
     58      'Full payto URI. Can identify a regular bank account, or also be a URI identifying a reserve-account (for P2P payments)'
     59     ,'payto_uri'
     60     ,'wire_targets'
     61     ,partition_suffix
     62   );
     63   PERFORM comment_partitioned_column(
     64      'Unsalted hash of (full) payto_uri'
     65     ,'wire_target_h_payto'
     66     ,'wire_targets'
     67     ,partition_suffix
     68   );
     69   PERFORM comment_partitioned_column(
     70      'high-entropy random value that is used as a bearer token used to authenticate access to the KYC SPA and its state (without requiring a signature)'
     71     ,'access_token'
     72     ,'wire_targets'
     73     ,NULL
     74   );
     75   PERFORM comment_partitioned_column(
     76      'Public key of a merchant instance or reserve to authenticate access; NULL if KYC is not allowed for the account (if there was no incoming KYC wire transfer yet); updated, thus NOT available to the auditor'
     77     ,'target_pub'
     78     ,'wire_targets'
     79     ,NULL
     80   );
     81   PERFORM comment_partitioned_column(
     82      'hash over the normalized payto URI for this account; used for KYC operations; NULL if not available (due to DB migration not initializing this value)'
     83     ,'h_normalized_payto'
     84     ,'wire_targets'
     85     ,NULL
     86   );
     87   PERFORM comment_partitioned_column(
     88      'If non-NULL, an AML program should be running and it holds a lock on this account, thus other AML programs should not be started concurrently. Given the possibility of crashes, the lock automatically expires at the time value given in this column. At that time, the lock can be considered stale.'
     89     ,'aml_program_lock_timeout'
     90     ,'wire_targets'
     91     ,NULL
     92   );
     93 END $$;
     94 
     95 
     96 CREATE FUNCTION constrain_table_wire_targets(
     97   IN partition_suffix TEXT
     98 )
     99 RETURNS void
    100 LANGUAGE plpgsql
    101 AS $$
    102 DECLARE
    103   table_name TEXT DEFAULT 'wire_targets';
    104 BEGIN
    105   table_name = concat_ws('_', table_name, partition_suffix);
    106   EXECUTE FORMAT (
    107     'ALTER TABLE ' || table_name ||
    108     ' ADD CONSTRAINT ' || table_name || '_wire_target_serial_id_key'
    109     ' UNIQUE (wire_target_serial_id)'
    110   );
    111   EXECUTE FORMAT (
    112     'ALTER TABLE ' || table_name ||
    113     ' ADD CONSTRAINT ' || table_name || '_wire_target_access_token_unique'
    114     ' UNIQUE (access_token)'
    115   );
    116   EXECUTE FORMAT (
    117     'CREATE INDEX ' || table_name || '_normalized_h_payto_index '
    118     'ON ' || table_name || ' '
    119     '(h_normalized_payto);'
    120   );
    121 END
    122 $$;
    123 
    124 
    125 INSERT INTO exchange_tables
    126     (name
    127     ,version
    128     ,action
    129     ,partitioned
    130     ,by_range)
    131   VALUES
    132     ('wire_targets'
    133     ,'exchange-0002'
    134     ,'create'
    135     ,TRUE
    136     ,FALSE),
    137     ('wire_targets'
    138     ,'exchange-0002'
    139     ,'constrain'
    140     ,TRUE
    141     ,FALSE);