libeufin-bank-0001.sql (11588B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2023 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 BEGIN; 17 18 SELECT _v.register_patch('libeufin-bank-0001', NULL, NULL); 19 20 CREATE SCHEMA libeufin_bank; 21 SET search_path TO libeufin_bank; 22 23 CREATE TYPE taler_amount 24 AS (val INT8 ,frac INT4); 25 COMMENT ON TYPE taler_amount 26 IS 'Stores an amount, fraction is in units of 1/100000000 of the base value'; 27 28 -- Indicates whether a transaction is incoming or outgoing. 29 CREATE TYPE direction_enum 30 AS ENUM ('credit', 'debit'); 31 32 CREATE TYPE token_scope_enum 33 AS ENUM ('readonly', 'readwrite'); 34 35 CREATE TYPE tan_enum 36 AS ENUM ('sms', 'email'); 37 38 CREATE TYPE cashout_status_enum 39 AS ENUM ('pending', 'confirmed'); 40 41 CREATE TYPE subscriber_key_state_enum 42 AS ENUM ('new', 'invalid', 'confirmed'); 43 44 CREATE TYPE subscriber_state_enum 45 AS ENUM ('new', 'confirmed'); 46 47 CREATE TYPE stat_timeframe_enum 48 AS ENUM ('hour', 'day', 'month', 'year'); 49 50 CREATE TYPE rounding_mode 51 AS ENUM ('zero', 'up', 'nearest'); 52 53 54 -- FIXME: comments on types (see exchange for example)! 55 56 -- start of: bank accounts 57 58 CREATE TABLE customers 59 (customer_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 60 ,login TEXT NOT NULL UNIQUE 61 ,password_hash TEXT NOT NULL 62 ,name TEXT 63 ,email TEXT 64 ,phone TEXT 65 ,cashout_payto TEXT 66 ); 67 COMMENT ON COLUMN customers.cashout_payto 68 IS 'RFC 8905 payto URI to collect fiat payments that come from the conversion of regional currency cash-out operations.'; 69 COMMENT ON COLUMN customers.name 70 IS 'Full name of the customer.'; 71 72 CREATE TABLE bank_accounts 73 (bank_account_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 74 ,internal_payto_uri TEXT NOT NULL UNIQUE 75 ,owning_customer_id INT8 NOT NULL UNIQUE -- UNIQUE enforces 1-1 map with customers 76 REFERENCES customers(customer_id) 77 ON DELETE CASCADE 78 ,is_public BOOLEAN DEFAULT FALSE NOT NULL -- privacy by default 79 ,is_taler_exchange BOOLEAN DEFAULT FALSE NOT NULL 80 ,balance taler_amount DEFAULT (0, 0) 81 ,max_debt taler_amount DEFAULT (0, 0) 82 ,has_debt BOOLEAN NOT NULL DEFAULT FALSE 83 ); 84 COMMENT ON TABLE bank_accounts 85 IS 'In Sandbox, usernames (AKA logins) are different entities 86 respect to bank accounts (in contrast to what the Python bank 87 did). The idea was to provide multiple bank accounts to one 88 user. Nonetheless, for simplicity the current version enforces 89 one bank account for one user, and additionally the bank 90 account label matches always the login.'; 91 COMMENT ON COLUMN bank_accounts.has_debt 92 IS 'When true, the balance is negative'; 93 COMMENT ON COLUMN bank_accounts.is_public 94 IS 'Indicates whether the bank account history 95 can be publicly shared'; 96 COMMENT ON COLUMN bank_accounts.owning_customer_id 97 IS 'Login that owns the bank account'; 98 99 CREATE TABLE bearer_tokens 100 (bearer_token_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 101 ,content BYTEA NOT NULL UNIQUE CHECK (LENGTH(content)=32) 102 ,creation_time INT8 103 ,expiration_time INT8 104 ,scope token_scope_enum 105 ,is_refreshable BOOLEAN 106 ,bank_customer INT8 NOT NULL 107 REFERENCES customers(customer_id) 108 ON DELETE CASCADE 109 ); 110 COMMENT ON TABLE bearer_tokens 111 IS 'Login tokens associated with one bank customer.'; 112 COMMENT ON COLUMN bearer_tokens.bank_customer 113 IS 'The customer that directly created this token, or the customer that' 114 ' created the very first token that originated all the refreshes until' 115 ' this token was created.'; 116 117 CREATE TABLE iban_history 118 (iban TEXT PRIMARY KEY 119 ,creation_time INT8 NOT NULL 120 ); 121 COMMENT ON TABLE iban_history IS 'Track all generated iban, some might be unused.'; 122 123 -- end of: bank accounts 124 125 -- start of: money transactions 126 127 CREATE TABLE bank_account_transactions 128 (bank_transaction_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 129 ,creditor_payto_uri TEXT NOT NULL 130 ,creditor_name TEXT NOT NULL 131 ,debtor_payto_uri TEXT NOT NULL 132 ,debtor_name TEXT NOT NULL 133 ,subject TEXT NOT NULL 134 ,amount taler_amount NOT NULL 135 ,transaction_date INT8 NOT NULL 136 ,account_servicer_reference TEXT 137 ,payment_information_id TEXT 138 ,end_to_end_id TEXT 139 ,direction direction_enum NOT NULL 140 ,bank_account_id INT8 NOT NULL REFERENCES bank_accounts(bank_account_id) 141 ); 142 143 COMMENT ON COLUMN bank_account_transactions.direction 144 IS 'Indicates whether the transaction is incoming or outgoing for the bank account associated with this transaction.'; 145 COMMENT ON COLUMN bank_account_transactions.payment_information_id 146 IS 'ISO20022 specific'; 147 COMMENT ON COLUMN bank_account_transactions.end_to_end_id 148 IS 'ISO20022 specific'; 149 COMMENT ON COLUMN bank_account_transactions.bank_account_id 150 IS 'The bank account affected by this transaction.'; 151 152 -- end of: money transactions 153 154 -- start of: TAN challenge 155 CREATE TABLE challenges 156 (challenge_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE, 157 code TEXT NOT NULL, 158 creation_date INT8 NOT NULL, 159 expiration_date INT8 NOT NULL, 160 retransmission_date INT8 NOT NULL DEFAULT 0, 161 retry_counter INT4 NOT NULL, 162 confirmation_date INT8 DEFAULT NULL); 163 COMMENT ON TABLE challenges 164 IS 'Stores a code which is checked for the authentication by SMS, E-Mail..'; 165 COMMENT ON COLUMN challenges.code 166 IS 'The pin code which is sent to the user and verified'; 167 COMMENT ON COLUMN challenges.creation_date 168 IS 'Creation date of the code'; 169 COMMENT ON COLUMN challenges.retransmission_date 170 IS 'When did we last transmit the challenge to the user'; 171 COMMENT ON COLUMN challenges.expiration_date 172 IS 'When will the code expire'; 173 COMMENT ON COLUMN challenges.retry_counter 174 IS 'How many tries are left for this code must be > 0'; 175 COMMENT ON COLUMN challenges.confirmation_date 176 IS 'When was this challenge successfully verified, NULL if pending'; 177 178 -- end of: TAN challenge 179 180 -- start of: cashout management 181 182 CREATE TABLE cashout_operations 183 (cashout_id INT8 GENERATED BY DEFAULT AS IDENTITY UNIQUE 184 ,request_uid BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(request_uid)=32) 185 ,amount_debit taler_amount NOT NULL 186 ,amount_credit taler_amount NOT NULL 187 ,subject TEXT NOT NULL 188 ,creation_time INT8 NOT NULL 189 ,bank_account INT8 NOT NULL 190 REFERENCES bank_accounts(bank_account_id) 191 ,challenge INT8 NOT NULL UNIQUE 192 REFERENCES challenges(challenge_id) 193 ON DELETE SET NULL 194 ,tan_channel TEXT NULL DEFAULT NULL 195 ,tan_info TEXT NULL DEFAULT NULL 196 ,aborted BOOLEAN NOT NULL DEFAULT FALSE 197 ,local_transaction INT8 UNIQUE DEFAULT NULL 198 REFERENCES bank_account_transactions(bank_transaction_id) 199 ON DELETE CASCADE 200 ); 201 COMMENT ON COLUMN cashout_operations.bank_account IS 'Bank amount to debit during confirmation'; 202 COMMENT ON COLUMN cashout_operations.challenge IS 'TAN challenge used to confirm the operation'; 203 COMMENT ON COLUMN cashout_operations.local_transaction IS 'Transaction generated during confirmation'; 204 COMMENT ON COLUMN cashout_operations.tan_channel IS 'Channel of the last successful transmission of the TAN challenge'; 205 COMMENT ON COLUMN cashout_operations.tan_info IS 'Info of the last successful transmission of the TAN challenge'; 206 207 -- end of: cashout management 208 209 -- start of: Taler integration 210 CREATE TABLE taler_exchange_outgoing 211 (exchange_outgoing_id INT8 GENERATED BY DEFAULT AS IDENTITY 212 ,request_uid BYTEA UNIQUE CHECK (LENGTH(request_uid)=64) 213 ,wtid BYTEA NOT NULL UNIQUE CHECK (LENGTH(wtid)=32) 214 ,exchange_base_url TEXT NOT NULL 215 ,bank_transaction INT8 UNIQUE NOT NULL 216 REFERENCES bank_account_transactions(bank_transaction_id) 217 ON DELETE CASCADE 218 ,creditor_account_id INT8 NOT NULL 219 REFERENCES bank_accounts(bank_account_id) 220 ); 221 222 CREATE TABLE taler_exchange_incoming 223 (exchange_incoming_id INT8 GENERATED BY DEFAULT AS IDENTITY 224 ,reserve_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_pub)=32) 225 ,bank_transaction INT8 UNIQUE NOT NULL 226 REFERENCES bank_account_transactions(bank_transaction_id) 227 ON DELETE CASCADE 228 ); 229 230 CREATE TABLE taler_withdrawal_operations 231 (withdrawal_id INT8 GENERATED BY DEFAULT AS IDENTITY 232 ,withdrawal_uuid uuid NOT NULL UNIQUE 233 ,amount taler_amount NOT NULL 234 ,selection_done BOOLEAN DEFAULT FALSE NOT NULL 235 ,aborted BOOLEAN DEFAULT FALSE NOT NULL 236 ,confirmation_done BOOLEAN DEFAULT FALSE NOT NULL 237 ,reserve_pub BYTEA UNIQUE CHECK (LENGTH(reserve_pub)=32) 238 ,subject TEXT 239 ,selected_exchange_payto TEXT 240 ,wallet_bank_account INT8 NOT NULL 241 REFERENCES bank_accounts(bank_account_id) 242 ON DELETE CASCADE 243 ); 244 COMMENT ON COLUMN taler_withdrawal_operations.selection_done 245 IS 'Signals whether the wallet specified the exchange and gave the reserve public key'; 246 COMMENT ON COLUMN taler_withdrawal_operations.confirmation_done 247 IS 'Signals whether the payment to the exchange took place'; 248 249 -- end of: Taler integration 250 251 -- start of: Statistics 252 CREATE TABLE bank_stats ( 253 timeframe stat_timeframe_enum NOT NULL 254 ,start_time timestamp NOT NULL 255 ,taler_in_count INT8 NOT NULL DEFAULT 0 256 ,taler_in_volume taler_amount NOT NULL DEFAULT (0, 0) 257 ,taler_out_count INT8 NOT NULL DEFAULT 0 258 ,taler_out_volume taler_amount NOT NULL DEFAULT (0, 0) 259 ,cashin_count INT8 NOT NULL DEFAULT 0 260 ,cashin_regional_volume taler_amount NOT NULL DEFAULT (0, 0) 261 ,cashin_fiat_volume taler_amount NOT NULL DEFAULT (0, 0) 262 ,cashout_count INT8 NOT NULL DEFAULT 0 263 ,cashout_regional_volume taler_amount NOT NULL DEFAULT (0, 0) 264 ,cashout_fiat_volume taler_amount NOT NULL DEFAULT (0, 0) 265 ,PRIMARY KEY (start_time, timeframe) 266 ); 267 COMMENT ON TABLE bank_stats 268 IS 'Stores statistics about the bank usage.'; 269 COMMENT ON COLUMN bank_stats.timeframe 270 IS 'particular timeframe that this row accounts for'; 271 COMMENT ON COLUMN bank_stats.start_time 272 IS 'timestamp of the start of the timeframe that this row accounts for, truncated according to the precision of the timeframe'; 273 COMMENT ON COLUMN bank_stats.taler_out_count 274 IS 'how many internal payments were made by a Taler exchange'; 275 COMMENT ON COLUMN bank_stats.taler_out_volume 276 IS 'how much internal currency was paid by a Taler exchange'; 277 COMMENT ON COLUMN bank_stats.taler_in_count 278 IS 'how many internal payments were made to a Taler exchange'; 279 COMMENT ON COLUMN bank_stats.taler_in_volume 280 IS 'how much internal currency was paid to a Taler exchange'; 281 COMMENT ON COLUMN bank_stats.cashin_count 282 IS 'how many cashin operations took place in the timeframe'; 283 COMMENT ON COLUMN bank_stats.cashin_regional_volume 284 IS 'how much regional currency was cashed in in the timeframe'; 285 COMMENT ON COLUMN bank_stats.cashin_fiat_volume 286 IS 'how much fiat currency was cashed in in the timeframe'; 287 COMMENT ON COLUMN bank_stats.cashout_count 288 IS 'how many cashout operations took place in the timeframe'; 289 COMMENT ON COLUMN bank_stats.cashout_regional_volume 290 IS 'how much regional currency was paid by the bank to customers in the timeframe'; 291 COMMENT ON COLUMN bank_stats.cashout_fiat_volume 292 IS 'how much fiat currency was paid by the bank to customers in the timeframe'; 293 294 -- end of: Statistics 295 296 -- start of: Conversion 297 298 CREATE TABLE config ( 299 key TEXT NOT NULL PRIMARY KEY, 300 value JSONB NOT NULL 301 ); 302 303 -- end of: Conversion 304 305 COMMIT;