summaryrefslogtreecommitdiff
path: root/src/auditordb/0001.sql
blob: f770d67efb4d726b9a164d0ce05d6203ebc62191 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
--
-- This file is part of TALER
-- Copyright (C) 2014--2020 Taler Systems SA
--
-- TALER is free software; you can redistribute it and/or modify it under the
-- terms of the GNU General Public License as published by the Free Software
-- Foundation; either version 3, or (at your option) any later version.
--
-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY
-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
-- A PARTICULAR PURPOSE.  See the GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License along with
-- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
--

-- Everything in one big transaction
BEGIN;

-- Check patch versioning is in place.
SELECT _v.register_patch('auditor-0001', NULL, NULL);


CREATE TABLE IF NOT EXISTS auditor_exchanges
  (master_pub BYTEA PRIMARY KEY CHECK (LENGTH(master_pub)=32)
  ,exchange_url VARCHAR NOT NULL
  );
-- Table with list of signing keys of exchanges we are auditing
CREATE TABLE IF NOT EXISTS auditor_exchange_signkeys
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,ep_start INT8 NOT NULL
  ,ep_expire INT8 NOT NULL
  ,ep_end INT8 NOT NULL
  ,exchange_pub BYTEA NOT NULL CHECK (LENGTH(exchange_pub)=32)
  ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
  );
-- Table with all of the denomination keys that the auditor
-- is aware of.
CREATE TABLE IF NOT EXISTS auditor_denominations
  (denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64)
  ,master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,valid_from INT8 NOT NULL
  ,expire_withdraw INT8 NOT NULL
  ,expire_deposit INT8 NOT NULL
  ,expire_legal INT8 NOT NULL
  ,coin_val INT8 NOT NULL
  ,coin_frac INT4 NOT NULL
  ,fee_withdraw_val INT8 NOT NULL
  ,fee_withdraw_frac INT4 NOT NULL
  ,fee_deposit_val INT8 NOT NULL
  ,fee_deposit_frac INT4 NOT NULL
  ,fee_refresh_val INT8 NOT NULL
  ,fee_refresh_frac INT4 NOT NULL
  ,fee_refund_val INT8 NOT NULL
  ,fee_refund_frac INT4 NOT NULL
  );
-- Table indicating up to which transactions the auditor has
-- processed the exchange database.  Used for SELECTing the
-- statements to process.  The indices below include the last
-- serial ID from the respective tables that we have
-- processed. Thus, we need to select those table entries that are
-- strictly larger (and process in monotonically increasing
-- order).
CREATE TABLE IF NOT EXISTS auditor_progress_reserve
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,last_reserve_in_serial_id INT8 NOT NULL DEFAULT 0
  ,last_reserve_out_serial_id INT8 NOT NULL DEFAULT 0
  ,last_reserve_payback_serial_id INT8 NOT NULL DEFAULT 0
  ,last_reserve_close_serial_id INT8 NOT NULL DEFAULT 0
  );
CREATE TABLE IF NOT EXISTS auditor_progress_aggregation
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,last_wire_out_serial_id INT8 NOT NULL DEFAULT 0
  );
CREATE TABLE IF NOT EXISTS auditor_progress_deposit_confirmation
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,last_deposit_confirmation_serial_id INT8 NOT NULL DEFAULT 0
  );
CREATE TABLE IF NOT EXISTS auditor_progress_coin
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,last_withdraw_serial_id INT8 NOT NULL DEFAULT 0
  ,last_deposit_serial_id INT8 NOT NULL DEFAULT 0
  ,last_melt_serial_id INT8 NOT NULL DEFAULT 0
  ,last_refund_serial_id INT8 NOT NULL DEFAULT 0
  ,last_payback_serial_id INT8 NOT NULL DEFAULT 0
  ,last_payback_refresh_serial_id INT8 NOT NULL DEFAULT 0
  );
CREATE TABLE IF NOT EXISTS wire_auditor_account_progress
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,account_name TEXT NOT NULL
  ,last_wire_reserve_in_serial_id INT8 NOT NULL DEFAULT 0
  ,last_wire_wire_out_serial_id INT8 NOT NULL DEFAULT 0
  ,wire_in_off INT8
  ,wire_out_off INT8
  );
CREATE TABLE IF NOT EXISTS wire_auditor_progress
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,last_timestamp INT8 NOT NULL
  ,last_reserve_close_uuid INT8 NOT NULL
  );
-- Table with all of the customer reserves and their respective
-- balances that the auditor is aware of.
-- last_reserve_out_serial_id marks the last withdrawal from
-- reserves_out about this reserve that the auditor is aware of,
-- and last_reserve_in_serial_id is the last reserve_in
-- operation about this reserve that the auditor is aware of.
CREATE TABLE IF NOT EXISTS auditor_reserves
  (reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
  ,master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,reserve_balance_val INT8 NOT NULL
  ,reserve_balance_frac INT4 NOT NULL
  ,withdraw_fee_balance_val INT8 NOT NULL
  ,withdraw_fee_balance_frac INT4 NOT NULL
  ,expiration_date INT8 NOT NULL
  ,auditor_reserves_rowid BIGSERIAL UNIQUE
  ,origin_account TEXT
  );
CREATE INDEX auditor_reserves_by_reserve_pub
  ON auditor_reserves
  (reserve_pub);
-- Table with the sum of the balances of all customer reserves
-- (by exchange's master public key)
CREATE TABLE IF NOT EXISTS auditor_reserve_balance
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,reserve_balance_val INT8 NOT NULL
  ,reserve_balance_frac INT4 NOT NULL
  ,withdraw_fee_balance_val INT8 NOT NULL
  ,withdraw_fee_balance_frac INT4 NOT NULL
  );
-- Table with the sum of the balances of all wire fees
-- (by exchange's master public key)
CREATE TABLE IF NOT EXISTS auditor_wire_fee_balance
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,wire_fee_balance_val INT8 NOT NULL
  ,wire_fee_balance_frac INT4 NOT NULL
  );
-- Table with all of the outstanding denomination coins that the
-- exchange is aware of and what the respective balances are
-- (outstanding as well as issued overall which implies the
-- maximum value at risk).  We also count the number of coins
-- issued (withdraw, refresh-reveal) and the number of coins seen
-- at the exchange (refresh-commit, deposit), not just the amounts. */GNUNET_PQ_make_execute (
CREATE TABLE IF NOT EXISTS auditor_denomination_pending
  (denom_pub_hash BYTEA PRIMARY KEY REFERENCES auditor_denominations (denom_pub_hash) ON DELETE CASCADE
  ,denom_balance_val INT8 NOT NULL
  ,denom_balance_frac INT4 NOT NULL
  ,denom_loss_val INT8 NOT NULL
  ,denom_loss_frac INT4 NOT NULL
  ,num_issued INT8 NOT NULL
  ,denom_risk_val INT8 NOT NULL
  ,denom_risk_frac INT4 NOT NULL
  ,payback_loss_val INT8 NOT NULL
  ,payback_loss_frac INT4 NOT NULL
  );
-- Table with the sum of the outstanding coins from
-- auditor_denomination_pending (denom_pubs must belong to the
-- respective's exchange's master public key); it represents the
-- auditor_balance_summary of the exchange at this point (modulo
-- unexpected historic_loss-style events where denomination keys are
-- compromised)
CREATE TABLE IF NOT EXISTS auditor_balance_summary
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,denom_balance_val INT8 NOT NULL
  ,denom_balance_frac INT4 NOT NULL
  ,deposit_fee_balance_val INT8 NOT NULL
  ,deposit_fee_balance_frac INT4 NOT NULL
  ,melt_fee_balance_val INT8 NOT NULL
  ,melt_fee_balance_frac INT4 NOT NULL
  ,refund_fee_balance_val INT8 NOT NULL
  ,refund_fee_balance_frac INT4 NOT NULL
  ,risk_val INT8 NOT NULL
  ,risk_frac INT4 NOT NULL
  ,loss_val INT8 NOT NULL
  ,loss_frac INT4 NOT NULL
  ,irregular_payback_val INT8 NOT NULL
  ,irregular_payback_frac INT4 NOT NULL
  );
-- Table with historic profits; basically, when a denom_pub has
-- expired and everything associated with it is garbage collected,
-- the final profits end up in here; note that the denom_pub here
-- is not a foreign key, we just keep it as a reference point.
-- revenue_balance is the sum of all of the profits we made on the
-- coin except for withdraw fees (which are in
-- historic_reserve_revenue); the deposit, melt and refund fees are given
-- individually; the delta to the revenue_balance is from coins that
-- were withdrawn but never deposited prior to expiration.
CREATE TABLE IF NOT EXISTS auditor_historic_denomination_revenue
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64)
  ,revenue_timestamp INT8 NOT NULL
  ,revenue_balance_val INT8 NOT NULL
  ,revenue_balance_frac INT4 NOT NULL
  ,loss_balance_val INT8 NOT NULL
  ,loss_balance_frac INT4 NOT NULL
  );
-- Table with historic profits from reserves; we eventually
-- GC auditor_historic_reserve_revenue, and then store the totals
-- in here (by time intervals).
CREATE TABLE IF NOT EXISTS auditor_historic_reserve_summary
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,start_date INT8 NOT NULL
  ,end_date INT8 NOT NULL
  ,reserve_profits_val INT8 NOT NULL
  ,reserve_profits_frac INT4 NOT NULL
  );
CREATE INDEX auditor_historic_reserve_summary_by_master_pub_start_date
  ON auditor_historic_reserve_summary
  (master_pub
  ,start_date);
-- Table with deposit confirmation sent to us by merchants;
-- we must check that the exchange reported these properly.
CREATE TABLE IF NOT EXISTS deposit_confirmations
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,serial_id BIGSERIAL UNIQUE
  ,h_contract_terms BYTEA CHECK (LENGTH(h_contract_terms)=64)
  ,h_wire BYTEA CHECK (LENGTH(h_wire)=64)
  ,timestamp INT8 NOT NULL
  ,refund_deadline INT8 NOT NULL
  ,amount_without_fee_val INT8 NOT NULL
  ,amount_without_fee_frac INT4 NOT NULL
  ,coin_pub BYTEA CHECK (LENGTH(coin_pub)=32)
  ,merchant_pub BYTEA CHECK (LENGTH(merchant_pub)=32)
  ,exchange_sig BYTEA CHECK (LENGTH(exchange_sig)=64)
  ,exchange_pub BYTEA CHECK (LENGTH(exchange_pub)=32)
  ,master_sig BYTEA CHECK (LENGTH(master_sig)=64)
  ,PRIMARY KEY (h_contract_terms,h_wire,coin_pub,merchant_pub,exchange_sig,exchange_pub,master_sig)
  );
-- Table with the sum of the ledger, auditor_historic_revenue and
-- the auditor_reserve_balance.  This is the
-- final amount that the exchange should have in its bank account
-- right now.
CREATE TABLE IF NOT EXISTS auditor_predicted_result
  (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE
  ,balance_val INT8 NOT NULL
  ,balance_frac INT4 NOT NULL
  );

-- Finally, commit everything
COMMIT;