magnet-bank-0001.sql (4214B)
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_out 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 taler_in IS 'Incoming talerable transactions'; 65 CREATE UNIQUE INDEX taler_in_unique_reserve_pub ON taler_in (metadata) WHERE type = 'reserve'; 66 67 CREATE TABLE taler_out( 68 tx_out_id INT8 PRIMARY KEY REFERENCES tx_out(tx_out_id) ON DELETE CASCADE, 69 wtid BYTEA NOT NULL UNIQUE CHECK (LENGTH(wtid)=32), 70 exchange_base_url TEXT NOT NULL 71 ); 72 COMMENT ON TABLE taler_out IS 'Outgoing talerable transactions'; 73 74 CREATE TYPE transfer_status AS ENUM( 75 'pending', 76 'transient_failure', 77 'permanent_failure', 78 'success', 79 'late_failure' 80 ); 81 COMMENT ON TYPE transfer_status IS 'Status of an initiated outgoing transaction'; 82 83 CREATE TABLE initiated( 84 initiated_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 85 amount taler_amount NOT NULL, 86 subject TEXT NOT NULL, 87 credit_account TEXT NOT NULL, 88 credit_name TEXT NOT NULL, 89 status transfer_status NOT NULL DEFAULT 'pending', 90 status_msg TEXT, 91 magnet_code INT8 UNIQUE, 92 last_submitted INT8, 93 submission_counter INT2 NOT NULL DEFAULT 0, 94 tx_out_id INT8 UNIQUE REFERENCES tx_out(tx_out_id) ON DELETE CASCADE, 95 initiated_at INT8 NOT NULL 96 ); 97 COMMENT ON TABLE initiated IS 'Initiated outgoing transactions'; 98 99 CREATE TABLE transfer( 100 initiated_id INT8 PRIMARY KEY REFERENCES initiated(initiated_id) ON DELETE CASCADE, 101 request_uid BYTEA UNIQUE NOT NULL CHECK (LENGTH(request_uid)=64), 102 wtid BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid)=32), 103 exchange_base_url TEXT NOT NULL 104 ); 105 COMMENT ON TABLE transfer IS 'Wire Gateway transfers'; 106 107 CREATE TABLE bounced( 108 tx_in_id INT8 NOT NULL UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE CASCADE, 109 initiated_id INT8 NOT NULL UNIQUE REFERENCES initiated(initiated_id) ON DELETE CASCADE, 110 reason TEXT NOT NULL 111 ); 112 COMMENT ON TABLE bounced IS 'Bounced transactions'; 113 114 CREATE TABLE kv( 115 key TEXT NOT NULL UNIQUE PRIMARY KEY, 116 value JSONB NOT NULL 117 ); 118 COMMENT ON TABLE kv IS 'KV table'; 119 120 CREATE TYPE register_result AS ENUM( 121 'idempotent', 122 'known', 123 'recovered' 124 ); 125 COMMENT ON TYPE register_result IS 'Status of a registered transaction';