diff options
Diffstat (limited to 'src/backenddb/merchant-0001.sql')
| -rw-r--r-- | src/backenddb/merchant-0001.sql | 516 |
1 files changed, 378 insertions, 138 deletions
diff --git a/src/backenddb/merchant-0001.sql b/src/backenddb/merchant-0001.sql index cd4ae9f3..2adb9996 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; @@ -25,6 +30,15 @@ COMMENT ON SCHEMA merchant IS 'taler-merchant data'; SET search_path TO merchant; +CREATE TYPE taler_amount_currency + AS + (val INT8 + ,frac INT4 + ,curr VARCHAR(12) + ); +COMMENT ON TYPE taler_amount_currency + IS 'Stores an amount, fraction is in units of 1/100000000 of the base value'; + ---------------- Exchange information --------------------------- CREATE TABLE IF NOT EXISTS merchant_exchange_wire_fees @@ -33,12 +47,8 @@ CREATE TABLE IF NOT EXISTS merchant_exchange_wire_fees ,h_wire_method BYTEA NOT NULL CHECK (LENGTH(h_wire_method)=64) ,start_date INT8 NOT NULL ,end_date INT8 NOT NULL - ,wire_fee_val INT8 NOT NULL - ,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 + ,wire_fee taler_amount_currency NOT NULL + ,closing_fee taler_amount_currency NOT NULL ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) ,UNIQUE (master_pub,h_wire_method,start_date) ); @@ -70,17 +80,17 @@ CREATE TABLE IF NOT EXISTS merchant_instances ,merchant_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(merchant_pub)=32) ,auth_hash BYTEA CHECK(LENGTH(auth_hash)=64) ,auth_salt BYTEA CHECK(LENGTH(auth_salt)=32) - ,merchant_id VARCHAR NOT NULL UNIQUE - ,merchant_name VARCHAR NOT NULL + ,merchant_id TEXT NOT NULL UNIQUE + ,merchant_name TEXT NOT NULL + ,website TEXT + ,email TEXT + ,logo BYTEA ,address BYTEA NOT NULL ,jurisdiction BYTEA NOT NULL - ,default_max_deposit_fee_val INT8 NOT NULL - ,default_max_deposit_fee_frac INT4 NOT NULL - ,default_max_wire_fee_val INT8 NOT NULL - ,default_max_wire_fee_frac INT4 NOT NULL - ,default_wire_fee_amortization INT4 NOT NULL + ,use_stefan BOOLEAN NOT NULL DEFAULT TRUE ,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'; @@ -89,15 +99,50 @@ COMMENT ON COLUMN merchant_instances.merchant_id COMMENT ON COLUMN merchant_instances.merchant_name IS 'legal name of the merchant as a simple string (required)'; COMMENT ON COLUMN merchant_instances.address - IS 'physical address of the merchant as a Location in JSON format (required)'; + 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)'; + 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.use_stefan + IS 'use STEFAN curve of exchange to determine acceptable fees (unless given explicitly)'; +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'; + 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)'; +CREATE TABLE IF NOT EXISTS merchant_login_tokens + (token BYTEA NOT NULL UNIQUE CHECK (LENGTH(token)=32), + creation_time INT8 NOT NULL, + expiration_time INT8 NOT NULL, + validity_scope INT4 NOT NULL, + merchant_serial BIGINT + REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE + ); +COMMENT ON TABLE merchant_login_tokens + IS 'login tokens that have been created for the given instance'; +COMMENT ON COLUMN merchant_login_tokens.token + IS 'binary value of the login token'; +COMMENT ON COLUMN merchant_login_tokens.creation_time + IS 'time when the token was created; currently not used, potentially useful in the future for a forced logout of all tokens issued before a certain date'; +COMMENT ON COLUMN merchant_login_tokens.expiration_time + IS 'determines when the token expires'; +COMMENT ON COLUMN merchant_login_tokens.validity_scope + IS 'identifies the operations for which the token is valid'; +COMMENT ON COLUMN merchant_login_tokens.merchant_serial + IS 'identifies the instance for which the token is valid'; + +CREATE INDEX IF NOT EXISTS merchant_login_tokens_by_expiration + ON merchant_login_tokens + (expiration_time); + CREATE TABLE IF NOT EXISTS merchant_keys (merchant_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(merchant_priv)=32), @@ -113,7 +158,10 @@ 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) - ,payto_uri VARCHAR NOT NULL + ,credit_facade_url TEXT + ,credit_facade_credentials TEXT + ,last_bank_serial INT8 NOT NULL DEFAULT (0) + ,payto_uri TEXT NOT NULL ,active BOOLEAN NOT NULL ,UNIQUE (merchant_serial,payto_uri) ,UNIQUE (h_wire) @@ -128,6 +176,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 --------------------------- @@ -136,14 +190,13 @@ CREATE TABLE IF NOT EXISTS merchant_inventory (product_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE - ,product_id VARCHAR NOT NULL - ,description VARCHAR NOT NULL + ,product_id TEXT NOT NULL + ,description TEXT NOT NULL ,description_i18n BYTEA NOT NULL - ,unit VARCHAR NOT NULL + ,unit TEXT NOT NULL ,image BYTEA NOT NULL ,taxes BYTEA NOT NULL - ,price_val INT8 NOT NULL - ,price_frac INT4 NOT NULL + ,price taler_amount_currency NOT NULL ,total_stock BIGINT NOT NULL ,total_sold BIGINT NOT NULL DEFAULT 0 ,total_lost BIGINT NOT NULL DEFAULT 0 @@ -164,7 +217,7 @@ COMMENT ON COLUMN merchant_inventory.image IS 'NOT NULL, but can be 0 bytes; must contain an ImageDataUrl'; COMMENT ON COLUMN merchant_inventory.taxes IS 'JSON array containing taxes the merchant pays, must be JSON, but can be just "[]"'; -COMMENT ON COLUMN merchant_inventory.price_val +COMMENT ON COLUMN merchant_inventory.price IS 'Current price of one unit of the product'; COMMENT ON COLUMN merchant_inventory.total_stock IS 'A value of -1 is used for unlimited (electronic good), may never be lowered'; @@ -207,12 +260,14 @@ CREATE TABLE IF NOT EXISTS merchant_orders (order_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE - ,order_id VARCHAR NOT NULL + ,order_id TEXT NOT NULL ,claim_token BYTEA NOT NULL CHECK (LENGTH(claim_token)=16) ,h_post_data BYTEA NOT NULL CHECK (LENGTH(h_post_data)=64) ,pay_deadline INT8 NOT NULL ,creation_time INT8 NOT NULL ,contract_terms BYTEA NOT NULL + ,pos_key TEXT DEFAULT NULL + ,pos_algorithm INT NOT NULL DEFAULT (0) ,UNIQUE (merchant_serial, order_id) ); COMMENT ON TABLE merchant_orders @@ -227,6 +282,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); @@ -253,16 +314,19 @@ CREATE TABLE IF NOT EXISTS merchant_contract_terms (order_serial BIGINT PRIMARY KEY ,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE - ,order_id VARCHAR NOT NULL + ,order_id TEXT NOT NULL ,contract_terms BYTEA NOT NULL + ,wallet_data TEXT DEFAULT NULL ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) ,creation_time INT8 NOT NULL ,pay_deadline INT8 NOT NULL ,refund_deadline INT8 NOT NULL ,paid BOOLEAN DEFAULT FALSE NOT NULL ,wired BOOLEAN DEFAULT FALSE NOT NULL - ,fulfillment_url VARCHAR - ,session_id VARCHAR DEFAULT '' NOT NULL + ,fulfillment_url TEXT + ,session_id TEXT DEFAULT '' NOT NULL + ,pos_key TEXT 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) @@ -275,8 +339,12 @@ COMMENT ON COLUMN merchant_contract_terms.merchant_serial IS 'Identifies the instance offering the contract'; COMMENT ON COLUMN merchant_contract_terms.contract_terms IS 'These contract terms include the wallet nonce'; +COMMENT ON COLUMN merchant_contract_terms.wallet_data + IS 'Data provided by the wallet when paying for the contract (subcontract selection, blinded tokens, etc.)'; COMMENT ON COLUMN merchant_contract_terms.h_contract_terms IS 'Hash over contract_terms'; +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.refund_deadline IS 'By what times do refunds have to be approved (useful to reject refund requests)'; COMMENT ON COLUMN merchant_contract_terms.paid @@ -289,6 +357,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'; @@ -312,39 +385,61 @@ CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_session_and_fulfi ---------------- Payment and refunds --------------------------- -CREATE TABLE IF NOT EXISTS merchant_deposits - (deposit_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY +CREATE TABLE IF NOT EXISTS merchant_deposit_confirmations + (deposit_confirmation_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,order_serial BIGINT REFERENCES merchant_contract_terms (order_serial) ON DELETE CASCADE ,deposit_timestamp INT8 NOT NULL - ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) - ,exchange_url VARCHAR NOT NULL - ,amount_with_fee_val INT8 NOT NULL - ,amount_with_fee_frac INT4 NOT NULL - ,deposit_fee_val INT8 NOT NULL - ,deposit_fee_frac INT4 NOT NULL - ,refund_fee_val INT8 NOT NULL - ,refund_fee_frac INT4 NOT NULL - ,wire_fee_val INT8 NOT NULL - ,wire_fee_frac INT4 NOT NULL + ,exchange_url TEXT NOT NULL + ,total_without_fee taler_amount_currency NOT NULL + ,wire_fee taler_amount_currency NOT NULL ,signkey_serial BIGINT NOT NULL REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) ,account_serial BIGINT NOT NULL REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE - ,UNIQUE (order_serial, coin_pub) + ,UNIQUE (order_serial, exchange_sig) ); -COMMENT ON TABLE merchant_deposits +COMMENT ON TABLE merchant_deposit_confirmations IS 'Table with the deposit confirmations for each coin we deposited at the exchange'; -COMMENT ON COLUMN merchant_deposits.signkey_serial +COMMENT ON COLUMN merchant_deposit_confirmations.signkey_serial IS 'Online signing key of the exchange on the deposit confirmation'; -COMMENT ON COLUMN merchant_deposits.deposit_timestamp +COMMENT ON COLUMN merchant_deposit_confirmations.deposit_timestamp IS 'Time when the exchange generated the deposit confirmation'; -COMMENT ON COLUMN merchant_deposits.exchange_sig +COMMENT ON COLUMN merchant_deposit_confirmations.exchange_sig IS 'Signature of the exchange over the deposit confirmation'; -COMMENT ON COLUMN merchant_deposits.wire_fee_val +COMMENT ON COLUMN merchant_deposit_confirmations.wire_fee IS 'We MAY want to see if we should try to get this via merchant_exchange_wire_fees (not sure, may be too complicated with the date range, etc.)'; + +CREATE TABLE IF NOT EXISTS merchant_deposits + (deposit_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY + ,coin_offset INT4 NOT NULL + ,deposit_confirmation_serial BIGINT NOT NULL + REFERENCES merchant_deposit_confirmations (deposit_confirmation_serial) ON DELETE CASCADE + ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) + ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64) + ,amount_with_fee taler_amount_currency NOT NULL + ,deposit_fee taler_amount_currency NOT NULL + ,refund_fee taler_amount_currency NOT NULL + ,UNIQUE (deposit_confirmation_serial, coin_pub) + ); +COMMENT ON TABLE merchant_deposits + IS 'Table with the deposit details for each coin we deposited at the exchange'; +COMMENT ON COLUMN merchant_deposits.coin_offset + IS 'Offset of this coin in the batch'; +COMMENT ON COLUMN merchant_deposits.deposit_confirmation_serial + IS 'Reference to the deposit confirmation of the exchange'; +COMMENT ON COLUMN merchant_deposits.coin_pub + IS 'Public key of the coin that was deposited'; +COMMENT ON COLUMN merchant_deposits.amount_with_fee + IS 'Total amount (incl. fee) of the coin that was deposited'; +COMMENT ON COLUMN merchant_deposits.deposit_fee + IS 'Deposit fee (for this coin) that was paid'; +COMMENT ON COLUMN merchant_deposits.refund_fee + IS 'How high would the refund fee be (for this coin)'; + + CREATE TABLE IF NOT EXISTS merchant_refunds (refund_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,order_serial BIGINT NOT NULL @@ -352,9 +447,8 @@ CREATE TABLE IF NOT EXISTS merchant_refunds ,rtransaction_id BIGINT NOT NULL ,refund_timestamp INT8 NOT NULL ,coin_pub BYTEA NOT NULL - ,reason VARCHAR NOT NULL - ,refund_amount_val INT8 NOT NULL - ,refund_amount_frac INT4 NOT NULL + ,reason TEXT NOT NULL + ,refund_amount taler_amount_currency NOT NULL ,UNIQUE (order_serial, coin_pub, rtransaction_id) ); COMMENT ON TABLE merchant_deposits @@ -380,13 +474,15 @@ COMMENT ON TABLE merchant_refund_proofs -------------------- Wire transfers --------------------------- CREATE TABLE IF NOT EXISTS merchant_transfers - (credit_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY - ,exchange_url VARCHAR NOT NULL + (credit_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY + ,exchange_url TEXT NOT NULL ,wtid BYTEA CHECK (LENGTH(wtid)=32) - ,credit_amount_val INT8 NOT NULL - ,credit_amount_frac INT4 NOT NULL - ,account_serial BIGINT NOT NULL - REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE + ,credit_amount taler_amount_currency 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) @@ -397,18 +493,28 @@ COMMENT ON COLUMN merchant_transfers.verified IS 'true once we got an acceptable response from the exchange for this transfer'; 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 +COMMENT ON COLUMN merchant_transfers.credit_amount 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 REFERENCES merchant_transfers (credit_serial) ON DELETE CASCADE ,signkey_serial BIGINT NOT NULL REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE - ,wire_fee_val INT8 NOT NULL - ,wire_fee_frac INT4 NOT NULL - ,credit_amount_val INT8 NOT NULL - ,credit_amount_frac INT4 NOT NULL + ,wire_fee taler_amount_currency NOT NULL + ,credit_amount taler_amount_currency NOT NULL ,execution_time INT8 NOT NULL ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) ); @@ -416,7 +522,7 @@ COMMENT ON TABLE merchant_transfer_signatures IS 'table represents the main information returned from the /transfer request to the exchange.'; COMMENT ON COLUMN merchant_transfer_signatures.execution_time IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant'; -COMMENT ON COLUMN merchant_transfer_signatures.credit_amount_val +COMMENT ON COLUMN merchant_transfer_signatures.credit_amount IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the exchange'; @@ -426,26 +532,23 @@ CREATE TABLE IF NOT EXISTS merchant_transfer_to_coin ,credit_serial BIGINT NOT NULL REFERENCES merchant_transfers (credit_serial) ON DELETE CASCADE ,offset_in_exchange_list INT8 NOT NULL - ,exchange_deposit_value_val INT8 NOT NULL - ,exchange_deposit_value_frac INT4 NOT NULL - ,exchange_deposit_fee_val INT8 NOT NULL - ,exchange_deposit_fee_frac INT4 NOT NULL + ,exchange_deposit_value taler_amount_currency NOT NULL + ,exchange_deposit_fee taler_amount_currency NOT NULL ); CREATE INDEX IF NOT EXISTS merchant_transfers_by_credit ON merchant_transfer_to_coin (credit_serial); COMMENT ON TABLE merchant_transfer_to_coin IS 'Mapping of (credit) transfers to (deposited) coins'; -COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_value_val +COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_value IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits minus refunds'; -COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_fee_val +COMMENT ON COLUMN merchant_transfer_to_coin.exchange_deposit_fee IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits'; CREATE TABLE IF NOT EXISTS merchant_deposit_to_transfer (deposit_serial BIGINT NOT NULL REFERENCES merchant_deposits (deposit_serial) ON DELETE CASCADE - ,coin_contribution_value_val INT8 NOT NULL - ,coin_contribution_value_frac INT4 NOT NULL + ,coin_contribution_value taler_amount_currency NOT NULL ,credit_serial BIGINT NOT NULL REFERENCES merchant_transfers (credit_serial) ,execution_time INT8 NOT NULL @@ -460,111 +563,105 @@ 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 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE ,creation_time INT8 NOT NULL ,expiration INT8 NOT NULL - ,merchant_initial_balance_val INT8 NOT NULL - ,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 + ,merchant_initial_balance taler_amount_currency NOT NULL + ,exchange_initial_balance taler_amount_currency NOT NULL + ,rewards_committed taler_amount_currency NOT NULL + ,rewards_picked_up taler_amount_currency NOT NULL ); -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 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 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 + IS 'Amount of outstanding approved rewards that have not been picked up'; +COMMENT ON COLUMN merchant_reward_reserves.rewards_picked_up + 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 - (exchange_initial_balance_val,exchange_initial_balance_frac); +CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_exchange_balance + ON merchant_reward_reserves + (exchange_initial_balance); -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 + ,exchange_url TEXT NOT NULL + ,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) - ,justification VARCHAR NOT NULL - ,next_url VARCHAR NOT NULL + REFERENCES merchant_reward_reserves (reserve_serial) ON DELETE CASCADE + ,reward_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(reward_id)=64) + ,justification TEXT NOT NULL + ,next_url TEXT NOT NULL ,expiration INT8 NOT NULL - ,amount_val INT8 NOT NULL - ,amount_frac INT4 NOT NULL - ,picked_up_val INT8 NOT NULL DEFAULT 0 - ,picked_up_frac INT4 NOT NULL DEFAULT 0 + ,amount taler_amount_currency NOT NULL + ,picked_up taler_amount_currency NOT NULL ,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 + IS 'Overall reward amount'; +COMMENT ON COLUMN merchant_rewards.picked_up + 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 + ,amount taler_amount_currency 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 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,12 +670,13 @@ 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) ,account_serial INT8 NOT NULL REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE -,exchange_url VARCHAR NOT NULL +,exchange_url TEXT NOT NULL ,PRIMARY KEY (account_serial,exchange_url) ); COMMENT ON TABLE merchant_kyc @@ -593,11 +691,153 @@ 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_otp_devices + (otp_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY + ,merchant_serial BIGINT NOT NULL + REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE + ,otp_id TEXT NOT NULL + ,otp_description TEXT NOT NULL + ,otp_key TEXT DEFAULT NULL + ,otp_algorithm INT NOT NULL DEFAULT (0) + ,otp_ctr INT8 NOT NULL DEFAULT (0) + ,UNIQUE (merchant_serial, otp_id) + ); +COMMENT ON TABLE merchant_otp_devices + IS 'OTP device owned by a merchant'; +COMMENT ON COLUMN merchant_otp_devices.otp_description + IS 'Human-readable OTP device description'; +COMMENT ON COLUMN merchant_otp_devices.otp_key + IS 'A base64-encoded key of the point-of-sale. It will be use by the OTP device'; +COMMENT ON COLUMN merchant_otp_devices.otp_algorithm + IS 'algorithm to used to generate the confirmation code. It is linked with the otp_key and otp_ctr'; +COMMENT ON COLUMN merchant_otp_devices.otp_ctr + IS 'counter for counter-based OTP generators'; + + +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 TEXT NOT NULL + ,template_description TEXT NOT NULL + ,otp_device_id BIGINT + REFERENCES merchant_otp_devices (otp_serial) ON DELETE SET NULL + ,template_contract TEXT 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.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 TEXT NOT NULL + ,event_type TEXT NOT NULL + ,url TEXT NOT NULL + ,http_method TEXT NOT NULL + ,header_template TEXT + ,body_template TEXT + ,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 TEXT NOT NULL + ,http_method TEXT NOT NULL + ,header TEXT + ,body TEXT + ,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 TEXT NOT NULL + ,conversion_url TEXT + ,debit_restrictions TEXT NOT NULL + ,credit_restrictions TEXT 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 + ,exchange_url TEXT PRIMARY KEY + ,keys_json TEXT 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.exchange_url + IS 'Base URL 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; |