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