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