merchant-0037.sql (11968B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2026 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 -- @file merchant-0037.sql 17 -- @brief Add a per-instance table tracking, per exchange, the latest 18 -- Taler-Terms-Version (etag) of the terms of service that the user 19 -- accepted ahead of the regular KYC flow (POST /private/accept-tos-early). 20 -- @author Christian Grothoff 21 22 BEGIN; 23 24 SELECT _v.register_patch('merchant-0037', NULL, NULL); 25 26 SET search_path TO merchant; 27 28 CREATE PROCEDURE merchant.merchant_0037_init(s TEXT) 29 LANGUAGE plpgsql 30 AS $OUTER$ 31 BEGIN 32 EXECUTE format('SET LOCAL search_path TO %I', s); 33 34 CREATE TABLE merchant_tos_accepted ( 35 tos_accepted_serial INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 36 exchange_url TEXT NOT NULL UNIQUE, 37 tos_version TEXT NOT NULL 38 ); 39 COMMENT ON TABLE merchant_tos_accepted IS 40 'Latest Taler-Terms-Version (etag) of the terms of service accepted' 41 ' early (ahead of the KYC flow) by the user, one row per exchange.'; 42 COMMENT ON COLUMN merchant_tos_accepted.exchange_url IS 43 'Base URL of the exchange the terms of service were accepted for.'; 44 COMMENT ON COLUMN merchant_tos_accepted.tos_version IS 45 'Accepted Taler-Terms-Version of the exchange terms of service.'; 46 47 -- Restore triggers previously lost in merchant-0036 48 CREATE OR REPLACE FUNCTION handle_category_changes() 49 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 50 CREATE TRIGGER trigger_category_changes 51 AFTER INSERT OR UPDATE OR DELETE 52 ON merchant_categories 53 FOR EACH ROW EXECUTE FUNCTION handle_category_changes(); 54 55 CREATE OR REPLACE FUNCTION handle_inventory_changes() 56 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 57 CREATE TRIGGER trigger_inventory_changes 58 AFTER INSERT OR UPDATE OR DELETE 59 ON merchant_inventory 60 FOR EACH ROW EXECUTE FUNCTION handle_inventory_changes(); 61 62 CREATE OR REPLACE FUNCTION merchant_orders_insert_statistics_trigger() 63 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 64 CREATE TRIGGER merchant_orders_on_insert_statistic 65 AFTER INSERT 66 ON merchant_orders 67 FOR EACH ROW EXECUTE FUNCTION merchant_orders_insert_statistics_trigger(); 68 69 CREATE OR REPLACE FUNCTION merchant_contract_terms_insert_statistics_trigger() 70 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 71 CREATE TRIGGER merchant_contract_terms_on_insert_statistic 72 AFTER INSERT 73 ON merchant_contract_terms 74 FOR EACH ROW EXECUTE FUNCTION merchant_contract_terms_insert_statistics_trigger(); 75 76 CREATE OR REPLACE FUNCTION merchant_contract_terms_update_statistics_trigger() 77 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 78 CREATE TRIGGER merchant_contract_terms_on_update_statistic 79 AFTER UPDATE 80 ON merchant_contract_terms 81 FOR EACH ROW EXECUTE FUNCTION merchant_contract_terms_update_statistics_trigger(); 82 83 CREATE OR REPLACE FUNCTION merchant_refunds_insert_statistics_trigger() 84 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 85 CREATE TRIGGER merchant_refunds_on_insert_statistic 86 AFTER INSERT 87 ON merchant_refunds 88 FOR EACH ROW EXECUTE FUNCTION merchant_refunds_insert_statistics_trigger(); 89 90 CREATE OR REPLACE FUNCTION merchant_transfer_signatures_insert_statistics_trigger() 91 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 92 CREATE TRIGGER merchant_transfer_signatures_on_insert_statistic 93 AFTER INSERT 94 ON merchant_transfer_signatures 95 FOR EACH ROW EXECUTE FUNCTION merchant_transfer_signatures_insert_statistics_trigger(); 96 97 CREATE OR REPLACE FUNCTION merchant_issued_tokens_insert_statistics_trigger() 98 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 99 CREATE TRIGGER merchant_issued_tokens_on_insert_statistic 100 AFTER INSERT 101 ON merchant_issued_tokens 102 FOR EACH ROW EXECUTE FUNCTION merchant_issued_tokens_insert_statistics_trigger(); 103 104 CREATE OR REPLACE FUNCTION merchant_used_tokens_insert_statistics_trigger() 105 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 106 CREATE TRIGGER merchant_used_tokens_on_insert_statistic 107 AFTER INSERT 108 ON merchant_used_tokens 109 FOR EACH ROW EXECUTE FUNCTION merchant_used_tokens_insert_statistics_trigger(); 110 111 CREATE OR REPLACE FUNCTION merchant_deposits_insert_statistics_trigger() 112 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 113 CREATE TRIGGER merchant_deposits_on_insert_statistic 114 AFTER INSERT 115 ON merchant_deposits 116 FOR EACH ROW EXECUTE FUNCTION merchant_deposits_insert_statistics_trigger(); 117 118 CREATE OR REPLACE FUNCTION merchant_expected_transfers_insert_statistics_trigger() 119 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 120 CREATE TRIGGER merchant_expected_transfers_on_insert_statistic 121 AFTER INSERT 122 ON merchant_expected_transfers 123 FOR EACH ROW EXECUTE FUNCTION merchant_expected_transfers_insert_statistics_trigger(); 124 125 CREATE OR REPLACE FUNCTION merchant_expected_transfers_update_statistics_trigger() 126 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 127 CREATE TRIGGER merchant_expected_transfers_on_update_statistic 128 AFTER INSERT 129 ON merchant_expected_transfers 130 FOR EACH ROW EXECUTE FUNCTION merchant_expected_transfers_update_statistics_trigger(); 131 132 CREATE OR REPLACE FUNCTION merchant_expected_transfers_insert_trigger() 133 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 134 CREATE TRIGGER merchant_expected_transfers_on_insert 135 BEFORE INSERT 136 ON merchant_expected_transfers 137 FOR EACH ROW EXECUTE FUNCTION merchant_expected_transfers_insert_trigger(); 138 139 CREATE OR REPLACE FUNCTION merchant_expected_transfers_update_trigger() 140 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 141 CREATE TRIGGER merchant_expected_transfers_on_update 142 BEFORE UPDATE 143 ON merchant_expected_transfers 144 FOR EACH ROW EXECUTE FUNCTION merchant_expected_transfers_update_trigger(); 145 146 CREATE OR REPLACE FUNCTION merchant_transfers_insert_trigger() 147 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 148 CREATE TRIGGER merchant_transfers_on_insert 149 BEFORE INSERT 150 ON merchant_transfers 151 FOR EACH ROW EXECUTE FUNCTION merchant_transfers_insert_trigger(); 152 153 CREATE OR REPLACE FUNCTION merchant_kyc_insert_trigger() 154 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 155 CREATE TRIGGER merchant_kyc_on_insert 156 AFTER INSERT 157 ON merchant_kyc 158 FOR EACH ROW EXECUTE FUNCTION merchant_kyc_insert_trigger(); 159 160 CREATE OR REPLACE FUNCTION merchant_kyc_update_trigger() 161 RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN RETURN NEW; END $$; 162 CREATE TRIGGER merchant_kyc_on_update 163 AFTER UPDATE 164 ON merchant_kyc 165 FOR EACH ROW EXECUTE FUNCTION merchant_kyc_update_trigger(); 166 167 -- Install default statistics tracking, if not already present 168 -- (for existing instances, we would have copied it over, but 169 -- not for new instances created after the initial migration). 170 -- Enable interval statistics 171 INSERT INTO merchant_statistic_interval_meta 172 (slug 173 ,description 174 ,stype 175 ,ranges 176 ,precisions) 177 VALUES 178 ('orders-created' 179 ,'number of orders created (but not necessarily claimed by wallets)' 180 ,'number' 181 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 182 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 183 ,array_fill (5, ARRAY[60]) -- precision: 5s 184 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 185 ), 186 ('orders-claimed' 187 ,'number of orders claimed by a wallet (but not necessarily paid)' 188 ,'number' 189 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 190 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 191 ,array_fill (5, ARRAY[60]) -- precision: 5s 192 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 193 ), 194 ('orders-paid' 195 ,'number of orders paid (but not necessarily settled by the exchange)' 196 ,'number' 197 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 198 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 199 ,array_fill (5, ARRAY[60]) -- precision: 5s 200 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 201 ), 202 ('orders-settled' 203 ,'number of orders settled' 204 ,'number' 205 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 206 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 207 ,array_fill (5, ARRAY[60]) -- precision: 5s 208 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 209 ), 210 ('tokens-issued' 211 ,'number of tokens issued to customers' 212 ,'number' 213 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 214 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 215 ,array_fill (5, ARRAY[60]) -- precision: 5s 216 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 217 ), 218 ('tokens-used' 219 ,'number of tokens used by customers' 220 ,'number' 221 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 222 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 223 ,array_fill (5, ARRAY[60]) -- precision: 5s 224 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 225 ) 226 ON CONFLICT DO NOTHING; 227 228 -- Enable bucket statistics 229 INSERT INTO merchant_statistic_bucket_meta 230 (slug 231 ,description 232 ,stype 233 ,ranges 234 ,ages) 235 VALUES 236 ('payments-received-after-deposit-fee' 237 ,'amount customers paid to us (excluded deposit fees paid by us or customers, wire fees are still deducted by the exchange)' 238 ,'amount' 239 ,ARRAY['hour'::merchant.statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 240 ,ARRAY[72, 14, 12, 24, 12, 10] 241 ), 242 ('total-deposit-fees-paid' 243 ,'deposit fees we or our customers paid to the exchange (includes those waived on refunds)' 244 ,'amount' 245 ,ARRAY['hour'::merchant.statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 246 ,ARRAY[72, 14, 12, 24, 12, 10] 247 ), 248 ('total-wire-fees-paid' 249 ,'wire fees we paid to the exchange' 250 ,'amount' 251 ,ARRAY['hour'::merchant.statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 252 ,ARRAY[72, 12, 12, 24, 12, 10] 253 ), 254 ('refunds-granted' 255 ,'refunds granted by us to our customers' 256 ,'amount' 257 ,ARRAY['hour'::merchant.statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 258 ,ARRAY[72, 14, 12, 24, 12, 10] 259 ), 260 ('tokens-issued' 261 ,'number of tokens issued to customers' 262 ,'number' 263 ,ARRAY['hour'::merchant.statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 264 ,ARRAY[72, 14, 12, 24, 12, 10] 265 ), 266 ('tokens-used' 267 ,'number of tokens used by customers' 268 ,'number' 269 ,ARRAY['hour'::merchant.statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 270 ,ARRAY[72, 14, 12, 24, 12, 10] 271 ) 272 ON CONFLICT DO NOTHING; 273 274 SET LOCAL search_path TO merchant; 275 END 276 $OUTER$; 277 278 INSERT INTO merchant.instance_fixups 279 (migration_name 280 ,version) 281 VALUES 282 ('merchant_0037_init' 283 ,37); 284 -- Apply new fix-up to existing instances 285 CALL merchant.fixup_instance_schema (37::INT8); 286 287 COMMIT;