commit d2fe54bfd303a5f3d2a2d2a3e0920a3fd1c2f376
parent 62796f978197ccbfbaad192335eda5e1fb013b34
Author: MS <ms@taler.net>
Date: Thu, 27 Jul 2023 18:52:35 +0200
commenting the SQL
Diffstat:
1 file changed, 35 insertions(+), 2 deletions(-)
diff --git a/database-versioning/new/nexus-0001-refactor.sql b/database-versioning/new/nexus-0001-refactor.sql
@@ -120,6 +120,8 @@ CREATE TABLE IF NOT EXISTS nexus_permissions
-- start of: Taler facade management
+-- Holds valid Taler payments, typically those that are returned
+-- to the Wirewatch by the Taler facade.
CREATE TABLE IF NOT EXISTS taler_incoming_payments
(id BIGSERIAL PRIMARY KEY
,payment NOT NULL REFERENCES nexus_bank_transactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
@@ -128,6 +130,9 @@ CREATE TABLE IF NOT EXISTS taler_incoming_payments
,incoming_payto_uri TEXT NOT NULL
);
+-- This table holds the outgoing payments that were requested
+-- by the exchange to pay merchants. The columns reflect the
+-- data model of the /transfer call from the TWG.
CREATE TABLE IF NOT EXISTS taler_requested_payments
(id BIGSERIAL PRIMARY KEY
,facade NOT NULL REFERENCES facades(id) ON DELETE RESTRICT ON UPDATE RESTRICT
@@ -139,6 +144,10 @@ CREATE TABLE IF NOT EXISTS taler_requested_payments
,credit_account TEXT NOT NULL
);
+
+-- Typically contains payments with an invalid reserve public
+-- key as the subject. The 'payment' columns points at the ingested
+-- transaction that is invalid in the Taler sense.
CREATE TABLE IF NOT EXISTS taler_invalid_incoming_payments
(id BIGSERIAL PRIMARY KEY
,payment NOT NULL REFERENCES nexus_bank_transactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
@@ -162,6 +171,9 @@ CREATE TABLE IF NOT EXISTS anastasis_incoming_payments
-- start of: core banking
+-- A bank account managed by Nexus. Each row corresponds to an
+-- actual bank account at the bank and that is owned by the 'account_holder'
+-- column. FIXME: is account_holder a name or a user-name?
CREATE TABLE IF NOT EXISTS nexus_bank_accounts
(id BIGSERIAL PRIMARY KEY
,bank_account_id TEXT NOT NULL UNIQUE
@@ -177,6 +189,10 @@ CREATE TABLE IF NOT EXISTS nexus_bank_accounts
,CONSTRAINT fk_nexusbankaccounts_defaultbankconnection_id FOREIGN KEY (default_bank_connection) REFERENCES nexus_bank_connections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
+
+-- All the payments that were ingested by Nexus. Each row
+-- points at the Nexus bank account that is related to the transaction.
+-- FIXME: explain 'updated_by'.
CREATE TABLE IF NOT EXISTS nexus_bank_transactions
(id BIGSERIAL PRIMARY KEY
,account_transaction_id TEXT NOT NULL
@@ -189,6 +205,12 @@ CREATE TABLE IF NOT EXISTS nexus_bank_transactions
,transaction_json TEXT NOT NULL
);
+-- Table holding the data that represent one outgoing payment
+-- made by the (user owning the) 'bank_account'. The 'raw_confirmation'
+-- column points at the global table of all the ingested payments
+-- where the pointed ingested payment is the confirmation that the
+-- pointing payment initiation was finalized at the bank. All
+-- the IDs involved in this table mimic the semantics of ISO20022 pain.001.
CREATE TABLE IF NOT EXISTS payment_initiations
(id BIGSERIAL PRIMARY KEY
,bank_account NOT NULL REFERENCES nexus_bank_accounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
@@ -209,7 +231,11 @@ CREATE TABLE IF NOT EXISTS payment_initiations
,raw_confirmation BIGINT NULL REFERENCES nexus_bank_transactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
-CREATE TABLE IF NOT EXISTS nexus_bank_balances -- table never used
+-- This table stores user balances for a certain bank account.
+-- It was however never used, plus it needs the collaboration
+-- of the bank, since giving balances along the ISO20022 is not
+-- mandatory.
+CREATE TABLE IF NOT EXISTS nexus_bank_balances
(id BIGSERIAL PRIMARY KEY
,balance TEXT NOT NULL
,credit_debit_indicator TEXT NOT NULL
@@ -218,16 +244,23 @@ CREATE TABLE IF NOT EXISTS nexus_bank_balances -- table never used
,CONSTRAINT fk_nexusbankbalances_bankaccount_id FOREIGN KEY (bank_account) REFERENCES nexus_bank_accounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
+
+-- This table holds the business content that came from the
+-- bank. Storing messages here happens with problematic messages,
+-- or when the storing is enabled. By default, successful messages
+-- are never stored.
CREATE TABLE IF NOT EXISTS nexus_bank_messages
(id BIGSERIAL PRIMARY KEY
,bank_connection BIGINT NOT NULL
,message bytea NOT NULL
,message_id TEXT NULL
- ,fetch_level VARCHAR(16) NOT NULL
+ ,fetch_level VARCHAR(16) NOT NULL -- report, statement or notification?
,errors BOOLEAN DEFAULT false NOT NULL
,CONSTRAINT fk_nexusbankmessages_bankconnection_id FOREIGN KEY (bank_connection) REFERENCES nexus_bank_connections(id) ON DELETE RESTRICT ON UPDATE RESTRICT
);
+-- Tuple made by the account name as it is offered by the bank
+-- and the associated connection name.
ALTER TABLE
offered_bank_accounts ADD CONSTRAINT offeredbankaccounts_offeredaccountid_bankconnection_unique UNIQUE (offered_account_id, bank_connection);
