-- -- This file is part of TALER -- 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 -- Foundation; either version 3, or (at your option) any later version. -- -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License along with -- TALER; see the file COPYING. If not, see -- -- @file merchant-0001.sql -- @brief database schema for the merchant -- @author Christian Grothoff -- @author Priscilla Huang -- Everything in one big transaction BEGIN; -- Check patch versioning is in place. SELECT _v.register_patch('merchant-0001', NULL, NULL); CREATE SCHEMA merchant; 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 (wirefee_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) ,h_wire_method BYTEA NOT NULL CHECK (LENGTH(h_wire_method)=64) ,start_date INT8 NOT NULL ,end_date INT8 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) ); COMMENT ON TABLE merchant_exchange_wire_fees IS 'Here we store proofs of the wire fee structure of the various exchanges'; COMMENT ON COLUMN merchant_exchange_wire_fees.master_pub IS 'Master public key of the exchange with these wire fees'; CREATE TABLE IF NOT EXISTS merchant_exchange_signing_keys (signkey_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) ,exchange_pub BYTEA NOT NULL CHECK (LENGTH(exchange_pub)=32) ,start_date INT8 NOT NULL ,expire_date INT8 NOT NULL ,end_date INT8 NOT NULL ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64), UNIQUE (exchange_pub, start_date, master_pub) ); COMMENT ON TABLE merchant_exchange_signing_keys IS 'Here we store proofs of the exchange online signing keys being signed by the exchange master key'; COMMENT ON COLUMN merchant_exchange_signing_keys.master_pub IS 'Master public key of the exchange with these online signing keys'; -------------------------- Instances --------------------------- CREATE TABLE IF NOT EXISTS merchant_instances (merchant_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,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 TEXT NOT NULL UNIQUE ,merchant_name TEXT NOT NULL ,website TEXT ,email TEXT ,logo BYTEA ,address BYTEA NOT NULL ,jurisdiction BYTEA 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'; COMMENT ON COLUMN merchant_instances.merchant_id IS 'identifier of the merchant as used in the base URL (required)'; 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)'; 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.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'; 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), merchant_serial BIGINT PRIMARY KEY REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE ); COMMENT ON TABLE merchant_keys IS 'private keys of instances that have not been deleted'; CREATE TABLE IF NOT EXISTS merchant_accounts (account_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,merchant_serial BIGINT NOT NULL 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 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) ); COMMENT ON TABLE merchant_accounts IS 'bank accounts of the instances'; COMMENT ON COLUMN merchant_accounts.h_wire IS 'salted hash of payto_uri'; COMMENT ON COLUMN merchant_accounts.salt IS 'salt used when hashing payto_uri into h_wire'; 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 --------------------------- 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 TEXT NOT NULL ,description TEXT NOT NULL ,description_i18n BYTEA NOT NULL ,unit TEXT NOT NULL ,image BYTEA NOT NULL ,taxes BYTEA 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 ,address BYTEA NOT NULL ,next_restock INT8 NOT NULL ,minimum_age INT4 NOT NULL DEFAULT 0 ,UNIQUE (merchant_serial, product_id) ); COMMENT ON TABLE merchant_inventory IS 'products offered by the merchant (may be incomplete, frontend can override)'; COMMENT ON COLUMN merchant_inventory.description IS 'Human-readable product description'; COMMENT ON COLUMN merchant_inventory.description_i18n IS 'JSON map from IETF BCP 47 language tags to localized descriptions'; COMMENT ON COLUMN merchant_inventory.unit IS 'Unit of sale for the product (liters, kilograms, packages)'; 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 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'; COMMENT ON COLUMN merchant_inventory.total_sold IS 'Number of products sold, must be below total_stock, non-negative, may never be lowered'; COMMENT ON COLUMN merchant_inventory.total_lost IS 'Number of products that used to be in stock but were lost (spoiled, damaged), may never be lowered; total_stock >= total_sold + total_lost must always hold'; COMMENT ON COLUMN merchant_inventory.address IS 'JSON formatted Location of where the product is stocked'; COMMENT ON COLUMN merchant_inventory.next_restock IS 'GNUnet absolute time i ndicating when the next restock is expected. 0 for unknown.'; COMMENT ON COLUMN merchant_inventory.minimum_age IS 'Minimum age of the customer in years, to be used if an exchange supports the age restriction extension.'; CREATE TABLE IF NOT EXISTS merchant_inventory_locks (product_serial BIGINT NOT NULL REFERENCES merchant_inventory (product_serial) -- NO "ON DELETE CASCADE": locks prevent deletion! ,lock_uuid BYTEA NOT NULL CHECK (LENGTH(lock_uuid)=16) ,total_locked BIGINT NOT NULL ,expiration INT8 NOT NULL ); CREATE INDEX IF NOT EXISTS merchant_inventory_locks_by_expiration ON merchant_inventory_locks (expiration); CREATE INDEX IF NOT EXISTS merchant_inventory_locks_by_uuid ON merchant_inventory_locks (lock_uuid); COMMENT ON TABLE merchant_inventory_locks IS 'locks on inventory helt by shopping carts; note that locks MAY not be honored if merchants increase total_lost for inventory'; COMMENT ON COLUMN merchant_inventory_locks.total_locked IS 'how many units of the product does this lock reserve'; COMMENT ON COLUMN merchant_inventory_locks.expiration IS 'when does this lock automatically expire (if no order is created)'; ---------------- Orders and contracts --------------------------- 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 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 IS 'Orders we offered to a customer, but that have not yet been claimed'; COMMENT ON COLUMN merchant_orders.contract_terms IS 'Claiming changes the contract_terms, hence we have no hash of the terms in this table'; COMMENT ON COLUMN merchant_orders.h_post_data IS 'Hash of the POST request that created this order, for idempotency checks'; COMMENT ON COLUMN merchant_orders.claim_token IS 'Token optionally used to authorize the wallet to claim the order. All zeros (not NULL) if not used'; 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); CREATE INDEX IF NOT EXISTS merchant_orders_by_creation_time ON merchant_orders (creation_time); CREATE TABLE IF NOT EXISTS merchant_order_locks (product_serial BIGINT NOT NULL REFERENCES merchant_inventory (product_serial) -- NO "ON DELETE CASCADE": locks prevent deletion! ,total_locked BIGINT NOT NULL ,order_serial BIGINT NOT NULL REFERENCES merchant_orders (order_serial) ON DELETE CASCADE ); CREATE INDEX IF NOT EXISTS merchant_orders_locks_by_order_and_product ON merchant_order_locks (order_serial, product_serial); COMMENT ON TABLE merchant_order_locks IS 'locks on orders awaiting claim and payment; note that locks MAY not be honored if merchants increase total_lost for inventory'; COMMENT ON COLUMN merchant_order_locks.total_locked IS 'how many units of the product does this lock reserve'; 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 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 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) ); COMMENT ON TABLE merchant_contract_terms IS 'Contracts are orders that have been claimed by a wallet'; COMMENT ON COLUMN merchant_contract_terms.order_id IS 'Not a foreign key into merchant_orders because paid contracts persist after expiration'; 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 IS 'true implies the customer paid for this contract; order should be DELETEd from merchant_orders once paid is set to release merchant_order_locks; paid remains true even if the payment was later refunded'; COMMENT ON COLUMN merchant_contract_terms.wired IS 'true implies the exchange wired us the full amount for all non-refunded payments under this contract'; COMMENT ON COLUMN merchant_contract_terms.fulfillment_url IS 'also included in contract_terms, but we need it here to SELECT on it during repurchase detection; can be NULL if the contract has no fulfillment URL'; 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'; CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_expiration ON merchant_contract_terms (merchant_serial,pay_deadline); COMMENT ON INDEX merchant_contract_terms_by_merchant_and_expiration IS 'for delete_contract_terms'; CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_expiration ON merchant_contract_terms (paid,pay_deadline); COMMENT ON INDEX merchant_contract_terms_by_expiration IS 'for unlock_contracts'; CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_payment ON merchant_contract_terms (merchant_serial,paid); CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_session_and_fulfillment ON merchant_contract_terms (merchant_serial,fulfillment_url,session_id); ---------------- Payment and refunds --------------------------- 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 ,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, exchange_sig) ); COMMENT ON TABLE merchant_deposit_confirmations IS 'Table with the deposit confirmations for each coin we deposited at the exchange'; COMMENT ON COLUMN merchant_deposit_confirmations.signkey_serial IS 'Online signing key of the exchange on the deposit confirmation'; COMMENT ON COLUMN merchant_deposit_confirmations.deposit_timestamp IS 'Time when the exchange generated the deposit confirmation'; COMMENT ON COLUMN merchant_deposit_confirmations.exchange_sig IS 'Signature of the exchange over the deposit confirmation'; 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 REFERENCES merchant_contract_terms (order_serial) ON DELETE CASCADE ,rtransaction_id BIGINT NOT NULL ,refund_timestamp INT8 NOT NULL ,coin_pub BYTEA 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 IS 'Refunds approved by the merchant (backoffice) logic, excludes abort refunds'; COMMENT ON COLUMN merchant_refunds.rtransaction_id IS 'Needed for uniqueness in case a refund is increased for the same order'; COMMENT ON COLUMN merchant_refunds.refund_timestamp IS 'Needed for grouping of refunds in the wallet UI; has no semantics in the protocol (only for UX), but should be from the time when the merchant internally approved the refund'; CREATE INDEX IF NOT EXISTS merchant_refunds_by_coin_and_order ON merchant_refunds (coin_pub,order_serial); CREATE TABLE IF NOT EXISTS merchant_refund_proofs (refund_serial BIGINT PRIMARY KEY REFERENCES merchant_refunds (refund_serial) ON DELETE CASCADE ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) ,signkey_serial BIGINT NOT NULL REFERENCES merchant_exchange_signing_keys (signkey_serial) ON DELETE CASCADE ); COMMENT ON TABLE merchant_refund_proofs IS 'Refunds confirmed by the exchange (not all approved refunds are grabbed by the wallet)'; -------------------- Wire transfers --------------------------- CREATE TABLE IF NOT EXISTS merchant_transfers (credit_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,exchange_url TEXT NOT NULL ,wtid BYTEA CHECK (LENGTH(wtid)=32) ,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) ); COMMENT ON TABLE merchant_transfers IS 'table represents the information provided by the (trusted) merchant about incoming wire transfers'; 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 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 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) ); 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 IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the exchange'; CREATE TABLE IF NOT EXISTS merchant_transfer_to_coin (deposit_serial BIGINT UNIQUE NOT NULL REFERENCES merchant_deposits (deposit_serial) ON DELETE CASCADE ,credit_serial BIGINT NOT NULL REFERENCES merchant_transfers (credit_serial) ON DELETE CASCADE ,offset_in_exchange_list INT8 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 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 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 taler_amount_currency NOT NULL ,credit_serial BIGINT NOT NULL REFERENCES merchant_transfers (credit_serial) ,execution_time INT8 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) ,UNIQUE(deposit_serial,credit_serial) ); COMMENT ON TABLE merchant_deposit_to_transfer IS 'Mapping of deposits to (possibly unconfirmed) wire transfers; NOTE: not used yet'; COMMENT ON COLUMN merchant_deposit_to_transfer.execution_time IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant'; -------------------------- Rewards --------------------------- 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 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_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_reward_reserves.merchant_initial_balance IS 'Set to the initial balance the merchant told us when creating the reserve'; 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_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_reward_reserves_by_merchant_serial_and_creation_time ON merchant_reward_reserves (merchant_serial,creation_time); CREATE INDEX IF NOT EXISTS merchant_reward_reserves_by_exchange_balance ON merchant_reward_reserves (exchange_initial_balance); CREATE TABLE IF NOT EXISTS merchant_reward_reserve_keys (reserve_serial BIGINT NOT NULL UNIQUE REFERENCES merchant_reward_reserves (reserve_serial) ON DELETE CASCADE ,reserve_priv BYTEA NOT NULL UNIQUE CHECK (LENGTH(reserve_priv)=32) ,exchange_url TEXT NOT NULL ,master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32) ); COMMENT ON TABLE merchant_reward_reserves IS 'private keys of reserves that have not been deleted'; 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_rewards (reward_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,reserve_serial BIGINT 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 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_rewards_by_pickup_and_expiration ON merchant_rewards (was_picked_up,expiration); 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 ,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 taler_amount_currency NOT NULL ); 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_reward_pickup_signatures (pickup_serial INT8 NOT NULL 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_reward_pickup_signatures IS 'blind signatures we got from the exchange during the reward pickup'; 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 TEXT NOT NULL ,PRIMARY KEY (account_serial,exchange_url) ); COMMENT ON TABLE merchant_kyc IS 'Status of the KYC process of a merchant account at an exchange'; COMMENT ON COLUMN merchant_kyc.kyc_timestamp IS 'Last time we checked our KYC status at the exchange. Useful to re-check if the status is very stale. Also the timestamp used for the exchange signature (if present).'; COMMENT ON COLUMN merchant_kyc.exchange_kyc_serial IS 'Number to use in the KYC-endpoints of the exchange to check the KYC status or begin the KYC process. 0 if we do not know it yet.'; COMMENT ON COLUMN merchant_kyc.kyc_ok IS 'true if the KYC check was passed successfully'; 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;