magnet-bank-0001.sql (4201B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 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 SELECT _v.register_patch('magnet-bank-0001', NULL, NULL); 17 18 CREATE SCHEMA magnet_bank; 19 SET search_path TO magnet_bank; 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 tx_in( 25 tx_in_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 26 magnet_code INT8 UNIQUE, 27 amount taler_amount NOT NULL, 28 subject TEXT NOT NULL, 29 debit_account TEXT NOT NULL, 30 debit_name TEXT NOT NULL, 31 valued_at INT8 NOT NULL, 32 registered_at INT8 NOT NULL 33 ); 34 COMMENT ON TABLE tx_in IS 'Incoming transactions'; 35 36 CREATE TABLE tx_out( 37 tx_out_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 38 magnet_code INT8 UNIQUE, 39 amount taler_amount NOT NULL, 40 subject TEXT NOT NULL, 41 credit_account TEXT NOT NULL, 42 credit_name TEXT NOT NULL, 43 valued_at INT8 NOT NULL, 44 registered_at INT8 NOT NULL 45 ); 46 COMMENT ON TABLE tx_in IS 'Outgoing transactions'; 47 48 CREATE TYPE incoming_type AS ENUM 49 ('reserve' ,'kyc', 'wad'); 50 COMMENT ON TYPE incoming_type IS 'Types of incoming talerable transactions'; 51 52 CREATE TABLE taler_in( 53 tx_in_id INT8 PRIMARY KEY REFERENCES tx_in(tx_in_id) ON DELETE CASCADE, 54 type incoming_type NOT NULL, 55 metadata BYTEA NOT NULL, 56 origin_exchange_url TEXT, 57 CONSTRAINT polymorphism CHECK( 58 CASE type 59 WHEN 'wad' THEN LENGTH(metadata)=24 AND origin_exchange_url IS NOT NULL 60 ELSE LENGTH(metadata)=32 AND origin_exchange_url IS NULL 61 END 62 ) 63 ); 64 COMMENT ON TABLE tx_in IS 'Incoming talerable transactions'; 65 66 CREATE UNIQUE INDEX taler_in_unique_reserve_pub ON taler_in (metadata) WHERE type = 'reserve'; 67 68 CREATE TABLE taler_out( 69 tx_out_id INT8 PRIMARY KEY REFERENCES tx_out(tx_out_id) ON DELETE CASCADE, 70 wtid BYTEA NOT NULL UNIQUE CHECK (LENGTH(wtid)=32), 71 exchange_base_url TEXT NOT NULL 72 ); 73 COMMENT ON TABLE tx_in IS 'Outgoing talerable transactions'; 74 75 CREATE TYPE transfer_status AS ENUM( 76 'pending', 77 'transient_failure', 78 'permanent_failure', 79 'success', 80 'late_failure' 81 ); 82 COMMENT ON TYPE transfer_status IS 'Status of an initiated outgoing transaction'; 83 84 CREATE TABLE initiated( 85 initiated_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 86 amount taler_amount NOT NULL, 87 subject TEXT NOT NULL, 88 credit_account TEXT NOT NULL, 89 credit_name TEXT NOT NULL, 90 status transfer_status NOT NULL DEFAULT 'pending', 91 status_msg TEXT, 92 magnet_code INT8 UNIQUE, 93 last_submitted INT8, 94 submission_counter INT2 NOT NULL DEFAULT 0, 95 tx_out_id INT8 UNIQUE REFERENCES tx_out(tx_out_id) ON DELETE CASCADE, 96 initiated_at INT8 NOT NULL 97 ); 98 COMMENT ON TABLE tx_in IS 'Initiated outgoing transactions'; 99 100 CREATE TABLE transfer( 101 initiated_id INT8 PRIMARY KEY REFERENCES initiated(initiated_id) ON DELETE CASCADE, 102 request_uid BYTEA UNIQUE NOT NULL CHECK (LENGTH(request_uid)=64), 103 wtid BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid)=32), 104 exchange_base_url TEXT NOT NULL 105 ); 106 COMMENT ON TABLE transfer IS 'Wire Gateway transfers'; 107 108 CREATE TABLE bounced( 109 tx_in_id INT8 NOT NULL UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE CASCADE, 110 initiated_id INT8 NOT NULL UNIQUE REFERENCES initiated(initiated_id) ON DELETE CASCADE, 111 reason TEXT NOT NULL 112 ); 113 COMMENT ON TABLE tx_in IS 'Bounced transactions'; 114 115 CREATE TABLE kv( 116 key TEXT NOT NULL UNIQUE PRIMARY KEY, 117 value JSONB NOT NULL 118 ); 119 COMMENT ON TABLE kv IS 'KV table'; 120 121 CREATE TYPE register_result AS ENUM( 122 'idempotent', 123 'known', 124 'recovered' 125 ); 126 COMMENT ON TYPE register_result IS 'Status of a registered transaction';