[libeufin] branch master updated: schema improvements

[gnunet]

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

dold pushed a commit to branch master
in repository libeufin.

The following commit(s) were added to refs/heads/master by this push:
     new 1ec31c04 schema improvements
1ec31c04 is described below

commit 1ec31c04288533de018972c9edb72180c25ab17f
Author: Florian Dold <florian@dold.me>
AuthorDate: Sat Aug 5 13:22:43 2023 +0200

    schema improvements
---
 database-versioning/new/sandbox-0001-refactor.sql | 276 ++++++++++++----------
 1 file changed, 151 insertions(+), 125 deletions(-)

diff --git a/database-versioning/new/sandbox-0001-refactor.sql 
b/database-versioning/new/sandbox-0001-refactor.sql
index 14130098..4c06c4df 100644
--- a/database-versioning/new/sandbox-0001-refactor.sql
+++ b/database-versioning/new/sandbox-0001-refactor.sql
@@ -28,56 +28,50 @@ 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.
+  AS ENUM ('pending', 'confirmed');
 
--- start of: demobank config tables
+-- FIXME: comments on types (see exchange for example)!
 
-CREATE TABLE IF NOT EXISTS demobank_configs 
-  (demobank_config_id BIGINT GENERATED BY DEFAULT AS IDENTITY
-  ,demobank_name TEXT NOT NULL
-  );
+-- start of: bank config tables
 
-CREATE TABLE IF NOT EXISTS demobank_config_pairs 
-  (config_pair_id BIGINT GENERATED BY DEFAULT AS IDENTITY
-  ,demobank_name TEXT NOT NULL
-  ,config_key TEXT NOT NULL
-  ,config_value TEXT NULL
+CREATE TABLE IF NOT EXISTS configuration
+  (config_key TEXT PRIMARY KEY
+  ,config_value TEXT
   );
 
-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
+-- end of: bank config tables
 
 -- start of: bank accounts
 
-CREATE TABLE IF NOT EXISTS demobank_customers
-  (demobank_customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+CREATE TABLE IF NOT EXISTS customers
+  (customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY
   ,login TEXT NOT NULL
   ,password_hash TEXT NOT NULL
-  ,name TEXT NULL
-  ,email TEXT NULL
-  ,phone TEXT NULL
-  ,cashout_address TEXT NULL
+  ,name TEXT
+  ,email TEXT
+  ,phone TEXT
+  ,cashout_payto TEXT
+  ,cashout_currency TEXT
   );
 
-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.'
+COMMENT ON COLUMN customers.cashout_payto
+  IS 'RFC 8905 payto URI to collect fiat payments that come from the 
conversion of regional currency cash-out operations.';
+
+COMMENT ON COLUMN customers.name
+  IS 'Full name of the customer.';
+
 
 CREATE TABLE IF NOT EXISTS bank_accounts 
   (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.
-  ,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 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 taler_amount DEFAULT '0'
+  ,iban TEXT NOT NULL UNIQUE
+  ,bic TEXT NOT NULL
+  ,bank_account_label TEXT NOT NULL
+  ,owning_customer_id BIGINT NOT NULL
+    REFERENCES bank_customers(bank_customer_id)
+  ,is_public BOOLEAN DEFAULT FALSE NOT NULL -- privacy by default
+  ,last_nexus_fetch_row_id BIGINT
+  ,balance taler_amount DEFAULT (0, 0)
+  ,UNIQUE (owning_customer_id, bank_account_label)
   );
 
 COMMENT ON TABLE bank_accounts
@@ -88,8 +82,8 @@ 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
+COMMENT ON TABLE bank_accounts.last_nexus_fetch_row_id
+  IS 'Keeps the ID of the last incoming 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.'
@@ -101,14 +95,9 @@ can be publicly shared'
 COMMENT ON COLUMN bank_accounts.label
   IS 'Label of the bank account'
 
-COMMENT ON COLUMN bank_accounts.owning_login
+COMMENT ON COLUMN bank_accounts.owning_customer_id
   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
@@ -123,14 +112,15 @@ CREATE TABLE IF NOT EXISTS bank_account_transactions
   ,debtor_name TEXT NOT NULL
   ,subject TEXT NOT NULL
   ,amount taler_amount NOT NULL
-  ,currency TEXT NOT NULL
-  ,transaction_date BIGINT NOT NULL
+  ,transaction_date BIGINT NOT NULL -- is this ISO20022 terminology? document 
format (microseconds since epoch)
   ,account_servicer_reference TEXT NOT NULL
-  ,pmt_inf_id TEXT NULL
-  ,end_to_end_id TEXT NULL
+  ,payment_information_id TEXT
+  ,end_to_end_id TEXT
+  ,is_pending BOOLEAN NOT NULL DEFAULT TRUE
   ,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
+  ,bank_account_id BIGINT NOT NULL
+    REFERENCES bank_accounts(bank_account_id)
+    ON DELETE CASCADE ON UPDATE RESTRICT
   );
 
 COMMENT ON bank_account_transactions.direction
@@ -141,48 +131,41 @@ 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.'
+COMMENT ON bank_account_transactions.bank
+  IS 'The bank hosting the transaction and its bank account.'
 
 -- end of: money transactions
 
 -- start of: cashout management
 
-CREATE TABLE IF NOT EXISTS cashout_submissions 
-  (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 
   (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
-  ,buy_in_fee TEXT NOT NULL
-  ,sell_at_ratio TEXT NOT NULL
-  ,sell_out_fee TEXT NOT NULL
+  ,cashout_uuid uuid PRIMARY KEY
+  ,local_transaction BIGINT UNIQUE -- FIXME: Comment that the transaction only 
gets created after the TAN confirmation
+    REFERENCES bank_account_transactions(bank_transaction_id)
+    ON DELETE RESTRICT
+    ON UPDATE RESTRICT
+  ,amount_debit taler_amount NOT NULL -- FIXME: comment on column how to 
derive the currency
+  ,amount_credit taler_amount NOT NULL -- FIXME: comment on column how to 
derive the currency
+  ,buy_at_ratio INT4 NOT NULL -- FIXME: document format (fractional base)
+  ,buy_in_fee taler_amount NOT NULL -- FIXME: comment on column how to derive 
the currency
+  ,sell_at_ratio INT4 NOT NULL -- FIXME: document format (fractional base)
+  ,sell_out_fee taler_amount NOT NULL -- FIXME: comment on column how to 
derive the currency
   ,subject TEXT NOT NULL
   ,creation_time BIGINT NOT NULL
-  ,tan_confirmation_time BIGINT NULL
+  ,tan_confirmation_time BIGINT
   ,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_salt TEXT NOT NULL
-  ,cashout_status cashout_status_enum DEFAULT 'pending' NOT NULL
+  ,tan_code TEXT NOT NULL
+  ,bank_account BIGINT DEFAULT(NULL)
+    REFERENCES bank_accounts(bank_account_id)
+    ON DELETE CASCADE
+    ON UPDATE RESTRICT
+  ,cashout_address TEXT NOT NULL -- FIXME: clarify payto, if it's a payto use 
it in the name
+  ,cashout_currency TEXT NOT NULL
   );
 
+-- FIXME: table comment missing
+
 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
@@ -190,6 +173,25 @@ COMMENT ON COLUMN cashout_operations.cashout_address
 COMMENT ON COLUMN cashout_operations.tan_salt
   IS 'text that the customer must send to confirm the cash-out operation'
 
+-- FIXME: check in the code if this really only has pending or failed 
submissions!
+CREATE TABLE IF NOT EXISTS pending_cashout_submissions 
+  (cashout_submission_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  ,cashout_operation_id BIGINT NOT NULL
+    REFERENCES cashout_operations(cashout_operation_id)
+    ON DELETE CASCADE
+    ON UPDATE RESTRICT
+  ,nexus_response TEXT
+  ,submission_time BIGINT
+  );
+
+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.nexus_response
+  IS 'Keeps the Nexus response to the payment submission on failure'
+
+
 -- end of: cashout management
 
 -- start of: EBICS management
@@ -197,7 +199,8 @@ COMMENT ON COLUMN cashout_operations.tan_salt
 CREATE TABLE IF NOT EXISTS ebics_hosts 
   (ebics_host_id BIGINT GENERATED BY DEFAULT AS IDENTITY
   ,ebics_host_name TEXT NOT NULL
-  ,ebics_version TEXT NOT NULL
+  ,ebics_version TEXT NOT NULL -- FIXME: This should be an enum
+  -- FIXME: Do we want to specify the dialect here?
   ,encryption_private_key BYTEA NOT NULL
   ,signature_private_key BYTEA NOT NULL
   );
@@ -206,46 +209,59 @@ CREATE TABLE IF NOT EXISTS ebics_subscribers
   (ebics_subscriber_id SERIAL PRIMARY KEY
   ,ebics_user_id TEXT NOT NULL
   ,ebics_partner_id TEXT NOT NULL
-  ,ebics_system_id TEXT NULL
+  ,ebics_system_id TEXT
   ,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
-  ,next_order_id INT NOT NULL -- set as a constant without significantly 
participating in the protocol.  Remove this column?
+  ,signature_key_rsa_pub BYTEA NOT NULL
+  ,signature_key_state subscriber_key_state_enum NOT NULL
+  ,encryption_key_rsa_pub BYTEA NOT NULL
+  ,encryption_key_state subscriber_key_state_enum NOT NULL
   ,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
+  -- FIXME: Do we need some information about the next order ID? There is a 
bug open.
   );
-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
-  (subscriber_key_id SERIAL PRIMARY KEY
-   ,rsa_public_key bytea NOT NULL
-   ,subscriber_key_state subscriber_key_state_enum NOT NULL
+-- FIXME: comment this table
+-- FIXME: indices on both columns individually
+CREATE TABLE ebics_subscribers_of_bank_accounts
+  (ebics_subscriber_id BIGINT NOT NULL
+    REFERENCES ebics_subscribers(ebics_subscriber_id)
+  ,bank_account_id BIGINT NOT NULL
+    REFERENCES bank_accounts(bank_account_id)
   );
 
+
 CREATE TABLE IF NOT EXISTS ebics_download_transactions 
-  (transaction_id TEXT NOT NULL
-  ,order_type TEXT NOT NULL
-  ,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
+  (transaction_id TEXT PRIMARY KEY
+  ,order_type VARCHAR(3) NOT NULL
+  -- EBICS3: ,btf_type TEXT -- fixme: document: in EBICS 2.x this can be NULL
+  -- FIXME: see what else we need for EBICS 3
+  ,ebics_host_id BIGINT NOT NULL
+    REFERENCES ebics_hosts(ebics_host_id)
+    ON DELETE CASCADE
+    ON UPDATE RESTRICT
+  ,ebics_subscriber BIGINT NOT NULL
+    REFERENCES ebics_subscribers(ebics_subscriber_id)
+    ON DELETE CASCADE
+    ON UPDATE RESTRICT
   ,encoded_response TEXT NOT NULL
-  ,transaction_key_enc bytea NOT NULL
+  ,transaction_key_enc BYTEA NOT NULL
   ,num_segments INT NOT NULL
   ,segment_size INT NOT NULL
-  ,receipt_received BOOLEAN NOT NULL
+  ,receipt_received BOOLEAN NOT NULL DEFAULT (FALSE) -- FIXME: Do we need this 
field if we anyway delete the entry after the receipt?
+  -- FIXME: Download start time for garbage collection / timeouts
   );
 
 COMMENT ON TABLE ebics_download_transactions
-  IS 'Tracks the evolution of one EBICS transaction'
+  IS 'Tracks the evolution of one EBICS download 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
+  (transaction_id TEXT PRIMARY KEY
+  ,order_type VARCHAR(3) NOT NULL
+  -- EBICS3: ,btf_type TEXT -- fixme: document: in EBICS 2.x this can be NULL
+  -- FIXME: see what else we need for EBICS 3
   ,order_id TEXT NOT NULL
   ,ebics_host BIGINT NOT NULL
     REFERENCES ebics_hosts(ebics_host_id)
@@ -258,20 +274,35 @@ CREATE TABLE IF NOT EXISTS ebics_upload_transactions
   ,num_segments INT NOT NULL
   ,last_seen_segment INT NOT NULL
   ,transaction_key_enc BYTEA NOT NULL
+  -- FIXME: Download start time for garbage collection / timeouts
   );
 
 CREATE TABLE IF NOT EXISTS ebics_upload_transaction_chunks 
-  (ebics_transaction_id BIGINT GENERATED BY DEFAULT AS IDENTITY
+  (ebics_transaction_id BIGINT
+    REFERENCES ebics_upload_transactions(ebics_transaction_id)
+    ON DELETE CASCADE
+    ON UPDATE RESTRICT
   ,upload_chunk_index INT NOT NULL
   ,upload_chunk_content BYTEA NOT NULL
   );
 
+-- FIXME: look at the code how it's used
+-- I *think* this is only used for upload orders.
+-- I am not sure if the signature (especially with VEU)
+-- can be uploaded before the order itself has been uploaded
 CREATE TABLE IF NOT EXISTS ebics_order_signatures 
   (order_signature_id SERIAL PRIMARY KEY
+   ,ebics_transaction_id BIGINT
+    REFERENCES ebics_upload_transactions(ebics_transaction_id)
+    ON DELETE CASCADE
+    ON UPDATE RESTRICT
+  ,ebics_subscriber_id BIGINT
+    REFERENCES ebics_subscribers(ebics_subscriber_id)
+    ON DELETE CASCASE
+    ON UPDATE RESTRICT
+  -- FIXME: do we also need to reference the ebics host? or does the 
subscriber uniquely determine it?
   ,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
   );
@@ -283,24 +314,16 @@ COMMENT ON TABLE ebics_order_signatures
 
 -- start of: accounts activity report 
 
-CREATE TABLE IF NOT EXISTS bank_account_fresh_transactions 
-  (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
+  (id BIGINT GENERATED BY IDENTITY
   ,report_id TEXT NOT NULL
   ,creation_time BIGINT NOT NULL
   ,xml_message TEXT NOT NULL
-  ,bank_account INT NOT NULL REFERENCES bank_accounts(id) ON DELETE RESTRICT 
ON UPDATE RESTRICT
+  ,bank_account BIGINT NOT NULL
+    REFERENCES bank_accounts(id)
+    ON DELETE CASCADE
+    ON UPDATE RESTRICT
   );
 
 -- Really keep this table?  It tracks the EBICS statements
@@ -310,22 +333,25 @@ CREATE TABLE IF NOT EXISTS bank_account_statements
   ,statement_id TEXT NOT NULL
   ,creation_time BIGINT NOT NULL
   ,xml_message TEXT NOT NULL
-  ,bank_account INT NOT NULL REFERENCES bank_accounts(id) ON DELETE RESTRICT 
ON UPDATE RESTRICT
-  ,balance_clbd TEXT NOT NULL
+  ,bank_account BIGINT NOT NULL
+    REFERENCES bank_accounts(id)
+    ON DELETE CASCADE
+    ON UPDATE RESTRICT
+  ,balance_clbd TEXT NOT NULL -- FIXME: name. balance_closing?
   );
 -- end of: accounts activity report 
 
 -- start of: Taler integration
 
-CREATE TABLE IF NOT EXISTS taler_withdrawals 
+CREATE TABLE IF NOT EXISTS taler_withdrawal_operations
   (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
+  ,selection_done BOOLEAN DEFAULT FALSE NOT NULL
+  ,aborted BOOLEAN DEFAULT FALSE NOT NULL
+  ,confirmation_done BOOLEAN DEFAULT FALSE NOT NULL
+  ,reserve_pub BYTEA CHECK(len(reserve_pub)=32) NULL
+  ,selected_exchange_payto TEXT
   ,wallet_bank_account BIGINT NOT NULL
     REFERENCES bank_accounts(id)
       ON DELETE RESTRICT

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