merchant-0002.sql (7452B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2023 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 -- 16 17 -- @file merchant-0002.sql 18 -- @brief database schema for the merchant 19 -- @author Christian Blättler 20 21 -- Everything in one big transaction 22 BEGIN; 23 24 -- Check patch versioning is in place. 25 SELECT _v.register_patch('merchant-0002', NULL, NULL); 26 27 SET search_path TO merchant; 28 29 ALTER TABLE merchant_orders 30 ADD COLUMN fulfillment_url TEXT DEFAULT NULL 31 ,ADD COLUMN session_id TEXT DEFAULT '' NOT NULL; 32 33 COMMENT ON COLUMN merchant_orders.fulfillment_url 34 IS 'URL where the wallet will redirect the user upon payment'; 35 COMMENT ON COLUMN merchant_orders.session_id 36 IS 'session_id to which the payment will be bound'; 37 38 39 CREATE INDEX IF NOT EXISTS merchant_orders_by_merchant_and_session 40 ON merchant_orders 41 (merchant_serial,session_id); 42 43 CREATE INDEX IF NOT EXISTS merchant_orders_by_merchant_and_fullfilment_and_session 44 ON merchant_orders 45 (merchant_serial,fulfillment_url,session_id); 46 47 CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_session 48 ON merchant_contract_terms 49 (merchant_serial,session_id); 50 51 52 53 ALTER TABLE merchant_deposit_confirmations 54 ADD COLUMN wire_transfer_deadline INT8 DEFAULT (0) NOT NULL, 55 ADD COLUMN wire_pending BOOL DEFAULT (TRUE) NOT NULL, 56 ADD COLUMN exchange_failure TEXT DEFAULT NULL; 57 58 COMMENT ON COLUMN merchant_deposit_confirmations.wire_transfer_deadline 59 IS 'when should the exchange make the wire transfer at the latest'; 60 COMMENT ON COLUMN merchant_deposit_confirmations.wire_pending 61 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)'; 62 COMMENT ON COLUMN merchant_deposit_confirmations.exchange_failure 63 IS 'Text describing exchange failures in making timely wire transfers for this deposit confirmation'; 64 65 CREATE INDEX IF NOT EXISTS merchant_deposit_confirmations_by_pending_wire 66 ON merchant_deposit_confirmations 67 (exchange_url,wire_transfer_deadline) 68 WHERE wire_pending; 69 70 CREATE INDEX IF NOT EXISTS merchant_deposits_by_deposit_confirmation_serial 71 ON merchant_deposits 72 (deposit_confirmation_serial); 73 74 -------------------------- Tokens ----------------------------- 75 76 CREATE TABLE IF NOT EXISTS merchant_token_families 77 (token_family_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 78 ,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 79 ,slug TEXT NOT NULL UNIQUE -- Constraint fixed in merchant-0013! 80 ,name TEXT NOT NULL 81 ,description TEXT 82 ,description_i18n BYTEA NOT NULL 83 ,valid_after BIGINT NOT NULL 84 ,valid_before BIGINT NOT NULL 85 ,duration BIGINT NOT NULL 86 ,kind TEXT NOT NULL CHECK (kind IN ('subscription', 'discount')) 87 ,issued BIGINT DEFAULT 0 88 ,redeemed BIGINT DEFAULT 0 89 ); 90 COMMENT ON TABLE merchant_token_families 91 IS 'Token families configured by the merchant.'; 92 COMMENT ON COLUMN merchant_token_families.merchant_serial 93 IS 'Instance where the token family is configured.'; 94 COMMENT ON COLUMN merchant_token_families.slug 95 IS 'Unique slug for the token family.'; 96 COMMENT ON COLUMN merchant_token_families.name 97 IS 'Name of the token family.'; 98 COMMENT ON COLUMN merchant_token_families.description 99 IS 'Human-readable description or details about the token family.'; 100 COMMENT ON COLUMN merchant_token_families.description_i18n 101 IS 'JSON map from IETF BCP 47 language tags to localized descriptions'; 102 COMMENT ON COLUMN merchant_token_families.valid_after 103 IS 'Start time of the token family''s validity period.'; 104 COMMENT ON COLUMN merchant_token_families.valid_before 105 IS 'End time of the token family''s validity period.'; 106 COMMENT ON COLUMN merchant_token_families.duration 107 IS 'Duration of the token.'; 108 COMMENT ON COLUMN merchant_token_families.kind 109 IS 'Kind of the token (e.g., subscription, discount).'; 110 COMMENT ON COLUMN merchant_token_families.issued 111 IS 'Counter for the number of tokens issued for this token family.'; 112 COMMENT ON COLUMN merchant_token_families.redeemed 113 IS 'Counter for the number of tokens redeemed for this token family.'; 114 115 116 CREATE TABLE IF NOT EXISTS merchant_token_family_keys 117 (token_family_key_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 118 ,token_family_serial BIGINT REFERENCES merchant_token_families(token_family_serial) ON DELETE CASCADE 119 ,valid_after BIGINT NOT NULL 120 ,valid_before BIGINT NOT NULL 121 ,pub BYTEA NOT NULL 122 ,h_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(h_pub)=32) 123 ,priv BYTEA 124 ,cipher TEXT NOT NULL CHECK (cipher IN ('rsa', 'cs')) 125 ,UNIQUE (token_family_serial, valid_after) 126 ); 127 128 COMMENT ON TABLE merchant_token_family_keys 129 IS 'Keys for token families.'; 130 COMMENT ON COLUMN merchant_token_family_keys.token_family_serial 131 IS 'Token family to which the key belongs.'; 132 COMMENT ON COLUMN merchant_token_family_keys.valid_after 133 IS 'Start time for the validity of the token key.'; 134 COMMENT ON COLUMN merchant_token_family_keys.valid_before 135 IS 'Expiration time for the validity of the token key.'; 136 COMMENT ON COLUMN merchant_token_family_keys.pub 137 IS 'Public key of the token family.'; 138 COMMENT ON COLUMN merchant_token_family_keys.h_pub 139 IS 'Hash of the public key for quick lookup.'; 140 COMMENT ON COLUMN merchant_token_family_keys.priv 141 IS 'Private key of the token family; can be NULL if no more tokens of this family should be issued.'; 142 COMMENT ON COLUMN merchant_token_family_keys.cipher 143 IS 'Cipher used (rsa or cs).'; 144 145 146 CREATE TABLE IF NOT EXISTS merchant_spent_tokens 147 (spent_token_serial BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 148 ,merchant_serial BIGINT NOT NULL REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 149 ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) 150 ,token_family_key_serial BIGINT REFERENCES merchant_token_family_keys(token_family_key_serial) ON DELETE CASCADE 151 ,token_pub BYTEA NOT NULL UNIQUE CHECK (LENGTH(token_pub)=32) 152 ,token_sig BYTEA NOT NULL CHECK (LENGTH(token_sig)=64) 153 ,blind_sig BYTEA NOT NULL 154 ); 155 COMMENT ON TABLE merchant_spent_tokens 156 IS 'Tokens that have been spent by customers.'; 157 COMMENT ON COLUMN merchant_spent_tokens.merchant_serial 158 IS 'Merchant serial where the token was spent.'; 159 COMMENT ON COLUMN merchant_spent_tokens.h_contract_terms 160 IS 'This is no foreign key by design.'; 161 COMMENT ON COLUMN merchant_spent_tokens.token_family_key_serial 162 IS 'Token family to which the spent token belongs.'; 163 COMMENT ON COLUMN merchant_spent_tokens.token_pub 164 IS 'Public key of the spent token.'; 165 COMMENT ON COLUMN merchant_spent_tokens.token_sig 166 IS 'Signature that the token was spent on specified order.'; 167 COMMENT ON COLUMN merchant_spent_tokens.blind_sig 168 IS 'Blind signature for the spent token to prove validity of token.'; 169 170 -- Complete transaction 171 COMMIT;