merchant-0014.sql (23046B)
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-0014.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-0014', NULL, NULL); 26 27 SET search_path TO merchant; 28 29 -- Ranges given here must be supported by the date_trunc function of Postgresql! 30 CREATE TYPE statistic_range AS 31 ENUM('century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'); 32 33 CREATE TYPE statistic_type AS 34 ENUM('amount', 'number'); 35 36 -- -------------- Bucket statistics --------------------- 37 38 CREATE TABLE merchant_statistic_bucket_meta 39 (bmeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 40 ,slug TEXT NOT NULL 41 ,description TEXT NOT NULL 42 ,stype statistic_type NOT NULL 43 ,ranges statistic_range[] NOT NULL 44 ,ages INT4[] NOT NULL 45 ,UNIQUE(slug,stype) 46 ,CONSTRAINT equal_array_length 47 CHECK (array_length(ranges,1) = 48 array_length(ages,1)) 49 ); 50 COMMENT ON TABLE merchant_statistic_bucket_meta 51 IS 'meta data about a statistic with events falling into buckets we are tracking'; 52 COMMENT ON COLUMN merchant_statistic_bucket_meta.bmeta_serial_id 53 IS 'unique identifier for this type of bucket statistic we are tracking'; 54 COMMENT ON COLUMN merchant_statistic_bucket_meta.slug 55 IS 'keyword (or name) of the statistic; identifies what the statistic is about; should be a slug suitable for a URI path'; 56 COMMENT ON COLUMN merchant_statistic_bucket_meta.description 57 IS 'description of the statistic being tracked'; 58 COMMENT ON COLUMN merchant_statistic_bucket_meta.stype 59 IS 'statistic type, what kind of data is being tracked, amount or number'; 60 COMMENT ON COLUMN merchant_statistic_bucket_meta.ranges 61 IS 'size of the buckets that are being kept for this statistic'; 62 COMMENT ON COLUMN merchant_statistic_bucket_meta.ages 63 IS 'determines how long into the past we keep buckets for the range at the given index around (in generations)'; 64 65 66 CREATE TABLE merchant_statistic_bucket_counter 67 (bmeta_serial_id INT8 NOT NULL 68 REFERENCES merchant_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE 69 ,merchant_serial BIGINT NOT NULL 70 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 71 ,bucket_start INT8 NOT NULL 72 ,bucket_range statistic_range NOT NULL 73 ,cumulative_number INT8 NOT NULL 74 ,UNIQUE (bmeta_serial_id,merchant_serial,bucket_start,bucket_range) 75 ); 76 COMMENT ON TABLE merchant_statistic_bucket_counter 77 IS 'various numeric statistics (cumulative counters) being tracked by bucket into which they fall'; 78 COMMENT ON COLUMN merchant_statistic_bucket_counter.bmeta_serial_id 79 IS 'identifies what the statistic is about'; 80 COMMENT ON COLUMN merchant_statistic_bucket_counter.merchant_serial 81 IS 'identifies the instance for which the statistic is kept'; 82 COMMENT ON COLUMN merchant_statistic_bucket_counter.bucket_start 83 IS 'start date for the bucket in seconds since the epoch'; 84 COMMENT ON COLUMN merchant_statistic_bucket_counter.bucket_range 85 IS 'range of the bucket'; 86 COMMENT ON COLUMN merchant_statistic_bucket_counter.cumulative_number 87 IS 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword'; 88 89 90 CREATE TABLE merchant_statistic_bucket_amount 91 (bmeta_serial_id INT8 NOT NULL 92 REFERENCES merchant_statistic_bucket_meta (bmeta_serial_id) ON DELETE CASCADE 93 ,merchant_serial BIGINT NOT NULL 94 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 95 ,bucket_start INT8 NOT NULL 96 ,bucket_range statistic_range NOT NULL 97 ,curr VARCHAR(12) NOT NULL 98 ,cumulative_value INT8 NOT NULL 99 ,cumulative_frac INT4 NOT NULL 100 ,UNIQUE (bmeta_serial_id,merchant_serial,curr,bucket_start,bucket_range) 101 ); 102 COMMENT ON TABLE merchant_statistic_bucket_amount 103 IS 'various amount statistics (in various currencies) being tracked'; 104 COMMENT ON COLUMN merchant_statistic_bucket_amount.bmeta_serial_id 105 IS 'identifies what the statistic is about'; 106 COMMENT ON COLUMN merchant_statistic_bucket_amount.merchant_serial 107 IS 'identifies the instance for which the statistic is kept'; 108 COMMENT ON COLUMN merchant_statistic_bucket_amount.bucket_start 109 IS 'start date for the bucket in seconds since the epoch'; 110 COMMENT ON COLUMN merchant_statistic_bucket_amount.bucket_range 111 IS 'range of the bucket'; 112 COMMENT ON COLUMN merchant_statistic_bucket_amount.curr 113 IS 'currency which this statistic is tracking the amount for'; 114 COMMENT ON COLUMN merchant_statistic_bucket_amount.cumulative_value 115 IS 'amount in the respective currency, non-fractional amount value'; 116 COMMENT ON COLUMN merchant_statistic_bucket_amount.cumulative_frac 117 IS 'amount in the respective currency, fraction in units of 1/100000000 of the base value'; 118 119 120 -- -------------- Interval statistics --------------------- 121 122 123 CREATE TABLE merchant_statistic_interval_meta 124 (imeta_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 125 ,slug TEXT NOT NULL 126 ,description TEXT NOT NULL 127 ,stype statistic_type NOT NULL 128 ,ranges INT8[] NOT NULL CHECK (array_length(ranges,1) > 0) 129 ,precisions INT8[] NOT NULL CHECK (array_length(precisions,1) > 0) 130 ,UNIQUE(slug,stype) 131 ,CONSTRAINT equal_array_length 132 CHECK (array_length(ranges,1) = 133 array_length(precisions,1)) 134 ); 135 COMMENT ON TABLE merchant_statistic_interval_meta 136 IS 'meta data about an interval statistic we are tracking'; 137 COMMENT ON COLUMN merchant_statistic_interval_meta.imeta_serial_id 138 IS 'unique identifier for this type of interval statistic we are tracking'; 139 COMMENT ON COLUMN merchant_statistic_interval_meta.slug 140 IS 'keyword (or name) of the statistic; identifies what the statistic is about; should be a slug suitable for a URI path'; 141 COMMENT ON COLUMN merchant_statistic_interval_meta.description 142 IS 'description of the statistic being tracked'; 143 COMMENT ON COLUMN merchant_statistic_interval_meta.stype 144 IS 'statistic type, what kind of data is being tracked, amount or number'; 145 COMMENT ON COLUMN merchant_statistic_interval_meta.ranges 146 IS 'range of values that is being kept for this statistic, in seconds, must be monotonically increasing'; 147 COMMENT ON COLUMN merchant_statistic_interval_meta.precisions 148 IS 'determines how precisely we track which events fall into the range at the same index (allowing us to coalesce events with timestamps in proximity close to the given precision), in seconds, 0 is not allowed'; 149 150 CREATE TABLE merchant_statistic_counter_event 151 (nevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 152 ,imeta_serial_id INT8 153 REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE 154 ,merchant_serial BIGINT NOT NULL 155 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 156 ,slot INT8 NOT NULL 157 ,delta INT8 NOT NULL 158 ,UNIQUE (imeta_serial_id, merchant_serial, slot) 159 ); 160 COMMENT ON TABLE merchant_statistic_counter_event 161 IS 'number to decrement an interval statistic by when a certain time value is reached'; 162 COMMENT ON COLUMN merchant_statistic_counter_event.nevent_serial_id 163 IS 'unique identifier for this number event'; 164 COMMENT ON COLUMN merchant_statistic_counter_event.imeta_serial_id 165 IS 'identifies what the statistic is about; must be of stype number'; 166 COMMENT ON COLUMN merchant_statistic_counter_event.merchant_serial 167 IS 'identifies which merchant instance the event is about'; 168 COMMENT ON COLUMN merchant_statistic_counter_event.slot 169 IS 'identifies the time slot at which the given event(s) happened, rounded down by the respective precisions value'; 170 COMMENT ON COLUMN merchant_statistic_counter_event.delta 171 IS 'total cumulative number that was added at the time identified by slot'; 172 173 CREATE TABLE merchant_statistic_interval_counter 174 (imeta_serial_id INT8 NOT NULL 175 REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE 176 ,merchant_serial BIGINT NOT NULL 177 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 178 ,range INT8 NOT NULL 179 ,event_delimiter INT8 NOT NULL 180 REFERENCES merchant_statistic_counter_event (nevent_serial_id) ON DELETE RESTRICT 181 ,cumulative_number INT8 NOT NULL 182 ,UNIQUE (imeta_serial_id,merchant_serial,range) 183 ); 184 COMMENT ON TABLE merchant_statistic_interval_counter 185 IS 'various numeric statistics (cumulative counters) being tracked'; 186 COMMENT ON COLUMN merchant_statistic_interval_counter.imeta_serial_id 187 IS 'identifies what the statistic is about'; 188 COMMENT ON COLUMN merchant_statistic_interval_counter.merchant_serial 189 IS 'identifies the instance for which the statistic is kept'; 190 COMMENT ON COLUMN merchant_statistic_interval_counter.range 191 IS 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges'; 192 COMMENT ON COLUMN merchant_statistic_interval_counter.event_delimiter 193 IS 'determines the last event currently included in the interval'; 194 COMMENT ON COLUMN merchant_statistic_interval_counter.cumulative_number 195 IS 'aggregate (sum) of tracked by the statistic; what exactly is tracked is determined by the keyword'; 196 197 198 CREATE TABLE merchant_statistic_amount_event 199 (aevent_serial_id INT8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 200 ,imeta_serial_id INT8 201 REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE 202 ,merchant_serial BIGINT NOT NULL 203 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 204 ,slot INT8 NOT NULL 205 ,delta_curr VARCHAR(12) NOT NULL 206 ,delta_value INT8 NOT NULL 207 ,delta_frac INT4 NOT NULL 208 ,CONSTRAINT event_key UNIQUE (imeta_serial_id, merchant_serial, delta_curr, slot) 209 ); 210 COMMENT ON TABLE merchant_statistic_amount_event 211 IS 'amount to decrement an interval statistic by when a certain time value is reached'; 212 COMMENT ON COLUMN merchant_statistic_amount_event.aevent_serial_id 213 IS 'unique identifier for this amount event'; 214 COMMENT ON COLUMN merchant_statistic_amount_event.imeta_serial_id 215 IS 'identifies what the statistic is about; must be of clazz interval and of stype amount'; 216 COMMENT ON COLUMN merchant_statistic_amount_event.merchant_serial 217 IS 'identifies which merchant instance the event is about'; 218 COMMENT ON COLUMN merchant_statistic_amount_event.slot 219 IS 'identifies the time slot at which the given event(s) happened'; 220 COMMENT ON COLUMN merchant_statistic_amount_event.delta_curr 221 IS 'currency of the total cumulative amount that was added at the time identified by slot'; 222 COMMENT ON COLUMN merchant_statistic_amount_event.delta_value 223 IS 'total cumulative amount (value) that was added at the time identified by slot'; 224 COMMENT ON COLUMN merchant_statistic_amount_event.delta_frac 225 IS 'total cumulative amount (fraction) that was added at the time identified by slot'; 226 227 228 CREATE TABLE merchant_statistic_interval_amount 229 (imeta_serial_id INT8 NOT NULL 230 REFERENCES merchant_statistic_interval_meta (imeta_serial_id) ON DELETE CASCADE 231 ,merchant_serial BIGINT NOT NULL 232 REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE 233 ,event_delimiter INT8 NOT NULL 234 REFERENCES merchant_statistic_amount_event (aevent_serial_id) ON DELETE RESTRICT 235 ,range INT8 NOT NULL 236 ,curr VARCHAR(12) NOT NULL 237 ,cumulative_value INT8 NOT NULL 238 ,cumulative_frac INT4 NOT NULL 239 ,UNIQUE (imeta_serial_id,merchant_serial,curr,range) 240 ); 241 COMMENT ON TABLE merchant_statistic_interval_amount 242 IS 'various amount statistics (in various currencies) being tracked'; 243 COMMENT ON COLUMN merchant_statistic_interval_amount.imeta_serial_id 244 IS 'identifies what the statistic is about'; 245 COMMENT ON COLUMN merchant_statistic_interval_amount.merchant_serial 246 IS 'identifies the instance for which the statistic is kept'; 247 COMMENT ON COLUMN merchant_statistic_interval_amount.range 248 IS 'for which range is this the counter; note that the cumulative_number excludes the values already stored in smaller ranges'; 249 COMMENT ON COLUMN merchant_statistic_interval_amount.curr 250 IS 'currency which this statistic is tracking the amount for'; 251 COMMENT ON COLUMN merchant_statistic_interval_amount.cumulative_value 252 IS 'amount in the respective currency, non-fractional amount value'; 253 COMMENT ON COLUMN merchant_statistic_interval_amount.cumulative_frac 254 IS 'amount in the respective currency, fraction in units of 1/100000000 of the base value'; 255 256 CREATE TYPE merchant_statistic_interval_number_get_return_value 257 AS 258 (range INT8 259 ,rvalue INT8 260 ); 261 COMMENT ON TYPE merchant_statistic_interval_number_get_return_value 262 IS 'Return type for merchant_statistic_interval_number_get stored procedure'; 263 264 CREATE TYPE merchant_statistic_interval_amount_get_return_value 265 AS 266 (range INT8 267 ,rvalue taler_amount_currency 268 ); 269 COMMENT ON TYPE merchant_statistic_interval_amount_get_return_value 270 IS 'Return type for merchant_statistic_interval_amount_get stored procedure'; 271 272 -- ---------------- Actual statistics below --------------------- 273 274 275 CREATE FUNCTION merchant_orders_insert_statistics_trigger() 276 RETURNS trigger 277 LANGUAGE plpgsql 278 AS $$ 279 BEGIN 280 CALL merchant_do_bump_number_stat 281 ('orders-created' 282 ,NEW.merchant_serial 283 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 284 ,1); 285 RETURN NEW; 286 END $$; 287 288 -- Whenever an order is created, call our trigger to bump statistics 289 CREATE TRIGGER merchant_orders_on_insert_statistic 290 AFTER INSERT 291 ON merchant_orders 292 FOR EACH ROW EXECUTE FUNCTION merchant_orders_insert_statistics_trigger(); 293 294 295 CREATE FUNCTION merchant_contract_terms_insert_statistics_trigger() 296 RETURNS trigger 297 LANGUAGE plpgsql 298 AS $$ 299 BEGIN 300 CALL merchant_do_bump_number_stat 301 ('orders-claimed' 302 ,NEW.merchant_serial 303 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 304 ,1); 305 RETURN NEW; 306 END $$; 307 308 -- Whenever an order is created, call our trigger to bump statistics 309 CREATE TRIGGER merchant_contract_terms_on_insert_statistic 310 AFTER INSERT 311 ON merchant_contract_terms 312 FOR EACH ROW EXECUTE FUNCTION merchant_contract_terms_insert_statistics_trigger(); 313 314 315 CREATE FUNCTION merchant_contract_terms_update_statistics_trigger() 316 RETURNS trigger 317 LANGUAGE plpgsql 318 AS $$ 319 DECLARE 320 my_rec RECORD; 321 BEGIN 322 IF (NEW.wired AND NOT OLD.wired) 323 THEN 324 CALL merchant_do_bump_number_stat 325 ('orders-settled' 326 ,NEW.merchant_serial 327 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 328 ,1); 329 END IF; 330 IF (NEW.paid AND NOT OLD.paid) 331 THEN 332 CALL merchant_do_bump_number_stat 333 ('orders-paid' 334 ,NEW.merchant_serial 335 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 336 ,1); 337 FOR my_rec IN 338 SELECT total_without_fee 339 FROM merchant_deposit_confirmations 340 WHERE order_serial = NEW.order_serial 341 LOOP 342 CALL merchant_do_bump_amount_stat 343 ('payments-received-after-deposit-fee' 344 ,NEW.merchant_serial 345 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 346 ,my_rec.total_without_fee); 347 END LOOP; 348 FOR my_rec IN 349 SELECT deposit_fee 350 FROM merchant_deposits 351 WHERE deposit_confirmation_serial IN 352 (SELECT deposit_confirmation_serial 353 FROM merchant_deposit_confirmations 354 WHERE order_serial = NEW.order_serial) 355 LOOP 356 CALL merchant_do_bump_amount_stat 357 ('total-deposit-fees-paid' 358 ,NEW.merchant_serial 359 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 360 ,my_rec.deposit_fee); 361 END LOOP; 362 END IF; 363 RETURN NEW; 364 END $$; 365 366 -- Whenever a contract is updated, call our trigger to bump statistics 367 CREATE TRIGGER merchant_contract_terms_on_update_statistic 368 AFTER UPDATE 369 ON merchant_contract_terms 370 FOR EACH ROW EXECUTE FUNCTION merchant_contract_terms_update_statistics_trigger(); 371 372 373 CREATE FUNCTION merchant_refunds_insert_statistics_trigger() 374 RETURNS trigger 375 LANGUAGE plpgsql 376 AS $$ 377 DECLARE 378 my_merchant_serial INT8; 379 BEGIN 380 SELECT merchant_serial 381 INTO my_merchant_serial 382 FROM merchant_contract_terms 383 WHERE order_serial = NEW.order_serial; 384 CALL merchant_do_bump_amount_stat 385 ('refunds-granted' 386 ,my_merchant_serial 387 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 388 ,NEW.refund_amount); 389 RETURN NEW; 390 END $$; 391 392 -- Whenever a refund is granted created, call our trigger to bump statistics 393 CREATE TRIGGER merchant_refunds_on_insert_statistic 394 AFTER INSERT 395 ON merchant_refunds 396 FOR EACH ROW EXECUTE FUNCTION merchant_refunds_insert_statistics_trigger(); 397 398 399 CREATE FUNCTION merchant_transfer_signatures_insert_statistics_trigger() 400 RETURNS trigger 401 LANGUAGE plpgsql 402 AS $$ 403 DECLARE 404 my_merchant_serial INT8; 405 BEGIN 406 SELECT merchant_serial 407 INTO my_merchant_serial 408 FROM merchant_accounts 409 WHERE account_serial = 410 (SELECT account_serial 411 FROM merchant_transfers 412 WHERE credit_serial = NEW.credit_serial); 413 CALL merchant_do_bump_amount_stat 414 ('wire-fees-paid' 415 ,my_merchant_serial 416 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 417 ,NEW.wire_fee); 418 RETURN NEW; 419 END $$; 420 421 -- Whenever a refund is granted created, call our trigger to bump statistics 422 CREATE TRIGGER merchant_transfer_signatures_on_insert_statistic 423 AFTER INSERT 424 ON merchant_transfer_signatures 425 FOR EACH ROW EXECUTE FUNCTION merchant_transfer_signatures_insert_statistics_trigger(); 426 427 428 CREATE FUNCTION merchant_issued_tokens_insert_statistics_trigger() 429 RETURNS trigger 430 LANGUAGE plpgsql 431 AS $$ 432 DECLARE 433 my_merchant_serial INT8; 434 BEGIN 435 SELECT merchant_serial 436 INTO my_merchant_serial 437 FROM merchant_token_families 438 WHERE token_family_serial = 439 (SELECT token_family_serial 440 FROM merchant_token_family_keys 441 WHERE token_family_key_serial = NEW.token_family_key_serial); 442 CALL merchant_do_bump_number_stat 443 ('tokens-issued' 444 ,my_merchant_serial 445 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 446 ,1); 447 RETURN NEW; 448 END $$; 449 450 -- Whenever a token is issued, call our trigger to bump statistics 451 CREATE TRIGGER merchant_issued_tokens_on_insert_statistic 452 AFTER INSERT 453 ON merchant_issued_tokens 454 FOR EACH ROW EXECUTE FUNCTION merchant_issued_tokens_insert_statistics_trigger(); 455 456 457 CREATE FUNCTION merchant_used_tokens_insert_statistics_trigger() 458 RETURNS trigger 459 LANGUAGE plpgsql 460 AS $$ 461 DECLARE 462 my_merchant_serial INT8; 463 BEGIN 464 SELECT merchant_serial 465 INTO my_merchant_serial 466 FROM merchant_token_families 467 WHERE token_family_serial = 468 (SELECT token_family_serial 469 FROM merchant_token_family_keys 470 WHERE token_family_key_serial = NEW.token_family_key_serial); 471 CALL merchant_do_bump_number_stat 472 ('tokens-used' 473 ,my_merchant_serial 474 ,CURRENT_TIMESTAMP(0)::TIMESTAMP 475 ,1); 476 RETURN NEW; 477 END $$; 478 479 -- Whenever a token is used, call our trigger to bump statistics 480 CREATE TRIGGER merchant_used_tokens_on_insert_statistic 481 AFTER INSERT 482 ON merchant_used_tokens 483 FOR EACH ROW EXECUTE FUNCTION merchant_used_tokens_insert_statistics_trigger(); 484 485 -- Enable interval statistics 486 INSERT INTO merchant_statistic_interval_meta 487 (slug 488 ,description 489 ,stype 490 ,ranges 491 ,precisions) 492 VALUES 493 ('orders-created' 494 ,'number of orders created (but not necessarily claimed by wallets)' 495 ,'number' 496 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 497 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 498 ,array_fill (5, ARRAY[60]) -- precision: 5s 499 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 500 ), 501 ('orders-claimed' 502 ,'number of orders claimed by a wallet (but not necessarily paid)' 503 ,'number' 504 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 505 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 506 ,array_fill (5, ARRAY[60]) -- precision: 5s 507 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 508 ), 509 ('orders-paid' 510 ,'number of orders paid (but not necessarily settled by the exchange)' 511 ,'number' 512 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 513 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 514 ,array_fill (5, ARRAY[60]) -- precision: 5s 515 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 516 ), 517 ('orders-settled' 518 ,'number of orders settled' 519 ,'number' 520 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 521 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 522 ,array_fill (5, ARRAY[60]) -- precision: 5s 523 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 524 ), 525 ('tokens-issued' 526 ,'number of tokens issued to customers' 527 ,'number' 528 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 529 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 530 ,array_fill (5, ARRAY[60]) -- precision: 5s 531 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 532 ), 533 ('tokens-used' 534 ,'number of tokens used by customers' 535 ,'number' 536 ,ARRAY(SELECT generate_series (1, 60*60, 60)) -- for last hour, per minute 537 || ARRAY(SELECT generate_series (60*60*2, 60*60*48, 60 * 60)) -- for last 2 days, per hour 538 ,array_fill (5, ARRAY[60]) -- precision: 5s 539 || array_fill (5 * 60, ARRAY[48-1]) -- precision: 5 minutes 540 ); 541 542 -- Enable bucket statistics 543 INSERT INTO merchant_statistic_bucket_meta 544 (slug 545 ,description 546 ,stype 547 ,ranges 548 ,ages) 549 VALUES 550 ('payments-received-after-deposit-fee' 551 ,'amount customers paid to us (excluded deposit fees paid by us or customers, wire fees are still deducted by the exchange)' 552 ,'amount' 553 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 554 ,ARRAY[72, 14, 12, 24, 12, 10] 555 ), 556 ('total-deposit-fees-paid' 557 ,'deposit fees we or our customers paid to the exchange (includes those waived on refunds)' 558 ,'amount' 559 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 560 ,ARRAY[72, 14, 12, 24, 12, 10] 561 ), 562 ('total-wire-fees-paid' 563 ,'wire fees we paid to the exchange' 564 ,'amount' 565 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 566 ,ARRAY[72, 12, 12, 24, 12, 10] 567 ), 568 ('refunds-granted' 569 ,'refunds granted by us to our customers' 570 ,'amount' 571 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 572 ,ARRAY[72, 14, 12, 24, 12, 10] 573 ), 574 ('tokens-issued' 575 ,'number of tokens issued to customers' 576 ,'number' 577 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 578 ,ARRAY[72, 14, 12, 24, 12, 10] 579 ), 580 ('tokens-used' 581 ,'number of tokens used by customers' 582 ,'number' 583 ,ARRAY['hour'::statistic_range, 'day', 'week', 'month', 'quarter', 'year'] 584 ,ARRAY[72, 14, 12, 24, 12, 10] 585 ); 586 587 588 589 COMMIT;