059-statistics.rst (9915B)
1 DD 59: Statistics 2 ################# 3 4 :Design status: Accepted 5 :Implementation status: Implemented 6 :DD shepherd: TBD 7 :Historical contributors: Christian Grothoff 8 :First published: 2025-03-26 9 :Last substantive change: 2025-03-26 10 :Implementation evidence: merchant (2025-03-21) 11 :Normative references: :doc:`../core/api-merchant`, :doc:`../taler-merchant-manual`, :doc:`../core/api-exchange` 12 13 Summary 14 ======= 15 16 This design document elaborates how we track various statistics 17 in the exchange and merchant, typically for tax reporting or 18 to detect anomalies to be investigated by anti-money laundering 19 officers. The key idea is to use SQL triggers to keep the 20 statistics always up-to-date and a bit of garbage collection 21 to expire ancient statistics. Finally, deployment-specific 22 statistics can easily be added this way by simply injecting 23 the correct SQL code into the backend, without having to modify 24 the core exchange or merchant logic. 25 26 27 Motivation 28 ========== 29 30 Exchange operators are required to monitor for suspicious 31 transactions as part of their AML efforts. Merchants need to 32 collect certain data for their business, especially for tax 33 purposes but also conceivably to analyze sales. The specific 34 data to be tracked varies by operator (and legislation), so 35 we need to be quite flexible in terms of which statistics 36 should be kept, especially to minimize the performance impact. 37 38 39 Requirements 40 ============ 41 42 - statistics should always be up-to-date (in real-time) and 43 not only be updated in batches 44 - some statistics are amounts, others are simple numerical 45 (integer) values 46 - some statistics need to be kept over a sliding interval that 47 moves over time, while others need to be mapped to fixed 48 buckets such as a day, month, quarter or year. 49 - which statistics are being tracked may depend on the 50 operational context, especially for the exchange; it must 51 thus be easy to add (or remove) statistics at any time; 52 - while tracking statistics inherently costs performance the 53 runtime (CPU and storage) overhead should be minimized; 54 in particular for "sliding intervals", the events that 55 "slide out" of the interval may be coarsened and do not 56 necessarily require accuracy down to the second; 57 - when adding new statistics, it may be desirable to compute 58 them retroactivey over historic data (if available); 59 - the SPAs displaying statistics should not have to make an 60 excessive number of REST API calls, so generally multiple 61 values should be returned from a single endpoint; 62 - the merchant is multi-currency capable, and thus amount-valued 63 statistics in the merchant backend should be kept per currency 64 65 66 Proposed Solution 67 ================= 68 69 At a high-level, we use SQL TRIGGERs to update statistics on-the-fly whenever 70 an INSERT or UPDATE changes values in the database that would affect the 71 statistics we are tracking. The statistics themselves are also stored in the 72 database. Additionally, we also store meta-data in the database that 73 determines the granularity of the statistics to keep. This way, the SQL 74 TRIGGERs can be generic. 75 76 77 Tables 78 ------ 79 80 The schema consists of several tables: 81 82 - **statistic_bucket_meta**: 83 meta data for statistics we track in buckets, including 84 a slug used as the main key to identify the statistic, 85 the data type (amount or number), and a pair of 86 range of the bucket (year, quarter, month, etc.) 87 and how many ages (generations) of that bucket to keep. 88 Basically, now minus age times range tells us how far 89 into the past statistics are kept. 90 - **statistic_interval_meta**: 91 meta data used for statistics we keep by time interval, 92 with again a slug and data type, but then pairs 93 consisting of a number of seconds for the range 94 and a precision. A range of 60s implies that we 95 count the events of the last 60s in the interval. 96 Usually multiple ranges are given, like 60s, 120s 97 and 180s to keep events for the last one, two and 98 three minutes. Note that in the database the data 99 kept for the 120s range excludes the data from the 100 first 60s, and the correct amount for any interval 101 is computed on-the-fly by adding up the amounts for 102 the smaller intervals. A precision of 5s means that 103 event timestamps are normalized (and rounded down) 104 to multiples of 5s and thus events at 64s may still 105 be counted for the 60s interval. Note that both 106 intervals and precisions must be monotonically 107 increasing in their respective arrays. 108 - **statistic_bucket_counter**: contains the current 109 numeric (non-amount) value for a particular bucket 110 (given by starting point and range) and object; 111 objects in the exchange are (normalized) payto 112 hashes identifying bank accounts, while objects for 113 the merchant backend are the instances of the merchant. 114 - **statistic_bucket_amount**: same as the counter 115 bucket, except for an amount; in the case of the 116 merchant the *currency* is an additional dimension 117 as amounts are tracked per currency. 118 - **statistic_counter_event**: represents the sum of 119 all (counter) events at a particular time-*slot* in 120 time, again given per object; the *slot* is the 121 time of the event rounded to the *presision* of the 122 interval the event falls into. 123 - **statistic_amount_event**: same as the counter 124 event, except for amounts. 125 - **statistic_interval_counter**: cumulative value 126 of the counter for the given range; additionally 127 includes the unique ID of the oldest event that 128 is included in the counter (as the range is given 129 as a relative time, we need to track which events 130 are included). 131 - **statistic_interval_amount**: same as the 132 interval counter, except for amounts. 133 134 Stored procedures 135 ----------------- 136 137 We additionally provide a few stored procedures to 138 update the statistics. These are: 139 140 * **bump_number_bucket_stat** (slug,object,timestamp,delta): 141 increases the *slug* bucket counter of *object* at *timestamp* 142 by *delta*. Does nothing if *slug* is not in the 143 bucket meta table or if *timestamp* is past the range 144 of buckets we are currently tracking; 145 * **bump_amount_bucket_stat** (slug,object,timestamp,amount): 146 similar to the above, just for statistics of type amount; 147 * **bump_number_interval_stat** (slug,object,timestamp,delta): 148 increases the *slug* interval counter of *object* at 149 *timestamp* by *delta*; 150 * **bump_amount_interval_stat** (slug,object,timestamp,amount): 151 similar to the above, just for statistics of type amount; 152 * **bump_number_stat** (slug, object, timestamp, delta): 153 increases both bucket and interval counters for *slug* 154 of *object* at *timestamp* by *delta*; 155 * **bump_amount_stat** (slug, object, timestamp, delta): 156 similar to the above, just for statistics of type amount; 157 158 We furthermore provide a few stored procedures to 159 access the interval statistics (while making sure 160 they are current). These are: 161 162 * **statistic_interval_number_get** (slug, object): 163 returns all non-zero counters and time intervals that 164 we are tracking for the given *slug* and *object*; 165 the intervals are updated and events are possibly 166 discarded or coarsened (if numeric value of the 167 precision for larger intervals is larger and thus 168 allows for more rounding); 169 * **statistic_interval_amount_get** (slug, object): 170 returns all non-zero amounts and time intervals that 171 we are tracking for the given *slug* and *object*; 172 the intervals are updated and events are possibly 173 discarded or coarsened (if numeric value of the 174 precision for larger intervals is larger and thus 175 allows for more rounding); 176 177 Finally, there are some helpers for cleaning up: 178 179 * **statistic_bucket_gc** (): 180 Removes buckets and events past the range for which 181 we track statistics; also coarsens events to the 182 precision of the range into which they are falling. 183 * **exchange_drop_customization** (schema): 184 special function for the exchange which allows dropping 185 customizations by *schema*. 186 187 Schema 188 ------ 189 190 For the exchange, all triggers (and associated stored 191 procedures) should be stored in one or more payment 192 service provider specific schema. Furthermore, the name 193 of the schema MUST be provided in the *schema* column of 194 the meta data table entries. This way, all triggers, 195 stored procedures and statistics can be removed simply 196 by DROPing the SCHEMA and removing the associated 197 entries from the meta data tables (the foreign key 198 constraints then CASCADE and clean up the statistics 199 themselves). 200 201 The SCHEMA name must also be used as the prefix 202 for the SQL files that inject the triggers. The 203 usual database versioning should also be used, 204 except this time using the SCHEMA name instead of 205 "exchange-". "exchange" is not allowed as a name 206 for customization SCHEMA. 207 208 **taler-exchange-dbinit** is extended with command line arguments to load the 209 latest version of a customization schema or to call 210 **exchange_drop_customization** to remove one. 211 212 213 214 Definition of Done 215 ================== 216 217 - [x] key merchant statistics implemented 218 - [x] merchant REST API specified and implemented 219 - [ ] TOPS-deployment statistics verified end to end 220 - [x] REST API for AML officers specified and implemented 221 - [ ] SPA visualization of all key statistics verified 222 223 Alternatives 224 ============ 225 226 - batch processing to compute statistics, REST API only 227 returns the value computed by the last batch (computational 228 cost only paid if statistic is desired, but then may be high, 229 also potential for outdated data being shown); 230 - computing of statistics on the C side; may have more data 231 easily available, but has the major disadvantage of making 232 it harder to add/remove statistics and makes the transaction 233 logic more complex; also easier to miss triggering events; 234 235 236 Drawbacks 237 ========= 238 239 - ongoing baseline cost for statistics even if nobody looks 240 at them (but only if the respective statistic is enabled) 241 242 243 Discussion / Q&A 244 ================ 245 246 (This should be filled in with results from discussions on mailing lists / personal communication.)