wise-0001.sql (3112B)
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 last_sync_at INT8 NOT NULL, 27 last_sync_success_at INT8 NOT NULL 28 ); 29 COMMENT ON TABLE balances IS 'Synchronized balances accounts'; 30 31 CREATE TABLE tx_in ( 32 tx_in_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 33 balance_id INT8 NOT NULL, 34 wise_ref TEXT UNIQUE, 35 amount taler_amount NOT NULL, 36 subject TEXT NOT NULL, 37 debit_name TEXT NOT NULL, 38 debit_payto TEXT, 39 valued_at INT8 NOT NULL, 40 registered_at INT8 NOT NULL 41 ); 42 COMMENT ON TABLE tx_in IS 'Incoming transactions'; 43 44 CREATE INDEX tx_in_balance_account ON tx_in (balance_id); 45 46 CREATE TYPE incoming_type AS ENUM 47 ('reserve' ,'kyc', 'map'); 48 COMMENT ON TYPE incoming_type IS 'Types of incoming talerable transactions'; 49 50 CREATE TABLE taler_in ( 51 tx_in_id INT8 PRIMARY KEY REFERENCES tx_in(tx_in_id) ON DELETE CASCADE, 52 type incoming_type NOT NULL, 53 metadata BYTEA NOT NULL CHECK (LENGTH(metadata)=32), 54 authorization_pub BYTEA CHECK (LENGTH(authorization_pub)=32), 55 authorization_sig BYTEA CHECK (LENGTH(authorization_sig)=64) 56 ); 57 COMMENT ON TABLE taler_in IS 'Incoming talerable transactions'; 58 59 CREATE UNIQUE INDEX taler_in_unique_reserve_pub ON taler_in (metadata) WHERE type = 'reserve'; 60 61 CREATE TABLE prepared_in ( 62 type incoming_type NOT NULL, 63 account_pub BYTEA NOT NULL CHECK (LENGTH(account_pub)=32), 64 authorization_pub BYTEA UNIQUE NOT NULL CHECK (LENGTH(authorization_pub)=32), 65 authorization_sig BYTEA NOT NULL CHECK (LENGTH(authorization_sig)=64), 66 recurrent BOOLEAN NOT NULL, 67 registered_at INT8 NOT NULL, 68 tx_in_id INT8 UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE CASCADE 69 ); 70 COMMENT ON TABLE prepared_in IS 'Prepared incoming transaction'; 71 CREATE UNIQUE INDEX prepared_in_unique_reserve_pub 72 ON prepared_in (account_pub) WHERE type = 'reserve'; 73 74 CREATE TABLE pending_recurrent_in( 75 tx_in_id INT8 NOT NULL UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE CASCADE, 76 authorization_pub BYTEA NOT NULL REFERENCES prepared_in(authorization_pub) 77 ); 78 CREATE INDEX pending_recurrent_inc_auth_pub 79 ON pending_recurrent_in (authorization_pub); 80 COMMENT ON TABLE pending_recurrent_in IS 'Pending recurrent incoming transaction';