wise-0001.sql (3705B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 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 SELECT _v.register_patch('wise-0001', NULL, NULL); 17 18 CREATE SCHEMA wise; 19 SET search_path TO wise; 20 21 CREATE TYPE taler_amount AS (val INT8, frac INT4); 22 COMMENT ON TYPE taler_amount IS 'Stores an amount, fraction is in units of 1/100000000 of the base value'; 23 24 CREATE TABLE balances ( 25 balance_id INT8 PRIMARY KEY, 26 created_at INT8 NOT NULL, 27 early_cursor INT8 NOT NULL, 28 sync_cursor INT8 NOT NULL, 29 checkpoint_cursor INT8 NOT NULL, 30 sync_execution_at INT8, 31 early_success_at INT8, 32 sync_success_at INT8, 33 checkpoint_success_at INT8 34 ); 35 COMMENT ON TABLE balances IS 'Balance creation time, transaction cursors and successful scan times (UTC microseconds)'; 36 COMMENT ON COLUMN balances.sync_execution_at IS 'Last sync attempt (UTC microseconds)'; 37 38 CREATE TABLE tx_in ( 39 tx_in_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 40 balance_id INT8 NOT NULL, 41 wise_ref TEXT UNIQUE, 42 amount taler_amount NOT NULL, 43 subject TEXT NOT NULL, 44 debit_name TEXT NOT NULL, 45 debit_payto TEXT, 46 valued_at INT8 NOT NULL, 47 registered_at INT8 NOT NULL 48 ); 49 COMMENT ON TABLE tx_in IS 'Incoming transactions'; 50 51 CREATE INDEX tx_in_balance_account ON tx_in (balance_id); 52 53 CREATE TYPE incoming_type AS ENUM 54 ('reserve' ,'kyc', 'map'); 55 COMMENT ON TYPE incoming_type IS 'Types of incoming talerable transactions'; 56 57 CREATE TABLE taler_in ( 58 balance_id INT8 NOT NULL, 59 taler_in_id INT8 GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 60 tx_in_id INT8 NOT NULL UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE CASCADE, 61 type incoming_type NOT NULL, 62 metadata BYTEA NOT NULL CHECK (LENGTH(metadata)=32), 63 authorization_pub BYTEA CHECK (LENGTH(authorization_pub)=32), 64 authorization_sig BYTEA CHECK (LENGTH(authorization_sig)=64) 65 ); 66 COMMENT ON TABLE taler_in IS 'Incoming talerable transactions'; 67 68 CREATE UNIQUE INDEX taler_in_unique_reserve_pub ON taler_in (balance_id, metadata) WHERE type = 'reserve'; 69 70 CREATE TABLE prepared_in ( 71 balance_id INT8 NOT NULL, 72 type incoming_type NOT NULL, 73 account_pub BYTEA NOT NULL CHECK (LENGTH(account_pub)=32), 74 authorization_pub BYTEA NOT NULL CHECK (LENGTH(authorization_pub)=32), 75 authorization_sig BYTEA NOT NULL CHECK (LENGTH(authorization_sig)=64), 76 recurrent BOOLEAN NOT NULL, 77 registered_at INT8 NOT NULL, 78 tx_in_id INT8 UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE CASCADE, 79 PRIMARY KEY (balance_id, authorization_pub) 80 ); 81 COMMENT ON TABLE prepared_in IS 'Prepared incoming transaction'; 82 CREATE UNIQUE INDEX prepared_in_unique_reserve_pub 83 ON prepared_in (balance_id, account_pub) WHERE type = 'reserve'; 84 85 CREATE TABLE pending_recurrent_in( 86 tx_in_id INT8 NOT NULL UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE CASCADE, 87 balance_id INT8 NOT NULL, 88 authorization_pub BYTEA NOT NULL, 89 FOREIGN KEY (balance_id, authorization_pub) 90 REFERENCES prepared_in(balance_id, authorization_pub) 91 ); 92 CREATE INDEX pending_recurrent_inc_auth_pub 93 ON pending_recurrent_in (balance_id, authorization_pub); 94 COMMENT ON TABLE pending_recurrent_in IS 'Pending recurrent incoming transaction';