depolymerizer-bitcoin-0001.sql (4539B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025-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('depolymerizer-bitcoin-0001', NULL, NULL); 17 18 CREATE SCHEMA depolymerizer_bitcoin; 19 SET search_path TO depolymerizer_bitcoin; 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 state ( 25 name TEXT NOT NULL PRIMARY KEY, 26 value BYTEA NOT NULL 27 ); 28 COMMENT ON TABLE state IS 'Key value state'; 29 30 CREATE TABLE tx_in ( 31 tx_in_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 32 received_at INT8 NOT NULL, 33 amount taler_amount NOT NULL, 34 debit_acc TEXT NOT NULL, 35 txid BYTEA UNIQUE CHECK (LENGTH(txid)=32) 36 ); 37 COMMENT ON TABLE tx_in IS 'Incoming transactions'; 38 39 CREATE TABLE tx_out ( 40 tx_out_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 41 created_at INT8 NOT NULL, 42 amount taler_amount NOT NULL, 43 credit_acc TEXT NOT NULL, 44 txid BYTEA NOT NULL UNIQUE CHECK (LENGTH(txid)=32) 45 ); 46 COMMENT ON TABLE tx_out IS 'Outgoing transactions'; 47 48 CREATE TYPE incoming_type AS ENUM 49 ('reserve' ,'kyc', 'map'); 50 COMMENT ON TYPE incoming_type IS 'Types of incoming talerable transactions'; 51 52 CREATE TABLE taler_in ( 53 taler_in_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 54 tx_in_id INT8 NOT NULL UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE CASCADE, 55 type incoming_type NOT NULL, 56 metadata BYTEA NOT NULL CHECK (LENGTH(metadata)=32), 57 authorization_pub BYTEA CHECK (LENGTH(authorization_pub)=32), 58 authorization_sig BYTEA CHECK (LENGTH(authorization_sig)=64) 59 ); 60 COMMENT ON TABLE tx_in IS 'Incoming talerable transactions'; 61 62 CREATE UNIQUE INDEX taler_in_unique_reserve_pub ON taler_in (metadata) WHERE type = 'reserve'; 63 64 CREATE TABLE taler_out( 65 tx_out_id INT8 PRIMARY KEY REFERENCES tx_out(tx_out_id) ON DELETE CASCADE, 66 wtid BYTEA NOT NULL UNIQUE CHECK (LENGTH(wtid)=32), 67 exchange_base_url TEXT NOT NULL, 68 metadata TEXT 69 ); 70 COMMENT ON TABLE taler_out IS 'Outgoing talerable transactions'; 71 72 CREATE TABLE prepared_in ( 73 type incoming_type NOT NULL, 74 account_pub BYTEA NOT NULL CHECK (LENGTH(account_pub)=32), 75 authorization_pub BYTEA UNIQUE NOT NULL CHECK (LENGTH(authorization_pub)=32), 76 authorization_sig BYTEA NOT NULL CHECK (LENGTH(authorization_sig)=64), 77 recurrent BOOLEAN NOT NULL, 78 registered_at INT8 NOT NULL, 79 tx_in_id INT8 UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE CASCADE 80 ); 81 COMMENT ON TABLE prepared_in IS 'Prepared incoming transaction'; 82 CREATE UNIQUE INDEX prepared_in_unique_reserve_pub 83 ON prepared_in (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 authorization_pub BYTEA NOT NULL REFERENCES prepared_in(authorization_pub) 88 ); 89 CREATE INDEX pending_recurrent_inc_auth_pub 90 ON pending_recurrent_in (authorization_pub); 91 COMMENT ON TABLE pending_recurrent_in IS 'Pending recurrent incoming transaction'; 92 93 CREATE TYPE debit_status AS ENUM( 94 'requested', 95 'ignored', 96 'sent', 97 'confirmed' 98 ); 99 COMMENT ON TYPE debit_status IS 'Status of an outgoing transaction'; 100 101 CREATE TABLE transfer ( 102 transfer_id INT8 PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 103 txid BYTEA UNIQUE CHECK (LENGTH(txid)=32), 104 created_at INT8 NOT NULL, 105 amount taler_amount NOT NULL, 106 wtid BYTEA NOT NULL UNIQUE CHECK (LENGTH(wtid)=32), 107 credit_acc TEXT NOT NULL, 108 credit_name TEXT, 109 exchange_url TEXT NOT NULL, 110 request_uid BYTEA NOT NULL UNIQUE CHECK (LENGTH(request_uid)=64), 111 metadata TEXT, 112 status debit_status NOT NULL, 113 status_msg TEXT 114 ); 115 COMMENT ON TABLE transfer IS 'Wire Gateway transfers'; 116 117 CREATE TABLE bounced ( 118 tx_in_id INT8 NOT NULL UNIQUE REFERENCES tx_in(tx_in_id) ON DELETE SET NULL, 119 txid BYTEA UNIQUE CHECK (LENGTH(txid)=32), 120 reason TEXT NOT NULL, 121 status debit_status NOT NULL, 122 status_msg TEXT 123 ); 124 COMMENT ON TABLE state IS 'Bounced incoming transactions';