combine different merchant DB versions into one new master, rename tip->reward in tables

7 files changed, 239 insertions, 259 deletions

diff --git a/src/backenddb/drop.sql b/src/backenddb/drop.sql
index c4185d8b..f2d4096c 100644
--- a/src/backenddb/drop.sql
+++ b/src/backenddb/drop.sql
@@ -23,10 +23,6 @@ BEGIN;
 -- Unlike the other SQL files, it SHOULD be updated to reflect the
 -- latest requirements for dropping tables.
 
-SELECT _v.unregister_patch('merchant-0005');
-SELECT _v.unregister_patch('merchant-0004');
-SELECT _v.unregister_patch('merchant-0003');
-SELECT _v.unregister_patch('merchant-0002');
 SELECT _v.unregister_patch('merchant-0001');
 
 
diff --git a/src/backenddb/merchant-0001.sql b/src/backenddb/merchant-0001.sql
index 77792735..0aa97f73 100644
--- a/src/backenddb/merchant-0001.sql
+++ b/src/backenddb/merchant-0001.sql
@@ -1,6 +1,6 @@
 --
 -- This file is part of TALER
--- Copyright (C) 2014--2022 Taler Systems SA
+-- Copyright (C) 2014--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,11 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
+-- @file merchant-0001.sql
+-- @brief database schema for the merchant
+-- @author Christian Grothoff
+-- @author Priscilla Huang
+
 -- Everything in one big transaction
 BEGIN;
 
@@ -37,8 +42,6 @@ CREATE TABLE IF NOT EXISTS merchant_exchange_wire_fees
   ,wire_fee_frac INT4 NOT NULL
   ,closing_fee_val INT8 NOT NULL
   ,closing_fee_frac INT4 NOT NULL
-  ,wad_fee_val INT8 NOT NULL
-  ,wad_fee_frac INT4 NOT NULL
   ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
   ,UNIQUE (master_pub,h_wire_method,start_date)
   );
@@ -72,6 +75,9 @@ CREATE TABLE IF NOT EXISTS merchant_instances
   ,auth_salt BYTEA CHECK(LENGTH(auth_salt)=32)
   ,merchant_id VARCHAR NOT NULL UNIQUE
   ,merchant_name VARCHAR NOT NULL
+  ,website VARCHAR
+  ,email VARCHAR
+  ,logo BYTEA
   ,address BYTEA NOT NULL
   ,jurisdiction BYTEA NOT NULL
   ,default_max_deposit_fee_val INT8 NOT NULL
@@ -81,6 +87,7 @@ CREATE TABLE IF NOT EXISTS merchant_instances
   ,default_wire_fee_amortization INT4 NOT NULL
   ,default_wire_transfer_delay INT8 NOT NULL
   ,default_pay_delay INT8 NOT NULL
+  ,user_type INT4
   );
 COMMENT ON TABLE merchant_instances
   IS 'all the instances supported by this backend';
@@ -92,10 +99,18 @@ COMMENT ON COLUMN merchant_instances.address
   IS 'physical address of the merchant as a Location in JSON format (required)';
 COMMENT ON COLUMN merchant_instances.jurisdiction
   IS 'jurisdiction of the merchant as a Location in JSON format (required)';
+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';
 COMMENT ON COLUMN merchant_instances.auth_hash
   IS 'hash used for merchant back office Authorization, NULL for no check';
 COMMENT ON COLUMN merchant_instances.auth_salt
   IS 'salt to use when hashing Authorization header before comparing with auth_hash';
+COMMENT ON COLUMN merchant_instances.user_type
+  IS 'what type of user is this (individual or business)';
 
 
 
@@ -113,6 +128,9 @@ CREATE TABLE IF NOT EXISTS merchant_accounts
      REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
   ,h_wire BYTEA NOT NULL CHECK (LENGTH(h_wire)=64)
   ,salt BYTEA NOT NULL CHECK (LENGTH(salt)=16)
+  ,credit_facade_url VARCHAR
+  ,credit_facade_credentials VARCHAR
+  ,last_bank_serial INT8 NOT NULL DEFAULT (0)
   ,payto_uri VARCHAR NOT NULL
   ,active BOOLEAN NOT NULL
   ,UNIQUE (merchant_serial,payto_uri)
@@ -128,6 +146,12 @@ COMMENT ON COLUMN merchant_accounts.payto_uri
   IS 'payto URI of a merchant bank account';
 COMMENT ON COLUMN merchant_accounts.active
   IS 'true if we actively use this bank account, false if it is just kept around for older contracts to refer to';
+COMMENT ON COLUMN merchant_accounts.credit_facade_url
+  IS 'Base URL of a facade where the merchant can inquire about incoming bank transactions into this account';
+COMMENT ON COLUMN merchant_accounts.credit_facade_credentials
+  IS 'JSON with credentials needed to access the credit facade';
+COMMENT ON COLUMN merchant_accounts.last_bank_serial
+  IS 'Serial number of the bank of the last transaction we successfully imported';
 
 
 -------------------------- Inventory  ---------------------------
@@ -213,6 +237,8 @@ CREATE TABLE IF NOT EXISTS merchant_orders
   ,pay_deadline INT8 NOT NULL
   ,creation_time INT8 NOT NULL
   ,contract_terms BYTEA NOT NULL
+  ,pos_key VARCHAR DEFAULT NULL
+  ,pos_algorithm INT NOT NULL DEFAULT (0)
   ,UNIQUE (merchant_serial, order_id)
   );
 COMMENT ON TABLE merchant_orders
@@ -227,6 +253,12 @@ COMMENT ON COLUMN merchant_orders.merchant_serial
   IS 'Identifies the instance offering the contract';
 COMMENT ON COLUMN merchant_orders.pay_deadline
   IS 'How long is the offer valid. After this time, the order can be garbage collected';
+COMMENT ON COLUMN merchant_orders.pos_key
+  IS 'encoded based key which is used for the verification of payment';
+COMMENT ON COLUMN merchant_orders.pos_algorithm
+  IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
+
+
 CREATE INDEX IF NOT EXISTS merchant_orders_by_expiration
   ON merchant_orders
     (pay_deadline);
@@ -263,6 +295,8 @@ CREATE TABLE IF NOT EXISTS merchant_contract_terms
   ,wired BOOLEAN DEFAULT FALSE NOT NULL
   ,fulfillment_url VARCHAR
   ,session_id VARCHAR DEFAULT '' NOT NULL
+  ,pos_key VARCHAR DEFAULT NULL
+  ,pos_algorithm INT NOT NULL DEFAULT (0)
   ,claim_token BYTEA NOT NULL CHECK (LENGTH(claim_token)=16)
   ,UNIQUE (merchant_serial, order_id)
   ,UNIQUE (merchant_serial, h_contract_terms)
@@ -289,6 +323,11 @@ COMMENT ON COLUMN merchant_contract_terms.session_id
   IS 'last session_id from we confirmed the paying client to use, empty string for none';
 COMMENT ON COLUMN merchant_contract_terms.pay_deadline
   IS 'How long is the offer valid. After this time, the order can be garbage collected';
+COMMENT  ON COLUMN merchant_contract_terms.pos_key
+  IS 'enconded based key which is used for the verification of payment';
+COMMENT ON COLUMN merchant_orders.pos_algorithm
+  IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
+
 COMMENT ON COLUMN merchant_contract_terms.claim_token
   IS 'Token optionally used to access the status of the order. All zeros (not NULL) if not used';
 
@@ -387,6 +426,9 @@ CREATE TABLE IF NOT EXISTS merchant_transfers
   ,credit_amount_frac INT4 NOT NULL
   ,account_serial INT8 NOT NULL
    REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE
+  ,ready_time INT8 NOT NULL DEFAULT (0)
+  ,validation_status INT4 DEFAULT NULL
+  ,failed BOOLEAN NOT NULL DEFAULT FALSE
   ,verified BOOLEAN NOT NULL DEFAULT FALSE
   ,confirmed BOOLEAN NOT NULL DEFAULT FALSE
   ,UNIQUE (wtid, exchange_url, account_serial)
@@ -399,6 +441,18 @@ COMMENT ON COLUMN merchant_transfers.confirmed
   IS 'true once the merchant confirmed that this transfer was received';
 COMMENT ON COLUMN merchant_transfers.credit_amount_val
   IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the merchant';
+COMMENT ON COLUMN merchant_transfers.failed
+  IS 'set to true on permanent verification failures';
+COMMENT ON COLUMN merchant_transfers.validation_status
+  IS 'Taler error code describing the state of the validation';
+
+CREATE INDEX merchant_transfers_by_open
+  ON merchant_transfers
+  (ready_time ASC)
+  WHERE confirmed AND NOT (failed OR verified);
+COMMENT ON INDEX merchant_transfers_by_open
+  IS 'For select_open_transfers';
+
 
 CREATE TABLE IF NOT EXISTS merchant_transfer_signatures
   (credit_serial BIGINT PRIMARY KEY
@@ -460,9 +514,9 @@ COMMENT ON COLUMN merchant_deposit_to_transfer.execution_time
   IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant';
 
 
--------------------------- Tipping ---------------------------
+-------------------------- Rewards ---------------------------
 
-CREATE TABLE IF NOT EXISTS merchant_tip_reserves
+CREATE TABLE IF NOT EXISTS merchant_reward_reserves
   (reserve_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
   ,reserve_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_pub)=32)
   ,merchant_serial BIGINT NOT NULL
@@ -473,53 +527,54 @@ CREATE TABLE IF NOT EXISTS merchant_tip_reserves
   ,merchant_initial_balance_frac INT4 NOT NULL
   ,exchange_initial_balance_val INT8 NOT NULL DEFAULT 0
   ,exchange_initial_balance_frac INT4 NOT NULL DEFAULT 0
-  ,tips_committed_val INT8 NOT NULL DEFAULT 0
-  ,tips_committed_frac INT4 NOT NULL DEFAULT 0
-  ,tips_picked_up_val INT8 NOT NULL DEFAULT 0
-  ,tips_picked_up_frac INT4 NOT NULL DEFAULT 0
+  ,rewards_committed_val INT8 NOT NULL DEFAULT 0
+  ,rewards_committed_frac INT4 NOT NULL DEFAULT 0
+  ,rewards_picked_up_val INT8 NOT NULL DEFAULT 0
+  ,rewards_picked_up_frac INT4 NOT NULL DEFAULT 0
   );
-COMMENT ON TABLE merchant_tip_reserves
-  IS 'balances of the reserves available for tips';
-COMMENT ON COLUMN merchant_tip_reserves.expiration
+COMMENT ON TABLE merchant_reward_reserves
+  IS 'balances of the reserves available for rewards';
+COMMENT ON COLUMN merchant_reward_reserves.expiration
   IS 'FIXME: EXCHANGE API needs to tell us when reserves close if we are to compute this';
-COMMENT ON COLUMN merchant_tip_reserves.merchant_initial_balance_val
+COMMENT ON COLUMN merchant_reward_reserves.merchant_initial_balance_val
   IS 'Set to the initial balance the merchant told us when creating the reserve';
-COMMENT ON COLUMN merchant_tip_reserves.exchange_initial_balance_val
+COMMENT ON COLUMN merchant_reward_reserves.exchange_initial_balance_val
   IS 'Set to the initial balance the exchange told us when we queried the reserve status';
-COMMENT ON COLUMN merchant_tip_reserves.tips_committed_val
-  IS 'Amount of outstanding approved tips that have not been picked up';
-COMMENT ON COLUMN merchant_tip_reserves.tips_picked_up_val
-  IS 'Total amount tips that have been picked up from this reserve';
-CREATE INDEX IF NOT EXISTS merchant_tip_reserves_by_reserve_pub_and_merchant_serial
-  ON merchant_tip_reserves
+COMMENT ON COLUMN merchant_reward_reserves.rewards_committed_val
+  IS 'Amount of outstanding approved rewards that have not been picked up';
+COMMENT ON COLUMN merchant_reward_reserves.rewards_picked_up_val
+  IS 'Total amount rewards that have been picked up from this reserve';
+
+CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_reserve_pub_and_merchant_serial
+  ON merchant_reward_reserves
     (reserve_pub,merchant_serial,creation_time);
-CREATE INDEX IF NOT EXISTS merchant_tip_reserves_by_merchant_serial_and_creation_time
-  ON merchant_tip_reserves
+CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_merchant_serial_and_creation_time
+  ON merchant_reward_reserves
     (merchant_serial,creation_time);
-CREATE INDEX IF NOT EXISTS merchant_tip_reserves_by_exchange_balance
-  ON merchant_tip_reserves
+CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_exchange_balance
+  ON merchant_reward_reserves
     (exchange_initial_balance_val,exchange_initial_balance_frac);
 
 
 
-CREATE TABLE IF NOT EXISTS merchant_tip_reserve_keys
+CREATE TABLE IF NOT EXISTS merchant_reward_reserve_keys
   (reserve_serial BIGINT NOT NULL UNIQUE
-     REFERENCES merchant_tip_reserves (reserve_serial) ON DELETE CASCADE
+     REFERENCES merchant_reward_reserves (reserve_serial) ON DELETE CASCADE
   ,reserve_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_priv)=32)
   ,exchange_url VARCHAR NOT NULL
-  ,payto_uri VARCHAR
+  ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)
   );
-COMMENT ON TABLE merchant_tip_reserves
+COMMENT ON TABLE merchant_reward_reserves
   IS 'private keys of reserves that have not been deleted';
-COMMENT ON COLUMN merchant_tip_reserve_keys.payto_uri
-  IS 'payto:// URI used to fund the reserve, may be NULL once reserve is funded';
+COMMENT ON COLUMN merchant_reward_reserve_keys.master_pub
+  IS 'Master public key of the exchange to which the reserve belongs';
 
 
-CREATE TABLE IF NOT EXISTS merchant_tips
-  (tip_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+CREATE TABLE IF NOT EXISTS merchant_rewards
+  (reward_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
   ,reserve_serial BIGINT NOT NULL
-     REFERENCES merchant_tip_reserves (reserve_serial) ON DELETE CASCADE
-  ,tip_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(tip_id)=64)
+     REFERENCES merchant_reward_reserves (reserve_serial) ON DELETE CASCADE
+  ,reward_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(reward_id)=64)
   ,justification VARCHAR NOT NULL
   ,next_url VARCHAR NOT NULL
   ,expiration INT8 NOT NULL
@@ -529,42 +584,42 @@ CREATE TABLE IF NOT EXISTS merchant_tips
   ,picked_up_frac INT4 NOT NULL DEFAULT 0
   ,was_picked_up BOOLEAN NOT NULL DEFAULT FALSE
   );
-CREATE INDEX IF NOT EXISTS merchant_tips_by_pickup_and_expiration
-  ON merchant_tips
+CREATE INDEX IF NOT EXISTS merchant_rewards_by_pickup_and_expiration
+  ON merchant_rewards
     (was_picked_up,expiration);
-COMMENT ON TABLE merchant_tips
-  IS 'tips that have been authorized';
-COMMENT ON COLUMN merchant_tips.amount_val
-  IS 'Overall tip amount';
-COMMENT ON COLUMN merchant_tips.picked_up_val
-  IS 'Tip amount left to be picked up';
-COMMENT ON COLUMN merchant_tips.reserve_serial
-  IS 'Reserve from which this tip is funded';
-COMMENT ON COLUMN merchant_tips.expiration
-  IS 'by when does the client have to pick up the tip';
-
-CREATE TABLE IF NOT EXISTS merchant_tip_pickups
+COMMENT ON TABLE merchant_rewards
+  IS 'rewards that have been authorized';
+COMMENT ON COLUMN merchant_rewards.amount_val
+  IS 'Overall reward amount';
+COMMENT ON COLUMN merchant_rewards.picked_up_val
+  IS 'Reward amount left to be picked up';
+COMMENT ON COLUMN merchant_rewards.reserve_serial
+  IS 'Reserve from which this reward is funded';
+COMMENT ON COLUMN merchant_rewards.expiration
+  IS 'by when does the client have to pick up the reward';
+
+CREATE TABLE IF NOT EXISTS merchant_reward_pickups
   (pickup_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY NOT NULL
-  ,tip_serial BIGINT NOT NULL
-      REFERENCES merchant_tips (tip_serial) ON DELETE CASCADE
+  ,reward_serial BIGINT NOT NULL
+      REFERENCES merchant_rewards (reward_serial) ON DELETE CASCADE
   ,pickup_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(pickup_id)=64)
   ,amount_val INT8 NOT NULL
   ,amount_frac INT4 NOT NULL
   );
-COMMENT ON TABLE merchant_tip_pickups
-  IS 'tips that have been picked up';
-COMMENT ON COLUMN merchant_tips.amount_val
+COMMENT ON TABLE merchant_reward_pickups
+  IS 'rewards that have been picked up';
+COMMENT ON COLUMN merchant_rewards.amount_val
   IS 'total transaction cost for all coins including withdraw fees';
 
-CREATE TABLE IF NOT EXISTS merchant_tip_pickup_signatures
+CREATE TABLE IF NOT EXISTS merchant_reward_pickup_signatures
   (pickup_serial INT8 NOT NULL
-     REFERENCES merchant_tip_pickups (pickup_serial) ON DELETE CASCADE
+     REFERENCES merchant_reward_pickups (pickup_serial) ON DELETE CASCADE
   ,coin_offset INT4 NOT NULL
   ,blind_sig BYTEA NOT NULL
   ,PRIMARY KEY (pickup_serial, coin_offset)
   );
-COMMENT ON TABLE merchant_tip_pickup_signatures
-  IS 'blind signatures we got from the exchange during the tip pickup';
+COMMENT ON TABLE merchant_reward_pickup_signatures
+  IS 'blind signatures we got from the exchange during the reward pickup';
 
 
 
@@ -573,6 +628,7 @@ CREATE TABLE IF NOT EXISTS merchant_kyc
 (kyc_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
 ,kyc_timestamp INT8 NOT NULL
 ,kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE)
+,aml_decision INT4 NOT NULL DEFAULT (0)
 ,exchange_sig BYTEA CHECK(LENGTH(exchange_sig)=64)
 ,exchange_pub BYTEA CHECK(LENGTH(exchange_pub)=32)
 ,exchange_kyc_serial INT8 NOT NULL DEFAULT(0)
@@ -593,11 +649,133 @@ COMMENT ON COLUMN merchant_kyc.exchange_sig
   IS 'signature of the exchange affirming the KYC passed (or NULL if exchange does not require KYC or not kyc_ok)';
 COMMENT ON COLUMN merchant_kyc.exchange_pub
   IS 'public key used with exchange_sig (or NULL if exchange_sig is NULL)';
+COMMENT ON COLUMN merchant_kyc.aml_decision
+  IS 'current AML decision for our account at the exchange';
 COMMENT ON COLUMN merchant_kyc.account_serial
   IS 'Which bank account of the merchant is the KYC status for';
 COMMENT ON COLUMN merchant_kyc.exchange_url
   IS 'Which exchange base URL is this KYC status valid for';
 
 
+CREATE TABLE IF NOT EXISTS merchant_template
+  (template_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+  ,merchant_serial BIGINT NOT NULL
+    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+  ,template_id VARCHAR NOT NULL
+  ,template_description VARCHAR NOT NULL
+  ,pos_key VARCHAR DEFAULT NULL
+  ,pos_algorithm INT NOT NULL DEFAULT (0)
+  ,template_contract VARCHAR NOT NULL -- in JSON format
+  ,UNIQUE (merchant_serial, template_id)
+  );
+COMMENT ON TABLE merchant_template
+  IS 'template used by the merchant (may be incomplete, frontend can override)';
+COMMENT ON COLUMN merchant_template.template_description
+  IS 'Human-readable template description';
+COMMENT ON COLUMN merchant_template.pos_key
+  IS 'A base64-encoded key of the point-of-sale. It will be use by the TOTP';
+COMMENT ON COLUMN merchant_template.pos_algorithm
+  IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
+COMMENT ON COLUMN merchant_template.template_contract
+  IS 'The template contract will contains some additional information.';
+
+
+CREATE TABLE IF NOT EXISTS merchant_webhook
+  (webhook_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+  ,merchant_serial BIGINT NOT NULL
+    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+  ,webhook_id VARCHAR NOT NULL
+  ,event_type VARCHAR NOT NULL
+  ,url VARCHAR NOT NULL
+  ,http_method VARCHAR NOT NULL
+  ,header_template VARCHAR
+  ,body_template VARCHAR
+  ,UNIQUE (merchant_serial, webhook_id)
+  );
+COMMENT ON TABLE merchant_webhook
+  IS 'webhook used by the merchant (may be incomplete, frontend can override)';
+COMMENT ON COLUMN merchant_webhook.event_type
+  IS 'Event of the webhook';
+COMMENT ON COLUMN merchant_webhook.url
+  IS 'URL to make the request to';
+COMMENT ON COLUMN merchant_webhook.http_method
+  IS 'http method use by the merchant';
+COMMENT ON COLUMN merchant_webhook.header_template
+  IS 'Template for the header of the webhook, to be modified based on trigger data';
+COMMENT ON COLUMN merchant_webhook.body_template
+  IS 'Template for the body of the webhook, to be modified based on trigger data';
+
+
+CREATE TABLE IF NOT EXISTS merchant_pending_webhooks
+  (webhook_pending_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+  ,merchant_serial BIGINT NOT NULL
+    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
+  ,webhook_serial BIGINT NOT NULL
+    REFERENCES merchant_webhook (webhook_serial) ON DELETE CASCADE
+  ,next_attempt INT8 NOT NULL DEFAULT(0)
+  ,retries INT4 NOT NULL DEFAULT(0)
+  ,url VARCHAR NOT NULL
+  ,http_method VARCHAR NOT NULL
+  ,header VARCHAR
+  ,body VARCHAR
+  ,UNIQUE (merchant_serial, webhook_pending_serial)
+  );
+COMMENT ON TABLE merchant_pending_webhooks
+  IS 'webhooks that still need to be executed by the merchant';
+COMMENT ON COLUMN merchant_pending_webhooks.url
+  IS 'URL to make the request to';
+COMMENT ON COLUMN merchant_pending_webhooks.webhook_serial
+  IS 'Reference to the configured webhook template';
+COMMENT ON COLUMN merchant_pending_webhooks.retries
+  IS 'How often have we tried this request so far';
+COMMENT ON COLUMN merchant_pending_webhooks.next_attempt
+  IS 'Time when we should make the next request to the webhook';
+COMMENT ON COLUMN merchant_pending_webhooks.http_method
+  IS 'http method use for the webhook';
+COMMENT ON COLUMN merchant_pending_webhooks.header
+  IS 'Header of the webhook';
+COMMENT ON COLUMN merchant_pending_webhooks.body
+  IS 'Body of the webhook';
+
+
+CREATE TABLE IF NOT EXISTS merchant_exchange_accounts
+  (mea_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
+  ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)
+  ,payto_uri VARCHAR NOT NULL
+  ,conversion_url VARCHAR
+  ,debit_restrictions VARCHAR NOT NULL
+  ,credit_restrictions VARCHAR NOT NULL
+  ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+  );
+COMMENT ON TABLE merchant_exchange_accounts
+ IS 'Here we store which bank accounts the exchange uses and with which constraints';
+COMMENT ON COLUMN merchant_exchange_accounts.master_pub
+ IS 'Master public key of the exchange with these accounts';
+COMMENT ON COLUMN merchant_exchange_accounts.payto_uri
+ IS 'RFC 8905 URI of the exchange bank account';
+COMMENT ON COLUMN merchant_exchange_accounts.conversion_url
+ IS 'NULL if this account does not require currency conversion';
+COMMENT ON COLUMN merchant_exchange_accounts.debit_restrictions
+ IS 'JSON array with account restrictions';
+COMMENT ON COLUMN merchant_exchange_accounts.credit_restrictions
+ IS 'JSON array with account restrictions';
+
+
+CREATE TABLE IF NOT EXISTS merchant_exchange_keys
+  (mek_serial BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+  ,master_pub BYTEA PRIMARY KEY CHECK (LENGTH(master_pub)=32)
+  ,keys_json VARCHAR NOT NULL
+  ,expiration_time INT8 NOT NULL
+  );
+COMMENT ON TABLE merchant_exchange_keys
+ IS 'Here we store the cached /keys response from an exchange in JSON format';
+COMMENT ON COLUMN merchant_exchange_keys.master_pub
+ IS 'Master public key of the exchange with these keys';
+COMMENT ON COLUMN merchant_exchange_keys.keys_json
+ IS 'JSON string of the /keys as generated by libtalerexchange';
+COMMENT ON COLUMN merchant_exchange_keys.expiration_time
+ IS 'When should this /keys object be deleted';
+
+
 -- Complete transaction
 COMMIT;
diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql
index 29dd0115..ef5642f8 100644
--- a/src/backenddb/merchant-0002.sql
+++ b/src/backenddb/merchant-0002.sql
@@ -18,21 +18,9 @@
 BEGIN;
 
 -- Check patch versioning is in place.
-SELECT _v.register_patch('merchant-0002', NULL, NULL);
+-- 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';
-
 -- Complete transaction
 COMMIT;
diff --git a/src/backenddb/merchant-0003.sql b/src/backenddb/merchant-0003.sql
index a3c8b484..69a3c57b 100644
--- a/src/backenddb/merchant-0003.sql
+++ b/src/backenddb/merchant-0003.sql
@@ -18,13 +18,9 @@
 BEGIN;
 
 -- Check patch versioning is in place.
-SELECT _v.register_patch('merchant-0003', NULL, NULL);
+-- SELECT _v.register_patch('merchant-0003', NULL, NULL);
 
 SET search_path TO merchant;
 
-ALTER TABLE merchant_exchange_wire_fees
-  DROP COLUMN wad_fee_val,
-  DROP COLUMN wad_fee_frac;
-
 -- Complete transaction
 COMMIT;
diff --git a/src/backenddb/merchant-0004.sql b/src/backenddb/merchant-0004.sql
index 5456573b..47fa71af 100644
--- a/src/backenddb/merchant-0004.sql
+++ b/src/backenddb/merchant-0004.sql
@@ -14,126 +14,11 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
-
--- @file merchant-0004.sql
--- @brief database helper functions for postgres used by the merchant and function for plugin_merchantdb_postgres.c
--- @author Priscilla Huang
-
-
 BEGIN;
 -- Check patch versioning is in place.
-SELECT _v.register_patch('merchant-0004', NULL, NULL);
+-- SELECT _v.register_patch('merchant-0004', NULL, NULL);
 
 SET search_path TO merchant;
 
-CREATE TABLE IF NOT EXISTS merchant_template
-  (template_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-  ,merchant_serial BIGINT NOT NULL
-    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
-  ,template_id VARCHAR NOT NULL
-  ,template_description VARCHAR NOT NULL
-  ,pos_key VARCHAR DEFAULT NULL
-  ,pos_algorithm INT NOT NULL DEFAULT (0)
-  ,template_contract VARCHAR NOT NULL -- in JSON format
-  ,UNIQUE (merchant_serial, template_id)
-  );
-COMMENT ON TABLE merchant_template
-  IS 'template used by the merchant (may be incomplete, frontend can override)';
-COMMENT ON COLUMN merchant_template.template_description
-  IS 'Human-readable template description';
-COMMENT ON COLUMN merchant_template.pos_key
-  IS 'A base64-encoded key of the point-of-sale. It will be use by the TOTP';
-COMMENT ON COLUMN merchant_template.pos_algorithm
-  IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
-COMMENT ON COLUMN merchant_template.template_contract
-  IS 'The template contract will contains some additional information.';
-
-
-CREATE TABLE IF NOT EXISTS merchant_webhook
-  (webhook_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-  ,merchant_serial BIGINT NOT NULL
-    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
-  ,webhook_id VARCHAR NOT NULL
-  ,event_type VARCHAR NOT NULL
-  ,url VARCHAR NOT NULL
-  ,http_method VARCHAR NOT NULL
-  ,header_template VARCHAR
-  ,body_template VARCHAR
-  ,UNIQUE (merchant_serial, webhook_id)
-  );
-COMMENT ON TABLE merchant_webhook
-  IS 'webhook used by the merchant (may be incomplete, frontend can override)';
-COMMENT ON COLUMN merchant_webhook.event_type
-  IS 'Event of the webhook';
-COMMENT ON COLUMN merchant_webhook.url
-  IS 'URL to make the request to';
-COMMENT ON COLUMN merchant_webhook.http_method
-  IS 'http method use by the merchant';
-COMMENT ON COLUMN merchant_webhook.header_template
-  IS 'Template for the header of the webhook, to be modified based on trigger data';
-COMMENT ON COLUMN merchant_webhook.body_template
-  IS 'Template for the body of the webhook, to be modified based on trigger data';
-
-
-CREATE TABLE IF NOT EXISTS merchant_pending_webhooks
-  (webhook_pending_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-  ,merchant_serial BIGINT NOT NULL
-    REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
-  ,webhook_serial BIGINT NOT NULL
-    REFERENCES merchant_webhook (webhook_serial) ON DELETE CASCADE
-  ,next_attempt INT8 NOT NULL DEFAULT(0)
-  ,retries INT4 NOT NULL DEFAULT(0)
-  ,url VARCHAR NOT NULL
-  ,http_method VARCHAR NOT NULL
-  ,header VARCHAR
-  ,body VARCHAR
-  ,UNIQUE (merchant_serial, webhook_pending_serial)
-  );
-COMMENT ON TABLE merchant_pending_webhooks
-  IS 'webhooks that still need to be executed by the merchant';
-COMMENT ON COLUMN merchant_pending_webhooks.url
-  IS 'URL to make the request to';
-COMMENT ON COLUMN merchant_pending_webhooks.webhook_serial
-  IS 'Reference to the configured webhook template';
-COMMENT ON COLUMN merchant_pending_webhooks.retries
-  IS 'How often have we tried this request so far';
-COMMENT ON COLUMN merchant_pending_webhooks.next_attempt
-  IS 'Time when we should make the next request to the webhook';
-COMMENT ON COLUMN merchant_pending_webhooks.http_method
-  IS 'http method use for the webhook';
-COMMENT ON COLUMN merchant_pending_webhooks.header
-  IS 'Header of the webhook';
-COMMENT ON COLUMN merchant_pending_webhooks.body
-  IS 'Body of the webhook';
-
-
-ALTER TABLE merchant_kyc
-  ADD COLUMN aml_decision INT4 NOT NULL DEFAULT (0);
-COMMENT ON COLUMN merchant_kyc.aml_decision
-  IS 'current AML decision for our account at the exchange';
-
-
-ALTER TABLE merchant_orders
-  ADD COLUMN pos_key VARCHAR DEFAULT NULL,
-  ADD COLUMN pos_algorithm INT NOT NULL DEFAULT (0);
-
-COMMENT ON COLUMN merchant_orders.pos_key
-  IS 'encoded based key which is used for the verification of payment';
-COMMENT ON COLUMN merchant_orders.pos_algorithm
-  IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
-
-
-
-ALTER TABLE merchant_contract_terms
-  ADD COLUMN pos_key VARCHAR DEFAULT NULL,
-  ADD COLUMN pos_algorithm INT NOT NULL DEFAULT (0);
-
-COMMENT  ON COLUMN merchant_contract_terms.pos_key
-  IS 'enconded based key which is used for the verification of payment';
-COMMENT ON COLUMN merchant_orders.pos_algorithm
-  IS 'algorithm to used to generate the confirmation code. It is link with the pos_key';
-
 
 COMMIT;
-
-
diff --git a/src/backenddb/merchant-0005.sql b/src/backenddb/merchant-0005.sql
index a0e283fa..b7bf4c91 100644
--- a/src/backenddb/merchant-0005.sql
+++ b/src/backenddb/merchant-0005.sql
@@ -18,73 +18,10 @@
 BEGIN;
 
 -- Check patch versioning is in place.
-SELECT _v.register_patch('merchant-0005', NULL, NULL);
+-- SELECT _v.register_patch('merchant-0005', NULL, NULL);
 
 SET search_path TO merchant;
 
-ALTER TABLE merchant_instances
-  ADD COLUMN user_type INT;
-COMMENT ON COLUMN merchant_instances.user_type
-  IS 'what type of user is this (individual or business)';
-
--- Column makes no sense for multi-account exchanges.  Instead, we should
--- lookup the various accounts of the exchange (by the master_pub) and return
--- all of them (with constraints).
-ALTER TABLE merchant_tip_reserve_keys
-  DROP COLUMN payto_uri,
-  ADD COLUMN master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32);
-
-ALTER TABLE merchant_transfers
-  ADD COLUMN ready_time INT8 NOT NULL DEFAULT (0),
-  ADD COLUMN failed BOOLEAN NOT NULL DEFAULT FALSE,
-  ADD COLUMN validation_status INT4 DEFAULT NULL;
-COMMENT ON COLUMN merchant_transfers.failed
-  IS 'set to true on permanent verification failures';
-COMMENT ON COLUMN merchant_transfers.validation_status
-  IS 'Taler error code describing the state of the validation';
-
-CREATE INDEX merchant_transfers_by_open
-  ON merchant_transfers
-  (ready_time ASC)
-  WHERE confirmed AND NOT (failed OR verified);
-COMMENT ON INDEX merchant_transfers_by_open
-  IS 'For select_open_transfers';
-
-
-ALTER TABLE merchant_accounts
-  ADD COLUMN credit_facade_url VARCHAR,
-  ADD COLUMN credit_facade_credentials VARCHAR,
-  ADD COLUMN last_bank_serial INT8 NOT NULL DEFAULT (0);
-COMMENT ON COLUMN merchant_accounts.credit_facade_url
-  IS 'Base URL of a facade where the merchant can inquire about incoming bank transactions into this account';
-COMMENT ON COLUMN merchant_accounts.credit_facade_credentials
-  IS 'JSON with credentials needed to access the credit facade';
-COMMENT ON COLUMN merchant_accounts.last_bank_serial
-  IS 'Serial number of the bank of the last transaction we successfully imported';
-
-
-CREATE TABLE IF NOT EXISTS merchant_exchange_accounts
-  (mea_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-  ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)
-  ,payto_uri VARCHAR NOT NULL
-  ,conversion_url VARCHAR
-  ,debit_restrictions VARCHAR NOT NULL
-  ,credit_restrictions VARCHAR NOT NULL
-  ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
-  );
-COMMENT ON TABLE merchant_exchange_accounts
- IS 'Here we store which bank accounts the exchange uses and with which constraints';
-COMMENT ON COLUMN merchant_exchange_accounts.master_pub
- IS 'Master public key of the exchange with these accounts';
-COMMENT ON COLUMN merchant_exchange_accounts.payto_uri
- IS 'RFC 8905 URI of the exchange bank account';
-COMMENT ON COLUMN merchant_exchange_accounts.conversion_url
- IS 'NULL if this account does not require currency conversion';
-COMMENT ON COLUMN merchant_exchange_accounts.debit_restrictions
- IS 'JSON array with account restrictions';
-COMMENT ON COLUMN merchant_exchange_accounts.credit_restrictions
- IS 'JSON array with account restrictions';
-
 
 -- Complete transaction
 COMMIT;
diff --git a/src/testing/test_merchant_api_home/taler/exchange-offline/secm_tofus.pub b/src/testing/test_merchant_api_home/taler/exchange-offline/secm_tofus.pub
deleted file mode 100644
index 507559b0..00000000
--- a/src/testing/test_merchant_api_home/taler/exchange-offline/secm_tofus.pub
+++ /dev/null