commit 344d6d55ae60c383f6418bf4a18e0de03c35ed5c
parent 1073d3521ce93657dd0f4cac02926438c5d117a6
Author: MS <ms@taler.net>
Date: Fri, 4 Aug 2023 16:32:25 +0200
Refactoring the Sandbox SQL
Diffstat:
1 file changed, 181 insertions(+), 67 deletions(-)
diff --git a/database-versioning/new/sandbox-0001-refactor.sql b/database-versioning/new/sandbox-0001-refactor.sql
@@ -1,8 +1,17 @@
--- Under discussion:
-
--- amount format
--- timestamp format
--- comment format: '--' vs 'COMMENT ON'
+--
+-- This file is part of TALER
+-- Copyright (C) 2023 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
+-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
+-- A PARTICULAR PURPOSE. See the GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/>
BEGIN;
@@ -11,27 +20,40 @@ SELECT _v.register_patch('sandbox-0001', NULL, NULL);
CREATE SCHEMA sandbox;
SET search_path TO sandbox;
+-- Indicates whether a transaction is incoming or outgoing.
+CREATE TYPE direction_enum
+ AS ENUM ('CRDT', 'DBIT');
+
+CREATE TYPE tan_enum
+ AS ENUM ('sms', 'email', 'file'); -- file is for testing purposes.
+
+CREATE TYPE cashout_status_enum
+ AS ENUM ('pending', 'confirmed'); -- file is for testing purposes.
+
-- start of: demobank config tables
CREATE TABLE IF NOT EXISTS demobank_configs
- (id BIGSERIAL PRIMARY KEY
- ,name TEXT NOT NULL
+ (demobank_config_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,demobank_name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS demobank_config_pairs
- (id BIGSERIAL PRIMARY KEY
+ (config_pair_id BIGINT GENERATED BY DEFAULT AS IDENTITY
,demobank_name TEXT NOT NULL
,config_key TEXT NOT NULL
,config_value TEXT NULL
);
+COMMENT ON COLUMN demobank_config_pairs.demobank_name
+ IS 'Name of the demobank affected by the configuration key-value pair.'
+
-- end of: demobank config tables
-- start of: bank accounts
-CREATE TABLE IF NOT EXISTS demobank_customers
- (id BIGSERIAL PRIMARY KEY
- ,username TEXT NOT NULL
+CREATE TABLE IF NOT EXISTS demobank_customers
+ (demobank_customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,login TEXT NOT NULL
,password_hash TEXT NOT NULL
,name TEXT NULL
,email TEXT NULL
@@ -39,26 +61,60 @@ CREATE TABLE IF NOT EXISTS demobank_customers
,cashout_address TEXT NULL
);
+COMMENT ON COLUMN demobank_customers.cashout_address
+ IS 'IBAN address to collect fiat payments that come from the conversion of regional currency cash-out operations.'
+
CREATE TABLE IF NOT EXISTS bank_accounts
- (id SERIAL PRIMARY KEY
+ (bank_account_id BIGINT GENERATED BY DEFAULT AS IDENTITY
,iban TEXT NOT NULL
,bic TEXT NOT NULL -- NOTE: This had a default of 'SANDBOXX', now Kotlin must keep it.
- ,label TEXT NOT NULL UNIQUE
- ,owner TEXT NOT NULL
+ ,bank_account_label TEXT NOT NULL UNIQUE
+ ,owning_login BIGINT NOT NULL
+ REFERENCES demobank_customers(demobank_customer_id)
,is_public BOOLEAN DEFAULT false NOT NULL
- ,demo_bank FIXME_TYPE REFERENCES demobank_configs(id) ON DELETE RESTRICT ON UPDATE RESTRICT
- ,last_transaction FIXME_TYPE NULL REFERENCES bank_account_transactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT -- FIXME: under discussion on MM, might be removed.
- ,last_fiat_submission FIXME_TYPE NULL REFERENCES bank_account_transactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,demo_bank BIGINT REFERENCES demobank_configs(demobank_config_id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,last_transaction BIGINT NULL
+ REFERENCES bank_account_transactions(bank_account_transaction_id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,last_fiat_submission BIGINT NULL REFERENCES bank_account_transactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
,last_fiat_fetch TEXT DEFAULT '0' NOT NULL
- ,balance TEXT DEFAULT '0'
+ ,balance taler_amount DEFAULT '0'
);
+COMMENT ON TABLE bank_accounts
+ IS 'In Sandbox, usernames (AKA logins) are different entities
+respect to bank accounts (in contrast to what the Python bank
+did). The idea was to provide multiple bank accounts to one
+user. Nonetheless, for simplicity the current version enforces
+one bank account for one user, and additionally the bank
+account label matches always the login.'
+
+COMMENT ON TABLE bank_accounts.last_fiat_fetch
+ IS 'Keeps the ID of the last fiat payment that was learnt
+from Nexus. For that reason, this ID is stored verbatim as
+it was returned by Nexus. It helps to build queries to Nexus
+that needs this value as a parameter.'
+
+COMMENT ON COLUMN bank_accounts.is_public
+ IS 'Indicates whether the bank account history
+can be publicly shared'
+
+COMMENT ON COLUMN bank_accounts.label
+ IS 'Label of the bank account'
+
+COMMENT ON COLUMN bank_accounts.owning_login
+ IS 'Login that owns the bank account'
+
+COMMENT ON COLUMN bank_accounts.last_transaction
+ IS 'Pointer to the last transaction that involves
+this bank account. Used to construct histories,
+because they start from the last and go backwards'
+
-- end of: bank accounts
-- start of: money transactions
CREATE TABLE IF NOT EXISTS bank_account_transactions
- (id BIGSERIAL PRIMARY KEY
+ (bank_transaction_id BIGINT GENERATED BY DEFAULT AS IDENTITY
,creditor_iban TEXT NOT NULL
,creditor_bic TEXT NULL
,creditor_name TEXT NOT NULL
@@ -66,31 +122,50 @@ CREATE TABLE IF NOT EXISTS bank_account_transactions
,debtor_bic TEXT NULL
,debtor_name TEXT NOT NULL
,subject TEXT NOT NULL
- ,amount TEXT NOT NULL
+ ,amount taler_amount NOT NULL
,currency TEXT NOT NULL
- ,date BIGINT NOT NULL
+ ,transaction_date BIGINT NOT NULL
,account_servicer_reference TEXT NOT NULL
,pmt_inf_id TEXT NULL
,end_to_end_id TEXT NULL
- ,direction TEXT NOT NULL
- ,account INT NOT NULL REFERENCES bank_accounts(id) ON DELETE CASCADE ON UPDATE RESTRICT
- ,demobank FIXME_TYPE NOT NULL REFERENCES demobank_configs(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,direction direction_enum NOT NULL
+ ,bank_account BIGINT NOT NULL REFERENCES bank_accounts(id) ON DELETE CASCADE ON UPDATE RESTRICT
+ ,demobank BIGINT NOT NULL REFERENCES demobank_configs(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
+COMMENT ON bank_account_transactions.direction
+ IS 'Indicates whether the transaction is incoming
+or outgoing for the bank account associated with this
+transaction.'
+COMMENT ON bank_account_transactions.pmt_inf_id
+ IS 'ISO20022 specific'
+COMMENT ON bank_account_transactions.end_to_end_id
+ IS 'ISO20022 specific'
+COMMENT ON bank_account_transactions.demobank
+ IS 'The demobank hosting the transaction and its bank account.'
+
-- end of: money transactions
-- start of: cashout management
CREATE TABLE IF NOT EXISTS cashout_submissions
- (id BIGSERIAL PRIMARY KEY
- ,local_transaction FIXME_TYPE NOT NULL UNIQUE REFERENCES bank_account_transactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ (cashout_submission_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,local_transaction BIGINT NOT NULL UNIQUE
+ REFERENCES bank_account_transactions(bank_transaction_id) ON DELETE RESTRICT ON UPDATE RESTRICT
,maybe_nexus_response TEXT NULL
,submission_time BIGINT NULL
);
+COMMENT ON TABLE cashout_submissions
+ IS 'Tracks payment requests made from Sandbox to Nexus to trigger fiat transactions that finalize cash-outs.'
+COMMENT ON COLUMN cashout_submissions.local_transaction
+ IS 'Points to the bank transaction from the customer to the admin bank account that triggered the cash-out submission'
+COMMENT ON cashout_submissions.maybe_nexus_response
+ IS 'Keeps the Nexus response to the payment submission.'
+
CREATE TABLE IF NOT EXISTS cashout_operations
- (id BIGSERIAL PRIMARY KEY
- ,uuid uuid NOT NULL
+ (cashout_operation_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,cashout_uuid uuid NOT NULL
,amount_debit TEXT NOT NULL
,amount_credit TEXT NOT NULL
,buy_at_ratio TEXT NOT NULL
@@ -99,52 +174,63 @@ CREATE TABLE IF NOT EXISTS cashout_operations
,sell_out_fee TEXT NOT NULL
,subject TEXT NOT NULL
,creation_time BIGINT NOT NULL
- ,confirmation_time BIGINT NULL
- ,tan_channel INT NOT NULL
- ,account TEXT NOT NULL
+ ,tan_confirmation_time BIGINT NULL
+ ,tan_channel tan_enum NOT NULL
+ ,bank_account BIGINT DEFAULT(NULL)
+ REFERENCES bank_accounts(bank_account_id) ON DELETE RESTRICT ON UPDATE RESTRICT
,cashout_address TEXT NOT NULL
- ,tan TEXT NOT NULL
- ,status INT DEFAULT 1 NOT NULL
+ ,tan_salt TEXT NOT NULL
+ ,cashout_status cashout_status_enum DEFAULT 'pending' NOT NULL
);
+COMMENT ON COLUMN cashout_operations.tan_confirmation_time
+ IS 'Timestamp when the customer confirmed the cash-out operation via TAN'
+COMMENT ON COLUMN cashout_operations.cashout_address
+ IS 'IBAN that ultimately gets the fiat payment'
+COMMENT ON COLUMN cashout_operations.tan_salt
+ IS 'text that the customer must send to confirm the cash-out operation'
+
-- end of: cashout management
-- start of: EBICS management
CREATE TABLE IF NOT EXISTS ebics_hosts
- (id SERIAL PRIMARY KEY
- ,host_id TEXT NOT NULL
+ (ebics_host_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,ebics_host_name TEXT NOT NULL
,ebics_version TEXT NOT NULL
- ,signature_private_key bytea NOT NULL
- ,encryption_private_key bytea NOT NULL
- ,authentication_private_key bytea NOT NULL
+ ,encryption_private_key BYTEA NOT NULL
+ ,signature_private_key BYTEA NOT NULL
);
CREATE TABLE IF NOT EXISTS ebics_subscribers
- (id SERIAL PRIMARY KEY
- ,user_id TEXT NOT NULL
- ,partner_id TEXT NOT NULL
- ,system_id TEXT NULL
- ,host_id TEXT NOT NULL
+ (ebics_subscriber_id SERIAL PRIMARY KEY
+ ,ebics_user_id TEXT NOT NULL
+ ,ebics_partner_id TEXT NOT NULL
+ ,ebics_system_id TEXT NULL
+ ,ebics_host_id BIGINT NOT NULL REFERENCES ebics_hosts(ebics_host_id)
,signature_key INT NULL REFERENCES ebics_subscriber_public_keys(id) ON DELETE RESTRICT ON UPDATE RESTRICT
,encryption_key INT NULL REFERENCES ebics_subscriber_public_keys(id) ON DELETE RESTRICT ON UPDATE RESTRICT
- ,authorization_key INT NULL REFERENCES ebics_subscriber_public_keys(id) ON DELETE RESTRICT ON UPDATE RESTRICT
- ,next_order_id INT NOT NULL
- ,state INT NOT NULL
- ,bank_account INT NULL REFERENCES bank_accounts(id) ON DELETE CASCADE ON UPDATE RESTRICT
+ ,next_order_id INT NOT NULL -- set as a constant without significantly participating in the protocol. Remove this column?
+ ,subscriber_state subscriber_state_enum DEFAULT 'new' NOT NULL
+ ,bank_account INT NULL
+ REFERENCES bank_accounts(bank_account_id) ON DELETE CASCADE ON UPDATE RESTRICT
);
+COMMENT ON COLUMN ebics_subscribers.bank_account
+ IS 'Bank account associated with this EBICS subscriber.'
+COMMENT ON COLUMN ebics_subscribers.subscriber_state
+ IS 'Tracks the state changes of one subscriber.' -- Really needed?
CREATE TABLE IF NOT EXISTS ebics_subscriber_public_keys
- (id SERIAL PRIMARY KEY
+ (subscriber_key_id SERIAL PRIMARY KEY
,rsa_public_key bytea NOT NULL
- ,state INT NOT NULL
+ ,subscriber_key_state subscriber_key_state_enum NOT NULL
);
CREATE TABLE IF NOT EXISTS ebics_download_transactions
(transaction_id TEXT NOT NULL
,order_type TEXT NOT NULL
- ,host INT NOT NULL REFERENCES ebics_hosts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
- ,subscriber INT NOT NULL REFERENCES ebics_subscribers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,ebics_host INT NOT NULL REFERENCES ebics_hosts(ebics_host_id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,ebics_subscriber INT NOT NULL REFERENCES ebics_subscribers(ebics_subscriber_id) ON DELETE RESTRICT ON UPDATE RESTRICT
,encoded_response TEXT NOT NULL
,transaction_key_enc bytea NOT NULL
,num_segments INT NOT NULL
@@ -152,42 +238,63 @@ CREATE TABLE IF NOT EXISTS ebics_download_transactions
,receipt_received BOOLEAN NOT NULL
);
+COMMENT ON TABLE ebics_download_transactions
+ IS 'Tracks the evolution of one EBICS transaction'
+COMMENT ON COLUMN ebics_download_transactions.ebics_host
+ IS 'EBICS host that governs this transaction' -- exists for a multi-host scenario.
+
CREATE TABLE IF NOT EXISTS ebics_upload_transactions
(transaction_id TEXT NOT NULL
,order_type TEXT NOT NULL
,order_id TEXT NOT NULL
- ,host INT NOT NULL REFERENCES ebics_hosts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
- ,subscriber INT NOT NULL REFERENCES ebics_subscribers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,ebics_host BIGINT NOT NULL
+ REFERENCES ebics_hosts(ebics_host_id)
+ ON DELETE RESTRICT
+ ON UPDATE RESTRICT
+ ,ebics_subscriber BIGINT NOT NULL
+ REFERENCES ebics_subscribers(ebics_subscribers_id)
+ ON DELETE RESTRICT
+ ON UPDATE RESTRICT
,num_segments INT NOT NULL
,last_seen_segment INT NOT NULL
- ,transaction_key_enc bytea NOT NULL
+ ,transaction_key_enc BYTEA NOT NULL
);
CREATE TABLE IF NOT EXISTS ebics_upload_transaction_chunks
- (transaction_id TEXT NOT NULL
- ,chunk_index INT NOT NULL
- ,chunk_content bytea NOT NULL
+ (ebics_transaction_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,upload_chunk_index INT NOT NULL
+ ,upload_chunk_content BYTEA NOT NULL
);
CREATE TABLE IF NOT EXISTS ebics_order_signatures
- (id SERIAL PRIMARY KEY
+ (order_signature_id SERIAL PRIMARY KEY
,order_id TEXT NOT NULL
,order_type TEXT NOT NULL
,partner_id TEXT NOT NULL
,user_id TEXT NOT NULL
,signature_algorithm TEXT NOT NULL
- ,signature_value bytea NOT NULL
+ ,signature_value BYTEA NOT NULL
);
+COMMENT ON TABLE ebics_order_signatures
+ IS 'Keeps signature data collected from the subscribers.'
+
-- end of: EBICS management
-- start of: accounts activity report
CREATE TABLE IF NOT EXISTS bank_account_fresh_transactions
- (id BIGSERIAL PRIMARY KEY
- ,transaction FIXME_TYPE NOT NULL REFERENCES bank_account_transactions(id) ON DELETE CASCADE ON UPDATE RESTRICT
+ (fresh_transaction_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,bank_account_transaction BIGINT NOT NULL
+ REFERENCES bank_account_transactions(bank_transaction_id)
+ ON DELETE CASCADE
+ ON UPDATE RESTRICT
);
+COMMENT ON TABLE bank_account_fresh_transactions
+ IS 'Keeps transactions that were never included in a EBICS report'
+
+-- Really keep this table? It tracks the EBICS reports.
CREATE TABLE IF NOT EXISTS bank_account_reports
(id SERIAL PRIMARY KEY
,report_id TEXT NOT NULL
@@ -196,6 +303,8 @@ CREATE TABLE IF NOT EXISTS bank_account_reports
,bank_account INT NOT NULL REFERENCES bank_accounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
+-- Really keep this table? It tracks the EBICS statements
+-- mostly because they are supposed never to change. Not used
CREATE TABLE IF NOT EXISTS bank_account_statements
(id SERIAL PRIMARY KEY
,statement_id TEXT NOT NULL
@@ -204,23 +313,28 @@ CREATE TABLE IF NOT EXISTS bank_account_statements
,bank_account INT NOT NULL REFERENCES bank_accounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
,balance_clbd TEXT NOT NULL
);
-
-- end of: accounts activity report
-- start of: Taler integration
CREATE TABLE IF NOT EXISTS taler_withdrawals
- (id BIGSERIAL PRIMARY KEY
- ,wopid uuid NOT NULL
- ,amount TEXT NOT NULL
+ (taler_withdrawal_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+ ,withdrawal_id uuid NOT NULL
+ ,amount taler_amount NOT NULL
,selection_done BOOLEAN DEFAULT false NOT NULL
,aborted BOOLEAN DEFAULT false NOT NULL
,confirmation_done BOOLEAN DEFAULT false NOT NULL
,reserve_pub TEXT NULL
,selected_exchange_payto TEXT NULL
- ,wallet_bank_account INT NOT NULL REFERENCES bank_accounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ ,wallet_bank_account BIGINT NOT NULL
+ REFERENCES bank_accounts(id)
+ ON DELETE RESTRICT
+ ON UPDATE RESTRICT
);
+COMMENT ON COLUMN taler_withdrawals.selection_done
+ IS 'Signals whether the wallet specified the exchange and gave the reserve public key'
+COMMENT ON COLUMN taler_withdrawals.confirmation_done
+ IS 'Signals whether the payment to the exchange took place'
-- end of: Taler integration
-
COMMIT;
