commenting the SQL

1 files changed, 35 insertions, 2 deletions

diff --git a/database-versioning/new/nexus-0001-refactor.sql b/database-versioning/new/nexus-0001-refactor.sql
index 453c9b60..e63cf753 100644
--- 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);