merchant-0021.sql (15017B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2025 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-0021.sql 18 -- @brief Tables for statistics 19 -- @author Christian Grothoff 20 21 22 BEGIN; 23 24 -- Check patch versioning is in place. 25 SELECT _v.register_patch('merchant-0021', NULL, NULL); 26 27 SET search_path TO merchant; 28 29 COMMENT ON TABLE merchant_transfers 30 IS 'table represents confirmed incoming wire transfers'; 31 COMMENT ON COLUMN merchant_transfers.credit_amount 32 IS 'actual value of the confirmed wire transfer'; 33 34 CREATE TABLE merchant_expected_transfers 35 (expected_credit_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 36 ,exchange_url TEXT NOT NULL 37 ,wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32) 38 ,expected_credit_amount taler_amount_currency 39 ,wire_fee taler_amount_currency 40 ,account_serial INT8 NOT NULL 41 REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE 42 ,expected_time INT8 NOT NULL 43 ,retry_time INT8 NOT NULL DEFAULT (0) 44 ,last_http_status INT4 DEFAULT NULL 45 ,last_ec INT4 DEFAULT NULL 46 ,last_detail TEXT DEFAULT NULL 47 ,retry_needed BOOLEAN NOT NULL DEFAULT TRUE 48 ,signkey_serial BIGINT 49 REFERENCES merchant_exchange_signing_keys (signkey_serial) 50 ON DELETE CASCADE 51 ,exchange_sig BYTEA CHECK (LENGTH(exchange_sig)=64) DEFAULT NULL 52 ,h_details BYTEA CHECK (LENGTH(h_details)=64) DEFAULT NULL 53 ,confirmed BOOLEAN NOT NULL DEFAULT FALSE 54 ,UNIQUE (wtid, exchange_url, account_serial) 55 ); 56 COMMENT ON TABLE merchant_expected_transfers 57 IS 'expected incoming wire transfers'; 58 COMMENT ON COLUMN merchant_expected_transfers.expected_credit_serial 59 IS 'Unique identifier for this expected wire transfer in this backend'; 60 COMMENT ON COLUMN merchant_expected_transfers.exchange_url 61 IS 'Base URL of the exchange that originated the wire transfer as extracted from the wire transfer subject'; 62 COMMENT ON COLUMN merchant_expected_transfers.wtid 63 IS 'Unique wire transfer identifier (or at least, should be unique by protocol) as selected by the exchange and extracted from the wire transfer subject'; 64 COMMENT ON COLUMN merchant_expected_transfers.expected_credit_amount 65 IS 'expected actual value of the (aggregated) wire transfer, excluding the wire fee; NULL if unknown'; 66 COMMENT ON COLUMN merchant_expected_transfers.wire_fee 67 IS 'wire fee the exchange claims to have charged us; NULL if unknown'; 68 COMMENT ON COLUMN merchant_expected_transfers.account_serial 69 IS 'Merchant bank account that should receive this wire transfer; also implies the merchant instance implicated by the wire transfer'; 70 COMMENT ON COLUMN merchant_expected_transfers.expected_time 71 IS 'Time when we should expect the exchange do do the wire transfer'; 72 COMMENT ON COLUMN merchant_expected_transfers.retry_time 73 IS 'Time when we should next inquire at the exchange about this wire transfer; used by taler-merchant-reconciliation to limit retries with the exchange in case of failures'; 74 COMMENT ON COLUMN merchant_expected_transfers.last_http_status 75 IS 'HTTP status of the last request to the exchange, 0 on timeout or if there was no request (200 on success)'; 76 COMMENT ON COLUMN merchant_expected_transfers.last_ec 77 IS 'Taler error code from the last request to the exchange, 0 on success or if there was no request'; 78 COMMENT ON COLUMN merchant_expected_transfers.last_detail 79 IS 'Taler error detail from the last request to the exchange, NULL on success or if there was no request'; 80 COMMENT ON COLUMN merchant_expected_transfers.signkey_serial 81 IS 'Identifies the online signing key of the exchange used to make the exchange_sig'; 82 COMMENT ON COLUMN merchant_expected_transfers.exchange_sig 83 IS 'Signature over the aggregation response from the exchange, or NULL on error or if we did not yet make that request'; 84 COMMENT ON COLUMN merchant_expected_transfers.confirmed 85 IS 'true once the merchant confirmed that this transfer was received and a matching transfer exists in the merchant_transfers table; set automatically via INSERT TRIGGER merchant_expected_transfers_insert_trigger'; 86 COMMENT ON COLUMN merchant_expected_transfers.retry_needed 87 IS 'true if we need to retry the HTTP request to the exchange (never did it, or transient failure)'; 88 COMMENT ON COLUMN merchant_expected_transfers.h_details 89 IS 'Hash over the aggregation details returned by the exchange, provided here for fast exchange_sig validation'; 90 91 CREATE INDEX merchant_expected_transfers_by_open 92 ON merchant_expected_transfers 93 (retry_time ASC) 94 WHERE NOT confirmed OR retry_needed; 95 COMMENT ON INDEX merchant_expected_transfers_by_open 96 IS 'For select_open_transfers'; 97 98 -- Migrate data. The backend will just re-do all of the 99 -- reconciliation work, so we only preserve confirmed transfers. 100 -- However, we must put those also into the new "merchant_expected_transfers" 101 -- table already. 102 DELETE FROM merchant_transfers 103 WHERE NOT confirmed; 104 105 -- This index was replaced by merchant_expected_transfers_by_open. 106 DROP INDEX merchant_transfers_by_open; 107 108 -- These columns will be in the new merchant_expected_transfers table. 109 ALTER TABLE merchant_transfers 110 ADD COLUMN bank_serial_id INT8, 111 ADD COLUMN expected BOOL DEFAULT FALSE, 112 ADD COLUMN execution_time INT8 DEFAULT (0), 113 DROP COLUMN ready_time, 114 DROP COLUMN confirmed, 115 DROP COLUMN failed, 116 DROP COLUMN verified, 117 DROP COLUMN validation_status; 118 119 COMMENT ON COLUMN merchant_transfers.expected 120 IS 'True if this wire transfer was expected (has matching entry in merchant_expected_transfers); set automatically via INSERT TRIGGER merchant_transfers_insert_trigger'; 121 COMMENT ON COLUMN merchant_transfers.bank_serial_id 122 IS 'Row ID of the wire transfer from the automated import; NULL if not available (like when a human manually imported the transfer)'; 123 COMMENT ON COLUMN merchant_transfers.execution_time 124 IS 'Time when the merchant transfer was added and thus roughly received in our bank account'; 125 126 -- Note: if the bank_serial_id is NULL (manual import), we always 127 -- consider confirmed transfers to be 'UNIQUE'; thus we do 128 -- NOT use "NULLS NOT DISTINCT" here. 129 130 ALTER TABLE merchant_transfers 131 DROP CONSTRAINT merchant_transfers_wtid_exchange_url_account_serial_key, 132 ADD CONSTRAINT merchant_transfers_unique 133 UNIQUE (wtid, exchange_url, account_serial, bank_serial_id); 134 135 136 -- Create triggers to set confirmed/expected status on INSERT. 137 CREATE FUNCTION merchant_expected_transfers_insert_trigger() 138 RETURNS trigger 139 LANGUAGE plpgsql 140 AS $$ 141 BEGIN 142 UPDATE merchant_transfers 143 SET expected = TRUE 144 WHERE wtid = NEW.wtid 145 AND exchange_url = NEW.exchange_url 146 AND credit_amount = NEW.expected_credit_amount; 147 NEW.confirmed = FOUND; 148 RETURN NEW; 149 END $$; 150 COMMENT ON FUNCTION merchant_expected_transfers_insert_trigger 151 IS 'Sets "confirmed" to TRUE for the new record if the expected transfer was already confirmed, and updates the already confirmed transfer to "expected"'; 152 153 -- Whenever an expected transfer is added, check if it was already confirmed 154 CREATE TRIGGER merchant_expected_transfers_on_insert 155 BEFORE INSERT 156 ON merchant.merchant_expected_transfers 157 FOR EACH ROW EXECUTE FUNCTION merchant_expected_transfers_insert_trigger(); 158 159 160 CREATE FUNCTION merchant_transfers_insert_trigger() 161 RETURNS trigger 162 LANGUAGE plpgsql 163 AS $$ 164 BEGIN 165 UPDATE merchant_expected_transfers 166 SET confirmed = TRUE 167 WHERE wtid = NEW.wtid 168 AND exchange_url = NEW.exchange_url 169 AND expected_credit_amount = NEW.credit_amount; 170 NEW.expected = FOUND; 171 RETURN NEW; 172 END $$; 173 COMMENT ON FUNCTION merchant_transfers_insert_trigger 174 IS 'Sets "expected" to TRUE for the new record if the transfer was already expected, and updates the already confirmed transfer to "confirmed"'; 175 176 -- Whenever a transfer is addeded, check if it was already expected 177 CREATE TRIGGER merchant_transfers_on_insert 178 BEFORE INSERT 179 ON merchant.merchant_transfers 180 FOR EACH ROW EXECUTE FUNCTION merchant_transfers_insert_trigger(); 181 182 183 -- Adjust contract terms table. 184 ALTER TABLE merchant_deposits 185 ADD COLUMN settlement_retry_needed BOOL DEFAULT TRUE, 186 ADD COLUMN settlement_retry_time INT8 DEFAULT (0), 187 ADD COLUMN settlement_last_http_status INT4 DEFAULT NULL, 188 ADD COLUMN settlement_last_ec INT4 DEFAULT NULL, 189 ADD COLUMN settlement_last_detail TEXT DEFAULT NULL, 190 ADD COLUMN settlement_wtid BYTEA CHECK (LENGTH(settlement_wtid)=32) DEFAULT NULL, 191 ADD COLUMN settlement_coin_contribution taler_amount_currency DEFAULT NULL, 192 ADD COLUMN settlement_expected_credit_serial INT8 DEFAULT NULL 193 REFERENCES merchant_expected_transfers (expected_credit_serial), 194 ADD COLUMN signkey_serial INT8 DEFAULT NULL 195 REFERENCES merchant_exchange_signing_keys (signkey_serial) 196 ON DELETE CASCADE, 197 ADD COLUMN settlement_exchange_sig BYTEA 198 DEFAULT NULL CHECK (LENGTH(settlement_exchange_sig)=64); 199 200 COMMENT ON COLUMN merchant_deposits.settlement_retry_needed 201 IS 'True if we should ask the exchange in the future about the settlement'; 202 COMMENT ON COLUMN merchant_deposits.settlement_retry_time 203 IS 'When should we next ask the exchange about the settlement wire transfer for this coin, initially set to the wire transfer deadline plus a bit of slack'; 204 COMMENT ON COLUMN merchant_deposits.settlement_last_http_status 205 IS 'HTTP status of our last inquiry with the exchange for this deposit, NULL if we never inquired, 0 on timeout'; 206 COMMENT ON COLUMN merchant_deposits.settlement_last_ec 207 IS 'Taler error code for our last inquiry with the exchange for this deposit, NULL if we never inquired, 0 on success'; 208 COMMENT ON COLUMN merchant_deposits.settlement_last_detail 209 IS 'Taler error detail for our last inquiry with the exchange for this deposit, NULL if we never inquired or on success'; 210 COMMENT ON COLUMN merchant_deposits.settlement_coin_contribution 211 IS 'Contribution of this coin to the overall wire transfer made by the exchange as claimed by exchange_sig; should match amount_with_fee minus deposit_fee, NULL if we did not get a reply from the exchange'; 212 COMMENT ON COLUMN merchant_deposits.settlement_expected_credit_serial 213 IS 'Identifies the expected wire transfer from the exchange to the merchant that settled the deposit of coin, NULL if unknown'; 214 COMMENT ON COLUMN merchant_deposits.signkey_serial 215 IS 'Identifies the online signing key of the exchange used to make the exchange_sig, NULL for none'; 216 COMMENT ON COLUMN merchant_deposits.settlement_exchange_sig 217 IS 'Exchange signature of purpose TALER_SIGNATURE_EXCHANGE_CONFIRM_WIRE, NULL if we did not get such an exchange signature'; 218 219 CREATE INDEX merchant_deposits_by_settlement_open 220 ON merchant_deposits 221 (settlement_retry_time ASC) 222 WHERE settlement_retry_needed; 223 COMMENT ON INDEX merchant_deposits_by_settlement_open 224 IS 'For select_open_deposit_settlements'; 225 226 CREATE INDEX merchant_deposits_by_deposit_confirmation 227 ON merchant_deposits 228 (deposit_confirmation_serial); 229 230 231 -- No 1:n mapping necessary, integrated into merchant_deposits table above. 232 DROP TABLE merchant_deposit_to_transfer; 233 234 -- We need to fully re-do the merchant_transfer_to_coin table, 235 -- and data should be re-constructed, so drop and re-build. 236 DROP TABLE merchant_transfer_to_coin; 237 CREATE TABLE merchant_expected_transfer_to_coin 238 (deposit_serial BIGINT UNIQUE NOT NULL 239 REFERENCES merchant_deposits (deposit_serial) ON DELETE CASCADE 240 ,expected_credit_serial BIGINT NOT NULL 241 REFERENCES merchant_expected_transfers (expected_credit_serial) ON DELETE CASCADE 242 ,offset_in_exchange_list INT8 NOT NULL 243 ,exchange_deposit_value taler_amount_currency NOT NULL 244 ,exchange_deposit_fee taler_amount_currency NOT NULL 245 ); 246 CREATE INDEX IF NOT EXISTS merchant_transfers_by_credit 247 ON merchant_expected_transfer_to_coin 248 (expected_credit_serial); 249 COMMENT ON TABLE merchant_expected_transfer_to_coin 250 IS 'Mapping of (credit) transfers to (deposited) coins'; 251 COMMENT ON COLUMN merchant_expected_transfer_to_coin.deposit_serial 252 IS 'Identifies the deposited coin that the wire transfer presumably settles'; 253 COMMENT ON COLUMN merchant_expected_transfer_to_coin.expected_credit_serial 254 IS 'Identifies the expected wire transfer that settles the given deposited coin'; 255 COMMENT ON COLUMN merchant_expected_transfer_to_coin.offset_in_exchange_list 256 IS 'The exchange settlement data includes an array of the settled coins; this is the index of the coin in that list, useful to reconstruct the correct sequence of coins as needed to check the exchange signature'; 257 COMMENT ON COLUMN merchant_expected_transfer_to_coin.exchange_deposit_value 258 IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits minus refunds'; 259 COMMENT ON COLUMN merchant_expected_transfer_to_coin.exchange_deposit_fee 260 IS 'Deposit value as claimed by the exchange, should match our values in merchant_deposits'; 261 262 263 -- We need to fully re-do the merchant_transfer_signatures table, 264 -- and data should be re-constructed, so drop and re-build. 265 266 DROP TABLE merchant_transfer_signatures; 267 CREATE TABLE merchant_transfer_signatures 268 (expected_credit_serial BIGINT PRIMARY KEY 269 REFERENCES merchant_expected_transfers (expected_credit_serial) 270 ON DELETE CASCADE 271 ,signkey_serial BIGINT NOT NULL 272 REFERENCES merchant_exchange_signing_keys (signkey_serial) 273 ON DELETE CASCADE 274 ,wire_fee taler_amount_currency NOT NULL 275 ,credit_amount taler_amount_currency NOT NULL 276 ,execution_time INT8 NOT NULL 277 ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) 278 ); 279 COMMENT ON TABLE merchant_transfer_signatures 280 IS 'table represents the main information returned from the /transfer request to the exchange.'; 281 COMMENT ON COLUMN merchant_transfer_signatures.expected_credit_serial 282 IS 'expected wire transfer this signature is about'; 283 COMMENT ON COLUMN merchant_transfer_signatures.signkey_serial 284 IS 'Online signing key by the exchange that was used for the exchange_sig signature'; 285 COMMENT ON COLUMN merchant_transfer_signatures.wire_fee 286 IS 'wire fee charged by the exchange for this transfer'; 287 COMMENT ON COLUMN merchant_transfer_signatures.exchange_sig 288 IS 'signature by the exchange of purpose TALER_SIGNATURE_EXCHANGE_CONFIRM_WIRE_DEPOSIT'; 289 COMMENT ON COLUMN merchant_transfer_signatures.execution_time 290 IS 'Execution time as claimed by the exchange, roughly matches time seen by merchant'; 291 COMMENT ON COLUMN merchant_transfer_signatures.credit_amount 292 IS 'actual value of the (aggregated) wire transfer, excluding the wire fee, according to the exchange'; 293 294 295 COMMIT;