1 files changed, 145 insertions, 12 deletions
diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql
index 29dd0115..00053cf3 100644
--- a/src/backenddb/merchant-0002.sql
+++ b/src/backenddb/merchant-0002.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2021 Taler Systems SA
+-- 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
@@ -14,6 +14,10 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
+-- @file merchant-0002.sql
+-- @brief database schema for the merchant
+-- @author Christian Blättler
+
 -- Everything in one big transaction
 BEGIN;
 
@@ -22,17 +26,146 @@ SELECT _v.register_patch('merchant-0002', NULL, NULL);
 
 SET search_path TO merchant;
 
-ALTER TABLE merchant_instances
-  ADD COLUMN website VARCHAR,
-  ADD COLUMN email VARCHAR,
-  ADD COLUMN logo BYTEA;
-
-COMMENT ON COLUMN merchant_instances.website
-  IS 'merchant site URL';
-COMMENT ON COLUMN merchant_instances.email
-  IS 'email';
-COMMENT ON COLUMN merchant_instances.logo
-  IS 'data image url';
+ALTER TABLE merchant_orders
+   ADD COLUMN fulfillment_url TEXT DEFAULT NULL
+  ,ADD COLUMN session_id TEXT DEFAULT '' NOT NULL;
+
+COMMENT ON COLUMN merchant_orders.fulfillment_url
+  IS 'URL where the wallet will redirect the user upon payment';
+COMMENT ON COLUMN merchant_orders.session_id
+  IS 'session_id to which the payment will be bound';
+
+
+CREATE INDEX IF NOT EXISTS merchant_orders_by_merchant_and_session
+  ON merchant_orders
+  (merchant_serial,session_id);
+
+CREATE INDEX IF NOT EXISTS merchant_orders_by_merchant_and_fullfilment_and_session
+  ON merchant_orders
+  (merchant_serial,fulfillment_url,session_id);
+
+CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_session
+  ON merchant_contract_terms
+  (merchant_serial,session_id);
+
+
+
+ALTER TABLE merchant_deposit_confirmations
+   ADD COLUMN wire_transfer_deadline INT8 DEFAULT (0) NOT NULL,
+   ADD COLUMN wire_pending BOOL DEFAULT (TRUE) NOT NULL,
+   ADD COLUMN exchange_failure TEXT DEFAULT NULL;
+
+COMMENT ON COLUMN merchant_deposit_confirmations.wire_transfer_deadline
+  IS 'when should the exchange make the wire transfer at the latest';
+COMMENT ON COLUMN merchant_deposit_confirmations.wire_pending
+  IS 'true if we are awaiting wire details for a deposit of this purchase (and are not blocked on KYC); false once the exchange says that the wire transfer has happened (does not mean that we confirmed it happened though)';
+COMMENT ON COLUMN merchant_deposit_confirmations.exchange_failure
+  IS 'Text describing exchange failures in making timely wire transfers for this deposit confirmation';
+
+CREATE INDEX IF NOT EXISTS merchant_deposit_confirmations_by_pending_wire
+  ON merchant_deposit_confirmations
+  (exchange_url,wire_transfer_deadline)
+  WHERE wire_pending;
+
+CREATE INDEX IF NOT EXISTS merchant_deposits_by_deposit_confirmation_serial
+  ON merchant_deposits
+  (deposit_confirmation_serial);
+
+-------------------------- Tokens -----------------------------
+
+CREATE TABLE IF NOT EXISTS merchant_token_families
+  (token_family_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+  ,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+  ,slug TEXT NOT NULL UNIQUE
+  ,name TEXT NOT NULL
+  ,description TEXT
+  ,description_i18n BYTEA NOT NULL
+  ,valid_after BIGINT NOT NULL
+  ,valid_before BIGINT NOT NULL
+  ,duration BIGINT NOT NULL
+  ,kind TEXT NOT NULL CHECK (kind IN ('subscription', 'discount'))
+  ,issued BIGINT DEFAULT 0
+  ,redeemed BIGINT DEFAULT 0
+  );
+COMMENT ON TABLE merchant_token_families
+ IS 'Token families configured by the merchant.';
+COMMENT ON COLUMN merchant_token_families.merchant_serial
+ IS 'Instance where the token family is configured.';
+COMMENT ON COLUMN merchant_token_families.slug
+ IS 'Unique slug for the token family.';
+COMMENT ON COLUMN merchant_token_families.name
+ IS 'Name of the token family.';
+COMMENT ON COLUMN merchant_token_families.description
+ IS 'Human-readable description or details about the token family.';
+COMMENT ON COLUMN merchant_token_families.description_i18n
+ IS 'JSON map from IETF BCP 47 language tags to localized descriptions';
+COMMENT ON COLUMN merchant_token_families.valid_after
+ IS 'Start time of the token family''s validity period.';
+COMMENT ON COLUMN merchant_token_families.valid_before
+ IS 'End time of the token family''s validity period.';
+COMMENT ON COLUMN merchant_token_families.duration
+ IS 'Duration of the token.';
+COMMENT ON COLUMN merchant_token_families.kind
+ IS 'Kind of the token (e.g., subscription, discount).';
+COMMENT ON COLUMN merchant_token_families.issued
+ IS 'Counter for the number of tokens issued for this token family.';
+COMMENT ON COLUMN merchant_token_families.redeemed
+ IS 'Counter for the number of tokens redeemed for this token family.';
+
+
+CREATE TABLE IF NOT EXISTS merchant_token_family_keys
+  (token_family_key_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+  ,token_family_serial BIGINT REFERENCES merchant_token_families(token_family_serial) ON DELETE CASCADE
+  ,valid_after BIGINT NOT NULL
+  ,valid_before BIGINT NOT NULL
+  ,pub BYTEA NOT NULL
+  ,h_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(h_pub)=32)
+  ,priv BYTEA
+  ,cipher TEXT NOT NULL CHECK (cipher IN ('rsa', 'cs'))
+  ,UNIQUE (token_family_serial, valid_after)
+  );
+
+COMMENT ON TABLE merchant_token_family_keys
+ IS 'Keys for token families.';
+COMMENT ON COLUMN merchant_token_family_keys.token_family_serial
+ IS 'Token family to which the key belongs.';
+COMMENT ON COLUMN merchant_token_family_keys.valid_after
+ IS 'Start time for the validity of the token key.';
+COMMENT ON COLUMN merchant_token_family_keys.valid_before
+ IS 'Expiration time for the validity of the token key.';
+COMMENT ON COLUMN merchant_token_family_keys.pub
+ IS 'Public key of the token family.';
+COMMENT ON COLUMN merchant_token_family_keys.h_pub
+ IS 'Hash of the public key for quick lookup.';
+COMMENT ON COLUMN merchant_token_family_keys.priv
+ IS 'Private key of the token family; can be NULL if no more tokens of this family should be issued.';
+COMMENT ON COLUMN merchant_token_family_keys.cipher
+ IS 'Cipher used (rsa or cs).';
+
+
+CREATE TABLE IF NOT EXISTS merchant_spent_tokens
+  (spent_token_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+  ,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+  ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
+  ,token_family_key_serial BIGINT REFERENCES merchant_token_family_keys(token_family_key_serial) ON DELETE CASCADE
+  ,token_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(token_pub)=32)
+  ,token_sig BYTEA NOT NULL CHECK (LENGTH(token_sig)=64)
+  ,blind_sig BYTEA NOT NULL
+  );
+COMMENT ON TABLE merchant_spent_tokens
+ IS 'Tokens that have been spent by customers.';
+COMMENT ON COLUMN merchant_spent_tokens.merchant_serial
+ IS 'Merchant serial where the token was spent.';
+COMMENT ON COLUMN merchant_spent_tokens.h_contract_terms
+ IS 'This is no foreign key by design.';
+COMMENT ON COLUMN merchant_spent_tokens.token_family_key_serial
+ IS 'Token family to which the spent token belongs.';
+COMMENT ON COLUMN merchant_spent_tokens.token_pub
+ IS 'Public key of the spent token.';
+COMMENT ON COLUMN merchant_spent_tokens.token_sig
+ IS 'Signature that the token was spent on specified order.';
+COMMENT ON COLUMN merchant_spent_tokens.blind_sig
+ IS 'Blind signature for the spent token to prove validity of token.';
 
 -- Complete transaction
 COMMIT;