[libeufin] branch master updated: Refactoring the Sandbox SQL

[gnunet]

This is an automated email from the git hooks/post-receive script.

ms pushed a commit to branch master
in repository libeufin.

The following commit(s) were added to refs/heads/master by this push:
     new 344d6d55 Refactoring the Sandbox SQL
344d6d55 is described below

commit 344d6d55ae60c383f6418bf4a18e0de03c35ed5c
Author: MS <ms@taler.net>
AuthorDate: Fri Aug 4 16:32:25 2023 +0200

    Refactoring the Sandbox SQL
---
 database-versioning/new/sandbox-0001-refactor.sql | 248 ++++++++++++++++------
 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
index 1faa6dc9..14130098 100644
--- 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;

-- 
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.