libeufin-nexus-0001.sql (4632B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2023 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 BEGIN; 17 18 SELECT _v.register_patch('libeufin-nexus-0001', NULL, NULL); 19 20 CREATE SCHEMA libeufin_nexus; 21 SET search_path TO libeufin_nexus; 22 23 CREATE TYPE taler_amount 24 AS (val INT8, frac INT4); 25 COMMENT ON TYPE taler_amount 26 IS 'Stores an amount, fraction is in units of 1/100000000 of the base value'; 27 28 CREATE TYPE submission_state AS ENUM 29 ('unsubmitted' 30 ,'transient_failure' 31 ,'permanent_failure' 32 ,'success' 33 ,'never_heard_back' 34 ); 35 COMMENT ON TYPE submission_state 36 IS 'expresses the state of an initiated outgoing transaction, where 37 unsubmitted is the default. transient_failure suggests that the submission 38 should be retried, in contrast to the permanent_failure state. success 39 means that the submission itself was successful, but in no way means that 40 the bank will fulfill the request. That must be asked via camt.5x or pain.002. 41 never_heard_back is a fallback state, in case one successful submission did 42 never get confirmed via camt.5x or pain.002.'; 43 44 CREATE TABLE incoming_transactions 45 (incoming_transaction_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 46 ,amount taler_amount NOT NULL 47 ,wire_transfer_subject TEXT NOT NULL 48 ,execution_time INT8 NOT NULL 49 ,debit_payto_uri TEXT NOT NULL 50 ,bank_id TEXT NOT NULL UNIQUE 51 ); 52 COMMENT ON COLUMN incoming_transactions.bank_id 53 IS 'ISO20022 AccountServicerReference'; 54 55 CREATE TABLE talerable_incoming_transactions 56 (incoming_transaction_id INT8 NOT NULL UNIQUE REFERENCES incoming_transactions(incoming_transaction_id) ON DELETE CASCADE 57 ,reserve_public_key BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_public_key)=32) 58 ); 59 60 CREATE TABLE outgoing_transactions 61 (outgoing_transaction_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 62 ,amount taler_amount NOT NULL 63 ,wire_transfer_subject TEXT 64 ,execution_time INT8 NOT NULL 65 ,credit_payto_uri TEXT 66 ,message_id TEXT NOT NULL UNIQUE 67 ); 68 COMMENT ON COLUMN outgoing_transactions.message_id 69 IS 'ISO20022 MessageIdentification'; 70 71 CREATE TABLE initiated_outgoing_transactions 72 (initiated_outgoing_transaction_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 73 ,amount taler_amount NOT NULL 74 ,wire_transfer_subject TEXT NOT NULL 75 ,initiation_time INT8 NOT NULL 76 ,last_submission_time INT8 77 ,submission_counter INT NOT NULL DEFAULT 0 78 ,credit_payto_uri TEXT NOT NULL 79 ,outgoing_transaction_id INT8 UNIQUE REFERENCES outgoing_transactions (outgoing_transaction_id) 80 ,submitted submission_state DEFAULT 'unsubmitted' 81 ,hidden BOOL DEFAULT FALSE -- FIXME: explain this. 82 ,request_uid TEXT NOT NULL UNIQUE CHECK (char_length(request_uid) <= 35) 83 ,failure_message TEXT -- NOTE: that may mix soon failures (those found at initiation time), or late failures (those found out along a fetch operation) 84 ); 85 COMMENT ON COLUMN initiated_outgoing_transactions.outgoing_transaction_id 86 IS 'Points to the bank transaction that was found via nexus-fetch. If "submitted" is false or nexus-fetch could not download this initiation, this column is expected to be NULL.'; 87 COMMENT ON COLUMN initiated_outgoing_transactions.request_uid 88 IS 'Unique identifier of this outgoing transaction initiation. 89 This value could come both from a nexus-httpd client or directly 90 generated when nexus-fetch bounces one payment. In both cases, this 91 value will be used as a unique identifier for its related pain.001 document. 92 For this reason, it must have at most 35 characters'; 93 94 -- only active in exchange mode. 95 CREATE TABLE bounced_transactions 96 (incoming_transaction_id INT8 NOT NULL UNIQUE REFERENCES incoming_transactions(incoming_transaction_id) ON DELETE CASCADE 97 ,initiated_outgoing_transaction_id INT8 NOT NULL UNIQUE REFERENCES initiated_outgoing_transactions(initiated_outgoing_transaction_id) ON DELETE CASCADE 98 ); 99 100 CREATE INDEX incoming_transaction_timestamp 101 ON incoming_transactions (execution_time); 102 103 CREATE INDEX outgoing_transaction_timestamp 104 ON outgoing_transactions (execution_time); 105 106 COMMIT;